В этом разделе самоучителя дана пошаговая инструкция, как создать продвинутую сводную таблицу в современных версиях Excel (2007 и более новых). Для тех, кто работает в более ранних версиях Excel, рекомендуем статью: Как создать продвинутую сводную таблицу в Excel 2003?
В качестве исходных данных для построения сводной таблицы, мы используем таблицу данных о продажах компании в первом квартале 2016 года.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Date | Invoice Ref | Amount | Sales Rep. | Region |
| 2 | 01/01/2016 | 2016-0001 | $819 | Barnes | North |
| 3 | 01/01/2016 | 2016-0002 | $456 | Brown | South |
| 4 | 01/01/2016 | 2016-0003 | $538 | Jones | South |
| 5 | 01/01/2016 | 2016-0004 | $1,009 | Barnes | North |
| 6 | 01/02/2016 | 2016-0005 | $486 | Jones | South |
| 7 | 01/02/2016 | 2016-0006 | $948 | Smith | North |
| 8 | 01/02/2016 | 2016-0007 | $740 | Barnes | North |
| 9 | 01/03/2016 | 2016-0008 | $543 | Smith | North |
| 10 | 01/03/2016 | 2016-0009 | $820 | Brown | South |
| 11 | … | … | … | … | … |
В следующем примере мы создадим сводную таблицу, которая показывает итоги продаж помесячно за год с разбивкой по регионам и по продавцам. Процесс создания этой сводной таблицы описан ниже.
- Выделите любую ячейку в диапазоне или весь диапазон данных, который нужно использовать для построения сводной таблицы.ЗАМЕЧАНИЕ: Если выделить одну ячейку в диапазоне данных, то Excel автоматически определит диапазон для создания сводной таблицы и расширит выделение. Для того, чтобы Excel выбрал диапазон правильно, должны быть выполнены следующие условия:
- Каждый столбец в диапазоне данных должен иметь уникальный заголовок.
- В диапазоне данных не должно быть пустых строк.
- Кликните по кнопке Сводная таблица (Pivot Table) в разделе Таблицы (Table) на вкладке Вставка (Insert) Ленты меню Excel.
- Откроется диалоговое окно Создание сводной таблицы (Create PivotTable), как показано на рисунке ниже.
Убедитесь, что выбранный диапазон охватывает именно те ячейки, которые должны быть использованы для создания сводной таблицы.Здесь же можно выбрать, где должна быть размещена создаваемая сводная таблица. Можно поместить сводную таблицу На существующий лист (Existing Worksheet) или На новый лист (New Worksheet). Нажмите ОК. - Появится пустая сводная таблица и панель Поля сводной таблицы (Pivot Table Field List), в которой уже содержатся несколько полей данных. Обратите внимание, что эти поля – заголовки из таблицы исходных данных.Мы хотим, чтобы сводная таблица показывала итоги продаж помесячно с разбиением по регионам и по продавцам. Для этого в панели Поля сводной таблицы (Pivot Table Field List) сделайте вот что:
- Перетащите поле Date в область Строки (Row Labels);
- Перетащите поле Amount в область Σ Значения (Σ Values);
- Перетащите поле Region в область Колонны (Column Labels);
- Перетащите поле Sales Rep. в область Колонны (Column Labels).
- В итоге сводная таблица будет заполнена ежедневными значениями продаж для каждого региона и для каждого продавца, как показано ниже.
Чтобы сгруппировать данные помесячно:
- Кликните правой кнопкой мыши по любой дате в крайнем левом столбце сводной таблицы;
- В появившемся контекстном меню нажмите Группировать (Group);
- Появится диалоговое окно Группирование (Grouping) для дат (как показано на рисунке ниже). В поле С шагом (By) выберите Месяцы (Months). Кстати, сгруппировать даты и время можно и по другим временным периодам, например, по кварталам, дням, часам и так далее;
- Нажмите ОК.
Как и требовалось, наша сводная таблица (смотрите картинку ниже) теперь показывает итоги продаж по месяцам с разбивкой по регионам и по продавцам.
Чтобы улучшить вид сводной таблицы, следует настроить форматирование. Например, если для значений в столбцах B – G настроить денежный формат, то прочесть сводную таблицу станет гораздо легче.
Фильтры в сводной таблице
Фильтры в сводной таблице позволяют отобразить информацию для одного значения или избирательно для нескольких значений из имеющихся полей данных. Например, в показанной выше сводной таблице мы сможем просматривать данные только для региона продаж North или только для региона South.
Чтобы отобразить данные только для региона продаж North, в панели Поля сводной таблицы (Pivot Table Field List) перетащите поле Region в область Фильтры (Report Filters).
Поле Region появится вверху сводной таблицы. Откройте выпадающий список в этом поле и выберите в нём регион North. Сводная таблица (как показано на картинке ниже) покажет значения только для региона North.
Вы можете быстро переключиться на просмотр данных только для региона South – для этого нужно в выпадающем списке в поле Region выбрать South.
Урок подготовлен для Вас командой сайта office-guru.ru
Источник: http://www.excelfunctions.net/advanced-pivot-table.html
Перевел: Антон Андронов
Правила перепечатки
Еще больше уроков по Microsoft Excel
