Как убрать деление на ноль в excel при расчете формулы

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

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

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

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

10-2=8
8-2=6
6-2=4
4-2=2
2-2=0

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Читайте также: Как убрать ошибки в Excel

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

Скрытие значений и индикаторов ошибок в ячейках

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

Существует множество причин, по которым формулы могут возвращать ошибки. Например, деление на 0 не допускается, и если ввести формулу =1/0, Excel возвращает #DIV/0. Значения ошибок: #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF! и #VALUE!.

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

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

Создание примера ошибки

Откройте чистый лист или создайте новый.
Введите 3 в ячейку B1, в ячейку C1 — 0, а в ячейку A1 — формулу =B1/C1.
The #DIV/0! в ячейке A1.

Выделите ячейку A1 и нажмите клавишу F2, чтобы изменить формулу.
После знака равно (=) введите ЕСЛИERROR и открываю скобку.
ЕСЛИERROR(
Переместите курсор в конец формулы.
Введите ,0), то есть запятую и закрываюю скобки.
Формула =B1/C1 становится=ЕСЛИERROR(B1/C1;0).
Нажмите клавишу ВВОД, чтобы завершить редактирование формулы.
Теперь в ячейке вместо ошибки #ДЕЛ/0! должно отображаться значение 0.

Применение условного формата

Выделите ячейку с ошибкой и на вкладке Главная нажмите кнопку Условное форматирование.
Выберите команду Создать правило.
В диалоговом окне Создание правила форматирования выберите параметр Форматировать только ячейки, которые содержат.
Убедитесь, что в разделе Форматировать только ячейки, для которых выполняется следующее условие в первом списке выбран пункт Значение ячейки, а во втором — равно. Затем в текстовом поле справа введите значение 0.
Нажмите кнопку Формат.
На вкладке Число в списке Категория выберите пункт (все форматы).
В поле Тип введите ;;; (три точки с запятой) и нажмите кнопку ОК. Нажмите кнопку ОК еще раз.
Значение 0 в ячейке исчезнет. Это связано с тем, что пользовательский формат ;;; предписывает скрывать любые числа в ячейке. Однако фактическое значение (0) по-прежнему хранится в ячейке.

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

Выделите диапазон ячеек, содержащих значение ошибки.
На вкладке Главная в группе Стили щелкните стрелку рядом с командой Условное форматирование и выберите пункт Управление правилами.
Появится диалоговое окно Диспетчер правил условного форматирования.
Выберите команду Создать правило.
Откроется диалоговое окно Создание правила форматирования.
В списке Выберите тип правила выберите пункт Форматировать только ячейки, которые содержат.
В разделе Измените описание правила в списке Форматировать только ячейки, для которых выполняется следующее условие выберите пункт Ошибки.
Нажмите кнопку Формат и откройте вкладку Шрифт.
Щелкните стрелку, чтобы открыть список Цвет, а затем в списке Цвета темывыберите белый цвет.

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

Описание функций

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

НД Эта функция возвращает в ячейке строку "#Н/Д". Синтаксис =NA().

Выберите отчет сводной таблицы.
Появится область "Инструменты для работы со pivottable".
Excel 2016 и Excel 2013: на вкладке Анализ в группе Таблица щелкните стрелку рядом с кнопкой Параметры ивыберите параметры.

Excel 2010 и Excel 2007: на вкладке Параметры в группе Таблица щелкните стрелку рядом с кнопкой Параметры ивыберите параметры.
Перейдите на вкладку Разметка и формат, а затем выполните следующие действия.
- Изменение способа отображения ошибок. В поле Формат выберите значение ошибкиПоказывать. Введите в поле значение, которое нужно выводить вместо ошибок. Для отображения ошибок в виде пустых ячеек удалите из поля весь текст.
- Изменение способа отображения пустых ячеек Установите флажок Для пустых ячеек отображать. Введите в поле значение, которое нужно выводить в пустых ячейках. Чтобы они оставались пустыми, удалите из поля весь текст. Чтобы отображались нулевые значения, снимите этот флажок.

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

Ячейка с ошибкой в формуле

В Excel 2016, Excel 2013 и Excel 2010: Выберите Файл >Параметры >Формулы.

In Excel 2007: Click the Microsoft Office button > Excel Options >Formulas.
В разделе Поиск ошибок снимите флажок Включить фоновый поиск ошибок.

Как исправить #DIV/0! ошибка

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel для iPad Excel Web App Excel для iPhone Excel для планшетов с Android Excel 2010 Excel 2007 Excel для Mac 2011 Excel для телефонов Android Excel для Windows Phone 10 Excel Mobile Excel Starter 2010 Дополнительно. .. Меньше

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

