Ребята, всем привет! 👋 Сейчас я пишу небольшую расчетную программку и по условиям расчета (так уж случается), что данные, на которые следует делить могут принимать нулевые или пустые значения (что эквивалентно нулю). Но, как мы знаем еще со школы, на ноль (0) делить нельзя... И Excel это тоже знает!.. А поэтому будет отображать ошибку # ДЕЛ/0!, что конечно визуально смотрится не очень 🧐...
Полагаю, что у Вас могут возникать такие ситуации, а потому делюсь примером из практики.
✨ А прежде, чем мы начнем 📣 напомню, теперь у нас на канале есть удобный рубрикатор 👉 Быстрый поиск решения. Путеводитель по Excel, а все видео 📽 предыдущих уроков доступны и на YouTube.
🔔 ПОЧЕМУ ТАК?
Ошибка # ДЕЛ/0! (# DIV / 0!) появляется, когда формула пытается разделить на ноль или значение, эквивалентное нулю (например, ячейка на листе пуста, поскольку данные еще недоступны).
Хотя ошибка # ДЕЛ/0! вызвана попыткой деления на ноль, она также может появляться в других формулах, которые ссылаются на ячейки, отображающие # ДЕЛ/0!
⚠️ Пустые ячейки являются частой причиной # ДЕЛ/0! ошибки.
Также ошибка # ДЕЛ/0! может возникать в случаях применения функций:
- AVERAGE (СРЗНАЧ)
- AVERAGEIF (СРЗНАЧЕСЛИ)
- AVERAGEIFS (СРЗНАЧЕСЛИМН)
Все три функции (AVERAGE (СРЗНАЧ), AVERAGEIF (СРЗНАЧЕСЛИ), AVERAGEIFS (СРЗНАЧЕСЛИМН)) могут возвращать ошибку # ДЕЛ/0!, когда количество "совпадающих" значений равно нулю.
Это связано с тем, что общая формула для вычисления средних значений равна = sum / A , а A иногда может быть равен нулю (0).
Т.к.количество числовых значений для усреднения равно нулю, то если мы попробуем усреднить диапазон ячеек, который содержит только текстовые значения,то функция СРЗНАЧ() вернет # ДЕЛ/0!:
Аналогичное произойдет, если мы используем функцию СРЗНАЧЕСЛИ() или СРЗНАЧЕСЛИМН() с логическими критериями, которые не соответствуют никаким данным, эти функции вернут # ДЕЛ/0! (потому что количество совпадающих записей равно нулю (0)).
⏭ Итак, пример:
Имеем некоторую таблицу с данными содержащую мероприятия по которым требуется проставлять критерии оценки. Всего в условиях примера определено 3 критерия:
- соответствует (1,0 балл)
- имеются нарушения (0,5 балла)
- не соответствует (0 баллов)
👩🎓 Задача: определить соответствие критерию
В данном примере, если какая-либо ячейка в диапазоне Таблица1[Балл за критерий оценки]/(Н4: Н14) содержит ошибку # ДЕЛ/0!, то приведенная формула =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;Таблица1[Балл за критерий оценки])/B15) также будет отображать # ДЕЛ/0!
Поскольку в диапазоне Таблица1[[соответствует]:[не соответствует]]/(E4:G14) мы имеем пустые ячейки, Excel оценивает их значение как ноль(0), и формула возвращает # ДЕЛ/0!
Даже если данные будут заполнены частично формула =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;Таблица1[Балл за критерий оценки])/B15 так же вернет ошибку # ДЕЛ/0!:
Как же решить данную проблему? Есть 3 (три) способа
✅ СПОСОБ №1. Проверяем ошибки
Лучший способ предотвратить ошибку # ДЕЛ/0! заключается в том, чтобы убедиться, что данные заполнены.
Если вы видите ошибка # ДЕЛ/0! проверьте следующее:
- все ли ячейки, используемые формулой, содержат достоверную информацию
- нет пустых ячеек, используемых для разделения других значений
- ячейки, на которые ссылается формула, еще не отображают # ДЕЛ/0!ошибка
Примечание: если вы попытаетесь разделить число на текстовое значение, вы увидите ошибку # ЗНАЧ!, а не # ДЕЛ/0!
...Но для решения данной задачи этот способ не применим... Т.к. оценка происходит поэтапно. Поэтому рассмотрим способ №2
✅ СПОСОБ №2. Используем функцию ЕСЛИ()
Простой способ перехватить ошибку # ДЕЛ/0! заключается в проверке требуемых значений с помощью функции ЕСЛИ():
Например,
=ЕСЛИ([@[*]]=0;0;[@[балл за критерия]]*СУММПРОИЗВ(Таблица1[@[соответствует]:[не соответствует]];$E$2:$G$2)/СУММ(Таблица1[@[соответствует]:[не соответствует]]))
Но как вы видите здесь пришлось использовать дополнительный столбец (можно было бы и без него, но для наглядности примера, я не захотела загромождать формулу). А поэтому рассмотрим способ №3
✅ СПОСОБ №3 Используем функцию ЕСЛИОШИБКА()
Еще один вариант исключить ошибку # ДЕЛ/0! - это функция ЕСЛИОШИБКА()
Данная функция перехватит любую ошибку и вернет альтернативный результат.
Чтобы исключить ошибку # ДЕЛ/0! в условиях нашего примера обернем функцию ЕСЛИОШИБКА() вокруг формулы следующим образом:
=ЕСЛИОШИБКА([@[балл за критерия]]*СУММПРОИЗВ(Таблица1[@[соответствует]:[не соответствует]];$E$2:$G$2)/СУММ(Таблица1[@[соответствует]:[не соответствует]]);0)
И еще вариант...
Если вы хотите отобразить сообщение при перехвате ошибки # ДЕЛ/0!, просто заключите сообщение в кавычки.
Например, чтобы отобразить сообщение "Укажите соответствие", мы можем использовать формулу:
=ЕСЛИОШИБКА([@[балл за критерия]]*СУММПРОИЗВ(Таблица1[@[соответствует]:[не соответствует]];$E$2:$G$2)/СУММ(Таблица1[@[соответствует]:[не соответствует]]);"Укажите соответствие")
А на этом сегодня все. Спасибо, что вы с нами 💕. Применяйте данные способы на практике и это позволит упростить работу и экономить время 👏
📚 Подробнее о других ошибках возникающих при работе Excel и действиях по их исправлению смотри в обзоре: Виды ошибок, описание и меры устранения.
Продолжение следует…
Подписывайтесь на канал, чтобы не пропустить новые уроки и полезные фишки Excel. Следите за нашими новостями и вы узнаете больше о VBA и Excel в частности.
За лайк 👍 и репост 🔁 данного поста благодарочка 💖 и респект 🤝 каждому!
#excel #встроенные функции excel #функции excel #ошибки excel #еслиошибка excel #как исправить ошибки в эксель #ошибка div #уроки эксель #обучение эксель #как сделать в эксель