Как сделать частотный словарь в excel

Обновлено: 07.07.2024

Недавно, для шлифовки морфологического словаря, способного (предположительно) генерировать все возможные формы слова из инфинитива — мне понадобился достаточно объемный частотный словарь русского языка. Частотный словарь — вещь очень простая, слова в нем упорядочены по частоте, с которой они встречаются в анализируемом тексте.

Задачка, казалось бы весьма простая и наверняка решается при изучении программирования в первых рядах. Но для анализа такой громоздкой библиотеки, а используемая мной библиотека простирается на 157 гектаров, средств среднего домашнего компьютера хватает с натягом. Если сказать точнее, то библиотека хранится в пятидесяти zip архивах по 0.5 — 2 Гб, в каждом из которых 20-30 тыс. произведений в формате fb2. В сжатом виде весит она 60 Гб.

Поиск решения

Массив

Самый очевидный метод решения, что называется «в лоб» — два массива, в первом — слова, во втором — число. Встретив новое слово, добавляем его в первый массив, если оно там отсутствует или прибавляем единичку к индексу во втором массиве, если нашли слово в первом. Опробовав данный вариант я незамедлительно в нем разочаровался, по прошествии нескольких часов программа скрипя ползла по первой половине первого — же архива. Любой профессиональный программист, наверняка уже смеется над таким подходом. Однако, признаюсь — я даже не предполагал что меня ждёт подвох.

Тогда я стал искать — где же то самое узкое место, которое не дает программе вздохнуть. Узкое место оказалось в моменте добавления нового слова. Чтобы сохранить массив упорядоченным — слово приходится вставлять где-то посередине, а иногда и в самое начало. Для этого приходится сдвигать каждый элемент массива расположенный правее выбранной позиции вправо. Когда речь идёт о миллионах слов — это занятие становится очень утомительным для процессора и он мстит, откладывая завершение программы на недели вперед.

Упорядоченный список

Пришлось искать такую структуру данных, которая позволит добавить каждый элемент просто в конец оной структуры, но позволит в то-же время их упорядочить. Тут-же я наткнулся на упорядоченные списки. В ячейке упорядоченного списка хранится сам кусочек данных и указатель на другую ячейку. Все превосходно, мы просто добавляем новое слово и меняем 2 индекса, индекс предыдущего слова, указывающий на данное и индекс данного слова, указывающие на следующее. Но, вот незадача, поиск в таком списке весьма вычислительно требователен. Если в упорядоченном массиве мы могли начинать поиск с середины и делить его пополам за одну итерацию, то в упорядоченном списке приходится карабкаться по указателям от одного к другому, как по ниточке, пока не найдем нужное место во всем клубке. Пробовать этот вариант я не стал, наученный предыдущим провалом я сразу пронюхал засаду.

Бинарное дерево поиска

Следующую структуру данных я нашел лишь через несколько часов. Мне попались бинарные деревья поиска. Немного почитав о различных вариантах я остановился на самобалансирующемся AVL дереве, созданном, кстати советскими учёными Адельсон-Вельским Георгием Максимовичем и Ландисом Евгением Михайловичем, и унаследовавшем от их фамилий своё название.

Структура бинарного дерева схожа с упорядоченным списком, но каждый элемент, кроме нескольких конечных (т.н. листьев) ссылается на два элемента. Корневой элемент — это тот, что находился бы в середине упорядоченного массива. Он ссылается на элемент меньший (левый) и больший (правый), кроме того — гарантированно, что все элементы левой ветви будут меньше данного, а все элементы правой ветви — больше. Рассмотрев левый или правый элемент, к которому мы пришли — мы увидим ту-же иерархию, он так-же ссылается на два элемента, левая ветвь так-же меньше, правая — больше. Чтобы понять способ балансировки такого дерева — лучше всего прочитать соответствующую статью, например тут: АВЛ-деревья. Важно заметить, что двоичное дерево поиска полностью удовлетворило моим требованиям. Быстрый поиск и быстрое добавление нового элемента.

Результат

В итоге, потратив еще несколько часов на оптимизацию я получил многопоточное приложение, которое распаковывает архив в оперативную память, считает частоту различных слов и с помощью AVL дерева обрабатывает полученные данные.

