=ЕСЛИОШИБКА(B2/C2;0) (по-английски IFERROR) возвращает результат B2/C2 или 0, когда этот результат ошибка. Первый аргумент это нужная вам формула; второй это то, что показать вместо любой ошибки, которую она выдаст. Таблицы на этой странице показывают английскую запись, но формулы в них можно вводить и по-русски, с точкой с запятой.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product | Revenue | Units | Plain | With IFERROR |
| 2 | Pens | $120 | 80 | $1.50 | $1.50 |
| 3 | Paper | $300 | 50 | $6.00 | $6.00 |
| 4 | Ink | $90 | 0 | #DIV/0! | $0.00 |
| 5 | Tape | $45 | 30 | $1.50 | $1.50 |
| 6 | Clips | $0 | 0 | #DIV/0! | $0.00 |
=ЕСЛИОШИБКА(B2/C2;0)У Ink и Clips 0 единиц, поэтому простое деление в столбце D показывает #DIV/0! (в русском Excel #ДЕЛ/0!; таблицы здесь показывают ошибки под английскими именами). Столбец E показывает для них $0.00, а для всех остальных строк обычную цену. Введите 15 в C4, и оба столбца покажут цену Ink.
Синтаксис ЕСЛИОШИБКА
=IFERROR(value, value_if_error)
value(значение) это формула, которую нужно вычислить.value_if_error(значение_если_ошибка) возвращается, когдаvalueдаёт любую ошибку: #Н/Д (#N/A), #ЗНАЧ! (#VALUE!), #ССЫЛКА! (#REF!), #ДЕЛ/0! (#DIV/0!), #ЧИСЛО! (#NUM!), #ИМЯ? (#NAME?), #ПУСТО! (#NULL!) и более новые, например #ВЫЧИСЛ! (#CALC!).- Если
valueне ошибка, ЕСЛИОШИБКА возвращает его без изменений.
Заменой может быть число (0), текст ("Not found"), пустой текст ("") или другая формула, например второй поиск в другой таблице: =ЕСЛИОШИБКА(ВПР(E2;A2:C6;3;ЛОЖЬ);ВПР(E2;G2:I6;3;ЛОЖЬ)).
ЕСЛИОШИБКА с ВПР
Поиск возвращает #Н/Д, когда значения нет в таблице. Если обернуть его в ЕСЛИОШИБКА, вместо ошибки появится сообщение:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Look for | Price | |
| 2 | Apple | Fruit | $1.20 | Pear | $1.50 | |
| 3 | Pear | Fruit | $1.50 | Kiwi | Not found | |
| 4 | Carrot | Vegetable | $0.80 | Milk | $1.10 | |
| 5 | Bread | Bakery | $2.40 | |||
| 6 | Milk | Dairy | $1.10 |
=ЕСЛИОШИБКА(ВПР(E2;$A$2:$C$6;3;ЛОЖЬ);"Not found")Kiwi в списке нет, поэтому F3 говорит Not found. Введите Kiwi в A4 вместо Carrot, и F3 его найдёт. С ПРОСМОТРX (XLOOKUP) ЕСЛИОШИБКА для этого не нужна, потому что её четвёртый аргумент и есть значение «не найдено»: =ПРОСМОТРX(E2;A2:A6;C2:C6;"Not found").
ЕСНД: перехватывать только #Н/Д
ЕСНД (IFNA) работает как ЕСЛИОШИБКА, но заменяет только #Н/Д. Для поиска обычно именно это и нужно: #Н/Д означает «не найдено», это нормальный ответ, а любая другая ошибка означает, что неверна сама формула. В этой таблице формулы просят столбец 4 из таблицы в три столбца, это опечатка:
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Look for | IFERROR | IFNA | |
| 2 | Apple | Fruit | 1.2 | Pear | Not found | #REF! | |
| 3 | Pear | Fruit | 1.5 | ||||
| 4 | Carrot | Vegetable | 0.8 | ||||
| 5 | Bread | Bakery | 2.4 | ||||
| 6 | Milk | Dairy | 1.1 |
=ЕСЛИОШИБКА(ВПР(E2;$A$2:$C$6;4;ЛОЖЬ);"Not found")Pear в таблице есть, но F2 говорит Not found: ЕСЛИОШИБКА превратила #ССЫЛКА! (в таблице #REF!) от неверного номера столбца в то же сообщение, что и для отсутствующего товара. G2 пропускает #REF!, и вы видите, что формула сломана. Замените 4 на 3 в G2, и она вернёт 1.5. ЕСНД нужен Excel 2013 или новее.
Пустая ячейка вместо ошибки
Чтобы ничего не показывать, используйте в качестве замены пустой текст, две двойные кавычки:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Month | Last year | This year | Growth |
| 2 | Jan | 200 | 240 | 20% |
| 3 | Feb | 0 | 150 | |
| 4 | Mar | 180 | 171 | -5% |
| 5 | Apr | 90 | ||
| 6 | May | 250 | 300 | 20% |
=ЕСЛИОШИБКА((C2-B2)/B2;"")В Feb и Apr продаж в прошлом году не было, поэтому их рост посчитать нельзя, и ячейка остаётся пустой. Остальные месяцы показывают 20%, минус 5% и 20%. В ячейке с "" хранится текст: СУММ и СРЗНАЧ её пропускают, но =D3*2 даёт #ЗНАЧ!. Если дальше по столбцу считают арифметику, возвращайте вместо этого 0.
Практика: поиск с запасным вариантом
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Product | Stock | Look for | Stock | ||
| 2 | Apple | 40 | Kiwi | |||
| 3 | Pear | 25 | ||||
| 4 | Carrot | 60 | ||||
| 5 | Bread | 12 | ||||
| 6 | Milk | 30 |
Ваша очередь: В F2 найдите остаток товара из E2 в A2:B6 и покажите "Not found", когда его нет в списке.
Почему, скрывая все ошибки, можно скрыть промахи
ЕСЛИОШИБКА ничего не исправляет; она решает, что покажет ячейка. Прежде чем оборачивать в неё формулу:
- Выясните, откуда ошибка. Когда #ДЕЛ/0! вызвана пустой ячейкой Units, настоящее решение может быть в данных, которые кто-то должен ввести, а не в нулевой цене.
- Для поиска выбирайте ЕСНД, чтобы неверный номер столбца (#ССЫЛКА!), опечатка в имени функции (#ИМЯ?) или текст в числовом столбце (#ЗНАЧ!) оставались видны.
- При делении проверяйте конкретный случай.
=ЕСЛИ(C2=0;0;B2/C2)обрабатывает нулевой делитель и больше ничего; неверная ссылка в B2 по-прежнему покажет свою ошибку. Страница об ошибке #ДЕЛ/0! сравнивает оба подхода. - Выбирайте замену, которую нельзя принять за данные. 0 в столбце цен выглядит как настоящая цена и снижает среднее;
""или «Not found» так не выглядят.
Оборачивайте формулу в последнюю очередь, когда она уже даёт верный результат в тех строках, где должна работать.
Часто задаваемые вопросы
Как использовать ЕСЛИОШИБКА с ВПР?
Оберните поиск: =ЕСЛИОШИБКА(ВПР(E2;A2:C6;3;ЛОЖЬ);"Not found"). Когда E2 нет в первом столбце, ячейка показывает Not found вместо #Н/Д. =ЕСНД(ВПР(E2;A2:C6;3;ЛОЖЬ);"Not found") делает то же самое, но по-прежнему показывает другие ошибки.
Как сделать, чтобы ЕСЛИОШИБКА возвращала пустую ячейку?
Укажите вторым аргументом пустой текст: =ЕСЛИОШИБКА(B2/C2;""). Ячейка выглядит пустой, но в ней текст, поэтому =D2+1 по ней даёт #ЗНАЧ!; СУММ и СРЗНАЧ её пропускают.
Чем ЕСЛИОШИБКА отличается от ЕСНД?
ЕСЛИОШИБКА заменяет любую ошибку: #Н/Д, #ДЕЛ/0!, #ЗНАЧ!, #ССЫЛКА!, #ИМЯ?, #ЧИСЛО! и #ПУСТО!. ЕСНД заменяет только #Н/Д, «не найдено» при поиске, и показывает все остальные ошибки, так что сломанная формула не прячется.
Как заменить #Н/Д на 0 в Excel?
Оберните формулу в ЕСНД со значением 0: =ЕСНД(ВПР(E2;A2:C6;3;ЛОЖЬ);0). У ПРОСМОТРX замена встроена в четвёртый аргумент: =ПРОСМОТРX(E2;A2:A6;C2:C6;0).
В каких версиях Excel есть ЕСЛИОШИБКА и ЕСНД?
ЕСЛИОШИБКА есть с Excel 2007, ЕСНД с Excel 2013. В старых файлах встречается =ЕСЛИ(ЕОШИБКА(B2/C2);0;B2/C2): она делает ту же работу, что ЕСЛИОШИБКА, но вычисляет формулу дважды.
