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

Створюючи зв'язки, можна створити різні таблиці, щоб зробити аналіз даних потужнішим. Зв'язок – це підключення між двома таблицями, які містять дані: один стовпець у кожній таблиці – це основа для зв'язку. Щоб зрозуміти користь від зв’язків, уявіть, що вам необхідно відстежувати замовлення клієнтів на вашому підприємстві. Усі дані в одній таблиці можна відстежувати так:

Ідентифікатор клієнта

Name

EMail

DiscountRate

OrderID

OrderDate

Product

Quantity

1

Артюхін

artem.artyuhin@contoso.com

0,05

256

01.07.2010

Компактна цифрова фотокамера

11

1

Артюхін

artem.artyuhin@contoso.com

0,05

255

01.03.2010

Дзеркальна фотокамера

15

2

Ярощук

mykola.yaroshchuk@contoso.com

0,10

254

01.03.2010

Бюджетна відеокамера

27

Цей підхід можливий, однак він призводить до збереження надлишкових даних, наприклад адреси електронної пошти для кожного замовлення. Це дуже просто, однак якщо адреса електронної пошти клієнта зміниться, її необхідно буде оновити в кожному рядку для цього клієнта. Одне із можливих рішень цієї проблеми – розподіл даних між кількома таблицями та визначення зв’язків між цими таблицями. Такий підхід використовується в реляційних базах даних, таких як SQL Server. Наприклад, імпортована база даних може зберігати дані замовлень у трьох пов’язаних таблицях:

Customers

[CustomerID]

Name

Email

1

Артюхін

artem.artyuhin@contoso.com

2

Ярощук

mykola.yaroshchuk@contoso.com

CustomerDiscounts

[CustomerID]

DiscountRate

1

0,05

2

0,10

Orders

[CustomerID]

OrderID

OrderDate

Product

Quantity

1

256

01.07.2010

Компактна цифрова фотокамера

11

1

255

01.03.2010

Дзеркальна фотокамера

15

2

254

01.03.2010

Бюджетна відеокамера

27

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

Якщо ви імпортуєте таблиці з тієї самої бази даних за допомогою надбудови Power Pivot, Power Pivot може визначати зв'язки між таблицями на основі стовпців у [дужках], а також відтворити ці зв'язки в моделі даних, побудованій за лаштунками. Докладні відомості див. в статті Автоматичне виявлення та присутність зв'язків у цій статті. Якщо імпортувати таблиці з кількох джерел, зв'язки можна створювати вручну, як описано в статті Створення зв'язку між двома таблицями.

Зв’язки засновані на стовпцях у кожній таблиці, які містять однакові дані. Наприклад, таблицю "Клієнти" можна зв'яжити з таблицею "Замовлення", якщо кожен містить стовпець, у якому зберігається ідентифікатор клієнта. У наведеному прикладі назви стовпців однакові, однак це необов’язкова умова. Один стовпець може називатися CustomerID, а інший – CustomerNumber, однак у всіх рядках таблиці Orders має зберігатися ідентифікатор, який також зберігається в таблиці Customers.

У реляційних базах даних є кілька типів ключів. Ключ – це зазвичай стовпець зі спеціальними властивостями. Розуміння призначення кожного ключа допоможе керувати моделлю даних, що складається з кількох таблиць, яка надає дані до зведеної таблиці, зведеної діаграмі або звіту Power View.

Хоча є багато типів ключів, вони найважливіші для нашої мети:

  • Первинний ключ – унікальний ідентифікатор рядка в таблиці (наприклад, стовпець CustomerID в таблиці Customers).

  • Альтернативний ключ (або первинний ключ) – унікальний стовпець, відмінний від первинного ключа. Наприклад, у таблиці Employees можуть зберігатися ідентифікатор працівника та код соціального страхування, обидва унікальні.

  • Зовнішній ключ – стовпець, який посилається на унікальний стовпець в іншій таблиці, наприклад CustomerID в таблиці Orders, який посилається на стовпець CustomerID в таблиці Customers.