Вот несколько строк по результатам работы программы, слева — слово, справа — частота. Это самые часто встречаемые слова различной длины:

я 34361402
а 36192092
с 52479822
в 126422246
и 158458227

по 23978868
он 32602346
то 42163693
на 83001625
не 97183097

все 19576264
это 21318968
его 27719894
как 30228770
что 63106338

даже 6789652
была 6832494
если 7750404
меня 12381969
было 15450767

может 5455561
очень 5477013
время 6317279
когда 9788599
чтобы 9987225

человеконенавистничества 296
высококвалифицированного 350
высокопревосходительству 384
высокопревосходительства 489
высокопревосходительство 3739

человеконенавистнического 46
человеконенавистничеством 52
частнопредпринимательской 60
высокопревосходительством 91
националсоциалистическата 96

В общем было получено 9.5 млн. слов, анализ длился 8482 сек., средняя скорость обработки распакованного текста — 18.57 мб/сек.

Теперь можно использовать полученные данные для доработки моего морфологического словарика, а заполучив словарик можно будет улучшить «частотный анализатор», т.к. появится возможность группировки однокоренных слов. Кроме того, доработки требует работа «частотного анализатора» с различными кодировками и т.п. Далее — синтаксический анализ предложения. В конечном итоге хочу заполучить сколь-нибудь адекватную семантическую сеть.

Несмотря на то, что мой «отчет» затрагивает как тему программирования, так и лингвистику — прошу не винить меня за неточности в написании (особенно пунктуации) или не самое гладкое решение задачи, но прошу указать на эти ошибки или предлагать более изящные решения, я буду рад.

Андрей
Викторович ЛЫЗЛОВ

Ольга
Николаевна
НАЙДЕНОВА

учитель информатики школы №37 г.Рязани

учитель математики и информатики школы №37 г.Рязани

Написать эту статью побудили три материала, опубликованные в журнале.

Замечательная статья Ирины Алексеевны Морозовой «Коллективное лингвистическое исследование» о составлении частотного словаря букв русского алфавита [1]. На основе этой статьи можно организовать увлекательный урок, внеклассное мероприятие, она может стать основой исследовательской работы. Так что, если у статьи могут быть фанаты, то мы относимся к их числу.
Константин Алексеевич Попов «Использование частотных словарей при изучении иностранных языков» [2]. В этой статье рассматривается использование генераторов частотных словарей в учебном процессе.
«Все на борьбу с рутиной!» Павла Юрьевича Белкина и дальнейшее обсуждение этой статьи, в которой рассматривается проблема поиска и замены символов в MS Word [3]-[5].

Как у нас, так и у учеников, возник вопрос: «А можно ли средствами стандартных программ, без обращений к специальным, составить частотный словарь слов одного или нескольких произведений?»

В результате совместных изысканий появился способ, прямо скажем, не лишенный недостатков, который, однако, работает. Он может служить хорошей иллюстрацией глубокой интеграции офисных программ пакета Microsoft Office и хорошим практикумом для уроков компьютерных технологий.

Итак, наша идея состоит в следующем: удалить из текста все знаки кроме букв русского языка, получив, таким образом, список слов, а затем отсортировать его и, сгруппировав одинаковые слова, подсчитать количество слов в каждой группе.

Далее, собственно, сам способ:

Сначала откроем анализируемый текст в Microsoft Word.

Шаг 1. Уберем из текста точки, запятые, цифры, латинские буквы и… В принципе, избавимся от всего, кроме букв русского алфавита. Это можно сделать с помощью пункта меню Правка\Заменить. При поиске необходимо использовать отрицание [!] и подстановочные знаки [А-я]. Запрос на все символы, кроме букв русского алфавита, будет выглядеть так [!А-я].
Заменять будем на пробел, хотя это и необязательно. Можно использовать на любой другой символ, например, @ или специальный символ ^p («символ абзаца»). Нажимаем кнопку Заменить все.

Шаг 2. Убираем из текста лишние пробелы, заменяя двойные на одиночные до тех пор, пока двойные еще встречаются [5].

