✅ Модель данных в Excel - Создание таблиц с использованием функции модели данных

Введение в модель данных в Excel

Модель данных Excel (Оглавление)

Введение в модель данных в Excel
Как создать модель данных в Excel?

Введение в модель данных в Excel

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

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

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

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

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

Пример № 1

У нас есть список продуктов, и у нас есть код полки для каждого продукта. Нам нужен стол, где у нас есть описание стеллажей вместе с кодами стеллажей. Так как же мы включаем описания полок в каждый код полок? Возможно, многие из нас прибегнут к использованию VLOOKUP здесь, но мы полностью удалим необходимость использовать VLOOKUP здесь, используя Excel Data Model.

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

Теперь модель данных совместима только с объектами таблицы. Поэтому иногда может потребоваться преобразовать наборы данных в объекты таблиц. Для этого выполните следующие шаги.

Щелкните левой кнопкой мыши в любом месте набора данных.
Перейдите на вкладку «Вставка» и перейдите к таблице в группе «Таблицы» или просто нажмите Ctrl + T.
Снимите флажок или проверьте, что у Моей таблицы есть опция Заголовок. В нашем примере он действительно имеет заголовок. Нажмите ОК.
Несмотря на то, что мы по-прежнему сосредоточены на новой таблице, нам необходимо указать имя, которое имеет смысл в поле «Имя» (слева от строки формул).

В нашем примере мы назвали таблицу Personnel.

Теперь нам нужно выполнить тот же процесс для таблицы поиска и назвать ее Shelf Code.

Создание отношений

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

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

Теперь первичная таблица - это таблица с данными. Это первичная таблица данных - Таблица5. С другой стороны, Связанная таблица - это таблица, в которой есть данные поиска - это наша таблица поиска ShelfCodesTable. Первичная таблица - это та, которая анализируется на основе справочной таблицы, которая содержит справочные данные, что в конечном итоге сделает представленные данные более значимыми.

Таким образом, общий столбец между двумя таблицами - это столбец кода полки. Это то, что мы использовали, чтобы установить связь между двумя таблицами. Что касается столбцов, столбец (внешний) - это тот, который ссылается на таблицу данных, в которой могут быть повторяющиеся значения. С другой стороны, Связанный столбец (основной) ссылается на столбец в таблице поиска, где у нас есть уникальные значения. Мы просто настраиваем поле для поиска значений из таблицы поиска в таблице данных.
Как только мы настроим это, Excel создаст отношения между двумя за сценой. Он объединяет данные и создает модель данных на основе общего столбца. Это не только облегчает требования к памяти, но и намного быстрее, чем использование VLOOKUP в больших книгах. После определения модели данных Excel будет обрабатывать эти объекты как таблицы модели данных вместо таблицы рабочего листа.
Теперь, чтобы увидеть, чем занимался Excel, мы можем щелкнуть Управление моделями данных в разделе Данные -> Инструменты данных.

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

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

В диалоговом окне «Создание сводной таблицы» в сводной таблице мы выберем источник «Использовать модель данных этой книги».

Это создаст сводную таблицу, и мы увидим, что обе исходные таблицы доступны в разделе источников.

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

Мы выберем Персонал в разделе Строки из Таблицы 5 (таблица данных), а затем Описание (Таблица поиска).

Теперь перетащите код полки из таблицы 5 в раздел «Значения».

Теперь мы добавим месяцы из таблицы 5 в раздел строк.

Или мы могли бы добавить месяцы в качестве фильтра и добавить его в раздел «Фильтры».

Пример № 2

Теперь у нас есть господин Басу, управляющий заводом под названием Корпорация Басу. Г-н Басу пытается оценить выручку за 2019 год на основе данных за 2018 год.
У нас есть таблица, в которой мы имеем доход за 2018 год и последующий доход на разных уровнях приращения.

Итак, у нас выручка на 2018 год - 1, 5 млн долларов, а минимальный рост, ожидаемый в следующем году, составляет 12%. Г-н Басу хочет таблицу, которая будет показывать доход на разных уровнях.
Мы создадим следующую таблицу для прогнозов на разных дополнительных уровнях на 2019 год.

Теперь мы дадим в первой строке «Доход» ссылку на предполагаемый минимальный доход на 2019 год, т. Е. $ 1, 68 млн.

После использования формулы ответ показан ниже.

Теперь мы выберем всю таблицу, то есть D2: E12, а затем перейдем в Данные -> Прогноз -> Анализ «что если» -> Таблица данных.

Это откроет диалоговое окно Data Table. Здесь мы введем минимальный процент приращения от ячейки B4 в ячейке ввода столбца. Причина этого заключается в том, что наши прогнозируемые процентные доли роста в таблице расположены в столбчатой форме.

Как только мы нажмем кнопку ОК, анализ «Что, если» автоматически заполнит таблицу прогнозируемым доходом с различными добавочными процентами.

Пример № 3

Теперь предположим, что у нас тот же сценарий, что и выше, за исключением того, что теперь у нас есть еще одна ось для рассмотрения. Предположим, в дополнение к показу прогнозируемой выручки в 2019 году на основе данных за 2018 год и минимального ожидаемого темпа роста, у нас теперь также есть предполагаемая ставка дисконтирования.

Во-первых, у нас будет таблица, показанная ниже.

Теперь мы дадим ссылку на минимальный прогнозируемый доход на 2019 год, т.е. от ячейки B5 до ячейки D8.

Теперь мы выберем всю таблицу, т. Е. D8: J18, а затем перейдем в Данные -> Прогноз -> Анализ «что если» -> Таблица данных.

Это откроет диалоговое окно Data Table. Здесь мы введем минимальный процент приращения от ячейки B3 в ячейке ввода столбца. Причина этого заключается в том, что наши прогнозируемые процентные доли роста в таблице расположены в столбчатой форме. Теперь мы также дополнительно введем минимальный процент скидки от ячейки B4 в ячейке ввода строки. Причина этого заключается в том, что наши прогнозируемые проценты дисконтирования в таблице расположены в ряд.

Нажмите ОК. Это позволит анализу «что, если» автоматически заполнить таблицу прогнозируемым доходом с различными процентами приращения в соответствии с процентами дисконта.

Что нужно помнить о модели данных в Excel

После успешного вычисления значений из таблицы данных простая отмена, то есть Ctrl + Z, не будет работать. Однако можно вручную удалить значения из таблицы.
Невозможно удалить одну ячейку из таблицы. Он описывается как массив внутри Excel, поэтому нам придется удалить все значения.
Нам нужно правильно выбрать ячейку ввода строки и ячейку ввода столбца.
Таблицу данных, в отличие от сводной таблицы, не нужно обновлять каждый раз.
Используя модель данных в Excel, мы можем не только повысить производительность, но и облегчить требования к памяти в больших рабочих листах.
Модели данных также значительно упрощают наш анализ по сравнению с использованием ряда сложных формул по всей книге.

Модель данных в Excel - Создание таблиц с использованием функции модели данных