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

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

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

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

Чтобы связать данные с таблицей, выполните указанные ниже действия.

1. Вы можете выбрать диапазон строк и столбцов, которые вы хотите использовать в связанной таблице.
2. Отформатирование строк и столбцов в виде таблицы
   • На ленте >в формате таблицы, а затем выберите стиль таблицы. Вы можете выбрать любой стиль, но не забудьте выбрать таблицу с заглавами. Если в таблице нет заглавных таблиц, можно создать их прямо сейчас. В противном Excel будут использовать произвольные имена (столбец1, столбец2 и так далее), которые не передают осмысленные сведения о содержимом столбца.
   • Присвойте таблице имя. В окне Excel нажмите кнопку Конструктор средств работы с таблицами. В группе Свойства введите имя таблицы.
3. Поместите курсор на любую ячейку таблицы.
4. Щелкните Power Pivot >добавить в модель данных, чтобы создать связанную таблицу. В окне Power Pivot вы увидите таблицу со значком ссылки, указывающим на то, что таблица связана с таблицей-источником в Excel:
5. Если модель уже содержит таблицы, то нужно сделать еще один шаг. Необходимо создать связь между новой таблицей, которую вы только что добавили, и другими таблицами в модели. Инструкции см. в таблицах Создание связи между двумя таблицами и Создание связей в представлении диаграммы.

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

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

Имена таблиц являются исключением. Если переименовать таблицу в Excel, потребуется обновить ее вручную в Power Pivot.

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

Синхронизация изменений между таблицей и моделью

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

В некоторых случаях может потребоваться контролировать поведение обновления. Переключение в режим обновления вручную с помощью надстройки Power Pivot.

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

1. Убедитесь, что книга со связанной таблицей открыта в Excel.
2. Откройте окно Power Pivot.
3. На вкладке внизу щелкните связанную таблицу. Любая связанная таблица обозначена маленьким значком ссылки рядом с ее именем.
4. В верхней части ленты щелкните Связанная таблица.
5. В режиме обновления выберите Вручную или Автоматически. По умолчанию этот режим является автоматическим. Если переключиться на ручное, обновления будут происходить только при использовании команд Обновить все или Обновить выбранные на ленте Связанной таблицы в окне Power Pivot или команды Обновить все на ленте Power Pivot в Excel.

Модель данных (язык Dax, расширение PowerPivot)¶

Power Pivot — это надстройка над Excel, представляющая из себя быструю колоночную базу данных VertiPaq с языком запросов DAX (часто вместо VertiPaq говорят Power Pivot). В отличие от Excel число строк загруженных в Power Pivot ограничено лишь размером доступной оперативной памяти компьютера. Быстродействие Power Pivot во много раз превосходит быстродействие формул в Excel. Также Power Pivot по производительности превосходит и Power Query (при этом часто он потребляет меньше ресурсов). Результаты выполнения запросов доступны пользователям в сводных таблицах и сводных диаграммах MS Excel. Таким образом сводные таблицы выступают аналитическим интерфейсом к данным хранящимся в Power Pivot.

Что такое Power Pivot в Power BI¶

В Power BI Power Pivot встроен как база данных, к которой присоединяются различные визуализации.

Что такое DAX¶

Dax (Data Analysis Expressions) — это язык программирования использующийся для запросов в базе данных VertiPaq.

Модель данных¶

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

Таблицы¶

Таблицы — совокупность строк, разделённых на столбцы.

У каждого столбца задан тип данных (который, как правило, наследуется из типов данных заданных для столбцов в Power Query).

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

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

Список из таблиц загруженных в модель данных можно найти в дереве fields в правой части окна Power BI.

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

‘название с пробелом’

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

Функции Dax¶

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

В качестве разделителей аргументов в зависимости от локали используются:

• «;» (и «,» для десятичных)
• «,» (и «.» для десятичных)

Меры (Measures)¶

Вычисляются только в момент использования. Рассчитываются в рамках текущего контекста фильтров. Именно это свойство позволяет строить интерактивные визуализации, которые фильтруются при нажатии на определенные области конкретных визуализаций. Меры хоть и принадлежат конкретной таблице, но могут быть перенесены в любую другую таблицу в рамках документа. Поэтому хорошая практика при использовании мер формулах не включать название таблицы, в которо й мера лежит. То есть вместо ‘таблица'[мера] писать просто [мера] .

Быстрые меры (Quick Measures)¶

