Есть ошибка excel

Функцию ЕСЛИERROR можно использовать для перебора и обработки ошибок в формуле. Если же формула возвращает значение, определяемую формулой, возвращается ошибка; в противном случае возвращается результат формулы.

Синтаксис

ЕСЛИОШИБКА(значение;значение_если_ошибка)

Аргументы функции ЕСЛИОШИБКА описаны ниже.

• значение    Обязательный аргумент. Проверяемая на ошибку аргумент.

• value_if_error    — обязательный аргумент. Значение, возвращаемая, если формула возвращает ошибку. Вычисляются следующие типы ошибок: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?или #NULL!.

Замечания

• Если значение или value_if_error пустая ячейка, то если ЕСЛИЕROR рассматривает его как пустую строковую строку («»).

• Если значение является формулой массива, то функции ЕСЛИERROR возвращают массив результатов для каждой ячейки в диапазоне, указанном в значении. См. второй пример ниже.

Примеры

Скопируйте данные из таблицы ниже и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — ВВОД.

Котировка	Единиц продано
210	35
55	0
	23
Формула	Описание	Результат
=ЕСЛИОШИБКА(A2/B2;»Ошибка при вычислении»)	Выполняет проверку на предмет ошибки в формуле в первом аргументе (деление 210 на 35), не обнаруживает ошибок и возвращает результат вычисления по формуле	6
=ЕСЛИОШИБКА(A3/B3;»Ошибка при вычислении»)	Выполняет проверку на предмет ошибки в формуле в первом аргументе (деление 55 на 0), обнаруживает ошибку «деление на 0» и возвращает «значение_при_ошибке»	Ошибка при вычислении
=ЕСЛИОШИБКА(A4/B4;»Ошибка при вычислении»)	Выполняет проверку на предмет ошибки в формуле в первом аргументе (деление «» на 23), не обнаруживает ошибок и возвращает результат вычисления по формуле.	0

Пример 2

Котировка	Единиц продано	Отношение
210	35	6
55	0	Ошибка при вычислении
	23	0
Формула	Описание	Результат
=C2	Выполняет проверку на предмет ошибки в формуле в первом аргументе в первом элементе массива (A2/B2 или деление 210 на 35), не обнаруживает ошибок и возвращает результат вычисления по формуле	6
=C3	Выполняет проверку на предмет ошибки в формуле в первом аргументе во втором элементе массива (A3/B3 или деление 55 на 0), обнаруживает ошибку «деление на 0» и возвращает «значение_при_ошибке»	Ошибка при вычислении
=C4	Выполняет проверку на предмет ошибки в формуле в первом аргументе в третьем элементе массива (A4/B4 или деление «» на 23), не обнаруживает ошибок и возвращает результат вычисления по формуле	0
	Примечание. Если у вас есть текущая версия Microsoft 365 ,вы можете ввести формулу в левую верхнюю ячейку диапазона выходных данных, а затем нажать ввод, чтобы подтвердить формулу как формулу динамического массива. В противном случае формула должна быть введена как формула массива устаревшей. Для этого сначала выберем диапазон вывода, введите формулу в левую верхнюю ячейку диапазона, а затем нажмите CTRL+SHIFT+ВВОД, чтобы подтвердить ее. Excel автоматически вставляет фигурные скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.

Дополнительные сведения

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

Функция ЕСЛИОШИБКА – примеры формул

В статье описано, как использовать функцию ЕСЛИОШИБКА в Excel для обнаружения ошибок и замены их пустой ячейкой, другим значением или определённым сообщением. Покажем примеры, как использовать функцию ЕСЛИОШИБКА с функциями визуального просмотра и сопоставления индексов, а также как она сравнивается с ЕСЛИ ОШИБКА и ЕСНД.

«Дайте мне точку опоры, и я переверну землю», — сказал однажды Архимед. «Дайте мне формулу, и я заставлю ее вернуть ошибку», — сказал бы пользователь Excel. Здесь мы не будем рассматривать, как получить ошибки в Excel. Мы узнаем, как предотвратить их, чтобы ваши таблицы были чистыми, а формулы — понятными и точными.

Итак, вот о чем мы поговорим:

Что означает функция Excel ЕСЛИОШИБКА

Функция ЕСЛИОШИБКА (IFERROR по-английски) предназначена для обнаружения и устранения ошибок в формулах и вычислениях. Это значит, что функция ЕСЛИОШИБКА должна выполнить определенные действия, если видит какую-либо ошибку. Более конкретно, она проверяет формулу и, если вычисление дает ошибку, то она возвращает какое-то другое значение, которое вы ей укажете. Если же всё хорошо, то просто возвращает результат формулы.

Синтаксис функции Excel ЕСЛИОШИБКА следующий:

ЕСЛИОШИБКА(значение; значение_если_ошибка)

Где:

• Значение (обязательно) — что проверять на наличие ошибок. Это может быть формула, выражение или ссылка на ячейку.
• Значение_если_ошибка (обязательно) — что возвращать при обнаружении ошибки. Это может быть пустая строка (получится пустая ячейка), текстовое сообщение, числовое значение, другая формула или вычисление.

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

Рассмотрим простой пример:

Чтобы этого не произошло, используйте формулу ЕСЛИОШИБКА, чтобы перехватывать и обрабатывать их нужным вам образом.

Если ошибка, то пусто

Укажите пустую строку (“”) в аргументе значение_если_ошибка, чтобы вернуть пустую ячейку, если обнаружена ошибка:

=ЕСЛИОШИБКА(A4/B4; «»)

Вернемся к нашему примеру и используем ЕСЛИОШИБКА:

Как видите по сравнению с первым скриншотом, вместо стандартных сообщений мы видим просто пустые ячейки.

Если ошибка, то показать сообщение

Вы также можете отобразить собственное сообщение вместо стандартного обозначения ошибок Excel:

=ЕСЛИОШИБКА(A4/B4; «Ошибка в вычислениях»)

Перед вами – третий вариант  нашей небольшой таблицы.

5 фактов, которые нужно знать о функции ЕСЛИОШИБКА в Excel

1. ЕСЛИОШИБКА в Excel обрабатывает все типы ошибок, включая #ДЕЛ/0!, #Н/Д, #ИМЯ?, #NULL!, #ЧИСЛО!, #ССЫЛКА! и #ЗНАЧ!.
2. В зависимости от содержимого аргумента значение_если_ошибка функция может заменить ошибки вашим текстовым сообщением, числом, датой или логическим значением, результатом другой формулы или пустой строкой (пустой ячейкой).
3. Если аргумент значение является пустой ячейкой, он обрабатывается как пустая строка (»’), но не как ошибка.
4. ЕСЛИОШИБКА появилась в Excel 2007 и доступна во всех последующих версиях Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel 2021 и Excel 365.
5. Чтобы перехватывать ошибки в 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 ЕСЛИОШИБКА ВПР можно также найти в нашей статье Как убрать сообщение #Н/Д в ВПР?

Вложенные функции ЕСЛИОШИБКА для выполнения последовательных ВПР

В ситуациях, когда вам нужно выполнить несколько операций ВПР в зависимости от того, была ли предыдущая ВПР успешной или неудачной, вы можете вложить две или более функции ЕСЛИОШИБКА одну в другую.

Предположим, у вас есть несколько отчетов о продажах из региональных отделений вашей компании, и вы хотите получить сумму по определенному идентификатору заказа. С ячейкой В9 в качестве критерия поиска (номер заказа) и тремя небольшими таблицами поиска (таблица 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 или пустая строка, то возвращается ошибка: #ДЕЛ/0! Из-за одной некорректной позиции мы не можем получить итоговый результат.

Чтобы исправить эту ситуацию, просто вложите деление внутрь формулы ЕСЛИОШИБКА:

=СУММ(ЕСЛИОШИБКА($B$2:$B$4/$C$2:$C$4;0))

Результат вы видите на скриншоте ниже:

Что делает эта формула? Делит значение в столбце B на значение в столбце C в каждой строке (3500/100, 2000/50 и 0/0) и возвращает массив результатов {35; 40; #ДЕЛ/0!}. Функция ЕСЛИОШИБКА перехватывает все ошибки #ДЕЛ/0! и заменяет их нулями. Затем функция СУММ суммирует значения в итоговом массиве {35; 40; 0} и выводит окончательный результат (35+40=75).

Примечание. Помните, что ввод формулы массива должен быть завершен нажатием комбинации  Ctrl + Shift + Enter (если у вас не Office365 или Excel2021 – они понимают формулы массива без дополнительных телодвижений).

ЕСЛИОШИБКА или ЕСЛИ + ЕОШИБКА?

Теперь, когда вы знаете, как использовать функцию ЕСЛИОШИБКА в Excel, вы можете удивиться, почему некоторые люди все еще склоняются к использованию комбинации ЕСЛИ + ЕОШИБКА. Есть ли у этого старого метода преимущества по сравнению с ЕСЛИОШИБКА? 

В старые недобрые времена Excel 2003 и более ранних версий, когда ЕСЛИОШИБКА не существовало, совместное использование ЕСЛИ и ЕОШИБКА было единственным возможным способом перехвата ошибок. Это просто немного более сложный способ достижения того же результата.

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

В Excel 2007 — Excel 2016:

ЕСЛИОШИБКА(ВПР( … ); «Не найдено»)

Во всех версиях Excel:

ЕСЛИ(ЕОШИБКА(ВПР(…)); «Не найдено»; ВПР(…))

Обратите внимание, что в формуле ЕСЛИ ЕОШИБКА ВПР вам нужно дважды выполнить ВПР.  Чтобы лучше понять, расшифруем: если ВПР приводит к ошибке, вернуть «Не найдено», в противном случае вывести результат ВПР.

А вот простой пример формулы Excel ЕСЛИ ЕОШИБКА ВПР:

=ЕСЛИ(ЕОШИБКА(ВПР(D2; A2:B5;2;ЛОЖЬ)); «Не найдено»; ВПР(D2; A2:B5;2;ЛОЖЬ ))

Применение вы видите на рисунке ниже.

ЕСЛИОШИБКА против ЕСНД – что использовать?

Представленная в Excel 2013, ЕСНД (IFNA в английской версии) — это еще одна функция для проверки формулы на наличие ошибок. Его синтаксис похож на синтаксис ЕСЛИОШИБКА:

ЕСНД(значение; значение_если_НД)

Чем ЕСНД отличается от ЕСЛИОШИБКА? Функция ЕСНД перехватывает только ошибки #Н/Д, тогда как ЕСЛИОШИБКА обрабатывает все типы ошибок.

В каких ситуациях вы можете использовать ЕСНД? Когда нецелесообразно скрывать все ошибки. Например, при работе с важными данными вы можете захотеть получать предупреждения о возможных ошибках в вашем наборе данных (случайном делении на ноль и т.п.), а стандартные сообщения об ошибках Excel с символом «#» могут быть яркими визуальными индикаторами проблем.

Давайте посмотрим, как можно создать формулу, отображающую сообщение «Не найдено» вместо ошибки «Н/Д», которая появляется, когда искомое значение отсутствует в наборе данных, но при этом вы будете видеть все  другие ошибки Excel.

Предположим, вы хотите получить Количество из таблицы поиска в таблицу с результатами, как показано на рисунке ниже.  Проще всего было бы использовать ЕСЛИОШИБКА с ВПР. Таблица приобрела бы красивый вид, но при этом за надписью «Не найдено» были бы скрыты не только ошибки поиска, но и все другие ошибки. И мы не заметили бы, что в исходной таблице поиска у нас есть ошибка деления на ноль, так как не заполнена цена персиков. Поэтому более разумно использовать ЕСНД, чтобы с ее помощью обработать только ошибки поиска:

=ЕСНД(ВПР(F3; $A$3:$D$6; 4;ЛОЖЬ); «Не найдено»)

Или подойдет комбинация ЕСЛИ ЕНД для старых версий Excel:

=ЕСЛИ(ЕНД(ВПР(F3; $A$3:$D$6; 4;ЛОЖЬ));»Не найдено»; ВПР(F3; $A$3:$D$6; 4;ЛОЖЬ))

Как видите, формула ЕСНД с ВПР возвращает «Не найдено» только для товара, которого нет в таблице поиска (Сливы). Для персиков она показывает #ДЕЛ/0! что указывает на то, что наша таблица поиска содержит ошибку деления на ноль.

Рекомендации по использованию ЕСЛИОШИБКА в Excel

Итак, вы уже знаете, что функция ЕСЛИОШИБКА — это самый простой способ отлавливать ошибки в Excel и маскировать их пустыми ячейками, нулевыми значениями или собственными сообщениями. Однако это не означает, что вы должны обернуть каждую формулу в функцию обработки ошибок. 

Эти простые рекомендации могут помочь вам сохранить приемлемый баланс.

1. Не ловите ошибки без весомой на то причины.
2. Оберните в ЕСЛИОШИБКА только ту часть формулы, где по вашему мнению могут возникнуть проблемы.
3. Чтобы обрабатывать только определенные ошибки, используйте другую функцию обработки ошибок с меньшей областью действия:
   • ЕСНД или ЕСЛИ ЕНД для обнаружения только ошибок #H/Д.
   • ЕОШ для обнаружения всех ошибок, кроме #Н/Д.

Мы постарались рассказать, как можно использовать функцию ЕСЛИОШИБКА в Excel. Примеры перехвата и обработки ошибок могут быть полезны и для «чайников», и для более опытных пользователей. 

Также рекомендуем:

Как объединить две или несколько таблиц в Excel — В этом руководстве вы найдете некоторые приемы объединения таблиц Excel путем сопоставления данных в одном или нескольких столбцах. Как часто при анализе в Excel вся необходимая информация собирается на одном…

Сравнение ячеек в Excel — Вы узнаете, как сравнивать значения в ячейках Excel на предмет точного совпадения или без учета регистра. Мы предложим вам несколько формул для сопоставления двух ячеек по их значениям, длине или количеству…

Вычисление номера столбца для извлечения данных в ВПР — Задача: Наиболее простым способом научиться указывать тот столбец, из которого функция ВПР будет извлекать данные. При этом мы не будем изменять саму формулу, поскольку это может привести в случайным ошибкам.…

4 способа, как сделать левый ВПР в Excel. — Функция ВПР – одна из самых популярных, когда нужно найти и извлечь из таблицы какие-либо данные. Но при этом она имеет один существенный недостаток. Поиск она производит в крайнем левом…

Почему не работает ВПР в Excel? — Функция ВПР – это очень мощный инструмент поиска. Но если он по каким-то причинам завершился неудачно, то вы получите сообщение об ошибке #Н/Д (#N/A в английском варианте). Давайте постараемся вместе…

ВПР с несколькими условиями: 5 примеров. — Очень часто наши требования к поиску данных не ограничиваются одним условием. К примеру, нам нужна выручка по магазину за определенный месяц, количество конкретного товара, проданного определенному покупателю и т.д. Обычными…

Функция ЕСЛИОШИБКА() в MS EXCEL

​Смотрите также​Так же ошибка #ССЫЛКА!​ горизонтальные диапазоны, заданные​ квадратный корень не​ содержать ячейки. Зная​ не повлияло.​ типа. К примеру,​ и не может​#ПУСТО!​ Приведем несколько случаев​ предлагает свой вариант​или, соответственно, результат​ #Н/Д​ «ИСТИНА» логическим​ЕССЫЛКА​

​ЕПУСТО(значение)​ более ранних версиях​

Синтаксис функции

​Функция ЕСЛИОШИБКА(), английский вариант​​ часто возникает при​

​ в аргументах функции.​​ может быть с​ значение каждого кода​

​Применение вышеуказанного формата не​​ номер столбца в​ вычислить результат. Ярким​

​возникает, когда задано​ возникновения ошибки​ исправления ошибки. Вы​ вычисления выражения A1/A2.​ИСТИНА​ЛОЖЬ​
​Аргумент «значение» ссылается на​

​ЕОШ(значение)​ придется использовать функцию​ IFERROR(), проверяет выражение​

​ неправильном указании имени​В данном случаи пересечением​ отрицательного числа, а​ (например: #ЗНАЧ!, #ДЕЛ/0!,​ влияет на вычисления.​ функции​ примером таких функций​ пересечение двух диапазонов,​#Н/Д​ можете либо согласиться​

Функция ЕСЛИОШИБКА() vs ЕОШИБКА()

​Функция ЕСЛИОШИБКА() впервые появилась​=ЕОШ(A6)​=ЕЧИСЛО(4)​ ссылку​ЕОШИБКА(значение)​ ЕОШИБКА().​
​ на равенство значениям​

​ листа в адресе​ диапазонов является ячейка​ программа отобразила данный​ #ЧИСЛО!, #Н/Д!, #ИМЯ!,​ В Строке формул,​ВПР​

​ в Excel являются​ не имеющих общих​:​ с Excel, либо​ в EXCEL 2007​Проверяет, является ли значение​Проверяет, является ли значение​

excel2.ru

Е (функции Е)

Описание

​ЕТЕКСТ​​ЕЛОГИЧ(значение)​​Обратите внимание, что использована​ #Н/Д, #ЗНАЧ!, #ССЫЛКА!,​ трехмерных ссылок.​ C3 и функция​ результат этой же​​ #ПУСТО!, #ССЫЛКА!) можно​​ по-прежнему, будет отображаться​задан числом меньше​СТАВКА​ точек.​Функция поиска не находит​ исправить формулу самостоятельно.​ и упростила написание​

​ в ячейке A6​​ 4 числом​​Аргумент «значение» ссылается на​ЕНД(значение)​ именно ЕОШИБКА(), а​ #ДЕЛ/0!, #ЧИСЛО!, #ИМЯ?​#ЗНАЧ! – ошибка в​ отображает ее значение.​ ошибкой.​ легко разобраться, как​ формула, хотя в​​ 1.​​и​Например,​​ соответствия. К примеру,​​ В любом случае​

​ формул для обработки​ ​ (#Н/Д) значением ошибки​ ​ИСТИНА​ текст​

​ЕНЕТЕКСТ(значение)​ не ЕОШ(), т.к.​ или #ПУСТО! Если​ значении. Если мы​​Заданные аргументы в функции:​​Значение недоступно: #Н/Д! –​ найти ошибку в​ ячейке ничего не​​Аргумент функции должен иметь​​ВСД​

Синтаксис

​=А1:А10 C5:E5​

​ функция​

​ слепо полагаться на​

​ ошибок. Если раньше​

​ЛОЖЬ​

​=ЕССЫЛКА(G8)​

​Аргументы в функциях​

​ЕЧИСЛО(значение)​

​ для последней ошибка​

​ проверяемое выражение или​​ пытаемся сложить число​​ =СУММ(B4:D4 B2:B3) –​

• ​ значит, что значение​​ формуле и устранить​ будет отображаться.​ единственное значение, а​.​– это формула,​ВПР​ это исправление ни​ приходилось писать формулы​

​=ЕЧИСЛО(A5)​	​Проверяет, является ли значение​
​Е​	​ЕССЫЛКА(значение)​ #Н/Д ошибкой не​
​ значение в ячейке​	​ и слово в​ не образуют пересечение.​ является недоступным для​
​ ее.​	​Используя Условное форматирование, также​ вместо этого ему​Ошибка​ использующая оператор пересечения,​при точном поиске​
​ в коем случае​	​ на подобие этой​Проверяет, является ли значение​
​ G8 допустимой ссылкой​	​не преобразуются. Любые​ЕТЕКСТ(значение)​ является (см. рисунок​
​ содержит ошибку, то​	​ Excel в результате​ Следовательно, функция дает​ формулы:​Как видно при делении​ можно добиться такого​ присваивают целый диапазон.​#ССЫЛКА!​ которая должна вернуть​
​ вернет ошибку​	​ нельзя. Например, на​ =ЕСЛИ(ЕОШИБКА(A1);»ОШИБКА!»;A1), то теперь​
​ в ячейке A5​	​ИСТИНА​ числа, заключенные в​
​аргумент функции​	​ выше).​ функция возвращает определенное​

Замечания

• ​ мы получим ошибку​​ значение с ошибкой​​Записанная формула в B1:​ на ячейку с​ же результата.​ На рисунке ниже​возникает в Excel,​ значение ячейки, находящейся​#Н/Д​ следующем рисунке Excel​ достаточно записать =ЕСЛИОШИБКА(A1;»ОШИБКА!»):​ (330,92) числом​​=ЕССЫЛКА(XYZ1)​​ кавычки, воспринимаются как​Е​Каждая из функций​ для этого случая​​ #ЗНАЧ! Интересен тот​​ – #ПУСТО!​

• ​ =ПОИСКПОЗ(„Максим”; A1:A4) ищет​ пустым значением программа​выделите интересующий диапазон;​ в качестве искомого​ когда формула ссылается​​ на пересечении двух​​, если соответствий не​ предложил нам неправильное​ в случае наличия​

Примеры

Пример 1

​ИСТИНА​Проверяет, является ли значение​ текст. Например, в​описаны ниже.​Е​ значение, в противном​ факт, что если​Неправильная ссылка на ячейку:​ текстовое содержимое «Максим»​ воспринимает как деление​в меню выберите Главная/​ значения функции​ на ячейку, которая​

​ диапазонов. Поскольку диапазоны​	​ найдено.​	​ решение.​
​ в ячейке​	​=ЕТЕКСТ(A3)​ XYZ1 допустимой ссылкой​	​ большинстве других функций,​
​Значение​	​проверяет указанное значение и​ случае — результат​	​ бы мы попытались​
​ #ССЫЛКА! – значит,​	​ в диапазоне ячеек​ на 0. В​	​ Стили/ Условное форматирование/​
​ВПР​	​ не существует или​ не имеют точек​	​Формула прямо или косвенно​
​Бывают случаи, когда ячейка​	​A1​Проверяет, является ли значение​	​ЛОЖЬ​

​ требующих числового аргумента,​

​    Обязательный аргумент. Значением этого​ возвращает в зависимости​ вычисления выражения или​ сложить две ячейки,​ что аргументы формулы​ A1:A4. Содержимое найдено​ результате выдает значение:​ Создать правило… );​используется диапазон A6:A8.​ удалена.​ пересечения, формула вернет​ обращается к ячейке,​ в Excel полностью​

​ошибки будет выведено​
​ в ячейке A3​
​Пример 2​
​ текстовое значение «19»​
​ аргумента может быть​
​ от результата значение​
​ содержимое ячейки.​	​ в которых значение​	​ ссылаются на ошибочный​
​ во второй ячейке​	​ #ДЕЛ/0! В этом​в появившемся окне выберите​	​Вот и все! Мы​
​Например, на рисунке ниже​	​#ПУСТО!​ в которой отображается​ заполнена знаками решетки.​	​ значение ОШИБКА!, в​
​ («Регион1») текстом​	​Скопируйте образец данных из​ преобразуется в число​ пустая ячейка, значение​ ИСТИНА или ЛОЖЬ.​	​Функция ЕСЛИОШИБКА() впервые появилась​
​ первой число, а​	​ адрес. Чаще всего​ A2. Следовательно, функция​ можно убедиться и​ Форматировать только ячейки,​	​ разобрали типичные ситуации​
​ представлена формула, которая​	​.​ значение​ Это означает один​	​ противном случае -​
​ИСТИНА​	​ следующей таблицы и​ 19. Однако в​ ошибки, логическое значение,​	​ Например, функция​
​ в EXCEL 2007.​	​ второй – текст​ это несуществующая ячейка.​ возвращает результат 2.​	​ с помощью подсказки.​

support.office.com

Функция ЕОШИБКА() в MS EXCEL

​ которые содержат;​ возникновения ошибок в​ суммирует значения двух​Также данная ошибка возникнет,​#Н/Д​ из двух вариантов:​ содержимое ячейки​Функция ЕОШИБКА(), английский вариант​ вставьте их в​

Синтаксис функции

​ формуле​​ текст, число, ссылка​

​ЕПУСТО​​ЕСЛИОШИБКАзначениезначение_при_ошибке​ с помощью функции​В данном примере ошибка​ Вторая формула ищет​

Функция ЕОШИБКА() vs ЕОШ()

​Читайте также: Как убрать​в выпадающем списке выберите​ Excel. Зная причину​ ячеек.​ если случайно опустить​.​Столбец недостаточно широк для​

​A1​ ISERROR(), проверяет на​ ячейку A1 нового​ЕЧИСЛО(«19»)​ на любой из​
​возвращает логическое значение​

​)​ =СУММ(), то ошибки​​ возникал при неправильном​​ текстовое содержимое «Андрей»,​ ошибку деления на​ Ошибки (см. рисунок​ ошибки, гораздо проще​Если удалить столбец B,​ один из операторов​​При работе с массивами​​ отображения всего содержимого​.​

Функция ЕОШИБКА() vs ЕСЛИОШИБКА()

​ равенство значениям #Н/Д,​ листа Excel. Чтобы​это значение не​ перечисленных объектов или​ ИСТИНА, если проверяемое​Значение​ не возникнет, а​ копировании формулы. У​ то диапазон A1:A4​ ноль формулой Excel.​ ниже);​​ исправить ее. Успехов​​ формула вернет ошибку​ в формуле. К​ в Excel, когда​ ячейки. Для решения​​Ошибки в Excel возникают​​ #ЗНАЧ!, #ССЫЛКА!, #ДЕЛ/0!,​

excel2.ru

Обзор ошибок, возникающих в формулах Excel

​ отобразить результаты формул,​ преобразуется из текста​ имя такого объекта.​ значение является ссылкой​- аргумент, проверяемый​ текст примет значение​ нас есть 3​​ не содержит таких​​В других арифметических вычислениях​выберите пользовательский формат (белый​ Вам в изучении​#ССЫЛКА!​ примеру, формулу​ аргументы массива имеют​ проблемы достаточно увеличить​ довольно часто. Вы,​

Несоответствие открывающих и закрывающих скобок

​ #ЧИСЛО!, #ИМЯ? или​ выделите их и​ в число, и​Функция​ на пустую ячейку;​ на возникновение ошибок.​ 0 при вычислении.​ диапазона ячеек: A1:A3,​ значений. Поэтому функция​ (умножение, суммирование, вычитание)​ цвет шрифта);​

​ Excel!​.​=А1*А2*А3​ меньший размер, чем​ ширину столбца, чтобы​​ наверняка, замечали странные​​ #ПУСТО! и возвращает​ нажмите клавишу F2,​

​ функция​Возвращает значение ИСТИНА, если​ в противном случае​Значение_при_ошибке​ Например:​ B1:B4, C1:C2.​ возвращает ошибку #Н/Д​ пустая ячейка также​Теперь ошибочные значения отображаются​Автор: Антон Андронов​Еще пример. Формула в​записать как​ результирующий массив. В​ все данные отобразились…​

Ячейка заполнена знаками решетки

​ значения в ячейках,​ в зависимости от​ а затем — клавишу​ЕЧИСЛО​ЕПУСТО​

1. 1. ​ возвращается логическое значение​   — значение, возвращаемое​Ряд решеток вместо значения​Под первым диапазоном в​ (нет данных).​ является нулевым значением.​

      ​ белым шрифтом и​Иногда требуется скрыть в​

1. ​ ячейке B2 ссылается​=А1*А2 A3​ этом случае в​…или изменить числовой формат​ вместо ожидаемого результата,​ этого ИСТИНА или​ ВВОД. При необходимости​возвращает значение ЛОЖЬ.​Аргумент «значение» ссылается на​ ЛОЖЬ.​ при ошибке.​

​ ячейки ;; –​ ячейку A4 вводим​Относиться к категории ошибки​

Ошибка #ДЕЛ/0!

​​​ не видны на​​ ячейке значения ошибки:​ на ячейку B1,​.​ незадействованных ячейках итогового​ ячейки.​ которые начинались со​ ЛОЖЬ.​ измените ширину столбцов,​С помощью этих функций​

Ошибка #Н/Д

​ пустую ячейку​​Функции​​Для обработки ошибок #Н/Д,​ данное значение не​ суммирующую формулу: =СУММ(A1:A3).​ в написании функций.​Неправильное число: #ЧИСЛО! –​​ стандартном белом фоне.​​ #ЗНАЧ!, #ССЫЛКА!, #ДЕЛ/0!,​

1. ​ т.е. на ячейку,​Ошибка​ массива отобразятся значения​​В ячейке содержится формула,​​ знака​ЕОШИБКАзначение​​ чтобы видеть все​​ удобно проверять результаты​ЕОШ​
2. ​Е​ #ЗНАЧ!, #ССЫЛКА!, #ДЕЛ/0!,​ является ошибкой. Просто​ А дальше копируем​​ Недопустимое имя: #ИМЯ!​​ это ошибка невозможности​
3. ​ Если фон поменять,​ #ЧИСЛО!, #ИМЯ? Сделаем​ расположенную выше на​#ЧИСЛО!​#Н/Д​ которая возвращает некорректное​#​)​​ данные.​​ вычислений в формулах.​Аргумент «значение» ссылается на​используются для получения​ #ЧИСЛО!, #ИМЯ? или​ это информация о​

   

   ​ эту же формулу​​ – значит, что​​ выполнить вычисление в​

   

Ошибка #ИМЯ?

​ то ошибки станут​​ это несколькими способами.​​ 1 строку.​возникает, когда проблема​.Например, на рисунке ниже​ значение даты или​

1. ​. Это говорит о​Значение​Данные​
2. ​ Комбинируя эти функции​ любое значение ошибки,​ сведений о значении​ #ПУСТО! обычно используют​

​ том, что ширина​ под второй диапазон,​

1. ​ Excel не распознал​ формуле.​
2. ​ снова видны. Даже​Скрыть значения ошибки можно​

Ошибка #ПУСТО!

​Если мы скопируем данную​​ в формуле связана​​ видно, что результирующий​ времени. Думаю, Вы​ том, что формула​- ссылка на​

1. ​Золотой​​ с функцией​​ кроме #Н/Д​ перед выполнением с​ формулу вида (см.​ столбца слишком узкая​ в ячейку B5.​ текста написанного в​Несколько практических примеров:​ если просто выделить​​ используя пользовательский формат, Условное​​ формулу в любую​
2. ​ со значением.​ массив C4:C11 больше,​ знаете, что Excel​ возвращает ошибку. Чтобы​ ячейку или результат​​Регион1​​ЕСЛИ​​ЕОШИБКА​​ ним вычисления или​

Ошибка #ЧИСЛО!

​ файл примера):​​ для того, чтобы​​ Формула, как и​ формуле (название функции​Ошибка: #ЧИСЛО! возникает, когда​

1. ​ диапазон, то ошибки​ форматирование или значение​ ячейку 1-й строки​Например, задано отрицательное значение​ чем аргументы массива​ не поддерживает даты​
2. ​ избавиться от ошибки,​​ вычисления выражения, которое​​#ССЫЛ!​, можно находить ошибки​Аргумент «значение» ссылается на​ другого действия. Например,​​=ЕСЛИОШИБКА(A2/B2;»Ошибка расчете»)​​ вместить корректно отображаемое​ прежде, суммирует только​

​ =СУМ() ему неизвестно,​ числовое значение слишком​ будут слегка видны.​ Пустой текст («»).​

1. ​ (например, ячейку D1),​ там, где должно​​ A4:A8 и B4:B8.​​ до 1900 года.​ Вы должны исправить​ необходимо проверить.​330,92​ в формулах (см.​ любое значение ошибки​ для выполнения другого​​Формула проверяет на предмет​​ содержимое ячейки. Нужно​​ 3 ячейки B2:B4,​​ оно написано с​

Ошибка #ССЫЛКА!

​ велико или же​​Замечательным свойством значения Пустой​​Пользовательский формат вводим через​ формула вернет ошибку​ быть положительное. Яркий​Нажав комбинацию клавиш​ Поэтому, если результатом​

1. ​ ее причину, а​Функции ЕОШИБКА() в отличие​#Н/Д​ приведенные ниже примеры).​

   ​ (#Н/Д, #ЗНАЧ!, #ССЫЛ!,​ действия при возникновении​​ ошибки результат вычисления​​ просто расширить столбец.​

   

2. ​ минуя значение первой​ ошибкой). Это результат​ слишком маленькое. Так​ текст является, то​ диалоговое окно Формат​#ССЫЛКА!​

   ​ пример – квадратный​Ctrl+Shift+Enter​ формулы оказывается такая​ они могут быть​ от функции ЕОШ()​​Формула​​Скопируйте образец данных из​ #ДЕЛ/0!, #ЧИСЛО!, #ИМЯ?​ ошибки можно использовать​

   

Ошибка #ЗНАЧ!

​ A2/B2.​​ Например, сделайте двойной​​ B1.​ ошибки синтаксиса при​ же данная ошибка​ что оно не​ ячеек (см. файл​, т.к. в ней​ корень из отрицательного​, получим следующий результат:​ дата, то Excel​​ самыми разными.​​ считает, что значение​

1. ​Описание​ следующей таблицы и​ или #ПУСТО!)​
2. ​ функцию​Если результат вычисления не​ щелчок левой кнопкой​Когда та же формула​ написании имени функции.​​ может возникнуть при​​ отображается в ячейке​ примера).​
3. ​ будет присутствовать ссылка​ числа.​Ошибка​ возвращает подобный результат.​Самым распространенным примером возникновения​ #Н/Д является ошибкой.​Результат​​ вставьте их в​​ЕЛОГИЧ​

​ЕОШИБКА​ является ошибкой #Н/Д,​ мышки на границе​ была скопирована под​ Например:​ попытке получить корень​ (например, введите формулу​для вызова окна Формат​

​ на несуществующую ячейку.​

office-guru.ru

Скрытие в MS EXCEL ошибки в ячейке

​К тому же, ошибка​#ИМЯ?​В данном случае увеличение​ ошибок в формулах​ Т.е. =ЕОШИБКА(НД()) вернет​

​=ЕПУСТО(A2)​ ячейку A1 нового​Аргумент «значение» ссылается на​в сочетании с​

Пользовательский формат

​ #ЗНАЧ!, #ССЫЛКА!, #ДЕЛ/0!,​ заголовков столбцов данной​ третий диапазон, в​Пустое множество: #ПУСТО! –​

• ​ с отрицательного числа.​ =»»). Записав, например,​​ ячеек нажмите​​Ошибка​
• ​#ЧИСЛО!​
• ​возникает, когда в​ ширины столбца уже​
• ​ Excel является несоответствие​
• ​ ИСТИНА, а =ЕОШ(НД())​Проверяет, является ли ячейка​ листа Excel. Чтобы​ логическое значение​

​ функцией​ #ЧИСЛО!, #ИМЯ? или​ ячейки.​​ ячейку C3 функция​​ это ошибки оператора​ Например, =КОРЕНЬ(-25).​ в ячейке​CTRL+1​

​#ЗНАЧ!​возникает, когда возвращается​ формуле присутствует имя,​ не поможет.​ открывающих и закрывающих​ вернет ЛОЖЬ.​ C2 пустой​

Условное форматирование

​ отобразить результаты формул,​ЕНД​ЕСЛИ​

• ​ #ПУСТО! , то​
• ​Так решетки (;;) вместо​ вернула ошибку #ССЫЛКА!​ пересечения множеств. В​
• ​В ячейке А1 –​А14​;​
• ​одна из самых​ слишком большое или​ которое Excel не​
• ​Ошибка​ скобок. Когда пользователь​

​Для обработки ошибок #Н/Д,​ЛОЖЬ​ выделите их и​Аргумент «значение» ссылается на​:​ формула возвращает результат​ значения ячеек можно​ Так как над​ Excel существует такое​ слишком большое число​

Значение Пустой текст («»)

​формулу =ЕСЛИОШИБКА(1/0;»») получим​выберите (все форматы).​ распространенных ошибок, встречающихся​ слишком малое значение.​ понимает.​#ДЕЛ/0!​ вводит формулу, Excel​​ #ЗНАЧ!, #ССЫЛКА!, #ДЕЛ/0!,​​=ЕОШИБКА(A4)​ нажмите клавишу F2,​​ значение ошибки #Н/Д​​=​

excel2.ru

Как убрать ошибки в ячейках Excel

​ вычисления A2/B2, если​ увидеть при отрицательно​ ячейкой C3 может​ понятие как пересечение​ (10^1000). Excel не​ пустую ячейку​в поле Тип введите​ в Excel. Она​

Ошибки в формуле Excel отображаемые в ячейках

​ Например, формула​Например, используется текст не​возникает, когда в​ автоматически проверяет ее​ #ЧИСЛО!, #ИМЯ? или​Проверяет, является ли значение​ а затем — клавишу​ (значение недоступно)​ЕСЛИ(​ обнаруживает, то возвращает​ дате. Например, мы​ быть только 2​

Как убрать #ДЕЛ/0 в Excel

​ множеств. Оно применяется​ может работать с​А14​ формат [Черный]Основной​ возникает, когда значение​=1000^1000​ заключенный в двойные​ Excel происходит деление​ синтаксис и не​

​ #ПУСТО! используют формулы​ в ячейке A4​ ВВОД. При необходимости​

​ЕНЕТЕКСТ​ЕОШИБКА(A1); «Произошла ошибка.»; A1​ строку Ошибка в​ пытаемся отнять от​

​ ячейки а не​

Результат ошибочного вычисления – #ЧИСЛО!

​ для быстрого получения​ такими большими числами.​.​нажмите ОК​

​ одного из аргументов​

​вернет как раз​ кавычки:​ на ноль. Это​ даст закончить ввод,​ следующего вида:​ (#ССЫЛ!) значением ошибки​ измените ширину столбцов,​Аргумент «значение» ссылается на​ * 2)​

​ расчете.​ старой даты новую​ 3 (как того​ данных из больших​В ячейке А2 –​

​При ошибочных вычислениях, формулы​цвет шрифта ячейки установите​ формулы или функции​ эту ошибку.​Функция ссылается на имя​ может быть, как​ пока в ней​=ЕСЛИ(ЕОШИБКА(A1);»ОШИБКА!»;A1) или =ЕСЛИ(ЕОШИБКА(A1/A2);»ОШИБКА!»;A1/A2)​ИСТИНА​ чтобы видеть все​

​ любой элемент, который​Эта формула проверяет наличие​В отличие от функции​ дату. А в​ требовала исходная формула).​ таблиц по запросу​ та же проблема​

Как убрать НД в Excel

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

​ диапазона, которое не​ явное деление на​ присутствует синтаксическая ошибка.​В случае наличия в​=ЕНД(A4)​ данные.​ не является текстом.​ ошибки в ячейке​ ЕСЛИОШИБКА() функция ЕОШИБКА()​ результате вычисления установлен​Примечание. В данном случае​ точки пересечения вертикального​ с большими числами.​ ошибок вместо значений.​ и ее фон​

Ошибка #ИМЯ! в Excel

​ Самые распространенные случаи​ поддерживает числовые величины​ существует или написано​ ноль, так и​Например, на рисунке выше​ ячейке​Проверяет, является ли значение​Формула​ (Обратите внимание, что​ A1. При возникновении​ не умеет самостоятельно​ формат ячеек «Дата»​ наиболее удобнее под​

Ошибка #ПУСТО! в Excel

​ и горизонтального диапазона​ Казалось бы, 1000​ Рассмотрим их на​ (обычно белый) ​ возникновения ошибки​ от -1Е-307 до​ с опечаткой:​ деление на ячейку,​ мы намеренно пропустили​А1​ в ячейке A4​Описание​ функция возвращает значение​ ошибки функция​ обрабатывать ошибку -​ (а не «Общий»).​ каждым диапазоном перед​ ячеек. Если диапазоны​ небольшое число, но​ практических примерах в​

​На рисунке ниже пользовательский​#ЗНАЧ!​ 1Е+307.​В данном примере имя​

​ которая содержит ноль​ закрывающую скобку при​ошибки или ошибки​ (#ССЫЛ!) значением ошибки​Результат​ ИСТИНА, если аргумент​

#ССЫЛКА! – ошибка ссылок на ячейки Excel

​ЕСЛИ​ приходится задействовать функцию​Скачать пример удаления ошибок​ началом ввода нажать​ не пересекаются, программа​ при возвращении его​

​ процессе работы формул,​ формат применен к​:​Еще одним случаем возникновения​ диапазон не определено.​ или пуста.​

​ вводе формулы. Если​ при вычислении выражения​ #Н/Д​=ЕЛОГИЧ(ИСТИНА)​ ссылается на пустую​возвращает сообщение «Произошла​ ЕСЛИ():​ в Excel.​ комбинацию горячих клавиш​ отображает ошибочное значение​ факториала получается слишком​ которые дали ошибочные​

​ ячейкам​Формула пытается применить стандартные​ ошибки​Адрес указан без разделяющего​Ошибка​ нажать клавишу​ A1/A2, формулой выводится​ЛОЖЬ​Проверяет, является ли значение​ ячейку.)​ ошибка.» Если ошибки​

​=ЕСЛИ(ЕОШИБКА(A2/B2);»Ошибка в расчетах»;A2/B2)​Неправильный формат ячейки так​ ALT+=. Тогда вставиться​ – #ПУСТО! Оператором​ большое числовое значение,​ результаты вычислений.​А6B6​ математические операторы к​#ЧИСЛО!​

​ двоеточия:​#Н/Д​Enter​ слово ОШИБКА! В​=ЕНД(A6)​

Как исправить ЗНАЧ в Excel

​ ИСТИНА логическим​ЕЧИСЛО​ отсутствуют, функция​Т.к. функция ЕСЛИОШИБКА() впервые​ же может отображать​ функция суммирования и​ пересечения множеств является​ с которым Excel​В данном уроке будут​. Как видно, на​ тексту.​является употребление функции,​В имени функции допущена​возникает, когда для​, Excel выдаст следующее​ противном случае -​Проверяет, является ли значение​ИСТИНА​Аргумент «значение» ссылается на​

Решетки в ячейке Excel

​ЕСЛИ​ появилась в EXCEL​ вместо значений ряд​ автоматически определит количество​ одиночный пробел. Им​ не справиться.​ описаны значения ошибок​ значения, которые не​В качестве аргументов функции​ которая при вычислении​ опечатка:​ формулы или функции​ предупреждение:​ содержимое ячейки​ в ячейке A6​=ЕЛОГИЧ(«ИСТИНА»)​

​ число​вычисляет произведение A1*2.​ 2007, то в​ символов решетки (;;).​ суммирующих ячеек.​ разделяются вертикальные и​В ячейке А3 –​ формул, которые могут​ являются ошибкой, форматирование​ используются данные несоответствующего​

​ использует метод итераций​Ошибка​

​ недоступно какое-то значение.​В некоторых случаях Excel​A1​ (#Н/Д) значением ошибки​

exceltable.com

​Проверяет, является ли значение​

• Что делает ЕСЛИОШИБКА?
• Синтаксис
• Пример 1: ЕСЛИОШИБКА + ВПР
• Пример 2: ЕСЛИОШИБКА + деление на ноль
• Пример 3: ЕСЛИОШИБКА в формулах массива
• Другие логические функции

Раздел функций	Логические
Название на английском	IFERROR
Волатильность	Не волатильная
Похожие функции	ЕСЛИ, ЕОШ

Что делает ЕСЛИОШИБКА?

Часто при использовании формул, если результат возвращает ошибку, нужно обрабатывать ее, а если нет – возвращать результат вычисления.

Именно эту задачу и решает функция ЕСЛИОШИБКА.

Функция проверяет входящее значение/вычисление на ошибочность, если ошибки нет, возвращает его само.

Оцениваются следующие типы ошибок: #Н/Д, #ЗНАЧ!, #ССЫЛКА!, #ДЕЛ/0!, #ЧИСЛО!, #ИМЯ? и #ПУСТО!

Эквивалентным сочетанием была бы формула на основе комбинации функции ЕСЛИ и ЕОШ.

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

ЕСЛИ(ЕОШ(вычисление);"результат-если-ошибка";вычисление)

Отличие ЕСЛИОШИБКА в том, что она производит вычисление однократно и экономит ресурсы.

Также использование функции упрощает синтаксис формул.

Синтаксис

Синтаксис функции ЕСЛИОШИБКА предполагает всего два аргумента, оба – обязательные:

=ЕСЛИОШИБКА(Значение-Или-Вычисление;Значение-если-ошибка)

Пример 1: ЕСЛИОШИБКА + ВПР

Наиболее характерный пример использования – в паре с функцией ВПР при поиске данных в больших таблицах.

=ЕСЛИОШИБКА(ВПР(ячейка-или-значение;диапазон;номер-столбца;0);"ошибка")
=ЕСЛИОШИБКА(ВПР(ячейка-или-значение;диапазон;номер-столбца;0);"")

Первый вариант вернет текстовое значение ошибки, второй оставит строку пустой.

Поскольку ВПР может изрядно загрузить процессор, функция ЕСЛИОШИБКА здесь весьма кстати.

Пример 2: ЕСЛИОШИБКА + деление на ноль

Задача маркетолога – произвести оценку эффективности рекламных кампаний. Один из ключевых показателей – стоимость привлечения клиента. Рассчитывается он довольно просто – расходы по рекламным кампаниям делятся на количество приведенных ими клиентов.

Что делать, когда кампания не привела ни одного? Вычисление выдаст ошибку

#ДЕЛ/0!

Оставлять ее не стоит, ведь деньги на рекламу были потрачены, и нужно это учитывать.

Лучший вариант – представить, что один клиент был приведен, т.к. рано или поздно это произойдет, а эффективность нужно смотреть уже сейчас. Функция ЕСЛИОШИБКА дает возможность вернуть весь расход на кампанию, если возникает ошибка деления на ноль.

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

Пример 3: ЕСЛИОШИБКА в формулах массива

Проблема функций МИН и МАКС при работе с массивами в том, что, если в массиве присутствует хотя бы одна ошибка, они также возвращают ошибку.

Поэтому, если есть вероятность такого события, функцию ЕСЛИОШИБКА нужно использовать как обработчик, чтобы избежать результирующей ошибки.

Формула для поиска позиции первого символа латиницы:

{=МИН(ЕСЛИОШИБКА(ПОИСК(СИМВОЛ(СТРОКА(65:90));A1);""))}

Механика ее работы такова:

• Функция СИМВОЛ создает массив букв английского алфавита;
• Функция ПОИСК ищет позицию каждой буквы в строке;
• Если буква не найдена, функция вернет ошибку Н/Д;
• Функция ЕСЛИОШИБКА возвращает пустую строку в таких случаях, а числа оставляет как они есть;
• Функция МИН пропускает пустые строки и возвращает минимальное число;
• Если весь массив будет состоять из пустых строк, функция МИН вернет 0.

А такая формула использует массив констант и ищет позицию первой цифры:

{=МИН(ЕСЛИОШИБКА(ПОИСК({1:2:3:4:5:6:7:8:9:0};A1);""))}

Читайте подробнее в статье про формулы массива.

Другие логические функции

ЕСЛИ, И, ИЛИ, НЕ

Понравилась статья? Поддержите ее автора!
Набор инструментов !SEMTools поможет решить множество задач без ввода сложных формул и сэкономит ваше время. И вам полезно, и автору приятно!

Если вы постоянно работаете или планируете работать в Excel вам необходимо знать как фильтровать ошибки. И конечно же в Excel есть и такая функция.

Итак, начнём!

Какие бывают ошибки?

Давайте рассмотрим каждый тип ошибки в Excel и из-за чего они возникают.

Ошибка #Н/Д (#N/A)

Эта ошибка возникает тогда, когда Excel не может «подгрузить» значение. Например, когда его в ячейке нет.

На картинке ниже, я пытаюсь посчитать значение, которого в табличке не существует (пустая ячейка):

Ошибка #ДЕЛ/0! (#DIV/0!)

Собственно, из названия функции все понятно. Ошибка возникает, когда вы пытаетесь разделить что-либо на 0.

Ошибка #ЗНАЧ! (#VALUE!)

Эта ошибка связана с типом данных. Например, когда вы пытаетесь прибавить к числу буквенное значение. Эти данные не совместимы для операции «сложение».

Пример ниже:

Ошибка #ССЫЛКА! (#REF!)

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

Я удалил столбец из нашей таблички и получилось это:

Ошибка #ИМЯ? (#NAME?)

Данная ошибка является ошибкой имени (понятно из названия), имени функции, чаще всего. Например вы хотите вызвать функцию ВПР, а вызываете ВП.

Пример на картинке ниже:

Ошибка #ЧИСЛО! (#NUM!)

Эта ошибка может возникнуть в том случае, если вы производите расчеты огромных чисел. В Excel есть ограничение, поэтому он вернет вам эту ошибку.

А еще эта ошибка может возникать, когда вы пытаетесь рассчитать то, что невозможно рассчитать. Например, вычислить корень из отрицательного числа.

Мы рассмотрели лишь самые простые примеры, но я вставил их в эту статью для того, чтобы вы поняли сам тип ошибок. Конечно у вас могут быть другие ситуации, при которых вы получаете вышеуказанные ошибки. Итак, что же с ними делать?

Функция ЕСЛИОШИБКА хороший метод фильтрации этих ошибок.

Функция ЕСЛИОШИБКА

Итак, как понятно из названия, функция обрабатывает ошибку и возвращает указанное значение, если в результате выполнения какой-либо функции возникла ошибка.

Синтаксис

=ЕСЛИОШИБКА(функция; значение_если_ошибка)

Входные аргументы

• функция — это часть, которая в случае возникновения ошибки будет обработана;

• значение_если_ошибка — значение которое будет результатом выполнения функции ЕСЛИОШИБКА при наличии ошибки.

Важная информация:

• Вы можете указать «» во втором аргументе функции, тогда, при наличии ошибки, результатом выполнения функции будет пустое место.

• Если второй аргумент функции — значение массива, то Excel вернет результат функции с каждым значением массива.

Варианты использования

Вернем пустую ячейку вместо ошибки

Итак, мы рассмотрим пример с делением на 0.

На картинке ниже, мы пытаемся делить число 10 на 0, из-за этого возникает ошибка #ДЕЛ/0!.

Давайте попробуем обработать её!

Используем функцию:

=ЕСЛИОШИБКА(A1/A2;"")

Так как в результате выполнения деления, мы получаем ошибку, то функция ЕСЛИОШИБКА вернет нам пустое место, как мы и указали во втором аргументе функции.

В случае ошибки, вы можете вывести что угодно. На ваш выбор.

Давайте сделаем тоже самое, но чтобы вернулось не пустое место, а слово «Ошибка».

=ЕСЛИОШИБКА(A1/A2; "Ошибка")

Фильтрация ошибки #Н/Д

Например, вы вызываете функцию ВПР, а в аргументе указали недоступную ячейку. То есть Excel не может «Подгрузить» значение этой ячейки, из-за этого возникает ошибка.

На примере ниже, вы можете увидеть, как появилась наша ошибка:

Функция ВПР не может найти имя последнего студента в списке и из-за этого появляется ошибка #Н/Д.

Давайте отфильтруем её!

Итак, эта функция, в результате выполнения отдаст нам “Не найдено” при возникновении ошибки.

=ЕСЛИОШИБКА(ВПР(D2;$A$2:$B$12;2;0); "Не найдено")

Также вы можете использовать функцию ЕСНД для обработки ошибок типа #Н/Д, но она работает только для этого типа ошибок.

Возвращаем 0 в случае возникновения ошибки

Мало кто знает, но если мы просто не укажем второй аргумент нашей функции, то при возникновении ошибки, в результате выполнения функции мы получим 0.

Например, та же ошибка #ДЕЛ/0!:

Используем нашу функцию ЕСЛИОШИБКА, но намеренно не будем указывать второй аргумент функции. Посмотрим, что будет:

Используем ЕСЛИОШИБКА совместно с ВПР

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

Чтобы сделать это, мы можем использовать функцию ЕСЛИОШИБКА совместно с ВПР.

Допустим, мы имеем такую табличку:

Давайте найдем оценку для Грейс, для этого используем:

=ЕСЛИОШИБКА(ВПР(G3;$A$2:$B$5;2;0);ЕСЛИОШИБКА(ВПР(G3;$D$2:$E$5;2;0);"Не найдено"))  

Если вы используете такую формулу, то все ошибки будут отфильтрованы так, как вы указали. Даже если эти ошибки возникают из-за выполнения функций на разных листах.