Понимание и устранение ошибок #NUM в Excel — это больше, чем просто задача устранения неполадок; это важный навык для любого, кто работает с Excel. При просмотре финансовых данных, проведении научных исследований или управлении запасами эти ошибки в формулах могут выйти на поверхность и поставить под угрозу ваш анализ. В этой статье мы расскажем вам о различных способах исправления ошибки #NUM.
Кроме того, обязательно ознакомьтесь с нашими руководствами по как исправить ошибки #Н/Д в формулах Excel и часто встречающимся ошибка нарушения обмена.
Что такое ошибка #NUM и почему она возникает?
Сообщение об ошибке #NUM в Excel — одна из наиболее распространенных ошибок. Он сработает по следующим причинам:
Исправьте ошибку #NUM, вызванную неправильным аргументом функции
Наиболее распространенной причиной ошибки #NUM является недопустимый аргумент или неправильные типы данных. Если вы подозреваете, что это является причиной ошибки #NUM в вашей функции, проверьте типы данных и синтаксис формулы на наличие ошибок.
Например, если вы используете функцию ДАТА, Excel ожидает, что вы будете использовать в качестве аргумента года только числа от 1 до 9999. Если вы предоставите ему значение года, выходящее за пределы этого диапазона, это приведет к ошибке #NUM.
Аналогично, если вы используете функцию РАЗНДАТ, указанная дата окончания должна быть больше даты начала. Вы также можете иметь записи с одинаковой датой. А если наоборот, результатом будет ошибка #NUM.
Давайте посмотрим на примере:
=DATEDIF(A2,B2»,d») вычисляет разницу в количестве дней между двумя датами (в ячейках A2 и B2). Результат будет числовым, если дата в ячейке A2 меньше даты в ячейке B2. Если это не так, как в нашем примере ниже, результатом будет ошибка #NUM..
Исправить ошибку #NUM, вызванную слишком большими или слишком маленькими числами
Если в вашей формуле есть аргумент, превышающий ограничение числа Excel, это приведет к ошибке #NUM. Да, Microsoft Excel имеет ограничение на размер чисел, которые он может вычислять. Чтобы решить эту проблему, вам придется настроить входное значение так, чтобы результат попадал в допустимый диапазон.
Если вам приходится работать с такими большими числами, рассмотрите возможность разбить расчет на более мелкие части. Это позволит вам использовать несколько ячеек для достижения конечного результата.
Вот пределы вычислений в Microsoft Excel:
Если результат используемой вами формулы выходит за пределы этих областей, вы получите ошибку #NUM.
Давайте рассмотрим это на примере.
Примечание: в новом Версии Microsoft Excel только большие числа приведут к ошибке #NUM. Слишком маленькие числа приведут к 0 (общий формат) или 0,00E+00 (научный формат).
Если вы не знакомы с научным форматом, помните, что, например, часть «E-20» переводится как «умножить на 10 в степени -20».
Исправить ошибку #NUM, вызванную невозможными вычислениями
Другая причина появления ошибки #NUM — если Excel считает расчет невозможным. В этом случае вам придется определить функцию или часть функции, вызывающую проблему, и соответствующим образом скорректировать формулу или входные данные.
Наиболее типичным примером невозможного вычисления является попытка найти квадратный корень из отрицательного числового значения. Давайте посмотрим это на примере с функцией SQRT:
Если вы используете =SQRT(25), результат будет 5.
Если вы используете =SQRT(-25), результатом будет #NUM.
Вы можете решить эту проблему, если примените функцию ABS и получите абсолютное значение числа..
=КОРЕНЬ(ABS(-25))
Или
=SQERT(ABS(cell_reference))
Аналогично, Excel не поддерживает комплексные числа, поэтому любые вычисления, которые приводят к получению комплексного числа, приведут к ошибке #NUM. В этом случае расчет невозможно выполнить в рамках ограничений Excel.
Примером этого может быть попытка возвести отрицательное число в нецелую степень.
Исправить ошибку #NUM, когда формула итерации не может сходиться
Возможно, вы получаете ошибку #NUM, поскольку используемая вами формула не может найти результат. В этом случае вам придется изменить входные значения и помочь формуле выполнить расчет.
Итерация – это функция Excel, позволяющая многократно вычислять формулы до тех пор, пока не будут выполнены необходимые условия. Это означает, что формула попытается найти результат методом проб и ошибок. Существует несколько функций Excel, использующих функцию итерации: IRR, XIRR или RATE. Формула итерации, которая не может найти допустимый результат в заданных параметрах, вернет ошибку #NUM.
Например:
Помогите формуле, предоставив первоначальное предположение:
Возможно, вам придется настроить параметры итерации в Excel, чтобы ваша формула сходилась. Вот как это сделать:
Исправьте # Число ошибок в функции IRR в Excel
Если возникнет проблема несовпадения, ваша формула внутренней нормы доходности не сможет найти решение. Результатом будет ошибка #NUM. Чтобы справиться с этой проблемой, вам придется настроить параметры итерации Excel и предоставить первоначальное предположение..
Как и другие итерационные функции, функция IRR может привести к ошибке #NUM, поскольку формула не может найти результат после определенного количества итераций. Вы можете легко решить эту проблему, если увеличите количество итераций и предоставите первоначальное предположение. Примеры смотрите в предыдущем разделе.
Если вы имеете дело с противоречивыми знаками, как в нашем примере ниже, обязательно вводите все первоначальные исходящие денежные потоки в виде отрицательных чисел. Функция предполагает наличие как положительных, так и отрицательных денежных потоков.
Например, если для вашей инвестиции установлено значение 10 000 долларов США, функция IRR выдаст ошибку #NUM.
Если вы измените сумму инвестиций на -10 000 долларов США в качестве отрицательного значения, например:
Функция IRR будет работать.
Обратите внимание, что в современных версиях Microsoft Excel отрицательные значения преобразуются в квадратные скобки (наш -10 000 долларов был заменен на (10 000 долларов США)), поэтому не волнуйтесь, если вы увидите, что это происходит. Формула по-прежнему будет работать.
Следуя шагам и рекомендациям, изложенным в этой статье, вы теперь готовы справиться с этими надоедливыми #NUM! проблемы, гарантируя, что ваши таблицы Excel останутся надежными и безошибочными. Удачной работы с таблицами!
.