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

(Insert) на ленте. Там есть две опции: «Сводная таблица» и (начиная с версии 2013) «Рекомендуемые сводные таблицы» (Recommended PivotTables).

Кнопка «Сводная таблица» вызовет диалоговое окно. Убедитесь, что в качестве источника данных для сводной будет использован правильный диапазон или таблица, и укажите, где должна быть создана сводная — на отдельном листе или на одном из уже существующих в книге.

«Рекомендуемые сводные» (Recommended PivotTables) появились в версии Excel 2013. При выборе этого варианта Excel в большинстве случаев предложит вам несколько вариантов сводных таблиц.

В большинстве случаев это будет не то, что вам нужно на 100%, но от предложенных вариантов можно отталкиваться и дорабатывать их, так что это все равно может сэкономить время.

Настройка сводной таблицы. Список полей

После создания сводной появляется пока еще пустой отчет, а справа будет список полей сводной таблицы. Это столбцы (поля) из исходных данных.

Хотя в будущем этот список может пополниться, так как в сводных можно создавать новые поля.

На ленте инструментов появятся контекстные (то есть возникающие только при активации сводной) вкладки.

• «Анализ сводной таблицы» (PivotTable Analyze): там можно изменить источник данных, добавить вычисляемое поле, вставить срез и временную шкалу и многое другое — все операции со сводной здесь.

• «Конструктор» (Design): на этой вкладке настраивается внешний вид сводной — стиль и макет.

Именно в области «Поля сводной таблицы» (PivotTable Fields) происходит настройка ее структуры. Здесь есть четыре области.

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

• Строки (Rows). При перенесении сюда полей будут выводиться уникальные значения из них — соответственно в строках и столбцах.

• Столбцы (Columns). Аналогично строкам.

• Значения (Values). То, что будет в области данных сводной, на пересечении строк и столбцов — над полями, добавленными сюда, можно проводить вычисления, например суммировать или усреднять.

Чтобы начать строить сводную, нужно определиться, по каким полям данные будут агрегироваться (например: по городам, регионам, менеджерам — соответствующие поля нужно перенести в строки или столбцы) и какие данные будут вычисляться (что будет суммироваться или иначе вычисляться: продажи, заявки, какие-то иные — как правило, числовые — данные).

И затем перенести нужные поля в соответствующую область — строк, столбцов или значений. Это можно сделать, просто перетащив поле с нажатой левой кнопкой мыши, а можно нажать на галочку рядом с полем. Если вы нажимаете на галочку, то текстовые поля автоматически попадут в строки, а числовые — в значения.

Вот так будет выглядеть сводная в нашем примере, если поставить галочки возле полей «Регион» и «Продажи». Текстовое поле (столбец с регионом в нашем случае) автоматически попадет в строки.

В сводной Excel не наследуется (в отличие от Google Таблиц) числовой формат исходных данных. Зато можно изменить формат сразу у всего поля, а не отдельных ячеек. Щелкайте правой кнопкой и выбирайте «Числовой формат» (Number Format) (не «Формат ячеек» / Format Cells — это настройки конкретной ячейки).

Полей в строках или столбцах сводной может быть несколько — просто перетащите еще одно поле в область строк, и у вас будет группировка по двум уровням. Уровней может быть и больше, их порядок можно менять.

По умолчанию все сводные строятся в сжатой форме (Compact Form). Форму можно изменить на вкладке «Конструктор» (Design).

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

По умолчанию к полю в области значений применяется функция суммирования (если в нем числа, как в нашем примере) или подсчета количества значений (если есть текстовые значения).

Но функцию можно изменить. Для этого нужно попасть в параметры поля (Value Field Settings):

• «Параметры поля» на вкладке «Анализ сводной таблицы»;

• правой кнопкой по полю в области значений (справа внизу) в панели «Поля сводной таблицы»;

• правой кнопкой по любому значению в поле непосредственно в отчете сводной таблицы → «Параметры полей значений».

А в них — выбрать нужную функцию (Summarize value field by).

В этом же окне во вкладке «Дополнительные вычисления» (Show Values As) можно поменять тип вычислений: вместо абсолютного значения выводить долю.

Вычисляем процент от общей суммы (% of Grand Total), то есть долю каждого значения в общих продажах.

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

По умолчанию несколько полей значений располагаются по столбцам, как на скриншоте ниже. Но их можно перенести в строки, для этого в «Полях сводной таблицы» нужно перенести «∑ Значения» (∑ Values).

Фильтры (Filters) и срезы (Slicers) в сводной таблице

Файл с примером: Срезы и временные шкалы в сводных.xlsx

Если вы хотите фильтровать данные по каким-то полям, их нужно перенести в область фильтра или (начиная с версии Excel 2010) вставить срез (Slicer).

Поле в фильтре (в данном примере — «Категория») выглядит так: оно указано в левом верхнем углу, над основной частью отчета сводной таблицы со строками и столбцами. Если выбрано одно значение, то будет видно какое, как на примере (если выбрано несколько, придется залезать в фильтр, чтобы посмотреть их).

Срез — более наглядный вариант. Его можно добавить на вкладке «Анализ сводной таблицы» (PivotTable Analyze).

После вставки Excel предложит выбрать одно или несколько полей, для фильтрации по которым вы хотите вставить срез.

Срез можно перемещать по рабочему