Чи доводилося вам використовувати функцію VLOOKUP, щоб переносити стовпець з однієї таблиці до іншої? Тепер, коли в програмі Excel вбудовано модель даних, функція VLOOKUP застаріла. Зв’язок між двома таблицями даних можна створити на основі зіставлення даних у кожній із них. Після цього можна створити аркуші Power View й побудувати зведені таблиці та інші звіти, у яких використовуються поля з кожної таблиці, навіть якщо ці таблиці отримано з різних джерел. Наприклад, за наявності даних про збут за клієнтами можна імпортувати та зв’язати дані часового аналізу, щоб проаналізувати тенденції продажів за рік або за місяць.
Усі таблиці книги перелічено у списках полів зведеної таблиці та надбудови Power View.
Якщо імпортувати зв’язані таблиці з реляційної бази даних, 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. Пошук стовпців для використання у створенні шляху з однієї таблиці в іншу
Визначивши таблицю, яку роз’єднано з рештою моделі, прогляньте стовпці цієї таблиці, щоб визначити, чи інший стовпець десь у моделі містить відповідні значення.
Наприклад, припустімо, що є модель, яка містить дані про продажі продукту за територіями та що ви згодом імпортували демографічні дані, щоб з’ясувати, чи існує взаємозв’язок між продажами і демографічними тенденціями на кожній території. Оскільки демографічні дані взято з іншого джерела даних, таблиці з ними спочатку ізольовано від решти моделі. Щоб інтегрувати демографічні дані з рештою моделі, необхідно знайти стовпець в одній із демографічних таблиць, який відповідає стовпцю, що вже використовується. Наприклад, якщо демографічні дані впорядковано за регіоном і дані про продажі вказують на те, у якому регіоні відбувалися продажі, можна створити два набори даних, знайшовши спільний стовпець, наприклад "Країна", "Поштовий індекс" або "Регіон", щоб забезпечити підстановку.
Окрім зведених значень ще є кілька додаткових вимог для створення зв’язку.
-
Значення даних у стовпці підстановки мають бути унікальні. Іншими словами, стовпець не має містити повторів. У моделі даних нульові значення та пусті рядки рівноцінні пустим значенням, які є окремим значенням даних. Це означає, що стовпець підстановки не може місити кілька нулів.
-
Типи даних як вихідного стовпця, так і стовпця підстановки мають бути сумісними. Докладні відомості про типи даних див. в статті Типи даних у моделях даних.
Докладні відомості про зв’язки між таблицями див. у статті Зв’язки між таблицями в моделі даних.
