Как разорвать все внешние ссылки в Excel?
При работе с книгами Excel часто возникает необходимость ссылаться на данные из других файлов — для анализа, составления отчётов или консолидации информации. Такие ссылки создают внешние соединения, связывающие вашу книгу с источниками за пределами текущего файла. Со временем эти ссылки могут накапливаться или устаревать, особенно если исходные файлы переименованы, перемещены, удалены или если вы стремитесь устранить зависимости для удобного совместного использования и контроля версий. Ручное разрывание ссылок по одной — трудоёмкий и рискованный процесс, особенно в крупных или сложных книгах. К счастью, Excel предлагает несколько практичных решений — от встроенных инструментов и полезных надстроек до макросов на VBA, — позволяющих быстро разорвать часть или все внешние ссылки, сохранив целостность данных и повысив переносимость файлов.
Разрыв внешних ссылок с помощью функции Excel «Изменить ссылки»
Разрыв всех внешних ссылок с помощью кода VBA
Разрыв всех / неработающих / указанных внешних ссылок с помощью Kutools для Excel
Разрыв внешних ссылок с помощью функции Excel «Изменить ссылки» (встроенный метод)
Во многих сценариях работы в Excel — особенно при совместной работе с несколькими книгами или при очистке шаблонов — может понадобиться быстрый способ просмотра, обновления или разрыва внешних ссылок. Встроенная функция Excel Изменить ссылки предоставляет именно такую возможность без необходимости использовать VBA, формулы или надстройки.
Применимые сценарии: Этот метод идеально подходит, когда нужно управлять ссылками — как по отдельности, так и массово — с полной прозрачностью относительно того, какие файлы связаны. Особенно полезен для пользователей в стандартных офисных средах, где использование надстроек или макросов ограничено.
Чтобы разорвать внешние ссылки с помощью встроенной функции, выполните следующие действия:
1. Откройте книгу, содержащую внешние ссылки, которые необходимо разорвать.
2. Перейдите на вкладку Данные на ленте.
3. В группе Запросы и подключения или Подключения нажмите кнопку Изменить ссылки.
4. В диалоговом окне Изменить ссылки отобразится список всех обнаруженных внешних ссылок (имена исходных файлов).
5. Чтобы разорвать все ссылки, выберите все исходные файлы в списке: удерживая клавишу Ctrl, щелкните каждый файл или выберите первый и последний, удерживая клавишу Shift.
6. Нажмите кнопку Разорвать ссылку.
7. Подтвердите действие в появившемся предупреждающем диалоговом окне.
8. После разрыва ссылки заменяются их текущими значениями. Быстрая проверка: сохраните, закройте и снова откройте книгу — кнопка Изменить ссылки должна быть недоступна, если все ссылки успешно разорваны.
Примечание:
- Всегда создавайте резервную копию книги перед тем, как разорвать ссылку — эта операция необратима. После разрыва восстановить ссылку уже невозможно: останутся только значения ячеек.
- Если кнопка Изменить ссылки недоступна или некоторые ссылки не удаляются, возможно, они встроены в именованные диапазоны, диаграммы, объекты или используются в условном форматировании. В таких случаях рекомендуется выполнить дополнительную очистку (например, проверить Диспетчер имён, исходные диапазоны диаграмм или воспользоваться средствами VBA и сторонними инструментами).
- После разрыва ссылки рекомендуется проверить формулы и ключевые данные, чтобы убедиться, что ничего важного не утеряно и не преобразовано некорректно.
Преимущества:
- Не требует программирования и установки надстроек.
- Позволяет выборочно обновлять, проверять, открывать источник или разрывать ссылку.
- Встроена в Excel (версия для Windows).
- Некоторые ссылки могут не отображаться — например, те, что находятся в диаграммах, именованных диапазонах или объектах.
- Функция может быть недоступна (затемнена), если внешние ссылки не обнаружены.
- Отсутствует в некоторых версиях Excel (например, в отдельных выпусках для Mac).
Если функция Изменить ссылки не отображает определённые ссылки или не может их разорвать, воспользуйтесь другими методами (например, решениями на основе VBA или надстроек), описанными ниже, чтобы найти и удалить скрытые или сложные ссылки.
Разрыв всех внешних ссылок с помощью кода VBA
Если вам нужно разорвать все обнаруженные внешние ссылки в книге — как рабочие, так и неработающие, — следующий код VBA позволяет быстро и массово выполнить эту задачу, избавляя от необходимости обрабатывать каждую ссылку вручную. Такой подход особенно эффективен, когда у вас десятки или сотни ссылок или когда встроенная функция «Изменить ссылки» не отображает их все.
1. Удерживая клавиши ALT + F11, откройте окно Microsoft Visual Basic для приложений.
2. В этом окне выберите Вставка > Модуль, затем вставьте следующий код VBA в пустое окно модуля:
Sub BreakLinks()
'Updateby20140318
Dim wb As Workbook
Set wb = Application.ActiveWorkbook
If Not IsEmpty(wb.LinkSources(xlExcelLinks)) Then
For Each link In wb.LinkSources(xlExcelLinks)
wb.BreakLink link, xlLinkTypeExcelLinks
Next link
End If
End Sub3. Нажмите клавишу F5 или кнопку «Выполнить», чтобы запустить код. Скрипт VBA пройдёт по всем внешним ссылкам в текущей книге и разорвёт их. После завершения убедитесь, что все ссылки удалены. Обязательно сохраните резервную копию перед запуском — это изменение нельзя отменить, и вы рискуете случайно потерять данные.
Советы и напоминания об ошибках:
- Решения на основе VBA обычно удаляют большинство стандартных внешних ссылок, но некоторые нестандартные или скрытые ссылки — например, встроенные в имена, фигуры или диаграммы — могут потребовать отдельной обработки.
- Если возникает ошибка или ссылка не удаляется, вручную проверьте наличие других ссылок в Менеджере имен (Формулы > Менеджер имен), на диаграммах или в объектах.
- Всегда тестируйте макросы VBA на копии файла, чтобы избежать потери данных.
Разрыв всех / неработающих / указанных внешних ссылок с помощью Kutools для Excel
Приведённый выше код VBA принудительно удаляет все обнаруженные внешние ссылки из всей книги. Однако бывают ситуации, когда нужно воздействовать только на определённые ссылки — например, неработающие, действительные или исходящие из конкретных источников. В таких случаях подход на основе VBA может не обеспечить достаточной точности управления или удобного интерфейса.
Функция Найти и разорвать ссылки из Kutools для Excel позволяет гибко сканировать внешние ссылки на активном листе, фильтровать их по типу (все, неработающие, действительные) и выбирать конкретные элементы для разрыва. Это идеальное решение для точечной очистки ссылок, которое минимизирует риск потери данных из-за случайного массового удаления.
Kutools для Excelвключает более чем 300 удобных инструментов для Excel и доступен в виде бесплатной пробной версии на 30 дней.Получить сейчас
После установки Kutools для Excel выполните следующие действия:
1. Выберите Kutools > Ссылка > Найти и разорвать ссылки.
2. В диалоговом окне Найти и разорвать ссылки отображаются все внешние ссылки на активном листе. Их можно фильтровать с помощью раскрывающегося списка Фильтр по статусу (например, все, все неработающие или все действительные ссылки). Это позволяет удобно сосредоточиться на определённом типе ссылок для пакетной обработки. См. снимки экрана:
 |
 |
 |
