Разрыв ссылок в Excel

Обновлено: 03.07.2024

Как разорвать все внешние ссылки в Excel?

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

• Повторное использование всего: добавляйте наиболее часто используемые или сложные формулы, диаграммы и другие объекты в избранное и быстро используйте их повторно в будущем.
• Более 20 текстовых функций: извлечение числа из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
• Инструменты объединения: несколько книг и листов в одну; Объединение нескольких ячеек/строк/столбцов без потери данных; Объединить повторяющиеся строки и суммировать.
• Инструменты разделения: разделение данных на несколько листов в зависимости от значения; Одна рабочая книга для нескольких файлов Excel, PDF или CSV; Один столбец в несколько столбцов.
• Вставить, пропуская скрытые/отфильтрованные строки; Подсчет и сумма по цвету фона; Массовая рассылка персонализированных электронных писем нескольким получателям.
• Суперфильтр: создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировка по неделям, дням, частоте и т. д.; Фильтр по полужирному шрифту, формулам, комментариям.
• Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.

Разорвать все внешние ссылки с помощью кода VBA

Потрясающе! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!

Сэкономьте 50 % своего времени и сократите количество кликов мышью каждый день!

Если вы хотите сломать все внешние ссылки, как ссылки ok, так и ссылки error, следующий код VBA поможет вам сломать их все.

<р>1. Удерживая нажатыми клавиши ALT + F11, откроется окно Microsoft Visual Basic для приложений.

<р>2. Нажмите «Вставить» > «Модуль» и вставьте следующий код в окно модуля.

<р>3. Затем нажмите клавишу F5, чтобы запустить код, и все внешние ссылки во всей книге не работают.

Разбить все / сломанные / указанные внешние ссылки с помощью Kutools for Excel

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

С помощью Kutools for Excel Функция «Найти и сломать неработающие ссылки» вы можете сломать все ссылки, неработающие ссылки, ссылки в порядке и указанные ссылки на источники ссылок.

Kutools for Excel включает более 300 удобных инструментов Excel. Бесплатно попробовать без ограничений в течение 30 дней. Получить сейчас

После установки Kutools for Excel сделайте следующее:

<р>1. Нажмите Kutools > Инструменты для ссылок > Найти и сломать неработающие ссылки, см. снимок экрана:

<р>2. В диалоговом окне «Найти и разорвать неработающие ссылки» все внешние ссылки на активном листе перечислены в списке. Вы можете выбрать тип ссылки, которую вы хотите разорвать, из выпадающего списка «Фильтр». Смотрите скриншоты:

<р>3. В этом случае я выбираю Все неработающие ссылки, и только неработающие ссылки будут перечислены в списке, см. снимок экрана:

<р>4. Затем нажмите кнопку «Разорвать ссылку», все неработающие ссылки будут выбраны и удалены с активного рабочего листа.

Примечание. Если флажок «Просмотр ячейки» установлен, при нажатии на ссылку в списке выполняется поиск и переход к конкретной ячейке, содержащей ссылку на листе.

Некоторые из этих проблем можно решить с помощью нашей надстройки: исправить и ускорить работу с файлами Excel® БЕСПЛАТНО.

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

1. Снять защиту с листов в проблемном файле

a) Когда активный лист защищен и вы пытаетесь редактировать ссылки, кнопка РАЗРЫВ ССЫЛКИ будет недоступна. Вам нужно будет снять защиту с этого листа или перейти к другому листу, прежде чем пытаться разорвать ссылки.

b) Если ячейка в защищенном листе связана с внешним файлом, вы не сможете разорвать ссылки. Excel® выдаст предупреждение о том, что внешняя ссылка не может быть нарушена, поскольку лист защищен.

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

Если вы используете Excel® 2010 или более позднюю версию, вы можете перейти в ФАЙЛ – ИНФОРМАЦИЯ. В разделе ЗАЩИТИТЬ РАБОЧУЮ КНИГУ вверху вы увидите список всех защищенных листов.

Рис.(a1)

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

2. Попытка разорвать ссылки

На этом шаге удастся разорвать ссылки, если с книгой нет «фантомных ссылок». Мы вернемся к ним позже.

Функция «Разорвать связь» заменяет внешние ссылки в любых формулах постоянными значениями.

Если вы хотите идентифицировать все ячейки со ссылками на внешние книги, прежде чем выполнять этот процесс, вы можете сделать это:

• Нажмите CTRL+F
• Введите «xl*]» в поле «Найти что:» (не ставьте кавычки в поле).
• Выберите «Внутри:» Книга.
• Выберите «Искать в:» формулы.
• Нажмите "Найти все".

Теперь вы можете проверить список ссылок, прежде чем продолжить.

Рис.(a3)

Чтобы разорвать ссылки, нажмите ЛЕНТУ ДАННЫХ – (подменю ПОДКЛЮЧЕНИЯ) ИЗМЕНИТЬ ССЫЛКИ

Вы увидите список внешних ссылок, на которые ссылается ваш текущий файл. Вы можете щелкнуть каждый из файлов, а затем нажать РАЗРЫВ ССЫЛКИ.

Если РАЗРЫВ ССЫЛКИ неактивен, вернитесь к шагу 1, так как в вашей книге все еще должен быть защищенный лист.

Рис.(a2)

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

3. Удалить именованные диапазоны во внешние файлы

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

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

Нажмите на ЛЕНТУ ФОРМУЛЫ – (ОПРЕДЕЛЕННЫЕ ИМЕНА) МЕНЕДЖЕР ИМЕН

Выберите каждый именованный диапазон, который ссылается на внешнюю книгу, и нажмите УДАЛИТЬ. Если есть много именованных диапазонов, вы можете отсортировать их по «Относится к». Это сгруппирует ссылки на один и тот же внешний файл.

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

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

Рис.(a4)

Вернитесь, чтобы проверить ИЗМЕНИТЬ ССЫЛКИ, чтобы узнать, решило ли это проблему с фантомной ссылкой. Если вы по-прежнему не можете разорвать ссылку, перейдите к следующему шагу.

4. Удалить внешние ссылки диаграммы

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

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

Если FILE-INFO сообщает, что все еще есть скрытые листы, возможно, в вашем файле есть листы, которые являются «очень скрытыми». Это маловероятно, если только вы или кто-то другой не установил для свойства листа Visible значение xlVeryHidden в редакторе Visual Basic. Нажмите ФАЙЛ – ПРОВЕРИТЬ НА ПРОБЛЕМЫ – ПРОВЕРИТЬ ДОКУМЕНТ, чтобы найти все скрытые листы. Вы можете либо щелкнуть, чтобы удалить листы. Или вы можете отобразить их в редакторе Visual Basic (нажмите ALT+F11). В VBE вы можете щелкнуть скрытый лист, а затем изменить его свойство visible на xlVisible.

На каждой диаграмме. Щелкните правой кнопкой мыши диаграмму, затем «ВЫБЕРИТЕ ДАННЫЕ». Нажмите на каждую серию, затем "ИЗМЕНИТЬ".

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

Рис. (b1)

Если вы уже пытались РАЗРЫВАТЬ ССЫЛКИ на этот файл, то вместо файла и диапазона (выше) вы получите только список значений (ниже).

Рис.(b3)

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

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

Рис.(b2)

Другие объекты, не относящиеся к диаграмме

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

4а. Удалите все внешние ссылки в условных форматах.

ОБНОВЛЕНИЕ: некоторые ссылки на внешние файлы могут быть скрыты в любой функции Excel, которая может ссылаться на диапазоны листов. Эти функции включают условные форматы. (Спасибо Роджеру, Юрию и другим в комментариях за эти дополнения)

Пройдитесь по каждому листу и выберите каждую ячейку. Нажмите CTRL+A

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

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

Если вы по-прежнему не можете разорвать ссылки, попробуйте следующее:

5. Сохраните копию проблемного файла.

Это последнее средство, если вы не можете разорвать ссылки в Excel®. Но это решит все оставшиеся проблемы с фантомными ссылками.

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

Этот метод обращается к файловой структуре книги для удаления данных ссылки.

1. Создайте копию файла, из которого вы пытаетесь удалить ссылки
2. В проводнике: щелкните правой кнопкой мыши этот новый файл и выберите ПЕРЕИМЕНОВАТЬ
3. Измените расширение с .xlsx на .zip.
4. Откройте эту папку ZIP и перейдите в папку «FILENAME.zip > xl».
5. Удалите папку с именем externalLinks.
6. Выйти из папки ZIP
7. Переименуйте папку ZIP с расширения .zip обратно на .xlsx.
8. Открыть этот файл

Когда файл открывается в Excel®, вас не должны просить обновить ссылки. Параметр ИЗМЕНИТЬ ССЫЛКИ должен быть недоступен.

Сравните этот новый файл с исходным, чтобы убедиться, что он работает правильно.

6. Измените тип файла на «xls», а затем снова на «xlsx».