У моделі даних на первинний ключ або на альтернативний ключ посилаються як на пов’язаний стовпець. Якщо таблиця містить як первинний ключ, так і альтернативний, будь-який з них можна використовувати як основу для зв’язку між таблицями. Зовнішній ключ називають стовпцем-джерелом або просто стовпцем. У нашому прикладі зв'язок визначено між ідентифікатором клієнта в таблиці "Замовлення" (стовпець) і "Ідентифікатор клієнта" в таблиці "Клієнти" (стовпець підстановки). Якщо дані імпортуються з реляційної бази даних, за замовчуванням програма Excel вибирає зовнішній ключ з однієї таблиці та відповідний первинний ключ із іншої. Однак замість стовпця підстановки можна використовувати будь-який стовпець, який містить унікальні значення.

Зв'язок між клієнтом і замовленням – це зв'язок "один-до-багатьох". У кожного клієнта може бути кілька замовлень, але для одного замовлення є лише один клієнт. Інший важливий зв'язок між таблицями – "один-до-одного". У нашому прикладі таблиця CustomerDiscounts, яка визначає єдину дисконтну ставку для кожного клієнта, має зв'язок "один-до-одного" з таблицею "Клієнти".

У цій таблиці показано зв'язки між трьома таблицями(Customers, CustomerDiscounts іOrders):

Зв’язок

Тип

Стовпець підстановки

Стовпець

Customers-CustomerDiscounts

один-до-одного

Customers.CustomerID

CustomerDiscounts.CustomerID

Customers-Orders

один-до-багатьох

Customers.CustomerID

Orders.CustomerID

Примітка.: Зв’язки "багато-до-багатьох" не підтримуються в моделі даних. Прикладом зв’язку "багато-до-багатьох" може бути прямий зв’язок між таблицями Products і Customers, у якому один клієнт може придбати кілька продуктів, а один продукт можуть придбати кілька клієнтів.

Коли зв'язок створено, Excel зазвичай потрібно переобчисити всі формули, у яких використовуються стовпці з таблиць у новостворених зв'язках. Обробка може тривати певний час залежно від обсягу даних і складності зв'язків. Докладні відомості див. в області Повторне обчислення формул.

Модель даних може містити кілька зв’язків між двома таблицями. Щоб побудувати точні обчислення, Excel шлях з однієї таблиці до наступної. Таким чином, у певний момент часу лише один зв’язок між двома таблицями буде активним. Хоча інші неактивні, у формулах і запитах можна вказати неактивний зв'язок.

У поданні схеми активний зв'язок – це суцільна лінія, а неактивні – пунктирні лінії. Наприклад, у adventureWorksDW2012 таблиця DimDate містить стовпець DateKey,пов'язаний із трьома різними стовпцями в таблиці FactInternetSales:OrderDate, DueDateі ShipDate. Якщо активний зв’язок міститься між DateKey та OrderDate, він буде зв’язком за замовчуванням у формулах, якщо ви не зазначите інше.

Зв’язок можна створити, коли дотримано такі вимоги:

Умови

Опис

Унікальний ідентифікатор для кожної таблиці

У кожній таблиці повинен бути один стовпець, який унікально визначатиме кожний рядок у цій таблиці. Цей стовпець часто називають первинним ключем.

Унікальні стовпці підстановки

Значення даних у стовпці підстановки мають бути унікальними. Іншими словами, у таблиці не повинно бути повторень. У моделі даних нульові значення та пусті рядки рівноцінні пустим значенням, які є окремим значенням даних. Це означає, що у стовпці підстановки не може бути кілька нульових значень.

Пов’язані типи даних

Типи даних у вихідному стовпці та стовпці підстановки мають бути сумісними. Докладні відомості про типи даних див. в ційпрограмі.

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

Складені ключі та стовпці підстановки

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

Щоб створити зв’язок між двома таблицями, у яких кілька стовпців визначатимуть первинний і зовнішній ключі, об’єднайте значення та створіть один стовпець для ключа, перш ніж створювати зв’язок. Це можна зробити, перш ніж імпортувати дані, або створивши обчислюваний стовпець у моделі даних за допомогою надбудови Power Pivot.

Зв’язки "багато-до-багатьох"

Зв’язки "багато-до-багатьох" не можна використовувати в моделі даних. Не можна просто додати з’єднувальні таблиці до моделі. Однак для моделювання зв’язків "багато-до-багатьох" можна використовувати функції DAX.

Рефлексивні з’єднання та цикли

