Эксель оператор если ошибка

Функция

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

, английский вариант IFERROR(), п

роверяет выражение на равенство значениям

#Н/Д, #ЗНАЧ!, #ССЫЛКА!, #ДЕЛ/0!, #ЧИСЛО!, #ИМЯ? или #ПУСТО! Если проверяемое выражение или значение в ячейке содержит ошибку, то функция возвращает определенное для этого случая значение, в противном случае — результат вычисления выражения или содержимое ячейки.

Функция

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

впервые появилась в EXCEL 2007.

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

ЕСЛИОШИБКА

(

значение

;

значение_при_ошибке

)

Значение

— аргумент, проверяемый на возникновение ошибок.

Значение_при_ошибке

— значение, возвращаемое при ошибке.

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

файл примера

):

=ЕСЛИОШИБКА(A2/B2;»Ошибка расчете»)

Формула проверяет на предмет ошибки результат вычисления

A2/B2.

Если результат вычисления не является ошибкой

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

, то формула возвращает результат вычисления

A2/B2

, если обнаруживает, то возвращает строку

Ошибка в расчете.

Функция

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

vs

ЕОШИБКА()

В отличие от функции

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

функция

ЕОШИБКА()

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

ЕСЛИ()

:

=ЕСЛИ(ЕОШИБКА(A2/B2);»Ошибка в расчетах»;A2/B2)

Т.к. функция

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

впервые появилась в EXCEL 2007, то в более ранних версиях придется использовать функцию

ЕОШИБКА()

.

Обратите внимание, что использована именно

ЕОШИБКА()

, а не

ЕОШ()

, т.к. для последней ошибка #Н/Д ошибкой не является (см. рисунок выше).

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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



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

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

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

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

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

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

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

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

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

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

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

Функция ИСТИНА

Не принимает никаких аргументов и просто возвращает логическое значение «ИСТИНА».

Синтаксис: =ИСТИНА()

Функция ЛОЖЬ

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

Синтаксис: =ЛОЖЬ()

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

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

Функция принимает 2 аргумента, все они являются обязательными.

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

Пример использования функции:

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

Довольно часто количество возможных условий не 2 (проверяемое и альтернативное), а 3, 4 и более. В этом случае также можно использовать функцию ЕСЛИ, но теперь ее придется вкладывать друг в друга, указывая все условия по очереди. Рассмотрим следующий пример.

Нескольким менеджерам по продажам нужно начислить премию в зависимости от выполнения плана продаж. Система мотивации следующая. Если план выполнен менее, чем на 90%, то премия не полагается, если от 90% до 95% — премия 10%, от 95% до 100% — премия 20% и если план перевыполнен, то 30%. Как видно здесь 4 варианта. Чтобы их указать в одной формуле потребуется следующая логическая структура. Если выполняется первое условие, то наступает первый вариант, в противном случае, если выполняется второе условие, то наступает второй вариант, в противном случае если… и т.д. Количество условий может быть довольно большим. В конце формулы указывается последний альтернативный вариант, для которого не выполняется ни одно из перечисленных ранее условий (как третье поле в обычной формуле ЕСЛИ). В итоге формула имеет следующий вид.

Комбинация функций ЕСЛИ работает так, что при выполнении какого-либо указанно условия следующие уже не проверяются. Поэтому важно их указать в правильной последовательности. Если бы мы начали проверку с B2<1, то условия B2<0,9 и B2<0,95 Excel бы просто «не заметил», т.к. они входят в интервал B2<1 который проверился бы первым (если значение менее 0,9, само собой, оно также меньше и 1). И тогда у нас получилось бы только два возможных варианта: менее 1 и альтернативное, т.е. 1 и более.

При написании формулы легко запутаться, поэтому рекомендуется смотреть на всплывающую подсказку.

В конце нужно обязательно закрыть все скобки, иначе эксель выдаст ошибку

Основные операции

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

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

Ведение простейших подсчётов

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

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

Часто необходимо не просто осуществлять подсчёты, используя данные в таблице, а проводить анализ, сопровождаемый несколькими условиями. Опять-таки всё это проделывать самостоятельно вручную неразумно, когда имеются такие уникальные возможности Excel, способной выполнять любые требования пользователя.

В категории «Логические» самой распространённой является функция ЕСЛИ. Эта функция рассматривает предложенные варианты, а далее определяет, выполняется это условие или нет. Результат функции ЕСЛИ зависит от того, что в итоге желает видеть пользователь. А для того чтобы результат правильно отображался, следует знать, как задать условие в Excel.

Функция ЕСЛИ состоит из трёх аргументов:

• Логического (основного) выражения (требование, которое пользователь желает проверить в таблице);
• значения, отображающегося, если условие будет полностью соблюдаться;
• значения, если заданное требование не выдерживается.

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

