Трюк №13. Удаление несуществующих ссылок на книги Excel

О, несуществующие ссылки! Вы открываете рабочую книгу и видите сообщение с просьбой обновить ссылки — но у вас нет никаких ссылок! Как же можно обновить то, что не существует?

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

Сначала необходимо узнать, присутствуют ли в книге реальные внешние ссылки (существующие), о которых вы просто забыли. Если вы не уверены, есть ли такие, начните с поиска в самом очевидном месте — в формулах. Для этого удостоверьтесь, что никакие другие книги не открыты и выполните поиск по ключевой фразе [*] в формулах на всех листах. Закройте все остальные книги, чтобы гарантировать, что все ссылки в формулах будут содержать [*], где звездочка представляет собой символ шаблона.
[stextbox id=»warning»]В Excel 97 не предусмотрена возможность поиска во всей рабочей книге, но вы можете выполнить поиск во всех листах в книге, сгруппировав их. Для этого правой кнопкой мыши щелкните ярлычок любого листа и в контекстном меню выберите команду Выделить все листы (Select All Sheets). В более поздних версиях Excel команды Найти (Find) и Заменить (Replace) обеспечивают возможность поиска в пределах листа или книги.[/stextbox]
Выполнив поиск ссылок в формулах, измените или вообще удалите соответствующие формулы. Решение изменять формулу или удалять зависит от конкретной ситуации, и только вам решать, по какому пути пойти. Кроме того, можно отправиться в Центр загрузки Microsoft Office (Microsoft Office Download Center), расположенный по адресу: http://office.microsoft.com/Downloads/default.aspx, и в категории Надстройки (Add-Ins) выбрать Delete Links Wizard. Эта утилита предназначена для поиска и удаления ссылок, таких, как ссылки на определенные имена, ссылки на скрытые имена, ссылки на диаграммы, ссылки Microsoft Query и ссылки на объекты. Однако, как показывает наш опыт, она не находит несуществующие ссылки.

Когда вы удостоверитесь, что в формулах ссылок нет, нужно будет убедиться, что допустимые ссылки не прячутся где-либо еще. Для этого нужно начать из глубины книги Excel, содержащей несуществующие ссылки. Выберите команду Вставка → Имя → Присвоить (Insert → Name → Define). Пролистайте список имен, щелкая и выделяя их по очереди и просматривая формулу в поле Формула (Refers to) внизу. Удостоверьтесь, что ни одно из имен не ссылается на другую рабочую книгу.
[stextbox id=»warning»]Вместо того чтобы щелкать подряд все имена в диалоговом окне Присвоение имени (Define Name), можно вставить в книгу новый лист и выбрать пункт меню Вставка → Имя → Вставить (Insert → Name → Paste). Затем в диалоговом окне Вставка имени (Paste Name) щелкните кнопку Вставить все (Paste Link). Будет создан список всех имен в вашей книге, а диапазоны, на которые они ссылаются, будут перечислены в соответствующем столбце.[/stextbox]
Если любое из имен указывает за пределы вашей книги, вы не найдете источника, по меньшей мере, одной ссылки, что и решит вопрос обновления. Теперь вы должны решить, что необходимо делать: изменить имя диапазона, чтобы оно ссылалось только на эту книгу, или оставить вес как есть.

Другой возможный источник ссылок — это ваши диаграммы. У них может проявиться та же проблема, которую мы только что рассматривали. Необходимо проверить, что все диапазоны данных и метки оси X (X-axis) диаграммы не ссылаются на внешние книги. И снова вам решать, являются ли найденные ссылки правильными.

Ссылки скрываются и в объектах, например, текстовых полях, автофигурах и т. д. Объекты могут ссылаться на внешние рабочие книги. Самый простой способ найти объекты — выбрать любую ячейку на листе и затем выбрать команду Правка → Перейти (Edit → Go To). В диалоговом окне Переход (Go To) щелкните на кнопке Выделить (Special), установите флажок Объекты (Objects) и щелкните на кнопке ОК. Будут выбраны все объекты на листе. Это необходимо выполнять на копии вашей рабочей книги. Когда все объекты будут выделены, вы сможете удалить, сохранить, закрыть и снова открыть вашу копию, чтобы проверить, решена ли проблема.

И, наконец, последнее неочевидное место, где можно найти реальные ссылки — это скрытые листы, которые вы могли создать и забыть о них. Отобразите такие листы, выбрав команду Формат → Лист → Отобразить (Format → Sheet → Unhide). Если пункт Отобразить (Unhide) во вложенном меню Лист (Sheet) неактивен, это означает, что у вас нет скрытых листов. (Если вы уверены, что в книге есть листы, не появляющиеся в этом меню, обратитесь за подробностями к разделу «Трюк № 5. Как скрыть лист, чтобы его невозможно было отобразить».)

Теперь, когда вы устранили возможность появления реальных ссылок, настало время удалить несуществующие. Откройте рабочую книгу с несуществующими ссылками и выберите команду Правка → Связи (Edit → Links). Иногда можно просто выделить ненужную ссылку, щелкнуть кнопку Изменить (Change Source) и связать ссылку с самой собой. Но чаще всего появляется сообщение, что одна из формул содержит ошибку, и это сделать невозможно.

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

Создайте настоящую связь между двумя книгами, открыв обе. Перейдите к проблемной книге и в любой ячейке любого листа введите =. Теперь щелкните любую ячейку в хорошей книге и нажмите клавишу Enter, чтобы создать настоящую внешнюю ссылку на другую книгу. Сохраните обе книги, но не закрывайте их. В книге с несуществующими ссылками выберите команду Правка → Связи (Edit → Links) и щелкните кнопку Изменить (Change Source), чтобы увидеть все ссылки на новую рабочую книгу, с которой вы только что намеренно создали новую связь. Снова сохраните вашу рабочую книгу и удалите ячейку, в которой создали настоящую внешнюю ссылку. И еще раз сохраните файл.

При этом часто исчезает ненужная несуществующая ссылка, так как Excel, наконец, понимает, что вы удалили внешнюю ссылку на книгу. Если проблема, однако, не решена, попробуйте выполнить следующее, но сначала сохраните копию книги.
[stextbox id=»warning»]Следующий процесс включает удаление данных без возможности их восстановления. Таким образом, прежде чем начать, создайте резервную копию вашей книги. Если вы этого не сделаете, возможно появление проблем.[/stextbox]
В открытой проблемной книге удалите один лист, сохраните книгу, затем закройте и заново откройте ее. Если вы не увидите приглашение обновить отсутствующие связи, значит, удаленный лист содержал несуществующую ссылку. Теперь проблема должна быть решена. Однако если это не так, повторите первый шаг для каждого листа в книге. Вам понадобится добавить новый лист перед удалением последнего, так как в рабочей книге должен быть хотя бы один лист.

Мы продолжаем, предполагая, что эта техника у вас сработала. Вот что следует сделать далее. Откройте копию вашей рабочей книги (ту, которая содержит все данные) и сделайте еще одну копию. Вы будете работать с проблемным листом (или листами) и выполнять процесс удаления, чтобы обнаружить, где на листе прячется проблема.

Открыв проблемный лист, выделите некоторый диапазон ячеек (область приблизительно 10 х 10 ячеек) и затем выберите команду Правка → Очистить → Все (Edit → Clear → All). (Вы уверены, что сохранили копию книги?) Сохраните, закройте и заново откройте лист. Если приглашение обновить ссылки не появилось, вы нашли проблему, и теперь необходимо переделать этот блок ячеек. Если приглашение открылось, продолжайте очищать ячейки, пока при очередном открытии приглашение не будет выведено. После этого измените проблемные ячейки.

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