Створіть взаємозв’язок між таблицями в Excel

Ви коли-небудь використовували VLOOKUP для перенесення стовпця з однієї таблиці в іншу? Тепер, коли Excel має вбудовану модель даних, VLOOKUP застарів. Ви можете створити взаємозв'язок між двома таблицями даних на основі відповідних даних у кожній таблиці. Потім ви можете створювати аркуші Power View та створювати зведені таблиці та інші звіти з полями з кожної таблиці, навіть коли таблиці надходять з різних джерел. Наприклад, якщо у вас є дані про продажі клієнтів, можливо, ви захочете імпортувати та зв’язати дані аналізу часу, щоб проаналізувати схеми продажів за роками та місяцями.

Усі таблиці робочої книги перелічені у списках зведеної таблиці та полів Power View.

excel

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

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

Виконайте одне з наступного: Відформатуйте дані як таблицю або Імпортуйте зовнішні дані як таблицю на новому аркуші.

Дайте кожній таблиці значущу назву: У таблиці Інструменти, натисніть Дизайн > Назва таблиці > введіть ім'я.

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

Наприклад, щоб пов’язати продажі клієнтів із аналізом часу, обидві таблиці повинні містити дати в одному форматі (наприклад, 1/1/2012), і принаймні одна таблиця (аналіз часу) перераховує кожну дату лише один раз у стовпці.

Клацніть Дані > Відносини.

Якщо відносини сіра, ваша робоча книга містить лише одну таблицю.

В Управління відносинами натисніть Новий.

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

Для стовпця (іноземний), виберіть стовпець, що містить дані, які відносяться до пов'язаного стовпця (основний). Наприклад, якби в обох таблицях був стовпець дати, ви вибрали б цей стовпець зараз.

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

Для пов’язаної колонки (первинна), виберіть стовпець, який має унікальні значення, які відповідають значенням у стовпці, який ви вибрали для стовпця.

Докладніше про взаємозв'язки між таблицями в Excel

Нотатки про стосунки

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

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

У моделі даних взаємозв'язок таблиці може бути "один до одного" (кожен пасажир має один посадковий талон) або "один до багатьох" (кожен рейс має багато пасажирів), але не "багато до багатьох". Відносини багато-до-багатьох призводять до помилок кругової залежності, наприклад "Виявлено кругову залежність". Ця помилка виникне, якщо ви встановите пряме з'єднання між двома таблицями, які багато-до-багатьох, або непрямі зв'язки (ланцюжок зв'язків таблиць, які є одним-до-багатьох у кожному відношенні, але багато-до-багатьох при перегляді закінчуються до кінця. Докладніше про взаємозв'язки між таблицями в моделі даних.

Типи даних у двох стовпцях повинні бути сумісними. Детальніше див. У розділі Типи даних у моделях даних Excel.

Інші способи створення взаємозв’язків можуть бути більш інтуїтивними, особливо якщо ви не впевнені, які стовпці використовувати. Див. Розділ Створення відносин у режимі перегляду діаграм у Power Pivot.

Приклад: Зв'язок даних розвідки часу з даними польотів авіакомпанії

Ви можете дізнатись як про взаємозв'язок таблиць, так і про аналіз часу, використовуючи безкоштовні дані на ринку Microsoft Azure. Деякі з цих наборів даних дуже великі, що вимагає швидкого підключення до Інтернету, щоб завершити завантаження даних за розумний проміжок часу.

Клацніть Отримати зовнішні дані > Від служби передачі даних > З Microsoft Azure Marketplace. Домашня сторінка Microsoft Azure Marketplace відкривається в майстрі імпорту таблиць.

За ціною, натисніть Безкоштовно.

Під категорією, натисніть Наука та статистика.

Знайдіть DateStream і натисніть Підписатися.

Введіть свій обліковий запис Microsoft і натисніть Увійти. У вікні повинен з’явитися попередній перегляд даних.

Прокрутіть униз і натисніть Вибрати запит.

Виберіть BasicCalendarUS а потім натисніть кнопку Готово імпортувати дані. Через швидке підключення до Інтернету імпорт займає близько хвилини. По завершенні ви побачите звіт про стан із 73 414 переданих рядків. Клацніть Закрити.

Клацніть Отримати зовнішні дані > Від служби передачі даних > З Microsoft Azure Marketplace імпортувати другий набір даних.

Під Тип, натисніть Дані.

За ціною, натисніть Безкоштовно.

Знайдіть затримки авіарейсів США і натисніть Вибрати.

Прокрутіть униз і натисніть Вибрати запит.

Клацніть Готово імпортувати дані. Через швидке підключення до Інтернету це може зайняти 15 хвилин, щоб імпортувати. Після завершення ви побачите звіт про стан із 2427284 переданих рядків. Клацніть Закрити. Тепер ви повинні мати дві таблиці в моделі даних. Щоб зв’язати їх, нам знадобляться сумісні стовпці в кожній таблиці.

Зверніть увагу, що DateKey в BasicCalendarUS у форматі 1/1/2012 00:00:00 AM. On_Time_Performance У таблиці також є стовпець дати і часу, FlightDate, значення яких вказані в одному форматі: 1/1/2012 00:00:00 AM. Два стовпці містять відповідні дані одного типу даних і принаймні один із стовпців (DateKey) містить лише унікальні значення. На наступних кількох кроках ви будете використовувати ці стовпці для зв’язку таблиць.

У вікні Power Pivot натисніть PivotTable створити зведену таблицю на новому або існуючому аркуші.

У списку полів розгорніть On_Time_Performance і натисніть ArrDelayMinutes додати його в область Значення. У зведеній таблиці ви повинні побачити загальну кількість часу затримки рейсів, виміряну хвилинами.

Розгорніть BasicCalendarUS і натисніть MonthInCalendar щоб додати його до області Рядки.

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

У списку полів у розділі «Можуть знадобитися зв’язки між таблицями» натисніть Створити.

У відповідній таблиці виберіть On_Time_Performance а у відповідній колонці (Основна) виберіть FlightDate.

У таблиці виберіть BasicCalendarUS а в стовпці (Іноземний) виберіть DateKey. Клацніть OK щоб створити стосунки.

Зверніть увагу, що сума затримок хвилин зараз змінюється для кожного місяця.

У BasicCalendarUS і перетягніть YearKey до області Рядки, над MonthInCalendar.

Тепер ви можете зрізати затримки прибуття за роками та місяцями або за іншими значеннями в календарі.

Поради: За замовчуванням місяці вказані в алфавітному порядку. За допомогою надбудови Power Pivot ви можете змінити сортування так, щоб місяці відображались у хронологічному порядку.

Переконайтеся, що BasicCalendarUS таблиця відкрита у вікні Power Pivot.

На домашній таблиці натисніть Сортувати за стовпцем.

У Сортування виберіть MonthInCalendar

У пункті «Оберіть» виберіть «MonthOfYear».

Зведена таблиця тепер сортує кожну комбінацію місяць-рік (жовтень 2011 р., Листопад 2011 р.) За номером місяця протягом року (10, 11). Змінити порядок сортування легко, оскільки DateStream канал містить усі необхідні стовпці, щоб цей сценарій спрацював. Якщо ви використовуєте іншу таблицю даних часу, ваш крок буде іншим.

"Можуть знадобитися взаємозв'язки між таблицями"

Додаючи поля до зведеної таблиці, ви будете проінформовані про необхідність відношення таблиці для розуміння полів, вибраних у зведеній таблиці.

Незважаючи на те, що Excel може сказати вам, коли потрібні відносини, він не може сказати, які таблиці та стовпці використовувати, чи можливо взаємини таблиці. Спробуйте виконати ці дії, щоб отримати відповіді, які вам потрібні.

Крок 1: Визначте, які таблиці вказати у зв’язку

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

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

Крок 2: Знайдіть стовпці, за якими можна створити шлях від однієї таблиці до наступної

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

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

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

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

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

Щоб дізнатись більше про взаємозв'язки таблиць, див. Зв'язок між таблицями в моделі даних.