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

Используя модель данных, автоматизируйте задачи анализа данных и бизнес-моделирования в Microsoft Excel. Посмотрите, как это делается!

Excel – это мощный инструмент для анализа данных и последующей автоматизации при работе с большими наборами данных. Вы можете тратить значительное время на анализ тонн данных, используя VLOOKUP, INDEX-MATCH, SUMIF и т.д

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

Основные требования

Вам понадобятся Power Pivot и Power Query (Get & Transform) для выполнения нескольких задач при создании модели данных Excel. Вот как вы можете получить эти функции в своей рабочей книге Excel:

Как получить Power Pivot

1. Excel 2010: Вам нужно загрузить дополнение Power Pivot с сайта Microsoft, а затем установить его для программы Excel на вашем компьютере

2. Excel 2013: Office Professional Plus edition of Excel 2013 включает Power Pivot. Но перед первым использованием его необходимо активировать. Вот как это сделать:

1. Нажмите Файл на Ленте рабочей книги Excel.
2. Затем нажмите Параметры , чтобы открыть Параметры Excel.
3. Теперь нажмите на Дополнения.
4. Выберите COM Add-ins , нажав на выпадающее меню в поле Manage.
5. Нажмите на Go , а затем установите флажок для Microsoft Power Pivot for Excel.

3. Excel 2016 и более поздние версии: Вы найдете меню Power Pivot на ленте

Похожие: Как добавить вкладку ‘Разработчик’ на ленту в Microsoft Word и Excel

Как получить Power Query (Get & Transform)

1. Excel 2010: Вы можете загрузить дополнение Power Query с сайта Microsoft. После установки Power Query появится на ленте

2. Excel 2013: Вам необходимо активировать Power Query, выполнив те же действия, что и для обеспечения функциональности Power Pivot в Excel 2013

3. Excel 2016 и последующие версии: Вы можете найти Power Query (Get & Transform), перейдя на вкладку Данные на Ленте Excel

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

Для этого учебника вы можете получить предварительно отформатированные образцы данных от Microsoft:

Скачать : Образец данных студента (только данные) | Образец данных студента (полная модель)

Вы можете импортировать базу данных с несколькими связанными таблицами из многих источников, таких как рабочие книги Excel, Microsoft Access, веб-сайты, SQL Server и т.д. Затем необходимо отформатировать набор данных так, чтобы Excel мог его использовать. Вот шаги, которые вы можете попробовать выполнить:

1. В Excel 2016 и более поздних редакциях перейдите на вкладку Данные и выберите Новый запрос

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

3. Если используется редакция Excel 2013, нажмите Power Query на Ленте , а затем выберите Get External Data , чтобы выбрать данные для импорта

4. Появится окно Навигатор , в котором нужно выбрать, какие таблицы необходимо импортировать. Нажмите на флажок Выбрать несколько элементов , чтобы выбрать несколько таблиц для импорта

5. Нажмите на Load , чтобы завершить процесс импорта

6.Excel создаст для вас модель данных, используя эти таблицы. Заголовки столбцов таблицы можно увидеть в списках PivotTable Fields

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

1. Отформатируйте данные в табличной модели, выделив все ячейки, содержащие данные, а затем нажмите Ctrl+T

2. Теперь выделите всю таблицу, а затем щелкните вкладку Power Pivot на Ленте

3. В разделе Таблицы нажмите Добавить в модель данных

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

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

Похожие: Как создать анализ What-If в Microsoft Excel

Построение взаимосвязей между таблицами в модели данных

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

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

1. На Ленте рабочей книги Excel щелкните меню Power Pivot

2. Теперь нажмите на Управление в разделе Модель данных. Вы увидите редактор Power Pivot , как показано ниже:

3. Нажмите кнопку Вид диаграммы , расположенную в разделе Вид вкладки Power Pivot Главная. Вы увидите заголовки столбцов таблицы, сгруппированные в соответствии с названием таблицы

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

Ниже описана связь между таблицами:

• Таблица Students | Student ID к таблице Grades | Student ID.
• Таблица Семестры | ID семестра к таблице Оценки | Семестр.
• Таблица Классы | Номер класса к таблице Оценки | Идентификатор класса.

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

6. Вы заметите Star (*) на одной стороне и One (1) на другой в представлении диаграммы отношений. Это определяет, что между таблицами существует связь ‘один-ко-многим’

7. В редакторе Power Pivot перейдите на вкладку Design , а затем выберите Manage Relationships , чтобы узнать, какие поля создают связи

Создание PivotTable с использованием модели данных Excel

Теперь вы можете создать PivotTable или PivotChart для визуализации данных на основе модели данных Excel. Рабочая книга Excel может содержать только одну Модель данных, но вы можете постоянно обновлять таблицы

Похожие: Что такое добыча данных и является ли она незаконной?

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

1. В редакторе Power Pivot перейдите на вкладку Главная

2. На ленте Лента щелкните PivotTable

3. Выберите один из вариантов: ‘Новый рабочий лист’ или ‘Существующий рабочий лист’

4. Выберите OK.Excel добавит PivotTable , которая отобразит панель Field List справа

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

Преобразование сложных наборов данных в простые отчеты с помощью модели данных Excel

Модель данных Excel использует преимущества создания взаимосвязей между таблицами для создания значимых поворотных таблиц или диаграмм для целей представления данных

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