Валентин Арьков - Анализ и визуализация данных в электронных таблицах

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

Рис. 6.6. Объединение столбцов

Задание. Введите формулу и объедините указанные столбцы.

Название столбца выбирается по умолчанию:

Calculated Column 1.

Это выражение мало что нам сообщает.

Переименуем новый столбец.

Дважды щёлкнем по заголовку столбца.

Введём новое название:

EditionID.

Это будет идентификатор года проведения Олимпийских игр (рис. 6.7).

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

Теперь нам нужно создать ключевое поле в таблице Medals.

Выбираем таблицу Medals.

Создаём новый столбец.

Вводим формулу извлечения номера года из даты:

= YEAR ([Edition]).

Получаем в новой колонке номер года (рис. 6.8).

Рис. 6.8. Номер года

Переименуем новый столбец:

YEAR.

Теперь будет понятно, что в этом столбце указан год (рис. 6.9).

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

Теперь создадим ещё один новый столбец:

Add Column.

У нас есть год и время года. Объединим их в один столбец.

Вводим формулу:

=CONCATENATE ([Year]; [Season]).

Получаем новый, объединённый столбец (рис. 6.10).

Рис. 6.10. Объединённый столбец

Название столбца по умолчанию снова назначено не слишком информативное.

Будет лучше, если название ключевого поля будет совпадать в обеих таблицах.

Переименуем столбец:

EditionID.

Результаты представлены на рис. 6.11.

Рис. 6.11. Ключевое поле

Мы будем создавать связь между таблицами типа «один ко многим». Для каждой Олимпиады у нас есть уникальная привязка ко времени: «Год + Время года».

Проверим, насколько корректно у нас получилось создать ключевое поле.

Отсортируем таблицу Hostsпо полю EditionID.

Проверим уникальность поля EditionID.

Рис. 6.12. Сортировка по ключевому полю

Задание. Проведите сортировку таблицы Hostsпо ключевому полю.

Данные отсортированы.

В таблице Hostsне так много строк. Их всего 47.

Так что просмотрим их вручную.

Пролистаем таблицу. На глаз не видно повторяющихся дат.

Рис. 6.13. Отсортированные данные

Задание. Проверьте уникальность данных в столбце ключевого поля.

Другое дело — сведения о медалях.

Для каждой записи о выигранных медалях может быть только одно значение ключевого поля «Год + Время года». А вот медалей каждый год может много. Даже очень много.

Поэтому у нас будет связь «один ко многим». Можно даже сказать: «один к очень многим».

Отсортируем таблицу Medalsпо полю EditionID.

Пролистаем таблицу и проверим уникальность поля EditionID.

Строк здесь достаточно много, больше 32 тысяч. Нам для демонстрации повторения значений достаточно пролистать несколько страниц (экранов).

Рис. 6.14. Отсортированные данные

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

У нас всё готово. Мы организовали ключевое поле в обеих таблицах.

Пора создавать связь между таблицами Medalsи Hostsпо полю EditionID.

Переключаемся в режим Diagram View.

Перетаскиваем поле EditionIDиз таблицы Hostsна поле EditionIDв таблице Medals.

Получаем связь между таблицами.

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

Обратим внимание на направление стрелки. Она идёт от таблицы Hostsк таблице Medals.

Рис. 6.15. Новая связь между таблицами

Задание. Создайте описанную связь между таблицами и убедитесь в правильности настроек.

Проверим, как работают новые связи в модели данных.

Создадим новый лист и назовём его Cities.

Создадим на этом листе сводную таблицу «Страна — Город — Медали».

В качестве исходных данных указываем модель данных текущей рабочей книги:

Choose the data you want to analyze — Use this workbook’s Data Model.

Расположим сводную таблицу на текущем листе (рис. 6.16):

Choose where you want the PivotTable report to be placed — Existing Worksheet.

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

Задание. Создайте новую сводную таблицу из модели данных.

Настраиваем поля сводной таблицы следующим образом:

— Rows — (Hosts) NOC, City;

— Columns — (Medals) Gender

— Values — (Medals) Medal.

Рассмотрим полученную сводную таблицу (рис. 6.17).

По каждой стране-организатору проведения Олимпийских игр мы видим список городов, в которых проходили соревнования. Страны и городы взяты из таблицы Hosts. Сведения о медалях и спортсменах — из таблицы Medals.

Рис. 6.17. Сводная таблица

Задание. Настройте поля сводной таблицы (рис. 6.17).

Основной способ создания иерархии — это модель данных в Excel или OLAP-куб в хранилище данных. В этом случае иерархия создаётся заранее и используется во всех сводных таблицах. Обычно это продуманная, логически обоснованная организация данных. И работать она будет во всех сводных таблицах.

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

ad hoc.

Задание. Изучите в Википедии статью «Ad hoc». Выясните, что означает это выражение и какими словами его можно перевести.

Рассмотрим пример создания иерархии в модели данных.

Запускаем Power Pivot:

Power Pivot — Data Model — Manage.

Переключаемся в режим просмотра диаграммы, нажав кнопку Диаграмма в правом нижнем углу окна:

Diagram.

Выбираем таблицу соревнований:

Events.

Выделяем поля Sport, Discipline, Event — нажимая и удерживая клавишу Ctrlна клавиатуре.

Щёлкаем по таблице правой кнопкой мыши и выбираем в контекстном меню создание иерархии:

Create Hierarchy.

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

Теперь в таблице Eventsпоявилась иерархия (рис. 7.2).

Обратим внимание, что выбранные поля остались в списке, а иерархии эти названия встречаются второй раз.

Рис. 7.2. Иерархия в таблице Events

Задание. Изучите полученную иерархию.

По умолчанию наша иерархия получила абстрактное название:

Hierarchy1.

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

— Презентация;