Excel Power Pivot — изучение данных

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

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

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

Чтобы загрузить данные из базы данных Access, выполните следующие действия:

• Откройте новую пустую книгу в Excel.

• Нажмите «Управление» в группе «Модель данных».

• Нажмите вкладку POWERPIVOT на ленте.

Откройте новую пустую книгу в Excel.

Нажмите «Управление» в группе «Модель данных».

Нажмите вкладку POWERPIVOT на ленте.

Откроется окно Power Pivot.

• Перейдите на вкладку «Главная» в окне Power Pivot.

• Нажмите Из базы данных в группе Получить внешние данные.

• Выберите From Access из выпадающего списка.

Перейдите на вкладку «Главная» в окне Power Pivot.

Нажмите Из базы данных в группе Получить внешние данные.

Выберите From Access из выпадающего списка.

Появится мастер импорта таблиц.

• Укажите имя дружественного соединения .

• Найдите файл базы данных Access, Events.accdb, файл базы данных событий.

• Нажмите на кнопку Далее>.

Укажите имя дружественного соединения .

Найдите файл базы данных Access, Events.accdb, файл базы данных событий.

Нажмите на кнопку Далее>.

В мастере импорта таблиц отображаются параметры для выбора способа импорта данных.

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

Мастер импорта таблиц отображает все таблицы в базе данных Access, которые вы выбрали. Установите все флажки, чтобы выбрать все таблицы, и нажмите «Готово».

Мастер импорта таблиц отображает — Импорт и показывает состояние импорта. Это может занять несколько минут, и вы можете остановить импорт, нажав кнопку « Остановить импорт» .

После завершения импорта данных в мастере импорта таблиц отображается « Успешно» и отображаются результаты импорта. Нажмите Закрыть .

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

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

Вы можете заметить, что между таблицами существует связь — Дисциплины и Медали . Это связано с тем, что при импорте данных из реляционной базы данных, такой как Access, отношения, существующие в базе данных, также импортируются в модель данных в Power Pivot.

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

Создайте сводную таблицу с таблицами, которые вы импортировали в предыдущем разделе, следующим образом:

• Нажмите Сводная таблица на ленте.

• Выберите сводную таблицу из выпадающего списка.

• Выберите «Новый лист» в открывшемся диалоговом окне «Создать сводную таблицу» и нажмите кнопку «ОК».

Нажмите Сводная таблица на ленте.

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

Выберите «Новый лист» в открывшемся диалоговом окне «Создать сводную таблицу» и нажмите кнопку «ОК».

Пустая сводная таблица создается на новом листе в окне Excel.

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

• Перетащите поле NOC_CountryRegion в таблице медалей в область COLUMNS.

• Перетащите Discipline из таблицы Disciplines в область ROWS.

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

• Перетащите медаль из таблицы медалей в область ЗНАЧЕНИЯ.

• Снова выберите медаль из таблицы медалей и перетащите ее в область ФИЛЬТРЫ.

Перетащите поле NOC_CountryRegion в таблице медалей в область COLUMNS.

Перетащите Discipline из таблицы Disciplines в область ROWS.

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

Перетащите медаль из таблицы медалей в область ЗНАЧЕНИЯ.

Снова выберите медаль из таблицы медалей и перетащите ее в область ФИЛЬТРЫ.

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

Изучение данных с помощью сводной таблицы

Возможно, вы захотите отобразить только эти значения с Medal Count> 80. Для этого выполните следующие шаги —

• Нажмите на стрелку справа от метки столбца.

• Выберите Value Filters из выпадающего списка.

• Выберите Больше чем… . из второго выпадающего списка.

• Нажмите ОК.

Нажмите на стрелку справа от метки столбца.

Выберите Value Filters из выпадающего списка.

Выберите Больше чем… . из второго выпадающего списка.

Нажмите ОК.

Откроется диалоговое окно « Фильтр значений ». Введите 80 в крайнем правом поле и нажмите ОК.

Сводная таблица отображает только те регионы с общим количеством медалей более 80.

Вы можете получить нужный отчет из разных таблиц всего за несколько шагов. Это стало возможным благодаря существовавшим ранее отношениям между таблицами в базе данных Access. Когда вы одновременно импортировали все таблицы из базы данных, Power Pivot воссоздала отношения в своей модели данных.

Обобщение данных из разных источников в Power Pivot

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

• Создайте новый лист в книге.

• Создать таблицу в Excel — Спорт.

Создайте новый лист в книге.

Создать таблицу в Excel — Спорт.

Добавьте спортивный стол в модель данных.

Создайте связь между таблицами Disciplines и Sports с полем SportID .

Добавьте поле Спорт в сводную таблицу.

Перемешайте поля — Дисциплина и Спорт в области ROWS.

Расширение исследования данных

Вы можете получить таблицу « События» и в дальнейшем исследовании данных.

Создайте связь между таблицами « События» и « Медали» с полем DisciplineEvent .

Добавьте таблицу Hosts в рабочую книгу и модель данных.

Расширение модели данных с использованием вычисляемых столбцов

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

• Перейдите к таблице Hosts в представлении данных окна PowerPivot.

• Перейдите на вкладку «Дизайн» на ленте.

• Нажмите Добавить.

Перейдите к таблице Hosts в представлении данных окна PowerPivot.

Перейдите на вкладку «Дизайн» на ленте.

Нажмите Добавить.

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

• Введите следующую формулу DAX в строке формул = CONCATENATE ([Edition], [Season])

• Нажмите Ввод.

Введите следующую формулу DAX в строке формул = CONCATENATE ([Edition], [Season])

Нажмите Ввод.

Создается новый столбец с заголовком CalculatedColumn1, и столбец заполняется значениями, полученными из приведенной выше формулы DAX.

Щелкните правой кнопкой мыши новый столбец и выберите «Переименовать столбец» в раскрывающемся списке.

Введите EditionID в заголовке нового столбца.

Как видите, столбец EditionID имеет уникальные значения в таблице Hosts.

Создание отношения с использованием вычисляемых столбцов

Если вам необходимо создать связь между таблицей Hosts и таблицей Медали , столбец EditionID также должен существовать в таблице Медали. Создайте вычисляемый столбец в таблице медалей следующим образом:

• Нажмите на таблицу медалей в представлении данных Power Pivot.

• Перейдите на вкладку «Дизайн» на ленте.

• Нажмите Добавить.

Нажмите на таблицу медалей в представлении данных Power Pivot.

Перейдите на вкладку «Дизайн» на ленте.

Нажмите Добавить.

Введите формулу DAX в формуле bar = YEAR ([EDITION]) и нажмите Enter.

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

• Введите следующую формулу DAX в строке формул = CONCATENATE ([Год], [Сезон])

• Переименуйте новый столбец, созданный как EditionID .

Введите следующую формулу DAX в строке формул = CONCATENATE ([Год], [Сезон])

Переименуйте новый столбец, созданный как EditionID .

Как вы можете заметить, столбец EditionID в таблице медалей имеет значения, идентичные столбцу EditionID в таблице Hosts. Таким образом, вы можете создать связь между таблицами — Медали и Спорт с полем EditionID.

• Переключитесь на представление схемы в окне PowerPivot.

• Создайте связь между таблицами-медалями и хостами с полем, полученным из вычисляемого столбца, т.е. EditionID .

Переключитесь на представление схемы в окне PowerPivot.

Создайте связь между таблицами-медалями и хостами с полем, полученным из вычисляемого столбца, т.е. EditionID .

Теперь вы можете добавлять поля из таблицы Hosts в Power PivotTable.