✅ Модель даних в Excel - Створення таблиць за допомогою функції моделі даних

Вступ до моделі даних в Excel

Модель даних Excel (Зміст)

Вступ до моделі даних в Excel
Як створити модель даних в Excel?

Вступ до моделі даних в Excel

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

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

Як створити модель даних в Excel?

Давайте розберемося, як створити модель даних в Excel з кількома прикладами.

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

Приклад №1

У нас є перелік товарів і у нас є код стелажів для кожного товару. Нам потрібна таблиця, де є опис стелажів разом з кодами стелажів. То як же ми включити описи стелажів до кожного коду стелажів? Можливо, багато хто з нас вдасться використовувати тут VLOOKUP, але ми взагалі усунемо необхідність використовувати тут VLOOKUP, використовуючи Excel Data Model.

Таблиця зліва - це таблиця даних, а праворуч - таблиця пошуку. Як ми бачимо з даних, можна створити відносини на основі загальних стовпців.

Тепер Модель даних сумісна лише з об'єктами таблиці. Отже, іноді може знадобитися перетворення наборів даних в об’єкти таблиці. Для цього виконайте наведені нижче дії.

Клацніть лівою кнопкою миші в будь-якому місці набору даних.
Перейдіть на вкладку Вставка та перейдіть до таблиці в групі Таблиці або просто натисніть Ctrl + T.
Зніміть прапорець або встановіть прапорець "Моя таблиця" опція Заголовок. У нашому прикладі він дійсно має заголовок. Натисніть кнопку ОК.
Поки все ще зосереджено на новій таблиці, нам потрібно вказати ім’я, яке має значення у полі Ім'я (ліворуч від рядка формул).

У нашому прикладі ми назвали таблицю Персонал.

Тепер нам потрібно виконати той самий процес для таблиці пошуку і назвати його Shelf Code.

Створення відносин

Отже, по-перше, ми перейдемо на вкладку «Дані», а потім виберемо «Зв’язки» у підгрупі «Інструменти даних». Після того, як ми натиснемо на варіант Відносини, спочатку, оскільки немає відносин, отже, у нас нічого не буде.

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

Тепер первинною таблицею є таблиця, в якій є дані. Це основна таблиця даних - Таблиця5. З іншого боку, пов'язана таблиця - це таблиця, в якій є дані пошуку - це наша таблиця пошуку ShelfCodesTable. Первинна таблиця - це та, яка аналізується на основі таблиці пошуку, яка містить дані пошуку, що зробить звітні дані в підсумку більш значущими.

Отже, загальний стовпчик між двома таблицями - це стовпець Код полиці. Це те, що ми використовували для встановлення зв’язку між двома таблицями. Переходячи до стовпців, стовпець (іноземний) - це той, який посилається на таблицю даних, де можуть бути дублюючі значення. З іншого боку, пов'язаний стовпець (первинний) посилається на стовпець таблиці пошуку, де у нас є унікальні значення. Ми просто встановлюємо поле для пошуку значень із таблиці пошуку в таблиці даних.
Як тільки ми це налаштуємо, Excel створив би стосунки між двома позаду сцени. Він інтегрує дані та створює модель даних на основі загального стовпця. Це не тільки освітлює потреби в пам'яті, але й набагато швидше, ніж використання VLOOKUP у великих робочих книгах. Визначивши модель даних, Excel буде обробляти ці об'єкти як таблиці моделі даних замість таблиці робочого аркуша.
Тепер, щоб побачити, чим займався Excel, ми можемо натиснути Керувати моделями даних у Дані -> Інструменти даних.

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

На діаграмі вище показано взаємозв'язок між значеннями унікальної таблиці пошуку та таблицею даних із дублюючими значеннями.
Тепер нам доведеться створити зведену таблицю. Для цього ми перейдемо на вкладку "Вставка", а потім натиснемо на пункт "Сводна таблиця".

У діалоговому вікні Створити таблицю зведеної таблиці виберемо джерело як "Використовувати модель даних цієї робочої книги".

Це створить таблицю зведення, і ми можемо побачити, що обидва вихідні таблиці доступні у розділі джерела.

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

