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

Применяется к

Excel для Microsoft 365 Excel 2024 Excel 2021

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

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

Необходимые условия

Где есть Power Pivot?

Excel для Microsoft 365 — Power Pivot включен в ленту.

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

Excel для Microsoft 365 — Get & Transform (Power Query) интегрировано с Excel на вкладке "Данные".

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

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

Создайте новую книгу или откройте книгу, в которой нет данных.
На ленте в Excel для Microsoft 365 выберите вкладку "Данные". В разделе "Получение & преобразования данных" выберите "Получить данные" для импорта данных из любого числа внешних источников данных, таких как текстовый файл, книга Excel, веб-сайт, Microsoft Access, SQL Server или другая реляционная база данных, содержащая несколько связанных таблиц.
Excel предложит выбрать одну или несколько таблиц. Если вы хотите получить несколько таблиц из одного и того же источника данных, проведите проверку в поле "Выбрать несколько элементов".
1. Выберите "Преобразовать". При выборе нескольких таблиц Excel автоматически создает модель данных. Дополнительные сведения см. в следующих статьях: Создание, загрузка и изменение запроса в Excel (Power Query).
  
  Примечание
  
  В этих примерах используется книга Excel с вымышленными сведениями об учащихся по классам и оценкам. Вы можете скачать наш образец рабочей книги "Модель данных учащихся " и следить за ним. Вы также можете скачать версию с готовой моделью данных.
У вас есть модель данных, содержащая все импортированные таблицы, которые отображаются в списке полей сводной таблицы.

Примечание

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

Совет

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

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

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

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

Мы создали следующие ссылки:
- tbl_Students | Студенческий билет > tbl_Grades | Идентификатор учащегося
  Другими словами, перетащите поле "Идентификатор учащегося" из таблицы "Студенты" в поле "Идентификатор студента" в таблице "Оценки".
- 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.