Валентин Арьков - Бизнес-аналитика. Сводные таблицы. Часть 1

Задание. Вызовите обновление сводной таблицы, нажав комбинацию клавиш.

Но и это ещё не всё! Нам дают возможность обновлять сводную таблицу при каждом открытии файла. Для этого выбираем в верхнем меню (рис. 6.26) следующий пункт:

Pivot Table Tools — Analyze — Pivot Table — Options — Options.

Рис. 6.26. Параметры сводной таблицы

Появляется диалоговое окно Pivot Table options(Настройка сводной таблицы), см. рис. 6.27. Открываем вкладку Data. Выбираем пункт

Refresh data when opening the file

Обновлять данные при открытии файла.

Рис. 6.27. Настройка автоматического обновления

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

В сводных таблицах можно организовать ИЕРАРХИЮ. По сути, это представление наших обобщённых показателей в виде дерева. В иерархии может быть несколько уровней. Можно сказать, что это вложенность уровней друг в друга.

С иерархией мы встречаемся и в обыной жизни. Например, в почтовом адресе.

Задание. Сделайте зарисовку схемы административного деления России в виде иерархии — начиная от страны в целом и заканчивая районом города. Сколько уровней можно здесь указать?

В наших примерах иерархия образуется естественным образом. Фактически, у нас уже есть две иерархии: «города — магазины» и «категории — товары». В одном городе есть несколько магазинов. В рамках одной категории товаров есть несколько наименований. Количество уровней иерархии может быть гораздо больше.

Задание. Сделайте зарисовки для иерархий по магазинам и товарам. Укажите на зарисовках все варианты значений полей на каждом уровне.

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

Сделаем первый опыт и организуем иерархию «города — магазины». Создаём новую сводную таблицу. Перетаскиваем название города и название магазина в раздел столбцов (рис. 7.1).

Рис. 7.1. Создание иерархии

Получаем сводную таблицу, где вначале идут города, а затем магазины (рис. 7.2). Это понятно. Это логично. И эту логичность может оценить только читатель. Компьютер обычно не в состоянии организовать иерархию грамотно и правильно. Тем более, что её строят с определённой целью, а не просто ради того, чтобы что-то построить, сдать и забыть.

Иерархию можно организовать при создании OLAP куба в хранилище данных. Тогда сводная таблица по SQL-запросу получит итоговые показатели в многомерной таблице с указанием иерархии. В дополнение к такой готовой иерархии пользователь может создать свою, если понадобится.

Рис. 7.2. Таблица с иерархией

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

1) «города — магазины»;

2) «категории — товары»;

3) «города — магазины» и «категории — товары».

Мы организовали иерархию. Теперь с неё можно работать. Мы можем «сворачивать» и «разворачивать» нашу иерархию.

Другие названия для этих действий такие:

— Expand — Детализация — Развернуть;

— Collapse — Консолидация — Свернуть.

Возможно, вам встретятся и другие варианты названий. Главное, вовремя распознать, что речь идёт про иерархию.

В любом случае, речь идёт о том, насколько подробно мы хотим рассматривать наши итоговые показатели. Или насколько мы хотим их «укрупнить», чтобы увидеть всю картину в целом, без лишних подробностей. По сути, это выбор того уровня иерархии, который мы хотим вывести на экран.

Объяснение иерархии длинное, а вот работа с иерархией очень простая. Уровни сводной таблицы можно сворачивать и разворачивать. Для этого достаточно щёлкнуть по кнопке с символом плюса [+]или минуса [-]. На рис. 7.2 можно увидеть кнопочку с минусом [-]. Она находится слева от названия каждого города.

Нажимаем эту кнопку, и все данные по одному городу сворачиваются до одного столбца (рис. 7.3). Что интересно, в этом случае перестраивается вся сводная таблица. После сворачивания или разворачивания номера столбцов Excel по-прежнему идут в алфавитном порядке:

A — B — C — D — Е — F — G.

Пропавших (скрытых) столбцов не будет.

Рис. 7.3. Сворачивание уровней

Задание. Ознакомьтесь с операциями сворачивания и разворачивания уровней, нажимая кнопки [+]и [-].

Второй способ работы с иерархией — это лента ( Ribbon) — верхнее меню для быстрого доступа к популярным инструментам. Щёлкаем по строчке с городами. В верхнем меню выбираем следующий пункт:

PivotTable Tools — Analyze — Active Field — Collapse Field.

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

Рис. 7.4. Сворачивание выбранного поля

Все подробности нам свернули. Остались только общие итоги по городам. Слева от названия каждого города появилась кнопка с плюсиком [+](рис. 7.5).

Рис. 7.5. Результаты сворачивания поля

Задание. Ознакомьтесь с операциями сворачивания и разворачивания уровней, нажимая кнопки Expand Fieldи Collapse Fieldв разделе Active Field.

Третий способ работы с иерархией — это контекстное меню. Щёлкаем правой кнопкой по заголовку столбца. Выбираем Expand/Collapse. Нам предлагают аж шесть вариантов детализации:

— Expand;

— Collapse;

— Expand Entire Field;

— Collapse Entire Field;

— Collapse to «Город»;

— Expand to «Название магазина».

Чтобы познакомиться с этими операциями, нам предстоит немного поэкспериментировать.

Рис. 7.6. Меню детализации

Задание. Ознакомьтесь с операциями сворачивания и разворачивания уровней, используя контекстное меню Expand/Collapse. Запишите в отчёте, что выполняется при вызове каждого из шести пунктов меню.

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

Создадим новую сводную таблицу на новом листе. Перетащим поля в раздел столбцов и расположим их неправильном порядке: название города внизу, название магазина вверху (рис. 7.7).

Рис. 7.7. Неправильный порядок полей в иерархии

В результате сводная таблица становится бессмысленной и нечитаемой (рис. 7.8). Столбец с названием магазина делится на две колонки: название города и общий итог. В рамках одного конкретного магазина нет смысла строить иерархию по разным городам. Это только загромождает нашу таблицу и никакой пользы не приносит.