Как удалить неверную ссылку в Excel

Обновлено: 03.07.2024

Если вы когда-либо открывали файл Excel и вам предлагалось обновить ссылки на другой файл, но вы уверены, что ссылок на какие-либо другие файлы нет, ваш файл может содержать фантомные ссылки.

Чтобы удалить фантомные ссылки из книги, сначала убедитесь, что в файле нет фиктивных ссылок.

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

Проверьте наличие реальных ссылок в формулах

Перейдите в меню "Правка" и выберите параметр "Ссылки" внизу (если этот параметр неактивен, значит, реальных ссылок на формулы нет).
Выполните поиск (Ctrl + F) файла, который отображается в меню "Редактировать ссылки".
Выполняйте одновременный поиск на всех вкладках, нажав Shift + Ctrl + Page Down, а затем запустив поиск.
В поиске можно использовать подстановочный знак *.
Если вы найдете настоящую ссылку, измените ссылку на файл или удалите ссылку

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

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

Проверить наличие ссылок на диаграммы

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

Проверить наличие ссылок на объекты

Ссылки могут существовать в объектах, таких как текстовые поля, автофигуры или объекты рисования.
На каждом листе нажмите клавишу F5, нажмите кнопку "Специальные" и выберите "Объекты".
Удалите эти объекты, а затем сохраните файл.
Закройте и снова откройте файл, чтобы проверить, решило ли это проблему (вы можете сделать копию файла перед выполнением этого шага).

Проверить наличие ссылок для проверки данных

Ссылки могут существовать как часть критериев проверки данных.
Самый простой тест – удалить всю проверку и посмотреть, осталась ли ссылка (перед выполнением этого шага вы можете сделать копию файла).
Скопируйте любую пустую неформатированную ячейку
Выберите все ячейки с проверкой; нажмите клавишу F5, нажмите кнопку "Специальные" и выберите "Проверка данных"
Выберите "Специальная вставка", а затем выберите "Проверка".
Сохраните, закройте и снова откройте файл, чтобы проверить, устранена ли проблема.
Повторите описанные выше шаги для остальных рабочих листов в файле.

Проверить ссылки условного форматирования

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

Проверить наличие ссылок на скрытых рабочих листах

Если вы или кто-то еще скрыл какие-либо рабочие листы в файле, на этих скрытых листах могут быть ссылки
Проверьте наличие скрытых рабочих листов, перейдя в меню "Отображение листа форматирования".
Если этот параметр неактивен, файл, вероятно, не содержит скрытых листов; однако не забудьте проверить наличие очень скрытых листов в редакторе Visual Basic.
Если есть скрытые рабочие листы, отобразите их и выполните все описанные выше шаги для скрытых листов.

Если вы выполняете действия, описанные на предыдущей странице, а нефантомные ссылки отсутствуют, возможно, книга содержит фантомные ссылки.

Удалить фантомные ссылки

Ссылаться на саму ссылку

Перейдите в меню «Редактировать ссылки» и выберите ненужную ссылку.
Выберите кнопку «Изменить источник» и укажите ссылку на себя.
Обычно это не работает, поскольку Excel, вероятно, сообщит вам, что есть ошибка в формуле, но попробовать стоит

У меня есть книга Excel, которая каждый раз, когда я открываю ее, жалуется на неработающие ссылки:

