Xlfn iferror как функцию прописать в экселе 2003

Обновлено: 04.07.2024

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

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

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

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

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

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

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

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

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

Видеоурок

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

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

Синтаксис

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

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

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

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

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

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

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

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

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

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

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

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

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

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

= Мир MS Excel/Имена и макрос - Мир MS Excel

Войти через uID

[/vba]
вываливается ошибка.

Причем вручную эти имена удаляются без проблем.

[/vba]
вываливается ошибка.

Причем вручную эти имена удаляются без проблем. RAN

[/vba]
вываливается ошибка.

Причем вручную эти имена удаляются без проблем. Автор - RAN
Дата добавления - 03.11.2013 в 11:35 Точно имена? Это ведь так 2003 и ниже воспринимает функции ЕСЛИОШИБКА и СУММЕСЛИМН Точно имена? Это ведь так 2003 и ниже воспринимает функции ЕСЛИОШИБКА и СУММЕСЛИМН Serge_007 Точно имена? Это ведь так 2003 и ниже воспринимает функции ЕСЛИОШИБКА и СУММЕСЛИМН Автор - Serge_007
Дата добавления - 03.11.2013 в 12:06

PS Обижаешь, начальник!

PS Обижаешь, начальник! RAN

Быть или не быть, вот в чем загвоздка!

PS Обижаешь, начальник! Автор - RAN
Дата добавления - 03.11.2013 в 12:12

[/vba]
(в текущий момент под рукой - версия 2003 с конвертером) [/vba]
(в текущий момент под рукой - версия 2003 с конвертером) ikki [/vba]
(в текущий момент под рукой - версия 2003 с конвертером) Автор - ikki
Дата добавления - 03.11.2013 в 12:29 А у меня не хочет. (2007).
Visible срабатывает, а Delete - ошибка. А у меня не хочет. (2007).
Visible срабатывает, а Delete - ошибка. RAN

именно на Delete ошибка?
или на Debug.Print nm.Name после Delete?

пс. я знаю, что вопрос глупый, но вдруг.

именно на Delete ошибка?
или на Debug.Print nm.Name после Delete?

пс. я знаю, что вопрос глупый, но вдруг. ikki

пс. я знаю, что вопрос глупый, но вдруг. Автор - ikki
Дата добавления - 03.11.2013 в 12:47

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

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

Теперь в ячейки С3 (столбец Значение №2) поменяем число 1 на число 0.

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

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

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

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

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

Теперь, если в ячейки С3 поставить число 1, то в ячейки D3 мы получим результат деления 20/1 = 20.

ЕСЛИОШИБКА — примеры использования

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

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

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

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

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

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

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

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

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

Синтаксис

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Читайте также: