Чи доводилося вам використовувати функцію VLOOKUP для перенесення стовпця з однієї таблиці до іншої? Програма Excel також містить вбудовану модель даних, яка дає змогу створювати зв'язки між таблицями, які можуть стати альтернативою використанню функцій підстановки, як-от VLOOKUP. Зв’язок між двома таблицями даних можна створити на основі зіставлення даних у кожній із них. Потім можна створювати зведені таблиці та інші звіти з полями з кожної таблиці, навіть якщо таблиці – з різних джерел. Наприклад, за наявності даних про збут за клієнтами можна імпортувати та зв’язати дані часового аналізу, щоб проаналізувати тенденції продажів за рік або за місяць.
Усі таблиці в книзі перелічено в списку Поля зведеної таблиці.
Зв'язки найчастіше використовуються під час створення зведених таблиць із кількох таблиць у моделі даних. Це дає змогу аналізувати пов'язані дані, не об'єднуючи їх в одну таблицю.
Примітка.
Якщо книга містить модель даних, можна керувати зв'язками таблиць на вкладці Дані.
Під час імпорту пов'язаних таблиць із реляційної бази даних програма Excel часто може створювати ці зв'язки в моделі даних, яка створюється у фоновому режимі. Для всіх інших випадків зв'язки потрібно створювати вручну.
- Переконайтеся, що у книзі міститься принаймні дві таблиці, а в кожній таблиці є стовпець, який можна зіставити зі стовпцем в іншій таблиці.
- Виконайте одну з таких дій: Відформатуйте дані як таблицю або Імпорт зовнішніх даних як таблиці на новому аркуші.
- Дайте кожній таблиці зрозуміле ім'я. У контекстному меню Робота з таблицямивиберіть пункт>Ім'я> конструктора таблиці введіть ім'я.
- Переконайтеся, що стовпець в одній із таблиць містить унікальні значення даних без повторень. Excel може створити зв’язок, лише якщо один стовпець містить унікальні значення.
Наприклад, щоб пов'язати збут клієнтів із часового аналізу, обидві таблиці мають містити дати в одному форматі (наприклад, 01.01.2026), а принаймні одна таблиця (часовий аналіз) – лише один раз у стовпці. - ВиберітьЗв'язкиданих>.
Якщо кнопка Зв’язки неактивна, то книга містить тільки одну таблицю.
- У полі Керування зв’язками натисніть кнопку Створити.
- У діалоговому вікні Створити зв’язок натисніть стрілку, щоб відкрити список Таблиця, і виберіть потрібну таблицю. Якщо вибрано зв’язок "один-до-багатьох", ця таблиця має бути на стороні "багатьох". У прикладі з даними про збут за клієнтами та часовим аналізом необхідно було б спочатку вибрати таблицю з клієнтами, адже в будь-який окремий день могло відбутися кілька операцій з продажу.
- В області Стовпець (зовнішній) виділіть стовпець, який містить дані, пов’язані зі стовпцем Пов’язаний стовпець (основний). Наприклад, якби в обох таблицях був стовпець дат, то можна було б вибрати цей стовпець.
- В області Пов’язана таблиця виберіть таблицю, в якій є щонайменше один стовпець з даними, пов’язаними з вибраною таблицею в області Таблиця.
- В області Пов’язаний стовпець (основний) виберіть стовпець з унікальними значеннями, що відповідають значенням у стовпці, який ви вибрали в області Стовпець.
- Натисніть кнопку OK.
Докладні відомості про зв’язки між таблицями в Excel
Примітки щодо зв’язків
Ви дізнаєтеся, чи існує зв'язок, коли ви перетягуєте поля з різних таблиць до списку полів зведеної таблиці. Якщо запит на створення зв'язку не з'являється, у програмі Excel уже є інформація про зв'язок, необхідна для зв'язування даних.
Створення зв’язків схоже на використання функції VLOOKUP: стовпці мають містити зіставлені дані, щоб програма Excel змогла додати перехресні посилання між рядками в одній таблиці на такі самі рядки інших таблиць. У прикладі про часовий аналіз таблиця Customer повинна містити значення даних, які також є в таблиці часового аналізу.
- У моделі даних Excel зв'язки зазвичай використовуються "один-до-одного" або "один-до-багатьох". Для зв'язків "багато-до-багатьох" потрібне додаткове моделювання (наприклад, за допомогою таблиці підстановки). Зв'язки "багато-до-багатьох" призводять до помилок циклічних залежностей, наприклад "Виявлено циклічну залежність". Ця помилка виникне, якщо встановити пряме з'єднання між двома таблицями ( зв'язок "багато-до-багатьох" або непрямий зв'язок (ланцюжок зв'язків між таблицями "один-до-багатьох" у кожному зв'язку, але "багато-до-багатьох", коли перегляд закінчується). Дізнайтеся більше про зв'язки між таблицями в моделі даних.
На відміну від формул підстановки зв'язки не дублюють дані. Натомість вони зв'язують таблиці, щоб поля з кожної таблиці можна було використовувати разом у зведеній таблиці.
Типи даних у двох стовпцях мають бути сумісними. Докладні відомості див. в статті Типи даних у моделях даних Excel .
Інші способи створення зв'язків можуть бути більш інтуїтивними, особливо якщо ви не знаєте, які стовпці використовувати. Див . статтю Створення зв'язку в поданні схеми в надбудові Power Pivot.
"Можливо, знадобляться зв'язки між таблицями"
Коли ви додаєте поля до зведеної таблиці, вам буде повідомлено, чи потрібен зв'язок між таблицями, щоб зрозуміти поля, вибрані у зведеній таблиці.
Хоча програма Excel може повідомити, коли потрібен зв'язок, вона не може визначити, які таблиці та стовпці слід використовувати або чи можна навіть створити зв'язок між таблицями. Щоб отримати потрібні відповіді, виконайте наведені нижче кроки.
Крок 1. Визначення таблиць, які необхідно вказати у зв’язку
Якщо модель містить лише кілька таблиць, дуже легко визначити, які з них необхідно використати. Проте для більших моделей, можливо, потрібна допомога. Один зі способів – використання подання схеми в надбудові Power Pivot. Подання схеми – це візуальне відображення всіх таблиць у моделі даних. Використовуючи подання схеми, можна швидко визначити, які таблиці відокремлено від решти моделі.
Примітка.
У зведеній таблиці можна створити неоднозначні зв'язки, неприпустимі. Припустімо, що всі таблиці якимось чином пов'язані з іншими таблицями в моделі, але під час спроби об'єднати поля з різних таблиць з'являється повідомлення "Можливо, знадобляться зв'язки між таблицями". Найбільш імовірною причиною є те, що ви зіткнулися зі зв'язком "багато-до-багатьох". Якщо відстежити ланцюжок зв’язків таблиць, які з’єднані з таблицями, що необхідно використовувати, можливо виявиться, що є два або кілька зв’язків "один до багатьох" між цими таблицями. Не існує загального вирішення для всіх ситуацій, але можна спробувати створити обчислювані стовпці для об’єднання необхідних стовпців в одну таблицю.
Крок 2. Пошук стовпців для використання у створенні шляху з однієї таблиці в іншу
Визначивши, яку таблицю відключено від решти моделі, перегляньте її стовпці, щоб визначити, чи містить інший стовпець в іншому місці моделі відповідні значення.
Наприклад, припустімо, що є модель, яка містить дані про продажі продукту за територіями та що ви згодом імпортували демографічні дані, щоб з’ясувати, чи існує взаємозв’язок між продажами і демографічними тенденціями на кожній території. Оскільки демографічні дані взято з іншого джерела даних, таблиці з ними спочатку ізольовано від решти моделі. Щоб інтегрувати демографічні дані з рештою моделі, потрібно знайти стовпець в одній із демографічних таблиць, який відповідає поточній моделі. Наприклад, якщо демографічні дані впорядковано за регіоном і дані про продажі вказують на те, у якому регіоні відбувалися продажі, можна створити два набори даних, знайшовши спільний стовпець, наприклад "Країна", "Поштовий індекс" або "Регіон", щоб забезпечити підстановку.
Окрім зведених значень ще є кілька додаткових вимог для створення зв’язку.
- Значення даних у стовпці підстановки мають бути унікальні. Іншими словами, стовпець не може містити дублікати. У моделі даних нульові значення та пусті рядки рівноцінні пустим значенням, які є окремим значенням даних. Це означає, що у стовпці підстановки не можна мати кілька null-значень.
- Типи даних як вихідного стовпця, так і стовпця підстановки мають бути сумісними. Докладні відомості про типи даних див. в статті Типи даних у моделях даних.
Докладні відомості про зв’язки між таблицями див. у статті Зв’язки між таблицями в моделі даних.