Я пытался использовать "Найти" для поиска [. как предложено в руководстве Microsoft. Я также пытался искать объекты, как предлагает руководство, и ничего не нашел.

Как мне найти эту проблемную неработающую ссылку?

Вы говорите о неработающих ссылках, но, насколько я могу судить, вы не говорите о неработающих ссылках. Что я упускаю?

Это может произойти из-за условного форматирования. Я исправил это, перейдя на каждую вкладку и нажав «Главная»> «Условное форматирование»> «Очистить правила»> «Очистить правила со всего листа». Затем вам нужно сохранить, закрыть и снова открыть.При повторном открытии ссылок больше не будет. Если у вас было какое-либо желаемое условное форматирование, вам нужно будет повторно применить его

14 ответов 14

Одно из мест, где эти ссылки могут «прятаться», — это именованные диапазоны. Excel только разрывает ссылки внутри формул. Чтобы исправить именованные диапазоны, перейдите в «Формулы» -> «Диспетчер имен» и посмотрите, есть ли там ссылки на внешние книги. Вы можете щелкнуть, удерживая клавишу Shift, чтобы выбрать группу и удалить их.

Обратите внимание, что страница MS, на которую вы ссылаетесь, также говорит об этом, но ваша ссылка ведет вниз по странице — вам придется прокрутить вверх, чтобы увидеть ссылки на имена.

У меня была точно такая же проблема, и я обнаружил еще одну неработающую ссылку, не упомянутую здесь, а именно проверку данных (иначе известную как раскрывающееся меню).

По аналогии с поиском объектов в службе поддержки Microsoft перейдите в раздел

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

При обновлении источника обязательно установите флажок

Применить эти изменения ко всем остальным ячейкам с такими же настройками

чтобы изменить все одинаковые источники.

Вот решение, которое оказалось для меня очень надежным:

Измените расширение файла .xlsx на .zip.
Разархивируйте этот ZIP-файл в новую папку
Используя Проводник, в этой папке найдите имя файла в файле CONTENTS.

Примечание. Иногда имя записывается в формате URL, например, %20 вместо пробелов, поэтому вы можете использовать упрощенную строку, которая встречается только в проблемном файле. Я использовал последнее слово имени файла + .xlsx .

Возможно, в результате будет возвращен листN.xml. Это файл, соответствующий проблемному листу, где N — номер проблемного листа в порядке их отображения. Откройте его в текстовом редакторе, например в Блокноте.

Найдите строку поиска и посмотрите на код вокруг нее; он должен указать вам на некоторые ссылки на ячейки, которые вы можете найти на проблемном листе.

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

Неработающие ссылки вызывают хаос в ваших таблицах? Не волнуйся! В этом руководстве вы узнаете о 3 простых способах поиска и исправления неработающих ссылок Excel, а также о нашем собственном решении в один клик в качестве дополнительного бонуса :)

Ячейки Excel часто могут ссылаться на другие книги, чтобы извлечь из них нужную информацию. Когда исходная книга удаляется, перемещается или повреждается, внешние ссылки на этот файл перестают работать, и ваши формулы начинают возвращать ошибки. Очевидно, что для исправления формул нужно найти битые ссылки. Вопрос в том, как? Ответы следуют ниже :)

Найти и исправить неработающие ссылки в Excel

Чтобы обнаружить неработающие ссылки на другие книги, выполните следующие действия:

Данные

Запросы и подключения

Если эта кнопка неактивна, это означает, что в вашей книге нет внешних ссылок.

Очевидно, что ссылки, диагностированные как Ошибка: источник не найден, не работают. В моей книге есть две такие ссылки:

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

Например, у нас были следующие пары ссылок на один и тот же файл: Colrado report.xlsx (с ошибкой) и Colorado report.xlsx (верно); Florida_report.xlsx (не существует) и Florida report.xlsx (верно). После исправления ссылок неправильные источники исчезли, и теперь у нас есть этот список:

Выявление и исправление неработающих ссылок с помощью функции «Найти и заменить»

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

Найти неработающие ссылки на всю книгу или определенную книгу

Внешние ссылки всегда указывают на другой файл Excel, в расширении имени которого есть ".xl", например .xls, .xlsx, .xlsm и т. д. Вы можете использовать этот факт при поиске ссылок на любые внешние файлы. рабочие тетради. Или вы можете искать определенный текст (подстроку) в определенном имени книги. Подробные шаги приведены ниже.

Нажмите Ctrl + F, чтобы открыть диалоговое окно Найти и заменить. Или нажмите Найти и выбрать >Найти… на вкладке Главная в группе Редактирование.
В диалоговом окне Найти и заменить нажмите кнопку Параметры.
В зависимости от того, хотите ли вы найти все внешние ссылки в книге или только ссылки на определенный файл, введите одно из следующих значений в поле Найти:
- Чтобы найти все ссылки, введите .xl.
- Чтобы найти ссылки на конкретную книгу, введите имя этой книги или ее уникальную часть.
В поле Внутри выберите "Книга" для поиска на всех вкладках или "Лист" для поиска на текущем листе.
В поле Искать выберите "Формулы".
Нажмите кнопку Найти все.

