Расширенный анализ данных — модель данных

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

С помощью модели данных вы можете создавать отношения между таблицами. Модель данных прозрачно используется в отчетах PivotTable, PivotChart, PowerPivot и Power View.

Создание модели данных при импорте данных

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

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

• Реляционные базы данных, по одной таблице за раз
• Текстовые файлы
• Книги Excel

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

Если вы хотите добавить импортируемые данные в модель данных, установите флажок.

Создание модели данных из таблиц Excel

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

Все команды модели данных доступны на вкладке PowerPivot на ленте. С помощью этих команд вы можете добавить таблицы Excel в модель данных.

Рассмотрим следующую рабочую книгу с данными о продажах, в которой у вас есть лист каталога товаров, который содержит продукт, идентификатор продукта и цену. У вас есть четыре рабочих листа для продаж в 4 регионах — Восток, Север, Юг и Запад.

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

Следующие шаги позволяют вам достичь желаемых результатов —

• Начните с создания модели данных.
• Нажмите на лист каталога товаров.
• Нажмите вкладку POWERPIVOT на ленте.
• Нажмите Добавить в модель данных. Откроется диалоговое окно «Создать таблицу».
• Выберите диапазон таблицы.
• Установите флажок Моя таблица имеет заголовки. Нажмите ОК.

Появится новое окно — PowerPivot для Excel — <имя файла Excel>.

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

Таблица Product Backlog, добавленная в модель данных, отображается в виде листа в окне PowerPivot. Каждая строка в таблице является записью, и вы можете перемещаться вперед и назад, используя кнопки со стрелками влево и вправо в нижней части окна.

• Перейдите на вкладку «Связанная таблица» в окне PowerPivot.
• Нажмите Перейти к таблице Excel.

Откроется окно данных Excel.

• Нажмите вкладку листа — Восток.
• Нажмите вкладку POWERPIVOT на ленте.
• Нажмите Добавить в модель данных.

Другой лист появляется в окне PowerPivot с восточной таблицей.

Повторите для рабочих листов — Север, Юг и Запад. Всего вы добавили пять таблиц в модель данных. Ваше окно PowerPivot выглядит так:

Создание отношений между таблицами

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

• Перейдите на вкладку «Главная» на ленте в окне PowerPivot. Как вы можете видеть, таблицы отображаются в представлении данных.

• Нажмите Вид диаграммы.

Перейдите на вкладку «Главная» на ленте в окне PowerPivot. Как вы можете видеть, таблицы отображаются в представлении данных.

Нажмите Вид диаграммы.

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

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

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

В поле под таблицей отображается восток. В поле «Столбец» отображается идентификатор продукта.

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

Появляется строка, представляющая взаимосвязь между таблицами East и Product Backlog.

• Повторите те же шаги для таблиц — Север, Юг и Запад. Появляются Линии Отношений.

Суммирование данных в таблицах в модели данных

Теперь все готово для суммирования данных о продажах для каждого из продуктов в каждом регионе всего за несколько шагов.

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

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

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

• Выберите идентификатор продукта из таблицы 1 (каталог продукции).

• Выберите Общая сумма из четырех других таблиц.

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

Выберите идентификатор продукта из таблицы 1 (каталог продукции).

Выберите Общая сумма из четырех других таблиц.

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

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

Добавление данных в модель данных

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

Добавьте новую таблицу данных в модель данных, выполнив следующие шаги.

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

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

• Нажмите вкладку Таблицы. Будут отображены имена всех таблиц в рабочей книге.

• Щелкните по имени таблицы, которую вы хотите добавить в модель данных.

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

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

Нажмите вкладку Таблицы. Будут отображены имена всех таблиц в рабочей книге.

Щелкните по имени таблицы, которую вы хотите добавить в модель данных.

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

Как вы знаете, при импорте таблицы данных она автоматически добавляется в модель данных. Недавно добавленная таблица появится в окне PowerPivot.

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

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