Перейти до основного
Підтримка
Вхід
Вхід за допомогою облікового запису Microsoft
Увійдіть або створіть обліковий запис.
Вітаємо,
Виберіть інший обліковий запис.
У вас є кілька облікових записів
Виберіть обліковий запис, за допомогою якого потрібно ввійти.
Створення моделі даних у програмі Excel

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

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

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

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

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

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

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

Початок

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

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

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

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

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

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

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

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

Примітки.: 

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

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

  • Модель може містити одну таблицю. Щоб створити модель на основі однієї таблиці, виберіть її та натисніть кнопку Додати до моделі даних у 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 | Номер класу

    Примітки.: 

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

    • Сполучні лінії в поданні схеми мають "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 чи отримати підтримку в спільнотах.

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

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

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

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

Навчальний посібник. Імпорт даних до програми Excel і створення моделі даних

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

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

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

Потрібні додаткові параметри?

Ознайомтеся з перевагами передплати, перегляньте навчальні курси, дізнайтесь, як захистити свій пристрій тощо.

Спільноти допомагають ставити запитання й відповідати на них, надавати відгуки та дізнаватися думки висококваліфікованих експертів.

Чи ця інформація була корисною?

Наскільки ви задоволені якістю мови?
Що вплинуло на ваші враження?
Натиснувши кнопку "Надіслати", ви надасте свій відгук для покращення продуктів і служб Microsoft. Ваш ІТ-адміністратор зможе збирати ці дані. Декларація про конфіденційність.

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

×