А теперь самое главное — анализ результатов.

Если вы искали ссылки на конкретную книгу, просто просмотрите результаты.

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

В списке результатов Найти все можно щелкнуть любой элемент, чтобы перейти к ячейке, содержащей ссылку, и отредактировать каждый из них по отдельности. Или вы можете использовать функцию Заменить Все, чтобы сразу исправить все вхождения недействительной ссылки. Вот как:

В диалоговом окне Найти и заменить перейдите на вкладку "Заменить".
В поле Найти введите неверное имя файла или путь.
В поле Заменить на введите правильное имя файла или путь.
Нажмите Заменить все.

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

В качестве примера заменим неправильное имя рабочей книги Colrado report.xlsx правильным Colorado report.xlsx. В этом конкретном случае замена всего одного слова (colrado) также будет работать. Однако имейте в виду, что указанный текст будет заменен в любом месте строки пути (полный путь к файлу отображается, если исходная книга в данный момент закрыта). Таким образом, чем меньше фрагмент текста вы вводите, тем выше вероятность ошибки.

Подобным образом вы можете заменить путь к исходному файлу. Например, если исходная книга изначально находилась в папке Документы, а затем вы переместили ее в подпапку Отчеты в той же папке, вы можете заменить \Documents \ с \Documents\Reports\.

Кто-то может сказать, что нелепо использовать Найти и заменить для решения проблемы с неработающими ссылками, но, насколько я знаю, это единственная встроенная функция, которая может помочь вам найти ячейки, содержащие неработающие ссылки.< /p>

Проверка неработающих ссылок с помощью VBA

Приведенный ниже код перебирает каждую внешнюю ссылку в книге и пытается выяснить, повреждена она или нет. Чтобы найти внешние файлы, мы используем метод LinkSources. Для выявления неработающих ссылок используется метод LinkInfo.

Список недействительных ссылок выводится на новом листе с именем Отчет о неработающих ссылках.Столбец B содержит гиперссылку на ячейку, содержащую ссылку.

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

Примечание. Этот код находит только ссылки на недопустимые книги (несуществующие, перемещенные или удаленные), но не на отсутствующие листы. Причина в том, что метод LinkInfo проверяет только имя файла. Попытка проверить имя листа приводит к ошибке 2015.

Находите неработающие ссылки в Excel одним щелчком мыши

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

Для пользователей Ultimate Suite мы предоставляем инструмент, позволяющий одним щелчком найти все внешние ссылки в книге или только неработающие ссылки. Просто нажмите кнопку Найти ссылки? на вкладке Инструменты Ablebits, и вы сразу же увидите список всех ссылок в текущей книге, где недействительные выделены светом. красный. Чтобы ограничить список неработающими ссылками, установите флажок Только нерабочие ссылки.

Нажав адрес ячейки на панели надстройки, вы перейдете к ячейке, содержащей определенную ссылку. Вот и все!

В отличие от приведенного выше кода VBA, надстройка находит все виды неработающих ссылок, в том числе те, в которых лист отсутствует или опечатан.

Вот как проверить неработающие ссылки в Excel. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!

В вашей книге Excel часто отображаются неработающие ссылки в Excel каждый раз, когда вы пытаетесь ее открыть?

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

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

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

Что такое неработающая ссылка Excel?

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

Чтобы исправить поврежденные файлы Excel, мы рекомендуем этот инструмент:

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

Загрузите средство восстановления файлов Excel с оценкой отлично от Softpedia, Softonic и CNET.
Выберите поврежденный файл Excel (XLS, XLSX) и нажмите «Восстановить», чтобы начать процесс восстановления.
Просмотрите восстановленные файлы и нажмите "Сохранить файл", чтобы сохранить файлы в нужном месте.

Что вызывает неработающие ссылки в Excel?

Важно:

Всегда помните, что связанные объекты полностью отличаются от гиперссылок. Таким образом, следующая процедура не поможет вам исправить неработающие гиперссылки Excel.

Что вызывает неработающие ссылки в Excel – главный вопрос, связанный с неработающими ссылками Excel.

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

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

