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

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

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

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

Де знайти надбудову "& Перетворити" (Power Query)?

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

  • Excel 2013 р.: Power Query – це надбудова, яка входить до складу Excel, але її потрібно активувати. Виберіть Файл > Параметри > Надбудови ,а потім у розкривному списку Керування в нижній частині області виберіть надбудови COM > Go. Перевірте, чи є в надбудові Microsoft Power Query Excel, а потім натисніть кнопку OK, щоб активувати її. До стрічки додасться вкладка Power Query.

  • Excel 2010: завантаження та інсталяція надбудови Power Query.. Після активації на стрічку додасться вкладка Power Query.

Початок

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

  1. У програмах Excel 2016 і Excel для Microsoft 365 скористайтеся програмою Data > Get & Transform Data > Get Data (Отримати дані) для імпорту даних із будь-якої кількості зовнішніх джерел даних, наприклад текстового файлу, книги Excel, веб-сайту, Microsoft Access, SQL Server або іншої реляційної бази даних, яка містить кілька пов'язаних таблиць.

    У Excel 2013 і 2010 перейдіть до надбудови Power Query > Отриматизовнішні дані та виберіть джерело даних.

  2. Excel буде запропоновано вибрати таблицю. Якщо потрібно отримати кілька таблиць з одного джерела даних, установіть прапорець Дозволити вибір кількох таблиць. Якщо вибрати кілька таблиць, Excel автоматично створює модель даних.

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

    Отримання & перетворення (Power Query) Навігатор
  3. Виберіть одну або кілька таблиць, а потім натисніть кнопку Завантажити.

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

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

Примітки.: 

  • Моделі створюються неявно під час імпорту кількох таблиць одночасно в Excel.

  • Моделі створюються явно під час імпорту даних за допомогою Power Pivot надбудови. У надбудові модель представлено в табличному макеті, схожому на Excel, де кожна вкладка містить табличні дані. Щоб навчитися основам імпортуданих, використовуючи надбудову Power Pivot, див. SQL Server даних.

  • Модель може містити одну таблицю. Щоб створити модель лише на основі однієї таблиці, виберіть таблицю та натисніть кнопку Додати до моделі даних у Power Pivot. Це можна зробити, якщо потрібно використовувати такі Power Pivot даних, як відфільтровані набіри даних, обчислювані стовпці, обчислювані поля, показники KPI та ієрархії.

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

  • Поради зі зменшення розміру моделі даних див. в статті Створення моделі даних з ефективним використанням пам'яті за допомогою Excel і Power Pivot.

  • Докладні відомості див. в навчальній вправі:імпорт даних Excel та Створення моделі даних.

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

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

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

  1. Перейдіть у надбудову Power Pivot > Керування.

  2. На вкладці Основне натисніть кнопку Подання схеми.

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

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

    Подання схеми зв'язків моделі даних Power Query

    Ми створили такі посилання:

    • tbl_Students | Ідентифікатор студента > tbl_Grades | Ідентифікатор студента

      Іншими словами, перетягніть поле "Ідентифікатор студента" з таблиці "Учні" до поля "Ідентифікатор студента" в таблиці "Оцінки".

    • tbl_Semesters | Ідентифікатор семестру > tbl_Grades | Семестр

    • tbl_Classes | Номер класу > tbl_Grades | Class Number

    Примітки.: 

    • Щоб створити зв'язок, не потрібно вказувати однакові імена полів, але вони мають бути однаковими типами даних.

    • З одного боку сполучні панелі подання схеми мають 1, а з іншого – "*". Це означає, що між таблицями існує зв'язок "один-до-багатьох", який визначає, як використовуються дані у зведених таблицях. Докладні відомості див. в статтях: Зв'язки між таблицями в моделі даних.

    • Сполучі вказують лише на те, що між таблицями існує зв'язок. Вони насправді не показують, які поля зв'язано між собою. Щоб переглянути посилання, перейдіть на вкладку Power Pivot>Керування > макетом > зв'язків > ними. У Excel відкрийте меню Зв'язки > даних.

Використання моделі даних для створення зведеної таблиці або зведена діаграма

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

  1. У Power Pivotперейдіть до пункту Керування.

  2. На вкладці Основне натисніть кнопку Зведена таблиця.

  3. Виберіть розташування зведеної таблиці: новий аркуш або поточне розташування.

  4. Натисніть кнопку OK,Excel додати пусту зведену таблицю з областю Список полів праворуч.

    Список полів зведеної таблиці Power Pivot

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

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

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

  1. Почніть із виділення будь-якої клітинки в даних, які потрібно додати до моделі. Це може бути будь-який діапазон даних, але найкраще форматувати дані Excel таблицю.

  2. Щоб додати дані, скористайтеся одним із наведених нижче способів.

  3. Натисніть Power Pivot > Додати до моделі даних.

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

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

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

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

Потрібна додаткова довідка?

Ви завжди можете поставити запитання експерту в спільноті Tech у розділі Excel чи отримати підтримку в спільноті Answers.

Додаткові відомості

Отримання & та навчальних посібників із Power Pivot

Загальні відомості про редактор запитів (Power Query)

Створення моделі даних з ефективним використанням пам'яті за допомогою Excel і Power Pivot

Навчальна вправа: імпорт Excel створення моделі даних і створення моделі даних

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

Зв'язки між таблицями в моделі даних

Потрібна додаткова довідка?

Отримуйте нові функції раніше за інших
Приєднатися до Microsoft оцінювачів

Чи були ці відомості корисні?

Наскільки ви задоволені якістю перекладу?
Що вплинуло на ваші враження?

Дякуємо за ваш відгук!

×