Чтобы ссылки на книги функционировали правильно, их необходимо поддерживать и обновлять. Вам потребуется обновить их, чтобы получить последние значения и найти их в книге, если они сломаются.
Обновление ссылок в книге
Вот как можно убедиться, что вы получаете последние значения из исходных книг.
Обновление всех ссылок в книгах
- Чтобы открыть область "Ссылки в книге", щелкните"Запросы к данным> и подключения>" Ссылки в книге.
-
Выберите "Обновить все" в верхней части области.
Обновление ссылки на определенную книгу
- Чтобы открыть область "Ссылки в книге", щелкните"Запросы к данным> и подключения>" Ссылки в книге.
- Выберите книгу в списке и нажмите кнопку "Обновить".
Автоматическое обновление всех ссылок в книгах
Примечание
В классических книгах нет команды "Обновлять автоматически ", поскольку эта операция обычно выполняется при автоматическом пересчете при открытии книги. Дополнительные сведения см. в статье Изменение пересчета, итерации или точности формулы в Excel.
Управление запросом на запуск для обновления ссылок в книге
Вы можете решить, будут ли ссылки в этой книге обновляться при открытии файла в соответствии с настройками пользователя, не обновляться ли при открытии файла или обновляться автоматически без обращения к пользователю.
Этот режим влияет на всех пользователей книги. Если вы решите не обновлять ссылки и не предлагать пользователям, пользователи книги не будут знать, что данные устарели.
Чтобы открыть область "Ссылки в книге", щелкните"Запросы к данным> и подключения>" Ссылки в книге.
Выберите и разверните "Параметры обновления " в правом верхнем углу панели и выберите один из следующих вариантов:
- Попросить освежить Предлагает пользователю обновить или отключить окно при открытии книги.
- Всегда обновлять Автоматически обновляет все ссылки при открытии книги.
- Не обновляйте Не обновляется при открытии и не запрашивает пользователя.
Управление обновлением ссылок в книгах
Если вы открываете целевую книгу, а исходная книга не открыта, панель безопасности может потребовать обновить связи.
Можно задать режим вывода этого запроса, а также режим обновления всех связей без вывода запроса. Кроме того, можно обновить только определенные ссылки, если книга содержит несколько.
Обновление всех ссылок в книгах вручную или ни одной
- Закройте все исходные книги. Если одна книга-источник будет открыта, а остальные закрыты, обновления не будут одинаковыми.
- Откройте конечную книгу.
- В диалоговом окне предупреждения об опасных ссылках нажмите кнопку "Обновить". Это обновит все связи с данными в книге.
Если нужно обновить только определенные ссылки, выберите "Не обновлять" и см. раздел "Изменение исходной книги " ниже.
При появлении панели предупреждения системы безопасности при открытии книги
Если при первом открытии книги появляется строка с предупреждением системы безопасности, это означает, что книга еще не является доверенной. Чтобы сделать книгу доверенной:
Выберите "Включить содержимое". Это делает книгу доверенной для данного сеанса и обновляет ссылки.
Если появится диалоговое окно с просьбой сделать документ надежным, нажмите кнопку " Да ", чтобы избежать появления панели предупреждений системы безопасности в будущем. Если нажать кнопку " Нет", при следующем открытии книги отобразится строка предупреждения системы безопасности.
Примечание
В зависимости от параметров также может появиться диалоговое окно предупреждения об опасных ссылках.
Сохранение ссылок на книги
Иногда требуется изменить исходную книгу, ссылка на книгу разрывается или ссылка на книгу больше не нужна.
Открытие исходной книги
Перед внесением существенных изменений рекомендуется сначала изучить и проверить исходную книгу.
- Чтобы открыть область "Ссылки в книге", щелкните"Запросы к данным> и подключения>" Ссылки в книге.
- Нажмите "Дополнительные команды" (...) рядом с нужной книгой, а затем выберите "Открыть книгу".
Изменение исходной книги
Чтобы изменить исходную книгу для всех ссылок в конечной книге:
- Чтобы открыть область "Ссылки в книге", щелкните"Запросы к данным> и подключения>" Ссылки в книге.
- Нажмите Дополнительные команды (...) рядом с нужной книгой, а затем выберите Изменить источник.
- В диалоговом окне "Изменить источник " в разделе "Последние" выберите новую исходную книгу. Или нажмите "Обзор" и в диалоговом окне откройте новый исходный файл.
Чтобы изменить исходную книгу для конкретной ссылки в конечной книге:
- Найдите книгу, которую необходимо использовать в качестве нового источника для внешней ссылки, и запомните ее расположение.
- В конечной книге выделите ячейку с внешней ссылкой, которую требуется изменить.
- Найдите в строке формул ссылку на другую книгу, например C:\Reports\[Budget.xlsx], и замените ее расположением новой исходной книги.
Исправление недействительной ссылки на книгу
- Чтобы открыть область "Ссылки в книге", щелкните"Запросы к данным> и подключения>" Ссылки в книге.
-
Нажмите кнопку Дополнительные команды (...) рядом с книгой данных, содержащей ссылки, которые требуется исправить, а затем выберите пункт Изменить источник.
- В диалоговом окне "Изменить источник " перейдите к расположению файла, содержащего связанные данные.
- Выберите новый исходный файл.
Разорвать ссылку на книгу
Чтобы открыть область "Ссылки в книге", щелкните"Запросы к данным> и подключения>" Ссылки в книге.
Нажмите кнопку "Дополнительные команды" (...) рядом с нужной книгой, а затем выберите пункт "Разорвать связи".
Важно
- При разрыве ссылки на исходную книгу все формулы, использующие значение в исходной книге, преобразуются в текущие значения. Например, если разорвать ссылку на книгу =СУММ([Budget.xls]Годовой! C10:C25), формула СУММ заменяется вычисляемым значением, каким бы оно ни было. Кроме того, поскольку это действие невозможно отменить, может потребоваться сохранить одну из версий целевой книги в качестве резервной копии.
- Если используется диапазон внешних данных, параметр в запросе может использовать данные из другой книги. Может потребоваться проверить и удалить эти типы связей.
Разрыв всех ссылок в книгах
- Чтобы открыть область "Ссылки в книге", щелкните"Запросы к данным> и подключения>" Ссылки в книге.
- Нажмите кнопку "Разорвать все" в верхней части области.
Поиск ссылок в книгах
Автоматического поиска всех ссылок в книге не существует. Формулы, определенные имена, объекты (например, надписи или фигуры), названия диаграмм и ряды данных диаграмм должны выглядеть по-разному.
Ссылка на следующую книгу
В книге может быть несколько ссылок на книгу. Вот как найти нужный файл.
- Чтобы открыть область "Ссылки в книге", щелкните"Запросы к данным> и подключения>" Ссылки в книге.
- Выберите книгу в списке и нажмите кнопку "Далее".
Поиск ссылок на книги, используемых в формулах
Нажмите клавиши CTRL+F , чтобы открыть диалоговое окно "Найти и заменить ".
Нажмите кнопку Параметры.
В поле "Найти " введите .xl.
В поле "В пределах выберите книгу".
В поле "Подстановка " щелкните "Формулы".
Нажмите кнопку "Найти все".
В появившемся списке найдите в столбце "Формулы" формулы, содержащие формат XL. В этом случае приложение Excel обнаружило несколько экземпляров Master.xlsx бюджета.
Чтобы выбрать ячейку со ссылкой на книгу, щелкните ссылку на адрес ячейки в списке.
Совет
Щелкните заголовок любого столбца, чтобы отсортировать столбец и сгруппировать все связи книги.
Поиск ссылок на книги, используемых в определенных именах
На вкладке Формулы в группе Определенные имена выберите пункт Диспетчер имен.
Проверьте каждую запись в списке и просмотрите столбец "Ссылки на " на наличие ссылок в книге. Ссылки на книги содержат ссылку на другую книгу, например [Budget.xlsx].
Совет
- Щелкните заголовок любого столбца, чтобы отсортировать столбец и сгруппировать все связи книги.
- Если нужно удалить сразу несколько объектов, можно сгруппировать несколько элементов с помощью клавиш SHIFT или CTRL и левой кнопки мыши .
Поиск ссылок в книгах, используемых в объектах, текстовых полях и фигурах
-
Нажмите клавиши CTRL+G (сочетание клавиш для диалогового окна «Перейти»), затем нажмитекнопку «ОК»для специальных> объектов>. Будут выделены все объекты на активном листе.
- Нажмите клавишу TAB для перемещения между выделенными объектами, а затем найдите в
строки формул ссылку на другую книгу, например [Budget.xlsx].
Поиск ссылок на книги, используемых в названиях диаграмм
- Щелкните на диаграмме заголовок диаграммы, которую нужно проверить.
-
строки формул найдите ссылку на другую книгу, например [Budget.xls].
Поиск ссылок в книгах, используемых в рядах данных диаграммы
- Выделите диаграмму, которую нужно проверить.
- На вкладке "Макет" в группе "Текущий выделенный" щелкните стрелку рядом с полем "Элементы диаграммы" и выберите ряд данных, которые нужно проверить.
- На строки формул найдите ссылку на другую книгу, например [Budget.xls] в функции РЯД.
Использование устаревшей команды "Изменить ссылки"
Устаревшая команда "Изменить ссылки " заменена командой "Ссылки в книге ". Однако вы можете вернуть старую команду "Изменить ссылки", добавив старую команду "Изменить ссылки " в настраиваемую группу на ленте. Команду "Изменить ссылки " нельзя добавить в группу "Запросы и связи ".
Добавление команды "Изменить ссылки" в настраиваемую группу
С помощью функции "Настройка ленты" необходимо создать настраиваемую группу, и только после этого можно добавлять ссылки для изменения в настраиваемую группу на ленте следующим образом:
- Щелкните ленту правой кнопкой мыши и выберите команду "Настройка ленты".
- В раскрывающемся списке "Выбрать команды " выберите пункт "Все команды".
- Нажмите "Изменить ссылки" и откройте вкладку "Данные".
- Нажмите Создать группу , а затем выберите Добавить. Изменение ссылок будет добавлено в настроенную группу.
На панель быстрого доступа можно также добавить редактирование ссылок. Дополнительные сведения см. в статье Настройка панели быстрого доступа. Команда "Изменить ссылки" недоступна, если книга не содержит ссылок.
Диалоговое окно "Изменение связей"
При открытии диалогового окна "Изменение связей" вам предлагается несколько вариантов работы с существующими связями. Вы можете выбрать отдельные книги с помощью клавиш CTRL+щелчок или все с помощью клавиш CTRL+A.
Команды редактирования связей
Обновить значения
При этом будут обновлены все выбранные книги.
Изменить источник
Этот параметр полезен, если вы хотите указать существующие ссылки на другой источник. Например, у вас может быть книга за предыдущий год, и вам может понадобиться указать на новую книгу в начале нового года. При выборе команды "Изменить источник " открывается диалоговое окно проводника, в котором можно перейти к новой исходной книге. Если в книге несколько листов, вам будет предложено указать, на какой из них нужно ссылаться. Просто щелкните нужный лист и нажмите кнопку "ОК".
Примечание
Можно вернуть книгу к самой себе, выбрав ее в диалоговом окне "Изменить источник". При этом будут разорваны все ссылки формул на исходную исходную книгу.
Открытый код
Откроется исходная книга.
Разрыв ссылки
Важно
При разрыве связей с источником все формулы, использующие источник, заменяются на их текущее значение. Например, ссылка =СУММ([Budget.xlsx]Годовой! C10:C25) будут преобразованы в сумму значений исходной книги. Поскольку это действие невозможно отменить, может потребоваться сначала сохранить одну из версий файла.
В диалоговом окне "Изменение связей " в списке "Источник " щелкните ссылку, которую нужно разорвать.
Вы можете выбрать отдельные книги с помощью клавиш CTRL+щелчок или все с помощью клавиш CTRL+A.Щелкните элемент Разорвать.
Если для ссылки использовалось определенное имя, вы также можете удалить это имя. Чтобы удалить имя:
На вкладке Формулы в группе Определенные имена нажмите кнопку Диспетчер имен.
В столбце Имя выберите имя, которое следует удалить, и нажмите кнопку Удалить.
Примечание
Если используется диапазон внешних данных, параметр запроса может также использовать данные из другой книги. Может потребоваться проверить и удалить эти типы связей.
Проверка состояния
При этом в области "Изменение ссылок" просто появится уведомление о том, является ли связанная книга допустимым источником. Он должен отображаться нормально, но если это не так, потребуется проверка исходной книги. Во многих случаях исходная книга может быть перемещена или удалена, что приведет к обрезке ссылки. Если книга по-прежнему существует, можно использовать параметр "Изменить источник ", чтобы повторно связать книгу.
Вопросы и ответы
Почему в книге появляются новые ссылки?
В предыдущих версиях Excel ссылки в именах, которые в настоящее время не используются в книге, скрываются. Эти связи существовали всегда и больше не скрыты. Чтобы удалить ссылку, нажмите кнопку " Разорвать связи " для выбранной книги в области "Ссылки книги ".
Можно ли заменить одну формулу ее вычисленным значением?
Да. Формулы в Excel, вместо которых вводится их значение, удаляются без возможности восстановления. Если вы случайно заменили формулу значением и хотите восстановить формулу, выберите "Главная " и нажмите " Отменить " или нажмите CTRL+Z сразу после ввода или вставки значения.
- Нажмите клавиши CTRL+C , чтобы скопировать ячейку с формулой.
- Нажмите клавиши ALT+E+S+V, чтобы вставить формулу в качестве значения, или выберите"Буфер обмена>""Главная>""Вставка" "Вставить> значения".
Что делать, если я не подключен к источнику?
Выберите "Не обновлять" в диалоговом окне предупреждения об опасных ссылках. Обновление из источника при отсутствии подключения к нему невозможно. Например, если книга-источник расположена в сети, а подключение к сети отсутствует.
Что делать, если я не хочу, чтобы текущие данные заменялись новыми?
Выберите "Не обновлять" в диалоговом окне предупреждения об опасных ссылках.
Как выполнить обновление, не занимая при этом так много времени?
Выберите "Не обновлять" в диалоговом окне предупреждения об опасных ссылках. Если не требуется получение текущих данных, можно сэкономить время, не обновляя все связи. Открыв книгу, перейдите на вкладку "Данные" и в группе "Запросы & связи" нажмите кнопку "Ссылки на книгу", а затем обновляйте ссылки только из нужных вам источников.
Как может не появиться этот запрос после того, как другой пользователь создал книгу?
В диалоговом окне предупреждения об опасных ссылках нажмите кнопку "Не обновлять " и обратитесь к владельцу книги. Можно также узнать, какие ссылки есть в книге. На вкладке " Данные " в группе "Запросы & подключения " выберите "Ссылки на книги".
Как может запрос не появляться снова, если я каждый раз нажимаю на него один и тот же запрос?
Вы можете выбрать и развернуть параметр "Параметры обновления " в правом верхнем углу области "Ссылки на книгу " и выбирать последовательно, не выводя запрос на запуск книги.
Разделы справки не запрашивают отображение во всех открываемых мною книгах и не обновляют ссылки автоматически?
Этот параметр влияет на все книги, которые открываются на компьютере. Пользователи, которые откроют книгу на другом компьютере, не будут затронуты.
- На вкладке "Файл " нажмите кнопку "Параметры " и выберите пункт "Дополнительно".
- В разделе "Общие " снимите флажок "Запрашивать об обновлении автоматических ссылок". Если флажок проверки снят, ссылки автоматически обновляются, и оповещение не отображается.
Как настроить одинаковый запрос в разделах справки для каждого пользователя этой книги?
Предупреждение
Этот режим влияет на всех пользователей книги. Если вы решите не обновлять ссылки и не предлагать пользователям, пользователи книги не будут знать, что данные устарели.
Выберите и разверните параметр "Параметры обновления " в правом верхнем углу области "Ссылки на книгу " и выберите нужный параметр.
Примечание
При наличии разорванных связей будет появляться оповещение об этом.
Что делать, если используется запрос с параметрами?
Связь с параметрическим запросом нельзя обновить без открытия книги-источника.
- Выберите "Не обновлять" в диалоговом окне предупреждения об опасных ссылках.
- Закройте конечную книгу.
- Откройте книгу-источник.
- Откройте конечную книгу.
- Нажмите кнопку Обновить.
Почему я не могу выбрать параметр "Вручную" в качестве параметра обновления для конкретной внешней ссылки?
Для ссылок на формулы всегда установлено значение "Автоматически".
