Вы когда-нибудь использовали функцию ВПР для переноса столбца из одной таблицы в другую? Excel также включает встроенную модель данных, которая позволяет создавать связи между таблицами, что может быть альтернативой использованию функций просмотра, таких как ВПР. Вы можете создать связь между двумя таблицами на основе совпадающих данных в них. После этого можно создавать сводные таблицы и другие отчеты с полями из каждой таблицы, даже если они взяты из разных источников. Например, если у вас есть данные о продажах клиентам, вам может потребоваться импортировать и связать данные логики операций со временем, чтобы проанализировать тенденции продаж по годам и месяцам.
Все таблицы в книге перечислены в списке полей сводной таблицы.
Связи чаще всего используются при создании сводных таблиц на основе нескольких таблиц в модели данных. Это позволяет анализировать связанные данные, не объединяя их в одну таблицу.
Примечание
Если книга содержит модель данных, связями между таблицами можно управлять на вкладке "Данные".
При импорте связанных таблиц из реляционной базы данных Excel часто может создать эти связи в модели данных, которая строится в фоновом режиме. Во всех остальных случаях отношения необходимо создавать вручную.
- Убедитесь, что книга содержит хотя бы две таблицы и в каждой из них есть столбец, который можно сопоставить со столбцом из другой таблицы.
- Выполните одно из следующих действий: отформатируйте данные в виде таблицы или импортируйте внешние данные в виде таблицы на новом листе.
- Присвойте каждой таблице понятное имя: в меню "Работа с таблицами" щелкните "Конструктор>>имени таблицы" и введите имя.
- Убедитесь, что столбец в одной из таблиц имеет уникальные значения без дубликатов. Excel может создавать связи только в том случае, если один столбец содержит уникальные значения.
Например, чтобы связать продажи клиентам с аналитикой времени, обе таблицы должны включать даты в одном и том же формате (например, 01.01.2026), и по крайней мере одна таблица (аналитика времени) должна перечислять каждую дату только один раз в столбце. - Выберите "Отношения данных>".
Если команда Отношения недоступна, значит книга содержит только одну таблицу.
- В окне "Управление связями" нажмите кнопку "Создать".
- В окне Создание связи щелкните стрелку рядом с полем Таблица и выберите таблицу из раскрывающегося списка. В связи "один ко многим" эта таблица должна быть частью с несколькими элементами. В примере с клиентами и логикой операций со временем необходимо сначала выбрать таблицу продаж клиентов, потому что каждый день, скорее всего, происходит множество продаж.
- Для элемента Столбец (чужой) выберите столбец, который содержит данные, относящиеся к элементу Связанный столбец (первичный ключ). Например, при наличии столбца даты в обеих таблицах необходимо выбрать этот столбец именно сейчас.
- В поле Связанная таблица выберите таблицу, содержащую хотя бы один столбец данных, которые связаны с таблицей, выбранной в поле Таблица.
- В поле Связанный столбец (первичный ключ) выберите столбец, содержащий уникальные значения, которые соответствуют значениям в столбце, выбранном в поле Столбец.
- Нажмите кнопку ОК.
Дополнительные сведения о связях между таблицами в Excel
Примечания о связях
Чтобы узнать, существует ли связь, перетащите поля из разных таблиц в список полей сводной таблицы. Если вам не предлагается создать связь, у Excel уже есть сведения о связи, необходимые для связи.
Создание связей похоже на использование функции ВПР: вам нужны столбцы с совпадающими данными, чтобы Excel мог ссылаться на перекрестные строки из одной таблицы с строками другой таблицы. В примере с логикой ко времени таблица Customer должна содержать значения дат, которые также существуют в таблице операций со временем.
- В модели данных Excel отношения обычно бывают "один-к-одному" или "один-ко-многим". Связи "многие-ко-многим" требуют дополнительного моделирования (например, с помощью таблицы подстановки). Связи "многие-ко-многим" приводят к циклическим ошибкам, например "Обнаружена циклическая зависимость". Эта ошибка возникает, если установить прямое соединение между двумя таблицами с отношением "многие-ко-многим", или косвенные связи (цепочка связей между таблицами, представляющими собой "один-ко-многим" внутри каждой связи, но "многие-ко-многим" при просмотре вдоль и поперек). Дополнительные сведения см. в статье Связи между таблицами в модели данных.
В отличие от формул подстановки, отношения не дублируют данные. Вместо этого они связывают таблицы, чтобы поля из каждой таблицы можно было использовать вместе в сводной таблице.
Типы данных в двух столбцах должны быть совместимы. Подробные сведения см. в статье Типы данных в моделях данных.
Другие способы создания связей могут оказаться более понятными, особенно если неизвестно, какие столбцы использовать. Дополнительные сведения см. в статье Создание связи в представлении диаграммы в Power Pivot.
"Могут потребоваться связи между таблицами"
При добавлении полей в сводную таблицу вы будете получать информацию о том, требуется ли связь между таблицами для определения значения полей, выбранных в сводной таблице.
Хотя приложение Excel может определить, когда требуется связь, оно не может сказать, какие таблицы и столбцы следует использовать и возможно ли оно вообще. Чтобы получить ответы на свои вопросы, попробуйте сделать следующее.
Шаг 1. Определите, какие таблицы указать в связи
Если ваша модель содержит всего лишь несколько таблиц, понятно, какие из них нужно использовать. Но для больших моделей вам может понадобиться помощь. Один из способов заключается в том, чтобы использовать представление диаграммы в надстройке Power Pivot. Представление диаграммы обеспечивает визуализацию всех таблиц в модели данных. С помощью него вы можете быстро определить, какие таблицы отделены от остальной части модели.
Примечание
В сводной таблице могут создаваться неоднозначные связи, которые могут оказаться недопустимыми. Предположим, что все таблицы каким-либо образом связаны с другими таблицами в модели, но при попытке объединить поля из разных таблиц появляется сообщение "Могут потребоваться связи между таблицами". Наиболее вероятная причина заключается в том, что вы заключили отношения "многие-ко-многим". Если вы будете следовать цепочке связей между таблицами, которые подключаются к необходимым для вас таблицам, то вы, вероятно, обнаружите наличие двух или более связей "один ко многим" между таблицами. Не существует простого обходного пути, который бы работал в любой ситуации, но вы можете попробоватьсоздать вычисляемые столбцы, чтобы консолидировать столбцы, которые вы хотите использовать в одной таблице.
Шаг 2. Найдите столбцы, которые могут быть использованы для создания пути от одной таблице к другой
Определив, какая таблица отключена от остальной части модели, просмотрите ее столбцы и определите, нет ли в других столбцах модели совпадающих значений.
Предположим, у вас есть модель, которая содержит продажи продукции по территории, и вы впоследствии импортируете демографические данные, чтобы узнать, есть ли корреляция между продажами и демографическими тенденциями на каждой территории. Так как демографические данные поступают из различных источников, то их таблицы первоначально изолированы от остальной части модели. Чтобы интегрировать демографические данные с остальной частью модели, нужно найти в одной из демографических таблиц столбец, соответствующий уже используемой. Например, если демографические данные организованы по регионам и ваши данные о продажах определяют область продажи, то вы могли бы связать два набора данных, найдя общие столбцы, такие как государство, почтовый индекс или регион, чтобы обеспечить подстановку.
Кроме совпадающих значений есть несколько дополнительных требований для создания связей.
- Значения данных в столбце подстановки должны быть уникальными. Другими словами, столбец не может содержать дубликатов. В модели данных нули и пустые строки эквивалентны пустому полю, которое является самостоятельным значением данных. Это означает, что в столбце подстановки не может быть нескольких значений NULL.
- Типы данных столбца подстановок и исходного столбца должны быть совместимы. Подробнее о типах данных см. в статье Типы данных в моделях данных.
Подробнее о связях таблиц см. в статье Связи между таблицами в модели данных.