- Измените тип файла на «xls» (Microsoft Excel 5.0/95 Workbook)
- Нажмите "Сохранить".
- Измените тип файла на xlsx
- Нажмите "Сохранить".
Это вполне может решить оставшиеся проблемы.
У этого метода может быть обратная сторона. Сохранение файла как «xls», который был создан как «xlsx», может привести к удалению некоторых функций из файла. Вы получите предупреждение, если есть какие-либо функции, несовместимые с форматом «xls», поэтому сначала проверьте это. Если есть какие-либо проблемы, ваша резервная копия должна вас выручить.
34 комментария на «Excel не может разорвать ссылку — полное руководство»
Большое спасибо!
Я создал кучу отфильтрованных сводок из моей основной сводки, но продолжал получать ошибку связи, когда сохранял их по отдельности. Я не могу их так распространять, и я не мог понять, где связь.
Следовал вашим инструкциям, и очистка именованных диапазонов сработала как по маслу.
Вы великолепны, сэр, спасибо за публикацию!
нет. 5 действительно круто…
Спасибо за все советы.
Однако у меня все еще была эта «фантомная» ссылка.
Наконец-то я сохранил файл с пониженным расширением с .xlsx в .xls.
Потом при повторном открытии ссылки пропали.
Возможно, это может быть полезно.
Спасибо, Джек! Это сработало, когда ничего другого, опубликованного здесь, не сработало!
Я поддерживаю этот подход. Все вышеперечисленные трюки работают в большинстве случаев, но для этой дополнительной липкой ссылки это единственный другой подход, помимо возможного трюка VBA.
Это единственное, что помогло, спасибо!!
Спасибо, Джек. Я включил этот совет в пост.
Ура,
Майк
и еще одна проблема — условное форматирование, которое может содержать формулы, связанные с другой книгой.
ДА». Это была моя точная проблема. Это сводило меня с ума. Спасибо!
ВОСХИТИТЕЛЬНО!! Большое спасибо за работу!
спасибо, что zip-наконечник помог, вы — Йода среди хаков Excel
Это здорово. Я знал, что нужно сделать все, кроме переименования в zip-файл и удаления папки с внешними ссылками. Большое спасибо!
У меня была ссылка, которая не сломается, несмотря ни на что. Я перепробовал все советы, даже те, которые предложил последний комментатор (Роджер). Ничего из этого не помогло удалить ссылку. Возвращаясь к историческим версиям, я смог выделить две вкладки в файле с проверкой данных.
В конце концов мне пришлось удалить проверку данных на обеих вкладках, а затем перейти к концу данных на каждой вкладке, начать с первой пустой строки после данных, выделить следующие несколько, используя SHIFT+ВНИЗ, а затем удалить эти строки.
Когда я это сделал, ссылка волшебным образом исчезла навсегда!
Итак, каким-то образом именно комбинация удаления всех проверок данных и пустых строк под данными привела к исчезновению ссылки.
Путем проб и ошибок я понял, что ссылка не исчезнет, если вы СНАЧАЛА не удалите проверку данных на обеих вкладках, а затем удалите пустые строки данных на каждой вкладке. Ссылка не исчезла, пока я не удалил пустые строки во 2-й вкладке.
Ничего из перечисленного мне не помогло. В итоге он был скрыт при проверке данных. Вероятно, то же самое может произойти с условным форматированием, но его легче найти. Для проверки вы начинаете с: Перейти к специальной проверке данных Все. чтобы увидеть, существуют ли они на этом листе. если это так, вам придется проверить каждую ячейку, выбрав ее и выбрав «Данные» -> «Проверка данных» и увидев, что проверка ссылается на внешнюю ячейку. Также можно выбрать множество ячеек, а затем «Данные» -> «Проверка данных», он сообщит вам, если у одной есть другие настройки, чем у остальной части проверки данных, что может быть полезно для поиска виновника.
Спасибо, Юрий, у меня ничего не вышло, включая (на удивление) трюк с zip-файлом. Ваш комментарий об условном форматировании был правильным — на нескольких вкладках условное форматирование было связано в другом месте……
нажмите [Главная вкладка], [Условное форматирование], [Управление правилами], в раскрывающемся списке вверху [Показать правила форматирования для:] выберите каждую из ваших вкладок по очереди и найдите все, что ссылается на внешний лист в либо правило, либо столбцы «применяется к».
После того, как я сделал это и удалил внешние ссылки, я смог удалить ссылку обычным способом с вкладки [Данные] и кнопки [Редактировать ссылки].
Спасибо. Лучший форум для решения этой проблемы. Я уже несколько месяцев ищу решение.
Большое спасибо! Я перепробовал буквально все, включая устранение проблем с условным форматированием/проверкой данных, но ничего не работало… пока я не попробовал трюк с переименованием в .zip. Больше никаких надоедливых и необъяснимых сообщений!
Джек, ваше предложение было правильным решением – ни один из 5 пунктов не сработал, но сохранение файла в формате .XLS сработало – отлично.
Мне ни один из советов статьи не помог (хотя часто они кажутся полезными!).
Но спасибо, Джек, понижение версии файла до .xls и обратно до .xlsx помогло мне!
Это… просто… потрясающе! МОЙ БОГ. Кто знал, что сжатие файла обнажает некоторые его внутренности. Я навсегда унижен.
Огромное спасибо за это — мне тоже пришлось использовать метод переименования в zip (я слышал о переименовании файлов в zip, но никогда не знал, что это бесполезно…). Вытащил меня из глубокой ямы, большое спасибо!!
Спасибо за это! Я сходил с ума из-за ссылки, которую не мог сломать. Мне никогда не приходило в голову, что на него можно ссылаться в условном форматировании.
Отлично. Вы мне очень помогли.
Это потрясающе. У меня были ссылки на внешние книги в условном форматировании. Теперь все исправлено.
Отслеживание всех внешних ссылок в книге может быть сложной задачей. Этот учебник научит вас нескольким полезным методам поиска ссылок на внешние источники в формулах, объектах и диаграммах Excel и покажет, как разорвать внешние ссылки.
Если вы хотите извлечь данные из одного файла в другой, самый быстрый способ — обратиться к исходной книге. Такие внешние ссылки или внешние ссылки — очень распространенная практика в Excel.Однако после выполнения определенной задачи вы можете захотеть найти и, возможно, разорвать эти ссылки. Удивительно, но не существует быстрого способа одновременно найти все ссылки в книге. В зависимости от того, где именно находятся ссылки — в формулах, определенных именах, объектах или диаграммах — вам придется использовать разные методы.
Как найти ячейки с внешними ссылками в Excel
- На листе нажмите Ctrl + F, чтобы открыть диалоговое окно Найти и заменить.
- Нажмите кнопку Параметры.
- В поле Найти введите .xl. Таким образом, вы будете искать все возможные форматы файлов Excel, включая .xls (старые книги), .xlsx (современные книги), .xlsm (книги с поддержкой макросов) и т. д.
- В поле Внутри выберите "Книга" для поиска на всех вкладках или "Лист" для поиска только на текущем листе.
- В поле Искать выберите "Формулы".
- Нажмите кнопку Найти все.
Вот оно! У вас есть список ячеек, в которых есть внешние ссылки.
И эти полезные советы помогут вам управлять результатами:
- Чтобы выбрать ячейку, содержащую внешнюю ссылку, щелкните адрес ячейки в поле Ячейка
.
- Чтобы сгруппировать найденные ссылки нужным образом, щелкните заголовок соответствующего столбца, например Лист или Формула.
- Чтобы выбрать все ячейки с внешними ссылками, поместите курсор в любом месте результатов и нажмите Ctrl + A . При этом будут выбраны как результаты в диалоговом окне Найти и заменить, так и ячейки в книге.
Примечание. С помощью Найти и заменить вы можете идентифицировать только внешние ссылки в ячейках. Если вы удалили все внешние ссылки из формул, но Excel по-прежнему говорит, что есть ссылки на внешние книги, проверьте другие возможные места, описанные ниже.
Как найти ссылки в именованных диапазонах Excel (определенные имена)
Профессионалы в Excel часто называют диапазоны и отдельные ячейки, чтобы их формулы было легче писать, читать и понимать. Раскрывающиеся списки проверки данных также легче создавать с именованными диапазонами, которые, в свою очередь, могут ссылаться на внешние данные. Чтобы позаботиться о таких случаях, проверьте наличие внешних ссылок в именах Excel:
- На вкладке Формулы в группе Определенные имена щелкните Диспетчер имен или нажмите комбинацию клавиш Ctrl + F3.
- В списке имен проверьте наличие внешних ссылок в столбце Refers To. Ссылки на другие книги заключены в квадратные скобки, например [Source_data.xlsx].
Как определить внешние ссылки в объектах Excel
Если вы связали такие объекты, как фигуры, текстовые поля, WordArt и т. п., с другими файлами Excel, вы можете использовать функцию Перейти к специальному, чтобы найти такие ссылки:
- На вкладке Главная в группе Форматы нажмите Найти и выбрать Перейти к специальному. Или нажмите F5, чтобы открыть диалоговое окно Перейти, а затем нажмите Специальные… .
- В диалоговом окне Перейти к специальному выберите Объекты и нажмите ОК. При этом будут выбраны все объекты на активном листе.
Если объект связан с определенной ячейкой, вы можете увидеть внешнюю ссылку в строке формул:
Если объект связан с файлом, наведите указатель мыши на объект, чтобы увидеть, куда он указывает:
Примечание. Если объект связан со всем файлом, а не с отдельной ячейкой, такую связь нельзя разорвать с помощью функции «Редактировать ссылки». Чтобы удалить ссылку, щелкните объект правой кнопкой мыши и выберите Удалить ссылку в контекстном меню.
Как найти ссылки на другие файлы в диаграммах Excel
Если в заголовке диаграммы или ряду данных используются внешние ссылки, их можно найти следующим образом:
- На диаграмме нажмите на название диаграммы или ряд данных, которые вы хотите проверить.
- В строке формул найдите ссылку на другой файл Excel.
Внешняя ссылка в заголовке диаграммы:
Внешняя ссылка в серии данных диаграммы:
Если ваша диаграмма содержит несколько рядов данных, вы можете быстро перемещаться между ними следующим образом:
- Выберите целевую диаграмму.
- Перейдите на вкладку Формат в группу Текущий выбор, щелкните стрелку рядом с полем Элементы диаграммы и выберите ряд данных интерес.
Как найти внешние ссылки в сводных таблицах
Сводная таблица чаще всего создается с использованием данных из той же книги. Но иногда исходные данные находятся во внешнем файле. Чтобы найти точное расположение исходных данных сводной таблицы, выполните следующие действия:
- Нажмите любую ячейку в сводной таблице.
- На вкладке Анализ сводной таблицы в группе Данные нажмите кнопку Изменить источник данных.
Как включить ссылки на внешние книги в Excel
Когда вы впервые открываете книгу со ссылками на другие файлы, Excel показывает предупреждение системы безопасности, информирующее вас о том, что файл содержит ссылки на внешние данные. Чтобы разрешить обновление ссылок, просто нажмите кнопку «Включить содержимое».
При последующем открытии того же файла вам будет предложено следующее приглашение с вопросом, хотите ли вы обновить ссылки. Если вы доверяете связанным документам и хотите получить последние данные, нажмите «Обновить».
Управление запросом безопасности об обновлении ссылок
По умолчанию Excel спрашивает, следует ли обновлять внешние ссылки каждый раз, когда вы открываете книгу. Однако вы можете контролировать, будет ли отображаться сообщение и будут ли обновляться ссылки.
- На вкладке Данные в группе Запросы и подключения нажмите Редактировать ссылки.
- В левом нижнем углу диалогового окна Редактировать ссылки нажмите Приглашение при запуске…
- Разрешить пользователям выбирать, отображать оповещение или нет (по умолчанию).
- Не отображать предупреждение и не обновлять автоматические ссылки. Этот параметр имеет смысл выбирать, если вы предоставляете доступ к книге другим людям, у которых нет доступа к исходным файлам.
- Не показывать ссылки для оповещений и обновлений — этот параметр можно выбрать, если вы полностью доверяете источникам.
Изменить настройки безопасности для внешних ссылок
Вы также можете настроить автоматическое обновление ссылок на другие файлы в определенной книге без предупреждения системы безопасности, изменив настройки безопасности Центра управления безопасностью:
- В целевой книге откройте вкладку Файл Параметры.
- В диалоговом окне Параметры Excel нажмите Центр управления безопасностью >Настройки центра управления безопасностью.
Обратите внимание, что автоматическое обновление ссылок на неизвестные файлы может быть вредным и поэтому не рекомендуется. Включайте его только тогда, когда вы на 100% уверены в безопасности внешних данных. Или временно включите этот параметр, а затем вернитесь к параметру по умолчанию Запрашивать пользователя при автоматическом обновлении ссылок на книги.
Примечание. Независимо от того, какой вариант вы выберете, Excel по-прежнему будет отображать приведенное ниже приглашение, если книга содержит недействительные или неработающие ссылки.
Как разорвать внешние ссылки в Excel
В Excel разрыв ссылки на другую книгу означает замену внешней ссылки ее текущим значением.
Например, если вы разорвете следующую внешнюю ссылку, она будет заменена значением, которое в данный момент находится в ячейке A1 на листе Янв в рабочей книге Исходные данные. :
Если вы разорвете внешнюю ссылку в приведенной ниже формуле, формула будет изменена на расчетное значение, каким бы оно ни было:
Примечание. Поскольку разрыв ссылок — это действие, которое нельзя отменить, может быть целесообразно сначала сохранить резервную копию книги.
Чтобы разорвать внешние ссылки в Excel, вам нужно сделать следующее:
На вкладке Данные в группе Запросы и подключения нажмите кнопку Изменить ссылки.
- Чтобы выбрать несколько ссылок, нажмите каждую из них по отдельности, удерживая нажатой клавишу Ctrl.
- Чтобы выбрать все ссылки, нажмите сочетание клавиш Ctrl + A .
Примечание. В идеальных условиях эта функция должна удалять все внешние ссылки в книге. К сожалению, мы живем не в идеальном мире :( Некоторые ссылки на внешние данные, например внешние исходные данные в сводных таблицах, не отображаются в диалоговом окне Редактировать ссылки, а другие нельзя разорвать. Если < Кнопка em>Редактировать ссылки неактивна в вашей книге, но вы все еще получаете подсказку о внешних данных, тогда вам придется проверить все возможные места, где могут скрываться внешние ссылки (например, объекты, диаграммы и т. д. ) и измените или удалите ссылки вручную.
Получить список всех внешних ссылок в книге
Чтобы получить список всех внешних источников, на которые ссылается ваша книга, вы можете использовать один из следующих способов.
Традиционный подход
Отобразится следующая информация:
- Источник – имя связанного файла.
- Тип – тип ссылки: книга или лист.
- Обновить — будет ли ссылка обновляться автоматически или вручную.
- Статус – статус ссылки, например ОК, Источник открыт, Предупреждение, Неизвестно и т. д. Чтобы получить самую свежую информацию, нажмите кнопку Проверить статус справа.
Очень быстро и просто, хотя этот метод не очень удобен. Чтобы увидеть расположение исходного файла, нужно щелкнуть каждую ссылку по одной за раз.
Динамические массивы и макросы Excel 4.0.
Очень милый трюк, предложенный Бобом Ульмасом в его книге "Это не Excel, это Magic!" может помочь вам получить расположение всех исходных файлов за один раз. В решении сочетаются недавно представленные динамические массивы со старыми добрыми макросами Excel 4.0.
Чтобы создать список всех внешних ссылок в данной книге, вам нужно сделать следующее:
Шаг 1. Создайте новое имя, которое ссылается на макрос
Чтобы использовать встроенный макрос Excel 4.0 в формуле, необходимо создать имя, ссылающееся на макрос. Вот как:
- На вкладке Формулы в группе Определенные имена нажмите Диспетчер имен. Или просто нажмите сочетание клавиш Ctrl + F3.
- В диалоговом окне Диспетчер имен нажмите кнопку Создать…
- В диалоговом окне Новое имя введите осмысленное имя, например GetLinks, в поле Имя и следующую формулу в поле < em>Ссылается на поле: =LINKS()
- Нажмите ОК.
Более подробные инструкции см. в разделе Как создать имя в Excel.
Шаг 2. Используйте только что созданное имя в формуле
Теперь, когда у вас есть имя, которое ссылается на макрос, вам просто нужно поместить это имя в формулу. В зависимости от версии Excel формула может иметь разный вид.
В Excel 365:
В самую верхнюю ячейку целевого диапазона введите следующую формулу:
GetLinks (или любое другое имя, которое вы использовали для ссылки на макрос) возвращает горизонтальный диапазон всех внешних ссылок в книге. Функция ТРАНСП превращает строки в столбцы и выводит вертикальный список, который легче читать.
Чтобы упорядочить список в алфавитном порядке, поместите приведенную выше формулу в функцию СОРТИРОВКИ:
Помните, что это решение работает только в Excel 365 с новым механизмом вычислений, поддерживающим динамические массивы.
В Excel 2019–2007:
В преддинамических версиях Excel используйте имя GetLinks для аргумента массив классической функции ИНДЕКС. Чтобы сделать решение более удобным для пользователя, вы можете обернуть конструкцию в ЕСЛИОШИБКА, чтобы позаботиться о ситуациях, когда формула копируется в большее количество ячеек, чем внешних ссылок в вашей книге:
Формула помещается в первую ячейку (A2), а затем вы перетаскиваете ее в следующие ячейки:
Важные примечания:
- Поскольку в этом решении используются макросы, файл необходимо сохранить как книгу с поддержкой макросов (.xlsm).
- Макросы Excel не выполняются и не обновляются автоматически. Чтобы обновить список ссылок, нажмите сочетание клавиш Ctrl + Alt + F9, при этом будут пересчитаны все формулы во всех открытых книгах.
Макрос VBA для получения списка внешних ссылок
Если вы ничего не имеете против использования макросов в своих рабочих листах, следующий код VBA может автоматически найти и перечислить все ссылки на внешние источники в рабочей книге:
Чтобы добавить код в книгу, выполните следующие действия:
- Нажмите клавиши ALT + F11, чтобы открыть редактор Visual Basic.
- На левой панели щелкните правой кнопкой мыши Эта книга и выберите Вставить >Модуль.
- Вставьте приведенный выше код в окно Код.
Подробные инструкции см. в разделе Как вставить код VBA в Excel.
Чтобы запустить макрос, нажмите клавиши Alt + F8 в книге или F5 в редакторе VBA.
Дополнительную информацию см. в разделе Как запустить макрос в Excel.
В результате вы получите список внешних источников на новом листе:
Поиск ячеек с внешними ссылками с помощью VBA
Результаты выводятся на новый лист с именем Отчет по всем ссылкам. Столбец B содержит гиперссылки на ячейки с внешними ссылками.
Чтобы сразу же воспользоваться кодом, вы можете загрузить образец нашей рабочей тетради в конце публикации. Книга содержит приведенный выше код, а также подробные пошаговые инструкции по его запуску.
Найти все внешние ссылки в книге одним щелчком мыши
Прочитав предыдущие примеры, возможно, вы задавались вопросом, почему простые вещи нужно делать такими сложными. Мы тоже задали себе этот вопрос... и реализовали решение этой задачи в один клик.
Если в Excel установлен Ultimate Suite, для поиска всех ссылок в книге достаточно одного нажатия кнопки «Найти ссылки»:
По умолчанию инструмент ищет все ссылки: внутренние, внешние и веб-страницы. Чтобы отображались только внешние ссылки, выберите этот параметр в раскрывающемся списке и нажмите кнопку Обновить.
Чтобы показывать только неработающие ссылки, просто поставьте галочку в соответствующем поле.
Чтобы перейти к ячейке, которая ссылается на внешние данные, щелкните адрес ячейки на панели.
Простые вещи должны быть простыми! :)
Вот как найти ссылки на внешние источники в Excel. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!
Блог, ориентированный в основном на Microsoft Excel, PowerPoint и Word, со статьями, призванными вывести ваши навыки анализа данных и работы с электронными таблицами на новый уровень. Научитесь всему: от создания информационных панелей до автоматизации задач с помощью кода VBA!
Как сломать внешние ссылки
Итак, вы хотите удалить внешние ссылки из своей книги Excel? Кажется, что это должно быть легко, но, как вы, вероятно, заметили, иногда это не так просто, как нажать кнопку "разорвать ссылки" (к сожалению).
В этом посте я расскажу вам обо всех тайниках, в которых могут скрываться внешние ссылки.
Удаление внешних ссылок из ячеек
Внешние ссылки в ячейках обычно проще всего найти и удалить. Вы всегда должны начинать с использования диалогового окна «Редактировать ссылки». Вы можете перейти к этому диалоговому окну, выполнив следующие действия:
Вкладка «Данные» >> Группа «Запросы и подключения» >> Кнопка «Изменить ссылки»
Когда вы увидите диалоговое окно «Редактировать ссылки», вы увидите список всех внешних файлов Excel, из которых извлекаются данные. Чтобы удалить/разорвать связь, просто выберите строки, которые вы хотите удалить, и нажмите кнопку "Разорвать связь".
Вы получите запрос (показан ниже) с вопросом, уверены ли вы, что хотите разорвать ссылки, поскольку это действие необратимо.
Нажмите «Разорвать ссылки», и все ваши ссылки «должны» быть разорваны. В идеале кнопка «Редактировать ссылки» будет неактивна, а все ваши внешние ссылки будут удалены.
Однако это далеко не идеальный мир! Иногда определенные ссылки нельзя разорвать с помощью диалогового окна «Редактировать ссылки». В других случаях вы все равно будете получать сообщения о том, что в вашей книге есть внешние ссылки. Если вы все еще думаете, что в вашей книге есть внешние ссылки, продолжайте читать, чтобы узнать, где еще могут скрываться эти надоедливые ссылки.
Удаление внешних ссылок из диаграмм
Внешние ссылки могут находиться внутри любого текстового поля объекта диаграммы. Сюда входят:
Удаление внешних ссылок из фигур
Если у вас есть фигуры со связанными с ними формулами, возможно, формулы имеют внешние ссылки. Вы можете легко это проверить, щелкнув нужную фигуру и просмотрев содержимое строки формул.
Если вам нужно просмотреть много фигур, выполните следующие действия, чтобы быстро просмотреть все фигуры:
Нажмите клавишу F5, чтобы открыть диалоговое окно "Перейти к"
Нажмите "Специальные"
Отметить только объекты
Нажмите "ОК".
Теперь у вас будут выбраны все фигуры в электронной таблице. Чтобы просмотреть каждую фигуру, просто нажмите клавишу TAB и следите за строкой формул, чтобы не пропустить формулу, которая может появиться.
Удаление внешних ссылок из именованных диапазонов
Удаление внешних ссылок из сводных таблиц
Исходные данные для сводной таблицы можно связать с внешним файлом. Выполните следующие действия, чтобы проверить подключение к исходным данным сводной таблицы.
Выберите ячейку в сводной таблице
Перейдите на вкладку Анализ инструментов сводной таблицы
Нажмите кнопку "Изменить источник данных"
Загляните в диалоговое окно "Изменить источник данных сводной таблицы" и убедитесь, что ваши данные не связаны извне
Удаление внешних ссылок из правил проверки данных
Внешние ссылки могут находиться в правилах проверки данных. Это может произойти в качестве входных данных источника для правила списка. Вы можете вручную выполнить поиск по каждому из правил проверки данных в своей книге, однако это может оказаться непростой задачей, если у вас много вкладок для поиска. Более простой способ — использовать средство проверки совместимости для поиска вас.
Использование средства проверки совместимости для поиска ошибок проверки данных:
Выберите вкладку "Файл"
В разделе "Информация" выберите раскрывающийся список "Проверить наличие проблем"
Выберите Проверить совместимость
В диалоговом окне "Проверка совместимости" нажмите кнопку "Копировать на новый лист"
Вы должны увидеть новый лист со всеми перечисленными проблемами. Используйте сочетание клавиш Ctrl + F, чтобы открыть диалоговое окно «Найти» и выполнить поиск экземпляров «проверки данных»
Если у вас есть какие-либо внешние ссылки или ошибки в ваших правилах проверки данных, вы найдете разделы на листе с гиперссылками, ведущими к ячейкам с помеченной проверкой данных
Нажимайте на каждую гиперссылку и проверяйте правило проверки данных, когда диапазон ячеек все еще выделен
Если вы видите какие-либо внешние ссылки в поле "Источник", вы, скорее всего, захотите нажать кнопку "Очистить все", чтобы избавиться от внешней ссылки
Любые другие области?
Обнаружили ли вы какие-либо другие области в ваших электронных таблицах, где прятались внешние ссылки? Дайте мне знать в комментариях, и я буду обновлять эту статью, чтобы у нас был хороший исчерпывающий список!
Читайте также:
