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

Учебное пособие

Валентин Юльевич Арьков

© Валентин Юльевич Арьков, 2020

ISBN 978-5-4498-4910-6

Создано в интеллектуальной издательской системе Ridero

Предисловие

Перед вами продолжение серии работ, позволяющих познакомиться с основными технологиями бизнес-аналитики. Как ни странно, многое можно сделать в рамках пакета Excel, который теперь выступает в роли знакомого и интуитивно понятного интерфейса к продвинутым инструментам анализа данных.

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

Введение

В предыдущих работах мы рассмотрели базовый инструмент анализа данных — сводные таблицы PivotTables. Затем мы познакомились со средствами ETL (извлечения, преобразования и загрузки данных) — Get & Transform / Power Query.

В данной работе мы сделаем следующий шаг в освоении средств бизнес-аналитики — рассмотрим модель данных и надстройку Power Pivot. Мы познакомимся с ними на примере Microsoft Excel в сочетании с надстройками из программного комплекса Power BI.

Power Pivot позволяет работать с гораздо бóльшими объёмами данных, чем стандартный Excel. Напомним, что в пакете Excel максимальное количество строк на одном листе составляет чуть больше ОДНОГО МИЛЛИОНА. Использование специализированной надстройки снимает это ограничение.

Название Power Pivot говорит о том, что это средство для обобщения данных: Pivot Table — это сводная таблица. Слово Power означает, что это часть программного комплекса Power BI.

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

— Excel;

— Power View;

— Power Map / 3D Maps;

— Power BI Dashboards.

Задание. Прочитайте статью «Power Pivot» в английской версии Википедии. При необходимости используйте машинный перевод, например, в браузере Google Chrome. Выясните, какую модель аналитической обработки реализует Power Pivot.

Модель данных

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

Английское название: Data Model.

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

Модель данных можно импортировать из базы данных либо сформировать вручную.

Надстройка PowerPivot позволяет работать с визуальным преставлением модели данных и создавать связи «один ко многим».

Задание. Прочитайте в Википедии статьи «База данных» и «Система управления базами данных». Выясните, чем отличаются эти два понятия.

Задание. Прочитайте в Википедии статью «Модель данных» и выясните, чем различаются МОДЕЛЬ ДАННЫХ и МОДЕЛЬ БАЗЫ ДАННЫХ.

Задание. Прочитайте в английской версии Википедии статьи «Logical schema» и «Physical schema». Выясните, чем различаются логическая и физическая модели (схемы) базы данных.

Отчёт по работе

Отчёт оформляется в виде многостраничной рабочей книги Excel. Отчёт начинается с титульного листа. Далее идёт оглавление. Затем следуют основные страницы отчёта.

Подробности оформления мы подробно описали в предыдущих пособиях [6]. При необходимости можно обратиться к этим материалам.

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

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

Зачем нужно стараться и правильно называть файлы? Это уважение к себе и к другим.

Этот файл отправится по электронной почте, а то и в систему дистанционного обучения. А там таких файлов видимо-невидимо. Хорошее название файла очень помогает. Так же, как и хорошая тема электронного письма. Так же, как и понятное имя отправителя в электронном письме.

Представьте, что преподаватель получает письмо, где нет темы, отправитель указан как Масяня2009 <mashunya2009@mail.com>, а приложенный файл назван МойОтчёт. xlsx. В тексте письма говорится: «Отчёт по лабам». Поможет ли это в общении?

Задание. Сохраните файл под коротким информативным названием.

Напомним, что результаты выполнения каждого задания нужно отразить в своём отчёте. Например, в задании говорится: «Выясните, какие надстройки для Excel используются в составе Power BI». Это означает не просто «узнать что-то для себя и никому не об этом не рассказывать». Это задание требует, чтобы вы сообщили о результатах своих изысканий в отчёте. Для этого можно завести отдельную страничку.

Задание. Прочитайте в Википедии статью «Power BI» и выясните, какие компоненты входят в состав Power BI Desktop. Опишите результаты изысканий на отдельной странице отчёта.

Анализ данных в PowerPivot

1. Установка или активизация Power Pivot

Надстройка Power Pivot входит в состав многих версий Microsoft Office и Microsoft Excel. Многих, но не всех.

Задание. Прочитайте статью «Where is Power Pivot?» на сайте поддержки:

https://support.office.com

Выясните, входит ли Power Pivot в состав вашей версии пакета Microsoft Office.

Проверим, установлена ли настройка в нашем случае.

Вызываем в верхнем меню

File — Options.

Появляется диалоговое окно Excel Options.

Выбираем раздел Add-ins (рис. 1.1).

В разделе неактивных надстроек находим Microsoft Power Pivot for Excel:

Inactive Application Add-ins.

Обратим внимание на тип надстройки Type:

COM Add-in.

Наша задача — активировать надстройку и увидеть её в разделе активных надстроек:

Active Application Add-ins.

Рис. 1.1. Список надстроек Excel

Задание. Проверьте наличие надстройки Power Pivot.

Находим в нижней части окна выпадающий список Manage.

Выбираем в списке наш тип надстройки COM Add-ins (рис. 1.2).

Нажимаем кнопку

Go.

Рис. 1.2. Выбор типа надстройки

Задание. При необходимости активируйте надстройку.

Появляется диалоговое окно

COM Add-ins (рис. 1.3).

Ставим выбор нашей надстройки.

Заодно выбираем остальные надстройки с названием Microsoft Power <…> for Excel. Слово Power намекает на инструментарий Power BI.

Нажимаем ОК.

Рис. 1.3. Активация надстроек

Убедимся, что надстройки активированы:

File — Options — Excel Options — Add-ins.

Все надстройки в нужном разделе:

Active Application Add-ins.

Рис. 1.4. Надстройки активированы

Задание. Активируйте надстройки Power ***.

Попутно познакомимся с этим названием COM.

Задание. Прочитайте в Википедии статью «Component Object Model» и выясните, в каких операционных системах реализована модель COM.

На всякий случай обновим Microsoft Office:

File — Account — Product Information — Office Updates — Update Options — Update Now (рис. 1.5).

Рис. 1.5. Настройка обновления

Если у нас установлена последняя версия пакета, нам об этом сообщат (рис. 1.6)

Рис. 1.6. Обновление не требуется

Задание. При необходимости обновите пакет.

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

https://download.microsoft.com

Вводим ключевые слова в строке поиска:

Power Pivot Excel.

Получаем некоторые подсказки по версиям пакета (рис. 1.7).

Рис. 1.7. Поиск загрузки

При просмотре результатов поиска лучше сразу перейти на вкладку

Explore.

Обнаруживаем, какое количество страниц на эту тему нам предлагают (рис. 1.8).

Рис. 1.8. Результаты поиска

Задание. Выполните поиск материалов в Центре загрузки Microsoft по следующим ключевым словам:

— Power Pivot Excel;

— Power Pivot Excel 2010.

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

2. Загрузка из базы данных

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

Для наших опытов нам понадобится скачать в Центре загрузки файл под следующим названием (рис. 2.1):

olympicmedals.accdb.

В этом файле находится готовая, заполненная база данных Microsoft Access, предназначенная для опытов с загрузкой данных в Excel.

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

Рис. 2.1. Поиск файла

Задание. Найдите файл olympicmedals.accdb в Центре загрузки и скачайте его. Разместите файл в рабочем каталоге, где вы сохраняете файлы для данной работы.

Для загрузки данных из файла в формате Microsoft Access *.accdb выбираем следующий пункт в верхнем меню:

Data — Get & Transform Data — Get Data — From Database — From Microsoft Access Database (рис. 2.2).

Рис. 2.2. Загрузка данных из файла Access

Появляется диалоговое окно

Import Data.

Заходим в нужный каталог.

Выбираем файл для загрузки (рис. 2.3).

Нажимаем кнопку

Import.

Рис. 2.3. Выбор файла для загрузки

Задание. Выберите файл для загрузки.

Появляется диалоговое окно

Navigator (рис. 2.4).

В левой части окна выводится список таблиц, найденных в выбранном файле.

Щёлкаем по любой таблице в списке.

В правой части окна выводится содержимое выбранной таблицы. Мы можем заглянуть в каждую таблицу и только потом загрузить её в Excel.

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

Display options — Enable Data Previews.

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

Задание. Просмотрите содержимое каждой найденной таблицы.

Отключим предварительный просмотр.

Выбираем любую таблицу в списке.

Нам сообщают, что просмотр отключён:

Preview is disabled (рис. 2.5).

Рис. 2.5. Просмотр отключён

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

Попробуем загрузить все найденные таблицы.

Включаем предварительный просмотр.

Устанавливаем разрешение на загрузку нескольких таблиц.

Select multiple items.

Выбираем все таблицы (рис. 2.6).

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

null.

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

Задание. Установите выбор всех таблиц.

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

Выбираем первую таблицу.

Нажимаем на кнопку

Select Related Tables.

Программа выбирает связанные таблицы.

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

Рис. 2.7. Связанные таблицы

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

Мы познакомились с окном выбора таблиц. Теперь пора переходить к загрузке данных.

Снова выбираем все таблицы.

Нажимаем кнопку загрузки:

Load.

Рассмотрим результаты загрузки.

В правой части окна Excel открывается список выполненных запросов на загрузку данных:

Queries & Connections.

Это должно быть нам знакомо по предыдущей работе. Тогда мы работали с набором инструментов Get & Transform и с надстройкой Power Query.

В разделе Queries нам сообщают, что было выполнено пять запросов:

5 queries.

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

Подведём курсор к любому запросу. Нам выводят подробные сведения об источнике данных (рис. 2.8).

Название таблицы:

S_Teams.

Далее идёт область предварительного просмотра.

Названия загруженных столбцов:

Columns [4] — Event, Discipline, Sport, Participation.

Время последнего обновления результатов запроса:

Last refreshed.

Указан источник данных:

Data Sources [1] — каталог и файл.

Между прочим, нам намекают про возможность загрузить данные сразу из нескольких источников.

Кроме того, здесь говорится, что данные из выбранной таблицы были загружены в «Модель данных»:

Load status — Loaded to Data Model.

Рис. 2.8. Запросы на загрузку данных

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

Мы загрузили данные. Переходим к анализу.

Создадим сводную таблицу:

Insert — Tables — PivotTable.

Появляется диалоговое окно создания сводной таблицы:

Create PivotTable (рис. 2.9).

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

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

Нажимаем ОК.

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

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

Появляется заготовка сводной таблицы и меню настройки полей:

PivotTable Fields.

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

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

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

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

Посмотрим повнимательнее на список полей сводной таблицы.

Значки таблиц намекают на базу данных — цилиндр как символ компьютерного диска (то есть база данных) на фоне таблички.

Каждая таблица на самом деле — это раскрывающийся список. Щёлкаем по значку> слева от названия таблицы и получаем список полей данной таблицы. Чтобы свернуть список полей, щёлкаем по значку слева от названия таблицы.

Задание. Ознакомьтесь с полями каждой таблицы в списке полей сводной таблицы.

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

Data Source: Query — Medals.

Здесь явно указано, что данные получены с помощью запроса на загрузку (рис. 2.10).

Задание. Ознакомьтесь с источником данных по каждой таблице.

Вернёмся к источнику данных.

Откроем наш исходный файл базы данных в пакете Microsoft Access. Для этого достаточно просто дважды щёлкнуть по файлу в Проводнике. Конечно, при этом сам пакет Access должен быть установлен на компьютере. Если нет, то для нашего опыта придётся его установить.

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

Database Tools — Relationships — Relationships.

Читаем всплывающую подсказку (рис. 2.11).

Рис. 2.11. Построение схемы базы данных

Задание. Откройте файл базы данных в Access и прочитайте описание функции Relationships.

Нажимаем на кнопку Relationships.

На экране появляется схема базы данных (рис. 2.12).

Видим, что здесь всего одна связь типа «один ко многим» между двумя таблицами.

Три оставшиеся таблицы оказались сами по себе.

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

Рис. 2.12. Модель базы данных

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

Мы создали сводную таблицу. Пришло время настроить поля.

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

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

По столбцам расположим страну национального оргкомитета:

Columns: Medals — NOC_CountryRegion.

По строкам расположим виды спорта:

Rows: Disciplines — Discipline.

Организуем фильтрацию по медалям:

Filters: Medals — Medal.

В качестве обобщённого показателя найдём количество медалей:

Values: Medals — Medal.

Обратите внимание на автоматическое вычисление количества медалей Count of Medal в области значений (рис. 2.13). Обычно при создании сводной таблицы нам предлагается по умолчанию найти сумму.

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

Задание. Настройте сводную таблицу описанным выше образом.

Отредактируем заголовки сводной таблицы и сделаем их более понятными (рис. 2.14).

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

Рис. 2.14. Заголовки сводной таблицы

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

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

Таблица получилась большая и очень подробная.

Чтобы ознакомиться с какими-то конкретными данными, можно сделать таблицу поменьше. Она станет более информативной и обозримой.

Нас будут интересовать следующие виды спорта:

— Archery — Стрельба из лука;

— Diving — Прыжки в воду;

— Fencing — Фехтование;

— Figure Skating — Фигурное катание;

— Speed Skating — Конькобежный спорт.

Выберем соответствующие строки. Для этого щёлкнем по значку выпадающего списка справа от заголовка «Виды спорта» (рис. 2.15). Снимаем выбор всех строк:

Select All.

Выбираем только перечисленные выше значения.

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

Рис. 2.15. Настройка фильтра по строкам

Подводим курсор к заголовку «Виды спорта».

Рассматриваем всплывающую подсказку (рис. 2.16).

Нам говорят, мы настроили вручную фильтр по полю «Вид спорта»:

Manual Filters — Discipline.

Рис. 2.16. Фильтр по видам спорта

Задание. Настройте фильтрацию сводной таблицы по видам спорта.

Мы настроили выборку данных по строкам — по видам спорта.

Теперь займёмся столбцами. В столбцах у нас названия стран-участников соревнований.

Сделаем выборку данных по странам, получившим более 90 медалей. Это будет фильтр по числовым значениям.

Щёлкаем по кнопке выпадающего списка справа от заголовка «Страны» (рис. 2.17).

Выбираем вариант фильтрации по значениям, которые окажутся больше заданного порога:

Value Filters — Greater Than.

Рис. 2.17. Вызов фильтра по значению

Появляется диалоговое окно настройки фильтра по значениям:

Value Filter (NOC_CountryRegion).

В заголовке окна указан метод фильтрации:

Value Filter.

Здесь же в скобках выводится название поля, по которому будет проводиться фильтрация:

NOC_CountryRegion.

Выбираем пороговое значение фильтрации 90:

Show items for which — Число медалей — is greater than — 90 (рис. 2.18).

Нажимаем кнопку ОК.

Рис. 2.18. Настройка фильтра по значениям

Задание. Проведите фильтрацию стран по числу медалей, превышающему 90.

После включения двух фильтров — по видам спорта и количеству медалей получаем более компактную сводную таблицу (рис. 2.19).

Обратим внимание на появление значка фильтрации в виде символа воронки рядом с заголовком «Страны».

Какие же страны были отобраны после фильтрации? Можно видеть, что фильтр «Больше 90» затрагивает общее, СУММАРНОЕ КОЛИЧЕСТВО медалей по выбранным видам спорта. Другими словами, мы выбрали страны, получившие боле 90 медалей по пяти видам спорта — в сумме.

Рис. 2.19. Отфильтрованная сводная таблица

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

Что же такое NOC_CountryRegion? Это название понятно англоязычной публике, но не слишком информативно для отечественного пользователя.

Делаем вывод: названия полей в базах данных должны быть понятны не только разработчику-программисту. Возможно, эти названия придётся расшифровывать пользователям. Ну а любая программа пишется, в первую очередь, для удобства пользователя, а не для программиста. Комфорт для разработчика тоже важен, но это должен быть второстепенный момент.

Посмотрим, что скажет Википедия. Поищем NOC в английской версии сайта (рис. 2.20). Нам подходит следующая расшифровка NOC:

National Olympic Committee.

Рис. 2.20. Расшифровка сокращения NOC

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

Находим в левой части окна раздел Languages и переходим по ссылке Русский (рис. 2.21).

Рис. 2.21. Переход на русскую страницу

Задание. Откройте в английской версии Википедии статью «National Olympic Committee». Переключитесь на соответствующую русскую страницу.

На этой странице мы выясняем русское название соответствующего комитета.

Кроме того, здесь есть очень полезные таблицы с расшифровкой названий стран.

Задание. Составьте на отдельном листе таблицу с расшифровкой обозначений НОК для стран, попавших в отфильтрованную сводную таблицу.

Ещё немного углубимся в спортивную тему.

Вспоминаем, что медали бывают разные:

— Gold — Золотые

— Silver — Серебряные

— Bronze — Бронзовые.

Проведём фильтрацию сводной таблицы по видам медалей.

Щёлкнем по значку фильтрации в разделе фильтров.

Выберем только золотые медали.

Нажимаем ОК.

Обращаем внимание на значок фильтра в виде символа воронки в разделе фильтров (рис. 2.22).

Если понадобится выбрать несколько значений, ставим выбор внизу окна:

Select Multiple Items.

Рис. 2.22. Фильтр по типам медалей

Задание. Проведите фильтрацию по типу медалей:

— золотые и серебряные медали;

— только золотые медали;

— только серебряные медали;

— только бронзовые медали.

Запишите в отчёте, какие страны попали в список после фильтрации.

Мы познакомились с организацией фильтров в сводной таблице.

Теперь проведём фильтрацию по другому принципу.

Выберем в нашей сводной таблице самое необычное и малочисленное.

Задание. Сделайте выборку по четырём экзотическим видам спорта для стран с минимальным числом медалей.

3. Импорт данных из электронной таблицы

Рассмотрим особенности импорта данных из файла электронной таблицы.

Для наших опытов будем использовать файл под названием:

OlympicSports. xlsx.

Это файл придётся найти в Центре загрузки и скачать на свой компьютер в рабочий каталог.

Судя по содержимому файла, здесь тоже содержатся данные по олимпийским видам спорта (рис. 3.1).

Рис. 3.1. Скачивание файла данных

Задание. Найдите в Центре загрузки и скачайте файл OlympicSports. xlsx.

Открываем загруженный файл в пакете Excel (рис. 3.2).

Здесь находим один рабочий лист под названием

Sheet1.

На этом листе расположена небольшая таблица из двух столбцов.

Рис. 3.2. Таблица видов спорта

Задание. Откройте загруженный файл в Excel.

Скопируем лист с таблицей по видам спорта и вставим его в наш файл отчёта.

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

Move or Copy (рис. 3.3).

Рис. 3.3. Копирование листа

Появляется диалоговое окно

Move or Copy.

Раскрываем выпадающей список:

Move selected sheets — To book.

Выбираем файл отчёта (рис. 3.4).

Указываем, куда нужно вставить этот лист:

Before sheet — (move to end).

Кроме того, указываем, что нам нужно только скопировать лист, не удаляя его в исходном файле:

Create a copy.

Рис. 3.4. Вставка листа в другую книгу

Задание. Скопируйте таблицу видов спорта в отчёт.

Отформатируем новые данные как таблицу Excel.

Выделяем диапазон ячеек загруженной таблицы.

Вызываем форматирование таблицы:

Home — Styles — Format as Table (рис. 3.5).

Рис. 3.5. Форматирование таблицы

Выбираем цвет и стиль оформления.

Появляется диалоговое окно создания таблицы:

Create Table.

Указываем, что первая строка выбранного диапазона содержит заголовки столбцов:

My data has headers (рис. 3.6).

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

Задание. Отформатируйте загруженные данные как таблицу Excel.

Назначим нашей таблице название:

Table tools — Design — Properties — Table Name — Sports (рис. 3.7).

Точно так же назовём текущий рабочий лист.

Рис. 3.7. Задание имени таблицы

Задания. Присвойте название Sports таблице и листу.

Следующий набор данных — это список мест проведения соревнований.

Мы будем загружать таблицу из веб-странички. Эту процедуру мы уже обсуждали в предыдущей работе. Здесь в дело вступает Power Query. Эта надстройка теперь скрывается под названием Get & Transform.

Поищем в интернете список олимпийских городов:

List of Olympic Games host cities.

Поисковая машина Яндекс выводит первой ссылку на страницу Википедии (рис. 3.8).

К сожалению, таблица Википедии не слишком нам подходит.

Рис. 3.8. Результаты поиска олимпийских городов

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

Мы рассматриваем пример, разработанный компанией Microsoft.

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

Переходим на сайт поддержки Microsoft Office:

https://support.office.com

Задаём ключевые слова для поиска:

olympic host cities.

Получаем ссылку на обучающий пример по импорту данных:

Tutorial: Import Data into Excel, and Create a Data Model.

Мы работаем по общему сценарию данного примера, но с некоторыми уточнениями. Наше описание полностью проверено на работоспособность и скорректировано по состоянию на момент составления данного пособия.

Рис. 3.9. Поиск на сайте поддержки

Ближе к середине страницы мы находим таблицу с полезной информацией (рис. 3.10).

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

С этим материалом мы и будем работать.

Рис. 3.10. Таблица олимпийских городов

Задание. Найдите список олимпийских городов на сайте поддержки Microsoft Office. Обратите внимание на название раздела, в котором находится эта таблица.

Попробуем загрузить найденную таблицу с помощью Power Query.

Запускаем загрузку из интернета:

Data — Get & Transform Data — From Web.

Копируем адрес веб-страницы в браузере и вставляем в диалоговое окно Excel:

From Web — URL.

Нажимаем ОК.

Получаем диалоговое окно выбора таблицы для загрузки из интернета:

Navigator.

Выбираем нужную таблицу (рис. 3.11).

Нажимаем ОК.

Рис. 3.11. Выбор таблицы для загрузки

Через несколько секунд наша таблица загружена (рис. 3.12).

Она уже отформатирована как таблица Excel.

Находим наш запрос в списке выполненных запросов на загрузку данных:

Queries & Connections.

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

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

Hosts.

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

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

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

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

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

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

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

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

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

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

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

4. Связи между таблицами

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

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…

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

5. Добавление связи в PowerPivot

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

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

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

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

Power Pivot — Tables — Add to Data Model.

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

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

Рис. 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. Выясните, какие соревнования проводились по каждой спортивной дисциплине.

6. Вычисляемое поле

Для создания связи между таблицами может потребоваться создать уникальное вычисляемое поле. Для этого служат формулы 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).

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

Рис. 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).

7. Создание иерархии

Основной способ создания иерархии — это модель данных в 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.

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

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

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

— Моя презентация.

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

Переименуем плод нашего творчества. Дважды щёлкнем по заголовку иерархии и введём новое название:

SDE.

Это первые буквы слов Sport — Discipline — Event.

Задание. Переименуйте иерархию.

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

Рис. 7.3. Сворачиваем иерархию

Задание. Сверните и разверните иерархию на диаграмме.

Теперь удалим иерархию.

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

Delete.

Нас, конечно, попросят подтвердить серьёзность намерений. Мы подтверждаем:

Delete from Model.

Нажимаем волшебную кнопку.

Иерархии больше нет (рис. 7.4).

Рис. 7.4. Удаление иерархии

Задание. Удалите иерархию.

Создадим ту же самую иерархию другим способом.

Щёлкнем правой кнопкой по заголовку таблицы Hosts.

Выбираем в контекстном меню создание иерархии:

Create Hierarchy.

Поскольку мы не указали поля, мы получили «пустую», незаполненную иерархию (рис. 7.5).

Нам сразу же подсказывают, что сюда можно перетащить выбранные столбцы (поля):

Drag columns here to add levels…

Так можно будет создать новые уровни иерархии (рис. 7.5).

Рис. 7.5. Создание «пустой» иерархии

Задание. Создайте пустую иерархию.

Теперь заполним иерархию. Перетащим в иерархию следующие поля:

— Sport;

— Discipline;

— Event.

Поля можно перетаскивать внутри иерархии (рис. 7.6).

Нам нужно организовать иерархию следующим образом:

Верхний уровень — виды спорта.

Ниже идут спортивные дисциплины. То есть каждый вид спорта делится на несколько спортивных дисциплин.

Самый нижний уровень — конкретный вид соревнований. По каждой спортивной дисциплине может быть несколько разных соревнований.

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

Переименуем иерархию в SDE. Такое название для нас пока что понятно. В дальнейшем можно придумать что-то более информативное.

Рис. 7.6. Заполнение и настройка иерархии

Задание. Организуйте уровни в правильном порядке и переименуйте иерархию в SDE.

Теперь поработаем с другой таблицей.

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

Создаём иерархию, нажав кнопку Create Hierarchy в правом верхнем углу таблицы (рис. 7.7).

На кнопке символически изображена развёрнутая иерархия с возможностью дальнейшей детализации. Это плюсики рядом с уровнями.

Переименуем иерархию и присвоим её название Locations.

Перетаскиваем сюда названия столбцов. Расположим уровни иерархии следующим образом:

— Season;

— City;

— NOC_CountryRegion;

