Как посмотреть связи в excel

Обнаружены внешние ссылки

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

Проблема

Инспектор документов не может удалить эти внешние ссылки.

Рекомендуемое решение

1. Закройте инспектор документов.
2. Выберите внешние ссылки, которые нужно удалить, и нажмите клавишу DELETE либо разорвите связи, если вы хотите удалить ссылки, но сохранить вычисленные значения.
3. Снова запустите инспектор документов, чтобы убедиться в том, что проблема устранена.

Дополнительные сведения о внешних ссылках

Инспектор документов обнаруживает внешние ссылки, отображаемые в диалоговом окне Изменить связи (в Excel щелкните Данные > Изменить связи).

Внешние ссылки могут использоваться в следующих элементах:

• ячейки листов;
• имена;
• объекты, например текстовые поля или фигуры;
• заголовки диаграмм;
• ряды данных диаграмм.

Как посмотреть связи в excel

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

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

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

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

1. Выбрать ячейку для проверки и перейти в меню ФОРМУЛЫ .
   
2. Нажать на кнопку «Влияющие ячейки»
3. При наличии ссылок на другие листы, мы увидим стрелку с указанием на другой лист.
   
4. Необходимо кликнуть два раза по стрелке и вызвать окно перехода.
   
5. В открывшемся окне , выбираем ссылку на необходимый лист и нажимаем ОК .
   
6. Программа выделит целевую ячейку , влияющую на наши расчеты.
   

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

Если материал Вам понравился или даже пригодился, Вы можете поблагодарить автора, переведя определенную сумму по кнопке ниже:
(для перевода по карте нажмите на VISA и далее «перевести»)

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

