Створення моделі даних у програмі Excel

Застосовується до

Excel для Microsoft 365 Excel 2024 Excel 2021

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

Перш ніж почати роботу з моделлю даних, потрібно отримати деякі дані. Для цього ми скористаємося функцією Power Query Get & Transform Experience, щоб ви могли зробити крок назад і переглянути відео або виконати навчальний посібник на веб-сайті Get & Transform and Power Pivot. Дані мають бути в таблицях (а не лише в діапазонах клітинок), щоб їх можна було завантажити та пов'язано правильно.

Попередні вимоги

Програми з підтримкою PowerPivot

Excel для Microsoft 365 – надбудову Power Pivot включено до стрічки.

Де знайти засіб перетворення & (Power Query)?

Excel для Microsoft 365 – засіб перетворення & (Power Query) інтегровано з Excel на вкладці Дані.

Початок

Спочатку потрібно отримати деякі дані.

Створіть нову або відкрийте книгу, яка не містить даних.
На стрічці в Excel для Microsoft 365 виберіть вкладку Дані. У розділі Отримати & перетворення даних виберіть пункт Отримати дані, щоб імпортувати дані з будь-якої кількості зовнішніх джерел даних, наприклад текстового файлу, книги Excel, веб-сайту, Microsoft Access, SQL Server або іншої реляційної бази даних, яка містить кілька пов'язаних таблиць.
Програма Excel запропонує вибрати одну або кілька таблиць. Якщо потрібно отримати кілька таблиць з одного джерела даних, установіть прапорець Вибрати кілька елементів .
1. Натисніть кнопку Перетворення. Якщо вибрати кілька таблиць, Excel автоматично створить модель даних. Докладні відомості див. в статті Створення, завантаження або редагування запиту в Програмі Excel (Power Query).
  
  Примітка.
  
  У цих прикладах ми використовуємо книгу Excel із вигаданими відомостями про учнів для класів і оцінок. Ви можете завантажити зразок книги моделі даних студента та виконати вказівки. Ви також можете завантажити версію з заповненою моделлю даних.
Тепер у вас є модель даних, яка містить усі імпортовані таблиці, і вони відображатимуться в списку полів зведеної таблиці.

Примітка.

Моделі створюються неявно, коли ви імпортуєте кілька таблиць одночасно в Excel.
Моделі створюються явно під час використання надбудови Power Pivot для імпорту даних. У надбудові модель представлено в макеті із вкладками, схожому на Excel, де кожна вкладка містить табличні дані. Щоб дізнатися, як імпортувати дані за допомогою SQL Server бази даних, див. статтю Отримання даних за допомогою надбудови Power Pivot.
Модель може містити одну таблицю. Щоб створити модель на основі однієї таблиці, виберіть її та натисніть кнопку Додати до моделі даних у надбудові Power Pivot. Це можна зробити, якщо потрібно використовувати такі функції Power Pivot, як фільтровані набори даних, обчислювані стовпці, обчислювані поля, KPI та ієрархії.
Зв'язки між таблицями можна створити автоматично, якщо імпортувати пов'язані таблиці зі зв'язками первинного та зовнішнього ключів. Зазвичай в Excel імпортовані відомості про зв'язки можна використовувати як основу для зв'язків між таблицями в моделі даних.
Поради зі зменшення розміру моделі даних див. в статті Створення моделі даних з ефективним використанням пам'яті за допомогою excel і Power Pivot.
Докладні відомості див. в статті Посібник: імпорт даних до Програми Excel і Створення моделі даних.

Порада.

Як визначити, чи є в книзі модель даних? Перейдіть дорозділу Керування НадбудовоюPower Pivot>. Якщо відображаються дані аркуша, модель існує. Докладні відомості див. в статті Визначення джерел даних, які використовуються в моделі даних книги .

Створення зв'язків між таблицями

Далі потрібно створити зв'язки між таблицями, щоб отримувати дані з будь-якого з них. У кожній таблиці має бути первинний ключ або унікальний ідентифікатор поля, наприклад "Ідентифікатор студента" або "Номер класу". Найпростіший спосіб – перетягнути ці поля, щоб з'єднати їх у поданні схеми Power Pivot.

Перейдіть дорозділу Керування НадбудовоюPower Pivot>.
На вкладці Основне натисніть кнопку Подання схеми.
Відобразяться всі імпортовані таблиці, і вам може знадобитися деякий час, щоб змінити їх розмір залежно від кількості полів у кожній із них.
Потім перетягніть поле первинного ключа з однієї таблиці до наступної. Нижче наведено приклад подання схеми таблиць учнів.

Ми створили такі посилання:
- tbl_Students | Ідентифікатор > студента tbl_Grades | Ідентифікатор студента
  Іншими словами, перетягніть поле "Ідентифікатор студента" з таблиці "Студенти" до поля "Ідентифікатор студента" в таблиці "Оцінки".
- tbl_Semesters | Ідентифікатор > семестру tbl_Grades | Семестр
- tbl_Classes | Номер > класу tbl_Grades | Номер класу
Примітка.
- Щоб створити зв'язок, імена полів не обов'язково мають бути однаковими, але вони мають бути однакового типу даних.
- Сполучні лінії в поданні схеми мають "1" з одного боку, а "*" – з іншого. Це означає, що між таблицями існує зв'язок "один-до-багатьох", який визначає, як дані використовуються у зведених таблицях. Докладні відомості див. в статті Зв'язки між таблицями в моделі даних .
- Сполучні лінії вказують лише на те, що між таблицями існує зв'язок. Вони насправді не показують, які поля пов'язані між собою. Щоб переглянути посилання, перейдіть до розділуКерування>зв'язкамиконструктора>в надбудові> Power Pivot >Керування зв’язками. У програмі Excel можна перейти до розділуЗв'язкиданих>.

Створення зведеної таблиці або зведеної діаграми за допомогою моделі даних

Книга Excel може містити лише одну модель даних, але вона може містити кілька таблиць, які можна використовувати кілька разів у книзі. Ви можете додати інші таблиці до наявної моделі даних у будь-який час.

У надбудові Power Pivot перейдіть до розділу Керування.
На вкладці Основне натисніть кнопку Зведена таблиця.
Виберіть розташування зведеної таблиці: новий аркуш або поточне розташування.
Натисніть кнопку OK, і Excel додасть пусту зведену таблицю з областю Список полів, що відображається праворуч.

Потім створіть зведену таблицю або створіть зведену діаграму. Якщо зв'язки між таблицями вже створено, можна використати будь-яке з їхніх полів у зведеній таблиці. Зв'язки вже створено в зразку книги моделі даних студентів.

Додавання наявних непов'язаних даних до моделі даних

Припустімо, ви імпортували або скопіювали багато даних, які потрібно використовувати в моделі, але не додали їх до моделі даних. Додати нові дані до моделі простіше, ніж вам здається.

Спочатку виберіть будь-яку клітинку в даних, які потрібно додати до моделі. Це може бути будь-який діапазон даних, але найкраще використовувати дані, відформатовані як таблицю Excel .
Щоб додати дані, скористайтеся одним із наведених нижче способів.
Натисніть кнопку Додати надбудову Power Pivot>до моделі даних.
Натисніть кнопку Вставити>зведену таблицю, а потім у діалоговому вікні Створення зведеної таблиці встановіть прапорець Додати ці дані до моделі даних .

Діапазон або таблиця тепер додаються до моделі як зв'язана таблиця. Докладні відомості про роботу зі зв'язаними таблицями в моделі див. в статті Додавання даних за допомогою зв'язаних таблиць Excel у надбудові Power Pivot.

Додавання даних до таблиці Power Pivot

У надбудові Power Pivot рядок не можна додати до таблиці, просто ввівши його в новому рядку, як на аркуші Excel. Але ви можете додати рядки, скопіювавши та вставивши або оновивши вихідні дані та оновивши модель Power Pivot.