Магия таблиц. 100+ приемов ускорения работы в Excel (и немного в Google Таблицах) - Ренат Шагабутдинов

листу поверх ячеек.

Получается более наглядный фильтр (видно, что выбрано, в отличие от обычного фильтра сводной таблицы).

Но у срезов для сводных есть еще одна приятная особенность: можно связать срез сразу с несколькими сводными. Допустим, у нас на листе две сводных, и мы хотим фильтровать сразу обе с помощью среза.

Щелкаем по срезу правой кнопкой и в контекстном меню выбираем «Подключения к отчетам» (Report Connections).

В появившемся диалоговом окне выбираем названия сводных, к которым нужно подключиться. Обратите внимание, что я заранее переименовал сводные (это можно сделать на вкладке ленты «Анализ сводной таблицы»), иначе тут были бы стандартные названия «Сводная таблица 1», «Сводная таблица 2». Переименовать сводную можно на вкладке ленты «Анализ сводной таблицы».

Нажимаем ОК — и можно фильтровать две сводные одним срезом.

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

Еще один вариант среза для полей с датами/временем — временная шкала (Timescale). Эта опция появилась в Excel 2013.

После нажатия «Вставить временную шкалу» Excel предложит выбрать столбцы, которые подходят (в которых хранится дата и/или время).

В нашем случае такой столбец с подходящими данными один.

Это такой же элемент, как срез, — только с помощью шкалы мы не фильтруем, а выбираем период.

Создание копий отчета сводной таблицы

Файл с примером: Копии фильтра отчета.xlsx

Не всегда хватает строк, столбцов и фильтров (срезов) в сводной. Иногда может возникнуть необходимость добавить еще один уровень, и это может быть уровень… листов. Если какой-то параметр уже не помещается в строки/столбцы, можно сделать отдельную сводную для каждого значения из этого поля.

Например, у нас есть данные по остаткам — тут и категория, и склад, и город. Категорий и складов несколько, а вот городов много — почти два десятка.

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

Конечно, можно добавить города в строки, но тогда сводная не будет помещаться на одном экране: она окажется очень громоздкой.

Возможный выход — «клонировать» сводную, сделать отдельный отчет для каждого города. Для этого нужно перенести «Город» (в общем случае — то поле, для каждого значения из которого вы хотите сделать отдельную сводную) в область фильтров сводной.

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

Теперь остается пройти по следующему адресу:

Анализ сводной таблицы → Параметры (выпадающий список справа от этой кнопки) → Отобразить страницы фильтра отчета

(PivotTable Analyze → Options → Show Report Filter Pages).

Нужно будет выбрать то поле, по которому мы «клонируем» сводную (у нас в фильтре оно одно).

После этого будут созданы листы с названиями городов (элементов из выбранного поля), на каждом из которых будет сводная — ровно как исходная, но с разными городами, выбранными в фильтре. На листе «Владимир» в фильтре будет выбран этот город и так далее.

Числовые форматы, структура сводной, сортировка будут перенесены в эти сводные из исходной. А вот условное форматирование, увы, не сохранится.

Вычисляемое поле (Calculated Field)

Файл с примером: Вычисляемое поле.xlsx

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

Команда для вставки вычисляемых полей — на вкладке «Анализ сводной таблицы» (PivotTable Analyze — Fields, Items & Sets — Calculated Field).

Появится диалоговое окно, в котором вводятся формула создаваемого поля и его название.

Теперь при любой компоновке строк и столбцов в сводной мы можем видеть 20% от продаж.

Поле «Продажи», кстати, отображать совсем не обязательно — можно выводить только вычисляемое поле.

Вычисляемый объект (Calculated Item)

Файл с примером: Вычисляемый объект.xlsx

Вычисляемый объект — дополнительная строка в сводной с вычислением на основе существующих строк. На практике используется реже, чем вычисляемое поле, но все же может пригодиться, если нужно агрегировать несколько значений в сводной.

Допустим, у нас есть следующая сводная: каналы продаж в строках, форматы — в столбцах.

И мы хотим добавить строку с розничными продажами (дистрибуция + интернет-магазин). Для этого выделим любую ячейку в том поле, для которого добавляется вычисляемый объект (любой канал продаж в нашем случае), и выберем команду «Вычисляемый объект…» на вкладке ленты «Анализ».

В появившемся окне задаем имя элемента и формулу. Для написания формулы можно выбирать элементы из списка внизу и щелкать по ним дважды либо нажимать «Добавить элемент». Конечно, можно вводить названия элементов и вручную, но смысла в этом нет (обратите внимание, что названия с пробелами/дефисами нужно брать в апострофы — это происходит автоматически при добавлении поля из списка).

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

Обратите внимание на строку «Общий итог». Там суммируются три канала продаж и добавленный нами вычисляемый элемент тоже. Получается задвоение. Можно убрать промежуточные значения фильтром (если вам не нужно смотреть на составляющие розничных продаж отдельно).