Ошибка #ЧИСЛО в Excel – исправление ошибки числа в формулах

В этом руководстве мы разберем причины ошибки #ЧИСЛО в Excel и покажем, как ее исправить.

Вы смотрите на экран своего компьютера, потягиваете кофе и задаетесь вопросом, почему в электронной таблице Excel внезапно появляется странное сообщение об ошибке с символом хэштега? Это расстраивает, сбивает с толку и совершенно раздражает, но не бойтесь! Мы здесь, чтобы разгадать тайну ошибки #ЧИСЛО в Excel и помочь вам ее исправить.

Ошибка #ЧИСЛО в Excel – причины и решения

Ниже мы более подробно рассмотрим каждую возможную причину ошибки #NUM и предоставим вам необходимые шаги для ее устранения.

Недопустимые входные аргументы

Причина: для некоторых функций Excel требуются определенные входные аргументы. Если вы укажете недопустимые аргументы или неправильные типы данных, Excel может выдать ошибку #ЧИСЛО.

Решение: Убедитесь, что все входные аргументы допустимы и имеют правильный тип данных. Дважды проверьте свою формулу на наличие синтаксических ошибок.

Например, функция ДАТА в Excel ожидает число от 1 до 9999 в качестве аргумента года. Если предоставленное значение года выходит за пределы этого диапазона, произойдет ошибка #ЧИСЛО.

Точно так же функция РАЗНДАТ требует, чтобы дата окончания всегда была больше даты начала (или обе даты были равны). В противном случае формула выдаст ошибку #ЧИСЛО.

Например, эта формула вернет разницу в 10 дней:

=DATEDIF(“01.01.2023”, “11.01.2023”, “d”)

И это вызовет ошибку #ЧИСЛО:

=DATEDIF(“11.01.2023”, “01.01.2023”, “d”)

Слишком большие или слишком маленькие числа

Причина: Microsoft Excel имеет ограничение на размер вычисляемых чисел. Если ваша формула выдает число, превышающее этот предел, возникает ошибка #ЧИСЛО.

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

Современные версии Excel имеют следующие пределы вычислений:

Наименьшее отрицательное число -2.2251E-308 Наименьшее положительное число 2.2251E-308 Наибольшее положительное число 9.99999999999999E+307 Наибольшее отрицательное число -9.99999999999999E+307 Наибольшее допустимое положительное число по формуле 1.7976931348623158E+308 Наибольшее допустимое отрицательное число по формуле -1,7976931348623158Е+ 308

Если вы не знакомы с научным обозначением, я кратко объясню, что оно означает, на примере наименьшего разрешенного положительного числа 2,2251E-308. Часть обозначения «E-308» означает «умножить на 10 в степени -308», поэтому число 2,2251 умножается на 10 в степени -308 (2,2251×10^-308). Это очень маленькое число, которое имеет 307 нулей в десятичной части перед первой ненулевой цифрой (которой является 2).

Как видите, допустимые входные значения в Excel находятся в диапазоне от -2,2251E-308 до 9,99999999999999E+307. В формулах допускаются числа от -1,7976931348623158E+308 до 1,7976931348623158E+308. Если результат вашей формулы или какой-либо из ее аргументов выходит за рамки, вы получите ошибку #ЧИСЛО! ошибка.

Обратите внимание, что в текущей версии Excel 365 только большие числа вызывают ошибку #ЧИСЛО. Небольшие числа, выходящие за пределы, отображаются как 0 (общий формат) или 0,00E+00 (научный формат). Похоже на новое недокументированное поведение:

Невозможные операции

Причина: когда Excel не может выполнить расчет, потому что он считается невозможным, он возвращает ошибку #ЧИСЛО! ошибка.

Решение: определите функцию или операцию, вызывающую ошибку, и соответствующим образом скорректируйте входные данные или измените формулу.

Типичным примером невозможного вычисления является попытка найти квадратный корень из отрицательного числа с помощью функции SQRT. Поскольку квадратный корень из отрицательного числа определить невозможно, формула выдаст ошибку #ЧИСЛО. Например:

=SQRT(25) – возвращает 5

=КОРЕНЬ(-25) – возвращает #ЧИСЛО!

Чтобы исправить ошибку, вы можете обернуть отрицательное число в функцию ABS, чтобы получить абсолютное значение числа:

=КОРЕНЬ(АБС(-25))

Или

=КОРЕНЬ(АБС(A2))

Где A2 — ячейка, содержащая отрицательное число.

Другой случай — возведение отрицательного числа в нецелую степень. Если вы попытаетесь возвести отрицательное число в нецелую степень, результатом будет комплексное число. Поскольку Excel не поддерживает комплексные числа, формула выдаст ошибку #ЧИСЛО. В этом случае ошибка указывает на то, что расчет невозможно выполнить в рамках ограничений Excel.

Формула итерации не может сходиться

Причина: Формула итерации, которая не может найти результат, вызывает ошибку #ЧИСЛО в Excel.

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

Итерация — это функция Excel, позволяющая неоднократно пересчитывать формулы до тех пор, пока не будет выполнено определенное условие. Другими словами, формула пытается достичь результата методом проб и ошибок. В Excel есть ряд таких функций, включая IRR, XIRR и RATE. Когда формула итерации не может найти допустимый результат в заданных ограничениях, она возвращает ошибку #ЧИСЛО.

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

Например, если ваша формула СТАВКИ приводит к ошибке #ЧИСЛО, вы можете помочь ей найти решение, предоставив начальное предположение:

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

1. Щелкните меню «Файл» и выберите «Параметры».
2. На вкладке «Формулы» в разделе «Параметры расчета» установите флажок «Включить итеративный расчет».
3. В поле «Максимум итераций» введите количество раз, которое Excel должен выполнять перерасчет формулы. Большее количество итераций увеличивает вероятность нахождения результата, но также требует больше времени для расчета.
4. В поле Максимальное изменение укажите величину изменения между результатами расчета. Меньшее число обеспечивает более точные результаты, но требует больше времени для расчета.

Ошибка #ЧИСЛО в функции Excel IRR

Причина: Формула IRR не может найти решение из-за проблем с несовпадением или когда денежные потоки имеют несовместимые знаки.

Решение. Настройте параметры итерации Excel, сделайте начальное предположение и убедитесь, что денежные потоки имеют соответствующие знаки.

Функция IRR в Excel вычисляет внутреннюю норму доходности для ряда денежных потоков. Однако, если денежные потоки не приводят к действительному IRR, функция возвращает #ЧИСЛО! ошибка.

Как и в случае любой другой функции итерации, ошибка #ЧИСЛО в IRR может возникнуть из-за невозможности найти результат после определенного количества итераций. Чтобы решить эту проблему, увеличьте максимальное количество итераций и/или укажите начальное предположение для скорости возврата (дополнительные сведения см. в предыдущем разделе).

Другая причина, по которой функция IRR может возвращать ошибку #ЧИСЛО, связана с несогласованными знаками в денежных потоках. Функция предполагает наличие как положительных, так и отрицательных денежных потоков. Если нет изменения знака денежных потоков, формула IRR приведет к ошибке #ЧИСЛО. Чтобы устранить эту проблему, убедитесь, что все исходящие платежи, включая первоначальные инвестиции, вводятся как отрицательные числа.

Таким образом, ошибка #ЧИСЛО в Excel является распространенной проблемой, которая может возникать по разным причинам, включая неправильные аргументы, нераспознанные числа/даты, невозможные операции и превышение пределов вычислений Excel. Надеюсь, эта статья помогла вам понять эти причины и эффективно устранить ошибку.