Если вы какое-то время работали с формулами в Excel, я уверен, что вы уже встречались с # REF! Ошибка (справочная ошибка). Поскольку это довольно распространенное явление, я подумал о написании учебного пособия только для решения проблемы #REF! ошибка.

В этом руководстве по Excel я расскажу, что такое #REF! ошибка, что ее вызывает и как ее исправить.

Что такое #REF! Ошибка в Excel?

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

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

Теперь, поскольку формула (которая относилась к диапазону до его удаления) не знает, куда указывать, эта предыдущая ссылка в формуле изменится на # REF!.

Это в свою очередь. заставляет формулу возвращать #REF! ошибка результата формулы.

Пример #REF! Ошибка

Позвольте мне показать вам простой пример, на котором мы получаем справочную ошибку, а позже я расскажу, как ее найти и исправить.

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

Все идет нормально!

Теперь, если я удалю один из столбцов, в формуле не будет ссылки на удаленную ячейку, поэтому она даст мне # REF! ошибка (как показано ниже)

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

Справочные ошибки довольно распространены, и вы можете выполнить быструю проверку, прежде чем использовать набор данных в расчетах (при которых может возникнуть ошибка #REF!).

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

Найдите #REF! Ошибка при использовании функции «Найти и заменить»

Предположим, у вас есть набор данных, показанный ниже, в котором есть пара опорных ошибок.

Ниже приведены шаги по поиску и выделению всех ячеек с «ссылочными ошибками»:

  • Выберите весь набор данных, который вы хотите проверить
  • Удерживая клавишу Control, нажмите клавишу F (или Command + F, если вы используете Mac). Откроется диалоговое окно «Найти и заменить».
  • В поле «Найти» введите #REF!
  • Нажмите кнопку «Найти все». Это найдет все ячейки с #REF! ошибка
  • Удерживая клавишу Control, нажмите клавишу A (или Command + A, если вы используете Mac). При этом будут выбраны все ячейки с ошибкой ссылки.
  • Закройте диалоговое окно «Найти и заменить».

Вышеупомянутые шаги найдут и затем выберут все ячейки с #REF! ошибка и выберите их.

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

Давайте посмотрим на некоторые из возможных способов справиться с ошибкой ссылки.

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

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

  • Удалите ошибку : просто нажмите клавишу удаления, и она удалит эту ошибку, и вместо этого у вас будут пустые ячейки.
  • Заменить значением или текстом : вы можете заменить ошибку 0, тире или любым другим значимым текстом. Для этого просто введите то, чем вы хотите заменить ошибку, удерживайте клавишу Control, а затем нажмите клавишу Enter. Это введет текст, который вы ввели во все выбранные ячейки.
  • Выделите эти ячейки с помощью параметра цвета ячеек на вкладке Главная на ленте.

Примечание. Когда вы используете метод «Найти и заменить», он найдет только те ячейки, у которых есть #REF! ошибка, так как это то, что мы искали в поле «Найти». Если есть другие ошибки (например, ошибка #NA или #DIV!), Они не будут обнаружены (если вы не повторите те же шаги и для этих ошибок.

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

Найдите ошибки, используя специальный параметр «Перейти»

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

Хорошая сторона этого метода заключается в том, что он найдет и выберет все ячейки с ошибками любого типа (включая ошибки ссылок). Но недостатком этого метода является то, что он будет находить и выбирать только те ячейки, в которых ошибка возникает из-за формулы. Если ошибка присутствует в виде текста, этот метод не сможет ее найти.

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

Ниже приведены шаги по использованию опции «Перейти к специальному», чтобы найти и выбрать все ошибки:

  • Выберите весь набор данных, в котором вы хотите проверить наличие ошибок
  • Перейдите на вкладку «Главная»
  • В группе «Редактирование» нажмите «Найти и выбрать».
  • В появившихся опциях нажмите на опцию «Перейти к специальному».
  • В диалоговом окне «Перейти к специальному» выберите параметр «Формулы».
  • В параметрах формулы снимите все флажки, кроме флажка Ошибка.
  • Нажмите ОК.

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

После того, как вы выбрали эти ячейки, вы можете удалить их, выделить или заменить на 0, тире или другой значимый текст.

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

Итак, это два быстрых метода, которые вы можете использовать для поиска и исправления ошибки #REF (справочная ошибка) в Excel.

Надеюсь, вы нашли этот урок полезным.

#REF! Ошибка в Excel — как исправить справочную ошибку!