Панели мониторинга Excel — сводные таблицы

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

Excel предоставляет более эффективный способ создания сводной таблицы из нескольких таблиц, разных источников данных и внешних источников данных. Он называется Power PivotTable, который работает в своей базе данных, известной как модель данных. В других главах вы познакомитесь с Power PivotTable и другими мощными инструментами Excel, такими как Power PivotChart и Power View Reports.

Сводные таблицы, Power PivotTable, Power PivotCharts и Power View Reports удобны для отображения сводных результатов из больших наборов данных на панели инструментов. Вы можете освоить обычную сводную таблицу, прежде чем приступить к работе с электроинструментами.

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

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

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

Рассмотрим следующий диапазон данных, который содержит данные о продажах для каждого продавца, в каждом регионе и в январе, феврале и марте —

Чтобы создать сводную таблицу из этого диапазона данных, выполните следующие действия:

• Убедитесь, что в первом ряду есть заголовки. Вам нужны заголовки, потому что они будут именами полей в вашей сводной таблице.

• Назовите диапазон данных как SalesData_Range.

• Нажмите на диапазон данных — SalesData_Range.

• Нажмите на вкладку INSERT на ленте.

• Нажмите на сводную таблицу в группе таблиц.

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

Назовите диапазон данных как SalesData_Range.

Нажмите на диапазон данных — SalesData_Range.

Нажмите на вкладку INSERT на ленте.

Нажмите на сводную таблицу в группе таблиц.

Откроется диалоговое окно «Создать сводную таблицу».

Как вы можете заметить, в диалоговом окне Создать сводную таблицу в разделе Выбор данных, которые вы хотите проанализировать, вы можете выбрать таблицу или диапазон из текущей рабочей книги или использовать внешний источник данных. Следовательно, вы можете использовать те же шаги, чтобы создать форму сводной таблицы Range или Table.

• Нажмите на Выбрать таблицу или диапазон.

• В поле Таблица / Диапазон введите имя диапазона — SalesData_Range.

• Нажмите «Новый лист» в разделе «Выбор места размещения отчета сводной таблицы».

Нажмите на Выбрать таблицу или диапазон.

В поле Таблица / Диапазон введите имя диапазона — SalesData_Range.

Нажмите «Новый лист» в разделе «Выбор места размещения отчета сводной таблицы».

Вы также можете заметить, что вы можете анализировать несколько таблиц, добавив этот диапазон данных в модель данных. Модель данных — это база данных Excel Power Pivot.

• Нажмите кнопку ОК. Новый лист будет вставлен в вашу книгу. Новая рабочая таблица содержит пустую сводную таблицу.

• Назовите рабочий лист — Range-PivotTable.

Нажмите кнопку ОК. Новый лист будет вставлен в вашу книгу. Новая рабочая таблица содержит пустую сводную таблицу.

Назовите рабочий лист — Range-PivotTable.

Как вы можете заметить, список полей сводных таблиц появляется в правой части листа и содержит имена заголовков столбцов в диапазоне данных. Далее на ленте отображаются инструменты сводной таблицы — АНАЛИЗ и ДИЗАЙН.

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

• Щелкните по полю «Продавец» в списке «Поля сводной таблицы» и перетащите его в область ROWS.

• Нажмите на поле Месяц в списке Поля сводной таблицы и перетащите его также в область ROWS.

• Нажмите на сумму заказа и перетащите его в область ∑ ЗНАЧЕНИЯ.

Щелкните по полю «Продавец» в списке «Поля сводной таблицы» и перетащите его в область ROWS.

Нажмите на поле Месяц в списке Поля сводной таблицы и перетащите его также в область ROWS.

Нажмите на сумму заказа и перетащите его в область ∑ ЗНАЧЕНИЯ.

Ваша сводная таблица готова. Вы можете изменить макет сводной таблицы, просто перетаскивая поля по областям. Вы можете выбрать / отменить выбор полей в списке Поля сводной таблицы, чтобы выбрать данные, которые вы хотите отобразить.

Фильтрация данных в сводной таблице

Если вам необходимо сосредоточиться на подмножестве данных вашей сводной таблицы, вы можете отфильтровать данные в сводной таблице на основе подмножества значений одного или нескольких полей. Например, в приведенном выше примере вы можете фильтровать данные на основе поля «Диапазон», чтобы вы могли отображать данные только для выбранных регионов.

Существует несколько способов фильтрации данных в сводной таблице.

• Фильтрация с использованием фильтров отчетов.
• Фильтрация с использованием слайсеров.
• Фильтрация данных вручную.
• Фильтрация с использованием Label Filters.
• Фильтрация с использованием Value Filters.
• Фильтрация с использованием фильтров даты.
• Фильтрация с использованием Top 10 Filter.
• Фильтрация с использованием временной шкалы.

Вы узнаете об использовании фильтров отчетов в этом разделе и срезов в следующем разделе. Другие параметры фильтрации см. В учебном руководстве по сводным таблицам Excel.

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

• Перетащите поле Регион в область ФИЛЬТРЫ.
• Перетащите поле Продавец в область ROWS.
• Перетащите поле Месяц в область КОЛОННЫ.
• Перетащите поле Order Amount в область AL VALUES.

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

Как вы можете наблюдать,

• Значения продавца отображаются в строках.

• Значения месяца отображаются в столбцах.

• Фильтр регионов появляется сверху, по умолчанию выбрано ВСЕ.

• Суммирующим значением является сумма суммы заказа.

  • Сумма суммы заказа в зависимости от продавца отображается в столбце Общая сумма.

  • Сумма суммы заказа По месяцам появляется в строке Итого.

• Нажмите на стрелку в фильтре региона.

Значения продавца отображаются в строках.

Значения месяца отображаются в столбцах.

Фильтр регионов появляется сверху, по умолчанию выбрано ВСЕ.

Суммирующим значением является сумма суммы заказа.

Сумма суммы заказа в зависимости от продавца отображается в столбце Общая сумма.

Сумма суммы заказа По месяцам появляется в строке Итого.

Нажмите на стрелку в фильтре региона.

Появляется выпадающий список со значениями поля Region.

• Установите флажок Выбрать несколько элементов. Флажки появятся для всех значений. По умолчанию все флажки отмечены.

• Снимите флажок (Все). Все коробки будут сняты.

• Установите флажки — Юг и Запад.

Установите флажок Выбрать несколько элементов. Флажки появятся для всех значений. По умолчанию все флажки отмечены.

Снимите флажок (Все). Все коробки будут сняты.

Установите флажки — Юг и Запад.

• Нажмите кнопку ОК. Данные, относящиеся только к южному и западному регионам, будут обобщены.

Нажмите кнопку ОК. Данные, относящиеся только к южному и западному регионам, будут обобщены.

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

Использование слайсеров в сводной таблице

Фильтрация с использованием слайсеров имеет много преимуществ —

• Вы можете иметь несколько фильтров, выбрав поля для срезов.

• Вы можете визуализировать поля, к которым применяется фильтр (один слайсер на поле).

• У слайсера будут кнопки, обозначающие значения поля, которое он представляет. Вы можете нажать на кнопки слайсера, чтобы выбрать / отменить выбор значений в поле.

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

• Вы можете использовать общий слайсер для нескольких сводных таблиц и / или сводных диаграмм.

• Вы можете скрыть / показать слайсер.

Вы можете иметь несколько фильтров, выбрав поля для срезов.

Вы можете визуализировать поля, к которым применяется фильтр (один слайсер на поле).

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

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

Вы можете использовать общий слайсер для нескольких сводных таблиц и / или сводных диаграмм.

Вы можете скрыть / показать слайсер.

Чтобы понять использование слайсеров, рассмотрите следующую сводную таблицу.

Предположим, вы хотите отфильтровать эту сводную таблицу на основе полей — Регион и Месяц.

• Нажмите на вкладку «АНАЛИЗ» под «Сменные инструменты» на ленте.
• Нажмите «Вставить слайсер» в группе «Фильтр».

Откроется диалоговое окно «Вставить слайсеры». Он содержит все поля из ваших данных.

• Установите флажки Регион и Месяц.

• Нажмите кнопку ОК. Слайсеры для каждого из выбранных полей отображаются со всеми значениями, выбранными по умолчанию. Инструменты на слайсере появляются на ленте для работы с настройками слайсера.

Нажмите кнопку ОК. Слайсеры для каждого из выбранных полей отображаются со всеми значениями, выбранными по умолчанию. Инструменты на слайсере появляются на ленте для работы с настройками слайсера.

Как вы можете заметить, каждый слайсер имеет все значения поля, которое он представляет, и значения отображаются в виде кнопок. По умолчанию все значения поля выбраны и, следовательно, все кнопки подсвечены.

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

• Нажмите на Юг в области Slicer. В области слайсера будет выделен только юг.

• Удерживайте нажатой клавишу Ctrl и нажмите «Запад» в разделе «Регион».

• Нажмите на февраль в месячном слайсере.

• Держите нажатой клавишу Ctrl и нажмите на март в месячном срезе. Выбранные значения в слайсерах подсвечиваются. Сводная таблица будет суммирована для выбранных значений.

Нажмите на Юг в области Slicer. В области слайсера будет выделен только юг.

Удерживайте нажатой клавишу Ctrl и нажмите «Запад» в разделе «Регион».

Нажмите на февраль в месячном слайсере.

Держите нажатой клавишу Ctrl и нажмите на март в месячном срезе. Выбранные значения в слайсерах подсвечиваются. Сводная таблица будет суммирована для выбранных значений.

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