Перейти до основного
Підтримка
Вхід
Створення моделі даних у програмі Excel

Створення моделі даних у програмі 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 > 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, де кожна вкладка містить табличні дані. Щоб дізнатися про основи імпортуданих за допомогою бази даних 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. Виберіть команду > зведенутаблицю та встановіть прапорець Додати дані до моделі даних у діалоговому вікні Створення зведеної таблиці.

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

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

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

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

Ви завжди можете поставити запитання експерту в спільноті Tech (у розділі Excel), отримати підтримку в спільноті, що допомагає знайти відповіді на запитання, або запропонувати нову функцію чи вдосконалення на форумі Excel User Voice.

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

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

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

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

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

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

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

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

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

Ця інформація корисна?

Дякуємо, що знайшли час і надіслали нам відгук! Можливо, у нас не буде часу відповісти на кожен коментар, але докладемо максимум зусиль, щоб переглянути їх усі. Вас цікавить, як ми використовуємо ваші відгуки?

×