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