Как исправить ошибку #REF! ошибка

Ошибка #REF! отображается, когда формула ссылается на недопустимую ячейку. Чаще всего это происходит, когда ячейки, на которые ссылаются формулы, удаляются или вставляются.

Пример — #REF! ошибка, вызванная удалением столбца

В следующем примере используется формула = SUM (B2, C2, D2) в столбце E.

Если бы вы удалили столбцы B, C или D, это вызвало бы #REF! ошибка. В этом случае мы удалим столбец C (Продажи 2007 г.), и формула будет выглядеть так: = SUM (B2, # REF!, C2) . Когда вы используете явные ссылки на ячейки, подобные этой (где вы ссылаетесь на каждую ячейку отдельно, через запятую) и удаляете указанную строку или столбец, Excel не может разрешить это, поэтому он возвращает #REF! ошибка. Это основная причина, по которой использование явных ссылок на ячейки в функциях не рекомендуется.

Решение

  • Если вы случайно удалили строки или столбцы, вы можете сразу нажать кнопку «Отменить» на панели быстрого доступа (или нажмите CTRL + Z), чтобы восстановить их.

  • Настройте формулу так, чтобы она использовала ссылку на диапазон вместо отдельных ячеек, например = SUM (B2: D2) . Теперь вы можете удалить любой столбец в пределах диапазона сумм, и Excel автоматически настроит формулу. Вы также можете использовать = SUM (B2: B5) для суммы строк.

Пример — ВПР с неправильными ссылками на диапазон

В следующем примере = VLOOKUP (A8, A2: D5,5, FALSE) вернет #REF ! ошибка, потому что он ищет значение для возврата из столбца 5, но диапазон ссылок — A: D, что составляет всего 4 столбца.

Решение

Отрегулируйте диапазон, чтобы он был больше, или уменьшите значение поиска столбца, чтобы оно соответствовало диапазону ссылок. = VLOOKUP (A8, A2: E5,5, FALSE) будет допустимым диапазоном ссылок, как и = VLOOKUP (A8, A2: D5,4, FALSE).

Пример — ИНДЕКС с неправильной ссылкой на строку или столбец

В этом примере формула = ИНДЕКС (B2 : E5,5,5) возвращает #REF! ошибка, потому что диапазон ИНДЕКС составляет 4 строки на 4 столбца, но формула просит вернуть то, что находится в 5-й строке и 5-м столбце.

Решение

Отрегулируйте ссылки на строки или столбцы так, чтобы они находились в диапазоне поиска INDEX. = INDEX (B2: E5,4,4) вернет допустимый результат.

Пример — ссылка на закрытую книгу with INDIRECT

В следующем примере функция INDIRECT пытается ссылаться на закрытую книгу, вызывая #REF! ошибка.

Решение

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

Проблемы OLE

Если вы использовали ссылку связывания и встраивания объектов (OLE), которая возвращает #REF! ошибка, затем запустите программу, которую вызывает ссылка.

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

Проблемы с DDE

Если вы использовали тему динамического обмена данными (DDE), которая возвращает #REF! ошибка, сначала убедитесь, что вы ссылаетесь на правильную тему. Если вы все еще получаете #REF! ошибка, проверьте настройки центра управления безопасностью для внешнего содержимого, как описано в разделе Блокировать или разблокировать внешнее содержимое в документах Office.

Примечание. Динамический обмен данными (DDE) — это установленный протокол для обмена данными между программами на базе Microsoft Windows.

Проблемы с макросами

Если макрос входит в функцию на лист, который ссылается на ячейку над функцией, а ячейка, содержащая функцию, находится в строке 1, функция вернет #REF! потому что над строкой 1 нет ячеек. Проверьте функцию, чтобы узнать, ссылается ли аргумент на недопустимую ячейку или диапазон ячеек. Для этого может потребоваться отредактировать макрос в редакторе Visual Basic (VBE), чтобы учесть эту ситуацию.

Нужна дополнительная помощь?

Вы всегда можете спросить эксперта в техническом сообществе Excel, получить поддержку в сообществе Answers или предложить новую функцию или улучшение в Excel User Voice.

См. Также

Обзор формул в Excel

Как избежать неправильных формул

Обнаруживать ошибки в формулах

Функции Excel (по алфавиту)

Функции Excel (по категориям)

Оцените статью
techsly.ru
Добавить комментарий