Модель даних в Excel | Як створити модель даних? (з прикладами)

Що таке модель даних у Excel?

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

Пояснення

  • Це дозволяє інтегрувати дані з декількох таблиць шляхом створення зв'язків на основі загального стовпця.
  • Моделі даних використовуються прозоро, забезпечуючи табличні дані, які можна використовувати у зведеній таблиці в Excel та зведених діаграмах у Excel. Він інтегрує таблиці, забезпечуючи широкий аналіз за допомогою зведених таблиць, Power Pivot та Power View в Excel.
  • Модель даних дозволяє завантажувати дані в пам’ять Excel.
  • Він зберігається в пам'яті там, де ми не можемо його безпосередньо побачити. Тоді Excel може отримати вказівку пов’язувати дані між собою за допомогою загального стовпця. Частина "Модель" Моделі даних стосується того, як всі таблиці співвідносяться між собою.
  • Модель даних може отримати доступ до всієї необхідної інформації, навіть коли інформація міститься в декількох таблицях. Після створення моделі даних Excel зберігає дані в своїй пам’яті. Маючи дані у своїй пам’яті, доступ до даних можна отримати різними способами.

Приклади

Ви можете завантажити цей шаблон даних моделі Excel тут - шаблон моделі даних Excel

Приклад №1

Якщо ми маємо три набори даних, пов’язані з продавцем: перший, що містить інформацію про доходи, другий, що містить дохід продавця, і третій, що містить витрати продавця.

Щоб зв’язати ці три набори даних та встановити зв’язок із ними, ми створюємо Модель даних із наступними кроками:

  • Перетворити набори даних в об’єкти таблиці:

Ми не можемо створити взаємозв'язок із звичайними наборами даних. Модель даних працює лише з об’єктами таблиць Excel. Зробити це:

  • Крок 1 - Клацніть де завгодно всередині набору даних, потім клацніть на вкладці «Вставити», а потім натисніть «Таблиця» в групі «Таблиці».

  • Крок 2 - Поставте або зніміть прапорець біля опції «Моя таблиця має заголовки» та натисніть «ОК».

  • Крок 3 - Вибравши нову таблицю, введіть назву таблиці в розділі «Назва таблиці» у групі «Інструменти».

  • Крок 4 - Тепер ми можемо бачити, що перший набір даних перетворюється на об'єкт "Таблиця". Повторюючи ці кроки для двох інших наборів даних, ми бачимо, що вони також перетворюються на об'єкти "Таблиця", як показано нижче:

Додавання об’єктів „Таблиця” до моделі даних: через зв’язки або зв’язки.

Через з'єднання

  • Виберіть одну таблицю та натисніть на вкладку "Дані", а потім натисніть "Підключення".

  • У діалоговому вікні, що вийшло, є піктограма "Додати". Розгорніть спадне меню "Додати" та натисніть "Додати до моделі даних".

  • Клацніть на «Таблиці» в діалоговому вікні, що з’явиться, а потім виберіть одну з таблиць і натисніть «Відкрити».

Після цього буде створена модель даних книги з однією таблицею, і з'явиться діалогове вікно наступним чином:

Отже, якщо ми повторимо ці кроки і для двох інших таблиць, модель даних тепер буде містити всі три таблиці.

Тепер ми можемо бачити, що всі три таблиці відображаються в Workbook Connections.

Через відносини

Створити взаємозв'язок: Коли обидва набори даних є об'єктами таблиці, ми можемо створити зв'язок між ними. Зробити це:

  • Клацніть на вкладку "Дані", а потім натисніть "Зв'язки".

  • Ми побачимо порожнє діалогове вікно, оскільки немає поточних з’єднань.

  • Клацніть на 'New', і з'явиться інше діалогове вікно.

  • Розгорніть спадні меню "Таблиця" та "Пов'язана таблиця": З'явиться діалогове вікно "Створити зв'язок", щоб вибрати таблиці та стовпці, які використовуватимуться для зв'язку. У розгортанні "Таблиці" виберіть набір даних, який ми хотіли б проаналізувати певним чином, а в "Пов'язана таблиця" виберіть набір даних, що має значення пошуку.
  • Таблиця підстановки в Excel - це менша таблиця у випадку співвідношень один до багатьох, і вона не містить повторюваних значень у загальному стовпці. У розгортанні "Стовпець (іноземний)" виберіть загальний стовпець у головній таблиці, у "Пов’язаний стовпець (основний)" виберіть загальний стовпець у відповідній таблиці.

  • Вибравши всі ці чотири налаштування, натисніть «ОК». Після натискання кнопки "OK" з'явиться діалогове вікно, як показано нижче.

Якщо ми повторимо ці кроки, щоб пов’язати дві інші таблиці: Таблиця доходів із таблицею витрат, тоді вони також зв’язані в моделі даних наступним чином:

Тепер Excel створює взаємозв’язок за лаштунками, об’єднуючи дані в моделі даних на основі загального стовпця: Ідентифікатор продавця (у даному випадку).

Приклад №2

Тепер, скажімо, у наведеному вище прикладі ми хочемо створити зведену таблицю, яка обчислює або аналізує об’єкти таблиці:

  • Клацніть на "Вставити" -> "Зведена таблиця".

  • У діалоговому вікні, що з’явиться, натисніть опцію із зазначенням: «Використовувати зовнішнє джерело даних», а потім натисніть «Вибрати підключення».

  • Клацніть на «Таблиці» у діалоговому вікні, що з’явиться, і виберіть Модель даних робочої книги, що містить три таблиці, та натисніть «Відкрити».

  • Виберіть у цьому місці опцію «Новий аркуш» і натисніть «ОК».

  • На панелі полів зведеної таблиці відображатимуться об’єкти таблиці.

  • Тепер зміни у зведеній таблиці можна зробити відповідно, щоб проаналізувати об’єкти таблиці за необхідності.

Наприклад, у цьому випадку, якщо ми хочемо знайти загальний дохід або дохід для конкретного продавця, то зведна таблиця створюється наступним чином:

Це надзвичайно допомагає у випадку моделі / таблиці, що містить велику кількість спостережень.

Отже, ми бачимо, що зведена таблиця миттєво використовує модель даних (вибираючи її, вибираючи з’єднання) в пам’яті Excel для відображення взаємозв’язків між таблицями.

Що слід пам’ятати

  • Використовуючи модель даних, ми можемо аналізувати дані з кількох таблиць одночасно.
  • Створюючи взаємозв'язки з Моделью даних, ми перевершуємо необхідність використання формул VLOOKUP, SUMIF, INDEX і MATCH, оскільки нам не потрібно отримувати всі стовпці в одній таблиці.
  • Коли набори даних імпортуються в Excel із зовнішніх джерел, тоді моделі створюються неявно.
  • Зв'язки таблиць можуть бути створені автоматично, якщо ми імпортуємо пов'язані таблиці, які мають зв'язки первинного та зовнішнього ключа.
  • Під час створення зв'язків стовпці, які ми з'єднуємо в таблиці, повинні мати однаковий тип даних.
  • За допомогою зведених таблиць, створених за допомогою Моделі даних, ми також можемо додати зрізи та нарізати зведені таблиці в будь-якому полі, яке ми хочемо.
  • Перевага моделі даних перед функціями LOOKUP () полягає в тому, що вона вимагає значно менше пам'яті.
  • Excel 2013 підтримує лише один до одного або один до багатьох відносин, тобто одна з таблиць не повинна мати повторюваних значень у стовпці, до якого ми посилаємося.