Как создать модель данных в excel

Модель данных в Excel | Как создать модель данных? (с примерами)

Что такое модель данных в Excel?

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

Объяснение

  • Он позволяет интегрировать данные из нескольких таблиц, создавая отношения на основе общего столбца.
  • Модели данных используются прозрачно, предоставляя табличные данные, которые можно использовать в сводной таблице в Excel и сводных диаграммах в Excel. Он объединяет таблицы, позволяя проводить обширный анализ с использованием сводных таблиц, Power Pivot и Power View в Excel.
  • Модель данных позволяет загружать данные в память Excel.
  • Он сохраняется в памяти, где мы не можем его непосредственно увидеть. Затем можно указать Excel связать данные друг с другом с помощью общего столбца. Часть «Модель» модели данных относится к тому, как все таблицы связаны друг с другом.
  • Модель данных может получить доступ ко всей необходимой информации, даже если информация находится в нескольких таблицах. После создания модели данных данные доступны в памяти Excel. Имея данные в своей памяти, к данным можно получить доступ разными способами.

Примеры

Пример # 1

Если у нас есть три набора данных, связанных с продавцом: первый содержит информацию о доходах, второй — доход продавца, а третий — расходы продавца.

Чтобы связать эти три набора данных и установить с ними связь, мы создаем модель данных, выполнив следующие шаги:

  • Преобразуйте наборы данных в объекты таблиц:

Мы не можем создать отношения с обычными наборами данных. Модель данных работает только с объектами таблиц Excel. Сделать это:

  • Шаг 1. Щелкните в любом месте набора данных, затем щелкните вкладку «Вставить», а затем щелкните «Таблица» в группе «Таблицы».
  • Шаг 2 — Установите или снимите флажок «Моя таблица имеет заголовки» и нажмите «ОК».
  • Шаг 3 — Выбрав новую таблицу, введите имя таблицы в поле «Имя таблицы» в группе «Инструменты».
  • Шаг 4 — Теперь мы видим, что первый набор данных преобразован в объект «Таблица». Повторяя эти шаги для двух других наборов данных, мы видим, что они также преобразуются в объекты «Таблица», как показано ниже:

Добавление объектов «Таблица» в модель данных: через соединения или отношения.

Через соединения

  • Выберите одну таблицу и щелкните вкладку «Данные», а затем щелкните «Подключения».
  • В появившемся диалоговом окне есть значок «Добавить». Разверните раскрывающийся список «Добавить» и нажмите «Добавить в модель данных».
  • В появившемся диалоговом окне нажмите «Таблицы», затем выберите одну из таблиц и нажмите «Открыть».

После этого будет создана модель данных книги с одной таблицей, и появится следующее диалоговое окно:

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

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

Через отношения

Создайте связь: как только оба набора данных являются объектами Table, мы можем создать связь между ними. Сделать это:

  • Щелкните вкладку «Данные», а затем щелкните «Взаимосвязи».
  • Мы увидим пустое диалоговое окно, так как текущих подключений нет.
  • Щелкните «Создать», появится другое диалоговое окно.
  • Разверните раскрывающиеся списки «Таблица» и «Связанная таблица»: появится диалоговое окно «Создать связь», в котором можно выбрать таблицы и столбцы для использования в связи. В раскрытии «Таблицы» выберите набор данных, который мы хотим проанализировать каким-либо образом, а в «Связанной таблице» выберите набор данных, который имеет значения поиска.
  • Таблица подстановки в Excel — это меньшая таблица в случае отношений один-ко-многим, и она не содержит повторяющихся значений в общем столбце. В раскрытии «Столбец (внешний)» выберите общий столбец в основной таблице, в «Связанный столбец (основной)» выберите общий столбец в связанной таблице.
  • Выбрав все эти четыре настройки, нажмите «ОК». При нажатии «ОК» появится диалоговое окно, как показано ниже.

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

Теперь Excel создает взаимосвязь за кулисами, объединяя данные в модели данных на основе общего столбца: ID продавца (в данном случае).

Пример # 2

Теперь предположим, что в приведенном выше примере мы хотим создать сводную таблицу, которая оценивает или анализирует объекты таблицы:

  • Нажмите «Вставить» -> «Сводная таблица».
  • В появившемся диалоговом окне выберите вариант «Использовать внешний источник данных», а затем нажмите «Выбрать соединение».
  • Нажмите «Таблицы» в появившемся диалоговом окне, выберите модель данных рабочей книги, содержащую три таблицы, и нажмите «Открыть».
  • Выберите опцию «Новый лист» в расположении и нажмите «ОК».
  • На панели полей сводной таблицы будут отображаться объекты таблицы.
  • Теперь можно внести соответствующие изменения в сводную таблицу, чтобы анализировать объекты таблицы по мере необходимости.

Например, в этом случае, если мы хотим найти общий доход или доход для конкретного продавца, сводная таблица создается следующим образом:

Это очень помогает в случае модели / таблицы, содержащей большое количество наблюдений.

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

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

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

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

Excel 2016 & Excel для Microsoft 365 — На ленте есть Power Pivot.

Excel 2013 — Power Pivot является частью Office профессиональный плюс версии Excel 2013, но по умолчанию не включена. Подробнее о запуске надстройки Power Pivot для Excel 2013.

Excel 2010: скачайте надстройку Power Pivotи установите надстройку Power Pivot,

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

Excel 2013 : Power Query — это надстройка, которая входит в состав Excel, но ее необходимо активировать. Перейдите в > файлов > надстройки ,а затем в меню Управление в нижней части области выберите Надстройки COM > Перейти. Проверьте, нет ли в 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)

Выберите одну или несколько таблиц и нажмите кнопку Загрузить.

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

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

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

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

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

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

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

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

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

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

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

Перейдите на > Power Pivot .

На вкладке Главная выберите представление диаграммы.

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

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

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

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

tbl_Students | ИД учащегося > tbl_Grades | ИД учащегося

Другими словами, перетащите поле "ИД учащегося" из таблицы "Учащиеся" в поле "ИД учащегося" в таблице "Оценки".

tbl_Semesters | ИД семестра > tbl_Grades | Семестр

tbl_Classes | Номер > tbl_Grades | Номер класса

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

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

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

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

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

В Power Pivot, перейдите вуправление .

На вкладке Главная выберите вкладку "PivotTable".

Выберите место для размещения таблицы: новый или текущий.

Нажмите кнопкуОК, Excel и справа отобразит пустую с помощью области Список полей пустую.

Список полей в power Pivot

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

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

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

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

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

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

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

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

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

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

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

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

Ссылка на основную публикацию