Пряма передача внутрішньої моделі даних Excels

Ви можете використовувати комбінацію зведених таблиць і підключень даних Excel для безпосередньої взаємодії з внутрішньою моделлю даних без надбудови Power Pivot. Це корисно, якщо ви використовуєте версії Excel, які не постачаються з надбудовою Power Pivot, наприклад, коли ви використовуєте Microsoft Office, домашній або малий бізнес. Кожна книга Excel 2013 і 2016 постачається з внутрішньою моделлю даних.

Уявіть, що у вас є таблиця «Транзакції», яку ви бачите тут, а на іншому робочому аркуші у вас є таблиця «Співробітники», яка містить інформацію про співробітників.

У цій таблиці показано операції за номером співробітника.

У цій таблиці наведено інформацію про працівників: ім’я, прізвище та посада.

Вам потрібно створити аналіз, який показує продажі за назвою посади. Зазвичай це було б важко, враховуючи той факт, що продажі та назва посади розміщені в двох окремих таблицях. Але з внутрішньою моделлю даних ви можете виконати ці прості кроки:

Клацніть всередині таблиці даних транзакцій і створіть нову зведену таблицю, вибравши «Вставити ➪ Зведена таблиця» на стрічці.

У діалоговому вікні Створення зведеної таблиці виберіть параметр Додати ці дані до моделі даних.

Коли ви створюєте нову зведену таблицю з таблиці «Транзакції», обов’язково виберіть «Додати ці дані до моделі даних».

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

Створіть нову зведену таблицю з таблиці Співробітники та виберіть Додати ці дані до моделі даних.

Щоб дати назву таблиці даних, просто виділіть усі дані в таблиці, а потім виберіть команду Формули→Визначити назву на стрічці. У діалоговому вікні введіть назву таблиці. Повторіть для всіх інших таблиць.

Після того, як обидві таблиці були додані до внутрішньої моделі даних, відкрийте список Поля зведеної таблиці та виберіть селектор ВСЕ. Цей крок показує обидва діапазони в списку полів.

Виберіть ВСЕ у списку Поля зведеної таблиці, щоб побачити обидві таблиці у внутрішній моделі даних.

Побудуйте зведену таблицю як зазвичай. У цьому випадку Job_Title розміщується в області «Рядок», а Sales_Amount — в області «Значення».
Як ви можете бачити тут, Excel одразу розпізнає, що ви використовуєте дві таблиці з внутрішньої моделі даних, і пропонує вам створити зв’язок між ними. У вас є можливість дозволити Excel автоматично визначати зв’язки між вашими таблицями або натиснути кнопку Створити. Завжди створюйте відносини самостійно, щоб уникнути будь-якої можливості помилки Excel.

Коли Excel запропонує вам створити зв’язок між двома таблицями.

Натисніть кнопку Створити.
Excel відкриває діалогове вікно Створення зв’язку, показане тут. Там ви вибираєте таблиці та поля, які визначають зв’язок. Ви можете побачити, що в таблиці Transactions є поле Sales_Rep. Це пов’язано з таблицею Employees через поле Employee_Number.

Побудуйте відповідні відносини за допомогою розкривних списків Таблиця та Стовпець.

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

Ви досягли своєї мети показати продажі за назвою посади.

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

Первинний ключ — це поле, яке містить лише унікальні ненульові значення (без дублікатів чи пробілів). Поля первинного ключа необхідні в моделі даних, щоб запобігти помилкам агрегації та дублюванням. Кожне створене вами відношення повинно мати поле, призначене як первинний ключ.

Таблиця Employees повинна мати всі унікальні значення в полі Employee_Number, без пробілів або нульових значень. Це єдиний спосіб, завдяки якому Excel може забезпечити цілісність даних при об’єднанні кількох таблиць.

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

Діалогове вікно «Керування зв’язками» дозволяє вносити зміни до зв’язків у внутрішній моделі даних.

Тут ви знайдете такі команди:

• Нове: Створіть новий зв’язок між двома таблицями у внутрішній моделі даних.
• Редагувати: змінити вибране відношення.
• Активувати: застосуйте вибране відношення, вказуючи Excel враховувати зв’язок під час зведення та аналізу даних у внутрішній моделі даних.
• Деактивувати: вимкнути вибране зв’язок, сказавши Excel ігнорувати зв’язок під час зведення та аналізу даних у внутрішній моделі даних.
• Видалити: видалення вибраного зв’язку.