Надстройка PLEX для Microsoft Excel 2007-2021 и Office 365

Инструменты сводных таблиц

24663 22.04.2016
Инструменты сводных таблиц

Если в своей работе вы периодически сталкиваетесь со сводными таблицами, то оцените пользу от этих инструментов:

Фильтрация сводной по именованным диапазонам

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

Команда Копировать как значения делает копию сводной таблицы в виде значений (т.е. не связанную с исходными данными) на отдельном листе, сохраняя при этом все форматирование. Весьма частая процедура у многих пользователей, которым нужно отправлять данные "во внешний мир", но при этом не хочется отдавать саму сводную, а только значения из нее.

Команда Фильтр по текущей ячейке работает аналогично двойному щелчку левой кнопкой мыши ("провалиться" или drill-down) по ячейке с данными в области значений сводной таблицы, но фильтрует исходную таблицу, а не создает новый лист с копией данных. Таким образом можно быстро перейти к тем строкам, которые участвуют в вычислениях любой интересующей вас ячейки в сводной. Отобразить полный список всех строк исходных данных можно либо отменив последнее действие, либо с помощью сочетания клавиш Ctrl+Shift+A (назначается в Диспетчере горячих клавиш). Данный макрос является улучшенной версией старого, описанного в свое время здесь.  

Команда Форматы исходных данных автоматически применяет к полям в сводной числовые форматы, которые были настроены в исходных данных. Так, например, если в исходной таблице столбец Прибыль был в денежном формате, то этот же формат применится и в сводной.

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

Очистка старых элементов в выпадающих списках и фильтрах сводной. При частых изменениях в исходных данных, в выпадающих списках и и фильтрах сводных часто остаются устаревшие элементы, которых, фактически, уже нет, т.к. Excel кэширует эти наборы для ускорения процесса. Этот макрос очищает все старые элементы, приводя списки в соответствие с реальностью.

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

Инструмент Фильтр по именованным диапазонам в выпадающем меню Сводные таблицы как раз и предназначен для решения такой задачи. Алгоритм его использования прост:

  1. Сначала нужно создать именованный диапазон со списком тех товаров, сделок, клиентов и т.п., которые вам нужны. Для этого можно использовать инструмент PLEX Вып.список – Создать новый или стандартный Диспетчер имен на вкладке Формулы.
  2. Затем установите активную ячейку в любое место сводной и выберите на вкладке PLEX – Сводные таблицы – Фильтр по именованным диапазонам. В открывшемся окне задайте какой именованный диапазон вы хотите применить к какому полю в сводной:

Где именно лежит соответствующие поле в сводной (в области строк, столбцов или фильтра) – не играет роли. После нажатия на ОК в сводной отфильтруются только те позиции, что входили в именованный диапазон.

Видео

Может ли это повредить мои данные?

Нет. Поля и форматы в сводной можно всегда настроить обратно руками. Редизайн кросс-таблиц выводит результаты на новый лист и никак не затрагивает ваши данные. Фильтрация по текущей ячейке просто скрывает ненужные строки - их можно всегда отобразить обратно.

Полный список всех инструментов надстройки PLEX



Наверх