Валентин Юльевич Арьков
Анализ и визуализация данных в электронных таблицах. Учебное пособие
© Валентин Юльевич Арьков, 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?» на сайте поддержки:
Выясните, входит ли 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.
Выбираем только перечисленные выше значения.
Обратим внимание на появление значка фильтрации в виде символа воронки – рядом с заголовком «Виды спорта».
