Создание модели данных в Excel

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

Прежде чем приступить к работе с моделью данных, необходимо получить некоторые данные. Для этого мы будем использовать интерфейс Get & Transform (Power Query), поэтому вы можете сделать шаг назад и watch видео или следовать нашему руководству по get & Transform и Power Pivot.

Где есть Power Pivot?

Excel 2016 & Excel для Microsoft 365 — Power Pivot включен в ленту.
Excel 2013 — Power Pivot входит в Office профессиональный плюс выпуск Excel 2013, но по умолчанию не включен. Узнайте больше о запуске надстройки PowerPivot для Excel 2013.
Excel 2010 — скачайте надстройку Power Pivot, а затем установите надстройку Power Pivot.

Где находится get & transform (Power Query)?

Excel 2016 & Excel для Microsoft 365 . Получение преобразования & (Power Query) интегрировано с Excel на вкладке Данные.
Excel 2013 — Power Query является надстройкой, которая входит в состав Excel, но ее необходимо активировать. Перейдите в раздел Параметры > файлов > надстройки, а затем в раскрывающемся списке Управление в нижней части панели выберите Надстройки COM > Go. Установите флажок Microsoft Power Query для Excel, а затем ОК, чтобы активировать его. Вкладка Power Query будет добавлена на ленту.
Excel 2010 — скачайте и установите надстройку Power Query.. После активации на ленту будет добавлена вкладка Power Query.

Начало работы

Сначала необходимо получить некоторые данные.

В Excel 2016 и Excel для Microsoft 365 используйте data > Get & Transform Data > Get Data для импорта данных из любого количества внешних источников данных, таких как текстовый файл, книга Excel, веб-сайт, Microsoft Access, SQL Server или другая реляционная база данных, содержащая несколько связанных таблиц.

В Excel 2013 и 2010 перейдите к Power Query > Получение внешних данных и выберите источник данных.
Excel предложит выбрать таблицу. Если вы хотите получить несколько таблиц из одного источника данных, проверка параметр Включить выбор нескольких таблиц. При выборе нескольких таблиц Excel автоматически создает модель данных.

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

Если необходимо изменить исходные данные, можно выбрать параметр Изменить . Дополнительные сведения см. в статье Введение в Редактор запросов (Power Query).

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

Примечания:

Модели создаются неявно, когда вы импортируете в Excel несколько таблиц одновременно.
Модели создаются явно, если вы импортируете данные с помощью надстройки Power Pivot. В надстройке модель представлена в макете с вкладками, похожем на Excel, где каждая вкладка содержит табличные данные. Сведения об импорте данных с помощью SQL Server базы данных см. в статье Получение данных с помощью надстройки PowerPivot.
Модель может содержать одну таблицу. Чтобы создать модель на основе только одной таблицы, выберите таблицу и нажмите кнопку Добавить в модель данных в Power Pivot. Это может понадобиться в том случае, если вы хотите использовать функции Power Pivot, например отфильтрованные наборы данных, вычисляемые столбцы, вычисляемые поля, ключевые показатели эффективности и иерархии.
Связи между таблицами могут создаваться автоматически при импорте связанных таблиц, у которых есть связи по первичному и внешнему ключу. Excel обычно может использовать импортированные данные о связях в качестве основы для связей между таблицами в модели данных.
Советы по уменьшению размера модели данных см. в статье Создание эффективной в памяти модели данных с помощью Excel и Power Pivot.
Дополнительные сведения см. в разделах Руководство. Импорт данных в Excel и Создание модели данных.

Совет: Как определить, есть ли в книге модель данных? Перейдите в Power Pivot > Управление. Если вы видите данные, похожие на лист, то модель существует. Дополнительные сведения см. в статье Сведения об источниках данных, используемых в модели данных книги .

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

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

Перейдите в Power Pivot > Управление.
На вкладке Главная выберите Представление схемы.
Будут отображаться все импортированные таблицы, и вам может потребоваться некоторое время, чтобы изменить их размер в зависимости от количества полей в каждой из них.
Затем перетащите поле первичного ключа из одной таблицы в другую. Ниже приведен пример представления схемы для таблиц учащихся:

Мы создали следующие ссылки:
- tbl_Students | Идентификатор учащегося > tbl_Grades | Идентификатор учащегося
  
  Другими словами, перетащите поле Идентификатор учащегося из таблицы Students в поле Идентификатор учащегося в таблице Оценки.
- tbl_Semesters | > tbl_Grades идентификатора семестра | Семестр
- tbl_Classes | Номер класса > tbl_Grades | Номер класса
Примечания:
- Имена полей не обязательно должны быть одинаковыми, чтобы создать связь, но они должны иметь один и тот же тип данных.
- Соединители в представлении диаграммы имеют значение "1" с одной стороны и "*" с другой. Это означает, что между таблицами существует связь "один ко многим", которая определяет, как данные используются в сводных таблицах. Дополнительные сведения см. в разделе Связи между таблицами в модели данных .
- Соединители указывают только на наличие связи между таблицами. Они фактически не показывают, какие поля связаны друг с другом. Чтобы просмотреть ссылки, перейдите в Power Pivot > Управление > проектирование > связей > Управление связями. В Excel можно перейти к разделу Связи > данных.

Создание сводной таблицы или сводной диаграммы с помощью модели данных

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

В Power Pivotперейдите в раздел Управление.
На вкладке Главная выберите Сводная таблица.
Выберите место размещения сводной таблицы: новый лист или текущее расположение.
Нажмите кнопку ОК, и Excel добавит пустую сводную таблицу с областью Список полей справа.

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

Добавление имеющихся несвязанных данных в модель данных

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

Начните с выбора любой ячейки в данных, которые нужно добавить в модель. Это может быть любой диапазон данных, но лучше всего форматировать данные в виде таблицы Excel .
Добавьте данные одним из следующих способов.
Щелкните Power Pivot > Добавить в модель данных.
Выберите Вставка > Сводная таблица и установите флажок Добавить эти данные в модель данных в диалоговом окне "Создание сводной таблицы".

Диапазон или таблица будут добавлены в модель как связанная таблица. Дополнительные сведения о работе со связанными таблицами в модели см. в статье Добавление данных с помощью связанных таблиц Excel в Power Pivot.

Добавление данных в таблицу Power Pivot

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