Как создать кросс-таблицу с помощью ИИ: полное руководство

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

На какие вопросы отвечает кросс-таблица

Простая сводка по одной переменной показывает лишь общий результат. Например, шестьдесят два процента выбрали вариант А. Это громкий заголовок, который скрывает детали, обычно самые важные.

Кросс-таблица раскрывает этот заголовок изнутри. Она распределяет одну переменную по строкам, другую — по столбцам, а в ячейках показывает количество или проценты. Теперь вы видите, что вариант А выбрали семьдесят восемь процентов одного сегмента и сорок процентов другого.

Этот термин объединяет целое семейство названий. Перекрестная классификация, таблица сопряженности и двухвходовая таблица — все это описывает один и тот же объект. В электронных таблицах инструментом для его создания является PivotTable.

На странице поддержки Microsoft этот инструмент описывается как «мощное средство для вычисления, обобщения и анализа данных». В том же предложении добавляется, что он «позволяет сравнивать данные, выявлять в них закономерности и тренды». Это отличное описание, и ключевое слово здесь — «сравнение».

Что должно быть указано в кросс-таблице для удобства чтения

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

База расчета. Любой процент — это процент от чего-то. В Excel это настраивается явно, а не по умолчанию. Вы можете «отобразить значения как процент от поля», включая «% от общей суммы». Для вложенного макета можно выбрать «% от суммы родительского элемента», указав «родительское поле, которое вы хотите использовать в качестве базы».

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

Количество. Ячейка с показателем восемьдесят процентов имеет совершенно разное значение при n=200 и при n=5. В документации Microsoft описано, как показать оба значения одновременно. Для этого нужно «дважды перетащить элемент в область значений», а затем «настроить параметры „Параметры значений“ и „Дополнительные вычисления“ для каждого из них».

Что вам понадобится перед началом работы

• Один файл экспорта, где каждая строка соответствует респонденту, транзакции или тикету.
• Две переменные, которые вы хотите сопоставить, каждая в своем столбце.
• Решение о направлении расчета процентов, зафиксированное до начала построения.
• Минимальный размер выборки для ячейки, ниже которого вы не будете рассчитывать процент.
• Чистые заголовки. Руководство Microsoft категорично: «убедитесь, что все столбцы имеют заголовки, причем для каждого столбца должна быть одна строка с уникальными непустыми подписями».

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

Как сделать это вручную

Вариант 1: Построить PivotTable

Это стандартный путь. Инструкция Microsoft лаконична: «Выберите Вставка > PivotTable».

Размещение полей происходит автоматически, и о настройках по умолчанию полезно знать. Нечисловые поля «добавляются в область строк». Поля даты и времени попадают «в область столбцов», а числовые поля — «в область значений».

Главное условие — чистая структура. В руководстве указано, что «данные должны быть организованы в табличном формате и не иметь пустых строк или столбцов».

Вариант 2: Подсчет с помощью COUNTIFS в созданной вручную сетке

Запишите названия строк слева, названия столбцов сверху и добавьте формулу COUNTIFS в каждую ячейку.

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

Вариант 3: Сначала сгруппировать данные в инструменте запросов, а затем вставить результат

Агрегируйте данные вне электронной таблицы, затем вставьте аккуратный результат из трех столбцов и преобразуйте его структуру.

Этот метод отлично справляется с большими объемами данных. Однако он скрывает логику расчетов от ваших коллег, что станет проблемой, как только кто-то спросит, как именно определялись границы групп.

В каких случаях ручной способ замедляет работу

Первое препятствие — ловушка типов данных, и она задокументирована. «По умолчанию поля PivotTable, помещенные в область значений, отображаются как СУММА. Если Excel интерпретирует ваши данные как текст, они отображаются как КОЛИЧЕСТВО». На странице также содержится предупреждение: «вот почему так важно не смешивать типы данных в полях значений».

Всего одна случайная текстовая запись в числовом столбце превращает сумму в количество. При этом внешне таблица все еще выглядит правильной.

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

Третье — неактуальность данных. Таблицы PivotTable не обновляются сами по себе. В документации четко сказано: если вы добавили новые данные в источник, «любые PivotTable, построенные на этом источнике данных, необходимо обновить».

Четвертое — проблема малых ячеек. Кросс-таблица с двенадцатью регионами и шестью вариантами ответов содержит семьдесят две ячейки, и во многих из них будет всего по два-три ответа. Расчет процентов для таких ячеек приводит к тому, что опрос выдает выводы, которые не подтвердятся при следующем исследовании.

Как создать кросс-таблицу с помощью Powerdrill Bloom

Шаг 1: Загрузите выгрузку с ответами

Перетащите необработанный файл экспорта в Powerdrill Bloom (одна строка на каждого респондента). Бесплатный тариф поддерживает загрузку файлов Excel, CSV, PDF и документов, поэтому вы можете загрузить файл без оформления платной подписки.

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

Шаг 2: Укажите две переменные, направление расчета и минимальный размер ячейки

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

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

Шаг 3: Сгенерируйте таблицу с одновременным отображением количества и процентов

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

Если таблица предназначена для отчета, тариф Pro позволяет экспортировать данные в полноценные документы Office. На странице AI report generator подробно описан процесс создания текстовых отчетов.

Что это экономит

Задача	Вручную	Из загруженного файла экспорта
Приведение двустрочного заголовка опроса к плоскому виду	Ручное преобразование структуры перед началом работы	Выполняется за тот же проход
Применение единого набора границ интервалов	Определяется заново каждый период	Указывается один раз в запросе
Отображение количества и процентов вместе	Поле перетаскивается дважды и настраивается отдельно	Включается в запрос
Скрытие процентов в малых ячейках	Ручная проверка каждой ячейки	Правило указывается один раз
Обновление после поступления новых ответов	Ручное обновление, о котором легко забыть	Повторный запрос к новому файлу

Рекомендации

Указывайте базу расчета в самой таблице, а не в сноске. Читатель, которому приходится искать значение n, предположит, что оно велико.

Выбирайте только одно направление расчета процентов для одной таблицы. Сетка, в которой смешаны проценты по строкам и столбцам, нечитаема, даже если все расчеты в ячейках верны.

Скрывайте данные вместо округления. Поставить прочерк в ячейке с четырьмя ответами честнее, чем написать «двадцать пять процентов».

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

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

Заключение

Кросс-таблицу легко построить, но в ней легко запутаться при чтении. Само построение выполняется через PivotTable; сложность заключается в трех обозначениях, которые делают числа понятными, а также в границах интервалов, которые никто не фиксирует.

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

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

Часто задаваемые вопросы

Что такое кросс-таблица?

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

Является ли кросс-таблица тем же самым, что и PivotTable?

PivotTable — это функция электронных таблиц, с помощью которой создается кросс-таблица. Microsoft описывает ее как инструмент для вычисления, обобщения и анализа данных, позволяющий сравнивать их и выявлять тренды. Кросс-таблица — это результат сопоставления двух переменных; PivotTable также может создавать макеты других типов.

Стоит ли использовать проценты по строкам или по столбцам?

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

Каков минимальный размер ячейки для расчета процента?

Универсального числа не существует, главное — установить его и применять последовательно. Многие команды скрывают проценты, если в ячейке менее тридцати ответов, и вместо этого показывают абсолютное количество.

Почему моя PivotTable показывает количество вместо суммы?

В документации Microsoft это объясняется напрямую. По умолчанию значения отображаются как СУММА. Если Excel интерпретирует данные как текст, они отображаются как КОЛИЧЕСТВО. Обычно причиной является одна нечисловая запись в столбце, который в остальном состоит из чисел.