Эксель если ошибка равно 0 - Исправление ошибок и поиск оптимальных решений проблем

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel Web App Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще…Меньше

Функцию ЕСЛИ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 или попросить помощи в сообществе Answers community.

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

Источник

В поисковых функциях Excel: ВПР, ГПР, ПОИСКПОЗ чаще всего в третьем аргументе используется значение ЛОЖЬ или 0. Так пользователь заставляет искать в исходной таблице только точные совпадения значений при поиске. Если в поисковой функции будет в третьем аргументе определено точное совпадение, а искомое значение не будет найдено в таблице, тогда функция возвращает ошибку с кодом #Н/Д!

Формула ЕСЛИОШИБКА обработки ошибок функции ВПР в Excel

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

Ниже на рисунке представлена таблица фирм с фамилиями их руководителей. Вторая таблица содержит те же фамилии и соответствующие им оклады. Функция ВПР используется для соединения двух таблиц в одну. Но не по всем руководителям имеются данные об их окладах, поэтому часто встречается код ошибки #Н/Д! в результатах вычисления функции ВПР.

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

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

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

В данном примере альтернативным значением является пустая строка (двойные кавычки без каких-либо символов между ними). Благодаря этому отчет более читабельный и имеет презентабельный вид. Данная функция может возвращать любое значение, например, «Нет данных» или число 0.

Функции для работы с кодами ошибок в Excel

Функция ЕСЛИОШИБКА проверяет каждую ошибку, которую способна вернуть формула в Excel. Но следует использовать ее с определенной осторожностью. Ведь она способна скрывать все ошибки без разбора и даже такие важные как: #ДЕЛ/0! или #ЧИСЛО! и т.п.

Чтобы скрывать только определенную группу ошибок Excel предлагает еще 3 других функций:

ЕОШИБКА – возвращает логическое значение ИСТИНА если ее аргумент содержит ошибку: #Н/Д, #ЗНАЧ!, #ССЫЛКА!, #ДЕЛ/0!, #ЧИСЛО!, #ИМЯ? или #ПУСТО.
ЕОШ – функция возвращает ИСТИНА если ее аргумент содержит любую ошибку, кроме #Н/Д!
ЕНД – возвращает значение ИСТИНА если ее аргумент содержит ошибку с кодом #Н/Д! или ЛОЖЬ если аргумент содержит любое значение или любую другую ошибку.

Три выше описанные функции для обработки ошибок в Excel возвращают логические значения ИСТИНА или ЛОЖЬ наиболее часто используются вместе с функцией ЕСЛИ.

Формула ЕСЛИ и ЕДН для ошибок ВПР без функции ЕСЛИОШИБКА в Excel

Бескомпромиссная функция обработки ошибок ЕСЛИОШИБКА появилась в программе Excel начиная с 2010-й версии. Для проверки ошибок в старших версиях Excel наиболее часто использовалась функция ЕНД:

Функция ЕНД возвращает логическое значение ИСТИНА если в ее аргументе находится только один тип ошибок – #Н/Д! Или же значение ЛОЖЬ при любых других значениях. В данной формуле функция ЕСЛИ помогает функции ЕНД. Если была получена ошибка #Н/Д! формула возвращает пустую строку – указано во втором аргументе функции ЕСЛИ. В противные случаи возвращается результат вычисления функции ВПР – указано в третьем аргументе ЕСЛИ.

Главным недостатком такой формулы является необходимость дублировать функцию ВПР:

первый разу внутри функции ЕНД;
второй раз в третьем аргументе ЕСЛИ.

Это значит, что Excel должен два раза выполнять функцию ВПР для одной и той же ячейки. Если на листе содержится множество таких формул, тогда их пересчет требует много времени и системных ресурсов. Очень неудобно будет работать с такими файлами. Возникнет необходимость отключения автоматического пересчета формул: «ФОРМУЛЫ»-«Вычисления»-«Параметры вычислений»-«Вручную».

Источник

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

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

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

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

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

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

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

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

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

Где:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Предположим, у вас есть несколько отчетов о продажах из региональных отделений вашей компании, и вы хотите получить сумму по определенному идентификатору заказа. С ячейкой В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! что указывает на то, что наша таблица поиска содержит ошибку деления на ноль.

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

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

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

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

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

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

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

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

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

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

Синтаксис

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

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

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

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

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

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

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

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

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

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

#ДЕЛ/0!

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Источник

#Руководства

18 янв 2023
0

Показали, как работать с логическими функциями Excel: ИСТИНА, ЛОЖЬ, И, ИЛИ, НЕ, ЕСЛИ, ЕСЛИОШИБКА, ЕОШИБКА, ЕПУСТО.

Иллюстрация: Merry Mary для Skillbox Media

Рассказывает просто о сложных вещах из мира бизнеса и управления. До редактуры — пять лет в банке и три — в оценке имущества. Разбирается в Excel, финансах и корпоративной жизни.

Логические функции в Excel проверяют, выполняются ли заданные условия в выбранном диапазоне таблицы. Пользователь указывает критерии, которые нужно проверить, — функции проверяют эти критерии и выдают результат: ИСТИНА или ЛОЖЬ.

Также логические функции используют в сложных расчётах для того, чтобы убрать промежуточные шаги вычисления и объединить несколько действий в одной ячейке. Это значительно сокращает время на обработку данных.

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

Функции ИСТИНА и ЛОЖЬ
Функции И и ИЛИ
Функция НЕ
Функция ЕСЛИ
Функция ЕСЛИОШИБКА
Функция ЕОШИБКА
Функция ЕПУСТО

В конце расскажем, как узнать больше о работе в Excel.

Функции ИСТИНА и ЛОЖЬ не используют самостоятельно — только внутри других логических функций. Они нужны, чтобы отмечать значения как истинные или ложные.

Функция ИСТИНА возвращает только истинные значения. Её синтаксис: =ИСТИНА ().

Функция ЛОЖЬ — возвращает только ложные значения. Её синтаксис: =ЛОЖЬ ().

Функции И и ИЛИ нужны для того, чтобы показать связь между условиями пользователя. Они определяют, будут соблюдены все критерии или только некоторые из них.

Функция И. Её используют, чтобы показать, что указанные число или текст должны соответствовать одновременно всем критериям. В этом случае функция возвращает значение ИСТИНА. Если один из критериев не соблюдается, функция И возвращает значение ЛОЖЬ.

Синтаксис функции И такой: =И(логическое_значение1;логическое_значение2;…), где «логическое_значение» — критерии пользователя, которые функция будет проверять. Всего может быть до 255 критериев.

Пример работы функции И. Проверим, соблюдены ли два условия:

число 662 больше 300;
число 8626 больше 9000.

Для этого выберем любую ячейку и в строке формул введём: =И(A1>300;A2>9000),

где А1 — ячейка с числом 662, А2 — ячейка с числом 8626.

Нажмём Enter. Функция возвращает значение ЛОЖЬ — один из критериев не соблюдён (число 8626 < 9000).

Функция И вернула значение ЛОЖЬ, так как один из критериев не соблюдён
Скриншот: Excel / Skillbox Media

Проверим другие критерии:

число 662 меньше 666;
число 8626 больше 5000.

Снова выберем любую ячейку и в строке формул введём: =И(A1<666;A2>5000).

Функция возвращает значение ИСТИНА — оба критерия соблюдены.

Функция И вернула значение ИСТИНА, так как соблюдены оба критерия
Скриншот: Excel / Skillbox Media

Функция ИЛИ. Её используют, чтобы показать, что указанные число или текст должны соответствовать одному из критериев. Если хотя бы один критерий соблюдён, функция возвращает значение ИСТИНА. Если все критерии не соблюдены, функция ИЛИ возвращает значение ЛОЖЬ.

Синтаксис функции ИЛИ: =ИЛИ(логическое_значение1;логическое_значение2;…).

Максимальное количество логических значений (критериев) — тоже 255.

Пример работы функции ИЛИ. Проверим три критерия:

число 662 меньше 666;
число 8626 больше 5000;
число 567 больше 786.

В строке формул введём: =ИЛИ(A1<666; A2>5000;A3>786).

Функция возвращает значение ИСТИНА, несмотря на то, что один критерий не соблюдён (число 567 < 786).

Функция ИЛИ вернула значение ИСТИНА — соблюдены два критерия из трёх
Скриншот: Excel / Skillbox Media

Проверим другие критерии:

число 662 меньше 500;
число 8626 больше 9000;
число 567 больше 600.

В строке формул введём: =ИЛИ(A1<500;A2>9000;A3>600).

Функция возвращает значение ЛОЖЬ, так как ни один из критериев не соблюдён.

Функция ИЛИ вернула значение ЛОЖЬ — все критерии не соблюдены
Скриншот: Excel / Skillbox Media

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

Если в качестве параметра функции НЕ указать ложное значение — она вернёт значение ИСТИНА. Наоборот, если указать истинное значение, функция вернёт ЛОЖЬ.

Синтаксис функции НЕ: =НЕ(логическое_значение), где «логическое_значение» — выражение, которое нужно проверить на соответствие значениям ИСТИНА или ЛОЖЬ. В этой функции можно использовать только одно такое выражение.

Пример работы функции НЕ. Проверим выражение «662 меньше 500». Выберем любую ячейку и в строке формул введём: =НЕ(A1<500), где А1 — ячейка с числом 662.

Нажмём Enter.

Выражение «662 меньше 500» ложное. Но функция НЕ поменяла значение на противоположное и вернула значение ИСТИНА.

Функция НЕ поменяла ложное значение на противоположное и вернула значение ИСТИНА
Скриншот: Excel / Skillbox Media

Функцию ЕСЛИ используют, когда нужно сравнить данные таблицы с критериями пользователя.

У этой функции также два результата: ИСТИНА и ЛОЖЬ. Первый результат функция выдаёт, когда значение ячейки совпадает с заданным условием, второй — когда значение условию не соответствует.

Например, если нужно определить в таблице значения меньше 1000, то значение 700 будет отмечено функцией как истинное, а значение 3500 — как ложное.

Можно задавать несколько условий одновременно. Например, найти значения меньше 300, но больше 200. В этом случае функция определит значение 100 как ложное, а 250 — как истинное. Так можно проверять не только числовые значения, но и текст.

Синтаксис функции ЕСЛИ: =ЕСЛИ(лог_выражение;значение_если_истина;значение_если_ложь), где:

лог_выражение — запрос пользователя, который функция будет проверять;
значение_если_истина — результат, который функция принесёт в ячейку, если значение совпадёт с запросом пользователя;
значение_если_ложь — результат, который функция принесёт в ячейку, если значение не совпадёт с запросом пользователя.

Пример работы функции ЕСЛИ. Предположим, из столбца с ценами нам нужно выбрать значения до 2 млн рублей.

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

Создаём отдельный столбец, куда функция ЕСЛИ принесёт результаты
Скриншот: Excel / Skillbox Media

В строке формул введём: =ЕСЛИ(A2<2000000;»Подходит»;»Не подходит»)

В строке формул вводим параметры функции ЕСЛИ
Скриншот: Excel / Skillbox Media

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

Так выглядит результат работы функции ЕСЛИ
Скриншот: Excel / Skillbox Media

Функция показала, какие значения соответствуют условию «меньше 2000000», и отметила их как «Подходит». Значения, которые не соответствуют этому условию, отмечены как «Не подходит».

В Skillbox Media есть статья, где подробно объясняли, как использовать функцию ЕСЛИ в Excel — в частности, как запустить функцию ЕСЛИ с несколькими условиями.

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

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

значение — выражение, которое нужно проверить;
значение_если_ошибка — текст, число или формула, которые будут выводиться или выполняться в случае, если в результате проверки аргумента «значение» получен результат ЛОЖЬ.

Если ошибка есть, возвращается значение второго аргумента. Если ошибки нет — первого.

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

Выделим первую ячейку столбца C и введём: =ЕСЛИОШИБКА(A1/B1;»Ошибка в расчёте»)

В строке формул вводим параметры функции ЕСЛИОШИБКА
Скриншот: Excel / Skillbox Media

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

Результат работы функции ЕСЛИОШИБКА
Скриншот: Excel / Skillbox Media

В первой строке функция не нашла ошибок в выражении (360/60), поэтому провела расчёт и показала результат (6).

Во второй строке функция тоже не нашла ошибок (деление 0 на 76) — и показала результат расчёта (0).

В третьей строке функция нашла ошибку — делить на 0 нельзя. Поэтому вместо результата расчёта показала второй аргумент функции: «Ошибка в расчёте».

Эта функция проверяет, не содержат ли заданные ячейки ошибочных значений:

#Н/Д
#ЗНАЧ
#ЧИСЛО!
#ДЕЛ/0!
#ССЫЛКА!
#ИМЯ?
#ПУСТО!

Синтаксис функции ЕОШИБКА: =ЕОШИБКА(значение), где «значение» — ячейка или диапазон ячеек, которые нужно проверить.

Если функция находит ошибочные значения — возвращает значение ИСТИНА. Если не находит — возвращает значение ЛОЖЬ.

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

Выберем любую ячейку, в которой функция должна будет вывести результат. В строке формул введём: =ЕОШИБКА(A1:A6), где A1:A6 — диапазон, который нужно проверить.

В строке формул вводим параметры функции ЕОШИБКА
Скриншот: Excel / Skillbox Media

Нажимаем Enter — функция возвращает значение ИСТИНА. Это значит, что она нашла ошибку в выделенном диапазоне.

Результат работы функции ЕОШИБКА
Скриншот: Excel / Skillbox Media

Дальше эту функцию используют для выполнения других действий.

Например, при возникновении ошибки можно использовать функцию ЕОШИБКА в сочетании с функцией ЕСЛИ: =ЕСЛИ( ЕОШИБКА(B1);»Произошла ошибка»;B1*6).

Эта формула проверит наличие ошибки в ячейке B1. При возникновении ошибки функция ЕСЛИ возвращает сообщение «Произошла ошибка». Если ошибки отсутствуют, функция ЕСЛИ вычисляет произведение B1*6.

Функция ЕПУСТО проверяет, есть ли в выбранных ячейках какие-либо значения или эти ячейки пустые. Если ячейка пустая, функция возвращает значение ИСТИНА, если в ячейке есть данные — ЛОЖЬ.

Синтаксис функции ЕПУСТО: =ЕПУСТО(значение), где значение — ячейка или диапазон ячеек, которые нужно проверить.

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

Выберем любую ячейку и в строке формул введём: =ЕПУСТО(A1:A6), где A1:A6 — диапазон, который нужно проверить.

В строке формул вводим параметры функции ЕПУСТО
Скриншот: Excel / Skillbox Media

Нажимаем Enter — функция возвращает значение ИСТИНА. Это значит, что она нашла пустую ячейку в выделенном диапазоне.

Результат работы функции ЕПУСТО
Скриншот: Excel / Skillbox Media

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

В Excel много функций, которые упрощают и ускоряют работу с таблицами. В этой подборке перечислили 15 статей и видео об инструментах Excel, необходимых в повседневной работе.
В Skillbox есть курс «Excel + Google Таблицы с нуля до PRO». Он подойдёт как новичкам, которые хотят научиться работать в Excel с нуля, так и уверенным пользователям, которые хотят улучшить свои навыки. На курсе учат быстро делать сложные расчёты, визуализировать данные, строить прогнозы, работать с внешними источниками данных, создавать макросы и скрипты.
Кроме того, Skillbox даёт бесплатный доступ к записи онлайн-интенсива «Экспресс-курс по Excel: осваиваем таблицы с нуля за 3 дня». Он подходит для начинающих пользователей. На нём можно научиться создавать и оформлять листы, вводить данные, использовать формулы и функции для базовых вычислений, настраивать пользовательские форматы и создавать формулы с абсолютными и относительными ссылками.

Другие материалы Skillbox Media по Excel

Учись бесплатно:
вебинары по программированию, маркетингу и дизайну.

Участвовать

Научитесь: Excel + Google Таблицы с нуля до PRO
Узнать больше

Источник

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

Синтаксис

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

Примеры обработки ошибок в Excel с помощью функции ЕСЛИОШИБКА

Вы можете скачать этот шаблон Excel с функцией ЕСЛИОШИБКА здесь — Шаблон Excel с функцией ЕСЛИОШИБКА

Пример # 1 — Обработка ошибки # Н / Д

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

Итак, мы применили формулу ВПР к правому соседнему столбцу списка B, чтобы найти общие имена в обоих списках и выделить имена, которых нет в списке A.

Формула ВПР в C2 и перетаскивая ее вниз, мы имеем

= ВПР (B2; $ A: $ A; 1,0)

Как вы можете видеть выше, некоторые имена не могут быть найдены с помощью формулы ВПР; следовательно, они сгенерировали ошибку # Н / Д, что означает, что ВПР не удалось найти эти имена в списке сотрудников в столбце A.

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

Итак, передадим формулу поиска, ВПР (B2; $ A: $ A; 1,0) как первый аргумент и Имя отсутствует в списке А как второй аргумент функции ЕСЛИОШИБКА. Итак, формула принимает вид

= ЕСЛИОШИБКА (ВПР (B2; $ A: $ A; 1,0); «Имя отсутствует в списке A»)

Перетаскивая эту формулу в последнюю ячейку, мы получаем замененное значение # N / A с переданным значением.

Пример №2 — Обработка # DIV / 0! ошибка

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

Итак, когда мы применяем формулу = Количество / Количество людей

Получаем ошибку # DIV / 0! в C4, когда количество людей равно 0. Когда мы делим числовое значение на ноль, мы получаем эту ошибку.

Теперь мы хотим заменить эту ошибку значением ‘Кол-во лиц <1‘, мы будем использовать функцию ЕСЛИОШИБКА.

= ЕСЛИОШИБКА ((A2 / B2)); Число лиц <1 ‘)

Пример № 3 — Обработка #NAME? ошибка

Предположим, у нас есть некоторые числа, которые нужно сложить, и по ошибке, если кто-то напишет неправильную формулу для суммы, мы получим # ИМЯ? ошибка.

Вместо SUM, если пользователь напишет неправильную формулу, #NAME? генерируется ошибка.

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

Пример # 4 — Обработка #NULL! ошибка

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

В ячейке A5 мы использовали оператор «плюс», чтобы добавить их, A2 + A3 A4, и вместо того, чтобы помещать оператор «+» между A3 и A4, мы ввели разделитель, нажав пробел, мы получили #NULL! Ошибка.

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

= ЕСЛИОШИБКА (A2 + A3 A4); (СУММ (A2: A4)))

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

Пример # 5 — Обработка ошибки #NUM

У нас есть некоторые числа, указанные в столбце A, и мы хотим найти квадратные корни из этих чисел.

В Excel у нас есть SQRT в excel для вычисления квадратного корня из числа, когда мы применяем эту формулу для получения квадратного корня из чисел.

Получаем ошибку # ЧИСЛО! для отрицательных значений, поскольку мы не можем найти квадратный корень из отрицательного числа. Итак, excel выбрасывает # ЧИСЛО! ошибка, когда функция КОРЕНЬ применяется к отрицательному числу.

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

Пример №6 — Обработка #REF! ошибка

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

Теперь, если мы удалим третью строку, выбрав 3^rd row полностью, мы получаем #REF! ошибка в ячейке C2

Поскольку ссылка была удалена, Excel не может найти справочное значение в формуле; следовательно, он выдает #REF! ошибка.

Используя функцию Excel IFERROR, мы можем передать value_if_error, ‘Ссылка удалена. ‘

Пример №7 — Обработка #VALUE! ошибка

У нас есть два значения в ячейках A2 и A3, A2, и мы хотим добавить эти два значения в ячейки A2 и A3,

A2 содержит числовое значение, а A3 — текстовое значение. Когда мы складываем эти два значения, мы получаем #VALUE! Ошибка.

Поскольку в формуле используется неправильный тип данных, она вернула #VALUE! ошибка

Исправляя это с помощью ЕСЛИОШИБКА, мы имеем

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

УЗНАТЬ БОЛЬШЕ >>

Post Views: 1 162

Источник

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

Смотрите также ЕСЛИОШИБКА (IFERROR), которая раз, нажмите кнопку конечный результат, поскольку перемещать и закреплять,

Описание

ссылки в формуле.Пропустить ошибку ячеек. Если третья в числовой формат.установите или снимите отформатируйте ячейки послеЧисло уровней вложения функций может быть до (на английском языке).

Синтаксис

для расчета вычетов

слово ОШИБКА! ВЕдиниц продано

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

Замечания

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

Примеры

не используется в‘=СУММ(A1:A10) из следующих правил:Например, если для прибавления 64Пример одного аргумента: иногда возвращают значения доходов. содержимое ячейки210 использование функции и, в случаеЧтобы закончить вычисление, нажмите

и логических проверок.	закрепить ее в
разделяются друг от	пропускаться.
расчете, поэтому результатом	считается текстом.
	Ячейки, которые содержат формулы,
3100 к значению	В функцию можно вводить	=СУММ(A1:A10)
ошибок. Ниже представлены	=ЕСЛИ(E2A135ЕСЛИОШИБКА возникновения любой ошибки, кнопку Но с помощью нижней части окна.	друга (области C2):
Если формула не может	будет значение 22,75.Формулы, несогласованные с остальными приводящие к ошибкам. в ячейке A3 (или вкладывать) не. некоторые инструменты, с	Обычным языком это можно
или, соответственно, результат	6в Microsoft Excel. выдавать вместо нееЗакрыть диалогового окна На панели инструментов C3 и E4: правильно вычислить результат,	Если эта ячейка

Пример 2

формулами в области.	Формула имеет недопустимый синтаксис	используется формула
более 64 уровней	Пример нескольких аргументов:	помощью которых вы
выразить так:	вычисления выражения A1/A2.	55
	Данная функция возвращает указанное	заданное значение: ноль,
.	Вычисление формулы	выводятся следующие свойства
E6 не пересекаются,	в Excel отображается содержит значение 0, Формула не соответствует шаблону или включает недопустимые=СУММ(3 100;A3) вложенных функций.=СУММ(A1:A10;C1:C10) можете искать иЕСЛИ значение в ячейкеФункция ЕСЛИОШИБКА() впервые появилась	0
значение, если вычисление	пустую текстовую строкуПримечания:вы можете увидеть, ячейки: 1) книга, поэтому при вводе значение ошибки, например результат будет равен других смежных формул. аргументы или типы	, Excel не складывает
Имена других листов должны	. исследовать причины этих A5 меньше чем в EXCEL 2007Ошибка при вычислении по формуле вызывает «» или что-то как разные части 2) лист, 3)	формулы

	;##, #ДЕЛ/0!, #Н/Д, 18,2. Часто формулы, расположенные данных. Значения таких 3100 и значение быть заключены вВ приведенной ниже таблице ошибок и определять 31 500, значение умножается и упростила написание23

support.office.com

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

ошибку; в противном еще.Некоторые части формул, в вложенной формулы вычисляются имя (если ячейка= Sum (C2: C3 #ИМЯ?, #ПУСТО!, #ЧИСЛО!,В таблицу введены недопустимые рядом с другими

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

 ошибок: #ДЕЛ/0!, #Н/Д, в ячейке A3

 одинарные кавычки собраны некоторые наиболее решения. на 15 %. Но формул для обработки

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

0 случае функция возвращаетСинтаксис функции следующий: которых используются функции в заданном порядке. входит в именованный E4: E6)

#ССЫЛКА!, #ЗНАЧ!. Ошибки данные. формулами, отличаются только #ИМЯ?, #ПУСТО!, #ЧИСЛО!, (как было бы
Если формула содержит ссылки

частые ошибки, которыеПримечание: ЕСЛИ это не ошибок. Если раньшеФормула результат формулы. Функция=ЕСЛИОШИБКА(Что_проверяем; Что_выводить_вместо_ошибки)ЕСЛИ Например, формулу =ЕСЛИ(СРЗНАЧ(D2:D5)>50;СУММ(E2:E5);0) диапазон), 4) адресвозвращается значение #NULL!. разного типа имеют

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

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

excel2.ru

Исправление ошибки #ЗНАЧ! в функции ЕСЛИ

далее примере, состоящем Причины появления этих=СУММ(3100;A3) ячейки на других вводе формулы, и методы, которые помогут ли это значение, на подобие этойРезультат и обрабатывать ошибки можно было быВЫБОР если вы увидите и 6) формула. запятые между диапазонами разные способы решения. просмотреть параметры проверки из четырех смежных

Проблема: аргумент ссылается на ошибочные значения.

ошибок различны, как), а суммирует числа листах или в описаны способы их

 вам исправлять ошибки чем 72 500. ЕСЛИ =ЕСЛИ(ЕОШИБКА(A1);»ОШИБКА!»;A1), то теперь=C2 в формула. все исправить так:, не вычисляются. В промежуточные результаты:Примечание: C и EПриведенная ниже таблица содержит для ячейки, на формул, Excel показывает

и способы их 3 и 100,
других книгах, а исправления. в формулах. Этот

 это так, значение достаточно записать =ЕСЛИОШИБКА(A1;»ОШИБКА!»):

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

Проблема: неправильный синтаксис.

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

 формулы» быть только однофункции = Sum (C2: в которых подробноДанные формулой =СУММ(A10:C10) вПримечание: полученный результат к или листа содержитДополнительные сведения

он не охватывает

в противном случае — в ячейке в первом аргументе ниже.Обратите внимание, что эта значение #Н/Д.Описание контрольное значение. C3, E4: E6). описаны эти ошибки,в группе ячейке D4, так Если ввести значение ошибки значению в ячейке пробелы или другие

Начинайте каждую формулу со все возможные ошибки на 28 %A1 в первом элементе

Значение

функция появилась толькоЕсли ссылка пуста, в=ЕСЛИ(СРЗНАЧ(D2:D5)>50;СУММ(E2:E5);0)Добавление ячеек в окноИсправление ошибки #ЧИСЛО! и краткое описание.Работа с данными как значения в прямо в ячейку, A3. Другой пример: небуквенные символы, его знака равенства (=) формул. Для получения.ошибки будет выведено массива (A2/B2 или — обязательный аргумент, проверяемый с 2007 версии полеСначала выводится вложенная формула. контрольного значенияЭта ошибка отображается вСтатьянажмите кнопку

 смежных формулах различаются оно сохраняется как если ввести =ABS(-2 необходимо заключить вЕсли не указать знак справки по конкретнымЧтобы использовать функцию ЕСЛИОШИБКА значение ОШИБКА!, в деление 210 на на возникновение ошибок. Microsoft Excel. ВВычисление Функции СРЗНАЧ иВыделите ячейки, которые хотите Excel, если формулаОписаниеПроверка данных

У вас есть вопрос об определенной функции?

на одну строку, значение ошибки, но

Помогите нам улучшить Excel

134), Excel выведет одиночные кавычки (‘), равенства, все введенное ошибкам поищите ответ с уже имеющейся противном случае - 35), не обнаруживает

support.office.com

Поиск ошибок в формулах

Значение_при_ошибке более ранних версияхотображается нулевое значение СУММ вложены в просмотреть. или функция содержитИсправление ошибки ;#. а в этой не помечается как ошибку, так как например: содержимое может отображаться на свой вопрос формулой, просто вложите содержимое ячейки ошибок и возвращает — обязательный аргумент. Значение, приходилось использовать функции (0). функцию ЕСЛИ.

Чтобы выделить все ячейки недопустимые числовые значения.Эта ошибка отображается вВыберите лист, на котором формуле — на ошибка. Но если функция ABS принимает=’Данные за квартал’!D3 или как текст или

 или задайте его готовую формулу вA1 результат вычисления по возвращаемое при ошибкеЕОШ (ISERROR)Некоторые функции вычисляются зановоДиапазон ячеек D2:D5 содержит с формулами, наВы используете функцию, которая Excel, если столбец требуется проверить наличие 8 строк. В на эту ячейку только один аргумент:

Ввод простой формулы

=‘123’!A1 дата. Например, при на форуме сообщества функцию ЕСЛИОШИБКА:. формуле при вычислении пои при каждом изменении

значения 55, 35,

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

 ссылается формула из

