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

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

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

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

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

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

© Copyright 2001-2005. Федерация Интернет Образования.

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

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

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

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

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

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

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

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

Сначала откроем анализируемый текст в 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

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

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

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.

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

Пошаговое руководство создания частотного словаря с помощью MS Word и MS Excel:

Транскрипт

1 Пошаговое руководство создания частотного словаря с помощью MS Word и MS Excel: 1. ПОДГОТОВКА ТЕКСТА В MS WORD: Шаг 1. Копируем текст в MSWord, избавляемся от всех знаков пунктуации (в том числе от пробелов) и разносим каждое слово на отдельную строку с помощью инструмента Заменить (команда Ctrl+H): Для поиска/замены знаков препинания необходимо включить опцию «Подстановочные знаки» (включается по кнопке «Больше >>»). Не все знаки пунктуации можно перечислять «как есть», некоторые из них сами являются командой для функции подстановочных знаков, поэтому их нужно перечислять через обратный слеш ! или?. Лучше поставить перед каждым знаком. Если нужно, скопируйте:[.,:;»()!? -] Обратите внимание, что если оставить знак дефиса в этом перечне, то такие слова, как «когда-нибудь» будут разделены на два отдельных слова (как и предлагает Л.Н. Засорина в своем частотном словаре). Замена производится на Знак Абзаца (кнопка «Специальный» или команда ^p). Шаг 2. После того, как знаки убраны, а каждое слово на отдельной строке, выделяем весь текст (команда Ctrl+A) и командой Shift+F3 приведем все слова к единому регистру (текст получится более стройным, если все слова будут с маленькой буквы).

2 2. ОБРАБОТКА В MS EXCEL: Затем следует избавиться от пустых строк с помощью того же инструмента замены (Ctrl+H), только теперь Подстановочные знаки должны быть отключены. Чтобы убрать пустые строки нужно Найти каждые два Знака абзаца (^p^p) и Заменить на один такой Знак абзаца (^p). За один проход по кнопке Заменить всё количество пустых строк сократится только вдвое. Замену следует производить до тех пор, пока пустых строк совсем не останется. Текст готов к дальнейшей обработке в MS Excel. Выделить весь текст Ctrl+A, вырезать Ctrl+X. Шаг 3. Полученный список следует вставить в MS Excel в ячейки A2 и С2. Первый столбец для словоупотреблений, второй для их грамматических категорий, а третий слова, приведенные к их исходной форме (леммы). Назовите их соответственно:

3 Шаг 4. Лемматизация для столбца С и определение для каждого словоупотребления его грамматической категории в столбце B производится самостоятельно (при этом MS Excel помогает с автозаполнением слов, упомянутых в списке выше). Шаг 5. Для создания частотного словаря, выделим столбцы с грамматическими категориями и леммами, проведя мышью по их буквам: B и C. На вкладке Вставка слева выбираем Сводная таблица. Рекомендуется не менять предложенные параметры, сводная таблица будет создана на новом листе: Для заполнения таблицы 1 из пояснения к Лабораторной работе 3 включите поле Грамматических категорий и перетащите его в область Σ Значения. Затем в столбце C рассчитайте относительную частоту грамматических категорий.

4 Шаг 6. Для наглядности простройте диаграмму из данных A и C (несмежные диапазоны выделяются с прижатой клавишей Ctrl). Диаграмму можно будет вставить в лабораторную работу для каждого из отрывков (см. рис. ниже). Шаг 7. Для заполнения таблицы 2 из пояснения к Лабораторной работе 3 включите опцию Леммы чтобы получить следующую картину (Названия строк должны стоять именно в таком порядке Грам. кат. выше, чем Лемма): Чтобы отсортировать глаголы и другие части речи не по алфавиту, а по частоте использования, станьте на ячейку с первым значением (на рис. это ячейка B5) и нажмите сортировку (от Я до А). Кнопка сортировки находится на Главной вкладке справа (рядом с опцией Найти и выделить).

Читать еще:  Круглая диаграмма как сделать в excel