Файлы/папки перемещены в другое место. Эта проблема с неработающей ссылкой Excel также возникает при перемещении местоположения связанного файла или папки.

Узнав о причинах неработающих ссылок в Excel, давайте перейдем к следующей теме, посвященной поиску неработающих ссылок в Excel.

Как найти неработающие ссылки в Excel?

Самый простой способ найти неработающие ссылки Excel – использовать метод Excel "Найти и заменить".

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

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

Следуйте приведенным ниже шагам, чтобы найти неработающие ссылки в Excel:

Прежде всего откройте лист Excel, на котором видны неработающие ссылки.
После этого нажмите Ctrl+F на клавиатуре. Откроется диалоговое окно «Найти и заменить».

В открывшемся диалоговом окне поиска и замены перейдите в поле «Найти». Здесь вы должны ввести соответствующее расширение файла, связанное с ячейкой.
Теперь в поле "внутри" выберите вариант "Рабочая книга".
После этого в поле «Искать» вы должны выбрать опцию «Формулы».
Наконец нажмите кнопку найти все.

После выполнения этих шагов вы легко получите неработающие ссылки Excel.

Теперь вам нужно заменить проблему со ссылкой на функциональную или исправить новую ссылку.

Также читайте: 6 способов исправить неработающую проблему поиска и замены в Excel

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

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

Вот как найти неработающие ссылки в Excel с помощью схемы взаимосвязей ячеек:

В строке меню выберите вкладку "Файлы" > "Параметры".
В открывшемся окне параметров Excel перейдите на вкладку "Надстройки".

Теперь в раскрывающемся списке выберите надстройку COM и нажмите кнопку ОК. В открывшемся диалоговом окне надстройки com выберите «Запросить».

После этого вы увидите, что вкладка "Запрос" будет добавлена на ленту Excel.

В группе диаграмм вы должны выбрать Cell Relationship.
На экране появится следующее окно диаграммы взаимосвязей ячеек.
Выберите нужные параметры в открывшемся диалоговом окне и нажмите кнопку ОК.
Очень скоро вы увидите, что диаграмма начнет появляться на экране вашего Excel.
Вы можете увеличить масштаб, чтобы увидеть связи между одной ячейкой и другой ячейкой или между одной ячейкой и другим рабочим листом.
Здесь вы увидите полную диаграмму взаимосвязей ячеек со всеми включенными ссылками. На этой диаграмме вы также можете проверить наличие неработающих ссылок в вашей книге Excel.

Я считаю этот метод лучшим способом найти ячейку с неработающими ссылками Excel.

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

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

Для использования кода необходимо добавить ссылку Microsoft XML V3:

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

Как исправить неработающие ссылки в Excel?

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

Откройте книгу Excel с неработающей ссылкой.
Перейдите на вкладку "Данные" и коснитесь параметра "Редактировать ссылки". Если вы не получите параметр «Редактировать ссылки», это означает, что ваша книга не содержит ссылок.
Теперь в поле "Исходный файл" выберите неработающую ссылку, которую нужно исправить.
Нажмите кнопку "Изменить источник".
Выполните поиск файла Excel, содержащего источник связанных данных.
Теперь выберите новый исходный файл и нажмите кнопку "Изменить источник".
Нажмите, чтобы закрыть параметр.

Как удалить неработающую ссылку в Excel?

При разрыве любой ссылки в Excel все формулы, относящиеся к этому конкретному исходному файлу, автоматически преобразуются в свое текущее значение. Предположим, если вы использовали формулу =СУММ([Бюджет.xls]Годовой!C10:C25) и ваш результат равен 45, ваша формула будет преобразована в 45 после разрыва связи.

Выполните следующие действия, чтобы удалить неработающие ссылки в Excel:

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

Подведение итогов:

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

Но если вы не найдете исходный файл, немедленно остановите Excel, чтобы обновить ссылку и навсегда удалить эту ссылку.

Маргрет

Маргрет Артур – предприниматель и эксперт по контент-маркетингу. Она ведет технические блоги и делится знаниями по MS Office, Excel и другим техническим дисциплинам. Ее самобытное искусство представления технической информации простым для понимания языком очень впечатляет. Когда она не пишет, она любит незапланированные путешествия.

Читайте также: