Изменение связей в excel

Exceltip

Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Вам также могут быть интересны следующие статьи

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

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

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

Источник: exceltip.ru

Microsoft Excel

трюки • приёмы • решения

Как в Excel 2013 обновить ссылки и изменить источник связи

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

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

  1. Если обе книги — источник и клиент — открыты, Excel автоматически обновляет ссылки, как только данные в книге-источнике меняются.
  2. Если открыта книга-источник в тот момент, когда вы открываете книгу-клиент, Excel также производит автоматическое обновление.
  3. Если книга-источник закрыта в момент открытия книги, использующей ее данные. Excel выводит строку предупреждения о том, что автоматическое обновление ссылок отключено. В таком случае нажмите на кнопку Параметры, установите переключатель в положение Включить это содержимое и нажмите на кнопку ОК. Вы также можете включить автоматическое обновление ссылок для всех книг. Для этого нажмите на кнопку Файл, далее Параметры, Центр управления безопасностью, затем нажмите на кнопку Параметры центра безопасности, далее на вкладке Внешнее содержимое переведите переключатель в положение включить автоматическое обновление для всех связей в книге.
  4. Если вы не обновили ссылки при открытии зависимого документа, вы можете обновить их в любое время впоследствии. Для этого нерейдите на вкладку Данные ленты инструментов, далее нажмите на кнопку Изменить связи. На экране вы увидите диалоговое окно Изменение связей. В этом окне выберите необходимую связь и нажмите на кнопку Обновить.
Читайте также:  Как сделать слияние excel и excel

Если имя документа, содержащего подгружаемые данные, изменилось, вам необходимо будет изменить и ссылку для поддержания данных в актуальном виде. Вы можете непосредственно изменить все связи, выполнив следующие шаги:

  1. Откройте книгу-клиент и активируйте ее. Перейдите на вкладку Данные ленты инструментов, затем нажмите на кнопку Изменить связи. На экране вы увидите диалоговое окно Изменение связей.
  2. Нажмите на ссылку (связь), с которой хотите работать.
  3. Нажмите на кнопку Изменить. Excel выведет на экран окно поиска/указания файлов, где вы можете указать путь к необходимому документу.
  4. Нажмите Закрыть для возврата к книге.

Курс для школьников, испытывающих страх перед математикой или потерявших к ней интерес. Занятия по подготовке к ЕГЭ по математике ведут два преподавателя: опытный психолог помогает вернуть интерес к учебе; опытный педагог-математик по специальной программе восполняет пробелы в знаниях. Ученики не только обучаются математике, но и получают стойкий психологический результат: уверенность в себе, возможность учиться дальше самостоятельно, интерес к предмету.

Источник: excelexpert.ru

Как разорвать связи в Excel

Описание проблемы

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

К сожалению, если книга-источник была удалена/перемещена или переименована, то связь нарушится. Также связь будет потеряна если вы переместите конечный файл (содержащий ссылку). Если вы передадите только конечный файл по почте, то получатель тоже не сможет обновить связи.

При нарушении связи, ячейки со ссылками на другие книги будут содержать ошибки #ССЫЛКА.

Как разорвать связь

Один из способов решения данной проблемы – разрыв связи. Если в файле только одна связь, то сделать это довольно просто:

  1. Перейдите на вкладку Данные.
  2. Выберите команду Изменить связи в разделе Подключения.
  3. Нажмите Разорвать связь.

ВАЖНО! При разрыве связи все формулы ссылающиеся на книгу-источник будут преобразованы в значения! Отмена данной операции невозможна!

Как разорвать связь со всеми книгами

Для удобства, можно воспользоваться макросом, который разорвет связи со всеми книгами. Макрос входит в состав надстройки VBA-Excel. Чтобы им воспользоваться необходимо:

  1. Перейти на вкладку VBA-Excel.
  2. В меню Связи выбрать команду Разорвать все связи.

Код на VBA

Код макроса удаляющего все связи с книгой представлен ниже. Можете скопировать его в свой проект.

Как разорваться связи только в выделенном диапазоне

Иногда в книге имеется много связей и есть опасения, что при удалении связи можно удалить лишнюю. Чтобы этого избежать с помощью надстройки можно удалить связи только в выделенном диапазоне. Для этого:

  1. Выделите диапазон данных.
  2. Перейдите на вкладку VBA-Excel (доступна после установки).
  3. В меню Связи выберите команду Разорвать связи в выделенных ячейках.

Источник: micro-solution.ru

Как разорвать связи в Excel

Описание проблемы

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

К сожалению, если книга-источник была удалена/перемещена или переименована, то связь нарушится. Также связь будет потеряна если вы переместите конечный файл (содержащий ссылку). Если вы передадите только конечный файл по почте, то получатель тоже не сможет обновить связи.

При нарушении связи, ячейки со ссылками на другие книги будут содержать ошибки #ССЫЛКА.

Как разорвать связь

Один из способов решения данной проблемы – разрыв связи. Если в файле только одна связь, то сделать это довольно просто:

  1. Перейдите на вкладку Данные.
  2. Выберите команду Изменить связи в разделе Подключения.
  3. Нажмите Разорвать связь.

ВАЖНО! При разрыве связи все формулы ссылающиеся на книгу-источник будут преобразованы в значения! Отмена данной операции невозможна!

Как разорвать связь со всеми книгами

Для удобства, можно воспользоваться макросом, который разорвет связи со всеми книгами. Макрос входит в состав надстройки VBA-Excel. Чтобы им воспользоваться необходимо:

  1. Перейти на вкладку VBA-Excel.
  2. В меню Связи выбрать команду Разорвать все связи.

Код на VBA

Код макроса удаляющего все связи с книгой представлен ниже. Можете скопировать его в свой проект.

Как разорваться связи только в выделенном диапазоне

Иногда в книге имеется много связей и есть опасения, что при удалении связи можно удалить лишнюю. Чтобы этого избежать с помощью надстройки можно удалить связи только в выделенном диапазоне. Для этого:

  1. Выделите диапазон данных.
  2. Перейдите на вкладку VBA-Excel (доступна после установки).
  3. В меню Связи выберите команду Разорвать связи в выделенных ячейках.

Источник: micro-solution.ru

Диалоговое окно “Новая связь с данными Excel”

Связывает данные из таблицы, созданной в Microsoft Excel, с данными в таблице внутри чертежа.

Список параметров

Отображаются следующие параметры.

Файл (включая путь к файлу), связь с данными которого создается.

Выбор файла Excel

Имеется возможность выбора установленного файла Microsoft XLS, XLSX или CSV для связывания его с чертежом. В нижней части этого раскрывающегося списка можно выбрать новый файл XLS, XLSX или CSV , чтобы создать связь с данными.

Читайте также:  Как в excel вставить текст из word

Нажмите кнопку [. ], чтобы осуществить поиск другого файла Microsoft Excel на вашем компьютере.

Определяет, какой путь будет использоваться для поиска указанного выше файла. Существует три варианта задания пути: полный путь, относительный путь и без пути.

  • Полный. Используется полный путь к указанному выше файлу, включающий корневую папку и все вложенные папки, внутри которых находится связанный файл Microsoft Excel.
  • Относительный. Для ссылки на связанный файл Microsoft Excel используется путь относительно текущего чертежа. Для использования относительного пути связанный файл должен быть сохранен.
  • Без пути. Для ссылки на связанный файл Microsoft Excel используется только имя файла.

Задаются данные в файле Excel для связи с чертежом.

Выбор листа Excel для связи его с

Отображаются имена всех листов внутри указанного файла XLS, XLSX или CSV. Указанные ниже параметры связи будут применены к листу, который выбран здесь.

Связь всего указанного листа в файле Excel с таблицей на чертеже.

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

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

Задается диапазон ячеек в файле Excel для связи с таблицей на чертеже.

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

  • Прямоугольные области (например, A1:D10)
  • Все столбцы (например, A:A)
  • Наборы столбцов (например, A:D)

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

Отображается образец таблицы с использованием примененных параметров.

Дополнительные параметры

Отображение дополнительных параметров. Эта кнопка становится активной при использовании существующего файла Excel или поиске нового.

С помощью опций этого поля определяются способы пересылки информации из внешнего источника в данный чертеж .

Сохранение форматов данных и формул

Импорт данных с формулами и присоединенными поддерживаемыми форматами данных.

Импорт форматов данных. Данные вычисляются по формулам в Excel.

Импорт данных Microsoft Excel в виде текста с данными, рассчитанными по формулам в Excel (поддерживаемые форматы данных не присоединены).

При выборе этой опции команда СВЯЗЬОБНОВИТЬ может использоваться для передачи любых изменений, сделанных в связанных данных на чертеже, в исходную внешнюю электронную таблицу.

Использование форматирования Excel

Признак использования в файле чертежа форматирования, заданного в исходном файле XLS, XLSX или CSV. Если этот параметр не выбран, применяется форматирование стиля таблиц, заданное в диалоговом окне “Вставка таблицы”.

При выборе вышеупомянутой опции она обновляет все измененное форматирование, когда используется команда СВЯЗЬОБНОВИТЬ.

При выборе этой опции форматирование, указанное в исходном файле XLS, XLSX или CSV, будет передано в чертеж, но любые сделанные в форматировании изменения не будут включены, когда используется команда СВЯЗЬОБНОВИТЬ.

Источник: docs.autodesk.com

Изменение связей в excel

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

Изначально в формуле будет прописан адрес ячейки следующего вида:
[Курсы валют.xlsx]ЦБ’!$B$3,
где в квадратных скобках указывается название файла — в приведенном примере ссылки — Курсы валют.xlsx, затем название листа ЦБ и адрес ячейки — $B$3. По умолчанию, ссылка на ячейку другого файла абсолютная, однако при необходимости ее можно сделать и относительной и смешанной.

При открытии файла (если другие книги-источники будут закрыты) на экране появляется ПРЕДУПРЕЖДЕНИЕ СИСТЕМЫ БЕЗОПАСНОСТИ [SECURITY WARNING], которое сообщает, что автоматическое обновление ссылок отключено. Если нажать Включить содержимое [Enable Content], то результаты расчетов будут обновлены.

Вид ссылки немного изменяется — в него добавляется полный путь расположения файла, например:
‘D:Материалы EXCEL Level 2[Курсы валют.xlsx]ЦБ’!$B$3.

Для редактирования связи с внешним источником на вкладке Данные [Data], в группе Подключения [Connections], выбрать Изменить связи [Edit Links]

В окне Изменение связей [Edit Links] выбрать нужное действие:

  • Обновить [Update Values] – обновление выделенного источника. Состояние Неизвестно свидетельствует о том, что в данном окне обновление данных из источников не производилось. После выполнения команды Обновить состояние изменяется на OK.
  • Изменить [Change source] – изменение внешнего источника в случае смены имени файла или перемещения источника в другую папку. Следует помнить, что структура книги изменяться не должна, т.к. внутри выбираемого файла название листа и адрес нужной ячейки должны совпадать с изначально заданными в формуле.
  • Открыть [Open Source] – открытие файла-источника.
  • Разорвать связь [Break Link] — разорвать связь с внешним источником. Формулы в ячейках заменяются значениями, которые получены в ячейках на данный момент. Команду отменить нельзя, поэтому следует создать копию файла, чтобы при необходимости остался исходный файл с формулами для последующих обновлений.
  • Запрос на обновление связей [Startup Prompt] – возможность настроить вариант обновления при открытии файла:
    — Пользователь указывает, нужно ли задавать вопрос [Let users choose to display the alert or not]]
    — Не задавать вопрос и не обновлять связи [Don’t display the alert and don’t update automatic links]
    — Не задавать вопрос и обновлять связи [Don’t display the alert and update links]
Читайте также:  If excel как пользоваться

Оставьте комментарий!

На сообщение “Ссылки на другие книги Excel” комментарий 41

У меня, обычно, возникает проблема как увидеть сами ячейки, в которых в формулах ссылки на другие файлы. Можно же это как-то сделать?

присоединяюсь к вопросу

Занятно-занятно, нигде раньше на такое не натыкался.
Тема довольно интересная для новичка

А вот и ответ на Ваши вопросы:
«Как увидеть есть ли ссылки на другие файлы, и где они находятся в книге Excel?» http://mirexcel.ru/?p=250

спасибо, я нашел ответы на свои вопросы

Спасибо, кратно и информативно

открыл источники, пишу формулу, но при переходе к другому файлу, формула завершается 🙁 Как быть?

Лёня, вероятнее всего, у вас открыта программа excel несколько раз и в каждой из них уже открыты файлы. Закройте файлы и откройте в одном окне программы

Я правильно понимаю, что использование внешних файлов в любых командах оставляет след в окне «Изменить связи»?

может ли быть так. что у меня есть ссылки на другие файлы, но данная команда недоступна?

Здорово, прочитал 2 статьи про внешние источники и нашел ответы на свои вопросы!

Если у меня файл находится на общем диске, то мне подойдет такой алгоритм действий?

Жанна, алгоритм будет такой же

А я считаю, что все это верно и очень точно подмечено! И таких мелочей можно накопать тысячу!

Все просто, когда знаешь. Я вечно ищу все по всем ячейкам. Хорошо быть умной! Я тоже хочу. Спасибо вам за сайт, очень полезный ресурс, мне ВСЁ-ВСЁ-ВСЁ здесь очень нравится…

Есть ли подобная команда, при использовании данных с разных листов, но внутри одной книги?

Может быть такое, что ссылки на другие файлы вроде как есть, но в окне Изменить связи я их не вижу?

Arn, возможно, используется в качестве источника в окне Данные — Проверка данных. Посмотрите.

Прошу прощения, что вопрос немного не по Excel, но где подобная команда в PowerPoint?

Robert, если 2013 офис, то открываете Файл — Сведения и в правой части найдете «изменить связи»

Здравствуйте.
вопрос такой: при указании ссылки с целым числом на ячейку в другом документе все в порядке, оно и отображается, а если в этой ячейке не число а формула, даже простая, показывает либо 0,0, либо #ЗНАЧ. В зависимости от формата ячеек. Как получить значение?

Евгений, поиграла с разными своими файлами, такой эффект не обнаружила. Если это не секретная информация, пришлите mirexcel@ya.ru — попробую разобраться

ДД!
У меня 4 файла в одной папке ссылаются друг на друга, но при открытии этих файлов на другом компьютере все формулы ломаются. Что делать?

ДД, Алёна!
Если Вы на другом компьютере и у Вас только один файл, то это правильно. Если Вы все файлы храните в одной папке и её перемещаете (копируете) на другой компьютер, то Excel сам ссылки корректируют. Но если расположение иное, то через команду «Изменить источник» необходимо «рассказать» об их новом расположении — простым открытием.

Я внимательно вчитываюсь в каждую статью и радуюсь новым знаниям

Boris, ничего нового не встречала

Здравствуйте! как по умолчанию установить относительную ссылку на другую книгу? Именно относительную, что бы каждый раз не убирать значки доллара. подскажите. пжлст, очень нужно.

Татьяна, здравствуйте!
Стандартными средствами такое не встречалось. Придется всё-таки 3 раза нажимать клавишу F4.

Если я разорвал связь с источником. Есть ли способ быстренько вернуть?

Увы, если Вы не создали копию файла, то только создавать формулы заново.

Можно сделать так, чтобы при открытии файла сразу обновлялись все связи?

Когда знаешь — всё просто, когда не знаешь — мучения. Спасибо Вам огромное за Ваш труд!

Добрый день! как открыть файлы с перекрестными ссылками на другом компьютере без ломки ссылок и без изменения расположения каждый раз при открытии файлов на другом компьютере?

Открыть без обновления

Почему когда я открываю файл на своем компьютере, у меня никогда не предлагается выбрать ОБновление?

Варианта, обычно два:
1) Может,открыть файл-источник
2) настройка в программе на автоматическое обновление связей без вопроса к пользователю

Есть возможность уменьшить размер файла, если в нем есть линки на другие файлы?

Mila, можно попробовать через VBA

У меня есть 5 файлов, 1-й ссылается на 2-й, 2-й на 3-й и т.д. Когда открываю 1-й и обновляю, то ничего не происходит. Что я делаю не так?

Tamara, чтобы у Вас обновилось, следует открыть все 5 файлов

Источник: mirexcel.ru