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

  • Статья
  • Применяется к:
    Office Products, Excel 2013, Excel 2016, Excel 2019, Excel 2021

Симптомы

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

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

    • #ССЫЛКА!

    • #ЗНАЧ!

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

    • #ИМЯ?

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

Причина

Этот сценарий возникает, если открытая книга была в последний раз сохранена в версии Excel, которая была более ранней, чем версия, используемая для открытия книги. Этот сценарий происходит из-за того, что Excel принудительно выполняет полный пересчет всех открытых книг, которые ранее были сохранены в более ранней версии Excel, независимо от состояния обновления ссылки. Для полного пересчета книги Excel получает текущее сохраненное значение всех внешних ссылок из таблицы внешних ссылок книг. Это происходит, даже если вы решили не обновлять эти ссылки при появлении запроса. Excel обновляет цепочку вычислений книги до текущей версии Excel. Если значения источников внешних ссылок недоступны в таблице внешних ссылок в книге, Excel не может вычислить правильно. Таким образом, Excel возвращает #REF! Ошибки. Это также относится к ссылкам DDE, которые недоступны в процессе пересчета. По умолчанию Excel сохраняет скрытую таблицу значений ссылок для вычислений. В некоторых сценариях значения внешних ссылок могут не совпадать с значениями, вычисляемыми в формулах. Кроме того, можно отключить функцию для хранения значений внешних ссылок в отдельной книге в параметрах Excel.

Обходной путь

Чтобы обойти эту проблему, используйте один из следующих методов.

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

  1. Не сохраняйте книгу. Вместо этого закройте книгу, не сохраняя ее. Все изменения, внесенные в книгу, будут отменяться.
  2. Для каждого источника внешней ссылки в книге, которую вы хотите открыть, убедитесь, что исходный файл доступен по пути, указанному в формуле ссылки. Если какие-либо источники ссылок больше не доступны, измените формулу ссылки, чтобы она указывала на альтернативный источник. Кроме того, можно окончательно удалить формулу ссылки, чтобы разорвать ссылку. Выполните действия, описанные в разделе "Обновление или удаление ссылок", чтобы изменить или удалить ссылки.
  3. Убедившись, что все источники ссылок доступны в определенных расположениях, откройте связанную книгу, в которых возникла проблема, в Excel. Разрешите Excel обновлять все внешние ссылки в книге при появлении запроса.
  4. После открытия связанной книги и подтверждения успешного обновления всех внешних ссылок и успешного пересчета книги в текущей версии Excel сохраните книгу. Теперь он должен открывать и обновлять ссылки, как ожидалось в текущей версии Excel.

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

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

    1. Закройте все книги.
    2. Создайте новую книгу.
    3. Нажмите кнопку Microsoft Office, а затем выберите Параметры Excel.
    4. На вкладке Формулы выберите Вручную в разделе Параметры вычисления, а затем нажмите кнопку ОК.
    5. Откройте сохраненную книгу.

  2. На вкладке Данные выберите Изменить ссылки в группе Connections.

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

  3. Выберите Проверить состояние , чтобы обновить состояние для всех ссылок в списке. Дождитесь обновления состояния всех ссылок.

  4. Просмотрите состояние в столбце Состояние , выберите ссылку и выполните одно из следующих действий:

    • Если состояние — ОК, никаких действий не требуется. Ссылка работает и является текущей.
    • Если для состояния задано значение Неизвестно, выберите Проверить состояние , чтобы обновить состояние для всех ссылок в списке.
    • Если состояние неприменимо, ссылка использует OLE или Динамический обмен данными (DDE). Excel не может проверка состояние этих типов ссылок.
    • Если состояние — Ошибка: источник не найден, выберите Изменить источник, а затем выберите соответствующую книгу для ссылки.
    • Если состояние — Ошибка: лист не найден, выберите Изменить источник, а затем выберите соответствующий лист в соответствующем файле. Источник может быть перемещен или переименован.
    • Если состояние — Предупреждение: значения не обновлены, выберите Обновить значения. Ссылка не была обновлена при открытии книги.
    • Если состояние — Предупреждение: Источник не пересчитан, выберите Открытый исходный код и нажмите клавишу F9, чтобы вычислить книгу. Книга может быть настроена на ручное вычисление в исходном файле. Чтобы задать для книги автоматическое вычисление, нажмите кнопку Microsoft Office, а затем выберите Параметры Excel. На вкладке Формулы выберите Автоматически в разделе Параметры вычисления.
    • Если состояние — Ошибка: Undefined или не прямоугольное имя, некоторые имена не могут быть разрешены до тех пор, пока не откроется исходная книга. Выберите Открытый исходный код, вернитесь к целевой книге, а затем выберите Проверить состояние. Если проблема не устранена, убедитесь, что имя не отсутствует или неправильно написано. Перейдите к исходной книге, перейдите на вкладку Формулы , выберите Определить имя и найдите имя.
    • Если состояние — Предупреждение: Открыть исходный код для обновления значений, выберите Открытый исходный код. Ссылка не может быть обновлена, пока не откроется исходный код.
    • Если состояние — Источник открыт, источник открыт. Никаких действий не требуется, если не возникают ошибки листа.
    • Если состояние — Значения, обновленные из имени файла, никаких действий не требуется. Значения обновлены.
    • Если состояние имеет значение Error: Status indeterminate, Excel не может определить состояние ссылки. Источник может не содержать листов. Кроме того, источник может быть сохранен в неподдерживаемом формате. Выберите Обновить значения.

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

    1. Нажмите кнопку Microsoft Office, а затем выберите Параметры Excel.

    2. На вкладке Формулы выберите Автоматически в разделе Параметры вычисления, а затем нажмите кнопку ОК.

      Теперь Excel должен вычислить книгу. Если вычисление выполнено успешно, сохраните книгу. Теперь книга должна открывать и обновлять ссылки, как ожидалось в текущей версии Excel.

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

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

Примечание.

При замене формулы ее значением Excel окончательно удаляет формулу.

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

  1. Откройте книгу в версии Excel, в которой книга была сохранена в последний раз. При появлении запроса на обновление ссылок выберите Нет. Так как файл был сохранен в последней версии Excel, ссылки не обновляются. Вычисление может выполняться с последним известным значением ссылки.
  2. Щелкните правой кнопкой мыши ячейку или диапазон ячеек, содержащих формулу, ссылающуюся на внешнюю ссылку, и выберите команду Копировать.
  3. Щелкните правой кнопкой мыши ту же ячейку или диапазон ячеек и выберите пункт Специальная вставка.
  4. В диалоговом окне Специальная вставка выберите Значения в разделе Вставить, а затем нажмите кнопку ОК.

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