— EditionID.

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

Задание. Создайте иерархию Locations.

Сохраняем изменения в модели данных:

File — Save.

Можно просто нажать кнопку сохранения в левом верхнем углу окна (рис. 7.8). На кнопке изображён символ дискеты. Эта картинка знакома только ветеранам-компьютерщикам. Молодое поколение видело дискеты только в музее. И то если нашли такой музей.

Пользуясь случаем, рассмотрим значок Power Pivot в левом верхнем углу окна. Можно предположить, что здесь изображена многомерная сводная таблица, то есть многомерный OLAP-куб.

Закрываем окно Power Pivot. Надеемся, что закрыть окно сможет каждый.

Рис. 7.8. Сохранение изменений в модели данных

Задание. Сохраните изменения и закройте Power Pivot.

8. Использование иерархии

Мы создали две иерархии в модели данных.

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

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

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

Задание. Создайте сводную таблицу (рис. 8.1).

Рассмотрим список полей для настройки сводной таблицы (рис. 8.2).

Вначале нам выводят просто список таблиц.

Раскроем таблицу Events, нажав кнопку [>] слева от названия таблицы.

Здесь мы обнаруживаем два элемента:

— SDE

— More Fields.

Раскроем пункт More Fields. Получаем список обычных полей таблицы.

Раскроем пункт SDE. Здесь находится список уровней иерархии.

Рис. 8.2. Иерархия в списке полей

Обратим внимание, что слева от названия каждого поля имеется квадратик для выбора, чтобы сформировать сводную таблицу (рис. 8.2).

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

Попробуем перетащить уровень Discipline из нашей иерархии SDE в раздел строк Rows. Перетащить-то мы сможем. Но в результате в разделе строк мы обнаруживаем не отдельное поле, а всю иерархию целиком (рис. 8.3).

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

Задание. Перетащите уровень Discipline из SDE в раздел строк. Обратите внимание на результаты.

Рассмотрим список полей и сводную таблицу (рис. 8.3). В списке полей наша иерархия SDE выделена галочкой и жирным шрифтом. Это говорит о том, что она используется в сводной таблице.

В разделе строк Rows мы видим SDE.

Кроме того, в списке полей жирным шрифтом выделен вид спорта Sport.

В сводной таблице уровни иерархии свёрнуты. Можно видеть только самый верхний уровень. Тот самый «Вид спорта».

Развернём водные виды спорта. Щёлкнем по значку [+] слева от названия Aquatics.

Мы получаем детализацию, но только для верхнего уровня (рис. 8.4). В списке полей жирный шрифт выделяет теперь два уровня — вид спорта и дисциплину.

Рис. 8.4. Детализация верхнего уровня

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

Например, возьмём плавание.

Щёлкнем по значку [+] рядом с названием Swimming.

Теперь у нас появился список соревнований с указанием дистанции заплыва и стиля плавания (рис. 8.5).

В списке полей жирным шрифтом выделены все три уровня иерархии.

Рис. 8.5. Детализация всех уровней

Задание. Раскройте и сверните уровни иерархии, наблюдая за выделением полей.

Посмотрим на иерархию с другой стороны. Мы уже обсуждали понятие «детализация» в предыдущих работах.

Соответствующие английские названия:

— Drill Down

— Drill Up.

В верхнем меню Excel можно обнаружить именно такие кнопки в следующем разделе:

Pivot Table Tool — Analyze — Active Field.

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

Подводим курсор к кнопке Drill Down.

Появляется всплывающая подсказка. Нам сообщают что-то про CHILDREN. Однако, это не просто «дети» в разговорном понимании. Здесь мы можем вывести на экран дочерние, нижестоящие уровни для конкретного значения выбранного поля.

Рис. 8.6. Кнопка детализации

Задание. Прочитайте всплывающую подсказку для кнопки Drill Down.

Выбираем значение Aquatics и нажимаем кнопку Drill Down.

Мы погрузились на один уровень в наши обобщённые показатели (рис. 8.7). Теперь на экран выводятся только спортивные дисциплины, относящиеся к водным видам спорта.

Мало того, виды спорта вообще исчезли из поля зрения.

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

Обратим внимание, что кнопка Drill Up стала активной.

Подводим курсор к кнопке Drill Up и читаем всплывающую подсказку. Здесь нам сообщают прямо, без намёков на детей и родителей: «Мы вам покажем вышестоящий уровень».

Рис. 8.7. Погружение на один уровень

Но и это ещё не всё. Под кнопкой Drill Up виднеется малозаметная стрелочка вниз, которая так и просит нажать на неё.

Здесь скрывается список уровней иерархии — куда мы можем вернуться (рис. 8.8).

На текущем уровне у нас особого выбора нет. Мы можем только подняться до видов спорта.

Так мы и сделаем.

Рис. 8.8. Список уровней иерархии

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

Так мы и сделаем. Дважды щёлкаем по значению Aquatics.

Мы погрузились до уровня спортивных дисциплин.

Теперь выбираем плавание. Дважды щёлкаем по значению Swimming. Мы погрузились до уровня соревнований разной дистанции и стиля.

На экране только соревнования по плаванию. Нет никаких указаний на виды спорта и спортивные дисциплины.

Попробуем опуститься ещё ниже.

Выбираем стометровый заплыв брассом:

100m breaststroke.

Дважды щёлкаем по этой строчке и получаем сообщение: дальше пути нет, детализация не получится.

Действительно, ниже опускаться нельзя, здесь нет значка раскрытия деталей [+]. Это самый нижний уровень.

Проверим, как поведёт себя кнопка Drill Up.

Нажимаем на стрелочку выпадающего списка.

Теперь мы можем выбрать один из двух вышестоящих уровней. Щёлкаем по видам спорта Sports и сразу переходим на верхний уровень.

Рис. 8.9. Нижний уровень иерархии

Рядом с кнопками Drill Down и Drill Up притаились две кнопки с похожим предназначением:

— Expand Field;

— Collapse Field.

Подводим курсор к кнопке Expand Field и читаем всплывающую подсказку. Оказывается, эта кнопка может развернуть всё, что попадётся под руку.

Нажимаем. Разворачиваем. Перед нами все уровни иерархии. Теперь видно одновременно и вид спорта, и дисциплину, и соревнование.

Рис. 8.10. Детализация всех значений

Наконец, проведём ещё один опыт. Скомбинируем погружение и детализацию.

Для начал поднимемся на верхний уровень.

Нажимаем кнопку Drill Down.

Затем нажимаем кнопку Expand Field.

Теперь перед нами детализация водных видов спорта. Мы видим дисциплины и соревнования. А вот названия вида спорта здесь нет. И других видов спорта, кроме водных, здесь нет и не предвидится.

Рис. 8.11. Разворачивание уровня иерархии

Вернёмся к пользовательской иерархии.

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

Настроим поля сводной таблицы так, как мы это уже делали в предыдущих работах. Просто перетащим отдельные поля из списка и расположим их одно под другим в разделе строк (рис. 8.12).

Внешне всё очень похоже. Мы тоже можем получать разную степень детализации сводной таблицы.

А вот кнопки Drill Down и Drill Up стали неактивными.

Нам остаётся только работать с кнопками Expand/Collapse Field.

Теперь, надеемся, начинает проясняться разница между «детализацией» и «детализацией». То есть между перемещением по уровням иерархии и разворачиванием списков.

Рис. 8.12. Пользовательская иерархия

Вернёмся в исходное положение. На верхний уровень иерархии в обеих таблицах.

Добавим в раздел значений Values поле Medal из таблицы Medals. Сравним две сводные таблицы (рис. 8.13).

Итоговые показатели совпадают.

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

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

Рис. 8.13. Сравнение значений

Задание. Сравните значения в обеих таблицах.

Напомним, что у нас есть возможность «провалиться в данные». Мы можем посмотреть исходные, необработанные данные, на основе которых было вычислено конкретное значение в сводной таблице.

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

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

Рассмотрим новый лист. В заголовке листа нам подробно указывают название обобщённого показателя и положение строки (Вид спорта — Дисциплина — Соревнование). При этом в скобках нам напоминают, что это не все данные. Это не вся таблица, только начало — первая тысяча строк. Или чуть меньше.

В заголовке каждого столбца указано название таблицы и поля.

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

Дважды щёлкнем по такой же числовой ячейке во второй таблице. Получаем такой же набор данных. Удаляем лист.

Рис. 8.14. Исходные данные

Задание. «Провалитесь» в данные и рассмотрите содержимое нового листа.

Мы создали две иерархии в модели данных и убедились в том, что они работают. Можно наконец-то создать что-то более интересное, чем один столбец чисел. Например, столбцов семь или восемь.

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

Перетаскиваем иерархию SDE из таблицы Events в область строк.

Перетаскиваем иерархию Locations из таблицы Hosts в область столбцов.

Количество медалей отправим в раздел значений.

Выбираем заголовок строки и нажимаем кнопку Drill Down.

Проделаем то же самое по столбцам.

В таблице остались только соревнования по плаванию и города, к которых проходили именно эти соревнования (рис. 8.15).

Рис. 8.15. Детализация по двум измерениям

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

Осталось бросить прощальный взгляд на реляционную модель данных.

Щёлкаем по сводной таблице.

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

PivotTable Tools — Analyze — Calculations — Relationships.

Подводим курсор к этой кнопке и читаем всплывающую подсказку (рис. 8.16).

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

Рис. 8.16. Кнопка «Связь между таблицами»

Задание. Прочитайте всплывающую подсказку про связи

Нажимаем кнопку Relationships.

Получаем диалоговое окно управления связями:

Manage Relationships.

Здесь мы видим список из четырёх связей с указанием таблиц и ключевых полей для организации связи (рис. 8.17).

Рис. 8.17. Список связей

Задание. Выведите на экран список связей.

Дважды щёлкнем по последней строчке списка.

Это связь между таблицами Medals и Hosts.

Получаем диалоговое окно редактирования связи:

Edit Relationship.

Здесь мы видим сведения о двух таблицах с указанием ключевого поля в каждой из них (рис. 8.18).

Закрываем окно диалога.

Рис. 8.18. Редактирование связи

Задание. Вызовите диалог редактирования связи для первой строчки списка и изучите полученные сведения.

Визуализация данных в Power View

9. Аналитические отчеты

Напомним, что в системах бизнес-аналитики обычно используют три типа инструментов:

— загрузка;

— анализ;

— визуализация.

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

Пора познакомиться со средствами визуализации. Общее название таких инструментов — аналитические отчёты. Или средства создания отчётов. Или генераторы отчётов.

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

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

Задание. Изучите список программных средств создания аналитических отчётов в статье «List of reporting software» в английской версии Википедии. Приблизительно оцените количество программных продуктов.

Задание. Прочитайте в Википедии статью «Генератор отчётов». Выясните, какая основная идея заложена в средствах создания отчётов.

Задание. Задайте поиск Картинок Яндекса по ключевым словам «аналитические отчеты». Познакомьтесь с примерами оформления и визуализации.

В данной работе мы познакомимся с технологиями построения аналитических отчетов с помощью надстройки Power View. Для начала просмотрим описание разработчиков продукта.

Задание. Изучите на сайте поддержки Microsoft Office статью: «Power View — Overview and Learning». Русский вариант «Power View — обзор и обучение». Выясните, где можно взять Power View и какие типы визуализации здесь имеются.

Этот инструмент позволяет создавать ИНТЕРАКТИВНЫЕ графики и представления данных. В этом его отличие от встроенных графиков и таблиц Excel. Кроме того, здесь есть средства повышения скорости работы. Есть смысл об этом немного почитать.

Задание. Изучите на сайте поддержки Microsoft Office статью: «Power View: Explore, visualize, and present your data». Русский вариант «Power View: исследование, визуализация и представление данных». Выясните, с какими источниками данных может работать Power View, какие возможности взаимодействия с графиками здесь заложены, а также за счёт чего повышается производительность работы.

Чтобы начать работу с Power View. Нужно активировать эту надстройку или хотя бы убедиться, что она включена.

Вызываем в верхнем меню параметры надстроек:

File — Options.

Появляется диалоговое окно Excel Options. Выбираем раздел Add-ins (рис. 9.1).

Находим Power View. Нам нужно, чтобы эта надстройка была в списке активных:

Active Application Add-ins.

Рис. 9.1. Параметры надстроек

Задание. Убедитесь, что надстройка Power View активирована.

Посмотрим на экран повнимательнее (рис. 9.1).

В нижней части окна выводятся дополнительные сведения о выбранной надстройке.

Обратим внимание на каталог для нашей надстройки. В этой длинной строчке встречается загадочное кодовое название: Office16.

Задание. Просмотрите в Википедии следующие статьи:

— Microsoft Office 2019

— Microsoft Office 2016

— Microsoft Office 2013

Обратите внимание на кодовые названия разных версий продукта. Проследите историю названий предыдущих версий офисного пакета.

На экране (рис. 9.1) есть ещё кое-что полезное для нас.

В колонке Type можно видеть, что это надстройка типа COM — как и Power Pivot.

Переходим в раздел именно таких настроек:

Manage — COM Add-ins — Go (рис. 9.2).

Что такое COM, мы уже обсуждали в начале данного пособия.

Рис. 9.2. Выбор типа надстроек

На экране появляется окно с нашими надстройками:

COM Add-ins.

Здесь целый список полезных инструментов (рис. 9.3).

Наше дело — убедиться, что надстройка Power View for Excel включена. Если нет, то поставим галочку для выбора инструмента.

Нажимаем ОК.

Рис. 9.3. Список надстроек типа COM

Задание. Убедитесь, что надстройка Power View активирована.

Итак, надстройка должна работать.

Попробуем создать отчёт.

Для начала поищем наш генератор отчётов в строке поиска Excel.

Подводим курсор к значку лупы (увеличительного стекла с ручкой) в верхнем меню. Почитаем всплывающую подсказку (рис. 9.4). Выясняется, что мы можем здесь поискать разделы справки. А ещё мы можем сразу вызвать нужную функцию пакета. На будущее нам предлагают комбинацию клавиш для быстрого вызова поиска. Так что иногда бывает полезно читать описания.

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

Щёлкаем по строке поиска и вводим название надстройки:

Power View.

Первый результат поиска — вставить аналитический отчёт:

Insert a Power View Report.

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

Здесь есть кое-какая полезная информация — кроме заманчивых, рекламных слов BETTER и BEAUTIFUL.

Здесь говорится о более качественном принятии решений по управлению предприятием. Напомним, что английское слово business может означать «любое предприятие, компания, организация». Ну а принятие решений —это главный компонент управления, руководства компанией. Английское название такой деятельности — MANAGEMENT.

Рис. 9.5. Результаты поиска

Задание. Введите в строке поиска название надстройки Power View и изучите результаты поиска. Прочитайте всплывающую подсказку по функции вставки отчёта.

Что же означает «более качественное принятие решений»? Вообще-то это тонкий намёк на сборник полезных советов под названием «Система менеджмента качества». Этот документ часто называют «международным стандартом», хотя сюда больше подходит выражение «рекомендации некоторых западных экспертов».

Задание. Найдите в интернете текст стандарта ISO 9004, изучите седьмой принцип менеджмента качества и выясните, за счёт чего предлагается повышать качество принимаемых решений.

Вот мы и разобрались, зачем нужна аналитика и отчёты.

Попробуем вставить отчёт.

Нажимаем найденный в строке поиска пункт:

Insert a Power View Report.

Однако, вместо отчёта получаем предупреждение от службы безопасности (рис. 9.6).

Мало того, что у нас заблокирован доступ к Power View. Нам просто показывают ссылку на объяснения, но предлагают просто нажать ОК.

Попробуйте вызвать Power View на своём компьютере. Может быть вам повезёт.

То, что мы описываем в данном пособии, работает на момент написания текста. Со временем всё меняется. И вместо блокировки Silverlight найдётся что-нибудь ещё.

В любом случае, нам нужно включить нашу надстройку и установить всё, что потребуется.

Рис. 9.6. Сообщение о блокировании

Можно вручную ввести ссылку из информационного окна.

Но мы поступим по-другому.

Вернёмся к результатам поиска.

Кроме функции вставки отчёта, у нас есть возможность вызова справки (рис. 9.7).

Находим ссылку на раздел справки по вопросу блокировки активных элементов (controls blocked) и зовём специалистов на помощь. В смысле нажимаем на эту кнопку.

Рис. 9.7. Вызов справки

Получаем справку по блокировке активных элементов (рис. 9.8).

Самый простой вариант разблокировки — скачать бесплатные ключи для реестра Windows:

Free package of registry keys.

Рис. 9.8. Справка по блокировке

Если набраться терпения и ввести ссылку из сообщения о блокировке, мы тоже получим такую же страницу справки, но только в браузере (рис. 9.9).

Можно даже увидеть, что скачивание будет производиться из библиотеки технических материалов Microsoft Technet.

Рис. 9.9. Ссылка на скачивание

Попробуем скачать ключи.

Переходим по предложенной ссылке.

На странице имеется кнопка для скачивания пакета ключей в виде архива в формате ZIP (рис. 9.10).

Рис. 9.10. Скачивание ключей

Скачиваем архив и сохраняем его в рабочем каталоге.

В этом архиве скрываются три набора (рис. 9.11).

Нас интересует SilverLight.

Для добавления ключей в реестр нам предлагают закрыть все офисные программы.

Рис. 9.11. Ключи реестра

Закрываем все приложения Microsoft Office.

Двойным щелчком запускаем набор ключей для разблокировки:

EnableSilverLight.reg.

Соглашаемся с предупреждением (рис. 9.12).

Рис. 9.12. Предупреждение перед установкой ключей

Появляется сообщение об успешном добавлении ключей и их значений (рис. 9.13).

Рис. 9.13. Успешная установка

Задание. Установите набор ключей реестра.

Итак, мы успешно добавили ключи и значения в реестр. В точности как нам предлагали.

Осталось понять, что это было.

Обратимся к описанию.

Задание. Просмотрите в Википедии статью «Реестр Windows» и выясните, что такое реестр и что такое ключи реестра.

Второй момент, который нам желательно понять, это Silverlight. Программная платформа Microsoft Silverlight позволяет создавать интерактивное мультимедийное содержание для вставки в интернет-страницы и документы. Почему-то она имеет некоторое отношение к надстройке Excel.

Задание. Просмотрите в Википедии статью «Microsoft Silverlight» и выясните, что это такое и поддерживается ли эта технология в настоящее время. Перейдите по ссылке RIA в тексте и выясните, что это такое.

Задание. Перейдите на сайт проекта Silverlight:

https://www.microsoft.com/silverlight/

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

Снова запускаем Excel.

Открываем наш файл с отчётом.

Щёлкаем по последней сводной таблице с двумя иерархиями.

Через поиск вставляем аналитический отчёт Power View.

Появляется новая пустая страница (рис. 9.14).

Можно сказать, что это заготовка или шаблон будущего отчёта.

Кажется, что это просто новый рабочий лист Excel.

Но это не совсем так. Перед нами нет электронной таблицы. На экране мы видим место для размещения визуальных

элементов отчёта и раздел для фильтров.

В правой части окна имеется область настройки полей аналитического отчёта:

Power View Fields.

Здесь расположен список уже знакомых нам таблиц.

При этом два значка отличаются от остальных.

Это таблицы Hosts и Sports.

Те самые таблицы, в которых мы создали иерархии.

Верхнее меню тоже изменилось. Перед нами раздел меню Power View с инструментами для оформления аналитического отчёта.

Рис. 9.14. Шаблон отчёта Power View

Задание. Создайте отчёт Power View и изучите все элементы текущего окна.

Рассмотрим список полей Power View немного подробнее (рис. 9.14).

Разворачиваем таблицы в этом списке и обнаруживаем наши иерархии.

Кроме того, некоторые поля отмечаются значком суммы. Это заглавная греческая буква «сигма». Это поля, принимающие числовые значения.

Задание. Разверните все таблицы в списке полей. Найдите все иерархии и числовые поля.

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

Мы будет постепенно добавлять поля в наш отчёт и следить за изменениями на экране.

Отметим поле City из таблицы Hosts.

Появляется раздел выбранных полей Field. Пока в этом списке только один элемент (рис. 9.15).

Вначале отчет выводится в форме таблицы.

Это список городов.

Затем мы сможем выбрать и настроить форму визуального представления данных.

Рис. 9.15. Выбор поля City

Задание. Выберите поле City и обратите внимание на созданную таблицу.

Добавим в наш отчёт поле Sport из таблицы Medals.

Ставим отметку рядом с названием поля, и оно добавляется в список полей для отчёта.

Таблица увеличилась (рис. 9.16). Теперь здесь две колонки с выбранными сведениями.

Рис. 9.16. Выбор поля Sport

Задание. Выберите поле Sport и обратите внимание на изменения в таблице.

Выбираем метод агрегирования данных.

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

Выбираем подсчёт заполненных ячеек в выпадающем списке:

Count (Not Blank).

Получаем таблицу с количественными показателями (рис. 9.17).

Рис. 9.17. Выбор метода агрегирования

Задание. Выберите метод агрегирования и обратите внимание на изменения в таблице.

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

Щёлкаем по нашей сводной таблице на листе аналитического отчёта.

Возьмём географическую карту в качестве средства визуализации (рис. 9.18). В верхнем меню выбираем следующий пункт:

Design — Switch Visualization — Map.

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

Рис. 9.18. Переключение способа визуализации

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

Bing Maps.

На экране появляется предупреждение на жёлтом фоне (рис. 9.19).

Щёлкаем по кнопке Enable Content и разрешаем использовать содержимое нашего аналитического отчёта внешним сервисом.

Рис. 9.19. Подключение к источнику географических данных

На карте появились отметки.

Настроим размеры карты, перетаскивая изображение карты за углы, отмеченные значками уголков.

Подводим курсор к уголку краты, и его форма изменяется (рис. 9.20).

Рис. 9.20. Настройка размера карты

Нашу карту можно развернуть на весь лист. Для этого служит кнопка Pop out в правом верхнем углу карты (рис. 9.21).

Кнопка появляется при наведении курсора на карту.

Рис. 9.21. Кнопка разворачивания изображения

Чтобы свернуть изображение в исходное состояние, используем кнопку Pop in.

Кнопка сворачивания тоже появляется в правом верхнем углу изображения при наведении курсора на карту (рис. 9.22).

Рис. 9.22. Кнопка сворачивания изображения

Чтобы освободить дополнительное место для карты, можно свернуть область фильтров Filters (рис. 9.23).

Чтобы свернуть область фильтров, имеется кнопка [<] Minimize the Filters Area.

Чтобы развернуть область фильтров, используется кнопка [>] Expand the Filters Area.

Обе кнопки располагаются в правом верхнем углу области фильтров.

Рис. 9.23. Управление областью фильтров

Масштаб карты можно изменить, вращая колесико мыши. Можно также нажимать Zoom In и Zoom Out.

Кнопки появляются в правом верхнем углу карты при наведении курсора на карту (рис. 9.24).

Здесь же находятся кнопки прокрутки (панорамирования) карты:

— Pan Up — Вверх;

— Pan Down — Вниз;

— Pan Left — Влево;

— Pan Right— Вправо.

Карту внутри своего окошка можно передвигать с помощью мышки.

Рис. 9.24. Управление масштабом карты

Задания.

— Щелкните мышкой на свободном месте листа

— Обратите внимание на изменение ленты меню Excel

— Щелкните мышкой на сводной таблице

— Обратите внимание на изменение ленты меню Excel

— Измените размер карты

— Измените масштаб карты

— Измените положение на карте

— Запустите интернет браузер

— Откройте сайт :

— Bing.com

— Перейдите на вкладку

— MAPS

— Выведите на экран карту региона, отображаемый в отчете Power View.

— Сравните карты.

Итак, мы получили диаграмму (рис. 9.25).

Здесь размер кружочка передаёт числовое значение обобщённого показателя.

В нашем примере это количество видов спорта, по которым проходили соревнования в разных городах за все годы.

Данные привязаны к географической карте.

Если подвести курсор к любому кружочку, на экран выводится значение показателя.

Обратим внимание, что справа появились дополнительные области настройки диаграммы, например, задание цвета:

Color.

Рис. 9.25. Диаграмма на карте

Задание. Ознакомьтесь с диаграммой. Наводите курсор на разные города-участники соревнований и наблюдайте всплывающие сообщения.

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

Задание. Задайте поиск в Картинках Яндекса и изучите примеры картодиаграмм и картограмм. Опишите, в чём состоит различие между ними.

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

Итак, у нас появилась возможность задать цвет.

Перетаскиваем поле Season из таблицы Medals в область Color.

Карта немного изменилась. Теперь у нас кружочки двух цветов. Некоторые кружочки показывают проведение как летних, так и зимних соревнований в одном и том же городе.

Рис. 9.26. Летние и зимние олимпиады

Задание. Перетащите поле Season из таблицы Medals в область Color. Обратите внимание на изменения на карте

Справа вверху от карты имеется расшифровка обозначений (легенда). Цвета назначены автоматически, и они не совсем привычные: лето обозначено голубым цветом, а зима — красным.

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

