Сводные таблицы позволяют быстро обработать данные из разных таблиц, привести их к удобному виду. С помощью этого инструмента рассчитывают средние и итоговые значения, группируют и фильтруют данные, не затрагивая при этом исходные.
Разберём на примере. Допустим, у нас есть данные по рассылкам для разных сегментов получателей по разным каналам — по SMS и email (ссылка на Google-таблицу). Посмотрим, какой канал для какого сегмента принёс больше всего конверсий.
Перед тем как начать работу, в исходных таблицах нужно подготовить данные для анализа. Убедитесь, что:
Если в ходе работы изменяться или добавятся новые данные, необходимо будет внести изменения в исходные таблицы, а затем обновить сводную.
В главном меню выбираем: «Вставка» — «Создать сводную таблицу». Далее в появившемся окошке «Новая сводная таблица» выбираем место, куда разместим сводную — на новом листе или текущем.
Далее переходим к сводной таблице. В «Редакторе сводной таблицы» выбираем, какие данные хотим видеть. Добавляем данные, которые будут показаны в строках, столбцах и ячейках — с помощью зеленой кнопки «Добавить» в соответствующих разделах.
Например, мы хотим увидеть общее количество получателей рассылки из каждого сегмента. Для этого в редакторе в разделе «Строки» нажимаем «Добавить» и выбираем «Сегмент».
Затем в разделе «Значения» добавим поле «Получатели». Нажимаем «ОК».
У нас получилась таблица, где в каждой строке — сегмент получателей, а напротив автоматически появилась сумма количества из столбца «Получатели» исходной таблицы.
Уберём из таблицы некоторые значения, например, данные по первым двум рассылкам, так как они были тестовые.
В редакторе сводной таблицы в разделе «Фильтры» нажмём «Добавить» и выберем «ID рассылки». Нажимаем на выпадающий список «Показаны все объекты» и в меню фильтрации выбираем «Фильтровать по значению». Снимаем галочки с первых двух рассылок. Нажимаем «ОК».
После этого из нашей сводной таблицы автоматически уберутся данные по первым двум рассылкам, а сумма в таблице пересчитается.
Кроме отображения данных из исходных таблиц, в сводных можно делать собственные расчеты и получать новые данные.
Посмотрим, рассылка для какого сегмента приносит больше всего конверсий в процентном отношении — рассчитаем CTR.
В разделе «Значения» нажмём зелёную кнопку «Добавить» и выберем «Рассчитываемое поле». В поле «Формула» пишем заголовки столбцов, с которыми будем проводить действия:
Нажимаем «Enter» и в таблице автоматически появится поле с расчетами.
Чтобы было удобнее читать сводную таблицу, её можно отформатировать. Алгоритм как и у обычных таблиц — через главное меню и меню форматирования. Переименуем название столбца и изменим формат данных на проценты.
По умолчанию сводная таблица строится на основе одной таблицы. Если исходные данные располагаются на нескольких листах, сначала их нужно объединить. Проще всего сделать это с помощью массивов:
Создадим новый лист и вставим туда формулу
Важно! Чтобы данные в объединенной таблице отображались корректно, исходные таблицы должны иметь одинаковую структуру: должны совпадать названия полей и тип данных. Мы не стали добавлять в объединенную таблицу шапку из второй таблицы.
Чтобы данные из разных таблиц можно было разделить, добавим идентификатор — сделаем новый столбец с указанием канала: email или sms. Если в ваших исходных таблицах такие идентификаторы есть (или такое разделение не требуется) — этот шаг можно пропустить.
Сделаем из этих данных сводную таблицу. Нажимаем в главном меню «Вставка» — «Создать сводную таблицу». Вставим сводную на новый лист. Выберем для отображения в строках «Сегменты», в столбцах — «Каналы», а в значениях — «Конверсии». Чтобы информация лучше читалась, изменим отображение в ячейках на проценты.
Получим простую сводную таблицу для анализа конверсий по сегментам и каналам.
Чтобы добавить сводную таблицу в Excel, нужно в главном меню на вкладке «Вставка» нажать кнопку «Сводная таблица». Появится окно, где мы указываем исходную таблицу и место расположения сводной, нажимаем «ОК».
После этого у нас в главном меню откроется вкладка «Анализ сводной таблицы», а в правой части экрана появится конструктор, где мы будем собирать сводную.
Сделаем таблицу, которая покажет нам общее количество получателей рассылки из каждого сегмента. Выбираем поля, которые необходимо показать в таблице — «Сегмент» и «Получатели», ставим напротив них галочки. Готово: в сводной таблице появились колонки с суммой этих данных.
Если для анализа нужны не все данные из исходной таблицы, некоторые можно исключить. Допустим, у нас рассылки 1 и 2 были тестовые, и их результаты мы исключим из сводной таблицы.
Перенесем «ID рассылки» в раздел «Фильтры». Сверху над таблицей появится выпадающий список. Нажимаем на маленький треугольник — раскроется список «ID рассылки». Убираем там галочки напротив рассылок 1 и 2.
Данные по первым двум рассылкам уберутся из сводной таблицы, а сама она автоматически пересчитается.
Кроме полей из исходной таблицы, в сводную можно добавить собственные поля. Например, добавим в таблицу поле с CTR и рассчитаем его.
В главном меню нажимаем кнопку «Поля, элементы и наборы», затем «Вычисляемое поле». В появившемся окошке можно сразу задать имя для нового поля, а ниже напишем формулу.
В формуле используем названия полей сводной таблицы. В нашем случае это будет выглядеть так:
Чтобы легче читать данные, поработаем над оформлением. Двойной щелчок по заголовку столбца откроет окошко «Поле сводной таблицы». Там можно ввести новое имя столбца, а по кнопке «Число… » открыть «Форматирование ячеек» и выбрать нужный формат.
Теперь данные в нашей сводной таблице выглядят значительно удобнее.
Кроме сводных таблиц, в Excel есть интересная функция — создание сводных диаграмм.
Построим диаграмму, наглядно показывающую общее количество конверсий для разных сегментов.
Выделяем ячейку таблицы, на основе которой будем строить диаграмму и нажимаем кнопку «Сводная диаграмма». Откроется уже знакомое нам окошко с выбором исходной таблицы и места расположения сводной.
После этого на экране появится уже знакомый редактор для создания сводной таблицы. Выберем нужные нам значения: поставим галочки напротив полей «Сегмент» и «Конверсии». Создается сводная таблица, а на ее основе — диаграмма.
Сводные таблицы — удобный и несложный инструмент для анализа данных. Он позволяет получать средние и итоговые значения, фильтровать и группировать данные для наглядного отображения — и на основе этого принимать необходимые решения.
Работа со сводными таблицами не ограничивается исходными данными. Можно добавлять вычисляемые поля и рассчитывать новые показатели на основе имеющихся.
Перед тем, как создавать сводные таблицы, важно подготовить данные: проверить наличие заголовков столбцов, отсутствие объединенных ячеек и скрытых строк. При построении сводных из нескольких листов у них должна быть одинаковая структура, одинаковые названия полей и тип данных.
Делимся новостями и свежими статьями, рассказываем о новинках сервиса
Искренние письма о работе и жизни. Свежие статьи из блога. Эксклюзивные кейсы и интервью с экспертами диджитала.
