Продолжаем изучать сводные таблицы в Excel. В этой статье (Часть 2) разбираем группировку данных, работу с процентами, настройку промежуточных и общих итогов, а также оформление отчётов. Много практических примеров и лайфхаков для анализа больших таблиц.
Сводные таблицы в Excel: Полный гайд (Часть 1) — построение, структура и первые шаги + VBA
Сводные таблицы в Excel: группировка, проценты и итоги (Часть 2) + VBA
Сводные таблицы в Excel: формулы, срезы и диаграммы (Часть 3) + VBA
📊 Сводные таблицы в Excel: Полный гайд (Часть 2)
В первой части мы разобрали основы: что такое сводная таблица, как её создать и какие базовые элементы в ней есть. Сегодня идём дальше.
В этой статье мы подробно рассмотрим:
- как группировать данные в сводной таблице;
- как использовать проценты и относительные вычисления;
- как настраивать промежуточные итоги и общие суммы;
- как управлять макетом и оформлением сводной таблицы.
Эти приёмы позволяют превратить простую сводку в настоящий инструмент аналитики, который даёт ответы на вопросы бизнеса в считанные секунды.
📌 Группировка данных
Одна из самых полезных функций сводных таблиц — группировка. Она позволяет собирать значения по категориям, датам или диапазонам.
🔹 Пример 1: группировка по датам
Допустим, у нас есть таблица с продажами, где даты указаны в столбце «Дата».
- Добавляем «Дата» в строки сводной таблицы.
- Excel автоматически сгруппирует даты по годам, кварталам и месяцам.
- Если нужно изменить уровень, щёлкаем правой кнопкой → «Группировать» и выбираем: дни, месяцы, кварталы, годы.
Это удобно, когда нужно анализировать продажи по месяцам или видеть динамику по кварталам.
🔹 Пример 2: группировка по числам
Если у нас есть данные по возрасту или суммам заказов, мы можем их сгруппировать диапазонами.
Например:
- возрасты 18–25, 26–35, 36–50, 50+;
- суммы покупок: до 1000, от 1000 до 5000, выше 5000.
Для этого:
- Щёлкаем правой кнопкой на поле → «Группировать».
- Указываем минимальное и максимальное значение и шаг.
- Excel создаст интервалы автоматически.
📐 Проценты и относительные вычисления
По умолчанию сводная таблица показывает сумму. Но часто нужны не абсолютные цифры, а доли и проценты.
🔹 Проценты от общей суммы
- Нажимаем на поле в блоке «Значения» → «Параметры поля значения».
- Переходим во вкладку «Дополнительные вычисления».
- Выбираем «% от итога по таблице».
Теперь вместо 25 000 рублей мы увидим «12%».
🔹 Проценты по строкам и столбцам
- % от итога по строке — показывает, какой вклад вносит каждый столбец в строку.
- % от итога по столбцу — показывает, какой вклад вносит строка в каждый столбец.
Пример: если у нас есть города по строкам и категории товаров по столбцам, мы можем увидеть, что в Москве 42% продаж приходится на фрукты.
🔹 Проценты от родительского элемента
Если у нас многоуровневая структура (например: Категория → Наименование), можно показывать долю каждого элемента внутри своей группы.
Пример: «Фрукты» = 100%, а «Яблоки» занимают 35% внутри категории.
📊 Промежуточные итоги и общие суммы
Excel автоматически добавляет общие итоги, но их можно гибко настраивать.
- На вкладке «Конструктор сводной таблицы» → «Промежуточные итоги».
Можно убрать совсем.
Можно показывать сверху или снизу группы. - Там же настраиваются Общие итоги.
Для строк.
Для столбцов.
Либо для обоих сразу.
Совет: если отчёт слишком громоздкий, уберите промежуточные итоги, чтобы упростить восприятие.
🎨 Оформление и макет
Сводная таблица может выглядеть сухо, если её не оформить. Но в Excel есть несколько приёмов:
- Чередование строк.
На вкладке «Конструктор» можно включить чередующиеся строки или столбцы. - Макет отчёта.
Выберите формат:
компактный (все уровни рядом),
табличный (каждый уровень в своей колонке),
структура (с отступами). - Условное форматирование.
Можно подсветить самые большие или маленькие значения, добавить цветовые шкалы.
🧑💻 VBA-пример: автоматическая группировка по месяцам
Иногда полезно сделать автоматизацию.
Sub GroupByMonths()
Dim pt As PivotTable
Set pt = ActiveSheet.PivotTables(1)
pt.PivotFields("Дата").Group _
Start:=DateSerial(2023, 1, 1), _
End:=DateSerial(2023, 12, 31), _
By:=xlMonths
End Sub
Этот макрос автоматически группирует поле «Дата» в активной сводной таблице по месяцам.
📢 Подписка
Если вам полезен этот материал, поддержите проект:
- Подпишитесь на Дзен
А в комментариях напишите: что вам сложнее — проценты или группировка?
📊 Вывод
Сегодня мы разобрали важные возможности сводных таблиц: группировки, проценты, промежуточные итоги и оформление.
В Части 3 мы займёмся ещё более интересным:
- вычисляемыми полями,
- срезами и временными шкалами,
- созданием сводных диаграмм,
- а также автоматизацией работы с отчётами.