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

Queries & Connections.

Здесь же указано количество загруженных строк.

Назначим название нашей загруженной таблице городов и рабочему листу:

Hosts.

Рис. 3.12. Загруженная таблица городов

Задание.Загрузите таблицу олимпийских городов из интернет.

Загадочное английское слово HOSTна русский язык переводится с трудом. В каждой ситуации оно переводится разными словами или выражениями. А встречается оно достаточно часто и по разным поводам. Познакомимся с этим термином — в качестве упражнения и для расширения кругозора.

Обычное поведение пользователя — «погуглить». То есть поискать в интернете фразу типа «host перевод».

Поисковая машина Яндекса даёт первым результатом Переводчик Яндекса. Получаем тридцать вариантов перевода (рис. 3.13). Обратим внимание, что в каждом варианте перевода даётся краткая формулировка значения на английском.

Рис. 3.13. Перевод Яндекса

Задание. Изучите результаты переводчика Яндекса.

Попробуем поискать не в Яндексе, а в Гугле. Конечно же, первый результат поиска — Переводчик Гугл. Здесь тоже самые разные русские эквиваленты. А вот в левой части окна мы видим английский текст. Это английский толковый словарь. То есть объяснение смысла английского слова с помощью английских слов. И здесь становится понятно, что у слова HOSTвсего пять разных значений.

Остаётся выбрать из этих пяти вариантов то значение, которое подходит для разговора об Олимпийских играх. А затем нужно подобрать адекватное русское слово или выражение, которое передаёт смысл.

Рис. 3.14. Перевод Гугл

Задание. Выберите подходящее значение слова HOSTи подберите русский эквивалент.

Мы загрузили таблицы из базы данных. В этих данных была заложена связь между двумя таблицами.

Power Pivotпозволяет вывести на экран изображение схемы связей между таблицами.

Чтобы запустить Power Pivot, выбираем в верхнем меню:

Power Pivot — Data Model — Manage(рис. 4.1).

Рис. 4.1. Запуск Power Pivot

Появляется новое окно:

Power Pivot for Excel(рис. 4.2).

Это не просто диалоговое окно. Это надстройка со своими особыми функциями.

Чтобы вывести на экран схему модели данных, выбираем следующий пункт в верхнем меню:

Home — View — Diagram View.

Можно также нажать кнопку Diagramс таким же значком в правом нижнем углу окна.

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

Между таблицами Disciplinesи Medalsимеется связь «один ко многим». На этой схеме она показана следующими символами:

1 —> *.

Рис. 4.2. Схема модели данных

Рассмотрим список доступных таблиц.

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

Рассмотрим список полей сводной таблицы:

PivotTable Fields.

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

All.

Сравним списки выбранных (активных) и всех доступных полей/таблиц (рис. 4.3). Активные поля, то есть задействованные в создании сводной таблицы, выделены жирным шрифтом.

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

Рис. 4.3. Поля сводной таблицы

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

Перетащим поле Sportиз новой таблицы Sportsв область строк сводной таблицы. Поместим его над полем Discipline.

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

Relationships between tables may be needed(рис. 4.4).

Это предупреждение выводится на жёлтом фоне, чтобы обратить на него наше внимание.

Рис. 4.4. Приглашение к созданию связей

Рассмотрим две таблицы в списке исходных полей для сводной таблицы:

— Disciplines — Спортивные дисциплины;

— Sports — Виды спорта.

В обеих таблицах есть одно и то же поле:

SportID — идентификатор вида спорта (рис. 4.5).

Пришло время прояснить ситуацию. Нам нужно разобраться, что такое — «вид спорта» и «спортивная дисциплина». Придётся поискать ответ в интернете.

Рис. 4.5. Общее поле

Задание. Выясните, как связаны между собой ВИД СПОРТА и СПОРТИВНАЯ ДИСЦИПЛИНА.

Теперь попробуем создать связь. Нажимаем кнопку

Create.

Открывается диалоговое окно создания связи:

Create Relationship.

Настроим связь следующим образом (рис. 4.6):

— Table — Disciplines;

— Column (Foreign) — SportID;

— Related Table — Sports;

— Related Column (Primary) — SportID.

В связанной таблице указываем первичный ключ — идентификатор вида спорта.

Нажимаем ОК.

Рис. 4.5. Создание связи между таблицами

Задание. Настройте связь между таблицами (рис. 4.5).

Сводная таблица изменилась и стала более осмысленной (рис. 4.6). Например, две спортивные дисциплины — фигурное катание Figure skatingи конькобежный спорт Speed skating — входят в коньковые виды спорта Skating.

Это так называемая ИЕРАРХИЯ.

Рис. 4.6. Сводная таблица после создания связи

Задание. Рассмотрите иерархию в сводной таблице.

Задание. Отключите фильтрацию по строкам и выясните, в каких видах спорта присутствует иерархия.

Мы работаем с реальными данными. Конечно, их хорошо подготовили к обработке, но это настоящие реальные данные.

Можно познакомиться с иерархией «Вид спорта — Дисциплина». Достаточно найти в Википедии соответствующую статью.

Ну а чтобы увидеть все виды спорта и дисциплины, отключим все виды фильтрации, нажав в меню фильтрации пункт Clear Filter from…

Задание. Изучите в Википедии статью «Олимпийские виды спорта» и сравните перечень видов спорта с иерархией в сводной таблице.

Мы рассмотрели, как настроить связь между таблицами в режиме диалога.

Второй способ управления связями — это использование надстройки Power Pivot.В этом случае мы просто перетаскиваем поля на схеме модели данных.

Продемонстрируем этот приём работы.

Добавим таблицу Hostsв модель данных. Для этого переходим на лист Hosts. Затем выбираем в верхнем меню следующий пункт:

Power Pivot — Tables — Add to Data Model.

Пока просто подводим курсор к этой кнопке и читаем всплывающую подсказка. Это сообщение поясняет суть предстоящей операции (рис. 5.1).

Здесь говорится, в модель данных будет добавлена таблица Excel, расположенная на текущем рабочем листе. То есть мы должны находиться на том листе, где лежит наша таблица. К тому же, это должна быть не просто таблица в виде диапазона ячеек. Это должна быть таблица Excel. Это объект, специальным образом отформатированный, с заголовками столбцов и с названием (именем таблицы).