Функционал быстрых мер позволяет создавать сложные DAX формулы без написания кода. Вместо написания кода нужно сконфигурировать желаемую меру в графическом интерфейсе. Подробнее о быстрых мерах можно прочитать в справке по Power BI

Вычисляемые Столбцы (Calculated Columns)¶

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

Подробнее про столбцы и меры читать здесь:

• Calculated Columns and Measures in DAX — SQLBI

Контекст выполнения функции (Evaluation context)¶

В Power Pivot существуют два контекста выполнения формулы, которые действуют одновременно:

• Контекст фильтров (Filter context)
• Контекст строк (Row context)

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

• Марко Руссо и Альберто Феррари https://www.sqlbi.com/.
• Справка Microsoft Power BI: Основные сведения о DAX в Power BI Desktop

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

Для связи таблиц в модели данных в одной из таблиц в ключевом поле должны быть уникальные значения. Также столбцы должны быть одного типа данных.

Направление связи имеет значение. Вычисляемые столбцы могут использоваться для создания связей между таблицами.

Справка: Создание связей и управление ими в Power BI Desktop — Power BI | Microsoft Docs

Связь таблиц по ключевому столбцу дата и time-intelligence функции¶

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

Часто используемые функции DAX¶

SUM (Столбец) — Cумма чисел по столбцу

COUNTA (Столбец) — Количество значений в столбце

DISTINCTCOUNT (Столбец) — Количество уникальных значений в столбце

SUMX (Таблица, Выражение) — Сумма значений выражения, которое выполняется для каждой строчки таблицы

DIVIDE (Значение числителя, значение знаменателя, альтернативный вариант в случае ошибки деления числителя на знаменатель) — Безопасное деление

IFERROR (Значение, Значение если ошибка) — Если ошибка

IF (Логическое выражение, значение если правда, значение если ложь ) — Если

Метрики контекстной рекламы в DAX¶

= SUM ( Клики ) / SUM ( Показы ) 

Excel Power Pivot – модель данных

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

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

В этой главе вы подробно разберетесь в модели данных.

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

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

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

• Нажмите вкладку POWERPIVOT на ленте.
• Нажмите Управление.

Нажмите вкладку POWERPIVOT на ленте.

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

Примечание. Если вы добавите таблицу Excel в модель данных, вы не преобразуете таблицу Excel в таблицу данных. Копия таблицы Excel добавляется в качестве таблицы данных в модель данных, и между ними создается связь. Следовательно, если изменения вносятся в таблицу Excel, таблица данных также обновляется. Однако с точки зрения хранения есть две таблицы.

Power Pivot и модель данных

Модель данных по своей сути является базой данных Power Pivot. Даже когда вы создаете модель данных из Excel, она создает только базу данных Power Pivot. Создание модели данных и / или добавление данных выполняется явно в Power Pivot.

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

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

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

Вы поймете, как добавить таблицы из Excel в модель данных в главе «Загрузка данных через Excel». Вы поймете, как загрузить данные в модель данных в главе «Загрузка данных в Power Pivot».

Таблицы в модели данных

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

Таблицы в модели данных называются таблицами данных.

Таблица в модели данных рассматривается как набор записей (запись – это строка), состоящая из полей (поле – это столбец). Вы не можете редактировать отдельные элементы в таблице данных. Однако вы можете добавить строки или добавить вычисляемые столбцы в таблицу данных.

Таблицы Excel и таблицы данных

Таблицы Excel – это просто набор отдельных таблиц. На рабочем листе может быть несколько таблиц. Доступ к каждой таблице возможен отдельно, но невозможно получить доступ к данным из более чем одной таблицы Excel одновременно. Это причина того, что при создании сводной таблицы она основана только на одной таблице. Если вам нужно использовать данные из двух таблиц Excel вместе, вам необходимо сначала объединить их в одну таблицу Excel.

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

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

• Просмотр данных.
• Диаграмма

Представление данных модели данных

В представлении данных модели данных каждая таблица данных находится на отдельной вкладке. Строки таблицы данных являются записями, а столбцы представляют собой поля. Вкладки содержат имена таблиц, а заголовки столбцов являются полями в этой таблице. Вы можете выполнять вычисления в представлении данных с использованием языка выражений анализа данных (DAX).

Представление схемы модели данных

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

Отношения в модели данных

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

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

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

• Просмотр данных – Использование диалогового окна «Создать связь».
• Представление схемы – нажав и перетащив, чтобы соединить две таблицы.

Просмотр данных – Использование диалогового окна «Создать связь».