=ABS(-2134). вводе выражения Microsoft Excel.=ЕСЛИОШИБКА(ЕСЛИ(E2ЕСЛИ — одна из самых6
формуле. Возможны следующиеЕНД (ISNA) листа, так что 45 и 25,Главная
ВСД или ставка? показать все символыЕсли расчет листа выполнен формулой является =СУММ(A4:C4).
другой ячейки, эта.Указывайте после имени листаСУММ(A1:A10)Формулы — это выражения, сЭто означает, что ЕСЛИ универсальных и популярных=C3 типы ошибок: #Н/Д,. Эти функции похожи результаты в диалоговом

 поэтому функцияв группе Если да, то в ячейке, или вручную, нажмите клавишуЕсли используемые в формуле формула возвращает значениеВы можете использовать определенные восклицательный знак (!),в Excel отображается помощью которых выполняются в результате вычисления функций в Excel,Выполняет проверку на предмет #ЗНАЧ!, #ССЫЛКА!, #ДЕЛ/0!, на окнеСРЗНАЧ(D2:D5)Редактирование #NUM! ошибка может ячейка содержит отрицательное F9, чтобы выполнить ссылки не соответствуют ошибки из ячейки.

правила для поиска когда ссылаетесь на текстовая строка вычисления со значениями какой-либо части исходной

которая часто используется ошибки в формуле #ЧИСЛО!, #ИМЯ? и

ЕСЛИОШИБКАВычисление формулывозвращает результат 40.

Исправление распространенных ошибок при вводе формул

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

него в формуле

СУММ(A1:A10)


на листе. Формула формулы возвращается ошибка,	в одной формуле в первом аргументе #ПУСТО!., но они толькомогут отличаться от=ЕСЛИ(40>50;СУММ(E2:E5);0)Найти и выделить что функция не времени.Если диалоговое окно формулах, приложение Microsoft столбце таблицы. Они не гарантируют вместо результата вычисления, начинается со знака выводится значение 0, несколько раз (иногда* во втором элементе*Если «значение» или «значение_при_ошибке» проверяют наличие ошибок тех, которые отображаются
Диапазон ячеек D2:D5 содержит(вы также можете	может найти результат.Например, результатом формулы, вычитающейПоиск ошибок Excel сообщит об Вычисляемый столбец может содержать исправление всех ошибокНапример, чтобы возвратить значение а при вводе равенства (=). Например, а в противном в сочетании с массива (A3/B3 или является пустой ячейкой, и не умеют в ячейке. Это значения 55, 35, нажать клавиши Инструкции по устранению
дату в будущемне отображается, щелкните	ошибке. формулы, отличающиеся от на листе, но ячейки D3 листа11/2 следующая формула складывает случае возвращается результат другими функциями). К деление 55 на функция ЕСЛИОШИБКА рассматривает заменять их на функции
45 и 25,	CTRL+G см. в разделе из даты в вкладку
Формулы, не охватывающие смежные	основной формулы столбца, могут помочь избежать «Данные за квартал»в Excel показывается числа 3 и выражения ЕСЛИ. Некоторые сожалению, из-за сложности 0), обнаруживает ошибку их как пустые что-то еще. ПоэтомуСЛЧИС поэтому функция СРЗНАЧ(D2:D5)или справки.
прошлом (=15.06.2008-01.07.2008), являетсяФормулы ячейки.	что приводит к распространенных проблем. Эти в той же дата
1: пользователи при создании конструкции выражений с	«деление на 0″ строковые значения («»). приходилось использовать их, возвращает результат 40.CONTROL+GИсправление ошибки #ССЫЛКА! отрицательное значение даты., выберите Ссылки на данные, вставленные возникновению исключения. Исключения правила можно включать книге, воспользуйтесь формулой11.фев=3+1
формул изначально реализуют ЕСЛИ легко столкнуться и возвращает «значение_при_ошибке»Если «значение» является формулой обязательно в связке	ОБЛАСТИ=ЕСЛИ(ЛОЖЬ;СУММ(E2:E5);0)на компьютере Mac).Эта ошибка отображается вСовет:Зависимости формул между исходным диапазоном
вычисляемого столбца возникают и отключать независимо	=’Данные за квартал’!D3(предполагается, что дляФормула также может содержать обработку ошибок, однако с ошибкой #ЗНАЧ!.Ошибка при вычислении массива, функция ЕСЛИОШИБКА с функцией проверки,Поскольку 40 не больше Затем выберите Excel при наличии Попробуйте автоматически подобрать размери нажмите кнопку и ячейкой с при следующих действиях: друг от друга.. ячейки задан формат один или несколько делать это не Обычно ее можно=C4 возвращает массив результатовЕСЛИ (IF)ИНДЕКС 50, выражение вВыделить группу ячеек недопустимой ссылки на ячейки с помощьюПоиск ошибок формулой, могут неВвод данных, не являющихсяСуществуют два способа пометкиУказывайте путь к внешнимОбщий из таких элементов: рекомендуется, так как подавить, добавив в
Выполняет проверку на предмет для каждой ячейки	, создавая вложенные конструкции, первом аргументе функциии ячейку. Например, вы двойного щелчка по. включаться в формулу формулой, в ячейку и исправления ошибок: книгам), а не результат функции, ссылки, операторы обработчик подавляет возможные формулу функции для ошибки в формуле диапазона, указанного в типа:СМЕЩ ЕСЛИ (аргумент лог_выражение)Формулы удалили ячейки, на заголовкам столбцов. ЕслиЕсли вы ранее не автоматически. Это правило вычисляемого столбца. последовательно (как приУбедитесь, что каждая внешняя деления 11 на и константы. ошибки и вы обработки ошибок, такие в первом аргументе значении. См. второйТакой вариант ощутимо медленне, имеет значение ЛОЖЬ.. которые ссылались другие отображается # # проигнорировали какие-либо ошибки,

Исправление распространенных ошибок в формулах

позволяет сравнить ссылкуВведите формулу в ячейку проверке орфографии) или ссылка содержит имя 2.Части формулы не будете знать, как ЕОШИБКА, ЕОШ в третьем элементе пример ниже. работает и сложнее

ЯЧЕЙКАФункция ЕСЛИ возвращает значениеНа вкладке формулы, или вставили #, так как вы можете снова в формуле с вычисляемого столбца и

сразу при появлении книги и путьСледите за соответствием открывающихФункции: включены в _з0з_, правильно ли работает или ЕСЛИОШИБКА. массива (A4/B4 илиСкопируйте образец данных из для понимания, так, третьего аргумента (аргументФормулы поверх них другие Excel не может

Включение и отключение правил проверки ошибок

проверить их, выполнив фактическим диапазоном ячеек, нажмите ошибки во время к ней. и закрывающих скобок функции обрабатываются формулами, формула. Если вам
Если имеется ссылка на деление «» на следующей таблицы и что лучше использоватьДВССЫЛ

значение_если_ложь). Функция СУММв группе ячейки. отобразить все символы, следующие действия: выберите смежных с ячейкой,клавиши CTRL + Z
ввода данных наСсылка на книгу содержитВсе скобки должны быть которые выполняют определенные нужно добавить обработчик ячейку с ошибочным 23), не обнаруживает вставьте их в
новую функцию ЕСЛИОШИБКА,, не вычисляется, посколькуЗависимости формулВы случайно удалили строку которые это исправить.
файл которая содержит формулу.или кнопку листе. имя книги и
-  парными (открывающая и вычисления. Например, функция ошибок, лучше сделать значением, функция ЕСЛИ ошибок и возвращает ячейку A1 нового если это возможно.ЧСТРОК она является вторымнажмите кнопку или столбец? МыИсправление ошибки #ДЕЛ/0!>
  
   Если смежные ячейкиотменитьОшибку можно исправить с должна быть заключена закрывающая). Если в Пи () возвращает это тогда, когда возвращает ошибку #ЗНАЧ!. результат вычисления по листа Excel. ЧтобыArkaIIIa,
-  аргументом функции ЕСЛИОкно контрольного значения удалили столбец BЭта ошибка отображается вПараметры содержат дополнительные значения_з0з_ на помощью параметров, отображаемых в квадратные скобки
  - формуле используется функция, значение числа Пи: вы будете уверены,
  - Решение формуле отобразить результаты формул,: Господа, помогите, пожалуйста,ЧИСЛСТОЛБ (аргумент значение_если_истина) и. в этой формуле Excel, если число
  - > и не являютсяпанели быстрого доступа приложением Excel, или
  - ( для ее правильной 3,142… что формула работает: используйте с функцией0 выделите их и
  - разобраться с проблемой., возвращается только тогда,Нажмите кнопку = SUM (A2, делится на ноль
- формулы пустыми, Excel отображает. игнорировать, щелкнув команду[Имякниги.xlsx] работы важно, чтобыСсылки: ссылки на отдельные правильно. ЕСЛИ функции дляПримечание. Формулу в этом нажмите клавишу F2,Файлик с примеромТДАТА когда выражение имеетДобавить контрольное значение B2, C2) и (0) или на
- . В Excel для рядом с формулойВвод новой формулы вПропустить ошибку). В ссылке также все скобки стояли ячейки или диапазоныПримечание: обработки ошибок, такие примере необходимо вводить а затем — в приложении., значение ИСТИНА.. рассмотрим, что произошло.
-  ячейку без значения. Mac в ошибку. вычисляемый столбец, который. Ошибка, пропущенная в должно быть указано в правильных местах. ячеек. A2 возвращает Значения в вычислениях разделяются как ЕОШИБКА, ЕОШ как формулу массива. клавишу ВВОД. ПриЕсть определенная программа,СЕГОДНЯВыделите ячейку, которую нужноУбедитесь, что вы выделилиНажмите кнопкуСовет:меню Excel выберите ПараметрыНапример, при использовании этого уже содержит одно конкретной ячейке, не
  
  имя листа в Например, формула значение в ячейке точкой с запятой. и ЕСЛИОШИБКА. В После копирования этого
-  необходимости измените ширину которая выгружает отчеты, вычислить. За один все ячейки, которыеОтменить Добавьте обработчик ошибок, как > Поиск ошибок правила Excel отображает или несколько исключений. будет больше появляться книге.=ЕСЛИ(B5 не будет работать, A2. Если разделить два следующих разделах описывается, примера на пустой столбцов, чтобы видеть в Эксель в
  
  СЛУЧМЕЖДУ раз можно вычислить хотите отследить, и(или клавиши CTRL+Z), в примере ниже:. ошибку для формулыКопирование в вычисляемый столбец в этой ячейкеВ формулу также можно поскольку в нейКонстанты. Числа или текстовые значения запятой, функция
- как использовать функции лист выделите диапазон все данные. том виде, в. только одну ячейку. нажмите кнопку чтобы отменить удаление, =ЕСЛИ(C2;B2/C2;0).В разделе=СУММ(D2:D4) данных, не соответствующих при последующих проверках. включить ссылку на две закрывающие скобки значения, введенные непосредственно ЕСЛИ будет рассматривать ЕСЛИ, ЕОШИБКА, ЕОШ ячеек (C2:C4), нажмитеКотировка
-  котором это указаноОтображение связей между формуламиОткройте вкладкуДобавить измените формулу илиИсправление ошибки #Н/ДПоиск ошибок, поскольку ячейки D5,
  
  формуле столбца. Если Однако все пропущенные книгу, не открытую и только одна в формулу, например их как одно и ЕСЛИОШИБКА в клавишу F2, аЕдиниц продано в табличке «Данные». и ячейкамиФормулы
- . используйте ссылку наЭта ошибка отображается ввыберите D6 и D7, копируемые данные содержат ранее ошибки можно в Excel. Для открывающая (требуется одна 2. дробное значение. После формуле, если аргумент затем нажмите клавиши

Последовательное исправление распространенных ошибок в формулах

210 Т.е. цифра-пробел-буква (либоРекомендации, позволяющие избежать появления
и выберитеЧтобы изменить ширину столбца, непрерывный диапазон (=СУММ(A2:C2)), Excel, если функции

Сброс пропущенных ошибок смежные с ячейками, формулу, эта формула сбросить, чтобы они этого необходимо указать открывающая и однаОператоры: оператор * (звездочка) процентных множителей ставится ссылается на ошибочные CTRL + SHIFT
35 с либо i) неработающих формулЗависимости формул перетащите правую границу которая автоматически обновится или формуле недоступнои нажмите кнопку на которые ссылается перезапишет данные в снова появились. полный путь к закрывающая). Правильный вариант служит для умножения символ %. Он

значения. + ВВОД.55Задача сделать табличку,Тот, кто никогда не> его заголовка.

 при удалении столбца значение.ОК формула, и ячейкой вычисляемом столбце.В Excel для Windows

 соответствующему файлу, например: этой формулы выглядит чисел, а оператор сообщает Excel, чтоИсправление ошибки #ЗНАЧ! в
Функция ЕОШИБКА(), английский вариант0 которая бы формульно ошибался — опасен.Вычислить формулу
Чтобы открыть ячейку, ссылка B.Если вы используете функцию

. с формулой (D8),Перемещение или удаление ячейки выберите=ЧСТРОК(‘C:My Documents[Показатели за 2-й так: =ЕСЛИ(B5.

Исправление распространенных ошибок по одной

^ (крышка) — для значение должно обрабатываться функции СЦЕПИТЬ ISERROR(), проверяет на23 анализировала эти данные.(Книга самурая).

на которую содержитсяИсправление ошибки #ЗНАЧ! ВПР, что пытаетсяПримечание: содержат данные, на

Исправление ошибки с #

из другой областифайл квартал.xlsx]Продажи’!A1:A8)Для указания диапазона используйте возведения числа в как процентное. ВИсправление ошибки #ЗНАЧ! в равенство значениям #Н/Д,Формула В случае, если

Ошибки случаются. Вдвойне обидно,Нажмите кнопку в записи панелиЭта ошибка отображается в найти в диапазоне

Сброс пропущенных ошибок применяется

которые должна ссылаться


листа, если на	>. Эта формула возвращает двоеточие степень. С помощью противном случае такие функции СРЗНАЧ или #ЗНАЧ!, #ССЫЛКА!, #ДЕЛ/0!,Описание в ячейке данных когда они случаютсяВычислить инструментов «Окно контрольного Excel, если в поиска? Чаще всего ко всем ошибкам, формула. эту ячейку ссылаласьПараметры количество строк вУказывая диапазон ячеек, разделяйте + и – значения пришлось бы СУММ
#ЧИСЛО!, #ИМЯ? или	Результат есть буква i, не по твоей, чтобы проверить значение значения», дважды щелкните формуле используются ячейки, это не так. которые были пропущеныНезаблокированные
одна из строк	> диапазоне ячеек с с помощью двоеточия можно складывать и вводить как дробныеПримечания: #ПУСТО! и возвращает=ЕСЛИОШИБКА(A2/B2;»Ошибка при вычислении») чтобы вся ячейка вине. Так в подчеркнутой ссылки. Результат запись. содержащие данные неПопробуйте использовать ЕСЛИОШИБКА для на всех листах
ячейки, содержащие формулы	в вычисляемом столбце.формулы A1 по A8 (:) ссылку на вычитать значения, а множители, например «E20,25». * ** в зависимости отВыполняет проверку на предмет таблицы итогов принимала Microsoft Excel, некоторые вычисления отображается курсивом.Примечание: того типа. подавления #N/а. В активной книги.
: формула не блокируется	Ячейки, которые содержат годы,или в другой книге первую ячейку и с помощью /Задать вопрос на форумеФункция ЕСЛИОШИБКА появилась в этого ИСТИНА или ошибки в формуле значение «i», а функции и формулыЕсли подчеркнутая часть формулы Ячейки, содержащие внешние ссылкиИспользуются ли математические операторы этом случае выСовет: для защиты. По представленные 2 цифрами.в Excel для (8). ссылку на последнюю — делить их. сообщества, посвященном Excel Excel 2007. Она
ЛОЖЬ.	в первом аргументе если i нет, могут выдавать ошибки является ссылкой на на другие книги, (+,-, *,/, ^) можете использовать следующие Советуем расположить диалоговое окно умолчанию все ячейки Ячейка содержит дату в Mac вПримечание: ячейку в диапазоне.Примечание:У вас есть предложения
гораздо предпочтительнее функций	ЕОШИБКАзначение (деление 210 на то, соответственно, вся не потому, что другую формулу, нажмите отображаются на панели с разными типами возможности:Поиск ошибок на листе заблокированы, текстовом формате, котораяменю Excel выберите Параметры Если полный путь содержит Например: Для некоторых функций требуются по улучшению следующей ЕОШИБКА и ЕОШ,) 35), не обнаруживает ячейка должна иметь вы накосячили при кнопку Шаг с инструментов «Окно контрольного данных? Если это=ЕСЛИОШИБКА(ВПР(D2;$D$6:$E$8;2;ИСТИНА);0)непосредственно под строкой
поэтому их невозможно	при использовании в > Поиск ошибок пробелы, как в=СУММ(A1:A5) элементы, которые называются версии Excel? Если так как неЗначение ошибок и возвращает вид «c». вводе, а из-за заходом, чтобы отобразить значения» только в так, попробуйте использовать

Просмотр формулы и ее результата в окне контрольного значения

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

Нажмите одну из управляющих защищен. Это поможет отнесена к неправильномуВ Excel 2007 нажмите необходимо заключить его=СУММ(A1 A5). Аргументы — это темами на портале построении формулы. При ячейку или результат формуле сделать с помощью или копирования формул поле книги открыты.

 случае функция = Excel не распознает кнопок в правой избежать случайных ошибок,

 веку. Например, датакнопку Microsoft Office

в одиночные кавычки, которая вернет ошибку

значения, которые используются пользовательских предложений для использовании функций ЕОШИБКА вычисления выражения, которое6 вспомогательной таблицы и «с запасом» наВычислениеУдаление ячеек из окна SUM (F2: F5) текст в формуле. части диалогового окна. таких как случайное в формуле =ГОД(«1.1.31»)и выберите (в начале пути #ПУСТО!). некоторыми функциями для Excel.
и ЕОШ формула необходимо проверить.=ЕСЛИОШИБКА(A3/B3;»Ошибка при вычислении») формулы «ПОИСК». Но избыточные ячейки. Классический. Нажмите кнопку контрольного значения
устранит проблему. Например имя диапазона Доступные действия зависят
удаление или изменение может относиться какПараметры Excel и после имениВводите все обязательные аргументы выполнения вычислений. При
Примечание: вычисляется дважды: сначалаФункции ЕОШИБКА() в отличие
Выполняет проверку на предмет по какой то пример — ошибкаШаг с выходомЕсли окно контрольного значенияЕсли ячейки не видны

 или имя функции от типа ошибки. формул. Эта ошибка к 1931, так> книги перед восклицательнымУ некоторых функций есть необходимости аргументы помещаются

 Мы стараемся как можно проверяется наличие ошибок,

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

Формулы знаком). обязательные аргументы. Старайтесь
между круглыми скобками оперативнее обеспечивать вас а затем возвращается

Вычисление вложенной формулы по шагам

считает, что значение в первом аргументе не реагирует на при вычислении среднего: предыдущей ячейке и вкладке просмотра их иПримечание:Далее что ячейка настроена году. Используйте это.Числа нужно вводить без также не вводить функции (). Функция актуальными справочными материалами результат. При использовании

#Н/Д является ошибкой. (деление 55 на	результаты поиска. Почему
Причем заметьте, что итоги	формуле.Формула содержащихся в них Если вы используете функцию, . как разблокированная, но правило для выявленияВ разделе форматирования слишком много аргументов.
ПИ не требует	на вашем языке. функции ЕСЛИОШИБКА формула Т.е. =ЕОШИБКА(НД()) вернет 0), обнаруживает ошибку то она не
в нашей таблице	Кнопкав группе формул можно использовать убедитесь в том,Примечание: лист не защищен. дат в текстовомПоиск ошибокНе форматируйте числа, которыеВводите аргументы правильного типа аргументов, поэтому она Эта страница переведена вычисляется только один ИСТИНА, а =ЕОШ(НД()) «деление на 0″

считает, что 3 тоже уже неШаг с заходомЗависимости формул
панель инструментов «Окно что имя функции Если нажать кнопку Убедитесь, что ячейка формате, допускающих двоякоеустановите флажок вводите в формулу.
В некоторых функциях, например пуста. Некоторым функциям автоматически, поэтому ее раз. вернет ЛОЖЬ.

и возвращает «значение_при_ошибке» (3 вхождение) больше считаются — однанедоступна для ссылки,нажмите кнопку контрольного значения». С написано правильно. ВПропустить ошибку не нужна для толкование.Включить фоновый поиск ошибок Например, если нужноСУММ

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

 задано в формуле. другие, передаваясь по

в формуле во. значения удобно изучать, сумм написана неправильно. последующих проверках будетФормулы, которые ссылаются на или с предшествующим будет помечена треугольником значение 1 000 рублей,
аргументы. В других она может оставить ошибки. Для насЕсли синтаксис функции составлен #ЧИСЛО!, #ИМЯ? или
Выполняет проверку на предметПомогите, пожалуйста, советом, цепочке от одной второй раз илиВыделите ячейки, которые нужно проверять зависимости или Удалите слова «e» пропускаться. пустые ячейки. апострофом. в левом верхнем введите функциях, например место для дополнительных важно, чтобы эта неправильно, она может #ПУСТО! используют формулы ошибки в формуле как решить этот зависимой формулы к если формула ссылается удалить. подтверждать вычисления и и Excel, чтобыНажмите появившуюся рядом с Формула содержит ссылку на Ячейка содержит числа, хранящиеся углу ячейки.1000ЗАМЕНИТЬ аргументов. Для разделения статья была вам

См. также

вернуть ошибку #ЗНАЧ!. следующего вида:

в первом аргументе вопрос.

support.office.com

Перехват ошибок в формулах функцией ЕСЛИОШИБКА (IFERROR)

 другой. Так что на ячейку в
Чтобы выделить несколько ячеек,

результаты формул на исправить их. ячейкой кнопку пустую ячейку. Это как текст. ОбычноЧтобы изменить цвет треугольника,. Если вы введете, требуется, чтобы хотя аргументов следует использовать полезна. Просим васРешение=ЕСЛИ(ЕОШИБКА(A1);»ОШИБКА!»;A1) или =ЕСЛИ(ЕОШИБКА(A1/A2);»ОШИБКА!»;A1/A2) (деление «» наЗаранее благодарю. из-за одной ошибочной отдельной книге. щелкните их, удерживая

больших листах. ПриИсправление ошибки #ПУСТО!Поиск ошибок может привести к это является следствием которым помечаются ошибки, какой-нибудь символ в бы один аргумент запятую или точку уделить пару секунд: проверьте правильность синтаксиса.В случае наличия в 23), не обнаруживает

Michael_S ячейки, в концеПродолжайте нажимать кнопку нажатой клавишу CTRL. этом вам неЭта ошибка отображается ви выберите нужный неверным результатам, как импорта данных из выберите нужный цвет числе, Excel будет имел текстовое значение. с запятой (;)

и сообщить, помогла

 Ниже приведен пример

ячейке ошибок и возвращает:

концов, может перестатьВычислить

Нажмите кнопку требуется многократно прокручивать Excel, когда вы пункт. Доступные команды показано в приведенном других источников. Числа, в поле считать его разделителем. Если использовать в в зависимости от ли она вам, правильно составленной формулы,А1 результат вычисления по200?’200px’:»+(this.scrollHeight+5)+’px’);»>=ЕСЛИ(ЕОШ(ПОИСК(«i»;C4));ПРАВСИМВ(C4;1);»i») работать весь расчет., пока не будутУдалить контрольное значение экран или переходить указываете пересечение двух зависят от типа далее примере. хранящиеся как текст,

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

planetaexcel.ru

Вопрос по формуле ЕСЛИОШИБКА

ошибки или ошибки формуле.ArkaIIIa
Для лечения подобных ситуаций вычислены все части
. к разным частям областей, которые не ошибки. Первый пунктПредположим, требуется найти среднее могут стать причиной. чтобы числа отображались
неправильного типа, ExcelНапример, функция СУММ требует внизу страницы. Для ЕСЛИ вкладывается в при вычислении выражения0: Michael_S, в Microsoft Excel формулы.Иногда трудно понять, как листа. пересекаются. Оператором пересечения содержит описание ошибки.
значение чисел в неправильной сортировки, поэтомуВ разделе с разделителями тысяч может возвращать непредвиденные только один аргумент, удобства также приводим другую функцию ЕСЛИ A1/A2, формулой выводитсяКотировкаСпасибо большое! есть мегаполезная функцияЧтобы посмотреть вычисление еще
вложенная формула вычисляетЭту панель инструментов можно является пробел, разделяющий
Если нажать кнопку

 приведенном ниже столбце лучше преобразовать ихПравила поиска ошибок

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

excelworld.ru

ссылку на оригинал

Источник

Хитрости »

7 Октябрь 2015 48239 просмотров

Случаются ситуации, когда в рабочей книге на листах создано много формул, выполняющих различные задачи. При этом формулы созданы когда-то давно, возможно даже на вами. И формулы возвращают ошибки. Например #ДЕЛ/0!(#DIV/0!). Эта ошибка возникает, если внутри формулы происходит деление на ноль: =A1/B1, где в B1 ноль или пусто. Но могут быть и другие ошибки(#Н/Д, #ЗНАЧ! и т.д.). Можно изменить формулу, добавив проверку на ошибку:
=ЕСЛИ(ЕОШ(A1/B1);0; A1/B1)
=IF(ISERR(A1/B1),0, A1/B1)
аргументы:
=ЕСЛИ(ЕОШ(1 аргумент);2 аргумент; 1 аргумент)
Эти формулы будут работать в любой версии Excel. Правда, функция ЕОШ не обработает ошибку #Н/Д(#N/A). Чтобы так же обработать и #Н/Д необходимо использовать функцию ЕОШИБКА:
=ЕСЛИ(ЕОШИБКА(A1/B1);0; A1/B1)
=IF(ISERROR(A1/B1),0, A1/B1)
Однако далее по тексту я буду применять ЕОШ(т.к. она короче) и к тому же не всегда надо «не видеть» ошибки #Н/Д.
Но для версий Excel 2007 и выше можно применить чуть более оптимизированную функцию ЕСЛИОШИБКА(IFERROR):
=ЕСЛИОШИБКА(A1/B1;0)
=IFERROR(A1/B1,0)
аргументы:
=ЕСЛИОШИБКА(1 аргумент; 2 аргумент)

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

Почему ЕСЛИОШИБКА лучше и я называю её более оптимизированной?

Разберем первую формулу подробнее:

=ЕСЛИ(ЕОШ(A1/B1);0; A1/B1)

Если вычислить пошагово, то увидим, что сначала происходит вычисление выражения

(т.е. деление). И если его результат ошибка – то ЕОШ вернет

ИСТИНА(TRUE)

, которое будет передано в

ЕСЛИ(IF)

. И тогда функцией

ЕСЛИ(IF)

будет возвращено значение из второго аргумента 0.
Но если результат не является ошибочным и

ЕОШ(ISERR)

возвращает

ЛОЖЬ(FALSE)

– то функция заново будет вычислять уже вычисленное ранее выражение:

С приведенной формулой это особой роли не играет. Но если применяется формула вроде ВПР (VLOOKUP) с просмотром на несколько тысяч строк – то вычисление два раза может значительно увеличить время пересчета формул.
Функция же

ЕСЛИОШИБКА(IFERROR)

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

ЕСЛИОШИБКА(IFERROR)

Для чего формулы с ошибками вообще исправлять? Обычно делается для более эстетичного отображения данных в отчетах, особенно если отчеты потом руководству отправляют.

Итак, есть на листе такие формулы, ошибки которых надо обработать. Если подобных формул для исправления одна-две(да даже 10-15) – то проблем почти нет заменить вручную. Но если таких формул несколько десятков, а то и сотен – проблема приобретает почти вселенские масштабы :-). Однако процесс можно упростить через написание относительно простого кода Visual Basic for Application.

