Магия таблиц. 100+ приемов ускорения работы в Excel (и немного в Google Таблицах) - Ренат Шагабутдинов
Шрифт:
Интервал:
![](images/i_474.jpg)
Кнопка «Сводная таблица» вызовет диалоговое окно. Убедитесь, что в качестве источника данных для сводной будет использован правильный диапазон или таблица, и укажите, где должна быть создана сводная — на отдельном листе или на одном из уже существующих в книге.
![](images/i_475.jpg)
«Рекомендуемые сводные» (Recommended PivotTables) появились в версии Excel 2013. При выборе этого варианта Excel в большинстве случаев предложит вам несколько вариантов сводных таблиц.
![](images/i_476.jpg)
В большинстве случаев это будет не то, что вам нужно на 100%, но от предложенных вариантов можно отталкиваться и дорабатывать их, так что это все равно может сэкономить время.
Настройка сводной таблицы. Список полей
После создания сводной появляется пока еще пустой отчет, а справа будет список полей сводной таблицы. Это столбцы (поля) из исходных данных.
Хотя в будущем этот список может пополниться, так как в сводных можно создавать новые поля.
На ленте инструментов появятся контекстные (то есть возникающие только при активации сводной) вкладки.
• «Анализ сводной таблицы» (PivotTable Analyze): там можно изменить источник данных, добавить вычисляемое поле, вставить срез и временную шкалу и многое другое — все операции со сводной здесь.
• «Конструктор» (Design): на этой вкладке настраивается внешний вид сводной — стиль и макет.
![](images/i_477.jpg)
Именно в области «Поля сводной таблицы» (PivotTable Fields) происходит настройка ее структуры. Здесь есть четыре области.
• Фильтры (Filters). Если поле перенесено сюда, его значения не будут показываться в сводной, по ним можно будет фильтровать, то есть выбирать только определенные значения.
• Строки (Rows). При перенесении сюда полей будут выводиться уникальные значения из них — соответственно в строках и столбцах.
• Столбцы (Columns). Аналогично строкам.
• Значения (Values). То, что будет в области данных сводной, на пересечении строк и столбцов — над полями, добавленными сюда, можно проводить вычисления, например суммировать или усреднять.
Чтобы начать строить сводную, нужно определиться, по каким полям данные будут агрегироваться (например: по городам, регионам, менеджерам — соответствующие поля нужно перенести в строки или столбцы) и какие данные будут вычисляться (что будет суммироваться или иначе вычисляться: продажи, заявки, какие-то иные — как правило, числовые — данные).
И затем перенести нужные поля в соответствующую область — строк, столбцов или значений. Это можно сделать, просто перетащив поле с нажатой левой кнопкой мыши, а можно нажать на галочку рядом с полем. Если вы нажимаете на галочку, то текстовые поля автоматически попадут в строки, а числовые — в значения.
Вот так будет выглядеть сводная в нашем примере, если поставить галочки возле полей «Регион» и «Продажи». Текстовое поле (столбец с регионом в нашем случае) автоматически попадет в строки.
![](images/i_478.jpg)
В сводной Excel не наследуется (в отличие от Google Таблиц) числовой формат исходных данных. Зато можно изменить формат сразу у всего поля, а не отдельных ячеек. Щелкайте правой кнопкой и выбирайте «Числовой формат» (Number Format) (не «Формат ячеек» / Format Cells — это настройки конкретной ячейки).
![](images/i_479.jpg)
Полей в строках или столбцах сводной может быть несколько — просто перетащите еще одно поле в область строк, и у вас будет группировка по двум уровням. Уровней может быть и больше, их порядок можно менять.
![](images/i_480.jpg)
По умолчанию все сводные строятся в сжатой форме (Compact Form). Форму можно изменить на вкладке «Конструктор» (Design).
![](images/i_481.jpg)
В табличной форме (Tabular Form) и в форме структуры (Outline Form) каждое поле из области строк сводной будет в отдельном столбце (эти два варианта отличаются между собой форматом вывода промежуточных итогов).
![](images/i_482.jpg)
По умолчанию к полю в области значений применяется функция суммирования (если в нем числа, как в нашем примере) или подсчета количества значений (если есть текстовые значения).
Но функцию можно изменить. Для этого нужно попасть в параметры поля (Value Field Settings):
• «Параметры поля» на вкладке «Анализ сводной таблицы»;
• правой кнопкой по полю в области значений (справа внизу) в панели «Поля сводной таблицы»;
• правой кнопкой по любому значению в поле непосредственно в отчете сводной таблицы → «Параметры полей значений».
А в них — выбрать нужную функцию (Summarize value field by).
![](images/i_483.jpg)
В этом же окне во вкладке «Дополнительные вычисления» (Show Values As) можно поменять тип вычислений: вместо абсолютного значения выводить долю.
![](images/i_484.jpg)
Вычисляем процент от общей суммы (% of Grand Total), то есть долю каждого значения в общих продажах.
![](images/i_485.jpg)
С одним полем можно провести несколько вычислений. Например, можно вывести в сводной и средние продажи, и сумму продаж. Для этого перетащите поле в область значений еще раз и выберите нужную функцию.
![](images/i_486.jpg)
По умолчанию несколько полей значений располагаются по столбцам, как на скриншоте ниже. Но их можно перенести в строки, для этого в «Полях сводной таблицы» нужно перенести «∑ Значения» (∑ Values).
![](images/i_487.jpg)
Фильтры (Filters) и срезы (Slicers) в сводной таблице
![](images/i_002.jpg)
Если вы хотите фильтровать данные по каким-то полям, их нужно перенести в область фильтра или (начиная с версии Excel 2010) вставить срез (Slicer).
Поле в фильтре (в данном примере — «Категория») выглядит так: оно указано в левом верхнем углу, над основной частью отчета сводной таблицы со строками и столбцами. Если выбрано одно значение, то будет видно какое, как на примере (если выбрано несколько, придется залезать в фильтр, чтобы посмотреть их).
![](images/i_488.jpg)
Срез — более наглядный вариант. Его можно добавить на вкладке «Анализ сводной таблицы» (PivotTable Analyze).
![](images/i_489.jpg)
После вставки Excel предложит выбрать одно или несколько полей, для фильтрации по которым вы хотите вставить срез.
![](images/i_490.jpg)
Срез можно перемещать по рабочему
Поделиться книгой в соц сетях:
Обратите внимание, что комментарий должен быть не короче 20 символов. Покажите уважение к себе и другим пользователям!