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

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

Анализ данных опроса в Excel

Часть 1: Подсчет всех видов отзывов в опросе

Часть 2: Расчет процентного соотношения всех отзывов

Часть 3: Создание отчета об опросе с использованием рассчитанных выше результатов

Часть 1: Подсчет всех видов отзывов в опросе

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

1. Выберите пустую ячейку, например, Ячейку B53, введите эту формулу =COUNTBLANK(B2:B51) (диапазон B2:B51 — это диапазон отзывов на вопрос 1, вы можете изменить его по мере необходимости), и нажмите кнопку Enter на клавиатуре. Затем перетащите маркер заполнения в нужный диапазон, здесь я заполняю его до диапазона B53:K53. См. скриншот:

2. В Ячейке B54 введите эту формулу =COUNTA(B2:B51) (диапазон B2:B51 — это диапазон отзывов на вопрос 1, вы можете изменить его по мере необходимости), и нажмите кнопку Enter на клавиатуре. Затем перетащите маркер заполнения в нужный диапазон, здесь я заполняю его до диапазона B54:K54. См. скриншот:

3. В Ячейке B55 введите эту формулу =SUM(B53:B54) (диапазон B2:B51 — это диапазон отзывов на вопрос 1, вы можете изменить его по мере необходимости), затем нажмите кнопку Enter на клавиатуре, а затем перетащите маркер заполнения в нужный диапазон, здесь я заполняю его до диапазона B55:K55. См. скриншот:

Затем подсчитайте количество каждого из вариантов «Сильно согласен», «Согласен», «Не согласен» и «Сильно не согласен» для каждого вопроса.

4. В Ячейке B57 введите эту формулу =COUNTIF(B2:B51,$B$51) (диапазон B2:B51 — это диапазон отзывов на вопрос 1, ячейка $B$51 — это критерий, который вы хотите подсчитать, вы можете изменить их по мере необходимости), затем нажмите кнопку Enter на клавиатуре, а затем перетащите маркер заполнения в нужный диапазон, здесь я заполняю его до диапазона B57:K57. См. скриншот:

5. Введите =COUNTIF(B2:B51,$B$11) (диапазон B2:B51 — это диапазон отзывов на вопрос 1, ячейка $B$11 — это критерий, который вы хотите подсчитать, вы можете изменить их по мере необходимости) в ячейку B58, и нажмите кнопку Enter на клавиатуре, затем перетащите маркер заполнения в нужный диапазон, здесь я заполняю его до диапазона B58:K58. См. скриншот:

6. Повторите шаг 5 или 6, чтобы подсчитать количество каждого отзыва на каждый вопрос. См. скриншот:

7. В Ячейке B61 введите эту формулу =SUM(B57:B60) (диапазон B2:B51 — это диапазон отзывов на вопрос 1, вы можете изменить его по мере необходимости), чтобы суммировать общее количество отзывов, и нажмите кнопку Enter на клавиатуре, затем перетащите маркер заполнения в нужный диапазон, здесь я заполняю его до диапазона B61:K61. См. скриншот:

Часть 2: Расчет процентного соотношения всех отзывов

Теперь вам нужно рассчитать процентное соотношение каждого отзыва на каждый вопрос.

8. В Ячейке B62 введите эту формулу =B57/$B$61 (Ячейка B57 указывает на конкретный отзыв, количество которого вы хотите подсчитать, Ячейка $B$61 представляет собой общее количество отзывов, вы можете изменить их по мере необходимости), чтобы суммировать общее количество отзывов и нажмите кнопку Enter на клавиатуре, затем перетащите маркер заполнения в нужный диапазон. Затем отформатируйте ячейку как процент, щелкнув правой кнопкой мыши > Формат ячеек > Процент. См. скриншот:

Вы также можете отобразить эти результаты в процентах, выбрав их и нажав % (Процентный стиль) в группе Число на вкладке Главная.

9. Повторите шаг 8, чтобы рассчитать процентное соотношение каждого отзыва в каждом вопросе. См. скриншот:

Часть 3: Создание отчета об опросе с использованием рассчитанных выше результатов

Теперь вы можете составить отчет о результатах опроса.

10. Выберите заголовки столбцов опроса (A1:K1 в данном случае), щелкните правой кнопкой мыши > Копировать, а затем вставьте их в другой пустой лист, щелкнув правой кнопкой мыши > Транспонировать (T). См. скриншот:

Если вы используете Microsoft Excel 2007, вы можете вставить эти рассчитанные проценты, выбрав пустую ячейку, а затем нажав Главная > Вставить > Транспонировать. См. следующий скриншот:

11. Отредактируйте заголовок по своему усмотрению, см. скриншот:

12. Выберите часть, которую необходимо отобразить в отчете, щелкните правой кнопкой мыши > Копировать, а затем перейдите на лист, куда нужно вставить данные, и выберите одну пустую ячейку, например, Ячейку B2, нажмите Главная > Вставить > Специальная вставка. См. скриншот:

13. В диалоговом окне Специальная вставка отметьте Значения и Транспонировать в разделах Вставить и Транспонировать, и нажмите OK, чтобы закрыть это диалоговое окно. См. скриншот:

Повторите шаги 10 и 11, чтобы скопировать и вставить необходимые данные, и тогда отчет об опросе будет создан. См. скриншот: