Вопросы и шаблоны · 21 июля 2026 г.

Обработка данных опроса в Excel: сводные таблицы по шагам

Чтобы обработать данные опроса в Excel, выгрузите ответы в CSV с одной строкой на респондента и одним столбцом на вопрос, постройте сводную таблицу (Вставка → Сводная таблица), перетащите вопрос в область строк, а любое поле — в область значений с операцией «Количество», и добавьте второй раз то же поле как «% от общей суммы». За минуту вы получите частоты и проценты по любому закрытому вопросу. Ниже — этот путь по шагам, формулы СЧЁТЕСЛИ и ЧАСТОТА для случаев, где сводная неудобна, отдельный разбор множественного выбора, который приходит в одну ячейку через запятую, и честный ответ на вопрос, где Excel перестаёт справляться.

Шаг 0. Как должна выглядеть выгрузка

Половина проблем с обработкой опроса в Excel начинается до Excel — в момент выгрузки. Правильный формат называется «плоская таблица»: одна строка — один респондент, один столбец — один вопрос, в шапке короткие названия столбцов, никаких объединённых ячеек и подзаголовков. Такую таблицу понимает сводная. Если конструктор отдаёт вам файл, где первые три строки — логотип и название опроса, а вопросы разбиты на группы объединёнными ячейками, — первое, что нужно сделать, это всё это удалить, оставив одну строку заголовков сверху.

Отдельная боль — CSV и русская локаль. При открытии CSV двойным кликом Excel часто разваливает файл в один столбец, потому что ожидает точку с запятой, а в файле запятые. Не боритесь с этим вручную: используйте Данные → Из текста/CSV, там явно указывается разделитель и кодировка UTF-8. Вторая типовая беда — даты и числа, которые Excel «умно» преобразует: код тарифа «1-2» превращается в дату, а «05» теряет ноль. Если у вас есть такие поля, при импорте задайте им тип «Текст», а не «Общий». Пять минут на аккуратный импорт экономят час на разгребание последствий.

1. Выгрузите ответы в CSV или XLSX: одна строка — один респондент.
2. Импортируйте через Данные → Из текста/CSV, укажите UTF-8 и правильный разделитель.
3. Удалите декоративные строки сверху, оставьте один ряд заголовков.
4. Переименуйте столбцы в короткие («Тариф», «NPS», «Сроки»), полные тексты вопросов держите в отдельном листе-словаре.
5. Проверьте, что числовые ответы распознались числами, а не текстом (по умолчанию они прижимаются вправо).
6. Преобразуйте диапазон в таблицу (Ctrl+T) — тогда сводная не поедет при добавлении новых ответов.

Шаг 1. Сводная таблица за одну минуту

Сводная таблица — основной инструмент, и для типового опроса она закрывает 80% работы. Встаньте на любую ячейку данных, Вставка → Сводная таблица, поместите её на новый лист. Дальше механика простая: поле вопроса перетаскиваете в область «Строки», и то же самое поле — в область «Значения». Excel по умолчанию поставит «Количество» для текстовых полей, и вы сразу получите частоты: сколько человек выбрали каждый вариант. Если поле числовое, Excel предложит «Сумма» — переключите на «Количество» через Параметры поля значений.

Проценты добавляются вторым перетаскиванием: бросьте то же поле в «Значения» ещё раз, откройте Параметры поля значений → Дополнительные вычисления → «% от общей суммы». Теперь рядом с абсолютными числами стоят доли, и таблицу можно вставлять в отчёт как есть. Не считайте проценты руками формулой рядом со сводной — при обновлении данных сводная меняет размер, и ваши формулы разъедутся. Это классическая ловушка: отчёт выглядит правильным, а проценты посчитаны от вчерашнего количества строк.

Шаг 2. Разбивка по сегментам: два поля в сводной