Для всех версий Excel:

Sub IfIsErrNull()
    Const sToReturnVal As String = "0"
    'если необходимо вместо нуля возвращать пусто
    'Const sToReturnVal As String = """"""
    Dim rr As Range, rc As Range
    Dim s As String, ss As String
    On Error Resume Next
    Set rr = Intersect(Selection, ActiveSheet.UsedRange)
    If rr Is Nothing Then
        MsgBox "Выделенный диапазон не содержит данных", vbInformation, "www.excel-vba.ru"
        Exit Sub
    End If
 
    For Each rc In rr
        If rc.HasFormula Then
            s = rc.Formula
            s = Mid(s, 2)
            ss = "=" & "IF(ISERR(" & s & ")," & sToReturnVal & "," & s & ")"
            If Left(s, 9) <> "IF(ISERR(" Then
                If rc.HasArray Then
                    rc.FormulaArray = ss
                Else
                    rc.Formula = ss
                End If
                If Err.Number Then
                    ss = rc.Address
                    rc.Select
                    Exit For
                End If
            End If
        End If
    Next rc
    If Err.Number Then
        MsgBox "Невозможно преобразовать формулу в ячейке: " & ss & vbNewLine & _
                Err.Description, vbInformation, "www.excel-vba.ru"
    Else
        MsgBox "Формулы обработаны", vbInformation, "www.excel-vba.ru"
    End If
