Как создать модель данных в Excel (и когда это полезно)

17 марта 2022 г.

Если вы работаете с несколькими связанными таблицами в Excel, вы можете анализировать их данные вместе. Функция модели данных в Excel может помочь вам сделать это без использования функции ВПР() или других сложных функций отчетности. Понимание того, как создать модель данных в Excel, может упростить процесс визуализации данных и обеспечить правильное объединение данных. В этой статье мы обсудим, как создать модель данных в Excel и когда это может быть полезно.

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

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

Когда использовать модель данных в Excel

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

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

Как создать модель данных в Excel, создав новые таблицы

Вот список шагов по созданию модели данных в Excel путем создания новых таблиц:

1. Скомпилируйте все свои данные в таблицы

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

Если выбор правильный, убедитесь, что вы установили флажок «В моей таблице есть заголовки». Нажмите «ОК» в правом нижнем углу, чтобы увидеть, как ваши данные преобразуются в таблицу. Нажмите на вкладку «Дизайн таблицы» на ленте Excel. Найдите поле «Имя» и измените имя таблицы с «Таблица1» на что-то более отчетливое. Повторите этот процесс для всех ваших уникальных наборов данных. Убедитесь, что вы создаете модель данных только для таблиц, которые имеют хотя бы один общий столбец.

2. Нажмите на опцию «Подключения» на вкладке «Данные».

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

3. Выберите соответствующие параметры во всплывающем окне «Подключения к рабочей книге».

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

4. Откройте каждую таблицу, которую вы хотите добавить в свою модель данных.

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

5. Сведите данные

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

6. Создайте отношения в вашей модели данных

Нажмите на вкладку «Анализ» на ленте Excel. Выберите кнопку «Отношения». В диалоговом окне «Управление отношениями» нажмите кнопку «Создать». Выберите таблицы и столбцы, которые вы хотите включить в свои отношения. Нажмите «ОК». Создавайте столько отношений, сколько хотите. Обратите внимание, что Excel автоматически исправляет внешние и основные столбцы, если пользователь неправильно их выбирает.

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

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

1. Нажмите на вкладку «Данные».

2. Найдите группу «Получить внешние данные».

3. Выберите вариант «Из доступа», чтобы открыть всплывающее окно «Выбор источника данных».

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

5. Установите флажок «Включить выбор нескольких таблиц».

6. Выберите две или более таблиц в вашей базе данных.

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

8. Выберите параметр «Отчет сводной таблицы», который импортирует все выбранные вами таблицы в Excel и формирует сводную таблицу.

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

Обратите внимание, что ни одна из компаний, упомянутых в этой статье, не связана с компанией Indeed.