Урок 6. Сводные таблицы в Excel

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

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

Чтобы создать сводную таблицу:

1. Выберите диапазон с помощью Ctrl/Cmd + Shift.
2. Выберите «Сводная таблица» во вкладке «Вставка».

3. Выберите место формирования сводной таблицы — существующий или новый лист.

4. В появившейся панели справа выберите поля для добавления в отчёт с помощью чекбоксов.

5. Настройте таблицу. Перетащите поля в нужную область: столбцы, строки или значения. Место поля в списке каждой области определяет порядок, в котором оно будет отображаться в таблице.
6. Проверьте, что сводная таблица свела данные в нужные вам значения. Например, суммировала или посчитала количество.
7. Чтобы изменить значение поля, нажмите его и в открывшемся меню выберите «Параметры значения полей».

8. Выберите операцию, которую нужно использовать для сведения данных в этом поле → нажмите «ОК».

9. Отредактируйте наименования строк и столбцов вручную или через функцию «Поиск и замена»:
• Нажмите Ctrl/Cmd + F (или Ctrl/Cmd + H).
• В поле «Найти» вставьте то, что нужно убрать из названий, а в поле «Заменить на» — вариант замены.
• Если вы хотите убрать только часть названия поля, вы можете вставить в поле «Найти» текст, который нужно убрать из названия, и пробел. А в поле «Заменить на» вставить только пробел.
• Нажмите «Исправить на всё».

Как форматировать сводную таблицу

Вычисляемое поле

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

Например, в двух таблицах средний чек по 1 000 рублей. В сводной он отобразится как 2 000 рублей (сумма). Корректно его рассчитывать, как суммарный доход в сводной, разделённый на количество покупок в ней же. Для этого нужно добавить вычисляемое поле:

1. Откройте вкладку «Анализ сводной таблицы» → «Вычисления» → «Поля, элементы и наборы». Нажмите «Вычисляемое поле».

2. Добавьте название и формулу расчёта для вычисляемого поля.

3. Можно добавить в формулу расчёта функцию «ЕСЛИОШИБКА», которая будет подставлять альтернативные данные при ошибке.

Фильтры

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

Чтобы добавить фильтр в сводную таблицу:

1. Кликните по сводной таблице → справа откроется панель для настройки.
2. С помощью чекбоксов выберите показатели, которые хотите использовать для фильтрации, и перетащите их в область «Фильтры».

3. Нажмите «Обновить».

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

Задание

1. Создайте сводную таблицу на основе вашего отчёта:
• Выделите диапазон отчёта для сведения
• Во вкладке «Вставка» выберите «Сводная таблица»
• Выберите место формирования и поля для добавления в отчёт
• Настройте таблицу, перетащите поля в нужную область — столбцы, строки или значения
• Проверьте, что сводная таблица свела данные в необходимые значения, и отредактируйте наименования строк и столбцов
2. Настройте форматирование для сводной таблицы:
• Откройте вкладку «Анализ сводной таблицы» → «Вычисления» → «Поля, элементы и наборы», нажмите «Вычисляемое поле»
• Добавьте название и формулу расчёта для вычисляемого поля
• В панели для настройки сводной таблицы выберите показатели, которые хотите использовать для фильтрации, и перетащите их в область «Фильтры»
• Настройте условное форматирование с помощью цветовых шкал

Выводы

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

В следующем блоке изучим, как работать с шаблоном кампаний в Директе.