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

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

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

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

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

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

  • Excel 2016 & Excel для Office 365 – отримати & Transform (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 для Office 365 використовуйте дані > отримання & перетворення даних > отримання даних для імпорту даних з будь-якої кількості зовнішніх джерел даних, наприклад текстового файлу, книги 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 PivotPower Pivotкерувати. Якщо відображаються дані на аркуші, а потім – модель. Див.: Дізнайтеся, які джерела даних використано в моделі даних книги , щоб дізнатися більше.

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

Наступний крок – створити зв'язки між таблицями, щоб можна було витягувати дані з будь-якого з них. Для кожної таблиці має бути первинний ключ або унікальний ідентифікатор поля, наприклад ІДЕНТИФІКАТОР студента або номер класу. Найпростіший спосіб – перетягнути ці поля, щоб підключити їх до подання схемиPower pivoe's.

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

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

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

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

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

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

    • tbl_Students | ІДЕНТИФІКАТОР студента > tbl_Grades | ІДЕНТИФІКАТОР студента

      Іншими словами, перетягніть поле "ІДЕНТИФІКАТОР студента" з таблиці "учні" до поля "ІДЕНТИФІКАТОР студента" в таблиці "класи".

    • tbl_Semesters | Семестр ID > tbl_Grades | Семестру

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

    Примітки.: 

    • Імена полів не повинні збігатися з тим, щоб створити зв'язок, але вони повинні мати однаковий тип даних.

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

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

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

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

  1. У Power Pivotпослідовно виберіть елементи керування.

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

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

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

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

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

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

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

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

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

  3. Натисніть кнопку Power PivotPower PivotДодати до моделі даних.

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

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

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

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

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

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

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

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

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

Створення моделі даних, що використовується для пам'яті, у програмі Excel і надбудові Power Pivot

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

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

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

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

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

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

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

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

×