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