Как убрать ошибку деления на ноль в excel

Как убрать ошибку деления на ноль в Excel с помощью формулы

В математике деление на ноль – невозможно! Одним из способов для объяснения данного правила является анализ процесса, который показывает, что происходит, когда одно число разделено на другое.

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

В реальности операция деление это по сути тоже что и вычитание. Например, деление числа 10 на 2 является многократным вычитанием 2 от 10-ти. Многократность повторяется до той поры пока результат не будет равен 0. Таким образом необходимо число 2 вычитать от десяти ровно 5 раз:

  1. 10-2=8
  2. 8-2=6
  3. 6-2=4
  4. 4-2=2
  5. 2-2=0

Если же попробовать разделить число 10 на 0, никогда мы не получим результат равен 0, так как при вычитании 10-0 всегда будет 10. Бесконечное количество раз вычитаний ноля от десяти не приведет нас к результату =0. Всегда будет один и ото же результат после операции вычитания =10:

  • 10-0=10
  • 10-0=10
  • 10-0=10
  • ∞ бесконечность.

В кулуарах математиков говорят, что результат деления любого числа на ноль является «не ограниченным». Любая компьютерная программа, при попытке деления на 0, просто возвращает ошибку. В Excel данная ошибка отображается значением в ячейке #ДЕЛ/0!.

Ошибка ДЕЛ 0.

Но при необходимости можно обойти возникновения ошибки деления на 0 в Excel. Просто следует пропустить операцию деления если в знаменателе находится число 0. Решение реализовывается с помощью помещения операндов в аргументы функции =ЕСЛИ():

Формула деления на ноль.

Таким образом формула Excel позволяет нам «делить» число на 0 без ошибок. При делении любого числа на 0 формула будет возвращать значение 0. То есть получим такой результат после деления: 10/0=0.

Как работает формула для устранения ошибки деления на ноль

Для работы корректной функция ЕСЛИ требует заполнить 3 ее аргумента:

  1. Логическое условие.
  2. Действия или значения, которые будут выполнены если в результате логическое условие возвращает значение ИСТИНА.
  3. Действия или значения, которые будут выполнены, когда логическое условие возвращает значение ЛОЖЬ.

В данном случаи аргумент с условием содержит проверку значений. Являются ли равным 0 значения ячеек в столбце «Продажи». Первый аргумент функции ЕСЛИ всегда должен иметь операторы сравнения между двумя значениями, чтобы получить результат условия в качестве значений ИСТИНА или ЛОЖЬ. В большинстве случаев используется в качестве оператора сравнения знак равенства, но могут быть использованы и другие например, больше> или меньше >. Или их комбинации – больше или равно >=, не равно !=.

Если условие в первом аргументе возвращает значение ИСТИНА, тогда формула заполнит ячейку значением со второго аргумента функции ЕСЛИ. В данном примере второй аргумент содержит число 0 в качестве значения. Значит ячейка в столбце «Выполнение» просто будет заполнена числом 0 если в ячейке напротив из столбца «Продажи» будет 0 продаж.

Если условие в первом аргументе возвращает значение ЛОЖЬ, тогда используется значение из третьего аргумента функции ЕСЛИ. В данном случаи — это значение формируется после действия деления показателя из столбца «Продажи» на показатель из столбца «План».

Таким образом данную формулу следует читать так: «Если значение в ячейке B2 равно 0, тогда формула возвращает значение 0. В противные случаи формула должна возвратить результат после операции деления значений в ячейках B2/C2».

Формула для деления на ноль или ноль на число

Усложним нашу формулу функцией =ИЛИ(). Добавим еще одного торгового агента с нулевым показателем в продажах. Теперь формулу следует изменить на:

Скопируйте эту формулу во все ячейки столбца «Выполнение»:

Формула деления на ноль и ноль на число.

Теперь независимо где будет ноль в знаменателе или в числителе формула будет работать так как нужно пользователю.

Данная функция позволяет нам расширить возможности первого аргумента с условием во функции ЕСЛИ. Таким образом в ячейке с формулой D5 первый аргумент функции ЕСЛИ теперь следует читать так: «Если значения в ячейках B5 или C5 равно ноль, тогда условие возвращает логическое значение ИСТИНА». Ну а дальше как прочитать остальную часть формулы описано выше.

Исправление ошибки #DIV/0! #BUSY!

Ошибка #ДЕЛ/0! возникает в Microsoft Excel, когда число делится на ноль (0). Это происходит, если вы вводите простую формулу, например =5/0, или если формула ссылается на ячейку с 0 или пустую ячейку, как показано на этом рисунке.

Примеры формул, которые вызывают #DIV/0! исчезнут.

Есть несколько способов исправления этой ошибки.

Убедитесь в том, что делитель в функции или формуле не равен нулю и соответствующая ячейка не пуста.

Измените ссылку в формуле, чтобы она указывала на ячейку, не содержащую ноль (0) или пустое значение.

Введите в ячейку, которая используется в формуле в качестве делителя, значение #Н/Д, чтобы изменить результат формулы на #Н/Д, указывая на недоступность делителя.

Во многих случаях это #DIV/0! их можно избежать, поскольку формулы ожидают ввода от вас или другого пользователя. В этом случае сообщение об ошибке отображаться не нужно, поэтому для ее подавления можно использовать несколько способов обработки ошибок.

Оценка знаменателя на наличие нуля или пустого значения

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

Например, если ошибка возникает в формуле =A2/A3, можно заменить ее формулой =ЕСЛИ(A3;A2/A3;0), чтобы возвращать 0, или формулой =ЕСЛИ(A3;A2/A3;""), чтобы возвращать пустую строку. Также можно выводить произвольное сообщение. Пример: =ЕСЛИ(A3;A2/A3;"Ожидается значение."). Он сообщает Excel ЕСЛИ(существует, то вернуть результат формулы, в противном случае игнорировать ее).

Примеры устранения #DIV/0! исчезнут.

Чтобы подавления этой #DIV/0, используйте #DIV. #BUSY!

Вы также можете скрыть эту ошибку, вложенную операцию деления в функцию ЕСЛИERROR. При использовании A2/A3 можно также использовать =ЕСЛИERROR(A2/A3;0). Он сообщает Excel, если формула возвращает ошибку, возвращается 0, в противном случае возвращается результат формулы.

В версиях до Excel 2007 можно использовать синтаксис ЕСЛИ(ЕОШИБКА()): =ЕСЛИ(ЕОШИБКА(A2/A3);0;A2/A3) (см. статью Функции Е).

Примечание. Как методы ЕСЛИERROR, так и ЕСЛИ(ЕERROR()) — это подавляющие обработчики всех ошибок, а не только #DIV/0!. Прежде чем применять обработку ошибок, убедитесь, что формула работает правильно. В противном случае вы можете не понимать, что формула работает неправильно.

Совет: Если в Microsoft Excel включена проверка ошибок, нажмите кнопку рядом с ячейкой, в которой показана ошибка. Выберите пункт Показать этапы вычисления, если он отобразится, а затем выберите подходящее решение.

У вас есть вопрос об определенной функции?

Помогите нам улучшить Excel

У вас есть предложения по улучшению следующей версии Excel? Если да, ознакомьтесь с темами на портале пользовательских предложений для Excel.

Ссылка на основную публикацию