Как разорвать все внешние ссылки в 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-дневная полнофункциональная пробная версия — без регистрации и кредитной карты
- Лучшее соотношение цены и качества — экономия по сравнению с покупкой отдельных надстроек