Чи доводилося вам використовувати функцію VLOOKUP, щоб переносити стовпець з однієї таблиці до іншої? Тепер, коли в програмі Excel вбудовано модель даних, функція VLOOKUP застаріла. Зв’язок між двома таблицями даних можна створити на основі зіставлення даних у кожній із них. Після цього можна створити аркуші Power View й побудувати зведені таблиці та інші звіти, у яких використовуються поля з кожної таблиці, навіть якщо ці таблиці отримано з різних джерел. Наприклад, за наявності даних про збут за клієнтами можна імпортувати та зв’язати дані часового аналізу, щоб проаналізувати тенденції продажів за рік або за місяць.
Усі таблиці книги перелічено у списках полів зведеної таблиці та надбудови Power View.
Примітка.: Перш ніж виконати кроки, описані в цій статті, переконайтеся, що цю функцію ввімкнуто. Докладні відомості див. в статті Запуск надбудови Power Pivot для Excel.
Під час імпорту пов'язаних таблиць із реляційної бази даних програма Excel часто може створювати ці зв'язки в моделі даних, яка створюється у фоновому режимі. Для всіх інших випадків зв'язки потрібно створювати вручну.
-
Переконайтеся, що у книзі міститься принаймні дві таблиці, а в кожній таблиці є стовпець, який можна зіставити зі стовпцем в іншій таблиці.
-
Виконайте одну з таких дій: Відформатуйте дані як таблицю або Імпорт зовнішніх даних як таблиці на новому аркуші.
-
Надайте кожній таблиці зрозумілу назву. На вкладці Робота з таблицями > Конструктор введіть назву таблиці в поле Ім’я таблиці.
-
Переконайтеся, що стовпець в одній із таблиць містить унікальні значення даних без повторень. Excel може створити зв’язок, лише якщо один стовпець містить унікальні значення.
Наприклад, щоб зв’язати збут за клієнтами з часовим аналізом, обидві таблиці мають містити дати в однаковому форматі (наприклад, 01.01.2012) та хоча б одна з таблиць (часовий аналіз) має містити кожну дату лише один раз у стовпці.
-
Послідовно виберіть елементи Дані > Зв’язки.
Якщо кнопка Зв’язки неактивна, то книга містить тільки одну таблицю.
-
У діалоговому вікні Керування зв’язками натисніть кнопку Створити.
-
У діалоговому вікні Створити зв’язок натисніть стрілку, щоб відкрити список Таблиця, і виберіть потрібну таблицю. Якщо вибрано зв’язок "один-до-багатьох", ця таблиця має бути на стороні "багатьох". У прикладі з даними про збут за клієнтами та часовим аналізом необхідно було б спочатку вибрати таблицю з клієнтами, адже в будь-який окремий день могло відбутися кілька операцій з продажу.
-
В області Стовпець (зовнішній) виділіть стовпець, який містить дані, пов’язані зі стовпцем Пов’язаний стовпець (основний). Наприклад, якби в обох таблицях був стовпець дат, то можна було б вибрати цей стовпець.
-
В області Пов’язана таблиця виберіть таблицю, в якій є щонайменше один стовпець з даними, пов’язаними з вибраною таблицею в області Таблиця.
-
В області Пов’язаний стовпець (основний) виберіть стовпець з унікальними значеннями, що відповідають значенням у стовпці, який ви вибрали в області Стовпець.
-
Натисніть кнопку OK.
Докладні відомості про зв’язки між таблицями в Excel
Примітки щодо зв’язків
-
Ви дізнаєтеся, чи існує зв'язок, коли ви перетягуєте поля з різних таблиць до списку полів зведеної таблиці. Якщо запит на створення зв'язку не з'являється, у програмі Excel уже є інформація про зв'язок, необхідна для зв'язування даних.
-
Створення зв’язків схоже на використання функції VLOOKUP: стовпці мають містити зіставлені дані, щоб програма Excel змогла додати перехресні посилання між рядками в одній таблиці на такі самі рядки інших таблиць. У прикладі про часовий аналіз таблиця Customer повинна містити значення даних, які також є в таблиці часового аналізу.
-
У моделі даних зв'язки між таблицями можуть бути "один-до-одного" (кожен пасажир має один посадковий талон) або "один-до-багатьох" (кожен рейс має багато пасажирів), але не багато-до-багатьох. Зв'язки "багато-до-багатьох" призводять до помилок циклічних залежностей, наприклад "Виявлено циклічну залежність". Ця помилка виникне, якщо встановити пряме з'єднання між двома таблицями ( зв'язок "багато-до-багатьох" або непрямий зв'язок (ланцюжок зв'язків між таблицями "один-до-багатьох" у кожному зв'язку, але "багато-до-багатьох", коли перегляд закінчується. Дізнайтеся більше про зв'язки між таблицями в моделі даних.
-
Типи даних у двох стовпцях мають бути сумісними. Докладні відомості див. в статті Типи даних у моделях даних Excel .
-
Інші способи створення зв'язків можуть бути більш інтуїтивними, особливо якщо ви не знаєте, які стовпці використовувати. Див . статтю Створення зв'язку в поданні схеми в надбудові Power Pivot.
Приклад. Зв’язування даних часового аналізу з даними про рейси авіакомпанії
Можна отримати додаткові відомості про зв’язки таблиць і часовий аналіз, використовуючи безкоштовні дані з Ринку Microsoft Azure. Деякі набори даних завеликі, і для виконання завантаження в прийнятний термін знадобиться швидкісне підключення до Інтернету.
-
Запустіть надбудову Power Pivot у програмі Microsoft Excel і відкрийте вікно Power Pivot.
-
Послідовно виберіть елементи Отримання зовнішніх даних > Зі служби даних > З Ринку Microsoft Azure. Головна сторінка Ринку Microsoft Azure відобразиться в майстрі імпорту таблиць.
-
У розділі Ціна виберіть елемент Безкоштовно.
-
У розділі Категорія виберіть елемент Наукові та статистичні.
-
Пошук DateStream та натисніть кнопку підписатися.
-
Введіть дані облікового запису Microsoft і натисніть кнопку Вхід. Попередній перегляд даних має відобразитись у вікні.
-
Прокрутіть до кінця сторінки та натисніть кнопку Запит на вибірку.
-
Натисніть кнопку Далі.
-
Виберіть елемент BasicCalendarUS і натисніть кнопку Готово, щоб імпортувати дані. Якщо підключення до Інтернету швидке, для імпорту даних знадобиться близько хвилини. Після завершення буде відображено звіт про стан передачі 73 414 рядків. Натисніть кнопку Закрити.
-
Щоб імпортувати другий набір даних, послідовно виберіть елементи Отримання зовнішніх даних > Зі служби даних > З Ринку Microsoft Azure.
-
У розділі Тип виберіть елемент Дані.
-
У розділі Ціна виберіть елемент Безкоштовно.
-
Знайдіть пункт US Air Carrier Flight Delays і натисніть кнопку Вибрати.
-
Прокрутіть до кінця сторінки та натисніть кнопку Запит на вибірку.
-
Натисніть кнопку Далі.
-
Натисніть кнопку Готово для імпорту даних. Якщо підключення до Інтернету швидке, для імпорту даних знадобиться близько 15 хвилин. Після завершення буде відображено звіт про стан передачі 2 427 284 рядків. Натисніть кнопку Закрити. Тепер у моделі даних міститься дві таблиці. Щоб пов'язати їх, нам знадобляться сумісні стовпці в кожній таблиці.
-
Зверніть увагу, що поле DateKey з таблиці BasicCalendarUS відображається у форматі "1/1/2012 12:00:00 AM". Таблиця On_Time_Performance також містить стовпець дати й часу FlightDate, де значення відображаються в такому самому форматі "1/1/2012 12:00:00 AM". Ці два стовпці містяться зведені дані однакового типу та, хоча б один із них, (DateKey) містить тільки унікальні значення. У наступних кількох кроках ці стовпці буде використовуватися для зв'язування таблиць.
-
У вікні Power Pivot натисніть кнопку Зведена таблиця, щоб створити зведену таблицю на новому або наявному аркуші.
-
У списку полів розгорніть таблицю 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. Пошук стовпців для використання у створенні шляху з однієї таблиці в іншу
Визначивши, яку таблицю відключено від решти моделі, перегляньте її стовпці, щоб визначити, чи містить інший стовпець в іншому місці моделі відповідні значення.
Наприклад, припустімо, що є модель, яка містить дані про продажі продукту за територіями та що ви згодом імпортували демографічні дані, щоб з’ясувати, чи існує взаємозв’язок між продажами і демографічними тенденціями на кожній території. Оскільки демографічні дані взято з іншого джерела даних, таблиці з ними спочатку ізольовано від решти моделі. Щоб інтегрувати демографічні дані з рештою моделі, потрібно знайти стовпець в одній із демографічних таблиць, який відповідає поточній моделі. Наприклад, якщо демографічні дані впорядковано за регіоном і дані про продажі вказують на те, у якому регіоні відбувалися продажі, можна створити два набори даних, знайшовши спільний стовпець, наприклад "Країна", "Поштовий індекс" або "Регіон", щоб забезпечити підстановку.
Окрім зведених значень ще є кілька додаткових вимог для створення зв’язку.
-
Значення даних у стовпці підстановки мають бути унікальні. Іншими словами, стовпець не може містити дублікати. У моделі даних нульові значення та пусті рядки рівноцінні пустим значенням, які є окремим значенням даних. Це означає, що у стовпці підстановки не можна мати кілька null-значень.
-
Типи даних як вихідного стовпця, так і стовпця підстановки мають бути сумісними. Докладні відомості про типи даних див. в статті Типи даних у моделях даних.
Докладні відомості про зв’язки між таблицями див. у статті Зв’язки між таблицями в моделі даних.