Чтобы исправить ошибку, выполните одно из следующих действий:

Убедитесь, что делитель в функции или формуле не равен нулю или пустой ячейке.
Измените ссылку на ячейку в формуле на другую ячейку, которая не имеет нулевого (0) или пустого значения.
Введите #Н/Д в ячейку, на которую ссылается делитель в формуле, что изменит результат формулы на #Н/Д, чтобы указать, что значение делителя недоступно.

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

Оценить знаменатель для 0 или отсутствия значения

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

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

Используйте ЕСЛИОШИБКА, чтобы подавить #DIV/0! ошибка

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

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

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

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

Нужна дополнительная помощь?

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

См. также

ЕСЛИ функция

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

функции ИС

Обзор формул в Excel

Как избежать неработающих формул

Обнаружение ошибок в формулах

Функции Excel (по алфавиту)

Функции Excel (по категориям)

Как избавиться от #DIV/0! Ошибка в Экселе? Легкие формулы!

Если вы работали с формулами в Excel, я уверен, что вы столкнулись с #DIV/0! ошибка (Ошибка деления).

В этом коротком уроке я покажу вам, как быстро удалить ошибку деления в Excel (используя несколько простых формул).

Я также покажу вам, как найти все ячейки на листе или в книге, содержащие ошибки DIV.

Это руководство охватывает:

Что такое #DIV/0! Ошибка в Экселе?

В Excel вы получите ошибку DIV, если у вас есть формула, в которой есть деление, а делитель равен 0.

Например, если вы введете = 12/0 в ячейку в Excel, это даст вам ошибка деления (#DIV/0!)

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

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

Удалить #DIV/0! Ошибка при использовании IFERROR

Если вы работаете с формулами и хотите избавиться от #DIV/0! ошибка (которая часто возникает в результате формулы), вы можете использовать технику ЕСЛИОШИБКА.

Ниже приведен синтаксис формулы ЕСЛИОШИБКА:

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

здесь:

значение – это формула, которая может дать вам ошибку деления
значение_если_ошибка — это значение, которое вы должны получить, когда у вас есть какая-либо ошибка

Позвольте мне объяснить это на примере.

Предположим, у вас есть набор данных, как показано ниже, где у меня есть формула деления в столбце C.

Как видите, я получаю ошибки деления во всех случаях, когда делитель равен 0 или пуст в столбце B.

Чтобы удалить ошибка div, я могу использовать следующую формулу:

 =ЕСЛИОШИБКА(A2/B2,"")

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

Точно так же, если вы хотите заменить ошибку div чем-то более значимым, чем пробел, вы можете указать это как второй аргумент.

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

 =ЕСЛИОШИБКА(A2/B2,"Недоступно")

Несколько важных моментов, которые нужно знать о формуле ЕСЛИОШИБКА:

Формула

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

Удалить #DIV/0! Ошибка при использовании ЕСЛИОШИБКА и ЕСЛИ

Хотя использование функции ЕСЛИОШИБКА является предпочтительным способом обработки ошибки #DIV/0! ошибка в Excel, если вы используете старую версию Excel, в которой нет функции ЕСЛИОШИБКА (или вам нужна обратная совместимость, если вам нужно поделиться файлом с кем-то, кто все еще использует более старую версию Excel), вы можно использовать метод ЕСЛИ + ЕОШИБКА.

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

Опять же, предположим, что у меня есть приведенный ниже набор данных, и я хочу удалить #DIV/0! ошибка.

Для этого я могу использовать приведенную ниже формулу:

 =ЕСЛИ(ЕОШИБКА(A2/B2),"Недоступно",A2/B2)

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

Найти все ячейки с #DIV/0! Ошибка в Excel

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

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

Хотя этот метод не устраняет ошибку DIV, он может быть полезен для поиска и выделения этих ошибок, чтобы вы могли решить, как с ними справиться (например, удалить их или проверить данные и исправить их).

Ниже приведены шаги для поиска и удаления всех ошибок DIV на листе/книге:

Откройте файл, в котором вы хотите удалить все ошибки div
Удерживая клавишу Control, нажмите клавишу F (или Command + F при использовании Mac). Откроется диалоговое окно «Найти и заменить».

Нажмите кнопку «Параметры». Это покажет вам некоторые дополнительные параметры.

В поле «Найти что:» введите #DIV/0!

В раскрывающемся списке «В пределах» выберите «Лист» или «Рабочая книга» (выберите «Рабочая книга», если хотите найти ячейки с ошибками во всем файле)

В раскрывающемся списке «Поиск» выберите «Значения».

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

Удерживая клавишу Control, нажмите клавишу A (или Command + A, если вы используете Mac).
Learn more

Blender уроки

Категории