В частности, в ячейку А1 ввести числовой показатель «5», а в ячейку А2 показатель «8». Если пользователю необходимо сравнить два показателя и выдать автоматический итог произведённых действий, составляется выражение, которое, безусловно, начинается со знака «равно»: =ЕСЛИ(А1>А2; «Больше»; «Меньше»). После введения этой записи в любую свободную ячейку, нажатия Enter, в ней сразу будет демонстрироваться ответ. Если пользователь заменит значения в А1 или А2, то ответ также автоматически перестроится.

В тех случаях, когда важно выявить несовпадение двух показателей, введённых в соседние столбцы, первоначально нужно вникнуть, как в Excel правильно записать условие неравно, после чего создать запрос, используя логические функции. Знак «неравно» в Excel обозначается двумя операторами — «<>». Соответственно, нужно записать так: =ЕСЛИ(А1<>А2; «Значения разные»; «Значения одинаковые»).

Логический набор

Количество логических функций меняется в зависимости от версии программы. В приложении 2007 года их было 7, впоследствие добавилось еще несколько. Список доступных логических операций можно посмотреть так:

• зайти во вкладку «Формулы» на главной панели;
• кликнуть по иконке fx с надписью «Вставить формулу
  
• в появившемся окне выбрать категорию «Логические
  
• внизу откроется список доступных операторов.

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

Видеоурок

Что возвращает функция

Указанное вами значение, в случае если в ячейке есть ошибка.

Синтаксис

=IFERROR(value, value_if_error) — английская версия

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

Аргументы функции

• value (значение) — это аргумент, который проверяет, есть ли в ячейке ошибка. Обычно, ошибкой может быть результат какого либо вычисления;
• value_if_error (значение_если_ошибка) — это аргумент, который заменяет ошибку в ячейке (в случае её наличия) на указанное вами значение. Ошибки могут выглядеть так:  #N/A, #REF!, #DIV/0!, #VALUE!, #NUM!, #NAME?, #NULL! (английская версия Excel) или #ЗНАЧ!, #ДЕЛ/0, #ИМЯ?, #Н/Д, #ССЫЛКА!, #ЧИСЛО!, #ПУСТО! (русская версия Excel).

Дополнительная информация

• Если вы используете кавычки («») в качестве аргумента value_if_error (значение_если_ошибка), ячейка ничего не отображает в случае ошибки.
• Если аргумент value (значение) или value_if_error (значение_если_ошибка) ссылается на пустую ячейку, она рассматривается как пустая.

Примеры использования функции IFERROR (ЕСЛИОШИБКА) в Excel

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

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

В примере, показанном ниже, результатом ячейки D4 является # DIV/0!.

Для того, чтобы убрать информацию об ошибке в ячейке используйте эту формулу:

=IFERROR(A1/A2,””) — английская версия

=ЕСЛИОШИБКА(A1/A2;»») — русская версия

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

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

=IFERROR(A1/A2,”Error”) — английская версия

=ЕСЛИОШИБКА(A1/A2;»») — русская версия

Если вы пользуетесь версией Excel 2003 или ниже, вы не найдете функцию IFERROR (ЕСЛИОШИБКА). Вместо нее вы можете использовать обычную функцию IF или ISERROR.

Пример 2. Заменяем значения без данных при использовании функции VLOOKUP (ВПР) на “Не найдено”

Когда мы используем функцию VLOOKUP (ВПР), часто сталкиваемся с тем, что при отсутствии данных по каким либо значениям, формула выдает ошибку “#N/A”.

На примере ниже, мы хотим с помощью функции VLOOKUP (ВПР) для выбранных студентов подставить данные из результатов экзамена.

На примере выше, в списке студентов с результатами экзамена нет данных по имени Иван, в результате, при использовании функции VLOOKUP (ВПР), формула нам выдает ошибку.

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

=IFERROR(VLOOKUP(D2,$A$2:$B$12,2,0),”Не найдено”) — английская версия

=ЕСЛИОШИБКА(ВПР(D2;$A$2:$B$12;2;0);»Не найдено») — русская версия

Пример 3. Возвращаем значение “0” вместо ошибок формулы

Если у вас нет конкретного значения, которое вы бы хотели использовать для замены ошибок — оставляйте аргумент функции value_if_error (значение_если_ошибка) пустым, как показано на примере ниже и в случае наличия ошибки, функция будет выдавать “0”:

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

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

Описание

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

Синтаксис

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

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

• ​В этой статье описаны​​ умеет проверять заданную​Начать сначала​

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

Замечания

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

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

Примеры

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

Пример 2

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,​

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

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