Рефлексивні з’єднання та цикли не можна використовувати в моделі даних. Рефлексивне з’єднання – це рекурсивний зв’язок таблиці із самою собою. Рефлексивні з’єднання часто використовуються для визначення батьківсько-дочірніх ієрархій. Наприклад, можна об’єднати таблицю Employees із нею самою, щоб отримати ієрархію для відображення ланцюжка керівництва на підприємстві.

Програма Excel не дозволяє створення циклів між зв’язками в книзі. Іншими словами, наведений далі набір зв’язків заборонений.

Від Таблиці 1, стовпець a   до   Таблиці 2, стовпець f

Від Таблиці 2, стовпець f   до   Таблиці 3, стовпець n

Від Таблиці 3, стовпець n   до   Таблиці 1, стовпець a

Якщо ви спробуєте створити зв’язок, який призведе до створення циклу, буде видано помилку.

Одна з переваг використання Power Pivot полягає в тому, що надбудова Power Pivot може іноді виявляти наявні зв’язки й установлювати нові в моделі даних, створеній у програмі Excel.

Якщо імпортувати кілька таблиць, надбудова Power Pivot автоматично виявляє наявні зв’язки між таблицями. Крім того, якщо створити зведену таблицю, надбудова Power Pivot аналізує дані в таблицях. У ній виявляються можливі, але ще не визначені зв’язки та пропонуються відповідні стовпці для цих зв’язків.

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

  • Типи даних у всіх пов'язаних стовпцях мають бути сумісними. Для автоматичного виявлення підтримуються лише типи даних "Ціле число" та "Текст". Докладні відомості про типи даних див. в цій програмі.

  • Для успішного виявлення зв’язку кількість унікальних ключів у стовпці підстановки має бути більшою за значення в таблиці на стороні "багато". Іншими словами, ключовий стовпець на стороні "багато" у зв’язку не може містити значення, відсутні у ключовому стовпці таблиці підстановки. Наприклад, у вас є таблиця, у якій наведено список продуктів з ідентифікаторами (таблиця підстановки), і таблиця продажів, яка містить список продажів кожного продукту (сторона зв’язку "багато"). Якщо в одному із записів продажів міститься ідентифікатор продукту, відсутній у таблиці продуктів, зв’язок не буде створено автоматично, однак ви зможете створити його вручну. Щоб програма Excel виявила зв’язок, спочатку необхідно оновити таблицю підстановки Product відсутніми ідентифікаторами продуктів.

  • Переконайтеся, що назва ключового стовпця на множинній стороні відповідає назві ключового стовпця в таблиці підстановки. Назви навіть не мають повністю збігатися. Наприклад, у бізнес-параметрах назви стовпців часто містять однакові дані: Emp ID, EmployeeID, Employee ID, EMP_IDтощо. Алгоритм виявляє схожі назви та призначає вищу ймовірність тим стовпцям, у яких назви схожі або однакові. Таким чином, для підвищення ймовірності створення зв’язку можна перейменувати назви стовпців у даних, які ви імпортуєте, на схожі назви стовпців у наявних таблицях. Якщо програма Excel знаходить кілька можливих зв’язків, то жодний зв’язок створено не буде.

Ці відомості можуть допомогти вам зрозуміти, чому не всі зв'язки виявлено або як зміни в метаданих – наприклад, ім'я поля та типи даних – можуть покращити результати автоматичного виявлення зв'язків. Докладні відомості див. в указівках із виправлення неполадок зв'язків.

Автоматичне виявлення іменованих наборів

У зведеній таблиці зв’язки між іменованими наборами та пов’язаними полями не виявляються автоматично. Їх можна створювати вручну. Якщо потрібно використовувати автоматичне виявлення зв’язків, видаліть кожний іменований набір і додайте окремі поля з іменованого набору безпосередньо до зведеної таблиці.

Припущення зв’язків

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

Products і Category – створюється вручну

Category та SubCategory – створюється вручну

Products і SubCategory – зв’язок припускається

Щоб зв’язки утворювалися автоматично, такі зв’язки мають бути однаково направленими, як зазначено вище. Якщо початкові зв’язки були, наприклад, між таблицями Sales і Products, а також між таблицями Sales і Customers, зв’язок не припускається. Це відбувається тому, що зв’язок між таблицями Products і Customers – "багато-до-багатьох".

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

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

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

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

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

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

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

×