Excel условие если не ошибка

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для 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 других функций:

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

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

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

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

ЕСЛИ и ЕДН для ошибок ВПР.

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

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

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

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

Skip to content

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

В статье описано, как использовать функцию ЕСЛИОШИБКА в 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. Примеры перехвата и обработки ошибок могут быть полезны и для «чайников», и для более опытных пользователей. 

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

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

  • 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.

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

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

Пример работы функции ЕПУСТО. Проверим, есть ли скрытые символы в ячейках А5 и А6. Визуально эти ячейки пустые.

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

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

Нажимаем Enter — функция возвращает значение ЛОЖЬ. Это значит, что ячейка А5 на самом деле не пустая, в ней есть значение, которое не видно, — например, пробел.

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

Проверим вторую ячейку. Выберем любую ячейку и в строке формул введём: =ЕПУСТО(A6) и нажмём Enter. Функция возвращает значение ИСТИНА. Это значит, что в ячейке А6 нет никаких значений.

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

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

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

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

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

Тот, кто никогда не ошибался — опасен.
(Книга самурая)

Ошибки случаются. Вдвойне обидно, когда они случаются не по твоей вине. Так в Microsoft Excel, некоторые функции и формулы могут выдавать ошибки не потому, что вы накосячили при вводе, а из-за временного отсутствия данных или копирования формул «с запасом» на избыточные ячейки. Классический пример — ошибка деления на ноль при вычислении среднего:

Ошибка деления на ноль

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

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

Синтаксис функции следующий:

=ЕСЛИОШИБКА(Что_проверяем; Что_выводить_вместо_ошибки)

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

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

Все красиво и ошибок больше нет.

Обратите внимание, что эта функция появилась только с 2007 версии Microsoft Excel. В более ранних версиях приходилось использовать функции  ЕОШ (ISERROR) и ЕНД (ISNA). Эти функции похожи на ЕСЛИОШИБКА, но они только проверяют наличие ошибок и не умеют заменять их на что-то еще. Поэтому приходилось использовать их обязательно в связке с функцией проверки ЕСЛИ (IF), создавая вложенные конструкции типа:

Перехват ошибок в функциями ЕСЛИ и ЕОШ

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

Понравилась статья? Поделить с друзьями:
  • Excel ошибка при запуске приложения 0x0000142
  • Excel vba игнорировать ошибку
  • Excel vba игнорирование ошибок
  • Excel vba если ошибка то пропустить
  • Excel value ошибка