Найти скрытые связи

Данная функция является частью надстройки MulTEx

Описание, установка, удаление и обновление Полный список команд и функций MulTEx Часто задаваемые вопросы по MulTEx   Скачать MulTEx

Вызов команды:
MulTEx -группа Книги/Листы -Книги -Найти скрытые связи

Иногда при работе с различными отчетами приходится создавать связи с другими книгами(отчетами). Чаще всего это используется в функциях вроде ВПР(VLOOKUP) для получения данных по критерию из таблицы, расположенной в другой книге. Так же это может быть и простая ссылка на ячейки другой книги. В итоге ссылки в таких ячейках выглядят следующим образом:
=ВПР(A2;'[Продажи 2018.xlsx]Отчет'!$A:$F;4;0)
или
='[Продажи 2018.xlsx]Отчет'!$A1
[Продажи 2018.xlsx] - обозначает книгу, в которой итоговое значение. Такие книги так же называют источниками
Отчет - имя листа в этой книге
$A:$F и $A1 - непосредственно ячейка или диапазон со значениями

Если закрыть книгу, на которую была создана такая ссылка, то ссылка сразу изменяется и принимает более "длинный" вид:
=ВПР(A2;'C:\Users\Дмитрий\Desktop\[Продажи 2018.xlsx]Отчет'!$A:$F;4;0)
='C:\Users\Дмитрий\Desktop\[Продажи 2018.xlsx]Отчет'!$A1
Такие ссылки так же принято называть связыванием книг. И как только создается такая ссылка, на вкладке Данные(Data) в группе Запросы и подключения(Queries & Coonections) активируется кнопка Изменить связи(Edit Links). Там же их можно изменить. В большинстве случаев ни использование связей, ни их изменение не доставляет особых проблем. Но если книгу-источник переместили или переименовали - при следующем открытии книги со ссылками на неё Excel покажет сообщение о недоступных связях в книге и запрос на обновление этих ссылок:

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

Так же изменение связей доступно непосредственно из вкладки Данные(Data). Там же связи можно разорвать, т.к. как правило связи редко нужны на продолжительное время(ведь они неизбежно увеличивают размер файла, особенно, если связей много). Чтобы разорвать связи необходимо перейти на вкладку Данные(Data) -группа Запросы и подключения(Queries & Coonections) -Изменить связи(Edit Links)(появится тоже самое окно, что показано выше). Выделить нужные связи и нажать Разорвать связь(Break Link). При этом все ячейки с формулами, содержащими связи, будут преобразованы в значения, вычисленные этой формулой при последнем обновлении. Данное действие нельзя будет отменить - только закрытием книги без сохранения.
Но иногда возникают ситуации, когда вроде все связи разорваны всеми доступными методами, но запрос на обновление каких-то связей все равно появляется. Вот для поиска этих мифических связей и предназначена команда MulTEx Найти скрытые связи, т.к. она ищет связи не только внутри формул, где их разрывает стандартно сам Excel, но и среди других возможных мест их нахождения:

Искать связи:
выбирается тип связей(с ошибками или все) и местонахождение связей: формулы, проверка данных, условное форматирование, именованные диапазоны.

типы связей
• все - будут просматриваться все связи на другие книги
• только с ошибками - будут просматриваться только те связи на другие книги, которые содержат ошибку типа #ССЫЛКА!(#REF!)

местонахождение (можно выбрать сразу несколько вариантов)
• в Формулах - связи будут просматриваться только в формулах, записанных в ячейках листа
• в Проверке данных - связи будут просматриваться только в ячейках, для которых установлена проверка данных(вкладка Данные -Проверка данных). Подробнее про проверку данных >>
• в Условном форматировании - связи будут просматриваться в правилах условного форматирования. При этом правила просматриваются в ячейках или листах, указанных в блоке Просматривать связи
• в Именованных диапазонах - связи будут просматриваться в именованных диапазонах. При этом имена могут быть скрытыми и не отображаться напрямую в списке имен, что делает невозможным их редактирование или удалению напрямую из Excel. Для просмотра и удаления таких имен следует воспользоваться командой MulTEx Управление именами

Искать только если имя источника содержит - если флажок установлен, то необходимо в поле ниже ввести слово или словосочетание, которое необходимо найти внутри ссылки/связи. В этом случае будут отобраны только те связи, внутри которых есть подобное слово/словосочетание. Необходимо для случаев, когда необходимо целенаправленно отыскать только связи, ссылающиеся на определенную книгу или папку.
Например, чтобы отобрать ссылки на книгу с именем "Отчет за 2-е полугодие 2018" необходимо задать в поле текст: *[Отчет за 2-е полугодие 2018.xls*]*. Звездочка после xls не случайна - это избавляет от необходимости определять конкретное расширение для книги(xlsm, xlsx,xlsb,xls и т.п.).
Если необходимо отобрать ссылки на любые книги из папки "Маркетинг", текст необходимо задать такой *\Маркетинг\*

Просматривать связи:

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

После нахождения связи:
выбирается действие вывода результата

• выделить ячейки со связями- будут выделены обычным выделением все ячейки, в которых так или иначе присутствуют найденных связи. После этого с выделенными ячейками можно будет делать любые действия, доступные для ячеек: залить цветом, удалить содержимое, изменить параметры и т.д.
  Примечание: Если связь в ячейке присутствует не напрямую, а через именованный диапазон, то такая ячейка не будет определена. Для того, чтобы найти такие связи лучше использовать вывод на лист.
• выделить ячейки цветом - все ячейки, в которых так или иначе присутствуют найденных связи, будут закрашены выбранным цветом
  Примечание: Если связь в ячейке присутствует не напрямую, а через именованный диапазон, то такая ячейка не будет определена. Для того, чтобы найти такие связи лучше использовать вывод на лист.
• вывести список ячеек и связей на отдельный лист - будет создана новая книга с одним листом, в котором списком будут выведены все найденные связи с указанием:
  • Имя листа - лист, где содержится ссылка, если ссылка является частью формулы, проверки данных или условного форматирования. Если связь содержится внутри именованного диапазона, то в это поле записывается область действия имени: [Книга], если область действия книги и имя листа, если конкретный лист.
  • Адрес ячейки - ячейка, в которой связь. В случае с именованным диапазоном - выводится имя диапазона
  • Формула - формула листа, проверки данных, условного форматирования или именованного диапазона
  • Тип - тип объекта, в котором обнаружена связь: формула, проверка данных, условное форматирование или именованный диапазон
• попытаться разорвать связь - в данном случае при нахождении связи MulTEx попытается удалить эту связь. Если это условное форматирование - MulTEx попытается удалить правило условного форматирования со связью. Если это проверка данных - MulTEx попытается удалить проверку данных из ячейки. Если это формула на листе - формула будет удалена. В случае с именованным диапазоном MulTEx не предпринимает никаких действий по простой причине: именованные диапазоны могут быть использованы и внутри других имен, и внутри формул, и внутри проверок данных и условного форматирования и удаление такого имени может привести к множественным ошибкам, корректного устранить которые уже не получится. В таких случаях лучше использовать сначала вывод результата на лист для определения нужных имен и удаления их вручную убедившись, что такое удаление не повлечет ошибки вычислений.