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

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

Excel - мощный инструмент для анализа данных и последующей автоматизации при работе с большими наборами данных. Вы можете потратить много времени на анализ тонны данных с помощью ВПР, ИНДЕКС-МАТЧ, СУММЕСЛИ и т. Д.





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



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

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





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

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

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



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

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

Связанный: Как добавить вкладку разработчика на ленту в Microsoft Word и Excel



Как получить Power Query (получить и преобразовать)

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

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



3. Excel 2016 и более поздние версии: Вы можете найти Power Query (Получить и преобразовать), перейдя в Данные вкладка в Excel Лента .

Создание модели данных путем импорта данных в книгу Excel

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

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

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

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

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

3. Если вы используете версию Excel 2013, нажмите Power Query на Лента а затем выберите Получить внешние данные выбрать данные для импорта.

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

5. Щелкните на Нагрузка для завершения процесса импорта.

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

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

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

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

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

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

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

Связанный: Как создать анализ 'что, если' в Microsoft Excel

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

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

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

1. О Лента книги Excel щелкните значок Power Pivot меню.

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

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

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

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

  • Стол студенты | Студенческий билет к таблице Оценки | Студенческий билет
  • Таблица семестров | Идентификатор семестра в таблицу Оценки | Семестр
  • Табличные классы | Номер класса в таблицу Оценки | ID класса

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

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

7. В редакторе Power Pivot щелкните значок Дизайн вкладка, а затем выберите Управляйте отношениями чтобы узнать, в каких полях выполняются соединения.

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

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

По теме: Что такое интеллектуальный анализ данных и является ли он незаконным?

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

1. В редакторе Power Pivot щелкните значок Дом таб.

2. О Лента , нажмите на Сводная таблица .

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

построить свой собственный Android TV Box

4. Выбрать Ok . Excel добавит Сводная таблица это покажет Список полей панель справа.

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

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

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

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

Делиться Делиться Твитнуть Эл. адрес Как создать сводную таблицу в Excel

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

Читать далее Похожие темы
  • Продуктивность
  • Советы по работе с таблицами
  • Майкрософт Эксель
  • Советы по Microsoft Office
  • Анализ данных
Об авторе Тамал Дас(Опубликовано 100 статей)

Тамал - внештатный писатель в MakeUseOf. Получив значительный опыт в области технологий, финансов и бизнес-процессов на своей предыдущей работе в ИТ-консалтинговой компании, он начал писать как постоянную профессию 3 года назад. Он не пишет о продуктивности и последних технических новостях, но любит играть в Splinter Cell и смотреть Netflix / Prime Video.

Ещё от Tamal Das

Подписывайтесь на нашу новостную рассылку

Подпишитесь на нашу рассылку, чтобы получать технические советы, обзоры, бесплатные электронные книги и эксклюзивные предложения!

Нажмите здесь, чтобы подписаться