Продолжение руководства по сводным таблицам в Excel. Углубляемся в настройки фильтров (в том числе срезы и фильтр отчёта), расчёты по регионам и другим категориям, обновление данных сводной таблицы при изменении исходной таблицы. Разбираем подводные камни копирования сводных таблиц и вставки значений, а также делимся советами по автоматизации работы со сводными таблицами с помощью макросов VBA.
Сводные таблицы в Excel с нуля: зачем нужны и как создать (Часть 1)
Сводные таблицы в Excel: фильтры, обновление и макросы (Часть 2)
Сводные таблицы в Excel: фильтры, обновление и макросы (Часть 2)
Это вторая часть руководства о сводных таблицах. В первой части мы рассмотрели, как создать сводную таблицу с нуля, зачем она нужна, и освоили базовые приёмы: перетаскивание полей, сортировку и простую фильтрацию по сотрудникам и товарам. Теперь настало время углубиться в более продвинутые возможности сводных таблиц.
В этой части мы поговорим о том, как настраивать фильтры для гибкого анализа данных, покажем вычисления на примере разбиения по регионам, научимся обновлять сводную таблицу при изменении данных и обсудим типичные подводные камни копирования и вставки значений. Напоследок вы узнаете несколько полезных приёмов для автоматизации работы со сводными таблицами с помощью макросов VBA.
Гибкая настройка фильтров в сводной таблице
В первой части мы уже познакомились с основами фильтрации: можно выбирать отдельные элементы через выпадающие списки полей или использовать срезы для наглядной фильтрации. Теперь рассмотрим, как сделать фильтрацию ещё более гибкой.
Несколько фильтров одновременно.
Сводная таблица позволяет использовать сразу несколько полей в качестве фильтров отчёта. Например, к нашему отчёту о продажах вы можете добавить в область Фильтры поле Год (если анализируются данные за несколько лет) и поле Регион одновременно.
Тогда над таблицей отобразятся два фильтра: по году и по региону. Вы сможете выбрать, например, 2023 год и Регион = Север – сводная таблица моментально перестроится, показав данные только за 2023 год по северному региону. При необходимости можно выбрать значение «(Все)» в фильтре, чтобы снова показать полный охват данных.
По умолчанию, если добавить более одного поля в область Фильтры, Excel будет располагать их вертикально (один под другим). Но в параметрах сводной таблицы можно настроить отображение фильтров в несколько столбцов, чтобы экономить место на листе.
Для этого кликните правой кнопкой внутри сводной таблицы и выберите Параметры сводной таблицы... – там найдётся опция, позволяющая указать, сколько фильтров выводить в ряд.
Фильтры по условию (метки и значения).
Кроме прямого выбора элементов списка, сводная таблица поддерживает фильтрацию по условиям:
- Фильтр по меткам: работает для полей, используемых в строках или столбцах. Например, у вас в строках перечислены названия товаров, и вы хотите отобразить только те из них, которые содержат слово "Премиум" в названии. Нажмите на стрелочку фильтра у поля Товар, выберите Фильтры по меткам → Содержит... и введите «Премиум».
В результате сводная таблица отобразит только товары, названия которых подходят под этот критерий. Аналогично можно фильтровать по началу/концу текста или по точному совпадению – Excel предлагает варианты (начинается с..., заканчивается на..., равно...). - Фильтр по значениям: работает для числовых показателей в значениях. Например, нужно вывести топ-5 менеджеров по объёму продаж. Для поля Менеджер (которое находится в строках) можно задать условие: Фильтры по значению → 10 наибольших... (или настроить своё число). Выбираем "5 наибольших элементов по полю Выручка".
В итоге сводная таблица покажет только 5 менеджеров с самыми высокими суммами выручки. Фильтры по значениям также позволяют задать условие "больше/меньше чем…": например, показать строки, где суммарная выручка превышает определённый порог.
Эти условные фильтры очень выручают, когда данные обширные. Вместо того чтобы вручную искать нужные элементы, можно задать критерий – и сводная таблица сама отберёт нужные записи.
Совет: комбинируйте разные виды фильтров. Никто не мешает одновременно применить фильтр отчёта (скажем, выбрать конкретный год), внутри него отфильтровать товары по условию (например, содержит "Премиум"), а затем ещё и отсортировать итоговые значения по убыванию. Сводная таблица поддерживает всё это разом, позволяя очень гибко "выуживать" информацию.
Вычисления по регионам и другим категориям
Рассмотрим теперь, как сводные таблицы помогают анализировать данные сразу по нескольким категориям. В качестве примера возьмём поле Регион. Предположим, у вас компания работает в нескольких регионах (Север, Юг, Запад, Восток), и вы хотите сравнить результаты по каждому региону.
Добавление новой категории в отчёт.
В уже созданной сводной таблице (из первой части) у нас на данный момент строки – это Менеджеры, колонки – Товары, значения – сумма Выручки. Добавим поле Регион: - Перетащите поле Регион в область Строки, причём поместите его выше поля Менеджер. Порядок в списке Rows определяет уровень вложенности: теперь в отчёте сначала будут отображаться регионы, а внутри каждого региона – список менеджеров этого региона.
Обратите внимание: Excel автоматически добавил строку-итог для каждого региона. Имя региона выделено жирным, а рядом с ним – свернутый значок «+». Это означает, что категории можно сворачивать и разворачивать. Сейчас развернуто (видны менеджеры), но вы можете нажать на «-» возле названия региона, и список менеджеров свернётся, показывая только общую сумму по региону. Таким образом, сводная таблица позволяет группировать данные и иерархически их отображать.
Теперь в колонках Товар и строках Менеджер внутри каждого региона вы видите подробный разрез, а сами регионы служат как бы верхним уровнем. Можно быстро сравнить регионы друг с другом по общей сумме: она указана в строке Итого каждого региона. Например, видно, что в регионе Север суммарная выручка 5 млн, а в регионе Юг – 3 млн.
Проценты и доли от общего.
Часто при анализе возникает вопрос: а какую долю составляют продажи региона от общего объёма? Или, например, какой вклад каждого менеджера в своём регионе? Сводные таблицы облегчают и это за счёт опции "Показать значения как". Давайте сделаем долю региона в общем объёме:
- Щёлкните правой кнопкой мыши по любой цифре, относящейся к сумме по региону (например, на ячейке с общей выручкой по региону Север).
- Выберите Показать значения как → % от итога по столбцу (если регионы у вас в строках, а итог по всем регионам показан в итоговом столбце). Либо можно выбрать "% от итога по строке", в зависимости от того, как у вас развернуты данные.
- Excel добавит еще одну колонку значений или изменит отображение существующей – в нашем случае появятся проценты, показывающие вклад каждого региона в общий итог.
Аналогично можно показать проценты по менеджерам внутри региона (например, % от итога по родительскому полю – региону). Для этого, в настройках поля значений есть вариант "% от итога по [поле]". Выберите поле Регион в качестве базы – тогда сводная таблица рассчитает, сколько процентов составляет выручка каждого менеджера от общего итога по его региону.
Такие вычисления помогают сразу видеть структуру данных: кто или что вносит наибольший вклад, где узкие места. И всё это — без единой формулы вручную!
(Если вы цените такие детальные разборы возможностей Excel, обязательно подпишитесь на наш Telegram-канал и Дзен. Там вы найдёте ещё больше примеров, а также сможете задать вопросы и обсудить свои случаи. Нам важно ваше участие — делитесь мыслями в комментариях!)
Обновление данных сводной таблицы
Сводная таблица, однажды созданная, не является статичной фотографией данных — её можно (и нужно) обновлять, когда исходные данные меняются или дополняются. Рассмотрим, как правильно обновить сводную таблицу и не потерять ничего важного.
Когда нужно обновлять сводную таблицу?
В двух случаях:
1. Изменились исходные данные. Например, вы поправили некоторые значения, добавили новые строки продаж или исправили опечатки. Сводная таблица не реагирует на эти изменения автоматически, пока вы её не обновите.
2. Добавились новые данные за пределами исходного диапазона. Например, изначально сводная строилась на диапазоне A1:F500, а вы добавили данные в строке 501. По умолчанию сводная таблица о них "не знает", поскольку они вне исходного диапазона, и их не покажет.
Как обновить данные в сводной таблице?
Очень просто: - Щёлкните правой кнопкой мыши по любой ячейке внутри сводной таблицы и выберите Обновить (Refresh). Либо нажмите на вкладке Анализ кнопку Обновить. - Сводная таблица перечитает исходные данные и пересчитает все итоги. Новые или изменённые цифры появятся в отчёте.
Однако, это сработает только для изменений внутри исходного диапазона. Если же вы добавили абсолютно новые строки ниже или справа от исходного массива, нужно сначала расширить диапазон, на основе которого строится сводная таблица: - Перейдите на вкладку Анализ и найдите кнопку Изменить источник данных (Change Data Source).
Откроется диалог, где вы можете скорректировать адрес диапазона. Укажите новый диапазон, включающий новые данные. - Лучше вариант: сделайте исходную таблицу умной таблицей (Table). Если ваш источник данных оформлен как таблица Excel, то сводная таблица будет ссылаться на неё по имени, и при добавлении новых строк диапазон источника расширяется автоматически. Вам останется только нажать Обновить, и сводная подхватит все новые данные.
Обновление нескольких сводных таблиц.
Если у вас в книге несколько сводных таблиц (возможно, на разных листах, ссылающихся на одну и ту же исходную таблицу), вы можете обновлять их разом: - Команда Данные → Обновить всё (Refresh All) обновит все сводные таблицы и все внешние подключения в книге. - Или воспользуйтесь макросом (о макросах расскажем ниже) для обновления – это особенно удобно, если сводных много, и вы хотите обновлять их по нажатию кнопки.
Подводные камни: копирование сводной таблицы и вставка значений
Сводные таблицы удобны, но при работе с ними важно знать о некоторых нюансах, чтобы не получить неожиданные результаты. Рассмотрим часто встречающиеся подводные камни:
Копирование сводной таблицы в тот же файл.
Если вы копируете лист со сводной таблицей или дублируете саму сводную таблицу в пределах одной книги Excel, новая копия будет связана с тем же источником данных и кешем сводной таблицы. Это значит, что обе сводные независимы в плане фильтров (вы можете по-разному отфильтровать каждую), но некоторые операции затронут сразу обе. Например, если вы группируете даты в одной сводной, такая же группировка автоматически применится и в её копии. Это происходит потому, что у них общий кеш (обобщённые данные хранятся единообразно).
Копирование в другой файл.
Если нужно перенести сводную таблицу в другую книгу, просто копировать диапазон с ней недостаточно. В буфер попадут только значения (лишь то, что сейчас отображается). Сама сводная таблица в новом файле не появится, так как без данных она существовать не может. Правильный подход: копировать вместе с листом исходных данных или в новом файле снова создать сводную на основе перенесённых данных.
Копирование и вставка значений.
Иногда требуется зафиксировать текущее состояние сводной таблицы как обычные значения (например, чтобы отправить отчёт внешнему получателю без передачи исходных данных). Для этого можно скопировать область сводной таблицы и сделать Специальная вставка → Значения в нужное место (или на новый лист). Вы получите статическую таблицу с цифрами. Учтите, что при этом пропадут все группировки и свернутые элементы – копируются только видимые на момент копирования значения. Кроме того, отсоединённая от данных таблица не обновится, если придут новые данные.
Формулы GETPIVOTDATA.
Отдельно отметим момент: когда вы пытаетесь ссылаться на значение из сводной таблицы в формуле (например, в другой ячейке написать "=" и кликнуть на ячейку внутри сводной), Excel вставляет функцию GETPIVOTDATA. Новички часто пугаются или злятся на неё. Эта функция вытягивает данные из сводной по именам полей и элементов. Если вы не хотите её использовать, можно отключить опцию "Генерировать формулы GETPIVOTDATA" на вкладке Анализ. Либо вручную написать формулу с обычной ссылкой (например, =B5).
Но помните: при перестройке сводной таблицы расположение данных может поменяться, и простая ссылка может начать указывать не на ту ячейку. GETPIVOTDATA, напротив, привязана к именам элементов и всегда вытащит правильное значение, даже если оно "уехало" в другое место при обновлении.
Зная эти нюансы, вы обезопасите себя от ошибок. Всегда задавайте себе вопрос: что я копирую и с какой целью? Если нужно просто перенести картинку сводной таблицы – используйте вставку значений. Если хотите сделать вторую сводную по тем же данным – можете копировать лист (это сэкономит время), но помните про общие настройки.
Для совершенно независимого отчёта по тем же данным можно принудительно создать отдельный кеш – для этого при вставке новой сводной таблицы снимите галочку «Добавить эти данные в модель данных» (или используйте опцию «Создать копию без связи», если продвинутое ПО позволяет).
Автоматизация сводных таблиц с помощью макросов
На десерт – то, что отличает продвинутого пользователя Excel от начинающего: макросы. VBA (Visual Basic for Applications) позволяет автоматизировать практически любую задачу в Excel, и сводные таблицы не исключение.
Подумайте, сколько шагов мы сделали, чтобы настроить отчет: выбрали диапазон, вставили сводную, перетащили поля, применили фильтры, сортировки... Если эти действия приходится повторять регулярно (например, вы каждую неделю делаете похожий отчет с новыми данными) – есть смысл записать или написать макрос, который сделает всё автоматически.
Что можно автоматизировать?
Практически всё:
- Создание типовых сводных таблиц. Вы можете записать макрос, когда вручную создаёте сводную таблицу первый раз. Excel запишет все ваши действия – выбор источника, размещение сводной на листе, добавление полей в строки/колонки/значения. Потом вы можете запустить этот макрос на новых данных, и сводная таблица построится в один клик.
- Обновление данных. Макрос может обновлять сразу все сводные таблицы, как мы упоминали выше. - Форматирование и вычисляемые поля. Если вы каждый раз добавляете одни и те же вычисляемые элементы или форматируете таблицу определенным образом (например, выделяете топ-10 цветом), это тоже можно включить в макрос.
Например, давайте создадим простой макрос, который обновляет все сводные таблицы в текущей книге:
Sub ОбновитьВсеСводные()
' Обновляет все сводные таблицы в книге
ActiveWorkbook.RefreshAll
End Sub
Этот макрос проходит по книге и выполняет команду обновления для всех сводных таблиц (а также запросов к внешним данным, если они есть). Вы можете привязать его выполнение к кнопке на панели или просто запускать через комбинацию клавиш. В результате вам больше не нужно вручную нажимать «Обновить» для каждой таблицы — достаточно одного вызова макроса.
Если вы новичок в VBA, не волнуйтесь: начать можно с простой записи макроса (в Excel: Вкладка Разработчик → Запись макроса), а затем слегка поправить код. На нашем канале Macros Channel мы подробно рассказываем о написании макросов для Excel, загляните туда за дополнительными гайдами.
Заключение
Поздравляем, теперь вы знакомы не только с основами, но и с продвинутыми приёмами работы со сводными таблицами! Мы рассмотрели, как с помощью фильтров вычленять именно ту информацию, которая нужна, как сравнивать показатели по регионам и другим разрезам, а также как обновлять сводные таблицы при обновлении данных и избегать распространённых подводных камней.
Бонусом вы узнали, как рутинные операции превратить в одну кнопку с помощью макросов VBA.
Сводные таблицы — это мощь, особенно в умелых руках. Практикуйтесь, пробуйте разные настройки.