Сводные таблицы в Excel: формулы, срезы и диаграммы (Часть 3) + VBA

Завершаем цикл статей о сводных таблицах. В Части 3 разбираем вычисляемые поля, срезы и временные шкалы, создание сводных диаграмм и автоматизацию с помощью VBA. Полный гайд для анализа данных в Excel.

Сводные таблицы в Excel: Полный гайд (Часть 1) — построение, структура и первые шаги + VBA

Сводные таблицы в Excel: группировка, проценты и итоги (Часть 2) + VBA

Сводные таблицы в Excel: формулы, срезы и диаграммы (Часть 3) + VBA

📊 Сводные таблицы в Excel: Полный гайд (Часть 3)

Мы подошли к финалу большого цикла по сводным таблицам.

В первой статье мы научились их строить с нуля, во второй — освоили группировки и проценты.

Сегодня рассмотрим ещё более мощные возможности:

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

🔢 Вычисляемые поля и элементы

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

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

Это формула, которая работает внутри сводной таблицы.

Пример: у нас есть данные «Доход» и «Расход». Нам нужно посчитать «Прибыль».

1. Выбираем сводную таблицу.
2. На вкладке «Анализ сводной таблицы» → «Поля, элементы, наборы» → «Вычисляемое поле».
3. Вводим название: «Прибыль».
4. В поле формулы пишем: = Доход - Расход.

Excel создаёт новое поле, и теперь мы видим прибыль по каждому городу или категории.

🔹 Вычисляемый элемент

Используется, если мы хотим создать новый элемент внутри поля.

Пример: у нас есть категории «Фрукты» и «Овощи». Мы хотим видеть показатель «Фрукты+Овощи».

1. Щёлкаем на поле «Категория».
2. «Анализ сводной таблицы» → «Вычисляемый элемент».
3. Формула: = Фрукты + Овощи.

Теперь в сводной таблице появится новый элемент.

🎛 Срезы и временные шкалы

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

🔹 Срезы

Это кнопки, которые позволяют фильтровать данные одним кликом.

Пример: у нас таблица продаж по менеджерам. Добавляем срез «Менеджер» — и можем быстро включать или отключать конкретных сотрудников.

1. «Анализ сводной таблицы» → «Вставить срез».
2. Выбираем поле (например, «Город» или «Менеджер»).
3. Получаем панель с кнопками.

🔹 Временная шкала

Работает только для полей с датами.

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

1. «Анализ сводной таблицы» → «Вставить временную шкалу».
2. Выбираем поле «Дата».
3. Теперь мы можем перетаскивать бегунок и смотреть данные за любой период.

📈 Сводные диаграммы

Сводная диаграмма — это график, который строится на основе сводной таблицы и автоматически меняется вместе с ней.

🔹 Как построить

1. Щёлкаем внутри сводной таблицы.
2. Вкладка «Анализ» → «Сводная диаграмма».
3. Выбираем тип: столбчатая, круговая, график.

🔹 Пример: продажи по категориям

• Категории товаров — по осям.
• Сумма продаж — по столбцам.
• Срез «Город» позволяет моментально переключать города.

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

🧑‍💻 VBA для автоматизации

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

🔹 Пример: обновление всех сводных таблиц

Sub RefreshAllPivots()

Dim pt As PivotTable

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets

For Each pt In ws.PivotTables

pt.RefreshTable

Next pt

Next ws

End Sub

Этот макрос обновляет все сводные таблицы во всей книге одним кликом.

🔹 Пример: создание сводной таблицы через VBA

Sub CreatePivot()

Dim pc As PivotCache

Dim pt As PivotTable

Set pc = ThisWorkbook.PivotCaches.Create( _

SourceType:=xlDatabase, _

SourceData:="Sheet1!A1:F1000")

Set pt = pc.CreatePivotTable( _

TableDestination:="Sheet2!R3C1", _

TableName:="SalesPivot")

pt.PivotFields("Город").Orientation = xlRowField

pt.PivotFields("Категория").Orientation = xlColumnField

pt.PivotFields("Сумма").Orientation = xlDataField

End Sub

Этот макрос создаёт сводную таблицу автоматически.

📢 Подписка

Если вам полезен этот материал — поддержите проект!

• Подпишитесь на Дзен
• Загляните в Телеграм — там файлы, шаблоны и чек-листы.

Напишите в комментариях: что вам больше всего нравится в сводных таблицах — срезы, диаграммы или автоматизация?

📊 Итоги

В этой серии статей мы:

• разобрали основы сводных таблиц (Часть 1),
• научились группировать и считать проценты (Часть 2),
• освоили вычисляемые поля, диаграммы и VBA (Часть 3).

Теперь у вас есть полный набор инструментов для анализа данных в Excel.