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

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).

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