Шаг 3. Преобразуем весь текст к верхнему регистру Формат\Регистр\ВСЕ ПРОПИСНЫЕ, предварительно выделив весь текст Правка\Выделить все.

Шаг 4. Преобразуем текст в таблицу Таблица\Преобразовать\текст в таблицу. В качестве разделителя используем пробел. Данная таблица характерна тем, что у нее один столбец и в каждой ячейке стоит отдельное слово.

Шаг 5. Выделяем полученную таблицу и копируем в буфер обмена.

Далее можно воспользоваться одной из двух других знаменитых офисных программ: Microsoft Excel или Microsoft Access.

Для Microsoft Excel:

Шаг 6. Создаем новую книгу Excel.

Шаг 7. В ячейке A1 пишем заголовок: «Частотный анализ слов в произведении А.С.Пушкина «Полтава».

Шаг 8. Вставляем таблицу из буфера обмена, начиная с ячейки A2.

Шаг 9. На отдельном листе создаем сводную таблицу Данные\Сводная таблица. Это ли не повод рассказать ученикам о сводной таблице!

Шаг 10. Нажмите клавишу Поместить в. Мы увидим отчет из слов встречающихся в произведении. Для того чтобы вывести количество вхождений данного слова в текст, достаточно перетащить элемент в сводную таблицу. По умолчанию будет как раз вычисляться количество повторений этого слова, что собственно нам и нужно. Таким образом, мы получили частотный словарь. Далее его можно изменять по своему усмотрению. Изменяя свойства поля можно отсортировать сводную таблицу по убыванию, подсчитать долю слов в произведении, добавить столбец с длиной слов и т.п.

Для Microsoft Access:

Шаг 6. Создаем новую базу данных. В режиме конструктора создаем таблицу «Слова» с полем [Слово].

Шаг 7. Переходим в режим таблицы и Вставляем таблицу из буфера обмена.

Шаг 8. Создаем запрос на выборку, используя групповые операции и функцию Count, подсчитывающую сумму по группе. При необходимости сортируем по нужному полю.

Замечание: можно решить эту задачу только с помощью Word и макросов на языке Visual Basic for Application (VBA), но это не совсем соответствует поставленной задаче. С другой стороны, при таком подходе можно автоматизировать описанные выше действия. Можно также попробовать составить частотный словарь с учетом словоформ, правда, только для английского языка.

Последовательность действий будет примерно такой:

Шаг 1. Удаляем из текста все знаки, кроме букв русского языка.

Шаг 2. Удаляем из текста лишние пробелы.

Шаг 3. Устанавливаем один шрифт для всего текста. Выделяем и переводим в верхний регистр.

Шаг 4. Заменяем пробелы на символ конца абзаца.

Шаг 5. Сортируем по возрастанию.

Шаг 6. Составляем частотный словарь и вычисляем длину слов.

Шаг 7. Преобразуем в таблицу с тремя столбцами.

Шаг 8. Выводим во второй столбец количество вхождений данного слова, а в третий - его длину.

Шаг 9. Добавляем заголовок.

Большинство описанных выше операций можно просто записать в макросы с помощью режима «Запись макроса» (Сервис\Макрос\Начать запись).

Сложности возникают в двух местах:

1. При удалении двойных пробелов. Действительно, количество повторений этой операции неизвестно, поэтому необходимо добавить в записанный макрос цикл с постусловием (Do-Loop). В приведенном ниже примере добавленные команды выделены красным цветом.

Sub Макрос2()
Do
Selection.Find.ClearFormatting
Selection.Find.Replacement.ClearFormatting
With Selection.Find
.Text = " "
.Replacement.Text = " "
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Loop While Selection.Find.Execute(Replace:=wdReplaceAll)
end Sub

2. Пожалуй, самое интересное. Для реализации шагов 6 и 8 добавим в код модуля NewMacros новые макросы, осуществляющие непосредственную обработку текста.

Dim n() As Integer 'динамический массив количества вхождений слов
Dim l() As Integer 'динамический массив длин слов

Sub Макрос6()
Dim x As Long 'количество слов
Dim y As Long 'количество обработанных слов
Dim i As Integer 'счетчик цикла

Dim s_old As String 'предыдущее слово
Dim s_next As String 'новое слово

