Как разорвать связи в excel
Перейти к содержимому

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

  • автор:

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

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

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

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

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

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

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

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

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

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

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

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

Разорвать все связи в Excel

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

Код на VBA

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

Sub UnlinkWorkBooks() Dim WbLinks Dim i As Long Select Case MsgBox("Все ссылки на другие книги будут удалены из этого файла, а формулы, ссылающиеся на другие книги будут заменены на значения." & vbCrLf & "Вы уверены, что хотите продолжить?", 36, "Разорвать связь?") Case 7 ' Нет Exit Sub End Select WbLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks) If Not IsEmpty(WbLinks) Then For i = 1 To UBound(WbLinks) ActiveWorkbook.BreakLink Name:=WbLinks(i), Type:=xlLinkTypeExcelLinks Next Else MsgBox "В данном файле отсутствуют ссылки на другие книги.", 64, "Связи с другими книгами" End If End Sub

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

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

Удалить связи в выделенных ячейках

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

Надстройка
VBA-Excel

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

Исправление недействительных связей с данными

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

Важно: связанный объект не совпадают с гиперссылками. Следующая процедура не исправляет неработающие гиперссылки. Дополнительные сведения о гиперссылках см. в статье Создание или изменение гиперссылки.

Исправление неработающие ссылки

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

  1. Откройте книгу, содержащую неработающую ссылку.
  2. На вкладке Данные щелкните Изменить ссылки. Команда Изменить ссылки недоступна, если книга не содержит ссылок.
  3. В поле Исходный файл выберите неработающую ссылку, которую нужно исправить.

Примечание: Чтобы исправить несколько ссылок, удерживайте нажатой клавишу

и щелкните каждую ссылку.

Удаление неработаемой ссылки

При разрыве ссылки все формулы, ссылающиеся на исходный файл, преобразуются в текущее значение. Например, если формула =SUM([Budget.xls]Annual! C10:C25) приводит к 45, формула будет преобразована в 45 после разрыва связи.

  1. Откройте книгу, содержащую неработающую ссылку.
  2. На вкладке Данные щелкните Изменить ссылки. Команда Изменить ссылки недоступна, если книга не содержит ссылок.
  3. В поле Исходный файл выберите неработающую ссылку, которую нужно удалить.

Примечание: Чтобы удалить несколько ссылок, удерживайте нажатой клавишу

и щелкните каждую ссылку.

Разрыв связи с внешним ресурсом в Excel

Изменить ссылки

  1. На вкладке Данные в группе Подключения нажмите кнопку Изменить связи.

Примечание: Отсутствие команды Изменить связи означает, что файл не содержит связанных данных.

  • Чтобы выделить несколько связанных объектов, удерживая нажатой клавишу CTRL, щелкните каждый связанный объект.
  • Чтобы выделить все связи, нажмите клавиши CTRL+A.

ТБД. Удаление имени определенной ссылки

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

  1. На вкладке Формулы в группе Определенные имена нажмите кнопку Диспетчер имен.
  2. В диалоговом окне Диспетчер имен щелкните имя, которое нужно изменить.
  3. Щелкните имя, чтобы выделить его.
  4. Нажмите кнопку Удалить.
  5. Нажмите кнопку ОК.

Дополнительные сведения

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

Excel 2013 — Разрыв связей

Версия для печати

Очень часто в Excel мы формируем отчеты со ссылками на другие листы файла, но при этом используем и простые формулы внутри самого листа. При копировании таких данных они вставляются в другую книгу «как значения», что приводит к потере формул. Если же переместить или скопировать весь лист целиком, в таблице появятся новые ссылки на файлы, из которых копировались данные.

Чтобы избавиться от таких ссылок, необходимо разорвать связь таблицы со старым файлом:
Данные — Изменить связи — Разорвать связь.

Теперь ссылки превратились в обычные числа, а формулы остались неизменными!

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

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