5 Шаг 8. Создайте таблицу 2 и заполните ее по следующему принципу: в колонку Частота:>=2 следует копировать только те строки, в которых перечислены слова, упомянутые более 1 раза в произведении. В колонку Частота 1 остальные, уникальные леммы. Существительные Частота: >=2 Частота: 1 клён 3 береза, верба, деревня, дом, дорога, доска, жена, лето, нога, метель 2 песня, попойка, скромность, сосна, сторож, сугроб Глаголы Частота: >=2 Частота: 1 выйти 2 опасть 2 встретить, дойти, казаться, нагнуться, обнимать, погулять, приметить, приморозить, распевать, стать, стоять, увидеть, услышать, утонуть, утратить и т.д. Частота: >=2 Частота: 1 Если со вставкой в колонку Частота:>=2 вопросов обычно не возникает, то при вставке уникальных лемм следует учитывать, что при копировании из MS Excel в ячейку таблицы MS Word вставляется вложенная таблица. Ее следует преобразовать в текст на вкладке Макет, либо изначально вставлять скопированный текст через Специальную вставку. или или Отсортируйте строки уникальных лемм по алфавиту (от А до Я). Затем произведите замену Знаков Абзаца ^p на запятую с пробелом:

Составление частотного словаря слов средствами Microsoft Office

Сайт ВИО (vio. *****) – электронный журнал «Вопросы Интернет-образования»

Выдержки из статьи http://vio. *****/vio_28/cd_site/Articles/art_2_3.htm

Составление частотного словаря слов средствами Microsoft Office

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

1. Замечательная статья Ирины Алексеевны Морозовой «Коллективное лингвистическое исследование» о составлении частотного словаря букв русского алфавита [1]. На основе этой статьи можно организовать увлекательный урок, внеклассное мероприятие, она может стать основой исследовательской работы. Так что, если у статьи могут быть фанаты, то мы относимся к их числу.

2. Константин Алексеевич Попов «Использование частотных словарей при изучении иностранных языков» [2]. В этой статье рассматривается использование генераторов частотных словарей в учебном процессе.

3. «Все на борьбу с рутиной!» Павла Юрьевича Белкина и дальнейшее обсуждение этой статьи, в которой рассматривается проблема поиска и замены символов в MS Word [3]-[5].

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Для Microsoft Access:

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

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

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

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

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

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

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

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

Читать еще:  Таблицы как сделать excel

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

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

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

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

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

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

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

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

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

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

Если вы хотите, чтобы макросы были доступны для других документов, просто скопируйте их и панель с кнопкой с помощью «Организатора» в шаблон Normal. dot (СервисШаблоны и надстройки…).

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

В этой статье я хочу рассказать, как можно подсчитать количество повторений какого-либо значения в таблице или в ячейке. Начнем по порядку. Имеется таблица:

И необходимо подсчитать количество повторений каждого наименования:

Как ни странно, но сделать это весьма просто: в Excel имеется функция — СЧЁТЕСЛИ, при помощи которой все это сделать можно буквально за секунды. Если количество повторений каждого наименования необходимо вывести в столбец В таблицы, а сами наименования расположены в столбце А:
=СЧЁТЕСЛИ( $A$2:$A$30 ; A2 )
Диапазон ( $A$2:$A$30 ) — указываются ячейки диапазона, в которых записаны значения, количество которых необходимо подсчитать. Главная особенность: данный аргумент может быть исключительно ссылкой на ячейку или диапазон ячеек. Недопустимо указывать произвольный массив значений.
Критерий ( A2 ) — указывается ссылка на ячейку или непосредственно значение для подсчета. Т.е. можно указать и так: =СЧЁТЕСЛИ( $A$2:$A$30 ;»Яблоко») . Помимо этого можно применять символы подстановки: ? и *. Т.е. указав в качестве Критерия «*банан*» можно подсчитать количество ячеек, в которых встречается слово «банан» (банановый, банан, бананы, банановый сок, сто бананов, три банана и орех и т.п.). А указав «банан*» — значения, начинающиеся на «банан» (бананы, банановый сок, банановая роща и т.п.). «?» — заменяет лишь один символ, т.е. указав «бан?н» можно подсчитать строки и со значением «банан» и со значением «банон» и т.д. Если в качестве критерия указать =СЧЁТЕСЛИ( $A$2:$A$30 ;»*») , то будут подсчитаны все текстовые значения. Числовые значения при этом игнорируются. Данные подстановочные символы (* и ?) не получится применить к числовым значениям — исключительно к тексту. Т.е. если если указать в качестве критерия «12*», то числа 1234, 123, 120 и т.п. не будут подсчитаны. Для подсчета числовых значений следует применять операторы сравнения: =СЧЁТЕСЛИ( $A$2:$A$30 ;»>12″)