Представление схемы – нажав и перетащив, чтобы соединить две таблицы.

Диалоговое окно «Создать связь»

В отношениях участвуют четыре объекта –

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

Таблица – таблица данных, с которой начинается связь.

Столбец – поле в таблице, которое также присутствует в соответствующей таблице.

Связанная таблица – таблица данных, где заканчивается связь.

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

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

Подробнее об отношениях вы узнаете в главе «Управление таблицами данных и отношениями с Power Pivot».

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

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

Прежде чем приступить к работе с моделью данных, необходимо получить некоторые данные. Для этого мы будем использовать интерфейс Get & Transform (Power Query), поэтому вам может потребоваться выполнить шаг назад и посмотреть видео, или следуйте нашему руководству по обучению по get & 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 . Get & Transform (Power Query) интегрировано с Excel на вкладке «Данные«.
• Excel 2013 — Power Query — это надстройка, которая входит в Excel, но ее необходимо активировать. Перейдите к разделу «Параметры >» > надстроек, а затем в раскрывающемся списке «Управление» в нижней части панели выберите com-надстройки >Go. Проверьте microsoft Power Query Excel, а затем ОК, чтобы активировать его. На Power Query будет добавлена вкладка Power Query.
• Excel 2010 — скачивание и установка Power Query надстройки.. После активации на ленту Power Query вкладки.

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

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

1. В Excel 2016 и Excel для Microsoft 365 используйте data >Get & Transform Data >Get Data > Get Data to import data from any number of external data sources, such as a text file, Excel workbook, website, Microsoft Access, SQL Server, or another relational database that contains multiple related tables. В Excel 2013 и 2010 перейдите к Power Query >получения внешних данных и выберите источник данных.
2. Excel предложит выбрать таблицу. Если вы хотите получить несколько таблиц из одного источника данных, установите флажок «Включить выбор нескольких таблиц «. При выборе нескольких таблиц Excel автоматически создает модель данных.

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

Выберите одну или несколько таблиц и нажмите кнопку «Загрузить «. Если необходимо изменить исходные данные, можно выбрать параметр «Изменить «. Дополнительные сведения см. в статье «Общие сведения Редактор запросов (Power Query)».

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

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

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

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

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

1. Перейдите в power Pivot >Manage.
2. На вкладке « Главная» выберите » Представление схемы».
3. Будут отображены все импортированные таблицы, и может потребоваться некоторое время, чтобы изменить их размер в зависимости от количества полей в каждой из них.
4. Затем перетащите поле первичного ключа из одной таблицы в следующую. В следующем примере показано представление схемы таблиц учащихся. Мы создали следующие ссылки:
   • tbl_Students | Идентификатор учащегося > tbl_Grades | Идентификатор учащегося Другими словами, перетащите поле «Идентификатор учащегося» из таблицы «Учащиеся» в поле «Идентификатор учащегося» в таблице «Оценки».
   • tbl_Semesters | Идентификаторы > tbl_Grades | Семестр
   • tbl_Classes | Номер класса > tbl_Grades | Номер класса

• Имена полей не обязательно должны совпадать для создания связи, но они должны быть одинаковыми типами данных.
• Соединители в представлении схемы имеют «1» с одной стороны, а «*» — с другой. Это означает, что между таблицами существует связь «один ко многим», которая определяет, как данные используются в сводных таблицах. См. дополнительные сведения о связях между таблицами в модели данных.
• Соединители указывают только на наличие связи между таблицами. На самом деле они не показывают, какие поля связаны друг с другом. Чтобы просмотреть ссылки, перейдите в раздел Power Pivot >Manage >Design >Relationships >Управление связями. В Excel можно перейти к разделу «>данных».

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

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

1. В Power Pivotперейдите к разделу » Управление».
2. На вкладке « Главная» выберите сводную таблицу.
3. Выберите место размещения сводной таблицы: новый лист или текущее расположение.
4. Нажмите кнопку «ОК», и Excel добавит пустую сводную таблицу с областью списка полей справа.

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

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

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

1. Начните с выбора любой ячейки в данных, которые необходимо добавить в модель. Это может быть любой диапазон данных, но лучше всего использовать данные, отформатированные в виде таблицы Excel .
2. Добавьте данные одним из следующих способов.
3. Щелкните Power Pivot >Добавить в модель данных.
4. Выберите Вставка >Сводная таблица и установите флажок Добавить эти данные в модель данных в диалоговом окне «Создание сводной таблицы».

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

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

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

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

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.