Иногда при работе с различными отчетами приходится создавать связи с другими книгами(отчетами). Чаще всего это используется в функциях вроде ВПР (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, но и среди других возможных мест их нахождения:

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

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

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

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

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

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

  Расскажи друзьям, если статья оказалась полезной:

  Видеоинструкции по использованию надстройки MulTEx

  Поделитесь своим мнением

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

  Как посмотреть связи в excel

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

  Прикрепленные файлы

  • post_330586.xlsx (34.38 КБ)

  Пользователь
  Сообщений: 9709 Регистрация: 22.12.2012
  27.04.2012 20:46:40

  это на самом деле ваш файл? почему тогда вы не знаете, что и где в нем находится?

  в имена загляните.

  фрилансер Excel, VBA — контакты в профиле
  «Совершенствоваться не обязательно. Выживание — дело добровольное.» Э.Деминг

  Пользователь
  Сообщений: 4 Регистрация: 01.01.1970
  27.04.2012 20:58:19

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

  27.04.2012 21:06:07

  В вашей книге 2 именованных диапазона (МЕСЯЦ и ТАБЕЛЬНЫЙ), которые ссылаются на ячейки другой книги
  Откройте окно редактирования имён (Ctrl+F3) и отредактируйте ссылки

  27.04.2012 22:05:37

  Ссылка на сбойный именованный диапазон МЕСЯЦ есть в списках ячеек столбца G:G
  В старой книге был лист «Месяц», а в новой его нет. Это тупик, и Excel принял правильное решение, скромно отчитавшись о собственной беспомощности
  И так случается всегда.
  Всегда помимо потерянной книги есть дополнительные причины, препятствующие восстановлению связей

  Пользователь
  Сообщений: 4 Регистрация: 01.01.1970
  27.04.2012 22:15:48

  Спасибо за отзыв. Я удал диапазоны из книги, но все равно существуют связи. Может быть еще где-то посмотреть?

  С уважением,
  Сергей

  Сообщений: 60951 Регистрация: 14.09.2012
  Контакты см. в профиле
  27.04.2012 22:32:25
  А темы зачем дублировать?
  27.04.2012 22:36:54

  У меня в Вашей книге после удаления имён связи уходят
  Возможно, Вы сейчас пишете о другой книге?
  Тогда её надо тоже посмотреть
  Можете переслать мне (v__step@mail.ru) только я смогу посмотреть скорее всего завтра вечером (на работе запарка)
  Лучше сохранить в формате 97-2003

  27.04.2012 22:54:35

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

  27.04.2012 23:06:37
  Кажется, понял
  Вы пишете об удалении диапазонов
  А надо удалить (или отредактировать) имена
  28.04.2012 00:51:45

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

  28.04.2012 01:05:56

  Ссылки на внешние книги могут прятаться в условиях проверок, в формулах условного форматирования, в ссылках некоторых графических объектов (когда выделяешь объект, в строке формул может появиться ссылка), в формулах диаграмм и в объектах в окне диаграмм
  Обратите внимание, у Вас могут быть скрытые объекты (с нулевым размером)
  Неприятность может быть скрыта вместе с ячейками, листами
  Часть имён может быть скрыта посредством VBA
  Возможны также скрытые или сильно скрытые листы (все они видны в окне проекта VBA)
  Это не полный список
  Все эти и другие опасности должна находить утилита Билла Менвилла
  Но можно поискать и самостоятельно

  Пользователь
  Сообщений: 2443 Регистрация: 21.12.2012
  28.04.2012 02:46:47

  http://www.planetaexcel.ru/forum.php?thread_id=22070
  есть post_181204.xls, в котором Sub Svyazi() выводит на отдельный лист все связи книги (в т.ч. битые ссылки). Автор не указан.

  28.04.2012 10:22:17

  Доброе утро!
  Я просмотрел процедуру Svyazi — работа идёт только с ячейками, поэтому её возможности интересны, но заведомо ограничены
  Утилита Менвилла распространяется с открытым исходным кодом
  Как бы её охарактеризовать.
  Никогда не видел ничего более обстоятельного и мощного
  У Уокенбаха есть утилита удаления имён. Проблема похожая: надо обойти много объектов, которые могут содержать ссылки (конечно, не только ячейки). В какой-то момент автор останавливается и ограничивает круг поиска, что разумно и очень естественно
  А Менвилл идёт до конца

  28.04.2012 11:28:17

  Есть мысль
  Поскольку книгу надо всё-таки посмотреть, можно сделать так:
  1) Ищете связи по ячейкам самостоятельно
  2) Для всех листов Вы очищаете формулы и значения всех ячеек
  3) Если файл ещё большой, частично очищаете форматы (за исключением формул условного форматирования)
  Ничего больше не трогаете (это самое главное)
  Присоединяете книгу к сообщению, а мы ищем связи в большом-большом числе всего оставшегося, где они ещё могут быть

  28.04.2012 21:30:08
  может это поможет.. http://www.excelworld.ru/forum/2-1384-1
  28.04.2012 22:23:37

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

  Связи — великое достояние Excel, его гибкость и сила
  Это — главный инструмент, самый простой и самый сложный одновременно
  Поэтому так трудно бывает найти потерянные связи
  Объектом — носителем внешней связи может быть и сводная таблица, и прямоугольник где-нибудь внутри сгруппированных объектов, и подпись какой-то одной точки на диаграмме.

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

  Пользователь
  Сообщений: 4 Регистрация: 01.01.1970
  01.05.2012 18:03:16

  Доброго всем время суток!

  Благодаря v__step наконец-то найдены связи в моей таблице. Перебирая массу вариантов поиска места «засады», в которой сидят ссылки, остановились на следующем:
  Создаём новую книгу, располагаем её рядом со старой и начинаем перетягивать листы из старой в новую по-одному за ярлычок.
  Перетянули — посмотрели — на вкладке ленты «Данные» оживёт кнопка «Изменить связи»
  Если ожила нажимаем на неё — открывается окно связей. Если там только старая книга, значит спокойно удаляем этот лист и переходим к следующему.
  Но если появятся ещё строчки — значит на этом листе есть внешняя ссылка!
  И так спокойно для каждого листа.
  Запоминаем или записываем на листочек (надежнее) те листы на которых есть ссылки на файл(ы) в других книгах. Нам это пригодится потом для исправления формул.

  Далее мы удалили все внешние связи следующим путем:
  Переименовываем файл (на всякий случай) с изменением расширения .xlsm на .zip
  Получается — зазипованная папка
  Заходим в эту папку не раззиповывая и ищем везде папку с названием «externalLinks», затем удаляем её и снова меняем расширение на .xlsm

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

  Читать:

  Как собрать соковыжималку в комбайне bosch