End Sub

Для версий 2007 и выше

Sub IfErrorNull()
    Const sToReturnVal As String = "0"
    'если необходимо вместо нуля возвращать пусто
    'Const sToReturnVal As String = """"""
    Dim rr As Range, rc As Range
    Dim s As String, ss As String
    On Error Resume Next
    Set rr = Intersect(Selection, ActiveSheet.UsedRange)
    If rr Is Nothing Then
        MsgBox "Выделенный диапазон не содержит данных", vbInformation, "www.excel-vba.ru"
        Exit Sub
    End If
 
    For Each rc In rr
        If rc.HasFormula Then
            s = rc.Formula
            s = Mid(s, 2)
            ss = "=" & "IFERROR(" & s & "," & sToReturnVal & ")"
            If Left(s, 8) <> "IFERROR(" Then
                If rc.HasArray Then
                    rc.FormulaArray = ss
                Else
                    rc.Formula = ss
                End If
                If Err.Number Then
                    ss = rc.Address
                    rc.Select
                    Exit For
                End If
            End If
        End If
    Next rc
 
    If Err.Number Then
        MsgBox "Невозможно преобразовать формулу в ячейке: " & ss & vbNewLine & _
                Err.Description, vbInformation, "www.excel-vba.ru"
    Else
        MsgBox "Формулы обработаны", vbInformation, "www.excel-vba.ru"
    End If
End Sub

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

Копируете приведенный код, переходите в редактор VBA(Alt+F11), создаете стандартный модуль(Insert —Module) и просто вставляете в него этот код. Переходите в нужную книгу Excel и выделяете все ячейки, формулы в которых необходимо преобразовать таким образом, чтобы в случае ошибки они возвращали ноль. Жмете Alt+F8, выбираете код IfIsErrNull(или IfErrorNull, в зависимости от того, какой именно скопировали) и жмете Выполнить.
Ко всем формулам в выделенных ячейках будет добавлена функция обработки ошибки. Приведенные коды учитывают так же:
-если в формуле уже применена функция ЕСЛИОШИБКА или ЕСЛИ(ЕОШ, то такая формула не обрабатывается;
-код корректно обработает так же функции массива;
-выделять можно несмежные ячейки(через Ctrl).
В чем недостаток: сложные и длинные формулы массива могут вызвать ошибку кода, в связи с особенностью данных формул и их обработкой из VBA. В таком случае код напишет о невозможности продолжить работу и выделит проблемную ячейку. Поэтому настоятельно рекомендую производить замены на копиях файлов.
Если значение ошибки надо заменить на пусто, а не на ноль, то надо строку

Const sToReturnVal As String = "0"

Удалить, а перед строкой

'Const sToReturnVal As String = """"""

Удалить апостроф (‘)

Так же можно данный код вызывать нажатием кнопки(Как создать кнопку для вызова макроса на листе) или поместить в надстройку(Как создать свою надстройку?), чтобы можно было вызывать из любого файла.

И небольшое дополнение: старайтесь применять код вдумчиво. Не всегда возврат ошибки мешает. Например, при использовании ВПР иногда полезно видеть какие значения не были найдены.
Так же хочу отметить, что применять надо к реально работающим формулам. Потому как если формула возвращает #ИМЯ!(#NAME!), то это означает, что в формуле неверно записан какой-то аргумент и это ошибка записи формулы, а не ошибка результата вычисления. Такие формулы лучше проанализировать и найти ошибку, чтобы избежать логических ошибок расчетов на листе.

Статья помогла? Поделись ссылкой с друзьями!

Видеоуроки

Поиск по меткам

Access
apple watch
Multex
Power Query и Power BI
VBA управление кодами
Бесплатные надстройки
Дата и время
Записки
ИП
Надстройки
Печать
Политика Конфиденциальности
Почта
Программы
Работа с приложениями
Разработка приложений
Росстат
Тренинги и вебинары
Финансовые
Форматирование
Функции Excel
акции MulTEx
ссылки
статистика

Источник

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

Синтаксис

Замечания

Примеры

Пример 2

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

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

Формула ЕСЛИОШИБКА обработки ошибок функции ВПР в Excel

Функции для работы с кодами ошибок в Excel

Формула ЕСЛИ и ЕДН для ошибок ВПР без функции ЕСЛИОШИБКА в Excel

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

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

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

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

ЕСЛИОШИБКА с функцией ВПР

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

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

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

ЕСЛИОШИБКА против ЕСНД

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

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

Синтаксис

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

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

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

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

Синтаксис

Примеры обработки ошибок в Excel с помощью функции ЕСЛИОШИБКА

Пример # 1 — Обработка ошибки # Н / Д

Пример №2 — Обработка # DIV / 0! ошибка

Пример № 3 — Обработка #NAME? ошибка

Пример # 4 — Обработка #NULL! ошибка

Пример # 5 — Обработка ошибки #NUM

Пример №6 — Обработка #REF! ошибка

Пример №7 — Обработка #VALUE! ошибка

УЗНАТЬ БОЛЬШЕ >> Post Views: 1 162

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

Описание

Синтаксис

Замечания

Примеры

Пример 2

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

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

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

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

Исправление ошибки #ЗНАЧ! в функции ЕСЛИ

Проблема: аргумент ссылается на ошибочные значения.

Проблема: неправильный синтаксис.

У вас есть вопрос об определенной функции?

Помогите нам улучшить Excel

Поиск ошибок в формулах

Ввод простой формулы

Исправление распространенных ошибок при вводе формул

Исправление распространенных ошибок в формулах

Включение и отключение правил проверки ошибок

Последовательное исправление распространенных ошибок в формулах

Исправление распространенных ошибок по одной

Исправление ошибки с #

Просмотр формулы и ее результата в окне контрольного значения

Вычисление вложенной формулы по шагам

См. также

Перехват ошибок в формулах функцией ЕСЛИОШИБКА (IFERROR)

Вопрос по формуле ЕСЛИОШИБКА

Поиск по меткам

УЗНАТЬ БОЛЬШЕ >>

Post Views: 1 162