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

Рис. 5.1. Добавление таблицы в модель данных

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

Нажимаем кнопку Add to Data Model.

На экране появляется окно Power Pivot.

Здесь мы видим все таблицы (рис. 5.2).

И мы можем просмотреть содержимое этих таблиц. Напомним, что на листах Excel не отображается содержимое таблиц, загруженных из базы данных. Одна из причин — ограничение на размер таблицы Excel.

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

Рис. 5.2. Просмотр таблиц в Power Pivot

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

Итак, с помощью Power Pivotмы можем работать с любой таблицей, не обращаясь к Excel и не упираясь в его ограничения.

Внешний вид интерфейса Power Pivotочень похож на традиционную электронную таблицу (рис. 5.3).

Здесь тоже показаны номера строк.

А вот столбцы не обозначены привычными латинскими буквами. Здесь только названия полей — как в базе данных.

Рис. 5.3. Просмотр содержимого таблицы

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

Пришло время немного подробнее познакомиться с технологиями «модели данных». Есть пара статей на эту тему сайте поддержки:

https://support.microsoft.com

1. Статья «Create a memory-efficient Data Model using Excel and the Power Pivot add-in», раздел «Compression ratios and the in-memory analytics engine».

2. Статья «Data Model specification and limits».

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

Кроме того, можно просмотреть в Википедии начало статьи «In-memory database» на английском и русском языках.

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

Переходим в режим просмотра модели данных.

В верхнем меню Power Pivotвыбираем следующий пункт:

Home — View — Diagram View.

Теперь в нашей модели две связи между таблицами (рис. 5.4).

В режиме Диаграммы модели данныхможно перетаскивать таблицы и менять их размеры.

Рис. 5.4. Диаграмма (схема) модели данных

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

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

Перетаскиваем поле DisciplineEvent(Дисциплина + соревнование) из таблицы Events(Соревнование) на поле DisciplineEventв таблице Medals(Медали).

Новая связь выделена рис. 5.5.

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

Задание. Создайте описанную выше связь в модели данных.

Итак, новую связь мы создали. Линию со стрелкой на диаграмме мы видим. А где же ключевое поле?

Чтобы выделить на экране связанные поля, щёлкнем по линии со стрелкой. Теперь всё понятно.

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

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

Сохраняем работу и закрываем окно Power Pivot.

Проверим, как работает связь по соревнованиям.

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

Настроим поля, как показано на рис. 5.7.

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

Теперь мы можем создать в сводной таблице следующую иерархию: «Вид спорта — Спортивная дисциплина — Соревнование».

Рис. 5.7. Иерархия в сводной таблице

Задание. Создайте сводную таблицу (рис. 5.7). Настройте фильтр, чтобы выбрать только водные виды спорта Aquatics. Выясните, какие соревнования проводились по каждой спортивной дисциплине.

Для создания связи между таблицами может потребоваться создать уникальное вычисляемое поле. Для этого служат формулы DAX — Data Analysis Expressions — Выражения анализа данных.

Чтобы просветиться насчёт DAX, почитаем статью «Выражения анализа данных (DAX) в Power Pivot» на сайте поддержки:

https://support.office.com

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

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

— Hosts;

— Medals.

Для организации связи между таблицами нам нужно будет указать ключевое поле. Это поле должно быть в обеих таблицах. Нам потребуется указать год и время года. Мы соединим эти сведения в одном поле.

Вызываем Power Pivot:

Power Pivot — Data Model — Manage.

Переключаемся в режим работы с данными:

Home — View — Data View.

Выбираем таблицу Hosts(рис. 6.1).

Рис. 6.1. Выбор таблицы

Рассмотрим указанные таблицы. Необходимые сведения в них есть, но в разных форматах и в разных колонках.

Задание. Рассмотрите указанные таблицы и выясните, в каком виде в них содержатся интересующие нас сведения — год и время года.

Переходим к практическому знакомству с DAX.

Мы объединим содержимое двух столбцов в таблице Hosts:

— Edition — год проведения Олимпиады;

— Season — время года (зимние или летние игры).

Будем использовать функцию объединения данных из нескольких столбцов:

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

В русском варианте программы эта команда звучит так:

=СЦЕПИТЬ ([Год проведения]; [Сезон]).

Добавляем вычисляемое поле. Для этого находим крайний правый столбец под красноречивым названием:

Add Column — Добавить столбец.

Щёлкаем по заголовку столбца.

Начинаем вводить выражение DAXв строке формул. При вводе формулы DAXвыводится подсказка и список доступных команд (рис. 6.2).

Как видим, программа начинает подсказки после ввода первых трёх букв названия команды.

Рис. 6.2. Начало ввода формулы

Дважды щёлкаем по нужной подсказке и получаем название функции. Теперь в строке формул появляется полное название функции и подсказка по аргументам (рис. 6.3).

Рис. 6.3. Подсказка по функции

Далее выбираем аргументы.

Щёлкаем мышкой по заголовку столбца Edition.

В строке формул появляется ссылка на выбранный столбец (рис. 6.4).

Рис. 6.4. Первый аргумент функции

Ставим запятую и переходим к вводу второго аргумента.

Щёлкаем по заголовку столбца Season.

Получаем второй аргумент функции в строке формул (рис. 6.5).

Рис. 6.5. Второй аргумент функции

Нажимаем клавишу Enter.

Появляется новый столбец. Смотрим на значения в ячейках последнего столбца (рис. 6.6).