Самое ценное в сводной — то, ради чего она вообще нужна: разбивка одного вопроса по другому. Перетащите вопрос в «Строки», а сегмент (тариф, тип клиента, канал) в «Столбцы» — и получите таблицу пересечения. Дальше важный нюанс: по умолчанию «% от общей суммы» посчитает долю от всех 180 респондентов, а вам почти всегда нужен процент внутри сегмента. Выберите «% от суммы по столбцу» — тогда каждый столбец даст в сумме 100%, и вы сможете честно сравнить, как отвечают базовые и про-клиенты.

Это то место, где чаще всего ошибаются. Если посчитать «% от общей суммы» в разбивке, получится, что «5 поставили 18% базовых и 12% про-клиентов» — и кажется, что базовые довольнее. Но базовых в выборке вдвое больше, поэтому цифры несравнимы: они отражают размер сегмента, а не удовлетворённость. Пересчёт по столбцу даёт «5 поставили 27% базовых и 36% про-клиентов» — вывод меняется на противоположный. Один неверный клик в настройке — и отчёт говорит обратное тому, что в данных.

Шаг 3. СЧЁТЕСЛИ и СЧЁТЕСЛИМН, когда сводная неудобна

Сводная хороша для разведки, но плоха, когда нужно собрать фиксированный отчёт, который обновляется при добавлении новых ответов. Здесь работают формулы. СЧЁТЕСЛИ(диапазон; критерий) считает, сколько раз встретилось значение: =СЧЁТЕСЛИ(D:D;5) даст количество пятёрок. СЧЁТЕСЛИМН добавляет условия: =СЧЁТЕСЛИМН(D:D;5;C:C;"Про") посчитает пятёрки только среди клиентов на тарифе «Про». Для процента делите на количество ответивших именно на этот вопрос — =СЧЁТ(D:D), а не на общее число строк, иначе пропуски занизят все доли.

Функция ЧАСТОТА нужна для другого случая — когда ответ числовой и непрерывный: возраст, сумма чека, стаж. Она разносит значения по интервалам: задаёте массив границ (например, 25, 35, 45, 55) и вводите =ЧАСТОТА(диапазон_данных; массив_границ) как формулу массива. На выходе — сколько человек попало в каждый интервал, то есть готовая гистограмма. Для оценок по шкале 1–5 ЧАСТОТА избыточна: там значений всего пять, и СЧЁТЕСЛИ проще. А вот для возраста она незаменима — иначе вы получите таблицу из шестидесяти строк по одному человеку в каждой.

Шаг 4. Множественный выбор — главная головная боль

Вот здесь начинается настоящая работа. Конструкторы опросов часто выгружают вопрос с множественным выбором в одну ячейку через запятую: «Доставка, Цена, Поддержка». Сводная по такому столбцу построит частоту по уникальным комбинациям — и вы увидите строки вроде «Доставка, Цена» — 7 человек, «Доставка, Поддержка» — 5, «Доставка» — 12. Это бесполезно: комбинаций десятки, каждая по два-три человека, а сколько всего людей упомянули доставку — неизвестно.

Решение — развернуть один столбец в несколько бинарных: по одному столбцу на вариант, где 1 означает «выбрал», 0 — «не выбрал». Делается это формулой =ЕСЛИ(ЕЧИСЛО(ПОИСК("Доставка";$F2));1;0), протянутой на все строки, и повторяется для каждого варианта. Дальше всё просто: сумма по столбцу — число упоминаний варианта, деление на число ответивших — доля респондентов. Обратите внимание на ПОИСК, а не НАЙТИ: первый не различает регистр, что здесь удобнее.

1. Создайте по одному столбцу на каждый вариант ответа, в шапке — точный текст варианта.
2. В первой строке формула =ЕСЛИ(ЕЧИСЛО(ПОИСК("Доставка";$F2));1;0), протяните вниз и вправо.
3. Проверьте варианты, которые являются подстроками других, — их считайте отдельно.
4. Просуммируйте каждый столбец: получите число упоминаний по каждому варианту.
5. Разделите на число ответивших на вопрос — получите доли респондентов.
6. В отчёте подпишите: сумма долей превышает 100%, потому что можно было выбрать несколько.

Пример: считаем NPS в Excel

