У меня есть книга Excel, которая жалуется на неработающие ссылки каждый раз, когда я ее открываю:
Я пытался использовать «Найти» для поиска [. как предложено в руководстве Microsoft. Я также попытался найти объекты, как подсказывает гид, и ничего не нашел.
Как я могу найти эту неприятную сломанную ссылку?
Одно место, в котором эти ссылки могут "спрятаться", это именованные диапазоны Excel только ломает ссылки внутри формул. Чтобы исправить именованные диапазоны, перейдите в Formulas -> Name Manager и посмотрите, есть ли там ссылки на внешние рабочие книги. Вы можете нажать Shift, чтобы выбрать группу и удалить их.
У меня была именно эта проблема, и я обнаружил еще одно неработающее местоположение ссылки, которое здесь не упоминалось: Проверка данных (иначе называемая выпадающим меню).
Аналогично поиску "Объектов" в поддержке Microsoft, перейдите в «Главная (вкладка) -> Редактирование (группа) -> Найти и выбрать -> Перейти к специальному ... -> Проверка данных», чтобы найти все ячейки с проверкой данных на рабочий лист. Затем вам нужно будет более или менее проверять ячейки проверки данных по очереди, что вы делаете, щелкая одну из ячеек проверки данных, затем перейдя в «Данные (вкладка) -> Инструменты данных (группа) -> Проверка данных -> Настройки -> Источник. " При обновлении источника обязательно установите флажок "Применить эти изменения ко всем другим ячейкам с такими же настройками", чтобы изменить все источники, которые совпадают.
Вот решение, которое было очень надежным для меня:
.xlsx на .zipПримечание. Иногда имя пишется в формате URL, например, %20 вместо пробелов, поэтому вы можете использовать упрощенную строку, которая находится только в проблемном файле. Я использовал последнее слово имени файла + .xlsx .
В результате он, вероятно, вернет sheetN.xml . Это файл, соответствующий вашему проблемному листу, где N - номер проблемного листа в порядке их отображения. Откройте его в текстовом редакторе, таком как Блокнот.
Найдите строку поиска и посмотрите на код вокруг нее; он должен указывать на некоторые ссылки на ячейки, которые вы можете найти на проблемном листе.
Примечание. Как указывалось ранее, оно также может быть в условном форматировании или проверке данных.
После проверки объектов, формул, имен, названий диаграмм и рядов данных я обнаружил, что моя внешняя ссылка была в "условном форматировании". Однако ни в одной из областей или условий не было ссылки на другую рабочую книгу, такую как [Budget.xls]. Только после этого я смог успешно разорвать связь в группе "Соединения". Надеюсь, это поможет!
Это, вероятно, проще, чем это. Период является концом предложения в помощь. Вы должны просто искать открытую квадратную скобку. Другими словами, ищите:
[
не для
[.
в формулах. Последний найдет только квадратную скобку, за которой следует точка. Excel найти не делает регулярных выражений.
я обнаружил, что запись "Dan G" была моей проблемой. Ячейка проверки со ссылкой на другой лист. Однако мне пришлось пройти через несколько сотен, и я обнаружил, что метод поиска Дэна Г работал эффективно, но с небольшим поворотом.
Найдите действительную ячейку проверки, выберите «Главная (вкладка) -> Редактирование (группа) -> Найти и выбрать -> Перейти к специальным ... -> Проверка данных», но установите переключатель "То же" в разделе "Проверка данных", чтобы найти все ячейки с одинаковой проверкой данных на листе.
Тот, который вызывает у вас проблему, должен выделяться, так как он не будет выделен / выбран.
У меня были похожие проблемы с внешними ссылками в Excel 2016. Я пробовал различные методы, но, в конце концов, это удалось с помощью вкладки проверки данных на вкладке данных. Я нажал "Круг неверных данных" и сразу заметил, что два выпадающих меню указывали на лист, откуда я скопировал данные. После поиска проблемы, я сразу же смог обновить данные в рабочей книге. Задача решена...
Чтобы подвести итог (и добавить ответ, который я только что наткнулся), посмотрите на:
На любом листе последние три могут быть легко достигнуты F5 -> «Special ...»
Сохраните, закройте и снова откройте файл, чтобы обновить список "Редактировать ссылку", в противном случае ссылки могут появляться, даже если источник был успешно удален.
Метод грубой силы будет делать копию рабочей книги, удалять все подозрительные объекты, кнопки и даже рабочие листы по одному и видеть, когда окно "Редактировать ссылку" показывает, что проблемной ссылки больше нет.
Для Excel 2010 или более поздней версии выполните следующие действия.
В разделе Общие,
NB. Когда флажок «Запросить обновление автоматических ссылок» снят, ссылки автоматически обновляются. И такого сообщения не появляется. Этот параметр применяется только к текущему пользователю и влияет на каждую рабочую книгу, которую открывает текущий пользователь, если на других пользователей тех же рабочих книг это не влияет.
Другой способ - обновить ссылки с помощью команд, выполните следующие действия.
В Excel 2010 или выше
Нажмите «Изменить ссылки» в группе «Подключения» на вкладке «Данные».
Нажмите Обновить значения и нажмите Закрыть.
Надеюсь, это поможет вам.
Я написал быстрый макрос, чтобы найти правило проверки, которое ссылается на электронную таблицу Excel:
Public Sub FindExtValidation()
Set s1 = ActiveSheet
Set v1 = s1.Cells.SpecialCells(xlCellTypeAllValidation)
For i = 1 To s1.UsedRange.Rows.Count
For j = 1 To s1.UsedRange.Columns.Count
If Not Intersect(v1, s1.Cells(i, j)) Is Nothing Then
If InStr(s1.Cells(i, j).Validation.Formula1, ".xl") > 0 Then
s1.Cells(i, j).Activate
Exit Sub
End If
End If
valid_error:
Next
Next
End Sub
Это терпит неудачу, если текущий лист не имеет никакой проверки вообще. Не уверен, как это исправить, на самом деле не все равно, чтобы попробовать.
У меня был файл Excel, который при открытии отображал сообщение об отсутствующей внешней ссылке. Такой ссылки найти не удалось, и различные инструменты - Kutools, FormulaDesk и т.д. Ничего не нашли. Наконец, я решил проблему, открыв файл xlsx как zip+xml и удалив папку, связанную с внешними ссылками (если вам нужна дополнительная информация - спросите меня).
Я обнаружил, что мои ссылки зомби были спрятаны в формулах условного форматирования. Как только я нашел и удалил все, я смог разорвать ссылки.
Проверьте следующую ссылку, чтобы найти больше способов найти неработающие ссылки:https://support.office.com/en-us/article/find-external-references-links-in-a-workbook-fcbf4576-3aab-4029-ba25 -54313a532ff1 # bmfind_external_references_used_in_name
ТАКЖЕ, неработающие ссылки могут скрываться в правилах условного форматирования.