Как решить ошибки #Div/0 в Excel (с примерами)

Математически число не может быть разделено на ноль. То же самое происходит и в Excel. Excel не может вычислить значение, которое делится на ноль, и обозначает его как #Div/0! Помимо нуля, деление на пустую или пустую ячейку также отображается как #Div/0!

#Раздел/0! Ошибка в Экселе

Проще говоря, когда Excel находит значение, разделенное на ноль, пустую/пустую ячейку или значение, равное нулю, отображается ошибка #Div/0! Ошибка. Хотя, мы обсудим #div/0! ошибка в отношении Excel, эта статья также действительна для других программ электронных таблиц, таких как Google Sheets, Open Office и т. д.

Ошибка из-за деления на ноль

Введите следующую формулу в ячейку B2:

=12/0#Раздел/0! Ошибка из-за деления на ноль

Это немедленно вызовет #Div/0! ошибка, так как знаменатель в формуле равен нулю.

Посмотрите на изображение ниже, и вы заметите ошибку деления на ноль в ячейке E9. Здесь формула такова:

=С9/D9

Но D9 равен 0, поэтому ошибка.

#Раздел/0! Ошибка из-за деления на ячейку, содержащую ноль

Ошибка после деления на пустую ячейку

Введите 10 в ячейку B2 и введите следующую формулу в ячейку D3:

=В2/С2

Это вызовет #Div/0! ошибка, так как ячейка C2 пуста, что рассматривается как ноль в вычислениях Excel.

Ошибка деления на ноль из-за пустой ячейки C2

Ошибка в формуле среднего из-за нечислового значения

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

=СРЕДНЕЕ(C3:C14)

Но это вызовет #Div/0! ошибка в ячейке C15.

#Раздел/0! Ошибка в среднем нечисловых значений

Среднее значение

= сумма/количество

И поскольку сумма и количество числовых значений (поскольку значения не являются числовыми) в заданном диапазоне равны нулю, это будет ситуация 0/0, следовательно, div/0! ошибка.

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

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

#Раздел/0! Ошибка в формуле усреднения

Есть две колонки, одна – цвет, другая – количество. Мы хотим рассчитать среднее значение цвета на основе значений в ячейках C3:C9. Введите формулу в ячейку F3:

=СРЗНАЧЕСЛИ(C5:C11,B5:B11,E3)

И скопируйте его в ячейки F4 и F5. Вы сразу заметите #div/0! ошибка в ячейке F5. Но почему?

Для ячейки F3 количество красных равно 2 (B3 и B5), а сумма равна 12+8=20 (C3 и C5). Среднее значение будет 20/2=10.

Для ячейки F5 количество черных равно 0 (поскольку черного цвета нет в эталонном диапазоне), а сумма также равна нулю, следовательно, 0/0 вызывает #div/0!

Ошибка в другой формуле, где подразделение не участвует

Введите следующую формулу в ячейку E15:

=СУММ(Е3:Е14)Разделить на нулевую ошибку в функции суммы

Но почему он покажет #div/0! ошибка? Это простая функция суммы, в которой нет деления. #div/0! ошибка в ячейке E3 вызывает ошибку в ячейке E15.

MOD также является формулой Excel, которая может вызвать прямую ошибку #div/0! ошибка.

Причины #Div/0! Ошибки

Итак, Excel покажет ошибку деления на ноль:

1. Если есть деление на ноль, задействована пустая или пустая ячейка.
2. Если информация в диапазоне формул недействительна, т. е. попытка получить среднее значение диапазона нечисловых ячеек.
3. Если диапазон формул уже содержит #div/0! ошибка.

Методы обработки #Div/0! Ошибки

Как #Div/0! имеет свои корни в математике, и ее нельзя избежать, сначала убедитесь, что ошибка не вызвана ошибкой или человеческим фактором. Для этого:

1. Убедитесь, что в процессе нет деления на ноль, пустой или пустой ячейки.
2. Проверьте, верна ли информация в диапазоне формул. Например, вы не пытаетесь вычислить среднее значение нечисловых ячеек.
3. Убедитесь, что ни одна ячейка уже не показывает #div/0! ошибка в формуле диапазона.
4. Используйте формулы Excel, чтобы замаскировать, перехватить или исправить #div/0! ошибки (рассмотрим позже).

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

Используйте отчеты об ошибках, чтобы найти проблемы, вызывающие ошибку #div/0! ошибка

Как найти #DIV/0! Ячейки ошибок

Можно вручную пропустить ошибку деления на ноль при просмотре большого набора данных и использовании возможностей Excel для поиска всех ячеек с #div/0! ошибки будут большим подспорьем. Это также может помочь нам решить, как управлять этими ошибками.

1. Нажмите клавиши Control + F, чтобы открыть окно «Найти и заменить».
2. Разверните «Параметры» и в поле «Найти» введите: #DIV/0!
3. Выберите «Лист» или «Книга» (в раскрывающемся списке «Внутри») и установите для параметра «Поиск в раскрывающемся списке» значение «Значения».
4. Нажмите «Найти все» и все ячейки с #Div/0! будут показаны ошибки. Вы можете просмотреть каждую ячейку и проверить, является ли ошибка результатом ошибки или можно сделать что-то еще (например, удалить ячейку, если она не требуется).

   Найти все #Div/0! Ошибки в книге Excel

Используйте формулы Excel для преодоления ошибок, разделенных на ноль

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

Используйте формулу ЕСЛИОШИБКА для ловушки #Div/0! Ошибки

ЕСЛИОШИБКА — это формула Excel, которая используется для маскировки всех ошибок на листе Excel. Он также работает для захвата или маскировки #Div/0! ошибки. Синтаксис формулы ЕСЛИОШИБКА следующий:

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

Здесь значение — это формула, которая показывает ошибку Excel (здесь #div/0!), а значение_если_ошибка — это значение, которое вы указываете для отображения вместо ошибки.

Поясним это на простом примере. Введите 10 в ячейку B2 и введите следующую формулу в ячейку D2:

=В2/С2

Это приведет к #div/0! ошибка в ячейке D2. Теперь введите следующую формулу в ячейку D2:

=ЕСЛИОШИБКА(B2/C2,””)Используйте ЕСЛИОШИБКА, чтобы скрыть ошибку деления на ноль

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

Добавьте пользовательское сообщение в формулу ЕСЛИОШИБКА

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

1. Введите следующую формулу в ячейку D2:=ЕСЛИОШИБКА(B2/C2,”C2 пуст, введите значение”)

   Добавление пользовательского сообщения в формулу ЕСЛИОШИБКА

2. Вы заметите, что теперь, когда ячейка D2 ясно говорит о том, что C2 пуста, введите значение.

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

Ограничения формулы ЕСЛИОШИБКА

Вот некоторые ограничения формулы ЕСЛИОШИБКА:

1. Формула ЕСЛИОШИБКА совместима с Excel 2007 и выше. Для версий ниже (Excel 2003 или ниже) эта формула не будет работать. Для этого вы можете использовать IF и ISERROR, которые обсуждаются позже.
2. ЕСЛИОШИБКА маскирует все значения ошибок, включая #DIV/0!, N/A, #VALUE!, #REF!, #NUM, #NAME и т. д., а не только #div/0! ошибки. Если ваша формула возвращает ошибку, отличную от #div/0! это будет рассматриваться таким же образом. Это может привести к неправильным расчетам и решениям.

Используйте формулу ЕСЛИ для решения #Div/0!

#Раздел/0! могут быть решены (а не просто замаскированы или захвачены) с использованием логики формулы ЕСЛИ, но это будет отличаться от случая к случаю.

Общий синтаксис формулы ЕСЛИ:

=ЕСЛИ(Логическая_проверка, [value_if_true], [value_if_false])

Логический тест — это условие, которое вы хотите проверить. Если это правда, возвращается первое значение, в противном случае возвращается второе значение. Чтобы лучше понять, давайте продолжим наш простой пример:

1. Введите следующую формулу в ячейку D2:=ЕСЛИ(C2,B2/C2,””)

   Используйте If, чтобы показать пустую ячейку вместо #Div/0! Ошибка

2. Вы увидите пустую ячейку в ячейке D2. Здесь формула гласит, что если в ячейке C2 есть ненулевое значение, продолжить деление B2 на C2, в противном случае вернуть пустое значение.
3. Давайте изменим формулу, чтобы она отображала пользовательское сообщение:=IF(C2,B2/C2,”Входное значение в C2″)

   Показывать пользовательское сообщение вместо #div/0! Ошибка при использовании IF

4. Вы увидите «Введите значение в C2» в ячейке D2. Здесь формула гласит, что если в ячейке C2 есть ненулевое значение, продолжить деление B2 на C2, в противном случае показать текст «Введите значение в C2».
5. Введите следующую формулу в ячейку D2:=IF(C2<>0,B2/C2,”Введите ненулевое значение в C2″)

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

6. Теперь всякий раз, когда значение C2 не будет равно нулю (обозначается <>), формула будет вычислять, в противном случае будет отображаться «Введите ненулевое значение в C2».
7. Вы можете объединить несколько критериев в одну формулу ЕСЛИ с помощью оператора ИЛИ. Например, если мы не хотим вычислять формулу, если B2 или C2 пусты, мы можем использовать следующее:=ЕСЛИ(ИЛИ(B=2″”,C2=””),””,B2/C2)

   Используйте ЕСЛИ и ИЛИ, чтобы использовать несколько критериев для исправления #div/0! ошибки

Та же логика может быть скопирована и в других, более сложных сценариях. Нет предела логике, которую можно использовать с IF и OR для преодоления любого #div/0! ошибка, но эта логика зависит от вашего конкретного случая использования.

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

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

ЕОШИБКА — это логическая функция, которая проверяет наличие ошибки. Если это так, он покажет True, а если нет, то False. Давайте продолжим наш простой пример:

1. Введите следующую формулу в ячейку D2:=ISERROR(B2/C2)

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

2. Вы увидите True, указывающий на наличие ошибки. Снова введите следующую формулу в ячейку D2:=ЕСЛИ(ЕОШИБКА(B2/C2),”Недоступно”,B2/C2)

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

3. Это означает, что если есть ошибка, покажите Not Available (вы можете использовать любую другую строку, если хотите), в противном случае рассчитайте B2/C2.

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

Удалить #DIV/0! Ошибка в сводной таблице

#Раздел/0! ошибки в сводной таблице могут обрабатываться по-разному. Более того, сводная таблица не будет отображать нужный текст вместо #DIV/0! ошибка, если текст является именем строки или столбца. Посмотрите на изображение ниже, и вы заметите #div/0! ошибки в ячейках D6, D7, D9 и F5.

Разделить на ноль Ошибка в сводной таблице

Чтобы изменить это:

1. Щелкните в любом месте сводной таблицы и перейдите на вкладку «Анализ сводной таблицы» на ленте.
2. Разверните параметр сводной таблицы и выберите Параметры.

   Откройте параметры сводной таблицы на вкладке «Анализ сводной таблицы».

3. Выберите «Макет и формат» и в разделе «Формат» включите параметр «Показывать значения ошибок».
4. В текстовом поле введите Недоступно (или любой другой текст, который вы хотите использовать) и #Div/0! ошибки будут заменены на Недоступно. Следует помнить, чтоУстановите для отображаемых значений ошибок значение «Недоступно» в параметрах макета и формата сводной таблицы.

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

Надеюсь, эти строки прояснили ваше представление о #div/0! ошибки, и если у вас есть какие-либо вопросы, вы более чем приветствуетесь в разделе комментариев.