NPS — хороший пример полного расчёта, потому что в нём есть все типовые ловушки. Респонденты ставят оценку от 0 до 10. Промоутеры — 9 и 10, нейтралы — 7 и 8, критики — от 0 до 6. Индекс = доля промоутеров минус доля критиков, в процентных пунктах. Формулы: =СЧЁТЕСЛИ(E:E;">=9")/СЧЁТ(E:E) для доли промоутеров, =СЧЁТЕСЛИ(E:E;"<=6")/СЧЁТ(E:E) для доли критиков, разность умножаем на 100. Обратите внимание на знаменатель: СЧЁТ(E:E), то есть число ответивших на вопрос, а не число строк в файле. Если вопрос был необязательным, разница будет заметной.

Первая ловушка — пустые ячейки. СЧЁТЕСЛИ с условием «<=6» пустоту не считает, а вот если кто-то выгрузил пропуски нулями, все они станут критиками и уронят индекс. Проверьте это до расчёта: =СЧЁТЕСЛИ(E:E;0) покажет, сколько у вас нулей, и если их подозрительно много, это пропуски, а не крайне недовольные. Вторая — текстовые числа: если оценки импортировались как текст, СЧЁТЕСЛИ со сравнением «>=9» вернёт ноль, и вы получите NPS, состоящий из одних критиков. Признак — значения прижаты влево. Третья, самая частая: показать NPS без n. Индекс 45 при 180 ответах и индекс 45 при 20 ответах — это разные утверждения, и второе не стоит показывать вовсе.

Шаг 5. Чистка данных: что удалять, а что нет

Перед подсчётами данные надо просмотреть, но без фанатизма. Реальные кандидаты на исключение — те, кто прошёл анкету за 15 секунд при заявленных трёх минутах (физически не читал вопросы), и те, кто поставил одинаковую оценку всем пунктам матрицы, включая перевёрнутые по смыслу утверждения. Это не придирка: такие ответы добавляют шум, не неся сигнала. Но фиксируйте, сколько строк вы удалили и почему, — это идёт в раздел ограничений отчёта.

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

• Удаляйте: тестовые прохождения свои и коллег, дубли одного человека, ответы быстрее физического минимума.
• Помечайте, но не удаляйте: прямолинейщиков (одна оценка на всё) — сначала посчитайте, сколько их.
• Не удаляйте: крайние мнения, пустые открытые ответы, «затрудняюсь ответить» — это данные.
• Всегда записывайте: сколько строк исключено, по какому правилу, и что осталось в итоге.

Где Excel перестаёт справляться

Честно: Excel отлично считает закрытые вопросы на выборке в несколько сотен человек, и для большинства бизнес-опросов этого достаточно. Но у него есть три ясные границы. Первая — открытые ответы. Excel не умеет группировать текст по смыслу: вам придётся читать двести реплик и вручную проставлять коды в соседнем столбце. Это работа на несколько часов, и она не масштабируется — при тысяче ответов вы просто не станете её делать, а значит, открытые вопросы останутся непрочитанными, что хуже, чем если бы вы их не задавали.

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

Практический вывод не в том, что Excel плох, а в том, где проходит его граница. Если у вас один-два опроса в квартал по 150–300 ответов с закрытыми вопросами — Excel закроет всё, и никакие инструменты не нужны. Если у вас регулярные замеры с открытыми вопросами и разбивкой по многим сегментам, обработка в Excel съест больше времени, чем сам сбор данных, и главный риск даже не в трудозатратах, а в том, что вы начнёте срезать углы: не читать открытые ответы, не считать n по сегментам, копировать прошлую методику по памяти.

Когда обработку берёт на себя инструмент

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

И последнее, что стоит держать в голове при любой обработке: Excel посчитает всё, что вы ему скажете, включая бессмыслицу. Он не предупредит, что процент посчитан от 12 человек, что сумма по множественному выбору не должна равняться 100%, что среднее по шкале скрывает поляризацию. Проверка на здравый смысл — не функция таблицы. Это ваша работа, и она начинается ровно там, где заканчиваются формулы.