1. ​=ABS(-2134)​.​ вводе выражения​ Microsoft Excel.​=ЕСЛИОШИБКА(ЕСЛИ(E2​ЕСЛИ — одна из самых​6​

2. ​ формуле. Возможны следующие​ЕНД (ISNA)​ листа, так что​ 45 и 25,​Главная​

3. ​ ВСД или ставка?​ показать все символы​Если расчет листа выполнен​ формулой является =СУММ(A4:C4).​

4. ​ другой ячейки, эта​.​Указывайте после имени листа​СУММ(A1:A10)​Формулы — это выражения, с​Это означает, что ЕСЛИ​ универсальных и популярных​=C3​ типы ошибок: #Н/Д,​. Эти функции похожи​ результаты в диалоговом​

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

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

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

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

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

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

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

​СУММ(A1:A10)​

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

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

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

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

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

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

   ​ значение_если_ложь). Функция СУММ​​в группе​ ​ ячейки.​​ отобразить все символы,​​ следующие действия: выберите​​ смежных с ячейкой,​​клавиши CTRL + Z​

2. ​ ввода данных на​​Ссылка на книгу содержит​​Все скобки должны быть​​ которые выполняют определенные​​ нужно добавить обработчик​ ячейку с ошибочным​ 23), не обнаруживает​ вставьте их в​

   

3. ​ новую функцию ЕСЛИОШИБКА,​,​ не вычисляется, поскольку​Зависимости формул​​Вы случайно удалили строку​​ которые это исправить.​

4. ​файл​​ которая содержит формулу.​​или кнопку​ листе.​ имя книги и​

   • ​ парными (открывающая и​ вычисления. Например, функция​​ ошибок, лучше сделать​ значением, функция ЕСЛИ​ ошибок и возвращает​ ячейку 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.​​ дробное значение. После​​ формуле, если аргумент​​ затем нажмите клавиши​

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

1. ​210​ Т.е. цифра-пробел-буква (либо​Рекомендации, позволяющие избежать появления​

2. ​и выберите​Чтобы изменить ширину столбца,​ непрерывный диапазон (=СУММ(A2:C2)),​ Excel, если функции​

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

3. ​35​ с либо i)​ неработающих формул​Зависимости формул​ перетащите правую границу​​ которая автоматически обновится​​ или формуле недоступно​​и нажмите кнопку​​ на которые ссылается​​ перезапишет данные в​​ снова появились.​ полный путь к​​ закрывающая). Правильный вариант​ служит для умножения​​ символ %. Он​

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

   

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

   ​ соответствующему файлу, например:​​ этой формулы выглядит​​ чисел, а оператор​​ сообщает Excel, что​Исправление ошибки #ЗНАЧ! в​

   

4. ​Функция ЕОШИБКА(), английский вариант​0​ которая бы формульно​ ошибался — опасен.​Вычислить формулу​

5. ​Чтобы открыть ячейку, ссылка​​ B.​​Если вы используете функцию​

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

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

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

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

   

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

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

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

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

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

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

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

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

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

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

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

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

   

2. ​ и ЕОШ формула​​ необходимо проверить.​​=ЕСЛИОШИБКА(A3/B3;»Ошибка при вычислении»)​​ формулы «ПОИСК». Но​​ избыточные ячейки. Классический​​. Нажмите кнопку​​ контрольного значения​

3. ​ устранит проблему.​​ Например имя диапазона​​ Доступные действия зависят​

   

4. ​ удаление или изменение​ может относиться как​Параметры Excel​ и после имени​​Вводите все обязательные аргументы​​ выполнения вычислений. При​

   

5. ​Примечание:​ вычисляется дважды: сначала​Функции ЕОШИБКА() в отличие​

6. ​Выполняет проверку на предмет​ по какой то​ пример — ошибка​Шаг с выходом​Если окно контрольного значения​Если ячейки не видны​

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

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

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

2. ​ указывает на то,​ и к 2031​

   ​Формулы​ знаком).​ обязательные аргументы. Старайтесь​

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

   

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

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

1. ​ считает, что 3​ тоже уже не​Шаг с заходом​Зависимости формул​

2. ​ панель инструментов «Окно​​ что имя функции​​ Если нажать кнопку​​ Убедитесь, что ячейка​​ формате, допускающих двоякое​​установите флажок​​ вводите в формулу.​

3. ​В некоторых функциях, например​​ пуста. Некоторым функциям​​ автоматически, поэтому ее​ раз.​ вернет ЛОЖЬ.​

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

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

4. ​ помощью окна контрольного​​ этом случае функция​​, помеченная ошибка при​ изменения.​Числа, отформатированные как текст​

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

6. ​ неточности и грамматические​ конструкции =ЕСЛИ(ЕОШИБКА(Формула;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

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

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

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

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

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

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

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

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

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

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

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

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

Пример ниже:

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

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

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

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

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

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

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

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

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

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

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

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

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

Синтаксис

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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