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

В этом разделе самоучителя дана пошаговая инструкция, как создать продвинутую сводную таблицу в современных версиях 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 ... ... ... ... ...

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

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

    ЗАМЕЧАНИЕ: Если выделить одну ячейку в диапазоне данных, то Excel автоматически определит диапазон для создания сводной таблицы и расширит выделение. Для того, чтобы Excel выбрал диапазон правильно, должны быть выполнены следующие условия:

    • Каждый столбец в диапазоне данных должен иметь уникальный заголовок.
    • В диапазоне данных не должно быть пустых строк.
  2. Кликните по кнопке Сводная таблица (Pivot Table) в разделе Таблицы (Table) на вкладке Вставка (Insert) Ленты меню Excel.

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

  3. Откроется диалоговое окно Создание сводной таблицы (Create PivotTable), как показано на рисунке ниже.

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

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

    Здесь же можно выбрать, где должна быть размещена создаваемая сводная таблица. Можно поместить сводную таблицу На существующий лист (Existing Worksheet) или На новый лист (New Worksheet). Нажмите ОК.

  4. Появится пустая сводная таблица и панель Поля сводной таблицы (Pivot Table Field List), в которой уже содержатся несколько полей данных. Обратите внимание, что эти поля – заголовки из таблицы исходных данных.

    Мы хотим, чтобы сводная таблица показывала итоги продаж помесячно с разбиением по регионам и по продавцам. Для этого в панели Поля сводной таблицы (Pivot Table Field List) сделайте вот что:

    • Перетащите поле Date в область Строки (Row Labels);
    • Перетащите поле Amount в область Σ Значения (Σ Values);
    • Перетащите поле Region в область Колонны (Column Labels);
    • Перетащите поле Sales Rep. в область Колонны (Column Labels).

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

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

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

    Чтобы сгруппировать данные помесячно:

    • Кликните правой кнопкой мыши по любой дате в крайнем левом столбце сводной таблицы;
    • В появившемся контекстном меню нажмите Группировать (Group);
    • Появится диалоговое окно Группирование (Grouping) для дат (как показано на рисунке ниже). В поле С шагом (By) выберите Месяцы (Months). Кстати, сгруппировать даты и время можно и по другим временным периодам, например, по кварталам, дням, часам и так далее;

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

    • Нажмите ОК.

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

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

Чтобы улучшить вид сводной таблицы, следует настроить форматирование. Например, если для значений в столбцах BG настроить денежный формат, то прочесть сводную таблицу станет гораздо легче.

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

Фильтры в сводной таблице позволяют отобразить информацию для одного значения или избирательно для нескольких значений из имеющихся полей данных. Например, в показанной выше сводной таблице мы сможем просматривать данные только для региона продаж North или только для региона South.

Чтобы отобразить данные только для региона продаж North, в панели Поля сводной таблицы (Pivot Table Field List) перетащите поле Region в область Фильтры (Report Filters).

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

Поле Region появится вверху сводной таблицы. Откройте выпадающий список в этом поле и выберите в нём регион North. Сводная таблица (как показано на картинке ниже) покажет значения только для региона North.

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

Вы можете быстро переключиться на просмотр данных только для региона South – для этого нужно в выпадающем списке в поле Region выбрать South.

14.02.2016 10:25
4459

3 комментария

28.02.2016 15:07
Здравствуйте! Во первых огромное спасибо за замечательный сайт! Хотелось бы узнать будет статья про сводные таблицы в excel 2003? Конечно 21 век на дворе, но что поделать, не все компании это понимают.
28.02.2016 16:24
Здравствуйте! Большое спасибо! Да, будет перевод двух статей — первая и вторая. Определенных сроков назвать не смогу, времени пока не хватает. Можете попробовать изучить английскую версию, воспользовавшись, к примеру, переводчиком, встроенным в браузер.

Как вариант, полистайте дополнительно это пособие по сводным таблицам в Excel 2003, но опять же на английском. Там можно последовательно переходить от одной статьи к другой (есть ссылка в каждой статье), либо справа есть меню со списком всех статей данного самоучителя.
29.02.2016 17:54
Спасибо!