Как удалить ссылки на другие файлы в excel
Связь – это очень полезная возможность Excel. Ведь очень часто пользователям приходится использовать информацию из других файлов. Но в некоторых ситуациях они способны скорее нанести вред, чем принести пользу. Ведь, к примеру, если отправлять эти файлы по почте, ссылки оказываются нерабочими. Сегодня мы более подробно поговорим о том, что сделать для избежания такой проблемы.
Что такое связи в Excel
- [Продажи 2018.xlsx]. Этот фрагмент содержит ссылку на файл, из которого нужно достать информацию. Его также называют источником.
- Отчет. Это мы использовали следующее имя, но это не название, которое должно обязательно быть. В этом блоке содержится название листа, в каком надо находить информацию.
- $A:$F и $A1 – адрес ячейки или диапазона с данными, которые содержатся в этом документе.
Собственно, процесс создания ссылки на внешний документ и называется связыванием. После того, как мы прописали адрес ячейки, содержащейся в другом файле, изменяется содержимое вкладки «Данные». А именно – становится активной кнопка «Изменить связи», с помощью которой пользователь может отредактировать имеющиеся связи.
Суть проблемы
Тут перед пользователем появляется два возможных варианта, как действовать этой ситуации. Он может нажать «Продолжить» и тогда изменения не обновятся, или же нажать кнопку «Изменить связи», с помощью которой он может обновить их вручную. После того, как мы нажмем эту кнопку, появится дополнительное окно, в котором можно будет изменить связи, указав, где находится правильный файл в данный момент и как он называется.
Как разорвать связь в Эксель
Один из самых простых методов решения описанной выше ситуации в случае, если не получается обновить местонахождение связанного файла самостоятельно – удалить саму связь. Особенно просто это сделать, если в документе содержится только одна связь. Для этого надо выполнить следующую последовательность шагов:
- Открываем меню «Данные».
- Находим раздел «Подключения», и там – опцию «Изменить связи».
- После этого нажимаем на «Разорвать связь».
Если собираетесь отправлять эту книгу по почте другому человеку, настоятельно рекомендуется это сделать до этого. Ведь после удаления связей все значения, которые содержатся в другом документе, будут автоматически загружены в файл, использованы в формулах, и вместо адреса ячейки информация в соответствующих ячейках будет просто трансформирована в значения.
Как разорвать связь со всеми книгами
Но если количество связей становится слишком большим, вручную их удалять может занять немало времени. Чтобы решить эту проблему за один раз, можно воспользоваться специальным макросом. Он находится в аддоне VBA-Excel. Нужно его активировать и перейти на одноименную вкладку. Там будет находиться раздел «Связи», в котором нам надо нажать на кнопку «Разорвать все связи».
Код на VBA
Если же нет возможности активировать это дополнение, можно создать макрос самостоятельно. Для этого необходимо открыть редактор Visual Basic, нажав на клавиши Alt + F11, и в поле ввода кода записать следующие строки.
If Not IsEmpty(WbLinks) Then
For i = 1 To UBound(WbLinks)
ActiveWorkbook.BreakLink Name:=WbLinks(i), Type:=xlLinkTypeExcelLinks
Как разорвать связи только в выделенном диапазоне
Время от времени количество связей очень большое, и пользователь боится, что после удаления какой-то из них не получится вернуть все назад, если какая-то была лишней. Но это проблема, которую легко не допустить. Для этого нужно выбрать диапазон, в котором осуществить удаление связей, а потом удалить их. Для этого надо выполнить следующую последовательность действий:
- Выделить тот набор данных, в котором надо вносить изменения.
- Устанавливаем дополнение VBA-Excel, после чего переходим на соответствующую вкладку.
- Далее находим меню «Связи» и нажимаем на кнопку «Разорвать связи в выделенных диапазонах».
После этого все связи в выделенном наборе ячеек будут удалены.
Что делать, если связи не разрываются
Все описанное выше звучит хорошо, но на практике всегда возникают какие-то нюансы. Например, может случиться ситуация, когда связи не разрываются. В этом случае все равно появляется диалоговое окно, что не получается автоматически обновить связи. Что же делать в этой ситуации?
- Сначала надо проверить, не содержится ли какая-то информация в именованных диапазонах. Для этого надо нажать на комбинацию клавиш Ctrl + F3 или же открыть вкладку «Формулы» – «Диспетчер имен». Если же имя к файлу указано полное, то нужно просто его отредактировать или же вовсе убрать. Перед тем, как удалять именованные диапазоны, необходимо скопировать файл в какое-то другое место, чтобы можно было вернуться к изначальному варианту, если были совершены неправильные действия.
- Если не получается решить проблему с помощью удаления имен, то можно проверить условное форматирование. Ссылка на ячейки в другой таблице может содержаться в правилах условного форматирования. Для этого надо найти соответствующий пункт на вкладке «Главная», а потом нажать на кнопку «Управление файлами».
Обычно Excel не дает возможности давать адрес других книг в условном форматировании, но это делается, если ссылаться на именованный диапазон с отсылкой на другой файл. Обычно даже после удаления связи ссылка остается. Нет никакой проблемы в том, чтобы убрать такую связь, потому что связь по факту нерабочая. Следовательно, ничего плохого не произойдет, если убрать ее.
Также можно воспользоваться функцией «Проверка данных», чтобы узнать, нет ли ненужных ссылок. Обычно связи остаются, если используется тип проверки данных «Список». Но что же делать, если ячеек много? Неужели необходимо последовательно проверять каждую из них? Конечно, нет. Ведь это займет очень много времени. Поэтому нужно воспользоваться специальным кодом, чтобы значительно сэкономить его.
Option Explicit
Dim rr As Range, rc As Range, rres As Range, s$
On Error Resume Next
If rr Is Nothing Then
On Error GoTo 0
For Each rc In rr
On Error Resume Next
On Error GoTo 0
If LCase(s) Like sToFndLink Then
If rres Is Nothing Then
Set rres = Union(rc, rres)
If Not rres Is Nothing Then
Необходимо в редакторе макросов сделать стандартный модуль, а потом туда вставить этот текст. После этого вызвать окно макросов с помощью комбинации клавиш Alt + F8, а потом выбрать наш макрос и кликнуть по кнопке «Выполнить». При использовании этого кода есть несколько моментов, которые надо учитывать:
Обычно после того, как выполнить действия, описанные в вышеприведенной инструкции, ненужных связей уже не должно оставаться. Но в случае наличия некоторых из них в документе и вашей неспособности по тем или иным причинам их убрать (типичный пример – защищенность данных в листе), то можно воспользоваться другой последовательностью действий. Эта инструкция справедлива лишь для версий 2007 и выше.
В Excel мы обычно создаем некоторые внешние ссылки для некоторых целей, однако после завершения конкретной цели вы можете найти и разорвать все внешние ссылки. Здесь я расскажу вам о способах быстрого поиска и взлома внешних ссылок.
Найдите и сломайте внешние ссылки с помощью Kutools for Excel
Находите и разрушайте внешние ссылки с помощью функции редактирования ссылки
Если вы хотите найти и разорвать внешние ссылки, вы можете применить функцию «Редактировать ссылку».
1. Нажмите Данные > Редактировать ссылки. Смотрите скриншот:
2. в Редактировать ссылки выберите книгу, внешние ссылки которой вы хотите разорвать, а затем щелкните Разорвать ссылку. Смотрите скриншот:
3. В появившемся диалоговом окне с предупреждением щелкните значок Разрыв ссылок кнопка. Смотрите скриншот:
4. Затем повторите шаг 2, чтобы разорвать все внешние ссылки из всей книги. После удаления всех внешних ссылок закройте Редактировать ссылки диалоговое окно.
Найдите и сломайте внешние ссылки с помощью Kutools for Excel
С помощью вышеуказанного метода вы сломаете все внешние ссылки из одной и той же книги, но иногда вам может потребоваться сломать некоторые внешние ссылки с разных листов, в этом случае вы можете использовать Kutools for ExcelАвтора Найдите и сломайте неработающие ссылки функция для решения.
После бесплатная установка Kutools for Excel, сделайте следующее:
1. Нажмите Kutools > Ссылка > Найдите и сломайте неработающие ссылки. Смотрите скриншот:
2. в Найдите и сломайте неработающие ссылки вы можете увидеть, что все внешние ссылки перечислены в диалоговом окне, и отметьте ссылки, которые вы хотите разорвать, а затем нажмите Разорвать ссылку чтобы разорвать все отмеченные ссылки.
Наконечник:
1. в Найдите и сломайте неработающие ссылки В диалоговом окне вы можете фильтровать ссылки на основе связанного листа, выбрав имя листа из раскрывающегося списка Фильтр.
2. Вы также можете просмотреть ячейку в реальном времени, щелкнув ссылку в Найдите и сломайте неработающие ссылки диалог после проверки Просмотр ячейки опцию.
Сначала необходимо узнать, присутствуют ли в книге реальные внешние ссылки (существующие), о которых вы просто забыли. Если вы не уверены, есть ли такие, начните с поиска в самом очевидном месте — в формулах. Для этого удостоверьтесь, что никакие другие книги не открыты и выполните поиск по ключевой фразе [*] в формулах на всех листах. Закройте все остальные книги, чтобы гарантировать, что все ссылки в формулах будут содержать [*], где звездочка представляет собой символ шаблона.
Когда вы удостоверитесь, что в формулах ссылок нет, нужно будет убедиться, что допустимые ссылки не прячутся где-либо еще. Для этого нужно начать из глубины книги Excel, содержащей несуществующие ссылки. Выберите команду Вставка → Имя → Присвоить (Insert → Name → Define). Пролистайте список имен, щелкая и выделяя их по очереди и просматривая формулу в поле Формула (Refers to) внизу. Удостоверьтесь, что ни одно из имен не ссылается на другую рабочую книгу.
Если любое из имен указывает за пределы вашей книги, вы не найдете источника, по меньшей мере, одной ссылки, что и решит вопрос обновления. Теперь вы должны решить, что необходимо делать: изменить имя диапазона, чтобы оно ссылалось только на эту книгу, или оставить вес как есть.
Другой возможный источник ссылок — это ваши диаграммы. У них может проявиться та же проблема, которую мы только что рассматривали. Необходимо проверить, что все диапазоны данных и метки оси X (X-axis) диаграммы не ссылаются на внешние книги. И снова вам решать, являются ли найденные ссылки правильными.
Ссылки скрываются и в объектах, например, текстовых полях, автофигурах и т. д. Объекты могут ссылаться на внешние рабочие книги. Самый простой способ найти объекты — выбрать любую ячейку на листе и затем выбрать команду Правка → Перейти (Edit → Go To). В диалоговом окне Переход (Go To) щелкните на кнопке Выделить (Special), установите флажок Объекты (Objects) и щелкните на кнопке ОК. Будут выбраны все объекты на листе. Это необходимо выполнять на копии вашей рабочей книги. Когда все объекты будут выделены, вы сможете удалить, сохранить, закрыть и снова открыть вашу копию, чтобы проверить, решена ли проблема.
Если применить простой способ невозможно, обратите внимание, к чему, по мнению Excel, привязана ссылка (мы будем называть эту книгу хорошей книгой).
Создайте настоящую связь между двумя книгами, открыв обе. Перейдите к проблемной книге и в любой ячейке любого листа введите =. Теперь щелкните любую ячейку в хорошей книге и нажмите клавишу Enter, чтобы создать настоящую внешнюю ссылку на другую книгу. Сохраните обе книги, но не закрывайте их. В книге с несуществующими ссылками выберите команду Правка → Связи (Edit → Links) и щелкните кнопку Изменить (Change Source), чтобы увидеть все ссылки на новую рабочую книгу, с которой вы только что намеренно создали новую связь. Снова сохраните вашу рабочую книгу и удалите ячейку, в которой создали настоящую внешнюю ссылку. И еще раз сохраните файл.
При этом часто исчезает ненужная несуществующая ссылка, так как Excel, наконец, понимает, что вы удалили внешнюю ссылку на книгу. Если проблема, однако, не решена, попробуйте выполнить следующее, но сначала сохраните копию книги.
В открытой проблемной книге удалите один лист, сохраните книгу, затем закройте и заново откройте ее. Если вы не увидите приглашение обновить отсутствующие связи, значит, удаленный лист содержал несуществующую ссылку. Теперь проблема должна быть решена. Однако если это не так, повторите первый шаг для каждого листа в книге. Вам понадобится добавить новый лист перед удалением последнего, так как в рабочей книге должен быть хотя бы один лист.
Мы продолжаем, предполагая, что эта техника у вас сработала. Вот что следует сделать далее. Откройте копию вашей рабочей книги (ту, которая содержит все данные) и сделайте еще одну копию. Вы будете работать с проблемным листом (или листами) и выполнять процесс удаления, чтобы обнаружить, где на листе прячется проблема.
Открыв проблемный лист, выделите некоторый диапазон ячеек (область приблизительно 10 х 10 ячеек) и затем выберите команду Правка → Очистить → Все (Edit → Clear → All). (Вы уверены, что сохранили копию книги?) Сохраните, закройте и заново откройте лист. Если приглашение обновить ссылки не появилось, вы нашли проблему, и теперь необходимо переделать этот блок ячеек. Если приглашение открылось, продолжайте очищать ячейки, пока при очередном открытии приглашение не будет выведено. После этого измените проблемные ячейки.
Мы надеемся, что эти приемы сэкономят ваше рабочее время и нервы, которые тратятся на эти несчастные несуществующие ссылки в рабочих книгах. Это не самые простые и приятные решения, но они позволят забыть о проблемах.
Что такое связи в Excel и как их создать
Иногда при работе с различными отчетами приходится создавать связи с другими книгами(отчетами). Чаще всего это используется в функциях вроде ВПР (VLOOKUP) для получения данных по критерию из таблицы, расположенной в другой книге. Так же это может быть и простая ссылка на ячейки другой книги. В итоге ссылки в таких ячейках выглядят следующим образом:
=ВПР( A2 ;'[Продажи 2018.xlsx]Отчет'!$A:$F;4;0)
или
='[Продажи 2018.xlsx]Отчет'!$A1
- [Продажи 2018.xlsx] - обозначает книгу, в которой итоговое значение. Такие книги так же называют источниками
- Отчет - имя листа в этой книге
- $A:$F и $A1 - непосредственно ячейка или диапазон со значениями
Как разорвать связи
Как правило связи редко нужны на продолжительное время, т.к. они неизбежно увеличивают размер файла, особенно, если связей много. Исключениями являются случаи, когда связи делаются на некие общие файлы, расположенные на сетевом диске и заполняемые разными подразделениями, а в итоговый файл связями просто забираются конкретные значения из этого файла. Чтобы разорвать связи необходимо перейти на вкладку Данные -группа Данные и подключения -Изменить связи:
Выделить нужные связи и нажать Разорвать связь. При этом все ячейки с формулами, содержащими связи, будут преобразованы в значения вычисленные этой формулой при последнем обновлении. Данное действие нельзя будет отменить - только закрытием книги без сохранения.
Так же связи внутри формул разрываются, если формулы просто заменить значениями -Копируем нужные ячейки -Правая кнопка мыши -Специальная вставка -Значения. Формулы в ячейках будут заменены результатами их вычислений, а все связи будут удалены.
Более подробно про замену формул значениями можно узнать из статьи: Как удалить в ячейке формулу, оставив значения?
Что делать, если связи не разрываются
Но иногда возникают ситуации, когда вроде все формулы во всех ячейках уже заменены на значения, но запрос на обновление каких-то связей все равно появляется. В этом случае есть парочка рекомендаций для поиска и удаления этих мифических связей:
Читайте также: