Сводные таблицы - отличный инструмент, позволяющий анализировать значительные массивы данных без формул и сложных вычислений. И у сводных таблиц, как и у многих других инструментов Excel, есть свои секреты или, точнее сказать, малоизвестные опции, которые могут быть весьма полезными в работе.
В этой заметке я расскажу о трех таких секретах, которые связаны с фильтрацией данных.
Отдельный лист на каждый элемент фильтра
Например, нам требуется создать сводную таблицу заказов на основе некоторой выгрузки из базы данных.
То есть в таблице будет выводиться общая сумма заказов по отдельным заказчикам за определенный временной период.
Для анализа такая сводная вполне сгодится, но в ряде ситуация нужно иметь отдельную сводную по каждому заказчику.
Решить задачу можно с помощью фильтра, переместив поле Заказчик в него.
Теперь выбирая заказчика в фильтре мы получим данные только по нему.
Но кроме этого можно автоматически сформировать отдельные листы со сводными по каждому заказчику. Для этого включаем в фильтре всех заказчиков и через контекстную вкладку Анализ в параметрах сводной таблицы выбираем Отобразить страницы фильтра отчета.
В появившемся окне мы должны указать поле из фильтра, по которому необходимо отобразить страницы. Так как мы использовали только один фильтр, то просто нажимаем ОК.
В документе будут созданы отдельные листы для каждого заказчика и на каждом листе представлена соответствующая сводная таблица.
Автообновляемый фильтр
Если вы часто используете сводные таблицы, то, скорее всего, сталкивались с такой ситуацией. Например, у нас есть выгрузка по продажам за определенный период и необходимо оценить количество сделок, совершенных каждым менеджером по отдельным товарам.
Создадим сводную. Сформируем столбец с количеством сделок, перетянув любое подходящее поле, например, Товар в Значения. Так же Товары добавим в фильтры.
Получили сводную, которая позволяет проанализировать работу менеджеров. И вот тут может возникнуть следующий рабочий момент - необходимо проанализировать данные по продажам всех товаров, кроме какого-то одного.
В фильтре это реализуется довольно просто - указываем все товары, а затем снимаем галочку с нужного.
И вот тут возникает проблема. Если исходные данные изменятся, то есть в них будут добавлены новые заказы с новыми, ранее не фигурировавшими в таблице, товарами, то они по умолчанию не будут отображаться фильтром (галочка на товаре будет снята).
То есть придется каждый раз после дополнения исходных данных контролировать фильтр сводной таблицы и в случае необходимости вносить в него изменение.
Решить эту задачу довольно просто. Вызываем контекстное меню на фильтре и выбираем Параметры поля. В открывшемся окне включаем соответствующую опцию.
Теперь все новые товары будут автоматически включаться в фильтр, что позволит сократить рутинные операции по контролю правильной настройки фильтра после каждого обновления исходной и сводной таблиц.
Фильтр по полю значения
И еще одна хитрость, которая касается фильтрации.
В заголовках сводных таблиц, также как и в заголовках умных таблиц, можно использовать кнопки, позволяющие настирывать сортировку и фильтрацию данных по определенному столбцу.
Однако в шапке столбца значений (Количество по полю Товар) кнопку автофильтрации включить не получится. Она отсутствует по умолчанию и даже если попытаться включить ее с помощью инструментов ленты, то ничего не выйдет.
Чтобы добавить кнопку фильтрации в столбец со значениями сводной, выделяем пустую ячейку рядом с шапкой крайнего правого столбца сводной таблицы и уже в этой ячейке включаем фильтр, например, через соответствующий инструмент Фильтр со вкладки Главная.
Кнопка фильтрации появится в заголовке столбца значений и теперь можно будет делать сортировку или фильтрацию таблицы по этому столбцу.
Ссылки на мои ресурсы по Excel
★ Телеграм