Почему слетают ссылки в excel на внешние файлы
Перейти к содержимому

Почему слетают ссылки в excel на внешние файлы

  • автор:

Почему слетают ссылки в excel на внешние файлы

Argument ‘Topic id’ is null or empty

Сейчас на форуме

© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru

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

ООО «Планета Эксел»
ИНН 7735603520
ОГРН 1147746834949
ИП Павлов Николай Владимирович
ИНН 633015842586
ОГРНИП 310633031600071

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

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

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

      Окончательно удалите формулу ссылки, а затем замените ее значением.

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

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

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

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

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

      Обратная связь

      Были ли сведения на этой странице полезными?

      Исправление недействительных связей с данными

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

      Важно: связанный объект гиперссылки — это не одно и то же. Следующая процедура не позволит исправить неправиленные гиперссылки. Дополнительные информацию о гиперссылках см. в теме «Создание и изменение гиперссылки».

      Исправление неправиленной ссылки

      Внимание: Это действие нельзя отменить. Перед началом этой процедуры может потребоваться сохранить резервную копию книги.

      1. Откройте книгу, которая содержит неверную связь.
      2. На вкладке «Данные» нажмите кнопку «Изменить связи». Команда «Изменить связи» недоступна, если книга не содержит ссылок.
      3. В поле «Исходный файл» выберите неправиленную ссылку, которую вы хотите исправить.

      Примечание: Чтобы исправить несколько ссылок, щелкните каждую из

      Удаление неявной ссылки

      При разрыве связи все формулы, которые ссылаются на исходный файл, преобразуются в их текущее значение. Например, если формула =СУММ([Budget.xls]Годовой! C10:C25) — 45, после того как связь не будет нарушена, формула будет преобразована в 45.

      1. Откройте книгу, которая содержит неверную ссылку.
      2. На вкладке «Данные» нажмите кнопку «Изменить связи». Команда «Изменить связи» недоступна, если книга не содержит ссылок.
      3. В поле «Исходный файл» выберите ненужную ссылку, которую нужно удалить.

      Примечание: Чтобы удалить несколько ссылок, щелкните каждую из

      , удерживая нажатой кнопку мыши.

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

      Исправление неправиленной ссылки

      Внимание: Это действие нельзя отменить. Перед началом этой процедуры может потребоваться сохранить резервную копию книги.

      1. Откройте книгу, которая содержит неверную связь.
      2. В меню Правка выберите пункт Связи. Если книга не содержит ссылок, команда «Ссылки» недоступна.
      3. В поле «Исходный файл» щелкните неправиленную ссылку, которую нужно исправить.

      Примечание: Чтобы исправить несколько ссылок, щелкните каждую из

      Задача Необходимые действия
      Исправление неправиленной ссылки на лист в другой книге В диалоговом окне «Открытие» найдите книгу и нажмите кнопку «Изменить».
      Исправление неправиленной ссылки на книгу или другой документ Office Введите новое имя или расположение документа в поле «Изменить ссылки на текстовое поле» и нажмите кнопку «ОК».

      Отключение автоматического обновления связанных данных

      1. Откройте книгу, которая содержит неверную связь.
      2. В меню Правка выберите пункт Связи. Если книга не содержит ссылок, команда «Ссылки» недоступна.
      3. В поле «Исходный файл» щелкните неправиленную ссылку, которую нужно исправить.

      Примечание: Чтобы исправить несколько ссылок, щелкните каждую из

      Удаление неявной ссылки

      При разрыве связи все формулы, ссылаясь на исходный файл, преобразуются в их текущее значение. Например, если формула =СУММ([Budget.xls]Годовой! C10:C25) — 45, после того как связь не будет нарушена, формула будет преобразована в 45.

      1. Откройте книгу, которая содержит неверную связь.
      2. В меню Правка выберите пункт Связи. Если книга не содержит ссылок, команда «Ссылки» недоступна.
      3. В поле «Исходный файл» щелкните ненужную ссылку, которую нужно удалить.

      Примечание: Чтобы удалить несколько ссылок, щелкните каждую из

      , удерживая нажатой кнопку мыши.

      Описание управления ссылками на книги и хранения в Excel

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

      Как обрабатываются пути ссылки на книги при открытии целевой книги

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

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

      Относительные и абсолютные ссылки

      По возможности ссылки на исходные книги создаются относительно. Это означает, что полный путь к исходной книге записывается не, а часть пути, связанная с целевой книгой. С помощью этого метода можно перемещать книги, не прерывая связи между ними. Однако ссылки на книги остаются нетронутыми, только если книги остаются в том же расположении относительно друг друга. Например, если целевая книга C:\Mydir\Destination.xlsx, а исходная книга — C:\Mydir\Files\Source.xlsx, можно переместить файлы на диск D, если исходная книга по-прежнему находится во вложенной папке с именем «Файлы».

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

      Три типа конфигураций хранилища ссылок книг

      Способ, с помощью которого создаются ссылки на книги, зависит от следующих способов:

      Тип хранилища 1: тот же диск с той же папкой или дочерней папкой

      Исходная книга находится в той же или дочерней папке, что и целевая книга. В этом случае мы сохраняем относительный путь к файлу, например вложенную папку/source.xlsx и destination.xlsx.

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

      Тип хранилища 2: тот же диск, но с разными одноуровневыми папками

      Исходные и целевые книги находятся на одном диске, но в разных папках одноуровневых элементов. В этом случае мы сохраняем путь относительно сервера, например /root/parent/sibling1/source.xlsx и /root/parent/sibling2/destination.xlsx.

      Этот тип лучше всего подходит, если целевая книга перемещается на том же диске, но исходная книга остается в том же расположении.

      Тип хранилища 3: разные диски

      Исходная книга находится на диске, отличном от целевой книги. Например, конечная папка книги находится на диске C, а исходная папка книги — на диске H. В этом случае мы сохраняем абсолютный путь, например H:\folder\source.xlsx или https://tenant.sharepoint.com/teams/site/folder/source.xlsx.

      Этот тип лучше всего подходит, если целевая книга перемещена, но исходная книга остается в том же расположении. При этом предполагается, что целевая книга по-прежнему может получить доступ к исходной книге.

      Основные сведения о папках XLStart и Office

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

      Excel распознает две папки XLStart по умолчанию, из которых автоматически открываются файлы при запуске. Эти две папки:

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

      Имя папки Office изменяется в разных версиях Office. Например, имя папки Office может быть Office14, Office15 или Office16 в зависимости от версии Office, которую вы используете. Это изменение имени папки приводит к разрыву ссылок на книги при переходе на компьютер под управлением версии Excel, отличной от версии, в которой была установлена ссылка.

      • Папка XLStart , которая находится в папке установки Office, например C:\Program Files\Microsoft Office\\XLStart
      • Папка XLStart, которая находится в профиле пользователя, например C:\Documents and Settings\\Application Data\Microsoft\Excel\XLStart

      Общие сведения о сопоставленных дисках и UNC

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

      Кроме того, ссылка на книгу, отображаемая в Excel, может отображаться по-разному в зависимости от способа открытия книги. Ссылка на книгу может показаться соответствующей корневой UNC-папке или букве корневого диска, которая использовалась для открытия файла.

      Сценарии, которые могут привести к неправильной работе ссылок на книги

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

      1. Вы сопоставляете диск под корнем общей папки. Например, вы сопоставляете диск Z с \\MyServer\MyShare\MyFolder1.
      2. После открытия целевой книги на сопоставленном диске создается ссылка на исходную книгу, которая хранится в сопоставленном расположении.
      3. Вы открываете целевую книгу по пути UNC.
      4. В результате ссылка на книгу будет нарушена.

      Если вы закроете целевую книгу, не сохранив ее, ссылки на нее не будут изменены. Однако если вы сохраните целевую книгу перед ее закрытием, вы сохраните ссылки на книгу с текущим неработаным путем. Папки между корнем общей папки и сопоставленной папкой останутся вне пути. В приведенном выше примере ссылка изменится на \\MyServer\MyFolder1. Иными словами, имя общего ресурса исключается из пути к файлу.

      1. Вы сопоставляете диск под корнем общей папки. Например, вы сопоставляете диск Z с \\MyServer\MyShare\MyFolder1.
      2. Файл открывается по пути UNC или сопоставленному диску, сопоставленному с другой папкой в общей папке, например \\MyServer\MyShare\MyFolder2.
      3. В результате ссылка на книгу будет нарушена.

      Если вы закроете целевую книгу, не сохранив ее, ссылки на нее не будут изменены. Однако если вы сохраните целевую книгу перед ее закрытием, вы сохраните ссылки на книгу с текущим неработаным путем. Папки между корнем общей папки и сопоставленной папкой останутся вне пути. В приведенном выше примере ссылка изменится на \\MyServer\MyFolder1. Иными словами, имя общего ресурса исключается из пути к файлу.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *