Поворот, построение и анализ данных опроса Excel

Question

У меня есть набор данных из опроса форм Google. Я столкнулся с проблемой, представляющей данные для определенного типа вопроса, точнее, сетки.

Так, например, вы получите такой вопрос :

и Google собирает все данные в электронную таблицу следующим образом:

Затем я загружаю его в формате .xlsx, чтобы разобраться во всем этом. Попытка развернуть его - это не то, чего я точно ожидаю, так как он просто считает "да" и "нет" из первого столбца и копирует это во второй и третий.

Я получаю что-то вроде этого:

Я потратил некоторое время на то, чтобы смотреть онлайн и пытаться выяснить сводные таблицы, и по большей части я могу использовать его просто отлично, но это доставляет мне проблемы. Я нашел способ использовать функцию Excel findif() для создания новой таблицы из необработанных данных, а затем манипулировать ею, но этот подход выглядит так, что он подвержен человеческим ошибкам.

Вот результат и то, что я хотел бы получить из необработанных данных без ручной обработки данных:

Чтобы лучше понять мою проблему, вы можете скачать ее или посмотреть электронную таблицу в Интернете.

Answer 1

Это более надежная формула по сравнению с исходной, так как она действительно суммирует столбцы и переменные, которые упоминаются в заголовке и первом столбце, вы не можете изменить их по ошибке. Наихудший случай может произойти, если вы наберете что-то неправильно, что вы получите ошибку или 0.

=COUNTIF(INDEX(Tabela2[#All],0,MATCH($E2,Tabela2[#Headers],0)),F$1) , где:

• MATCH($E2,Tabela2[#Headers],0)) - ищет столбец исходной таблицы, указанный в первом столбце
• INDEX(Tabela2[#All],0,MATCH(...)) - получить адрес этого найденного столбца
• =COUNTIF(INDEX(...),F$1) - подсчитать количество появлений данных заголовка в столбце.

Answer 2

Как упомянул @ mátéjuhász, сводные таблицы не очень хорошо работают с данными, которые уже "сводятся". Вам нужно будет сплющить таблицу, чтобы в ней были столбцы: question; Ответьте, чтобы заставить сводную таблицу работать так, как вы хотите.

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

В каждой ячейке таблицы есть следующая формула: COUNTIF(<name_of_your_table>[<column_title>];[@answer]) (замените <...> правильными значениями). Например, в столбце покупки у меня есть COUNTIF(Tabela2[purchase];[@answer])