Створення взаємозв’язку між таблицями в Excel - Підтримка Office
Ви коли-небудь використовували VLOOKUP для перенесення стовпця з однієї таблиці в іншу? Тепер, коли Excel має вбудовану модель даних, VLOOKUP застарів. Ви можете створити взаємозв'язок між двома таблицями даних на основі відповідних даних у кожній таблиці. Потім ви можете створювати аркуші Power View та створювати зведені таблиці та інші звіти з полями з кожної таблиці, навіть коли таблиці надходять з різних джерел. Наприклад, якщо у вас є дані про продажі клієнтів, можливо, ви захочете імпортувати та зв’язати дані аналізу часу, щоб проаналізувати схеми продажів за роками та місяцями.
Усі таблиці робочої книги перелічені у списках зведеної таблиці та полів Power View.
Коли ви імпортуєте пов’язані таблиці з реляційної бази даних, Excel часто може створювати ці зв’язки в моделі даних, яку вона будує за кадром. У всіх інших випадках вам потрібно буде створити стосунки вручну.
Переконайтеся, що книга містить принаймні дві таблиці та що кожна таблиця має стовпець, який можна зіставити зі стовпцем іншої таблиці.
Виконайте одне з наступного: Відформатуйте дані як таблицю або Імпортуйте зовнішні дані як таблицю на новому аркуші.
Дайте кожній таблиці значущу назву: У таблиці Інструменти, натисніть Дизайн > Назва таблиці > введіть ім'я.
Переконайтеся, що стовпець в одній з таблиць містить унікальні значення даних без дублікатів. Excel може створити зв'язок, лише якщо один стовпець містить унікальні значення.
Наприклад, щоб пов’язати продажі клієнтів із аналізом часу, обидві таблиці повинні містити дати в одному форматі (наприклад, 1/1/2012), і принаймні одна таблиця (аналіз часу) перераховує кожну дату лише один раз у стовпці.
Клацніть Дані > Відносини.
Якщо відносини сіра, ваша робоча книга містить лише одну таблицю.
В Управління відносинами натисніть Новий.
У Створити стосунки натисніть стрілку для таблиці, і виберіть таблицю зі списку. У відносинах один-до-багатьох ця таблиця повинна бути з багатьох сторін. Використовуючи наш приклад споживчої інформації та часу, ви спочатку вибираєте таблицю продажів споживачів, оскільки багато продажів може відбутися в будь-який день.
Для стовпця (іноземний), виберіть стовпець, що містить дані, які відносяться до пов'язаного стовпця (основний). Наприклад, якби в обох таблицях був стовпець дати, ви вибрали б цей стовпець зараз.
Для пов'язаної таблиці, виберіть таблицю, яка містить принаймні один стовпець даних, пов’язаний із таблицею, яку ви щойно вибрали для таблиці.
Для пов’язаної колонки (первинна), виберіть стовпець, який має унікальні значення, які відповідають значенням у стовпці, який ви вибрали для стовпця.
Докладніше про взаємозв'язки між таблицями в Excel
Нотатки про стосунки
Ви дізнаєтесь, чи існує взаємозв'язок, коли перетягуєте поля з різних таблиць до списку Полів зведеної таблиці. Якщо вам не буде запропоновано створити зв’язок, Excel уже має інформацію про зв’язок, необхідну для зв’язку даних.
Створення взаємозв’язків подібно до використання VLOOKUP: вам потрібні стовпці, що містять відповідні дані, щоб Excel міг перехресно посилатися на рядки в одній таблиці з таблицями іншої таблиці. У прикладі аналізу часу таблиця Клієнта повинна мати значення дати, які також існують у таблиці аналізу часу.
У моделі даних взаємозв'язок таблиці може бути "один до одного" (кожен пасажир має один посадковий талон) або "один до багатьох" (кожен рейс має багато пасажирів), але не "багато до багатьох". Відносини багато-до-багатьох призводять до помилок кругової залежності, наприклад "Виявлено кругову залежність". Ця помилка виникне, якщо ви встановите пряме з'єднання між двома таблицями, які багато-до-багатьох, або непрямі зв'язки (ланцюжок зв'язків таблиць, які є одним-до-багатьох у кожному відношенні, але багато-до-багатьох при перегляді закінчуються до кінця. Докладніше про взаємозв'язки між таблицями в моделі даних.
Типи даних у двох стовпцях повинні бути сумісними. Детальніше див. У розділі Типи даних у моделях даних Excel.
Інші способи створення взаємозв’язків можуть бути більш інтуїтивними, особливо якщо ви не впевнені, які стовпці використовувати. Див. Розділ Створення відносин у режимі перегляду діаграм у Power Pivot.
Приклад: Зв'язок даних розвідки часу з даними польотів авіакомпанії
Ви можете дізнатись як про взаємозв'язок таблиць, так і про аналіз часу, використовуючи безкоштовні дані на ринку Microsoft Azure. Деякі з цих наборів даних дуже великі, що вимагає швидкого підключення до Інтернету, щоб завершити завантаження даних за розумний проміжок часу.
Клацніть Отримати зовнішні дані > Від служби передачі даних > З Microsoft Azure Marketplace. Домашня сторінка Microsoft Azure Marketplace відкривається в майстрі імпорту таблиць.