Задание. Щёлкните по каждому обозначению времени года и наблюдайте за изменениями на диаграмме.

Продолжим украшать нашу картодиаграмму.

У нас есть возможность выбора варианта карты.

Переходим в верхнем меню в раздел Layout.

Рассматриваем и пробуем варианты карт:

Layout — Labels — Map Background.

У нас не меньше пяти разных вариантов (рис. 9.27).

Рис. 9.27. Выбор фоновой карты

Задание. Проверьте, как выглядит каждый вариант карты.

Для полноты картины осталось сделать заголовок.

Сворачиваем карту и щёлкаем по строчке над ней:

Click here to add a title.

Вводим что-нибудь осмысленное (рис. 9.28).

Рис. 9.28. Заголовок аналитического отчёта

Задание. Введите свой заголовок над картой.

Фильтрация картограммы

Аналитический отчёт в форме карты можно анализировать с помощью фильтров и срезов.

Фильтрация выполняется в панели Filters справа от карты.

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

Фильтрация по числовому полю выполняется путем выбора диапазона значений (рис. 9.29). Минимальное и максимальное пороговые значения можно задать путём перетаскивания границ диапазона.

Рис. 9.29. Настройка фильтрации на карте

Задания.

Выполните фильтрацию карты по параметру Season (Сезон проведения олимпиады):

— Summer (Летняя олимпиада).

— Winter (Зимняя олимпиада).

Обратите внимание на изменения на карте.

Раскройте область фильтров справа от карты.

Выполните фильтрацию карты по каждому параметру.

Обратите внимание на изменения на карте.

Вычисляемые поля

Мы уже работали с вычисляемыми полями.

Открываем окно Power Pivot для управления моделью данных:

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

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

Задание. Запустите Power Pivot.

Выбираем таблицу Medals. Щёлкаем по закладке Medals внизу окна.

Включаем показ области вычислений:

Home — View — Calculation Area.

Окно делится на две части. В нижней части окна будут отображаться вычисления (рис. 9.31).

Рис. 9.31. Область вычислений

Задание. Включите показ области вычислений.

Создадим новое вычисляемое поле.

Щёлкаем по ячейке в области вычислений внизу столбца Edition.

Вызываем выбор операции в выпадающем меню:

Home — Calculations — AutoSum.

В разделе AutoSum (Автосумма) нам доступны следующие вычисления:

— Sum — Сумма;

— Average — Среднее;

— Count — Число;

— Distinct Count — Число уникальных значений;

— Max — Максимум;

— Min — Минимум.

Мы выбираем показатель Distinct Count.

Получаем значение, равное 168.

При этом в строке формул можно видеть команду для формирования вычисляемого поля (рис. 9.32).

Для расчётов вычисляемого поля автоматически создается формула DAX.

Рис. 9.32. Вычисляемое поле

Задание. Создайте вычисляемое поле (рис. 9.32) и объясните смысл найденного значения.

Сохраняем модель данных и закрываем окно Power Pivot.

На экран выводится сообщение об изменении в модели данных (рис. 9.33).

Нам предлагают передать изменения модели данных в аналитический отчёт Power View.

Рис. 9.33. Сообщение об изменении модели данных

Задание. Сохраните изменения и закройте окно Power Pivot.

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

Переходим на вкладку со сводной таблицей

Перетаскиваем поле Distinct Count of Edition из таблицы Medals в раздел значений Values.

Обращаем внимание на изменения в сводной таблице (рис. 9.34).

Рис. 9.34. Вычисляемое поле в сводной таблице

Задание. Введите вычисляемое поле в сводную таблицу. Объясните смысл полученных значений.

Интерактивные диаграммы

C помощью надстройки Power View можно создавать интерактивные графики. Пользователь сможет взаимодействовать с элементами графика, выбирать, фильтровать, раскрывать и т. п.

В предыдущем разделе мы вставляли отчёт через строку поиска. Для удобства работы добавим кнопку вставки аналитического отчёта в меню ленты.

Вызываем меню настройки Excel:

File — Options.

Появляется диалоговое окно

Excel Options.

Выбираем раздел настройки ленты:

Customize Ribbon (рис. 9.35).

В левом списке находим кнопку вставки отчёта Power View:

Choose commands from — Main Tabs — Power View — Insert — Power View.

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

Customize the Ribbon — Main Tabs — Insert — New Group.

Рис. 9.35. Настройка ленты

Переименуем новую группу кнопок:

Rename.

Назовем группу отчётами:

Reports.

Нажимаем кнопку добавления пункта меню:

Add.

Получаем новую кнопку в новой группе (рис. 9.36).

Рис. 9.36. Добавление отчёта

Проверяем, что у нас получилось (рис. 9.37).

Выбираем раздел вставки Insert.

У нас появилась группа Reports.

В этой группе одна кнопка Power View.

Наводим на неё курсор и получаем знакомую всплывающую подсказку по поводу вставки аналитического отчёта.

Рис. 9.37. Кнопка вставки отчёта

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

У нас появилась кнопка. Давайте ей воспользуемся.

Создадим новый отчёт:

Insert — Reports — Power View.

Для нового отчёта создаётся новый лист.

Это не просто новый лист Excel.

Это лист Power View. Это особый объект.

Сохраним и закроем наш файл. Затем снова его откроем в Excel. На экране появляется информационное окно с сообщением про открытие листа Power View (рис. 9.38).

Рис. 9.38. Открытие листа отчёта

Задание. Создайте новый отчет Power View. Закройте и откройте файл, наблюдая за сообщением об открытии листа Power View.

Итак, мы создали новый отчёт.

Выбираем поля в области Power View Fields для отображения в отчете.

Отмечаем поле Sport в таблице Events.

Отмечаем поле Year в таблице Medals.

Для поля Year выбираем метод агрегирования Count (Distinct) в выпадающем списке (рис. 9.39).

Рассмотрим полученную таблицу.

В первом столбце список видов спорта.

Во втором столбце — числовые значения.

Рис. 9.39. Метод агрегирования

Задание. Настройте поля отчёта (рис. 9.39). Объясните смысл числового показателя.

При создании отчёта Power View вначале строит сводную таблицу.

Затем мы используем данные из этой таблицы для другого способа визуализации.

Построим круговую диаграмму:

Design — Switch Visualization — Other Chart — Pie.

Теперь вместо таблицы на экран выводится соответствующая диаграмма (рис. 9.40).

Потянем диаграмму за уголок и настроим её размеры.

Рис. 9.40. Выбор метода визуализации

Задание. Установите построение круговой диаграммы. Введите информативный заголовок отчета.

Мы создали круговую диаграмму. Теперь займёмся фильтрами. Фильтры позволяют отбирать сведения для вывода на график.

Напомним, что область фильтров можно сворачивать и разворачивать с помощью кнопок в правой верхней части области графика и в области фильтров.

Начнём с фильтрации для отдельной диаграммы.

Щёлкаем по диаграмме и выбираем раздел Chart в области фильтров (рис. 9.41).

При настройке фильтра на экран выводятся его параметры.

В нашем примере мы выбрали значения больше десяти:

Count of Year — is greater or equal to 10.

Выбранные виды спорта отмечены галочками в списке и перечислены под названием поля.

Подводим курсор к любому сектору диаграммы и получаем пояснение и числовое значение.

Рис. 9.41. Фильтрация данных для диаграммы

Задание. Настройте фильтрацию диаграммы.

Займёмся фильтрацией всего отчёта. Мы настроим фильтр, который будет влиять на все элементы визуализации.

Щёлкаем по свободному месту вне графика. Обращаем внимание на чистую область фильтров.

Перетаскиваем поле Season из таблицы Medals на панели Power View Fields в область фильтров отчёта Filters.

Устанавливаем фильтрацию по зимним видам спорта:

Filters — View — Season — Winter.

Обратим внимание на изменение графика (рис. 9.42).

Рис. 9.42. Настройка общего фильтра отчёта

Мы создали два набора фильтров — для графика и для всего отчёта. Они применяются одновременно.

Щёлкнем по диаграмме и рассмотрим область фильтров (рис. 9.43).

В разделе Chart у нас фильтры для конкретной диаграммы.

В разделе View — фильтры для всего отчёта в целом.

Рис. 9.43. Два вида фильтров

Задание. Ознакомьтесь с двумя видами фильтров.

Детализация иерархии

Аналитические отчёты — это интерактивные объекты, а не просто статичные графики и таблицы. С ними можно взаимодействовать, общаться.

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