3. В этом случае выберите Все недействительные ссылки. В поле отобразятся только неработающие ссылки, как показано:
4. Нажмите кнопку Разорвать ссылку. Все выбранные неработающие ссылки будут удалены с активного листа.
Примечание: Если установлен флажок Активировать ячейку, при щелчке по любой ссылке в списке соответствующая ячейка на листе будет выделена и активирована — это упрощает проверку или анализ перед удалением.
Подробнее о функции «Найти и разорвать ссылки».
Дополнительные примечания и устранение неполадок:
- Kutools обеспечивает более безопасное удаление ссылок по сравнению с ручными методами или VBA и идеально подходит для пакетной обработки.
- Всегда проверяйте результаты на листах после разрыва ссылки, чтобы убедиться, что формулы и значения отображаются корректно.
См. также:
Как найти и вывести список всех внешних ссылок в Excel?
Лучшие инструменты повышения продуктивности в Office
Раскройте весь потенциал Excel с помощью Kutools для Excel и ощутите эффективность как никогда раньше.Kutools для Excel предлагает более 300 расширенных функций для повышения продуктивности и Экономия времени.Нажмите здесь, чтобы получить нужную Вам функцию…
Office Tab добавляет в Office вкладки и значительно упрощает Вашу работу
- Включите редактирование и чтение во вкладках в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна — вместо того чтобы использовать отдельные окна.
- Повышает вашу продуктивность на 50 % и экономит сотни кликов мышью каждый день!
Все надстройки Kutools — один установщик
Kutools for Office — набор надстроек для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro, идеально подходящий командам, работающим с разными приложениями Office.
- Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
- Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
- Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
- 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек