Ребята, всем привет! Сейчас я пишу небольшую расчетную программку и по условиям расчета (так уж случается) , что данные, на которые следует делить могут принимать нулевые или пустые значения (что эквивалентно нулю) . Но, как мы знаем еще со школы, на ноль (0) делить нельзя. .. И Excel это тоже знает. А поэтому будет отображать ошибку # ДЕЛ/0!, что конечно визуально смотрится не очень .
Полагаю, что у Вас могут возникать такие ситуации, а потому делюсь примером из практики .
✨ А прежде, чем мы начнем напомню, теперь у нас на канале есть удобный рубрикатор Быстрый поиск решения. Путеводитель по Excel , а все видео предыдущих уроков доступны и на YouTube.
Как исправить ошибку #ДЕЛ/0!
ПОЧЕМУ ТАК?
Ошибка # ДЕЛ/0! (# DIV / 0!) появляется, когда формула пытается разделить на ноль или значение, эквивалентное нулю (например, ячейка на листе пуста, поскольку данные еще недоступны).
Хотя ошибка # ДЕЛ/0! вызвана попыткой деления на ноль, она также может появляться в других формулах, которые ссылаются на ячейки, отображающие # ДЕЛ/0!
Как в экселе убрать ошибку деление на 0
⚠️ Пустые ячейки являются частой причиной # ДЕЛ/0! ошибки.
Также ошибка # ДЕЛ/0! может возникать в случаях применения функций:
- AVERAGE (СРЗНАЧ)
- AVERAGEIF (СРЗНАЧЕСЛИ)
- AVERAGEIFS (СРЗНАЧЕСЛИМН)
Все три функции (AVERAGE (СРЗНАЧ), AVERAGEIF (СРЗНАЧЕСЛИ) , AVERAGEIFS (СРЗНАЧЕСЛИМН)) могут возвращать ошибку # ДЕЛ/0! , когда количество «совпадающих» значений равно нулю.
Это связано с тем, что общая формула для вычисления средних значений равна = sum / A , а A иногда может быть равен нулю (0).
Т.к.количество числовых значений для усреднения равно нулю , то если мы попробуем усреднить диапазон ячеек, который содержит только текстовые значения,то функция СРЗНАЧ() вернет # ДЕЛ/0! :
Функция AVERAGE (СРЗНАЧ) может возвращать ошибку # ДЕЛ/0!
Аналогичное произойдет, если мы используем функцию СРЗНАЧЕСЛИ() или СРЗНАЧЕСЛИМН() с логическими критериями, которые не соответствуют никаким данным, эти функции вернут # ДЕЛ/0! ( потому что количество совпадающих записей равно нулю (0)).
⏭ Итак, пример:
Имеем некоторую таблицу с данными содержащую мероприятия по которым требуется проставлять критерии оценки. Всего в условиях примера определено 3 критерия :
- соответствует (1,0 балл)
- имеются нарушения (0,5 балла)
- не соответствует (0 баллов)
Задача: определить соответствие критерию
В данном примере , если какая-либо ячейка в диапазоне Таблица1[Балл за критерий оценки] /( Н4: Н14) содержит ошибку # ДЕЛ/0! , то приведенная формула =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;Таблица1[Балл за критерий оценки])/B15) также будет отображать # ДЕЛ/0!
Поскольку в диапазоне Таблица1[[соответствует]:[не соответствует]]/ (E4:G14) мы имеем пустые ячейки, Excel оценивает их значение как ноль(0), и формула возвращает # ДЕЛ/0!
Ошибка в Excel «#ДЕЛ/0».Функция ЕСЛИОШИБКА
Даже если данные будут заполнены частично формула =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;Таблица1[Балл за критерий оценки])/B15 так же вернет ошибку # ДЕЛ/0!:
Как же решить данную проблему? Есть 3 (три) способа
✅ СПОСОБ №1. Проверяем ошибки
Лучший способ предотвратить ошибку # ДЕЛ/0! заключается в том, чтобы убедиться, что данные заполнены.
Если вы видите ошибка # ДЕЛ/0! проверьте следующее:
- все ли ячейки, используемые формулой, содержат достоверную информацию
- нет пустых ячеек, используемых для разделения других значений
- ячейки, на которые ссылается формула, еще не отображают # ДЕЛ/0! ошибка
Примечание: если вы попытаетесь разделить число на текстовое значение, вы увидите ошибку # ЗНАЧ!, а не # ДЕЛ/0!
. Но для решения данной задачи этот способ не применим. Т.к. оценка происходит поэтапно. Поэтому рассмотрим способ №2
✅ СПОСОБ №2. Используем функцию ЕСЛИ()
Простой способ перехватить ошибку # ДЕЛ/0! заключается в проверке требуемых значений с помощью функции ЕСЛИ() :
Способ перехватить ошибку # ДЕЛ/0! с помощью функции ЕСЛИ()
Но как вы видите здесь пришлось использовать дополнительный столбец (можно было бы и без него, но для наглядности примера, я не захотела загромождать формулу). А поэтому рассмотрим способ №3
✅ СПОСОБ №3 Используем функцию ЕСЛИОШИБКА()
Еще один вариант исключить ошибку # ДЕЛ/0! — это функция ЕСЛИОШИБКА()
Данная функция перехватит любую ошибку и вернет альтернативный результат.
Чтобы исключить ошибку # ДЕЛ/0! в условиях нашего примера обернем функцию ЕСЛИОШИБКА() вокруг формулы следующим образом:
Источник: dzen.ru
Как исправить ошибку #ДЕЛ/0! в Excel
При работе в Excel вы можете столкнуться с ошибкой #ДЕЛ/0, которая значит что вы пытаетесь поделить число на 0, а как вы знаете, делить на 0 нельзя. Исправить эту ошибку в Экселе можно несколькими способами.
Способ 1. При помощи функции ЕСЛИ
При помощи функции ЕСЛИ() мы сможем обработать ситуацию, при которой числитель равен 0. Синтаксис функции следующий ЕСЛИ(условие; результат при верном значении; результат при неверном значении). В примере выше мы должны проверить, является ли значение в ячейке С4 равно 0, если равно 0, то присваиваем значение «-«, а если нет — то вычисляем значение от деления. Итого формула выглядит так: =ЕСЛИ(C4=0;»-«;C3/C4)
Способ 2. При помощи функции ЕСЛИОШИБКА
Функция ЕСЛИОШИБКА возвращает заданное значение, если в ячейке ошибка, иначе вычисляет заданную формулу. Синтаксис функции следующий ЕСЛИОШИБКА(Формула; Значение если ошибка). В нашем случае будет так: =ЕСЛИОШИБКА(C3/C4;»-«)
Спасибо, что прочитали статью. Теперь вы знаете, как исправить ошибку #ДЕЛ/0 в Excel. Есть вопросы — добро пожаловать в комментарии 😉
Источник: www.excelguide.ru
Exceltip
Блог о программе Microsoft Excel: приемы, хитрости, секреты, трюки
Ошибки в Excel — поиск и обработка ошибок в Excel
Опубликовано 02.07.2013 Автор Ренат Лотфуллин
Внесение даже небольшого изменения на рабочем листе Excel может привести к образованию ошибок в других ячейках. К примеру, вы можете случайно ввести значение в ячейку, которая раньше содержала формулу. Эта простая ошибка может оказать значительное влияние на другие формулы, и вы не сможете обнаружить ее, пока не сделаете какие-нибудь изменения на листе.
Ошибки в формулах делятся на несколько категорий:
Синтаксические ошибки: Возникают при неправильном синтаксисе формулы. Например, формула имеет несоответствующие скобки, или функция имеет не корректное количество аргументов.
Логические ошибки: В этом случает формула не возвращает ошибку, но имеет логический изъян, что является причиной неправильного результата расчета.
Ошибки неправильных ссылок: Логика формул верна, но формула использует некорректную ссылку на ячейку. Простой пример, диапазон данных для суммирования в формуле СУММ может содержать не все элементы, которые вы хотите суммировать.
Семантические ошибки: Например, название функции написано неправильно, в этом случае Excel вернет ошибку #ИМЯ?
Циклические ошибки: Циклические ссылки возникают, когда формула ссылается на саму себя, прямо или косвенно. Циклические ссылки иногда могут быть полезны, но зачастую они указываю на наличие проблемы.
Ошибки в формулах массивов: Когда вы вводите формулу массива, по окончании ввода необходимо нажать Ctrl + Sift + Enter. Если вы не сделали этого, Excel не поймет, что это формула массива, и вернет ошибку или некорректный результат.
Ошибки неполных расчётов: В этом случае формулы рассчитываются не полностью. Чтобы удостовериться, что се формулы пересчитаны, наберите Ctrl + Alt + Shift + F9.
Проще всего найти и скорректировать синтаксические ошибки. Чаще всего, вы знаете, когда формула содержит синтаксическую ошибку. К примеру, Excel не даст ввести формулу с несогласованными скобками. Иные ситуации синтаксических ошибок приводят к выводу следующих ошибок в ячейке листа.
Ошибка #ДЕЛ/0!
Если вы создали формулу, в которой производится деление на ноль, Excel вернет ошибку #ДЕЛ/0!
Так как Excel воспринимает пустую ячейку как ноль, то при делении на пустую ячейку тоже будет возвращена ошибка. Эта проблема часто встречается при создании формулы для данных, которые еще не были введены. Формула ячейки D4 была протянута на весь диапазон (=C4/B4).
Эта формула возвращает отношение значений колонок C к B. Так как не все данные по дням были занесены, формула вернула ошибку #ДЕЛ/0!
Чтобы избежать ошибки, вы можете воспользоваться формулой ЕСЛИ, для проверки, являются ли ячейки колонки B пустыми или нет:
Эта формула вернет пустое значение, если ячейка B4 будет пустой или содержать 0, в противном случае вы увидите посчитанное значение.
Другим подходом является использование функции ЕСЛИОШИБКА, которая проверяет на наличие ошибки. Следующая формула вернет пустую строку, если выражение C4/B4 будет возвращать ошибку:
Ошибка #Н/Д
Ошибка #Н/Д возникает в случаях, когда ячейка, на которую ссылается формула, содержит #Н/Д.
Обычно, ошибка #Н/Д возвращается в результате работы формул подстановки (ВПР, ГПР, ПОИСКПОЗ и ИНДЕКС). В случае, когда совпадение не было найдено.
Чтобы перехватить ошибку и отобразить пустую ячейку, воспользуйтесь функцией =ЕСНД().
Обратите внимание, что функция ЕСНД является новой функцией в Excel 2013. Для совместимости с предыдущими версиями воспользуйтесь аналогом этой функции:
Ошибка #ИМЯ?
Excel может вернуть ошибку #ИМЯ? в следующих случаях:
- Формула содержит неопределенный именованный диапазон
- Формула содержит текст, который Excel интерпретирует как неопределенный именованный диапазон. Например, неправильно написанное имя функции вернет ошибку #ИМЯ?
- Формула содержит текст не заключенный в кавычки
- Формула содержит ссылку на диапазон, у которого отсутствует двоеточие между адресами ячеек
- Формула использует функцию рабочего листа, которая была определена надстройкой, но надстройка не была установлена
Ошибка #ПУСТО!
Ошибка #ПУСТО! возникает в случае, когда формула пытается использовать пересечение двух диапазонов, которые фактически не пресекаются. Оператором пересечения в Excel является пробел. Следующая формула вернет #ПУСТО!, так как диапазоны не пересекаются.
Ошибка #ЧИСЛО!
Ошибка #ЧИСЛО! будет возвращена в следующих случаях:
- В числовом аргументе формулы введено нечисловое значение (например, $1,000 вместо 1000)
- В формуле введен недопустимый аргумент (например, =КОРЕНЬ(-12))
- Функция, использующая итерацию, не может рассчитать результат. Примеры функций, использующих итерацию: ВСД(), СТАВКА()
- Формула возвращает значение, которое слишком большое или слишком маленькое. Excel поддерживает значения между -1E-307 и 1E-307.
Ошибка #ССЫЛКА!
Ошибка #ССЫЛКА! возникает в случаях, когда формула использует недействительную ссылку на ячейку. Ошибка возникает в следующих ситуациях:
- Вы удалили колонку или строку, на которую ссылалась ячейка формулы. Например, следующая формула вернёт ошибку, если первая строка или столбцы A или B были удалены:
- Вы удалили рабочий лист, на которую ссылалась ячейка формулы. Например, следующая формула вернёт ошибку, если Лист1 был удален:
- Вы скопировали формулу в расположение, где относительная ссылка становится недействительной. Например, при копировании формулы из ячейки A2 в ячейку A1, формула вернет ошибку #ССЫЛКА!, так как она пытается обратиться к несуществующей ячейке.
- Вы вырезаете ячейку и затем вставляете ее в ячейку, на которую ссылается формула. В этом случае будет возвращена ошибка #ССЫЛКА!
Ошибка #ЗНАЧ!
Ошибка #ЗНАЧ! является самой распространенной ошибкой и возникает в следующих ситуациях:
- Аргумент функции имеет неверный тип данных или формула пытается выполнить операцию, используя неверные данные. Например, при попытке сложения числового значения с текстовым, формула вернет ошибку
- Аргумент функции является диапазоном, когда он должен быть одним значением
- Пользовательские функции листа не рассчитываются. Для принудительного пересчета нажмите Ctrl + Alt + F9
- Пользовательская функция листа пытается выполнить операцию, которая не является допустимой. Например, пользовательская функция не может изменить среду Excel или сделать изменения в других ячейках
- Вы забыли нажать Ctrl + Shift + Enter при вводе формулы массива
Вам также могут быть интересны следующие статьи
- Что если отобразить скрытые строки в Excel не работает
- Седьмой урок обучающего курса — Основы Excel — Управление несколькими рабочими листами
- Пятый урок курса по основам Excel — Печать в программе
- Шестой урок онлайн курса по основам Excel — Управление рабочим листом
- Четвертый урок курса по основам Excel — Изменение ячеек
- Третий урок курса по основам Excel — Форматирование рабочих листов
- Второй урок обучающего курса по Excel «Основы Excel»
- Запуск новой рубрики — Школа Excel
- Несколько условий ЕСЛИ в Excel
- Как построить график с нормальным распределением в Excel
Рубрика: Основы | Метки: логические функции, функции | 5 комментариев | Permalink
5 комментариев
Ренат, прошу у вас помощи,как у спеца… Создала книгу с кучей страниц и ссылок на разные страницы. Потом необходимо построить диаграммы, а у меня мастер диаграмм почему-то стал неактивен, и никак не могу сделать ни через «Вставку», никак. Может я что-то в настройках сбила? помогите, пожалуйста, что можно проверить.
Людмила, есть вариант, что мастер диаграмм перестает быть доступен, если книга находится в общем доступе. Если это ваш случай, сделайте монопольный доступ в книге, вставляйте диаграммы и снова делайте книгу общей.
спасибо! так оно и было
Добрый день! Подскажите как решить проблему, у коллеги на компе все получается а у меня нет…. Надо завязать одну таблицу на другую, раньше все получалось самым простым способом, т.е. в нужную ячейку ставлю знак =, перехожу во вторую таблицу, выбираю нежное значение, нажимаю Ентер, и в первой таблице отображается нужное значение с адресом на вторую табл., т.е. при изменении значений во второй табл. все меняется автомат в первой. Так вот теперь у меня след.ситуация: два файла Ексель, в первом выбираю нужную ячейку, нажимаю =, перехожу во второй файл выбираю нужное значение, а оно не активно, т.е. не дает выбрать, ячейка не светится, как должна при копировании…..первый раз такое, может что-то в настройках…. Подскажите, пожалуйста.
Источник: exceltip.ru