Задание. Сделайте зарисовки для иерархий по магазинам и товарам. Укажите на зарисовках все варианты значений полей на каждом уровне.
7.1. Создание иерархии
Иерархию в сводной таблице можно организовать, если перетаскивать несколько полей в один список. Поле, которое окажется выше в этом списке, будет считаться более высоким уровнем иерархии.
Сделаем первый опыт и организуем иерархию «города магазины». Создаём новую сводную таблицу. Перетаскиваем название города и название магазина в раздел столбцов (рис. 7.1).
Рис. 7.1. Создание иерархии
Получаем сводную таблицу, где вначале идут города, а затем магазины (рис. 7.2). Это понятно. Это логично. И эту логичность может оценить только читатель. Компьютер обычно не в состоянии организовать иерархию грамотно и правильно. Тем более, что её строят с определённой целью, а не просто ради того, чтобы что-то построить, сдать и забыть.
Иерархию можно организовать при создании OLAP куба в хранилище данных. Тогда сводная таблица по SQL-запросу получит итоговые показатели в многомерной таблице с указанием иерархии. В дополнение к такой готовой иерархии пользователь может создать свою, если понадобится.
Рис. 7.2. Таблица с иерархией
Задание. Создайте три сводных таблицы, используя следующие иерархии:
1) «города магазины»;
2) «категории товары»;
3) «города магазины» и «категории товары».
7.2. Детализация уровней
Мы организовали иерархию. Теперь с неё можно работать. Мы можем «сворачивать» и «разворачивать» нашу иерархию.
Другие названия для этих действий такие:
7.2. Детализация уровней
Мы организовали иерархию. Теперь с неё можно работать. Мы можем «сворачивать» и «разворачивать» нашу иерархию.
Другие названия для этих действий такие:
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.3. Неудачная иерархия
Если создавать иерархию вручную, это может привести к неприятностям. Расположение полей не в том порядке сделает сводную таблицу совершенно нечитаемой.
Создадим новую сводную таблицу на новом листе. Перетащим поля в раздел столбцов и расположим их неправильном порядке: название города внизу, название магазина вверху (рис. 7.7).
Рис. 7.7. Неправильный порядок полей в иерархии
В результате сводная таблица становится бессмысленной и нечитаемой (рис. 7.8). Столбец с названием магазина делится на две колонки: название города и общий итог. В рамках одного конкретного магазина нет смысла строить иерархию по разным городам. Это только загромождает нашу таблицу и никакой пользы не приносит.
Рис. 7.8. Неправильное построение иерархии
Задание. Создайте иерархию «Магазин Город» в сводной таблице. Сверните и разверните уровни детализации. Поменяйте местами поля в списке Columns. Сверните и разверните уровни детализации.