Магия таблиц на службе общественного мнения
Продолжаем практиковаться с электронными таблицами. Сегодня вместо Экселя попробуем поработать с Гуглотаблицами — у них есть много дополнительных функций, которые нам пригодятся. Если вы пропустили начало нашей практики — держите подборку:
Что делаем
Ситуация: мы провели опрос в интернете — например про увольнения и их причины. Все ответы собрались в одной гуглотаблице, потому что опрос был в гуглоформах. Нам стало интересно, что больше всего беспокоит людей в вопросах увольнения. Самый простой способ это выяснить — посмотреть, какие слова в ответах используются чаще всего, и сделать на основе этого какие-то выводы.
Мы взяли колонку ответов на вопрос:
«Что должно произойти на работе, чтобы вы положили руководителю на стол заявление об увольнении? Вот прямо чтобы уволиться одним днём».
Всего на момент написания статьи у нас 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
)
Эта формула работает так:
- Сначала она разбивает по пробелам все слова в первой колонке
- Потом использует их как основу для запроса с автоматической сортировкой и подсчётом слов.
- Результат выполнения запроса возвращается в формулу и превращается в 2 колонки — со словами и их количеством
Если упростить, то этот запрос обращается к виртуальной базе данных, получает оттуда результат и выводит его в нашу таблицу. Подробнее про запросы и базы можно почитать в статье про связи в базах данных.
Вставим эту формулу в ячейку C1 — она автоматически заполнит словами и их количеством две ближайшие колонки:
Считаем слова в общем виде
Для начала мысленно отбросим:
- союзы и предлоги;
- знаки препинания;
- местоимения;
- общие слова, например «который», «проблема»,«решение», «будет» и так далее.
Из того, что осталось, посмотрим на то, что повторяется чаще остальных. В нашем случае это:
- руководство,
- конфликт,
- унижение,
- оскорбление,
- понижение,
- хамство,
- нарушение,
- личность,
- зарплата.
Чтобы посчитать слова в разных словоформах и смыслах, используем регулярные выражения. С их помощью мы сможем задать основу слова, а система посчитает нам всё, что подходит под эти критерии. Например, если нам нужно найти все слова, которые относятся к «руководству» или «руководителю», то используем такую формулу:
=COUNTA(FILTER(A:A; REGEXMATCH(A:A; "[Рр]уковод")))
Вот что здесь происходит:
- Мы сначала отбираем в столбце A все слова, внутри которых есть слово «руковод» — с большой или с маленькой буквы.
- Потом мы отфильтровываем совпадения этих слов с тем же столбцом.
- В конце — считаем, сколько таких совпадений у нас получилось.
С зарплатой сделаем интереснее — она может быть «ЗП», «Зп», «зп», «зарплата» и «заработная плата». Чтобы найти все эти варианты, используем знак | — он работает как ИЛИ, позволяя выбрать один из предложенных вариантов:
=COUNTA(FILTER(A:A; REGEXMATCH(A:A; "[Зз][Пп]|[Зз]арплат|[Зз]аработн")))
Применим эту формулу для подсчёта выбранных словоформ и посмотрим, что получилось:
Видно, что словоформ в общем виде получилось больше, чем уникальных слов — а всё потому, что мы постарались учесть все варианты написания.
Что дальше
В следующий раз сделаем ещё круче — напишем скрипт на Python и проанализируем ответы с помощью какой-нибудь библиотеки работы с текстом. Заодно сравним точность ответов в таблице в и программе.