Перейти к основному контенту
Office

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

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

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

Где есть Power Pivot?

Где найти & преобразования (Power Query)?

  • Excel 2016 & Excel для Office 365 — получение & преобразования (Power Query) с помощью Excel на вкладке " данные ".

  • Excel 2013 -Power Query — это надстройка, которая входит в состав Excel, но должна быть активирована. Перейдите в меню файл > Параметры > надстройки, а затем в раскрывающемся списке Управление в нижней части области выберите пункт надстройки com > Перейти. Установите флажок Microsoft Power Query для Excelи нажмите кнопку ОК , чтобы активировать его. Вкладка Power Query будет добавлена на ленту.

  • Excel 2010Загрузка и установка надстройки Power Query.. После активации вкладка Power Query будет добавлена на ленту.

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

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

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

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

  2. Excel предложит выбрать таблицу. Если вы хотите получить несколько таблиц из одного и того же источника данных, установите флажок Разрешить выбор нескольких таблиц . При выборе нескольких таблиц Excel автоматически создает модель данных.

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

    Навигатор "получение & преобразования" (Power Query)
  3. Выберите одну или несколько таблиц, а затем нажмите кнопку загрузить.

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

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

Примечания: 

  • Модели создаются неявно, когда вы импортируете в Excel несколько таблиц одновременно.

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

  • Модель может содержать одну таблицу. Чтобы создать модель на основе только одной таблицы, выберите таблицу и нажмите кнопку Добавить в модель данных в Power Pivot. Это может понадобиться в том случае, если вы хотите использовать функции Power Pivot, например отфильтрованные наборы данных, вычисляемые столбцы, вычисляемые поля, ключевые показатели эффективности и иерархии.

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

  • Советы по уменьшению размера модели данных можно найти в статье Создание модели данных с эффективным использованием памяти с помощью Excel и Power Pivot.

  • Дополнительные сведения можно найти в статье Учебник: импорт данных в Excel и создание модели данных.

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

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

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

  1. Перейдите в Power Pivot > Управление.

  2. На вкладке Главная нажмите кнопку представление диаграммы.

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

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

    Представление схемы отношений модели данных Power Query

    Мы создали следующие ссылки:

    • Тбл_студентс | КОД учащегося > Тбл_градес | ИДЕНТИФИКАТОР учащегося

      Другими словами, перетащите поле учащихся ID из таблицы Students в поле "код учащегося" в таблице "оценки".

    • Тбл_семестерс | Семестр, код > Тбл_градес | Семестра

    • Тбл_классес | Номер класса > Тбл_градес | Номер класса

    Примечания: 

    • Имена полей не должны быть одинаковыми для создания связи, но должны быть одного типа данных.

    • Соединительные линии в представлении схемы имеют "1" с одной стороны и "*" на другом. Это означает, что существует связь "один-ко-многим" между таблицами, которая определяет способ использования данных в сводных таблицах. Для получения дополнительных сведений ознакомьтесь со связями между таблицами в модели данных .

    • Соединители указывают на то, что между таблицами есть связь. Они не будут показывать, какие поля связаны друг с другом. Чтобы просмотреть ссылки, перейдите на вкладку Power Pivot > Управление > > связей > Управление связями. В Excel можно переходить к данным > связям.

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

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

  1. В Power Pivotвыберите Управление.

  2. На вкладке Главная нажмите кнопку Сводная таблица.

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

  4. Нажмите кнопку ОК, и Excel добавит пустую сводную таблицу с областью списка полей, которая отображается справа.

    Список полей сводной таблицы в Power Pivot

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

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

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

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

  2. Добавьте данные одним из следующих способов.

  3. Щелкните Power Pivot > Добавить в модель данных.

  4. Выберите Вставка > Сводная таблица и установите флажок Добавить эти данные в модель данных в диалоговом окне "Создание сводной таблицы".

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

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

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

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

См. также

Справочные руководства по & преобразованию и PowerPivot

Общие сведения о редакторе запросов (Power Query)

Создание модели данных с эффективным использованием памяти с помощью Excel и Power Pivot

Учебник: импорт данных в Excel и создание модели данных

Определение источников данных, используемых в модели данных книги

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

Примечание:  Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).

Совершенствование навыков работы с Office
Перейти к обучению
Первоочередный доступ к новым возможностям
Присоединиться к программе предварительной оценки Office

Были ли сведения полезными?

Спасибо за ваш отзыв!

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

×