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

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

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

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

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

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

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

Початок

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

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

Примітка.: У цих прикладах ми використовуємо книгу Excel із вигаданими відомостями про учнів для класів і оцінок. Ви можете завантажити зразок книги моделі даних студента та виконати вказівки. Ви також можете завантажити версію з заповненою моделлю даних..
Виберіть одну або кілька таблиць і натисніть кнопку Завантажити.

Якщо потрібно відредагувати вихідні дані, можна вибрати параметр Редагувати . Докладні відомості див. в статті Створення, завантаження або редагування запиту в Програмі Excel (Power Query).

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

Примітки.:

Моделі створюються неявно, коли ви імпортуєте кілька таблиць одночасно в 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.