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

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

Excel для Microsoft 365 Excel 2024 Excel 2021

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

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

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

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

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

Де знайти функцію "Отримати & перетворення" (Power Query)?

Excel для Microsoft 365 - Get & Transform (Power Query) інтегровано з Excel на вкладці "Дані".

Початок

По-перше, вам потрібно отримати деякі дані.

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

Примітка.

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

Порада.

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

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

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

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

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

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

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

У надбудові Power Pivot перейдіть до розділу "Керування".
On the Home tab, select PivotTable.
Виберіть місце для зведеної таблиці: новий аркуш або поточне розташування.
Натисніть кнопку "OK", і Excel додасть пусту зведену таблицю з областю "Список полів" праворуч.

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

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

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

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

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

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

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