Drill-Down.

Щёлкаем по диаграмме и меняем выбор поля для графика.

В списке Power View Fields снимаем выделение поля Sport в таблице Medals.

Вместо него выбираем иерархию, созданную в модели данных — поле SDE в таблице Events. Здесь придётся выбрать три уровня иерархии вручную. Надеемся, это будет несложно — поставить три галочки вместо одной.

Обращаем внимание на изменение списка Color. Теперь здесь присутствуют три выбранных поля. Их же можно обнаружить в составе фильтров для диаграммы.

Рис. 9.44. Иерархия на диаграмме

Задание. Настройте поля для диаграммы (рис. 9.44).

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

Чтобы опуститься по иерархии и перейти на нижестоящий уровень, дважды щёлкнем по любому сектору диаграммы.

Чтобы подняться по иерархии и перейти на вышестоящий уровень, нажимаем кнопку Drill Up.

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

Рис. 9.45. Детализация уровней иерархии

Задание. Переходите по уровням иерархии на диаграмме.

Срезы на диаграмме

На диаграмме можно использовать срезы, которые нам уже знакомы по работе со сводными таблицами. Однако, срезы работают здесь немного по-своему.

Перетаскиваем поле Gender из таблицы Medals в области Power View Fields в область срезов Slices.

Теперь каждый сектор диаграммы делится на две части — мужчины и женщины. Обратим внимание на то, что обе части одного сектора одного цвета.

Подводим курсор к сектору диаграммы и получаем всплывающее окно с подробными сведениями.

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

Рис. 9.46. Срезы на диаграмме

Задание. Настройте срезы на диаграмме. Переходите по уровням иерархии и обращайте внимание на отображение срезов.

Анимация на диаграммах

На диаграммах Power View можно настроить анимацию и «оживить» представленные данные. Это может добавить наглядности, например, при изучении динамики изменений.

Построим пузырьковую диаграмму с анимацией.

Создадим новый лист Power View:

Insert — Reports — Power View.

В этом нам помогает наша пользовательская кнопка из предыдущего раздела.

Выбираем поля для диаграммы.

— поле Medal из таблицы Medals;

— поле NOC_CountryRegion из таблицы Medals;

— поле Sport из таблицы Events.

Рассматриваем результаты. Пока что это сводная таблица без расчётов (рис. 9.47).

Рис. 9.47. Выбор полей для диаграммы

Выбираем метод агрегирования данных в выпадающем списке для поля в области Fields:

— поле NOC_CountryRegion — метод Count (Distinct);

— поле Medals — метод Count (Not Blank).

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

Обратим на изменения в сводной таблице (рис. 9.48).

Теперь по каждому виду спорта у нас есть количество медалей и число стран.

Для более привычного оформления таблицы перетащим поле Sport в верхнюю строчку списка Fields.

Рис. 9.48. Агрегирование данных

Задание. Настройте методы агрегирования данных и рассмотрите полученную таблицу.

Таблица с данными готова.

Щёлкаем по таблице.

Выбираем диаграмму разброса:

Design — Switch Visualization — Other Chart — Scatter.

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

Рис. 9.49. Диаграмма разброса

Задание. Постройте диаграмму разброса.

Настроим нашу диаграмму разброса и сделаем из неё так называемую «пузырьковую диаграмму» Bubble Chart. Здесь размер кружочка (пузырька) передаёт числовое значение обобщённого показателя.

Разворачиваем диаграмму на весь лист

Настраиваем поля для отображения на диаграмме:

— ∑ X VALUE (Значения по оси X) — Count of Medal;

— ∑ Y VALUE (Значения по оси Y) — Count of NOC_CountryRegion;

— ∑ SIZE (Значения, определяющие размер пузырька) — Count of Events — Поле Event из таблицы Events, метод агрегирования — Count (Not Blank);

— Details (Дополнительные сведения) — поле Sport из таблицы Events.

Наводим курсора на пузырёк и получаем всплывающее окошко с дополнительной информацией (рис. 9.50).

Рис. 9.50. Пузырьковая диаграмма

Задание. Настройте пузырьковую диаграмму. Наводите курсор на пузырьки для вывода всплывающего окошка.

Наша пузырьковая диаграмма показывает общие, суммарные сведения за все годы.

Мы можем настроить анимацию и увидеть, как разворачивается процесс в динамике, как картина меняется со временем. Для этого нам нужно создать дополнительную Ось воспроизведения PLAY AXIS с привязкой ко времени.

Перетаскиваем поле Year из таблицы Medals в область Play Axis (рис. 9.51).

Внизу окна появляется ось времени с указанием номера года.

Слева от этой оси находим кнопку запуска воспроизведения:

Play.

Нажимаем кнопку и наблюдаем анимацию.

Рис. 9.51. Пузырьковая диаграмма с анимацией

Задание. Настройте и запустите анимацию на пузырьковой диаграмме.

Перед нами разворачивается картина развития спортивных соревнований. Но здесь собраны все виды спорта с разу.

Выберем один вид спорта, например, водные виды спорта.

Щёлкаем по пузырьку Aquatics и запускаем анимацию (рис. 9.52). Теперь мы видим движение и траекторию по нашему пузырьку.

Можно выбрать несколько объектов для анализа, щёлкая по ним мышкой при нажатой клавише Ctrl. Например, можно выбрать водные виды спорта Aquatics и атлетику Athletics.

Рис. 9.52. Анимация по выбранному объекту

Задание. Выберите один вид спорта и запустите анимацию.

Задание. Выберите два вида спорта и запустите анимацию.

Кроме анимации, мы можем сами, вручную «прокрутить» процесс вперёд и назад.

Для этого нужно перемещать маркер по оси времени (рис. 9.53).

Рис. 9.53. Прокрутка процесса по времени

Задание. Перемещайте маркер по оси времени и наблюдайте за диаграммой.

На диаграмме можно использовать выделение цветом и упрощённую фильтрацию.

Настроим выделение цветом для времени года: зима/лето.

Перетаскиваем поле Season из таблицы Medals в область Color (рис. 9.54).

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

Рис. 9.54. Время года на диаграмме

Задание. Запустите анимацию отдельно по зимним и летним олимпиадам.

Заключение

Мы рассмотрели основные возможности анализа и визуализации данных в Excel. Мы использовали в работе надстройки Power Query, Power Pivot и Power View. Первоначально эти надстройки были разработаны для пакета Microsoft Excel. В настоящее время они доступны в виде самостоятельного продукта Power BI.

Задание. Просмотрите в Википедии статью «Power BI» и обратите внимание на описание трёх компонентов настольного приложения.

Литература

1. Паклин Н. Б., Орешков В. И. Бизнес-аналитика: от данных к знаниям. Учебное пособие. — СПб.: Питер, 2013. — 704 с.

2. Теория статистики: учебник / Р. А. Шмойлова, В. Г. Минашкин, Н. А. Садовникова, Е. Б. Шувалова; под ред. Р. А. Шмойловой. — М.: Финансы и статистика, 2014. — 656 с.

3. Винстон У. Л. Бизнес-моделирование и анализ данных. Решение актуальных задач с помощью Microsoft Excel. — СПб.: Питер, 2018. — 864 с.

4. Александер М. Excel 2019. Библия пользователя. Исчерпывающее руководство. — СПб.: Диалектика, 2019. — 1136 с.

5. Джелен Б., Александер М. Сводные таблицы в Microsoft Excel 2013. — М.: Вильямс, 2017. — 448 с.

6. Арьков В. Ю. Бизнес-аналитика в Excel. Сводные таблицы: Учебное пособие. — [б. м.]: Издательские решения, 2020. — 180 с.

Режим доступа:

https://ridero.ru/books/biznes-analitika_v_excel_svodnye_tablicy/

Content

Анализ и визуализация данных в электронных таблицах
Учебное пособие
Валентин Юльевич Арьков

Предисловие

Введение

Модель данных

Отчёт по работе

Анализ данных в PowerPivot

1. Установка или активизация Power Pivot

2. Загрузка из базы данных

3. Импорт данных из электронной таблицы

4. Связи между таблицами

5. Добавление связи в PowerPivot

6. Вычисляемое поле

7. Создание иерархии

8. Использование иерархии

Визуализация данных в Power View

9. Аналитические отчеты

Фильтрация картограммы

Вычисляемые поля

Интерактивные диаграммы

Детализация иерархии

Срезы на диаграмме

Анимация на диаграммах

Заключение

Литература