Методика · 5 ноября 2026 г.

Как в Excel посчитать ответы опроса по категориям

Чтобы посчитать ответы опроса по категориям в Excel, расположите каждый ответ отдельной строкой, выделите столбец с нужным вопросом и посчитайте варианты формулой СЧЁТЕСЛИ или через сводную таблицу. Перед расчётом проверьте пропуски, одинаковость написания вариантов и правило подсчёта вопросов с несколькими ответами. Для долей выберите подходящее основание: число ответивших на вопрос или все заполненные анкеты. На условном примере опроса сотрудников склада разберём оба способа, проверку итогов и оформление результата для коллег. Таблицу можно повторить на своей выгрузке, изменив диапазон и категории.

Подготовьте исходную таблицу

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

Представим, что сотрудники склада ответили на вопрос «Понятно ли, к кому обратиться при проблеме с оборудованием?». Предусмотрены варианты «Да», «Не всегда», «Нет» и «Не сталкивался». В рабочей копии каждая ячейка столбца содержит выбранный вариант, а пустая ячейка обозначает пропуск. Это условный пример, его числа нужны для объяснения расчёта и не описывают результаты реального склада. Дальше мы считаем распределение по одному вопросу; те же действия применимы к другим вопросам с одним вариантом ответа.

Проверьте категории и пропуски

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

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

Посчитайте варианты через СЧЁТЕСЛИ

Допустим, ответы находятся в ячейках B2:B23, а список категорий расположен в D2:D5. В E2 введите формулу =СЧЁТЕСЛИ(ДВССЫЛ("B2:B23");D2). Она проверит ячейки указанного диапазона и вернёт число тех, которые совпадают с категорией из D2. Скопируйте формулу для остальных категорий: ДВССЫЛ сохраняет диапазон ответов при копировании, а ссылка на категорию меняется в каждой строке. Для английской локализации функции и разделители могут выглядеть иначе. Важно брать диапазон из своего файла: границы B2:B23 здесь приведены для условного примера.

1. Скопируйте исходную выгрузку на отдельный рабочий лист.
2. В соседнем пустом месте перечислите все предусмотренные варианты вопроса.
3. Введите рядом с первым вариантом формулу СЧЁТЕСЛИ с диапазоном ответов и ссылкой на категорию.
4. Скопируйте формулу для остальных категорий и проверьте диапазон ответов в каждой строке.
5. Сложите полученные количества и отдельно посчитайте пропуски.
6. Сверьте сумму категорий и пропусков с числом анкет.

В условной таблице результаты для четырёх категорий составят 12, 5, 2 и 1. Сумма равна 20, а с двумя пропусками получается 22 строки анкет. Такое совпадение показывает арифметическую согласованность для вопроса с одним ответом. Оно ещё не доказывает, что все данные загружены верно: строки могли потеряться до расчёта, а лишние анкеты — попасть в выгрузку. Поэтому сравните число строк с доступным итогом сбора и проверьте фильтры. Если итог расходится, разберите разницу до построения графиков.

Сделайте сводную таблицу

Сводная таблица удобна, когда категорий много или нужно быстро посмотреть распределение по нескольким отдельным вопросам. Выделите любую ячейку внутри подготовленного диапазона и выберите на вкладке «Вставка» команду создания сводной таблицы. Проверьте предложенный диапазон и создайте таблицу на новом листе. В списке полей перетащите название нужного вопроса в область строк и добавьте то же поле в область значений. Для текстовых ответов выберите подсчёт количества, если программа предложила другую операцию. Названия кнопок могут различаться в зависимости от версии Excel.

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

Рассчитайте доли и назовите основание

Число ответов показывает масштаб, доля помогает читать распределение. Выберите основание до расчёта и напишите его рядом с результатом. В примере 12 человек выбрали «Да» из 20 ответивших на вопрос: доля равна 12/20, или 60 процентам. Если разделить те же 12 на все 22 анкеты, получится около 54,5 процента. Оба вычисления арифметически допустимы, но отвечают на разные вопросы. Первый результат характеризует ответы на конкретный пункт, второй показывает долю среди всех анкет, включая пропуски.

Для доли от ответивших можно разделить число категории в E2 на сумму заполненных категорий: =E2/СУММ(ДВССЫЛ("E2:E5")). Установите процентный формат ячейки. Скопируйте формулу для остальных строк и проверьте, что ДВССЫЛ сохраняет диапазон суммы категорий. Если знаменатель равен нулю, долю рассчитывать нельзя: укажите, что ответов на вопрос нет, и проверьте выгрузку. Округление иногда даёт сумму чуть больше или меньше ста процентов, поэтому полезно хранить исходные числа. Для небольшой группы рядом с процентом особенно важно показывать количество, чтобы читатель видел, сколько ответов стоит за долей.

Учтите вопрос с несколькими вариантами

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

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

Как обработать открытые ответы

Свободные комментарии сначала нужно прочитать и объединить по смыслу. Составьте перечень тем, который отвечает задаче исследования: например, доступность инструмента, передача информации и возможность сделать перерыв. Возьмите несколько ответов, проверьте, хватает ли этих тем, и уточните определения. К одному комментарию может относиться несколько тем; заранее решите, допускаете ли вы множественную разметку. После этого отмечайте темы отдельными признаками и считайте их так же, как варианты множественного выбора. Подсчёт показывает частоту упоминаний в ответах, но не объясняет причины сам по себе.

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

Проверьте результат перед отчётом

• Число строк в рабочем файле согласуется с выгрузкой, а фильтры не скрывают часть анкет.
• Диапазон формулы охватывает все нужные строки и не захватывает заголовок или итог.
• Категории записаны одинаково, содержательные варианты сохранены отдельно от пропусков.
• Для каждого вопроса указано число ответивших и объяснено основание долей.
• Для множественного выбора отдельно подписано, что сумма отметок может быть больше числа людей.
• Сводная таблица обновлена после изменений, а источник включает новые строки.
• В отчёте нет мелких групп и комментариев, по которым можно определить человека.

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

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

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

Рабочий файл удобно разделить на листы: копия выгрузки, подготовленные сведения, расчёты и итоговая таблица. В названиях столбцов сохраните связь с исходными вопросами, а рядом с вычислениями поясните правила обработки пропусков и множественных ответов. При передаче файла коллеге не удаляйте формулы и не заменяйте все результаты значениями, пока требуется проверка. Если отчёт идёт широкой аудитории, подготовьте отдельную сводку без исходных строк и персональных подробностей. Доступ к рабочей книге ограничьте кругом людей, которым он нужен для обработки.

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

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

Как представить результаты сотрудникам

Покажите формулировку вопроса, число анкет, число ответивших на него и распределение категорий. Рядом укажите число пропусков. Для условного примера корректная подпись выглядит так: «Ответили 20 человек из 22; вариант „Да“ выбрали 12 из 20 ответивших». Числа здесь приведены исключительно для демонстрации. Не описывайте такое распределение как мнение всех сотрудников склада, если неизвестно, кто не получил приглашение или решил не участвовать. Количество и доля ответов не устраняют это ограничение.

После таблицы сформулируйте решение, которое команда готова проверить: например, уточнить порядок обращения по неисправности и сообщить сотрудникам понятный контакт. Назначьте ответственного и дату, когда вернётесь к результату проверки. В сервисе «До Сути» можно собрать ответы через форму; выгружая их для расчёта в Excel, сохраняйте исходный файл и проверяйте структуру данных. Подсчёт категорий помогает увидеть распределение ответов, а вывод о причине трудностей потребует дополнительных сведений и разговора с участниками, готовыми к нему добровольно.