В этой статье описывается, как использовать функцию ЕСЛИОШИБКА в Excel для обнаружения ошибок и замены их пустой ячейкой, другим значением или конкретным сообщением. Мы покажем примеры того, как использовать функцию ЕСЛИ ОШИБКА с визуальными функциями и функциями сопоставления индексов, и как это сравнивается с ЕСЛИ ОШИБКА и ISND.
«Дайте мне точку опоры, и я переверну Землю», — сказал однажды Архимед. «Дайте мне формулу, и я заставлю ее вернуть ошибку», — сказал бы пользователь Excel. Здесь мы не будем рассматривать, как получить ошибки в Excel. Мы узнаем, как предотвратить их, чтобы ваши электронные таблицы были чистыми, а формулы — ясными и точными.
Итак, вот о чем мы будем говорить:
- Что означает функция Excel ЕСЛИОШИБКА
- Если ошибка, сбросить
- Если ошибка, показать сообщение
- 5 вещей, которые нужно знать о функции ЕСЛИОШИБКА в Excel
- ЕСЛИОШИБКА с ВПР
- Вложенные функции ЕСЛИОШИБКА для выполнения последовательных операций ВПР
- ЕСЛИОШИБКА в формулах массива
- ОШИБКА или ЕСЛИ + ОШИБКА?
- ЕСЛИОШИБКА против ЕСНД
- Рекомендации по использованию ЕСЛИОШИБКА в Excel
Что означает функция Excel ЕСЛИОШИБКА
Функция ЕСЛИОШИБКА (IFFERROR на английском языке) предназначена для обнаружения и устранения ошибок в формулах и вычислениях. Это означает, что функция ЕСЛИОШИБКА должна выполнить определенные действия, если увидит ошибку. Точнее, он проверяет формулу и, если вычисление возвращает ошибку, возвращает другое значение, которое вы ему передаете. Если все хорошо, он просто возвращает результат формулы.
Синтаксис функции Excel ЕСЛИОШИБКА следующий:
ЕСЛИОШИБКА(значение; значение_если_ошибка)
Где:
- Значение (обязательно) — что проверять на наличие ошибок. Это может быть формула, выражение или ссылка на ячейку.
- Value_if_error (обязательно) — что возвращать при обнаружении ошибки. Это может быть пустая строка (которая приведет к пустой ячейке), текстовое сообщение, числовое значение, другая формула или расчет.
Например, при делении двух столбцов чисел можно получить кучу разных ошибок, если в одном из столбцов есть пустые ячейки, нули или текст.
Рассмотрим простой пример:
Чтобы этого не произошло, используйте формулу ЕСЛИОШИБКА, чтобы перехватывать и обрабатывать их по своему усмотрению.
Если ошибка, то пусто
Укажите пустую строку ("") в аргументе value_if_error, чтобы возвращать пустую ячейку в случае возникновения ошибки:
=ОШИБКА(A4/B4; "")
Вернемся к нашему примеру и воспользуемся ЕСЛИОШИБКА:
Как видите, по сравнению с первым скриншотом вместо стандартных сообщений мы видим только пустые ячейки.
Если ошибка, то показать сообщение
Вы также можете отобразить собственное сообщение вместо стандартной записи ошибок Excel:
=ОШИБКА(A4/B4, "Ошибка вычисления")
Перед вами третья версия нашей небольшой таблицы.
5 фактов, которые нужно знать о функции ЕСЛИОШИБКА в Excel
- ЕСЛИОШИБКА в Excel обрабатывает все типы ошибок, включая #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF! и #ЗНАЧ!.
- В зависимости от содержимого аргумента значение_если_ошибка функция может заменить ошибку текстовым сообщением, числом, датой или логическим значением, результатом другой формулы или пустой строкой (пустой ячейкой).
- Если аргумент значения является пустой ячейкой, он обрабатывается как пустая строка ('''), но не как ошибка.
- ЕСЛИОШИБКА появилась в Excel 2007 и доступна во всех последующих версиях Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel 2021 и Excel 365.
- Чтобы отловить ошибки в Excel 2003 и более ранних версиях, используйте функцию ЕОШИБКА в сочетании с функцией ЕСЛИ, например:
=ЕСЛИ(ОШИБКА(A4/B4),"Ошибка вычисления",A4/B4)
Далее вы увидите, как использовать ЕСЛИОШИБКА в Excel в сочетании с другими функциями для выполнения более сложных задач.
ЕСЛИОШИБКА с функцией ВПР
Распространенной задачей в Excel является поиск нужного значения в таблице по определенным критериям. И этот поиск не всегда бывает успешным. Одно из наиболее распространенных применений функции ЕСЛИОШИБКА — сообщить пользователям, что искомое значение не существует в базе данных. Для этого оберните формулу ВПР в функцию ЕСЛИОШИБКА следующим образом:
ОШИБКА(ВПР(...),"Не найдено")
Если искомое значение не существует в просматриваемой таблице, стандартная формула ВПР вернет ошибку #Н/Д:
Чтобы лучше понять таблицу и улучшить ее внешний вид, заключите функцию ВПР в ЕСЛИОШИБКА и отобразите более удобное для пользователя сообщение:
=ОШИБКА(ПРОСМОТР(D3, $A$3:$B$5, 2, ЛОЖЬ), "Не найдено")
На снимке экрана ниже показан пример ЕСЛИОШИБКА вместе с ВПР в Excel:
Если вы хотите отловить только #Н/Д, но не все ошибки, используйте функцию ИСЧ, а не ЕСЛИОШИБКА. Он просто возвращает ИСТИНА или ЛОЖЬ в зависимости от возникновения ошибки #Н/Д. Поэтому нам все еще нужна функция ЕСЛИ для обработки этих логических значений:
=ЕСЛИ(КОНЕЦ(ВПР(D3, $A$3:$B$5, 2, ЛОЖЬ)), "Не найдено", ВПР(D3, $A$3:$B$5, 2, ЛОЖЬ))
Дополнительные примеры формул Excel ЕСЛИОШИБКА ВПР см в нашей статье Как удалить сообщение #Н/Д в ВПР?
Вложенные функции ЕСЛИОШИБКА для выполнения последовательных ВПР
В ситуациях, когда вам нужно выполнить несколько операций ВПР, в зависимости от того, была ли предыдущая ВПР успешной или неудачной, вы можете вложить две или более функции ЕСЛИОШИБКА.
Допустим, у вас есть несколько отчетов о продажах из региональных офисов вашей компании, и вы хотите получить сумму для определенного идентификатора заказа. С ячейкой B9 в качестве критерия поиска (номер заказа) и тремя небольшими справочными таблицами (таблицы 1, 2 и 3) формула выглядит следующим образом:
=ОШИБКА(КОНТУР ОШИБКИ(B9;A3:B6;2,0),ОШИБКА(КОНТУР ОШИБКИ(B9;D3:E6;2,0),ОШИБКА(РЕШЕНИЕ(B9;G3:H6;2,0)),"Не найдено")))
Результат будет выглядеть примерно так, как показано на рисунке ниже:
То есть, если поиск не удался (то есть ошибка) в первой таблице, мы начинаем искать во второй и так далее. Если нигде ничего не найдено, получаем сообщение «Не найдено».
ЕСЛИОШИБКА в формулах массива
Как вы, наверное, знаете, формулы массива в Excel предназначены для выполнения нескольких вычислений в рамках одной формулы. Если вы укажете формулу или выражение, которое возвращает массив в аргументе значения функции ЕСЛИОШИБКА, она также обработает и вернет массив значений для каждой ячейки в указанном диапазоне. Пример ниже поможет прояснить это.
Допустим, у вас есть количество в столбце B и цена в столбце C, и вы хотите рассчитать количество. Это можно сделать с помощью следующей формулы массива, которая делит каждую ячейку в диапазоне B2:B4 на соответствующую ячейку в диапазоне C2:C4, а затем суммирует результаты:
=СУММ(($B$2:$B$4/$C$2:$C$4))
Формула работает нормально, пока в диапазоне делителей нет нулей или пустых ячеек. Если есть хотя бы одно значение 0 или пустая строка, возвращается ошибка: #DIV/0! Из-за одной неправильной позиции мы не можем получить окончательный результат.
Чтобы исправить эту ситуацию, просто поставьте деление в формулу ЕСЛИОШИБКА:
=СУММ(ОШИБКА($B$2:$B$4/$C$2:$C$4,0))
Что делает эта формула? Делит значение в столбце B на значение в столбце C в каждой строке (3500/100, 2000/50 и 0/0) и возвращает массив результатов {35; 40; #ДЕЛ/0!}. Функция ЕСЛИОШИБКА перехватывает все #DIV/0! и заменяет их нулями. Затем функция СУММ складывает значения в результирующий массив {35; 40; 0} и отображает окончательный результат (35+40=75).
Примечание. Помните, что ввод формулы массива необходимо завершать нажатием комбинации Ctrl+Shift+Enter (если только у вас не Office365 или Excel2021 — они понимают формулы массива без лишних телодвижений).
ЕСЛИОШИБКА или ЕСЛИ + ЕОШИБКА?
Теперь, когда вы знаете, как использовать функцию ЕСЛИОШИБКА в Excel, вам может быть интересно, почему некоторые люди до сих пор не используют комбинацию ЕСЛИ + ЕОШИБКА. Есть ли у этого старого метода какие-либо преимущества перед IFERROR?
В старые недобрые времена Excel 2003 и более ранних версий, когда ЕСЛИОШИБКА не существовало, совместное использование ЕСЛИ и ЕОШИБКИ было единственным возможным способом обнаружения ошибок. Это просто немного более сложный способ добиться того же результата.
Например, чтобы отловить ошибки ВПР, вы можете использовать любую из следующих формул.
В Excel 2007 - Excel 2016:
ОШИБКА(ВПР(...), "Не найдено")
Во всех версиях Excel:
ЕСЛИ(ОШИБКА(ПРОСМОТР(...)), "Не найдено", ВПР(…))
Обратите внимание, что в формуле IF ERROR VLOOKUP необходимо дважды запустить VLOOKUP. Чтобы лучше понять, давайте расшифруем: если ВПР приводит к ошибке, верните «Не найдено», иначе выведите результат ВПР.
А вот простой пример формулы Excel ЕСЛИ ОШИБКА ВПР:
=ЕСЛИ(ОШИБКА(ВПР(D2; A2:B5,2;ЛОЖЬ)), "Не найдено", ВПР(D2; A2:B5,2;ЛОЖЬ))
ЕСЛИОШИБКА против ЕСНД
Представленная в Excel 2013 функция IFNA (IFNA на английском языке) — это еще одна функция для проверки формулы на наличие ошибок. Синтаксис аналогичен ЕСЛИОШИБКА:
ЕСНД(значение; значение_если_НД)
Чем ЕСНД отличается от ЕСЛИОШИБКА? Функция ISN отлавливает только ошибки #N/A, тогда как ЕСЛИОШИБКА обрабатывает все типы ошибок.
В каких ситуациях вы можете использовать ESND? Когда нецелесообразно скрывать все ошибки. Например, при работе с конфиденциальными данными вы можете захотеть получать предупреждения о возможных ошибках в вашем наборе данных (случайное деление на ноль и т д.), а стандартные сообщения об ошибках Excel со знаком «#» могут быть яркими визуальными индикаторами проблем.
Давайте посмотрим, как вы можете создать формулу, которая отображает «Не найдено» вместо ошибки «Н/Д», которая возникает, когда искомое значение отсутствует в наборе данных, но вы все равно видите все другие ошибки Excel.
Предположим, вы хотите получить Count из таблицы поиска в таблицу результатов, как показано на рисунке ниже. Самый простой способ — использовать ЕСЛИОШИБКА с функцией ВПР. Таблица выглядела бы хорошо, но не только ошибки поиска, но и все остальные ошибки были бы скрыты за надписью «Не найдено». И мы не заметим, что в исходной справочной таблице у нас есть ошибка, равная части нуля, потому что цена персика не заполнена. Поэтому разумнее использовать ESND для обработки только ошибок поиска с помощью:
=ESND(ВПР(F3, $A$3:$D$6, 4, ЛОЖЬ), "Не найдено")
Или подойдет комбинация ЕСЛИ ОНД для старых версий Excel:
=ЕСЛИ(КОНЕЦ(ВПР(F3, $A$3:$D$6, 4, ЛОЖЬ)), "Не найдено", ВПР(F3, $A$3:$D$6, 4, ЛОЖЬ))
Как видите, формула SSIT с функцией ВПР возвращает «Не найдено» только для продукта, которого нет в таблице поиска (сливы). Для персика отображается #DIV/0! что указывает на то, что наша таблица поиска содержит ошибку части нуля.
Рекомендации по использованию ЕСЛИОШИБКА в Excel
Итак, вы уже знаете, что функция ЕСЛИОШИБКА — это самый простой способ отлавливать ошибки в Excel и маскировать их пустыми ячейками, нулевыми значениями или вашими собственными сообщениями. Однако это не означает, что вы должны обернуть каждую формулу функцией обработки ошибок.
Эти простые рекомендации помогут вам сохранить равновесие.
- Не ловите ошибки без уважительной причины.
- Оберните ЕСЛИОШИБКА только в той части формулы, где, по вашему мнению, могут возникнуть проблемы.
- Чтобы обрабатывать только определенные ошибки, используйте другую функцию обработки ошибок с меньшей областью действия:
- ECND или IF END только для обнаружения ошибок #H/A.
- EOSH для обнаружения всех ошибок, кроме #N/A.
Мы попытались рассказать вам, как использовать функцию ЕСЛИОШИБКА в Excel. Примеры перехвата и обработки ошибок могут быть полезны как «чайникам», так и более опытным пользователям.
Мы также рекомендуем:
ВПР нескольких значений по нескольким условиям. В этой статье показано, как выполнять поиск (ВПР) нескольких значений в Excel на основе одного или нескольких условий и возвращать несколько результатов в столбце, строке или отдельной ячейке. Когда вы используете Microsoft…
Функция MAX в Excel: как найти максимальное значение. В этой статье объясняется функция MAX со множеством примеров формул, которые показывают, как найти наибольшее значение в Excel и выделить наибольшее число в электронной таблице. MAX — один из самых простых…
Как объединить две или более таблиц в Excel. В этом руководстве вы найдете несколько приемов объединения таблиц Excel путем сопоставления данных в одном или нескольких столбцах. Как часто при анализе в Excel вся необходимая информация собирается на одном…
Как сравнить два столбца на наличие совпадений и различий — прочтение этой статьи займет у вас около 10 минут, а в следующие 5 минут (или даже быстрее) вы сможете легко сравнить два столбца Excel на наличие дубликатов и выделить найденные…
Сравнение ячеек в Excel. Вы узнаете, как сравнивать значения в ячейках Excel на предмет точного совпадения или нечувствительности к регистру. Мы предложим вам несколько формул для сопоставления двух ячеек по их значениям, длине или количеству…
2 способа извлечения данных из разных таблиц с помощью ВПР. - Задача: данные, которые нужно найти и извлечь с помощью функции ВПР, находятся в нескольких таблицах. Эти таблицы имеют одинаковую структуру (то есть одни и те же столбцы, помещенные в одни и те же…
Вычислить номер столбца для извлечения данных в ВПР - Задача: Самый простой способ научиться указывать столбец, из которого функция ВПР должна извлекать данные. При этом мы не будем менять саму формулу, так как это может привести к случайным ошибкам.…
4 способа создать левую ВПР в Excel. - Функция ВПР — одна из самых популярных, когда нужно найти и извлечь данные из таблицы. Однако у него есть существенный недостаток. Она ищет в крайнем левом углу…
Формула ВПР в Excel для сравнения двух таблиц — 4 способа — Сравнение таблиц — задача, которую Excel приходится решать довольно часто. Например, у нас есть старый прайс-лист и его новая версия. Вам нужно посмотреть, какие цены изменились и…
Почему ВПР не работает в Excel? - Функция ВПР — очень мощный инструмент поиска. Но если по какой-то причине это не удастся, вы получите сообщение об ошибке #N/A (#N/A на английском языке). Попробуем вместе... Александр Трифунтов2023-02-09T12:13:05+03:009 Февраль 2023 Комментариев нет
