Анализ N-грамм в Excel

Для поисковой рекламы и SEO анализ n-грамм — один из самых эффективных методов. Однако долгое время n-gram анализ оставался в силу сложности реализации алгоритма доступен только крупным агентствам с программистами в штате, или продвинутым специалистам со знанием программирования.

Чтобы популяризовать подход и сделать его доступным всем, у кого есть Windows и Excel, инструменты для анализа n-грамм были реализованы в !SEMTools для Excel. Ниже перечислены различные подходы анализа со схематичными примерами.

Во всех кейсах создается отдельный лист с результатами подсчета, исходные данные никак не изменяются.

Простой анализ n-gram (анализ встречаемости)

Данный подход самый простой — берется N-грамма и для нее анализируется ее встречаемость в тексте.

Анализ частотности слов, или как посчитать в тексте уникальные слова и вывести списком с их встречаемостью

Как посчитать, сколько раз встречается слово в Excel-таблице? Если мы ищем лишь одно слово, может помочь формула СЧЁТЕСЛИ. Формула ниже посчитает количество строк, в которых встречается последовательность символов «слова» вне зависимости от их регистра.

Символ звездочки определяет, что перед и после указанной последовательности символов могут быть любые другие или их отсутствие. В связи с этим могут быть учтены строки со словами «словарь», «словарный» и т.д. Чтобы найти слова по точному совпадению, нужно добавить символ пробела в начало и конец всех ячеек столбца, и воспользоваться подсчетом с учетом пробелов:

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

Поэтому был реализован макрос в !SEMTools, с легкостью выполняющий эту задачу.

Выделяем текст, выбираем слова, готово. Текст может быть как 5 строк, так и миллион строк — процедура займет секунды. Главное, чтобы уникальных слов в тексте было не больше 1048575 — иначе их не получится вывести на лист. Но такая ситуация — редкость.

Можно обратить внимание, что разные словоформы рассматриваются как отдельные слова, поэтому, если нужно проанализировать встречаемость без учета словоформ, текст нужно предварительно лемматизировать. Тогда вы составите не просто частотный словарь слов, а частотный словарь лемм.

Анализ встречаемости биграмм (2-gram)

Аналогично предыдущему, но берутся биграммы — последовательности из двух слов. Как посчитать в данном случае триграммы и т.д., кажется, уже понятно.

Анализ n-gram с частотностью

Когда текст состоит из фраз, и для каждой фразы известна определенная метрика (в поисковой рекламе это частотность), чтобы более достоверно измерить вес каждой словоформы или леммы, требуется производить анализ уже с учетом этой метрики.

В !SEMTools это вшито по умолчанию — просто нужно выделить два столбца вместе со столбцом используемой метрики. Аналогично можно составлять частотность биграмм, триграмм и т.д.

N-gram анализ по нескольким метрикам

Ранжируем отдельные слова по метрикам эффективности.

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

Измеряем расчетные метрики эффективности словосочетаний по набору абсолютных метрик и их значений

Заключение

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

Если у вас остались вопросы — подписывайтесь на канал автора и задавайте вопросы в чате: https://t.me/semtoolschat

Часто сталкиваетесь с этой или похожими задачами при работе в Excel?
Скачивайте !SEMTools и начинайте экономить рабочее время, выделяя его для более важных задач!

Функция СРЗНАЧ (или AVERAGE) вычисляет выборочное (или генеральное) среднее, то есть среднее арифметическое значение признака выборочной (или генеральной) совокупности. Аргументом функции СРЗНАЧ является набор чисел, как правило, задаваемый в виде интервала ячеек, например, =СРЗНАЧ (А3:А201).

Как найти размах в excel

1. Найдите панель функций с обозначением «Fx». Она над основной рабочей областью таблицы.
2. Поставьте курсор в любую ячейку.
3. Введите в поле «Fx» аргумент. Он начинается со знака равенства. Потом идёт формула и адрес диапазона/клетки.
4. Должно получиться что-то вроде «=МАКС(B8:B11)» (максимальное), «=МИН(F7:V11)» (минимальное), «=СРЗНАЧ(D14:W15)» (среднее).
5. Кликните на «галочку» рядом с полем функций. Или просто нажмите Enter. В выделенной ячейке появится нужное значение.
6. Формулу можно скопировать непосредственно в саму клетку. Эффект будет тот же.

Сутью этой формулы является функция НАИМЕНЬШИЙ, которая просто возвращает n-е наименьшее значение в списке значений, которое соответствует номеру строки. Номера строк были «отфильтрованы» функцией ЕСЛИ, которая применяет логику для совпадения.

Excel поддерживает символы подстановки «*» и «?». Тем не менее, если вы используете специальные символы с номером, вы будете преобразовывать числовое значение в текстовое значение. Другими словами, «*» & 99 & «*» = «* 99 *» (текстовая строка).

Функция СЧЁТЕСЛИ и подсчет количества значения ячейки в Excel

• Совершенствование умений и навыков нахождения статистических характеристик случайной величины, работа с расчетами в Excel;
• применение информационно коммутативных технологий для анализа данных; работа с различными информационными носителями.

1. Сегодня на уроке мы научимся рассчитывать статистические характеристики для больших по объему выборок, используя возможности современных компьютерных технологий.
2. Для начала вспомним:

В некоторых случаях перед нами может стоять задача – посчитать в массиве данных только пустые ячейки. Тогда крайне полезной окажется функция СЧИТАТЬПУСТОТЫ, которая проигнорирует все ячейки, за исключением пустых.

Уровень безработицы
Исходные данные
I	BEL(%)	ZVET(%)
3,2	6,9
3,1	6,7
3,2	6,5
3,3	7,1
3,3	6,8
3,2	6,4
3,2	6,6
3,1	7,3
3,0	6,5
3,0	6,5
3,0	6,0
2,9	5,7
3,1	6,0
3,1	6,9
3,1	6,5
3,0	7,0
3,2	6,4

Только что мы научились считать простейшим способом. Конечно, таким образом можно вычислить и более сложные вещи. Главное, не забывать ставить скобки, где нужно. Например: =((375*230)+(1263-455))/(120*33)

Получить первое, последнее или определенное значение читать подробную статью

В этой формуле, значение представляет собой именованный диапазон, который относится к Н2, а также данные , представляет собой именованный диапазон , который относится к B3: E102. Без названных диапазонов, формула может быть записана следующим образом: