logo

Блог Александра Томма

О том, как заставить Microsoft Office работать на Вас

Обходим ошибки с помощью функций Excel

Дата: 28 февраля 2017 Категория: Excel
Поделиться, добавить в закладки или распечатать статью

Здравствуйте, друзья. В предыдущей статье я рассказывал, как найти ошибку в расчетах и исправить ее. А вот что делать, если появление ошибки допускается? Например, функция поиска ВПР возвращает «#Н/Д», если в таблицу еще не внесено нужное значение. Или у Вас возникает деление на ноль, которое не является синтаксической ошибкой, просто именно сейчас мы имеем такие исходные данные. Как же сделать так, чтобы «ошибка» в одной формуле не тянула за собой целый ряд ошибок в зависимых формулах? Очень просто, с помощью функций Эксель!

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

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

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

Ошибка Код
#ПУСТО! 1
#ДЕЛ/0! 2
#ЗНАЧ! 3
#ССЫЛКА! 4
#ИМЯ? 5
#ЧИСЛО! 6
#Н/Д 7
#ОЖИДАНИЕ_ДАННЫХ 8
Другая ошибка, или ошибки нет #Н/Д

Например, запишем такую формулу: =ЕСЛИ(ТИП.ОШИБКИ(А1)=4;«Внешняя ссылка нарушена»;А1). Если в ячейке А1 будет ошибка «#ССЫЛКА!», формула выведет надпись: ;«Внешняя ссылка нарушена». Иначе – выведет значение ячейки А1.

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

Если же не справились – задавайте свои вопросы в комментариях к этому посту!

Следующая статья будет посвящена сводным таблицам. Вот, где Вы научитесь экономить свое время!

Жду Вас на страницах своего блога и с радостью отвечу на Ваши вопросы.

Поделиться, добавить в закладки или распечатать статью

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *