Словарь в excel как сделать

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

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

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

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

Зарегистрировано в Министерстве РФ по делам печати, телерадиовещания и средств массовой информации. Свидетельство Эл 77-4640 от 28.06.2001 г

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

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

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

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

Как можно использовать частотный словарь? Увлекательный рассказ о статистическом лексическом анализе можно найти по адресу http://euro.svoboda.org/programs/sc/2001/sc.062601.asp. Там же есть и предостережение от излишнего увлечения такими методами.

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

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

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

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

Читать еще: Как сделать поверхность в excel?

Шаг 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, подсчитывающую сумму по группе. При необходимости сортируем по нужному полю.

Читать еще: Сводная диаграмма в excel как сделать

Замечание: можно решить эту задачу только с помощью 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 ‘динамический массив длин слов

Читать еще: Как сделать наложение таблиц в excel?

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 ‘ но уже не новое 🙁

UserForm1.Label3.Caption = x ‘ выводим новую информацию на форму
Else ‘ если такое слово уже встретилось, то
n(x) = n(x) + 1 ‘отмечаем это, увеличивая переменную на 1
c.Range.Delete ‘и удаляем это слово
End If
DoEvents ‘ Ждем окончания предыдущего процесса
Next c
End Sub

Sub Макрос8()
Set myTable = ActiveDocument.Tables(1)
i = 1
For Each aCell In myTable.Columns(2).Cells ‘повторяем с первого слова до последнего
UserForm1.Label2.Caption = i ‘ выводим новую информацию на форму
aCell.Range.Text = n(i) ‘вводим во второй столбец количество слов
i = i + 1
DoEvents ‘ Ждем окончания предыдущего процесса
Next aCell
i = 1
For Each aCell In myTable.Columns(3).Cells ‘повторяем с первого слова до последнего
UserForm1.Label2.Caption = i ‘ выводим новую информацию на форму
aCell.Range.Text = l(i) ‘вводим в третий столбец длину слов
i = i + 1
DoEvents ‘ Ждем окончания предыдущего процесса
Next aCell
End Sub

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

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