Книга: Магия таблиц. 100+ приемов ускорения работы в Excel (и немного в Google Таблицах)
Назад: Создание сводной таблицы
Дальше: Фильтры (Filters) и срезы (Slicers) в сводной таблице
Настройка сводной таблицы. Список полей
После создания сводной появляется пока еще пустой отчет, а справа будет список полей сводной таблицы. Это столбцы (поля) из исходных данных.
Хотя в будущем этот список может пополниться, так как в сводных можно создавать новые поля.
На ленте инструментов появятся контекстные (то есть возникающие только при активации сводной) вкладки.
• «Анализ сводной таблицы» (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).
