Находим самые популярные слова в опросах

Находим самые популярные слова в опросах

Магия таблиц на службе общественного мнения

Продолжаем практиковаться с электронными таблицами. Сегодня вместо Экселя попробуем поработать с Гуглотаблицами — у них есть много дополнительных функций, которые нам пригодятся. Если вы пропустили начало нашей практики — держите подборку:

Что делаем

Ситуация: мы провели опрос в интернете — например про увольнения и их причины. Все ответы собрались в одной гуглотаблице, потому что опрос был в гуглоформах. Нам стало интересно, что больше всего беспокоит людей в вопросах увольнения. Самый простой способ это выяснить — посмотреть, какие слова в ответах используются чаще всего, и сделать на основе этого какие-то выводы.

Мы взяли колонку ответов на вопрос:

«Что должно произойти на работе, чтобы вы положили руководителю на стол заявление об увольнении? Вот прямо чтобы уволиться одним днём».

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

Посмотреть таблицу

Алгоритм подсчёта

Разобъём нашу задачу на два этапа: поиск повторяющихся слов и подсчёт тех из них, которые нам покажутся важными.

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

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

Чтобы это поправить, мы на втором этапе сделаем так: посмотрим на самые осмысленные слова и уже по ним посчитаем частотность, используя более сложные алгоритмы. 

Считаем слова

Для подсчёта слов используем формулу, которую сделал разработчик с ником contributorpw:

=QUERY(
TRANSPOSE(SPLIT(QUERY(A:A;;99^99);" "));
"select lower(Col1), count(Col1)
group by lower(Col1)
order by count(Col1) desc
label lower(Col1)'Слово', count(Col1)'Количество'";
1
)

Эта формула работает так:

  1. Сначала она разбивает по пробелам все слова в первой колонке
  2. Потом использует их как основу для запроса с автоматической сортировкой и подсчётом слов.
  3. Результат выполнения запроса возвращается в формулу и превращается в 2 колонки — со словами и их количеством

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

Вставим эту формулу в ячейку C1 — она автоматически заполнит словами и их количеством две ближайшие колонки:

Находим самые популярные слова в опросах

Считаем слова в общем виде

Для начала мысленно отбросим:

  • союзы и предлоги;
  • знаки препинания;
  • местоимения;
  • общие слова, например «который», «проблема»,«решение», «будет» и так далее.

Из того, что осталось, посмотрим на то, что повторяется чаще остальных. В нашем случае это:

  • руководство,
  • конфликт,
  • унижение,
  • оскорбление,
  • понижение,
  • хамство,
  • нарушение,
  • личность,
  • зарплата.

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

=COUNTA(FILTER(A:A; REGEXMATCH(A:A; "[Рр]уковод")))

Вот что здесь происходит:

  1. Мы сначала отбираем в столбце A все слова, внутри которых есть слово «руковод» — с большой или с маленькой буквы.
  2. Потом мы отфильтровываем совпадения этих слов с тем же столбцом.
  3. В конце — считаем, сколько таких совпадений у нас получилось.

С зарплатой сделаем интереснее — она может быть «ЗП», «Зп», «зп», «зарплата» и «заработная плата». Чтобы найти все эти варианты, используем знак | — он работает как ИЛИ, позволяя выбрать один из предложенных вариантов:

=COUNTA(FILTER(A:A; REGEXMATCH(A:A; "[Зз][Пп]|[Зз]арплат|[Зз]аработн")))

Применим эту формулу для подсчёта выбранных словоформ и посмотрим, что получилось:

Находим самые популярные слова в опросах

Видно, что словоформ в общем виде получилось больше, чем уникальных слов — а всё потому, что мы постарались учесть все варианты написания.

Что дальше

В следующий раз сделаем ещё круче — напишем скрипт на Python и проанализируем ответы с помощью какой-нибудь библиотеки работы с текстом. Заодно сравним точность ответов в таблице в и программе.

Код поиска всех слов:

contributorpw (Александр)

Текст:

Михаил Полянин

Редактор:

Максим Ильяхов

Художник:

Алексей Сухов

Корректор:

Ирина Михеева

Вёрстка:

Кирилл Климентьев

Соцсети:

Виталий Вебер

Любите данные? Посмотрите вот это
Возможно, у вас получится построить карьеру в мире дата-сайенса. Это новое направление, в котором очень нужны люди. Изучите эту сферу и начните карьеру в ИТ: старт — бесплатно, а после обучения — помощь с трудоустройством.
Посмотреть
Любите данные? Посмотрите вот это Любите данные? Посмотрите вот это Любите данные? Посмотрите вот это Любите данные? Посмотрите вот это
Получите ИТ-профессию
В «Яндекс Практикуме» можно стать разработчиком, тестировщиком, аналитиком и менеджером цифровых продуктов. Первая часть обучения всегда бесплатная, чтобы попробовать и найти то, что вам по душе. Дальше — программы трудоустройства.
Начать карьеру в ИТ
Получите ИТ-профессию Получите ИТ-профессию Получите ИТ-профессию Получите ИТ-профессию
medium