Как разорвать связи в Excel
При выполнении сложных расчетов приходится использовать данные из других книг, тем самым создавая связь. При отправке таких файлов по почте, у получателя «слетают» ссылки. Как этого избежать описано ниже.
Описание проблемы
Когда в формуле вы указываете ссылку на другую книгу, то Excel образует с ней связь. Эта связь будет прекрасно работать и обновляться автоматически в том случае, когда открыты обе книги.
К сожалению, если книга-источник была удалена/перемещена или переименована, то связь нарушится. Также связь будет потеряна если вы переместите конечный файл (содержащий ссылку). Если вы передадите только конечный файл по почте, то получатель тоже не сможет обновить связи.
При нарушении связи, ячейки со ссылками на другие книги будут содержать ошибки #ССЫЛКА.
Как разорвать связь
Один из способов решения данной проблемы — разрыв связи. Если в файле только одна связь, то сделать это довольно просто:
- Перейдите на вкладку Данные.
- Выберите команду Изменить связи в разделе Подключения.
- Нажмите Разорвать связь.
ВАЖНО! При разрыве связи все формулы ссылающиеся на книгу-источник будут преобразованы в значения! Отмена данной операции невозможна!
Как разорвать связь со всеми книгами
Для удобства, можно воспользоваться макросом, который разорвет связи со всеми книгами. Макрос входит в состав надстройки VBA-Excel. Чтобы им воспользоваться необходимо:
- Перейти на вкладку VBA-Excel.
- В меню Связи выбрать команду Разорвать все связи.
Код на 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
Как разорваться связи только в выделенном диапазоне
Иногда в книге имеется много связей и есть опасения, что при удалении связи можно удалить лишнюю. Чтобы этого избежать с помощью надстройки можно удалить связи только в выделенном диапазоне. Для этого:
- Выделите диапазон данных.
- Перейдите на вкладку VBA-Excel (доступна после установки).
- В меню Связи выберите команду Разорвать связи в выделенных ячейках.
Надстройка
VBA-Excel
Надстройка для Excel содержит большой набор полезных функций, с помощью которых вы значительно сократите время и увеличите скорость работы с программой.
Excel 2013 — Разрыв связей
Очень часто в Excel мы формируем отчеты со ссылками на другие листы файла, но при этом используем и простые формулы внутри самого листа. При копировании таких данных они вставляются в другую книгу «как значения», что приводит к потере формул. Если же переместить или скопировать весь лист целиком, в таблице появятся новые ссылки на файлы, из которых копировались данные.
Чтобы избавиться от таких ссылок, необходимо разорвать связь таблицы со старым файлом:
Данные — Изменить связи — Разорвать связь.
Теперь ссылки превратились в обычные числа, а формулы остались неизменными!
Разрыв связи с внешним ресурсом в Excel
- На вкладке Данные в группе Подключения нажмите кнопку Изменить связи.
Примечание: Отсутствие команды Изменить связи означает, что файл не содержит связанных данных.
- Чтобы выбрать несколько связанных объектов, щелкните каждый из них, удерживая нажатой клавишу CTRL.
- Чтобы выделить все связи, нажмите клавиши CTRL+A.
Удаление имени определенной ссылки
Если для ссылки использовался определенный имя, имя не удаляется автоматически. Чтобы удалить имя, с помощью следующих действий:
- На вкладке Формулы в группе Определенные имена нажмите кнопку Диспетчер имен.
- В диалоговом окне Диспетчер имен щелкните имя, которое нужно изменить.
- Щелкните имя, чтобы выделить его.
- Нажмите кнопку Удалить.
- Нажмите кнопку ОК.
Дополнительные сведения
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.
покупка
Как разорвать ссылки диаграммы на исходные данные в Excel?
Как правило, диаграмма автоматически изменяется при изменении исходных данных. Однако в некоторых случаях я хочу, чтобы диаграмма оставалась неподвижной при изменении исходных данных. Эта статья покажет вам простой способ разорвать связи диаграмм с их исходными данными в Excel.
- Разрывная диаграмма ссылок на исходные данные с VBA
- Разрыв связи диаграммы с исходными данными с помощью замечательного инструмента
Разрывная диаграмма ссылок на исходные данные с VBA
Этот метод представит VBA для быстрого разрыва связей между всеми диаграммами и их исходными данными на листе Excel. Пожалуйста, сделайте следующее:
1. Включите указанный рабочий лист, на котором вы хотите разорвать все ссылки на диаграмму, и нажмите другой + F11 вместе, чтобы открыть окно Microsoft Visual Basic для приложений.
2. Нажмите Вставить > Модули, а затем вставьте VBA в новое окно модуля.
VBA: разорвать все ссылки на диаграммы на листе
Sub BreakChartLinks() Dim I As Long Dim xChartCount As Long Dim xLinkArr As Variant Dim xSerColl As Variant xLinkArr = ActiveWorkbook.LinkSources(xlLinkTypeExcelLinks) If Not IsEmpty(xLinkArr) Then For I = LBound(xLinkArr) To UBound(xLinkArr) ActiveWorkbook.BreakLink xLinkArr(I), xlLinkTypeExcelLinks Next I End If xChartCount = ActiveSheet.ChartObjects.Count If xChartCount <> 0 Then For I = 1 To xChartCount ActiveSheet.ChartObjects(I).Activate For Each xSerColl In ActiveChart.SeriesCollection xSerColl.Values = xSerColl.Values xSerColl.XValues = xSerColl.XValues xSerColl.Name = xSerColl.Name Next Next End If End Sub3, нажмите F5 ключ или щелкните Run кнопку для запуска этого VBA.
Теперь вы увидите, что все ссылки диаграммы на исходные данные на текущем листе разорваны.
Разрывная диаграмма ссылок на исходные данные с VBA
Если у вас есть Kutools for Excel установлен, вы можете применить его Отсоединить диаграмму инструмент, позволяющий быстро разорвать связь между диаграммой и ее исходными данными одним щелчком мыши в Excel.
Kutools for Excel — Содержит более 300 основных инструментов для Excel. Наслаждайтесь полнофункциональным 30 -дневная БЕСПЛАТНАЯ пробная версия без необходимости использования кредитной карты! Скачать сейчас!
Kutools for Excel — Combines more than 300 Advanced Functions and Tools for Microsoft Excel
1. Выберите диаграмму, ссылку на которую нужно разорвать в Excel.
2. Нажмите Кутулс > Графики > Инструменты для диаграмм > Отсоединить диаграмму для включения этой функции.
Тогда вы увидите, что связь между выбранной диаграммой и ее исходными данными немедленно разорвана.