Анализ частоты слов Excel с пользовательской функцией
В этой статье мы познакомим вас с пользовательской функцией, которая может быстро генерировать список частотности слов в Excel.
Microsoft Excel — фантастический инструмент для работы с числами, но он может оказаться затруднительным, когда дело доходит до подсчета отдельных слов в электронных таблицах. Если вы когда-либо сталкивались с трудностями при выполнении этой задачи со сложными формулами Excel, есть эффективное решение — создание специальной функции, которая упрощает анализ частоты текста.
Пользовательская функция для анализа частоты слов/текста в Excel
Excel предлагает различные подходы для получения списка уникальных значений и количества определенного текста в целевом диапазоне. Однако эти методы часто требуют использования сложных функций Excel, которые не для всех могут быть интуитивно понятными и удобными для пользователя.
С помощью нашей пользовательской функции вы можете выполнить обе задачи за один раз:
- Получите список всех различных значений (слов и чисел), которые появляются в заданном диапазоне или ячейке.
- Найдите, сколько раз встречается каждое значение.
Чтобы получить функцию в Excel, просто добавьте ее код в редактор VBA, как описано в разделе «Как вставить код VBA в Excel».
Пользовательская функция для анализа частоты слов в тексте
Функция FreqWords(Data_range As Range, Необязательный разделитель As String) As Variant() Dim rCell As Range Dim text As String, sWord As String, PuncChars() As Variant Dim arr() As String, arr2() As Variant, arr3() Как вариант, i Как длинный, j Как вариант, y Как вариант PuncChars = Array(“.”, “,”, “;”, “:”, “‘”, “!”, “#”, _ “$” , “%”, “&”, “(“, “)”, “-“, “_”, “–“, “+”, _ “=”, “~”, “/”, “\” , “{“, “}”, “[“, “]”, “”””, “?”, “*”) ‘ разделителем по умолчанию является пробел. If Delimiter = “” Or Delimiter = ” ” then Delimiter = ” ” ‘ объединяет диапазон в текстовую строку для каждой rCell в Data_range ‘ удалить лишние пробелы text = text & ” ” & WorksheetFunction.Trim(rCell) Next rCell ‘ удалить знаки препинания For y = 0 To UBound(PuncChars) text = replace(text, PuncChars(y), “”) Next y Else For Each rCell In Data_range text = text & Delimiter & WorksheetFunction.Trim(rCell) Next rCell End If ‘создать массив текстовых значений arr() = Split(text, Delimiter)’ создать массив без дубликатов arr2 = ArrayUnique(arr) ‘ create 2D-массив для подсчета ReDim arr3(UBound(arr2) – 1, 1) Для i = 1 To UBound(arr2) arr3(i – 1, 0) = arr2(i) Следующий i Для j = 0 To UBound(arr3) arr3(j, 1) = Application.Count(Application.Match(arr, Array(arr3(j, 0)), 0)) Следующий j FreqWords = ArraySort(arr3, 1) Конечная функция Функция ArrayUnique(ByVal aArrayIn As Variant) As Variant ‘ Эта функция удаляет повторяющиеся значения из одномерного массива Dim aArrayOut() As Variant Dim bFlag As Boolean Dim vIn As Variant Dim vOut As Variant Dim i%, j%, k% ReDim aArrayOut(LBound(aArrayIn) To UBound( aArrayIn)) i = LBound(aArrayIn) j = i Для каждого vIn в aArrayIn Для k = j To i – 1 Если LCase(vIn) = LCase(aArrayOut(k)) Тогда bFlag = True: Выход для следующего Если не bFlag Тогда aArrayOut(i) = vIn: i = i + 1 bFlag = False Next If i <> UBound(aArrayIn) then ReDim Preserve aArrayOut(LBound(aArrayIn) To i – 1) ArrayUnique = aArrayOut End Function Function ArraySort(SourceArr As Variant, ByVal n As Integer) As Variant ‘ сортируем двумерный массив по столбцу N ‘ Счет столбцов начинается с 0 If n > UBound(SourceArr, 2) Or n < LBound(SourceArr, 2) Тогда _ MsgBox "Такого столбца нет в массиве!", vbCritical: выход из функции Dim Check As Boolean, iCount As Integer, jCount As Integer, nCount As Integer ReDim tmpArr(UBound(SourceArr, 2)) As Variant Do Until Check Check = True For iCount = LBound(SourceArr , 1) To UBound(SourceArr, 1) - 1 ' Знак "<" означает сортировку по убыванию If val(SourceArr(iCount, n)) < val(SourceArr(iCount + 1, n)) Тогда For jCount = LBound (SourceArr, 2) To UBound(SourceArr, 2) tmpArr(jCount) = SourceArr(iCount, jCount) SourceArr(iCount, jCount) = SourceArr(iCount + 1, jCount) SourceArr(iCount + 1, jCount) = tmpArr(jCount) ) Check = False Next End If Next Loop ArraySort = SourceArr End Function
Кончик. Если вы планируете часто использовать эту функцию, рассмотрите возможность сохранения ее в файле надстройки, а не в текущей книге. Таким образом, вы можете получить доступ к функции из любого файла Excel. Ниже приведены подробные инструкции по созданию и использованию надстроек для хранения пользовательских функций в Excel.
Синтаксис функции FreqWords
Прежде чем мы углубимся в использование пользовательской функции FreqWords, давайте сначала посмотрим на ее синтаксис:
FreqWords(диапазон_данных, [delimiter])
- Диапазон_данных (обязательно) — ячейка или диапазон ячеек для поиска слов.
- Разделитель (необязательно) – разделитель слов. Если этот параметр опущен, разделителем по умолчанию является пробел.
И вот несколько важных вещей, на которые стоит обратить внимание:
- Без учета регистра. Функция FreqWords нечувствительна к регистру, то есть она игнорирует регистр букв при подсчете частот слов. Однако это не меняет регистр слов в исходном диапазоне. Например, если слова «Улица», «улица» и «УЛИЦА» появляются последовательно в указанном диапазоне данных, функция будет рассматривать их как одно и то же слово и включит первое найденное слово («Улица») в выход с частотой 3.
- Выход отсортирован по частоте. Список частотности слов, сгенерированный функцией, будет отсортирован в порядке убывания количества слов. Это позволяет с первого взгляда идентифицировать наиболее часто встречающиеся слова.
Как использовать пользовательскую функцию FreqWords в Excel
После того как вы добавили функцию FreqWords в свой Excel, ее использование станет простым процессом. Вот пошаговое руководство:
- Выберите ячейку. Начните с выбора крайней левой ячейки, в которой вы хотите, чтобы отображался список частотности слов.
- Введите имя функции. В выделенной ячейке введите имя функции после знака равенства (=). Когда вы начнете вводить текст, Excel отобразит список доступных функций и названий. Найдите в списке функцию FreqWords и выберите ее. Excel автоматически вставит функцию в ячейку.
- Приведите аргументы. После вставки функции вам необходимо предоставить необходимые аргументы. Укажите диапазон data_range, в котором вы хотите подсчитывать частоты слов. Если ваши слова разделены символами, отличными от пробелов, укажите необязательный аргумент-разделитель.
- Заполните формулу. После ввода функции и ее аргументов нажмите клавишу Enter или сочетание клавиш Ctrl + Shift + Enter в зависимости от вашей версии Excel (подробности см. ниже). Затем Excel рассчитает частоту текста и отобразит результаты.
Кончик. К сожалению, Excel не отображает всплывающие подсказки для аргументов пользовательских функций, как для встроенных функций. Однако есть небольшая хитрость для отображения имен аргументов. Просто введите имя пользовательской функции, а затем нажмите сочетание клавиш Ctrl + Shift + A. Дополнительные сведения см. в статье, как отображать всплывающие подсказки для пользовательских функций VBA в Excel.
Альтернативно вы можете использовать Мастер функций для настройки функции FreqWords, как описано в руководстве, указанном выше.
Использование функции FreqWords в Excel 365 и 2021
В Excel с динамическими массивами FreqWord ведет себя как функция динамического массива, т. е. вы вводите формулу в одну ячейку, и она возвращает массив значений в соседние ячейки.
Использование функции FreqWords в Excel 2019–2007
В преддинамических версиях Excel вам необходимо использовать функцию FreqWords в качестве формулы массива, что требует несколько иного подхода:
- Выберите диапазон назначения. Выберите диапазон пустых ячеек, в которых вы хотите, чтобы отображались результаты. Эти ячейки будут заполнены частотами слов.
- Введите формулу. Введите формулу FreqWords в строке формул.
- Сделайте это формулой массива. Вот решающий шаг — вместо того, чтобы просто нажимать Enter, нажмите комбинацию клавиш Ctrl + Shift + Enter. Это говорит Excel, что формула должна рассматриваться как формула массива и вычислять ее соответствующим образом.
Дополнительные сведения см. в разделе «Как ввести формулу массива в Excel».
Как посчитать частоту слов в Excel
Предположим, у вас есть список адресов в формате A2:A25. Чтобы узнать, какие слова чаще всего встречаются в этом списке, введите в C3 такую формулу:
=Частослова(A2:A25)
Нажав Enter, вы получите список всех уникальных слов и количество раз, которое каждое слово появляется в исходном диапазоне. Слова будут отображаться в столбце C, а соответствующие им частоты — в столбце D.
В Excel 365 и Excel 2021 формула возвращает массив значений в нескольких ячейках. Однако в Excel 2019 и более ранних версиях обычная формула возвращает только одно значение. Чтобы получить список значений, обязательно введите его как формулу массива, как описано. выше.
Как выполнить анализ частоты слов с помощью специального разделителя
Часто при импорте данных в Excel из других программ или файлов CSV значения разделяются не пробелами, а специальными символами, такими как запятые, точки с запятой, двоеточия, косая черта и т. д.
В таких случаях нужно указать второй аргумент [delimiter] функции FreqWords.
Например, чтобы вычислить частоту слов в списке, разделенном запятыми, в формате A3:A16, используйте следующую формулу:
=FreqWords(A3:A16, “,”)
Если ваши значения разделены другим символом, при необходимости замените «,» конкретным разделителем.
Результатом этого расчета является таблица частотности слов, основанная на указанном вами разделителе:
Как посчитать частоту текста в одной ячейке
Если вам нужно подсчитать частоту текста в одной ячейке, просто укажите адрес этой ячейки в качестве первого аргумента функции FreqWords:
=FreqWords(A3, “,”)
В результате вы получите следующий список частотности слов:
Как сравнить частоту слов в двух столбцах
Microsoft Excel предлагает различные методы сравнения столбцов и выявления повторяющихся и уникальных значений в двух списках. Однако бывают ситуации, когда нужно сравнивать не значения ячеек целиком, а частоты слов внутри каждого столбца, независимо от их положения или порядка в ячейках. Именно здесь функция FreqWords становится неоценимой!
Давайте рассмотрим сценарий, когда у вас есть два списка с похожими элементами, но в разном порядке. Более того, элементы в этих списках могут иметь разное расположение слов, например «шоколадное молоко» и «молочный шоколад». Вопрос в следующем: эти списки идентичны или различны?
Решение включает в себя два простых шага.
Шаг 1. Найдите частоту слов в каждом списке.
Чтобы определить, сколько раз каждое слово появляется в каждом списке, используйте функцию FreqWords в ее базовой форме.
Частоты слов в списке 1:
=Частослова(A3:A18)
Частоты слов в Списке 2:
=Частослова(C3:C18)
Применяя эти формулы, вы получаете количество слов для каждого списка. Изучив результаты, как показано на скриншоте ниже, можно окончательно определить, что эти два списка различны.
Шаг 2. Сравните частотность слов в двух списках.
После того, как вы установили, что сравниваемые списки различны, вы можете углубиться и собрать более подробную информацию, например:
- Какие слова встречаются в обоих списках, а какие являются уникальными для одного списка?
- Какие слова встречаются в обоих списках одинаковое количество раз, а какие – разное?
Чтобы выполнить эту задачу, вам нужно расположить частоты слов рядом. В нашем примере, поскольку список 2 содержит больше уникальных слов, мы будем считать его основным списком и переносить частоты из списка 1 в соседний столбец с помощью функции XLOOKUP.
Формула ниже переходит в ячейку J3, а затем копируете ее в столбец:
=XLOOKUP(H3, $E$3:$E$10, $F$3:$F$10, «не найдено»)
Сравнивая количество слов в столбцах I (Список 1) и J (Список 2), вы можете четко увидеть, сколько раз каждое слово встречается в обоих списках. А текст «не найдено» в столбце J означает, что слово отсутствует в списке 2.
Чтобы сделать различия еще более очевидными, вы можете добавить в отдельный столбец такие метки, как «совпадает» и «не совпадает». Этого можно добиться с помощью следующего оператора IF, который сравнивает количество слов в столбцах I и J:
=IF(I3=J3, «Совпадение», «Не совпадение»)
«Совпадение» означает, что данное слово встречается в обоих списках одинаковое количество раз. «Не совпадает» означает, что слово встречается только в одном списке или имеет разное количество вхождений.
Это эффективный подход при работе с текстовыми данными, в которых слова расположены в другом порядке или в их сочетаниях. Он также отлично подходит для сравнения частот текста в двух списках с множеством слов в каждой ячейке, разделенных любым символом. Кроме того, это удобно для сравнения очень больших ячеек, даже очень больших, содержащих десятки или сотни разных слов.
Выделить совпадения и различия в таблицах частотности слов.
Используя формулы, рассмотренные выше, вы можете эффективно сравнивать частоты текста в двух столбцах. Чтобы получить еще более ценную информацию о сходствах и различиях между двумя списками, вы можете выделить эти различия цветом. Это можно легко сделать с помощью инструмента «Сравнить таблицы», входящего в состав нашего Ultimate Suite for Excel.
В качестве примера сравним списки частотности слов, полученные применением функции FreqWords: Таблица 1 (A3:B10) и Таблица 2 (D3:E13).
Подробные инструкции по использованию инструмента «Сравнить таблицы» можно найти в онлайн-документации. Здесь мы предоставим краткий обзор шагов:
- Выберите первую таблицу (A3:B10).
- Выберите вторую таблицу для сравнения (D3:E13).
- Укажите, хотите ли вы найти дубликаты (элементы, встречающиеся в обоих списках) или уникальные значения (различия между двумя списками). Мы выбираем дубликаты, т.е. находим одинаковое количество слов в обеих таблицах.
- Выберите столбцы для сравнения. Это ключевой шаг, поэтому внимательно делайте свой выбор. В этом примере мы выбираем две пары столбцов, которые включают слова и соответствующие им частоты.
- Наконец, выберите действие, которое вы хотите выполнить, например, выделив определенный цвет. Затем нажмите кнопку Готово.
- Повторите те же шаги, чтобы сравнить Таблицу 2 с Таблицей 1.
В результате в обоих списках будут выделены одинаковые частоты слов, что позволит визуально различать совпадения и различия.
Подводя итог, можно сказать, что анализ частотности текста в Excel больше не должен быть сложной задачей. Благодаря специальной функции FreqWords и инструменту «Сравнить таблицы» это становится очень просто! Независимо от того, работаете ли вы над текстовым анализом, интеллектуальным анализом данных или просто пытаетесь выяснить закономерности в своей электронной таблице, эти инструменты вместе сделают ваши задачи более простыми и понятными. Просто попробуйте и посмотрите, какую разницу они могут изменить.
Если у вас все еще есть какие-либо вопросы или вы хотите поделиться своими мыслями по этой теме, не стесняйтесь, оставьте несколько строк в комментариях ниже 🙂
Доступные загрузки
Подсчитайте частоты слов – примеры формул (файл .xlsm)
Полнофункциональная версия Ultimate Suite на 14 дней (файл .exe)
