- Вопрос или проблема
- Ответ или решение
- Как предотвратить ошибки #REF появляющиеся в формулах при открытии ссылочной книги
- Проблема
- Сравнение
- Причины ошибки #REF
- Решения
- 1. Настройка доверительных параметров
- 2. Перепроверка и редактирование формул
- 3. Избегание использования INDIRECT()
- 4. Обновление ссылок вручную
- Заключение
Вопрос или проблема
В моей книге A есть формулы, которые ссылаются на мою книгу B. Как только я открываю книгу B и включаю редактирование, все формулы в книге A показывают имя листа книги B как #ССЫЛ (но путь и ссылки на ячейки остаются). Это невероятно раздражает, так как мне каждый раз нужно вручную изменять все эти #ССЫЛ обратно на имена листов, когда я хочу обновить книгу B.
Это не так для некоторых других книг, которые у меня есть, ссылающихся на внешние книги. В этих случаях, когда я открываю ссылочную книгу, я все равно получаю ошибки, но формулы физически не меняются. Затем, когда я заканчиваю редактирование ссылочной книги, я просто могу нажать “Обновить значения” в диалоговом окне “Правка ссылок”.
Что отличается между этими двумя ситуациями? Как я могу изменить первую описанную мной ситуацию на вторую? Почему Excel физически изменяет формулы, если структура ссылочной книги не меняется?
Я не хочу использовать формулы INDIRECT(), так как диапазоны, на которые я ссылаюсь в внешней книге, могут изменяться.
После того как я решил ту же проблему с помощью следующего метода:
- В меню Файл нажмите Параметры.
- В списке Параметры выберите Центр управления безопасностью.
- Нажмите Настройки центра управления безопасностью.
- В окне Центра управления безопасностью нажмите Защищенный просмотр.
- Затем снимите отметку с Включить защищенный просмотр для файлов, находящихся в небезопасных местах.
Это изменение настройки решит проблему с ошибкой #ССЫЛ!
Ответ или решение
Как предотвратить ошибки #REF появляющиеся в формулах при открытии ссылочной книги
Ошибки #REF в Excel могут значительно усложнять работу с формулами, особенно когда две рабочие книги связаны друг с другом. Рассмотрим, как избежать ситуации, когда при открытии одной книги (рабочей книги B) формулы в другой книге (рабочей книге A) начинают отображать ошибку #REF вместо правильных имён листов.
Проблема
Ваша проблема заключается в том, что при открытии рабочей книги B и включении редактирования все формулы в рабочей книге A обновляются и показывают #REF вместо названий листов, хотя путь и ссылки на ячейки остаются прежними. Это может вызвать недовольство, поскольку требует ручного исправления для каждой формулы.
Сравнение
Вы отметили, что у вас есть другие рабочие книги, которые также ссылаются на внешние книги, но они не подвергаются такой же проблеме. В этих случаях ошибки появляются, но сами формулы остаются неизменными до тех пор, пока вы не нажмёте "Обновить значения" в меню "Редактировать ссылки".
Причины ошибки #REF
Ошибка #REF появляется, когда Excel не может найти указанный элемент ссылки. Это может произойти по нескольким причинам:
- Изменение структуры работы: Если структура рабочей книги B изменилась (например, листы были переименованы или удалены), Excel может не знать, как обновить ссылки в рабочей книге A.
- Настройки безопасности: Опции защиты в Excel могут мешать его нормальной работе. Например, защищённый просмотр отключает некоторые функции, что может привести к ошибкам.
Решения
1. Настройка доверительных параметров
Одним из способов снизить вероятность ошибок #REF — это изменить настройки доверия:
- Перейдите в меню Файл и выберите Параметры.
- В списке параметров кликните на Центр управления.
- В открывшемся окне выберите Настройки центра управления.
- Перейдите к разделу Защищенный просмотр.
- Снимите галочку с параметра Включить защищённый просмотр для файлов, расположенных в небезопасных местах.
Эти изменения позволят Excel корректно обновлять ссылки без генерации ошибок #REF.
2. Перепроверка и редактирование формул
Если ошибки продолжают возникать даже после изменения настроек, вы можете вручную редактировать формулы в рабочей книге A. Убедитесь, что имена листов и другие ссылки актуальны.
3. Избегание использования INDIRECT()
Как вы сами отметили, формулы с функцией INDIRECT() могут не подойти в вашем случае, поскольку диапазоны в рабочей книге B могут изменяться. Лучше всего использовать прямые ссылки, которые будут менее подвержены изменению.
4. Обновление ссылок вручную
После внесения всех правок и корректировок, вы можете проверить и вручную обновить ссылки в меню "Редактировать ссылки". Это позволит вам избежать ошибок, которые могут возникнуть при автоматическом обновлении.
Заключение
Предотвращение ошибок #REF требует внимания к настройкам как Excel, так и структуры ваших рабочих книг. Настройка доверия, корректное редактирование формул и регулярная проверка актуальности ссылок помогут вам поддерживать чистоту и точность в ваших расчетах. Настоятельно рекомендую заранее проверять изменения в вашей рабочей книге B, чтобы минимизировать влияние на рабочую книгу A, а также регулярно сохраняйте резервные копии, чтобы избежать потери данных.