Гид по сводным таблицам в Google Sheets и Excel 

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

Разберём на примере. Допустим, у нас есть данные по рассылкам для разных сегментов получателей по разным каналам — по SMS и email (ссылка на Google-таблицу). Посмотрим, какой канал для какого сегмента принёс больше всего конверсий.

Общие правила создания сводных таблиц

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

• у всех столбцов таблиц есть заголовки,
• в таблицах нет объединенных ячеек,
• в таблицах нет скрытых строк или столбцов,
• данные в пределах одного столбца приведены к единому формату: дата, число или валюта.

Если в ходе работы изменяться или добавятся новые данные, необходимо будет внести изменения в исходные таблицы, а затем обновить сводную.

Гид по ВПР в Excel и Google Таблицах

Как работать со сводными таблицами в Google Sheets

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

В главном меню выбираем: «Вставка» — «Создать сводную таблицу». Далее в появившемся окошке «Новая сводная таблица» выбираем место, куда разместим сводную — на новом листе или текущем. 

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

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

Затем в разделе «Значения» добавим поле «Получатели». Нажимаем «ОК».

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

Фильтруем данные

Уберём из таблицы некоторые значения, например, данные по первым двум рассылкам, так как они были тестовые.

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

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

Производим расчеты

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

Посмотрим, рассылка для какого сегмента приносит больше всего конверсий в процентном отношении — рассчитаем CTR.

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

Нажимаем «Enter» и в таблице автоматически появится поле с расчетами.

Чтобы было удобнее читать сводную таблицу, её можно отформатировать. Алгоритм как и у обычных таблиц — через главное меню и меню форматирования. Переименуем название столбца и изменим формат данных на проценты.

Объединяем несколько листов

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

Создадим новый лист и вставим туда формулу

Важно! Чтобы данные в объединенной таблице отображались корректно, исходные таблицы должны иметь одинаковую структуру: должны совпадать названия полей и тип данных. Мы не стали добавлять в объединенную таблицу шапку из второй таблицы.  

Чтобы данные из разных таблиц можно было разделить, добавим идентификатор — сделаем новый столбец с указанием канала: email или sms. Если в ваших исходных таблицах такие идентификаторы есть (или такое разделение не требуется) — этот шаг можно пропустить.

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

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

Как сделать сводную таблицу в Excel

Создание сводной таблицы

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

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

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

Фильтрация сводной таблицы

Если для анализа нужны не все данные из исходной таблицы, некоторые можно исключить. Допустим, у нас рассылки 1 и 2 были тестовые, и их результаты мы исключим из сводной таблицы.

Перенесем «ID рассылки» в раздел «Фильтры». Сверху над таблицей появится выпадающий список. Нажимаем на маленький треугольник — раскроется список «ID рассылки». Убираем там галочки напротив рассылок 1 и 2. 

Данные по первым двум рассылкам уберутся из сводной таблицы, а сама она автоматически пересчитается.

Расчеты в сводной таблице

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

В главном меню нажимаем кнопку «Поля, элементы и наборы», затем «Вычисляемое поле». В появившемся окошке можно сразу задать имя для нового поля, а ниже напишем формулу. 

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

Чтобы легче читать данные, поработаем над оформлением. Двойной щелчок по заголовку столбца откроет окошко «Поле сводной таблицы». Там можно ввести новое имя столбца, а по кнопке «Число… » открыть «Форматирование ячеек» и выбрать нужный формат.

Теперь данные в нашей сводной таблице выглядят значительно удобнее.

Создание сводной диаграммы

Кроме сводных таблиц, в Excel есть интересная функция — создание сводных диаграмм.

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

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

После этого на экране появится уже знакомый редактор для создания сводной таблицы. Выберем нужные нам значения: поставим галочки напротив полей «Сегмент» и «Конверсии». Создается сводная таблица, а на ее основе — диаграмма.

Подытожим

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

Работа со сводными таблицами не ограничивается исходными данными. Можно добавлять вычисляемые поля и рассчитывать новые показатели на основе имеющихся.

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