Еошибка excel пример

Microsoft Excel

трюки • приёмы • решения

Как избежать отображения ошибок в формулах таблицы Excel

Иногда формула возвращает такую ошибку, как #ССЫЛКА! или #ДЕЛ/0!. Как правило, вам нужно знать, что случилась ошибка в вычислениях формулы, но в некоторых ситуациях вы можете предпочесть, чтобы сообщения об ошибках не выводились. На рис. 85.1 показан пример.

Столбец D содержит формулы, которые вычисляют средний объем продаж. Например, ячейка D2 содержит следующую формулу: =В2/С2 .

Рис. 85.1. Формулы в столбце D выдают ошибки, если данные отсутствуют

Использование функции ЕОШИБКА

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

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

Рис. 85.2. Для скрытия ошибочных значений использована функция ЕСЛИ

Вы можете адаптировать этот метод к любой формуле. Исходная формула (без начального знака равенства) выступает аргументом для функции ЕОШИБКА и повторяется в качестве последнего аргумента функции ЕСЛИ: =ЕСЛИ(ЕОШИБКА(исходная_формула);»»;исходная_формула) .

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

Использование функции ЕСЛИОШИБКА

Если с вашей книгой будут работать только те пользователи, у которых установлен Excel 2007 или его более поздняя версия, то вы можете предпочесть функцию ЕСЛИОШИБКА. Она принимает два аргумента: первый является выражением, которое проверяется на ошибку, а второй — возвращаемым значением при условии, что формула примет ошибочное значение. Формула, представленная в предыдущем разделе, может быть переписана в следующем виде: =ЕСЛИ0ШИБКА(В2/С2;»») .

Использование этой функции имеет два преимущества:

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

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

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

Всем добрый день!

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

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

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

=ЕСЛИ(ЕОШИБКА(F5*G5);»»;F5*G5) Как видно в формулы, если в процессе вычисления значения «F5*G5» вы получаете ошибку, то вместо нее ставится просто пустое поле без каких-либо значений, если ошибки нет, выводится результат вычислений.

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

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

До встречи в новых статьях!

«Остерегайтесь незначительных расходов; маленькая течь потопит большой корабль.
»
Б. Франклин

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

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

Синтаксис

Аргументы функции ЕСЛИОШИБКА описаны ниже.

значение Обязательный аргумент. Аргумент, проверяемый на наличие ошибки.

value_if_error — обязательный аргумент. Возвращаемое значение, если формула возвращает ошибку. Оцениваются следующие типы ошибок: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME? и #NULL!.

Замечания

Если значение или value_if_error — пустая ячейка, ЕСЛИОШИБКА рассчитает ее как пустую строку («»).

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

Примеры

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — ВВОД.

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

Выполняет проверку на предмет ошибки в формуле в первом аргументе (деление 210 на 35), не обнаруживает ошибок и возвращает результат вычисления по формуле

=ЕСЛИОШИБКА(A3/B3;»Ошибка при вычислении»)

Выполняет проверку на предмет ошибки в формуле в первом аргументе (деление 55 на 0), обнаруживает ошибку «деление на 0» и возвращает «значение_при_ошибке»

Ошибка при вычислении

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

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

Пример 2

Ошибка при вычислении

Выполняет проверку на предмет ошибки в формуле в первом аргументе в первом элементе массива (A2/B2 или деление 210 на 35), не обнаруживает ошибок и возвращает результат вычисления по формуле

Выполняет проверку на предмет ошибки в формуле в первом аргументе во втором элементе массива (A3/B3 или деление 55 на 0), обнаруживает ошибку «деление на 0» и возвращает «значение_при_ошибке»

Ошибка при вычислении

Выполняет проверку на предмет ошибки в формуле в первом аргументе в третьем элементе массива (A4/B4 или деление «» на 23), не обнаруживает ошибок и возвращает результат вычисления по формуле

Примечание. Если у вас установлена текущая версия Office 365, вы можете ввести формулу в левую верхнюю ячейку диапазона вывода, а затем нажмите клавишу ВВОД, чтобы подтвердить формулу как формулу динамических массивов. В противном случае необходимо ввести формулу в качестве устаревшей формулы массива, сначала выделив диапазон вывода, введите формулу в верхнюю левую ячейку выходного диапазона, а затем нажмите клавиши CTRL + SHIFT + ВВОД, чтобы подтвердить его. Excel автоматически вставляет фигурные скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.

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

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

Примечание: Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Была ли информация полезной? Для удобства также приводим ссылку на оригинал (на английском языке).

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

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

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

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

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

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

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

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

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

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