UserForm1.Label1.Caption = ActiveDocument.Range.Paragraphs.Count 'определяем количество всех слов
Set myRange = ActiveDocument.Range.Paragraphs ' рассматриваем каждый абзац в отдельности
s_old = "" 'начальные присваивания
x = 0
y = 0
ReDim n(x)
ReDim l(x)
n(x) = 1
For Each c In myRange 'перебираем все абзацы по порядку
y = y + 1 'считано очередное слово
UserForm1.Label2.Caption = y ' выводим новую информацию на форму
s_next = c 'новое слово равно текущему абзацу
If s_next <> s_old Then 'если найдено новое слово, то
x = x + 1 'количество слов будет на 1 больше
ReDim Preserve n(x) 'расширяем динамические массивы
ReDim Preserve l(x)
l(x) = Len(s_next) - 1 ' определяем длину этого слова, без символа конца абзаца
n(x) = 1 'пока оно встретилось только один раз
s_old = s_next ' но уже не новое :-(

Пример этих макросов находится в документе Частотный анализ слов_А_С_ Пушкин_ ПОЛТАВА.doc. Для выполнения макросов необходимо установить в пункте меню Сервис\Макрос\Безопасность уровень безопасности: «Низкий» или «Средний».

После изменения уровня безопасности - перезапустите MS Word.

Запустите форму my_form, нажав кнопку Составление словаря на панели Частотный анализ.

Для
работы
вам
понадобится
русскоязычный текст объемом около 3 стр.
(порядка 1200-1400 слов).
В ходе выполнения работы каждому
студенту необходимо представить в отдельной
папке:
1) исходный вариант текста-источника (в формате
Microsoft Word);
2) трансформированный вариант исходного
текста без знаков препинания в виде списка слов
(в формате Microsoft Word);
3) Частотный словарь словоформ (в формате
Microsoft Excel).

1. Первым этапом в построении частотного словаря
станет удаление знаков препинания из текста. Для этого
необходимо использовать опцию «Правка» → «Найти» →
«Заменить».
В поле «Найти» пишем нужный знак препинания
(например, запятую), в поле заменить – ставим пробел.
(Рис. 1.)
Далее нажимаем на кнопку «Заменить все». (Рис.
2). Запятые будут убраны из всего текста.
Аналогичную операцию проводим с оставшимися
знаками препинания (точки, кавычки и т.д.).

2. Следующий шаг – постановка всех слов текста в
столбик. Для этого также будем использовать опцию
«Правка» → «Найти» → «Заменить».
В поле «Найти» ставим знак пробела (нажать один
раз), в поле заменить нам нужно указать знак абзаца.
(Рис. 3.) Для этого в поле «Специальные знаки» выберем
параметр «Знак абзаца».
Далее нажимаем на кнопку «Заменить все». (Рис.
4). Слова должны расположиться друг под другом.

3. Далее сориентируем все слова по центру листа (Рис. 5).

4. С помощью сочетания клавиш Shift
прописные буквы (Рис. 6).
и
F3 уберем

6. Вставляем скопированные слова в таблицу Microsoft Excel
(Рис. 8).

8. Далее сортируем список слов в таблице по алфавиту.
Для этого необходимо выделить их нажатием на колонку
«А». Затем использовать опцию сортировка по
возрастанию (от А до Я). (Рис. 10)

9. При необходимости можно удалить ячейки с цифровыми
данными (Рис. 11).

10. Затем нажатием правой кнопки мыши по первой ячейке
добавляем новую ячейку (Рис. 12).

11. Затем называем нашу таблицу. Например, «Частотный
словарь» (Рис. 13).

12. Далее выделяем столбец нажатием на колонку «А» и
создаем сводную таблицу на новом листе. Для этого
переходим на вкладку «Данные» → «Сводная таблица». (Рис.
14)

13. Затем соглашаемся с предлагаемыми в диалоговом
окне параметрами и заканчиваем создание сводной
таблицы на новом листе (Рис. 15-17).

14. После этого дважды щелкаем левой кнопкой мыши
по значку «Частотный словарь» и в левой части листа
появляется сводная таблица (Рис. 18).
В отличие от предыдущего списка слов, в сводной
таблице каждая словоформа встречается только один
раз.

15. Для получения полноценного частотного словаря в
полученной сводной таблице необходимо отобразить
столбец с частотой слов.
Для этого перетаскиваем значок «Частотный словарь»,
нажав на него левой кнопки мыши, в пустую выделенную
область справа от списка слов (Рис. 19).

16. После этого отобразиться столбец с частотой слов (Рис.
20).

17. Далее выделяем столбец с числовыми значениями
(лучше выделять от последнего слова к первому). Затем
копируем выделение. После этого нажимаем на вкладку
«Данные» «Сортировка». Выбираем позицию «По
убыванию» (Рис. 21).

Как создать частотное распределение в Excel?

Распределение частот в Excel с помощью формул
Распределение частот в Excel с использованием сводной таблицы

Давайте изучим эту концепцию на примере. В корпоративной компании проводился ежегодный обзор, и каждый получил оценку из 10 возможных. Всего было проведено 50 сотрудников.

Ниже представлены рейтинговые данные для 50 сотрудников.

Шаг 1: Теперь мне нужно проверить, сколько людей получили рейтинг от 4 до 6, от 6 до 8 и от 8 до 10. На этот раз я не применяю сводную таблицу; скорее, я буду использовать функцию СЧЁТЕСЛИ, чтобы получить сумму. Перед этим я создал такие частотные уровни.

Шаг 2: Я продолжаю и применяю функцию СЧЁТЕСЛИ, чтобы получить общее количество. В ячейке E2 я упоминаю функцию СЧЁТЕСЛИ, которая считает все числа в диапазоне от A2 до A52, которые меньше или равны 6.

Шаг 3: В ячейке E3 я использовал функцию СЧЁТЕСЛИМН, которая считает числа, если число больше 6, но меньше 8.

Шаг 4: В ячейке E4 используйте функцию СЧЁТЕСЛИМН, которая считает числа, если число больше 8, но меньше 10.

Давайте изучим это понятие на примерах.

Позвольте мне объяснить вам на примере частотного распределения в Excel. У меня есть данные о проданных единицах с указанием цены продукта.

Шаг 1: Выберите данные и примените сводную таблицу.

Шаг 2: Перетащите заголовок «Цена продукта» в «Строки и единицы, проданные до значений».

Шаг 3: Теперь сводный сводный отчет должен выглядеть так.

Шаг 4: Теперь щелкните правой кнопкой мыши столбец «Цена продукта» и выберите «Группа».

Шаг 5: После того, как вы нажмете «Группа», откроется диалоговое окно, показанное ниже.

Шаг 7: Щелкните ОК. Значения сгруппированы в сводной таблице, такие как 15–30, 31–45, 46–60 и т.

Заключение: Теперь мы можем проанализировать это наибольшее количество проданных единиц при цене от 15 до 29, т. Е. 54819 единиц.

Когда цена продукта составляет от 30 до 44 единиц, количество проданных единиц составляет 53794 и аналогично, наименьшее количество проданных продуктов при цене от 45 до 59, т. Е. 10982.

Было проведено исследование денег, потраченных на алкоголь, по возрастным группам. В разных возрастных группах деньги тратятся ежемесячно, у меня есть данные. Из этих данных мне нужно выяснить, какая возрастная группа тратит больше.

Ниже приведены данные.

Шаг 1: Примените сводную таблицу к этим данным. В ROWS укажите Age, а для значений укажите Amt Spent.

Шаг 2: Теперь сводный сводный отчет должен выглядеть так.

Шаг 3: Теперь щелкните правой кнопкой мыши возраст в сводной таблице и выберите «Группировать». Начиная с упоминания 15 и заканчивая 72 и По упоминания 15.

Шаг 4: Щелкните по ОК. Он сгруппирует возраст, вернет сумму для возрастной группы.

Заключение: Понятно, что возрастная группа от 15 до 29 тратит больше денег на потребление алкоголя, что не является хорошим признаком.

Но возрастная группа от 30 до 44 лет тратит на алкоголь меньше; возможно, они осознали ошибку, которую совершили в молодом возрасте.

Читайте также: