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

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

Excel для Microsoft 365 Excel 2024 Excel 2021

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

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

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

Где есть Power Pivot?

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

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

Excel для Microsoft 365 . Получение преобразования & (Power Query) интегрировано с Excel на вкладке Данные.

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

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

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

Примечание

Модели создаются неявно, когда вы импортируете в Excel несколько таблиц одновременно.
Модели создаются явно при использовании надстройки PowerPivot для импорта данных. В надстройке модель представлена в макете с вкладками, похожем на Excel, где каждая вкладка содержит табличные данные. Сведения об импорте данных с помощью SQL Server базы данных см. в статье Получение данных с помощью надстройки Power Pivot.
Модель может содержать одну таблицу. Чтобы создать модель на основе одной таблицы, выберите таблицу и нажмите кнопку Добавить в модель данных в 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.

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

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