Создание связи между таблицами Excel

Связь между таблицами Excel – это формула, которая возвращает данные с ячейки другой рабочей книги. Когда вы открываете книгу, содержащую связи, Excel считывает последнюю информацию с книги-источника (обновление связей)

Межтабличные связи в Excel используются для получения данных как с других листов рабочей книги, так и с других рабочих книг Excel. К примеру, у вас имеется таблица с расчетом итоговой суммы продаж. В расчете используются цены на продукт и объем продаж. В таком случае имеет  смысл создать отдельную таблицу с данными по ценам, которые будут подтягиваться с помощью связей первой таблицы.

связи Excel

Когда вы создаете связь между таблицами, Excel создает формулу, которая включает в себя имя исходной книги, заключенную в скобки [], имя листа с восклицательным знаком на конце и ссылку на ячейку.

Создание связей между рабочими книгами

  1. Открываем обе рабочие книги в Excel
  2. В исходной книге выбираем ячейку, которую необходимо связать, и копируем ее (сочетание клавиш Ctrl+С)
  3. Переходим в конечную книгу, щелкаем правой кнопкой мыши по ячейке, куда мы хотим поместить связь. Из выпадающего меню выбираем Специальная вставка
  4. В появившемся диалоговом окне Специальная вставка выбираем Вставить связь.

Специальная вставка

Есть еще один, более простой, вариант создания связи между таблицами. В ячейку, куда  мы хотим вставить связь, ставим знак равенства (так же как и для обычной формулы), переходим в исходную книгу, выбираем ячейку, которую хотим связать, щелкаем Enter.

Вы можете использовать инструменты копирования и автозаполнения для формул связи так же, как и для обычных формул.

Прежде чем создавать связи между таблицами

Прежде чем вы начнете распространять знания на свои грандиозные идеи, прочитайте несколько советов по работе со связями в Excel:

Делайте легко отслеживаемые связи. Не существует автоматического поиска всех ячеек, содержащих ссылки. Поэтому, используйте определенный формат, чтобы быстро идентифицировать связи с другими таблицами, в противном случае, документ, содержащий ссылки, может разрастись до такой степени, что его будет трудно поддерживать.

Автоматические вычисления. Исходная книга должна работать в режиме автоматического вычисления (установлено по умолчанию). Для переключения параметра вычисления перейдите по вкладке Формулы в группу Вычисление. Выберите Параметры вычислений –> Автоматически.

Избегайте циклические ссылки. Циклические связи – когда две рабочие книги содержат ссылки друг на друга – могут быть причиной медленного открытия и работы файла.

Обновление связей

Для ручного обновления связи между таблицами, перейдите по вкладке Данные в группу Подключения. Щелкните по кнопке Изменить связи.

Изменить связи Excel

В появившемся диалоговом окне Изменение связей, выберите интересующую вас связь и щелкните по кнопке Обновить.

Обновление связи

Разорвать связи в книгах Excel

Разрыв связи с источником приведет к замене существующих формул связи на значения, которые они возвращают. Например, связь =[Источник.xlsx]Цены!$B$4 будет заменена на 16. Разрыв связи нельзя отменить, поэтому прежде чем совершить операцию, рекомендую сохранить книгу.

Перейдите по вкладке Данные в группу Подключения. Щелкните по кнопке Изменить связи. В появившемся диалоговом окне Изменение связей, выберите интересующую вас связь и щелкните по кнопке Разорвать связь.


8 комментариев

  1. Спасибо! очень полезный материал!

    Пожалуйста, исправьте опечатку:
    «В исходной книге выбираем ячейку, которую необходимо связать, и копируем ее (сочетание клавиш Ctrl+V)»
    Думаю должно быть «Ctrl+С»

  2. Добрый день!
    Спасибо! Интересная информация. Подскажите, связь между файлами не нарушится, если изменить название файла Источник?

  3. Добрый день.
    Есть вопрос: как мультиплицировать связи, т.е. копировать готовые связи в файле, применять их к другим ячейкам и изменяя свойства скопированных связей «подкачивать» информацию из другого файла?
    например:
    Есть файлы «Январь», «февраль», «март» с показателями, форма файлов (ячейки, столбцы, строки) идентичны, изменяются только данные.
    Задача сделать файл «сводка», в котором построчно
    январь
    февраль
    март
    свести данные из каждого файла.
    легко получается сделать связи по строчке январь, и дальше нужно опять ВРУЧНУЮ делать связи для февраля и марта
    Хотелось бы связи января «скопировать» в строки февраля и марта и настроить связь в каждой строчке на файл соответствующего месяца.

    Заранее спасибо.
    p.s. сам потыкал, не получается. все скопированные связи он видит как одну и ту же связь, хоть сто раз её в этом файле вставь( во вкладке Данные\изменить связи, остаётся один набор связей), а хотелось бы что бы после Ctrl+V было 2, 3, 4 и.т.д. наборов связей которые уже можно было настраивать.

    Если не сложно подскажите как это можно сделать?

  4. Добрый день.
    Создаю связанные таблицы для создания бланков заказа, чтобы не вводить 2 раза одни и те же значения, а также для того, чтобы была создана база данных клиентов.
    Итого: Нужен документ с данными клиентов, Исходный бланк заказов должен автоматически копировать данные о клиенте, разумеется эти данные изменяются.

    Создал таблицу бланка заказов, создал таблицу Базы данных клиентов с основной информацией для заполнения бланка.

    Создал связь, все вписывается автоматически. Как теперь выйти из ситуации, чтобы в следующий раз, при внесении сведений о новом клиенте, сохранить предыдущего клиента и в бланк вписать новые данные?

    Я подумал, что смогу простым добавлением новой строки поверх созданного клиента обнулить бланк заказа и внести новые данные, однако при таком ходе данные в бланке остаются неизменными, при том, что исходные координаты изменились, по. идее в бланке должны быть пустые значения.

    Можно ли как-то решить эту задачу? Привязать значение строки в исходной книге?

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *