Excel Power Pivot — управление моделью данных

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

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

Добавление таблиц Excel в модель данных с помощью сводной таблицы

При создании сводной таблицы в Excel она основана только на одной таблице / диапазоне. Если вы хотите добавить больше таблиц в сводную таблицу, вы можете сделать это с помощью модели данных.

Предположим, у вас есть две таблицы в вашей книге —

• Один содержит данные о продавцах и регионах, которые они представляют, в таблице — продавец.

• Другой, содержащий данные о продажах по регионам и месяцам, в таблице — Продажи.

Один содержит данные о продавцах и регионах, которые они представляют, в таблице — продавец.

Другой, содержащий данные о продажах по регионам и месяцам, в таблице — Продажи.

Вы можете суммировать продажи в зависимости от продавца, как указано ниже.

• Нажмите на таблицу — Продажа.

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

• Выберите Сводная таблица в группе Таблицы.

Нажмите на таблицу — Продажа.

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

Выберите Сводная таблица в группе Таблицы.

Будет создана пустая сводная таблица с полями из таблицы продаж — Регион, Месяц и Сумма заказа. Как вы можете заметить, под списком полей сводной таблицы есть команда MORE TABLES .

• Нажмите на БОЛЬШЕ СТОЛОВ.

Нажмите на БОЛЬШЕ СТОЛОВ.

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

Новая сводная таблица будет создана, как показано ниже —

Под полями сводной таблицы вы можете видеть две вкладки — ACTIVE и ALL .

• Нажмите вкладку ВСЕ.

• Две таблицы — Sales и Salesperson с соответствующими полями отображаются в списке полей сводной таблицы.

• Щелкните поле «Продавец» в таблице «Продавец» и перетащите его в область ROWS.

• Щелкните поле «Месяц» в таблице «Продажи» и перетащите его в область ROWS.

• Щелкните поле «Сумма заказа» в таблице «Продажи» и перетащите его в область ∑ ЗНАЧЕНИЯ.

Нажмите вкладку ВСЕ.

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

Щелкните поле «Продавец» в таблице «Продавец» и перетащите его в область ROWS.

Щелкните поле «Месяц» в таблице «Продажи» и перетащите его в область ROWS.

Щелкните поле «Сумма заказа» в таблице «Продажи» и перетащите его в область ∑ ЗНАЧЕНИЯ.

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

Нажмите кнопку СОЗДАТЬ рядом с сообщением. Откроется диалоговое окно « Создать связь ».

• Под таблицей выберите Продажи.

• Под полем Column (Foreign) выберите Region.

• Под связанной таблицей выберите продавца.

• В поле « Связанный столбец (основной)» выберите «Регион».

• Нажмите ОК.

Под таблицей выберите Продажи.

Под полем Column (Foreign) выберите Region.

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

В поле « Связанный столбец (основной)» выберите «Регион».

Нажмите ОК.

Ваша сводная таблица из двух таблиц на двух листах готова.

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

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

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

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

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

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

Добавление таблиц Excel из другой книги в модель данных

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

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

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

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

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

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

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

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

• В поле Таблица / Диапазон введите Продажи.

• Нажмите на новый лист.

• Установите флажок Добавить эти данные в модель данных.

• Нажмите ОК.

В поле Таблица / Диапазон введите Продажи.

Нажмите на новый лист.

Установите флажок Добавить эти данные в модель данных.

Нажмите ОК.

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

Вы добавили данные таблицы продаж в модель данных. Затем вы должны получить данные таблицы Salesperson также в Data Model следующим образом:

• Нажмите на лист, содержащий таблицу продаж.

• Нажмите вкладку ДАННЫЕ на ленте.

• Нажмите Существующие подключения в группе Получить внешние данные. Откроется диалоговое окно «Существующие подключения».

• Нажмите на вкладку Таблицы.

Нажмите на лист, содержащий таблицу продаж.

Нажмите вкладку ДАННЫЕ на ленте.

Нажмите Существующие подключения в группе Получить внешние данные. Откроется диалоговое окно «Существующие подключения».

Нажмите на вкладку Таблицы.

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

• Нажмите «Продавец» в разделе «Salesperson.xlsx».

• Нажмите Открыть. Откроется диалоговое окно « Импорт данных ».

• Нажмите на отчет сводной таблицы.

• Нажмите на новый лист.

Нажмите «Продавец» в разделе «Salesperson.xlsx».

Нажмите Открыть. Откроется диалоговое окно « Импорт данных ».

Нажмите на отчет сводной таблицы.

Нажмите на новый лист.

Вы можете видеть, что флажок — Добавить эти данные в модель данных отмечен и неактивен. Нажмите ОК.

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

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

Добавление таблиц Excel в модель данных с ленты PowerPivot

Еще один способ добавления таблиц Excel в модель данных — сделать это с ленты PowerPivot .

Предположим, у вас есть две таблицы в вашей книге —

• Один, содержащий данные о продавцах и регионах, которые они представляют, в таблице — продавец.

• Другой, содержащий данные о продажах по регионам и месяцам, в таблице — Продажи.

Один, содержащий данные о продавцах и регионах, которые они представляют, в таблице — продавец.

Другой, содержащий данные о продажах по регионам и месяцам, в таблице — Продажи.

Вы можете добавить эти таблицы Excel в модель данных, прежде чем выполнять какой-либо анализ.

• Нажмите на таблицу Excel — Продажи.

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

• Нажмите Добавить в модель данных в группе Таблицы.

Нажмите на таблицу Excel — Продажи.

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

Нажмите Добавить в модель данных в группе Таблицы.

Появится окно Power Pivot с добавленной в него таблицей данных Salesperson. Далее на ленте в окне Power Pivot появляется вкладка — Связанная таблица.

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

• Нажмите на таблицу Excel: продавец.

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

Нажмите на таблицу Excel: продавец.

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

Нажмите Перейти к таблице Excel .

Откроется окно Excel с рабочим листом, содержащим таблицу Salesperson.

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

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

• Нажмите Добавить в модель данных в группе Таблицы на ленте.

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

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

Нажмите Добавить в модель данных в группе Таблицы на ленте.

Таблица продаж Excel также добавлена ​​в модель данных.

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

• Из представления данных

• Из представления схемы

Из представления данных

Из представления схемы

Создание отношений из представления данных

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

• Нажмите на вкладку «Дизайн» в окне Power Pivot.

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

Нажмите на вкладку «Дизайн» в окне Power Pivot.

Нажмите «Создать отношение» в группе «Отношения». Откроется диалоговое окно « Создать связь ».

• Нажмите на Продажи в поле Таблица. Это таблица, с которой начинаются отношения. Как вам известно, столбец должен быть полем, присутствующим в связанной таблице Salesperson, содержащей уникальные значения.

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

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

Нажмите на Продажи в поле Таблица. Это таблица, с которой начинаются отношения. Как вам известно, столбец должен быть полем, присутствующим в связанной таблице Salesperson, содержащей уникальные значения.

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

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

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

Нажмите кнопку Создать. Отношения созданы.

Создание отношений из представления схемы

Создание отношений из представления диаграммы относительно проще. Следуйте данным шагам.

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

• Нажмите «Вид диаграммы» в группе «Вид».

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

Нажмите «Вид диаграммы» в группе «Вид».

Представление схемы модели данных появится в окне Power Pivot.

• Нажмите на регион в таблице продаж. Регион в таблице продаж выделен.

• Перетащите в Регион в таблице Salesperson. Регион в таблице продавца также выделен. Линия появляется в направлении, которое вы перетащили.

• Из таблицы Sales в таблицу Salesperson появляется строка с указанием взаимосвязи.

Нажмите на регион в таблице продаж. Регион в таблице продаж выделен.

Перетащите в Регион в таблице Salesperson. Регион в таблице продавца также выделен. Линия появляется в направлении, которое вы перетащили.

Из таблицы Sales в таблицу Salesperson появляется строка с указанием взаимосвязи.

Как вы можете видеть, из таблицы Sales в таблицу Salesperson появляется линия, указывающая взаимосвязь и направление.

Если вы хотите знать поле, которое является частью отношения, щелкните по строке отношения. Строка и поле в обеих таблицах выделены.

Управление отношениями

Вы можете редактировать или удалять существующие отношения в модели данных.

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

• Нажмите «Управление отношениями» в группе «Отношения». Откроется диалоговое окно «Управление отношениями».

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

Нажмите «Управление отношениями» в группе «Отношения». Откроется диалоговое окно «Управление отношениями».

Будут отображены все отношения, существующие в модели данных.

Редактировать отношения

• Нажмите на Отношения.

• Нажмите кнопку Изменить . Откроется диалоговое окно « Редактировать отношения ».

Нажмите на Отношения.

Нажмите кнопку Изменить . Откроется диалоговое окно « Редактировать отношения ».

• Сделайте необходимые изменения в отношениях.

• Нажмите ОК. Изменения отражаются в отношениях.

Сделайте необходимые изменения в отношениях.

Нажмите ОК. Изменения отражаются в отношениях.

Чтобы удалить отношения

• Нажмите на Отношения.

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

• Нажмите OK, если вы уверены, что хотите удалить. Выбранное отношение будет удалено.

Нажмите на Отношения.

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

Нажмите OK, если вы уверены, что хотите удалить. Выбранное отношение будет удалено.

Обновление данных Power Pivot

Предположим, вы изменили данные в таблице Excel. Вы можете добавить / изменить / удалить данные в таблице Excel.

Чтобы обновить данные PowerPivot, выполните следующие действия:

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

• Нажмите Обновить все.

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

Нажмите Обновить все.

Таблица данных обновляется с учетом изменений, внесенных в таблицу Excel.

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