Модель данных в Excel

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

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

Объяснение

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

Модель данных в Excel

Примеры

Вы можете скачать этот шаблон модели данных Excel здесь — шаблон модели данных Excel

Пример # 1

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

Пример модели данных 1

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

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

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

  • Шаг 1 — Щелкните в любом месте набора данных, затем щелкните вкладку «Вставить», а затем щелкните «Таблица» в группе «Таблицы».

Пример модели данных 1-1

  • Шаг 2 — Установите или снимите флажок «Моя таблица имеет заголовки» и нажмите «ОК».

Пример модели данных 1-2

  • Шаг 3 — Выбрав новую таблицу, введите имя таблицы в поле «Имя таблицы» в группе «Инструменты».

Пример модели данных 1-3

  • Шаг 4 — Теперь мы видим, что первый набор данных преобразован в объект «Таблица». Повторяя эти шаги для двух других наборов данных, мы видим, что они также преобразуются в объекты «Таблица», как показано ниже:

Пример модели данных 1-4

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

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

  • Выберите одну таблицу и щелкните вкладку «Данные», а затем щелкните «Подключения».

Модель через соединения, пример 1-5

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

Модель через соединения, пример 1-6

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

Модель через соединения, пример 1-7

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

Модель через соединения Пример 1-8

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

Модель через соединения, пример 1-9

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

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

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

  • Щелкните вкладку «Данные», а затем щелкните «Взаимосвязи».

Модель через отношения, пример 1-10

  • Мы увидим пустое диалоговое окно, так как текущих подключений нет.

Модель через отношения, пример 1-11

  • Щелкните «Создать», появится другое диалоговое окно.

Модель через отношения, пример 1-12

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

Модель через отношения, пример 1-13

  • Выбрав все эти четыре параметра, нажмите «ОК». После нажатия кнопки «ОК» появится диалоговое окно следующего вида.

Модель через отношения, пример 1-14

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

Пример модели данных 1-15

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

Пример # 2

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

  • Нажмите «Вставить» -> «Сводная таблица».

Пример модели данных 2

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

Пример модели данных 2-1

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

Пример модели данных 2-2

  • Выберите опцию «Новый лист» в расположении и нажмите «ОК».

Пример модели данных 2-3

  • На панели полей сводной таблицы будут отображаться объекты таблицы.

Пример модели данных 2-4

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

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

Пример модели данных 2-5

Пример модели данных 2-6

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

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

То, что нужно запомнить

  • Используя модель данных, мы можем анализировать данные сразу из нескольких таблиц.
  • Создавая отношения с моделью данных, мы избавляемся от необходимости использовать ВПР, СУММЕСЛИ, ИНДЕКС и формулы ПОИСКПОЗ, поскольку нам не нужно получать все столбцы в одной таблице.
  • Когда наборы данных импортируются в Excel из внешних источников, модели создаются неявно.
  • Связи между таблицами могут быть созданы автоматически, если мы импортируем связанные таблицы, которые имеют отношения первичного и внешнего ключей.
  • При создании отношений столбцы, которые мы соединяем в таблицы, должны иметь один и тот же тип данных.
  • С помощью сводных таблиц, созданных с помощью модели данных, мы также можем добавлять срезы и срезать сводные таблицы по любому полю, которое нам нужно.
  • Преимущество модели данных перед функциями LOOKUP () состоит в том, что она требует значительно меньше памяти.
  • Excel 2013 поддерживает отношения только один к одному или один ко многим, т. Е. Одна из таблиц не должна иметь повторяющихся значений в столбце, на который мы ссылаемся.

УЗНАТЬ БОЛЬШЕ >>

Похожие записи

Добавить комментарий

Ваш адрес email не будет опубликован.