Подсчитать числа, которые больше нуля: =СЧЁТЕСЛИ( $A$2:$A$30 ;»>0″)
Подсчитать количество непустых ячеек: =СЧЁТЕСЛИ( $A$2:$A$30 ;»<>«)

Как видно из второго рисунка — там наименования не повторяются, хотя в таблице они все записаны вперемешку. Я не буду заострять на этом внимание — я уже описывал это в статье Как получить список уникальных(не повторяющихся) значений? и при необходимости можно воспользоваться любым описанным в ней методом.

Если необходимо подсчитать количество повторений на основании нескольких условий(значений), то начиная с 2007 Excel это легко можно сделать при помощи функции СЧЁТЕСЛИМН(COUNTIFS). Синтаксис функции почти такой же, как у СЧЁТЕСЛИ(COUNTIF), только условий и диапазонов больше:
=СЧЁТЕСЛИМН( $A$2:$A$30 ; A2 ; $B$2:$B$30 ; B2 )
предполагается, что условия записаны в столбце В
По сути идет просто перечисление:
=СЧЁТЕСЛИМН(Диапазон_условий1;Условие1; Диапазон_условий2;Условие2; Диапазон_условий3;Условие3; и т.д.)

Но. Бывают случаи, когда список расположен вовсе не в таблице, а в одной ячейке( $D$1 ):
Дыня Киви Груша Яблоко Дыня Груша Груша Арбуз Яблоко Банан Яблоко Яблоко Банан Яблоко Яблоко Дыня Дыня Киви Банан Дыня Арбуз Дыня Киви Яблоко Дыня Груша Яблоко Киви Арбуз
Здесь СЧЁТЕСЛИ точно не поможет. Но в Excel полно других функций и все можно сделать так же достаточно просто:
=(ДЛСТР( $D$1 )-ДЛСТР(ПОДСТАВИТЬ( $D$1 ; D3 ;»»)))/ДЛСТР( D3 )
ДЛСТР — подсчитывает количество символов в указанной ячейке/строке( $D$1 , D3 )
ПОДСТАВИТЬ (текст; старый_текст; новый_текст) — заменяет в указанном тексте заданный символ на любое другое заданное значение. По умолчанию заменяет все повторы указанного символа. Именно это и положено в основу алгоритма. На примере значения Банан( D3 ) пошаговый разбор формулы:

  • при помощи функции ДЛСТР получаем количество символов в строке с исходным текстом( $D$1 ) =(170-ДЛСТР(ПОДСТАВИТЬ( $D$1 ; D3 ;»»)))/ДЛСТР( D3 ) ;
  • при помощи функции ПОДСТАВИТЬ заменяем в строке с исходным текстом( $D$1 ) все значения Банан( D3 ) на пусто и при помощи ДЛСТР получаем количество символов строки после этой замены =(170-155)/ДЛСТР( D3 ) ;
  • вычитаем из общего количества символов количество символов в строке после замены и делим результат на количество символов в критерии =(170-155)/5 .

Получаем число 3. Что нам и требовалось.

И простая функция пользователя, которая так же подсчитывает повторения внутри ячейки:

Function GetRepeat(sTxt As String, sCntWord As String) GetRepeat = (Len(sTxt) — Len(Replace(sTxt, sCntWord, «»))) / Len(sCntWord) End Function

Чтобы правильно использовать приведенный код, необходимо сначала ознакомиться со статьей Что такое функция пользователя(UDF)?. Вкратце: скопировать текст кода выше, перейти в редактор VBA( Alt + F11 ) -создать стандартный модуль(InsertModule) и в него вставить скопированный текст. После чего функцию можно будет вызвать из Диспетчера функций( Ctrl + F3 ), отыскав её в категории Определенные пользователем (User Defined Functions) .
Синтаксис функции:
=GetRepeat( $D$1 ; D3 )
sTxt — текст, в котором подсчитываем кол-во вхождения.
sCntWord — текст для подсчета. Может быть символом или словом.

Tips_All_Count_Duplicate.xls (39,0 KiB, 9 217 скачиваний)

Статья помогла? Поделись ссылкой с друзьями!

Ссылка на основную публикацию
Adblock
detector