Ми виберемо Персонал у розділі Рядки із таблиці 5 (таблиця даних), а потім Опис (таблиця пошуку).

Тепер ми перетягнемо Код полиці з таблиці 5 в розділ Значення.

Тепер ми додамо місяці з таблиці 5 до розділу Рядки.

Або ми можемо додати місяці як фільтр і додати їх до розділу Фільтри.

Приклад №2

Зараз у нас пан Басю працює на заводі під назвою корпорація Basu. Пан Басу намагається оцінити дохід за 2019 рік на основі даних 2018 року.
У нас є таблиця, де ми отримуємо дохід за 2018 рік та подальший дохід на різних додаткових рівнях.

Отже, у нас дохід за 2018 рік - 1, 5 млн доларів, а мінімальний приріст, який очікується наступного року, становить 12%. Пан Басу хоче таблицю, яка відображатиме дохід на різних поступових рівнях.
Ми створимо наступну таблицю для прогнозів на різних поступових рівнях на 2019 рік.

Тепер ми наведемо в першому рядку доходів посилання на передбачуваний мінімальну виручку на 2019 рік, тобто 1, 68 млн. Дол.

Після використання формули відповідь показана нижче.

Тепер виберемо всю таблицю, тобто D2: E12, а потім перейдемо до Дані -> Прогноз -> Що-якщо аналіз -> Таблиця даних.

Це відкриє діалогове вікно таблиці даних. Тут ми введемо мінімальний відсотковий приріст від комірки B4 у комірці Введення стовпців. Причиною тому є те, що наші прогнозовані прогнозні відсотки росту в таблиці розташовані стовпчиком.

Як тільки ми натиснемо кнопку ОК, аналіз What-If автоматично заповнить таблицю з прогнозованим доходом у різних додаткових відсотках.

Приклад №3

Припустимо, у нас той самий сценарій, що і вище, за винятком того, що зараз ми також маємо розглянути іншу вісь. Припустимо, окрім показу прогнозованого доходу на 2019 рік на основі даних 2018 року та мінімального очікуваного темпу зростання, ми також маємо прогнозовану ставку дисконту.

Спочатку у нас буде таблиця, показана нижче.

Тепер ми наведемо посилання на мінімальний прогнозований дохід на 2019 рік, тобто клітинку B5 до клітини D8.

Тепер виберемо всю таблицю, тобто D8: J18, а потім перейдемо до Дані -> Прогноз -> Що-якщо аналіз -> Таблиця даних.

Це відкриє діалогове вікно таблиці даних. Тут ми введемо мінімальний відсотковий відсоток від клітини B3 у комірці Введення стовпців. Причиною тому є те, що наші прогнозовані прогнозні відсотки росту в таблиці розташовані стовпчиком. Тепер ми також додатково введемо мінімальний відсоток знижки від комірки B4 у комірці введення рядків. Причиною цього є те, що наші прогнозовані відсоткові знижки в таблиці розташовані послідовно.

Натисніть кнопку ОК. Це зробить аналіз What-If автоматично заповнити таблицю прогнозованим доходом у різних додаткових відсотках відповідно до відсотків знижок.

Що слід пам’ятати про модель даних в Excel

Після успішного обчислення значень з таблиці даних, просто скасувати, тобто Ctrl + Z, не буде працювати. Однак можна вручну видалити значення з таблиці.
Видалити одну таблицю зі таблиці неможливо. Він описаний як масив внутрішньо в Excel, отже, нам доведеться видалити всі значення.
Нам потрібно правильно вибрати рядок вхідної комірки та вхідну комірку стовпця.
Таблицю даних, на відміну від зведеної таблиці, не потрібно оновлювати щоразу.
Використовуючи Модель даних в Excel, ми можемо не тільки підвищити продуктивність, але й полегшити вимоги пам’яті у великих робочих аркушах.
Моделі даних також роблять наш аналіз набагато простішим порівняно з використанням кількох складних формул у всій книжці.

Модель даних в Excel - Створення таблиць за допомогою функції моделі даних