Обновление: это добавлено благодаря комментарию Джека ниже. Спасибо, Джек.

Это крайняя мера. Понизьте версию файла.

1. Создайте резервную копию файла.
2. «Сохранить как» книгу.
   1. Измените тип файла на «xls» (Microsoft Excel 5.0/95 Workbook)
   2. Нажмите "Сохранить".
   1. Измените тип файла на xlsx
   2. Нажмите "Сохранить".

   Это вполне может решить оставшиеся проблемы.

   У этого метода может быть обратная сторона. Сохранение файла как «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

   1. На листе нажмите Ctrl + F, чтобы открыть диалоговое окно Найти и заменить.
   2. Нажмите кнопку Параметры.
   3. В поле Найти введите .xl. Таким образом, вы будете искать все возможные форматы файлов Excel, включая .xls (старые книги), .xlsx (современные книги), .xlsm (книги с поддержкой макросов) и т. д.
   4. В поле Внутри выберите "Книга" для поиска на всех вкладках или "Лист" для поиска только на текущем листе.
   5. В поле Искать выберите "Формулы".
   6. Нажмите кнопку Найти все.

   Вот оно! У вас есть список ячеек, в которых есть внешние ссылки.

   
   

   И эти полезные советы помогут вам управлять результатами:

   • Чтобы выбрать ячейку, содержащую внешнюю ссылку, щелкните адрес ячейки в поле Ячейка
   .
   • Чтобы сгруппировать найденные ссылки нужным образом, щелкните заголовок соответствующего столбца, например Лист или Формула.
   • Чтобы выбрать все ячейки с внешними ссылками, поместите курсор в любом месте результатов и нажмите Ctrl + A . При этом будут выбраны как результаты в диалоговом окне Найти и заменить, так и ячейки в книге.

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

   Как найти ссылки в именованных диапазонах Excel (определенные имена)

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

   1. На вкладке Формулы в группе Определенные имена щелкните Диспетчер имен или нажмите комбинацию клавиш Ctrl + F3.
   2. В списке имен проверьте наличие внешних ссылок в столбце Refers To. Ссылки на другие книги заключены в квадратные скобки, например [Source_data.xlsx].

   
   

   Как определить внешние ссылки в объектах Excel

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

   1. На вкладке Главная в группе Форматы нажмите Найти и выбрать Перейти к специальному. Или нажмите F5, чтобы открыть диалоговое окно Перейти, а затем нажмите Специальные… .
   2. В диалоговом окне Перейти к специальному выберите Объекты и нажмите ОК. При этом будут выбраны все объекты на активном листе.

   
   

   Если объект связан с определенной ячейкой, вы можете увидеть внешнюю ссылку в строке формул:

   
   

   Если объект связан с файлом, наведите указатель мыши на объект, чтобы увидеть, куда он указывает:

   
   

   Примечание. Если объект связан со всем файлом, а не с отдельной ячейкой, такую ​​связь нельзя разорвать с помощью функции «Редактировать ссылки». Чтобы удалить ссылку, щелкните объект правой кнопкой мыши и выберите Удалить ссылку в контекстном меню.

   Как найти ссылки на другие файлы в диаграммах Excel

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

   1. На диаграмме нажмите на название диаграммы или ряд данных, которые вы хотите проверить.
   2. В строке формул найдите ссылку на другой файл Excel.

   Внешняя ссылка в заголовке диаграммы:

   
   

   Внешняя ссылка в серии данных диаграммы:

   
   

   Если ваша диаграмма содержит несколько рядов данных, вы можете быстро перемещаться между ними следующим образом:

   1. Выберите целевую диаграмму.
   2. Перейдите на вкладку Формат в группу Текущий выбор, щелкните стрелку рядом с полем Элементы диаграммы и выберите ряд данных интерес.

   
   

   Как найти внешние ссылки в сводных таблицах

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

   1. Нажмите любую ячейку в сводной таблице.
   2. На вкладке Анализ сводной таблицы в группе Данные нажмите кнопку Изменить источник данных.

   Как включить ссылки на внешние книги в Excel

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

   
   

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

   
   

   Управление запросом безопасности об обновлении ссылок

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

   1. На вкладке Данные в группе Запросы и подключения нажмите Редактировать ссылки.
   2. В левом нижнем углу диалогового окна Редактировать ссылки нажмите Приглашение при запуске…

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

   
   

   Изменить настройки безопасности для внешних ссылок

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

   1. В целевой книге откройте вкладку Файл Параметры.
   2. В диалоговом окне Параметры Excel нажмите Центр управления безопасностью >Настройки центра управления безопасностью.

   
   

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

   
   

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

   Как разорвать внешние ссылки в Excel

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

   Например, если вы разорвете следующую внешнюю ссылку, она будет заменена значением, которое в данный момент находится в ячейке A1 на листе Янв в рабочей книге Исходные данные. :

   Если вы разорвете внешнюю ссылку в приведенной ниже формуле, формула будет изменена на расчетное значение, каким бы оно ни было:

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

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

   На вкладке Данные в группе Запросы и подключения нажмите кнопку Изменить ссылки.

   
   

   • Чтобы выбрать несколько ссылок, нажмите каждую из них по отдельности, удерживая нажатой клавишу Ctrl.
   • Чтобы выбрать все ссылки, нажмите сочетание клавиш Ctrl + A .

   
   

   Примечание. В идеальных условиях эта функция должна удалять все внешние ссылки в книге. К сожалению, мы живем не в идеальном мире :( Некоторые ссылки на внешние данные, например внешние исходные данные в сводных таблицах, не отображаются в диалоговом окне Редактировать ссылки, а другие нельзя разорвать. Если < Кнопка em>Редактировать ссылки неактивна в вашей книге, но вы все еще получаете подсказку о внешних данных, тогда вам придется проверить все возможные места, где могут скрываться внешние ссылки (например, объекты, диаграммы и т. д. ) и измените или удалите ссылки вручную.

   Получить список всех внешних ссылок в книге

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

   Традиционный подход

   Отобразится следующая информация:

   • Источник – имя связанного файла.
   • Тип – тип ссылки: книга или лист.
   • Обновить — будет ли ссылка обновляться автоматически или вручную.
   • Статус – статус ссылки, например ОК, Источник открыт, Предупреждение, Неизвестно и т. д. Чтобы получить самую свежую информацию, нажмите кнопку Проверить статус справа.

   
   

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

   Динамические массивы и макросы Excel 4.0.

   Очень милый трюк, предложенный Бобом Ульмасом в его книге "Это не Excel, это Magic!" может помочь вам получить расположение всех исходных файлов за один раз. В решении сочетаются недавно представленные динамические массивы со старыми добрыми макросами Excel 4.0.

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

   Шаг 1. Создайте новое имя, которое ссылается на макрос

   Чтобы использовать встроенный макрос Excel 4.0 в формуле, необходимо создать имя, ссылающееся на макрос. Вот как:

   1. На вкладке Формулы в группе Определенные имена нажмите Диспетчер имен. Или просто нажмите сочетание клавиш Ctrl + F3.
   2. В диалоговом окне Диспетчер имен нажмите кнопку Создать…
   3. В диалоговом окне Новое имя введите осмысленное имя, например GetLinks, в поле Имя и следующую формулу в поле < em>Ссылается на поле: =LINKS()
   4. Нажмите ОК.

   
   

   Более подробные инструкции см. в разделе Как создать имя в Excel.

   Шаг 2. Используйте только что созданное имя в формуле

   Теперь, когда у вас есть имя, которое ссылается на макрос, вам просто нужно поместить это имя в формулу. В зависимости от версии Excel формула может иметь разный вид.

   В Excel 365:

   В самую верхнюю ячейку целевого диапазона введите следующую формулу:

   GetLinks (или любое другое имя, которое вы использовали для ссылки на макрос) возвращает горизонтальный диапазон всех внешних ссылок в книге. Функция ТРАНСП превращает строки в столбцы и выводит вертикальный список, который легче читать.

   
   

   Чтобы упорядочить список в алфавитном порядке, поместите приведенную выше формулу в функцию СОРТИРОВКИ:

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

   В Excel 2019–2007:

   В преддинамических версиях Excel используйте имя GetLinks для аргумента массив классической функции ИНДЕКС. Чтобы сделать решение более удобным для пользователя, вы можете обернуть конструкцию в ЕСЛИОШИБКА, чтобы позаботиться о ситуациях, когда формула копируется в большее количество ячеек, чем внешних ссылок в вашей книге:

   Формула помещается в первую ячейку (A2), а затем вы перетаскиваете ее в следующие ячейки:

   
   

   Важные примечания:

   • Поскольку в этом решении используются макросы, файл необходимо сохранить как книгу с поддержкой макросов (.xlsm).
   • Макросы Excel не выполняются и не обновляются автоматически. Чтобы обновить список ссылок, нажмите сочетание клавиш Ctrl + Alt + F9, при этом будут пересчитаны все формулы во всех открытых книгах.

   Макрос VBA для получения списка внешних ссылок

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

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

   1. Нажмите клавиши ALT + F11, чтобы открыть редактор Visual Basic.
   2. На левой панели щелкните правой кнопкой мыши Эта книга и выберите Вставить >Модуль.
   3. Вставьте приведенный выше код в окно Код.

   Подробные инструкции см. в разделе Как вставить код VBA в Excel.

   Чтобы запустить макрос, нажмите клавиши Alt + F8 в книге или F5 в редакторе VBA.

   Дополнительную информацию см. в разделе Как запустить макрос в Excel.

   В результате вы получите список внешних источников на новом листе:

   
   

   Поиск ячеек с внешними ссылками с помощью VBA

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

   
   

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

   Найти все внешние ссылки в книге одним щелчком мыши

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

   Если в Excel установлен Ultimate Suite, для поиска всех ссылок в книге достаточно одного нажатия кнопки «Найти ссылки»:

   
   

   По умолчанию инструмент ищет все ссылки: внутренние, внешние и веб-страницы. Чтобы отображались только внешние ссылки, выберите этот параметр в раскрывающемся списке и нажмите кнопку Обновить.

   
   

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

   Чтобы перейти к ячейке, которая ссылается на внешние данные, щелкните адрес ячейки на панели.

   Простые вещи должны быть простыми! :)

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

   Блог, ориентированный в основном на Microsoft Excel, PowerPoint и Word, со статьями, призванными вывести ваши навыки анализа данных и работы с электронными таблицами на новый уровень. Научитесь всему: от создания информационных панелей до автоматизации задач с помощью кода VBA!

   
   

   Как сломать внешние ссылки

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

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

   Удаление внешних ссылок из ячеек

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

   Вкладка «Данные» >> Группа «Запросы и подключения» >> Кнопка «Изменить ссылки»

   Когда вы увидите диалоговое окно «Редактировать ссылки», вы увидите список всех внешних файлов Excel, из которых извлекаются данные. Чтобы удалить/разорвать связь, просто выберите строки, которые вы хотите удалить, и нажмите кнопку "Разорвать связь".

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

   Нажмите «Разорвать ссылки», и все ваши ссылки «должны» быть разорваны. В идеале кнопка «Редактировать ссылки» будет неактивна, а все ваши внешние ссылки будут удалены.

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

   Удаление внешних ссылок из диаграмм

   Внешние ссылки могут находиться внутри любого текстового поля объекта диаграммы. Сюда входят:

   Удаление внешних ссылок из фигур

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

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

   Нажмите клавишу F5, чтобы открыть диалоговое окно "Перейти к"

   Нажмите "Специальные"

   Отметить только объекты

   Нажмите "ОК".

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

   Удаление внешних ссылок из именованных диапазонов

   Удаление внешних ссылок из сводных таблиц

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

   Выберите ячейку в сводной таблице

   Перейдите на вкладку Анализ инструментов сводной таблицы

   Нажмите кнопку "Изменить источник данных"

   Загляните в диалоговое окно "Изменить источник данных сводной таблицы" и убедитесь, что ваши данные не связаны извне

   Удаление внешних ссылок из правил проверки данных

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

   Использование средства проверки совместимости для поиска ошибок проверки данных:

   Выберите вкладку "Файл"

   В разделе "Информация" выберите раскрывающийся список "Проверить наличие проблем"

   Выберите Проверить совместимость

   В диалоговом окне "Проверка совместимости" нажмите кнопку "Копировать на новый лист"

   Вы должны увидеть новый лист со всеми перечисленными проблемами. Используйте сочетание клавиш Ctrl + F, чтобы открыть диалоговое окно «Найти» и выполнить поиск экземпляров «проверки данных»

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

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

   Если вы видите какие-либо внешние ссылки в поле "Источник", вы, скорее всего, захотите нажать кнопку "Очистить все", чтобы избавиться от внешней ссылки

   Любые другие области?

   Обнаружили ли вы какие-либо другие области в ваших электронных таблицах, где прятались внешние ссылки? Дайте мне знать в комментариях, и я буду обновлять эту статью, чтобы у нас был хороший исчерпывающий список!

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

     
   • Как создать файл cs в Visual Studio
     
   • Договор найма жилого помещения образец 2016 между физическими лицами слово
     
   • Как сделать календарь в word
     
   • Как вставить список в таблицу в word
     
   • Как удалить супремо с компьютера