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

Мы разберем причины появления ошибки Excel ЧИСЛО и покажем, как ее можно исправить.

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

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

Содержание

Что означает ошибка #ЧИСЛО в Excel?

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

Эта ошибка обычно возникает по следующим причинам:

функция содержит неверные аргументы,
когда предоставленные числа или даты не являются допустимыми числовыми значениями(например, записаны как текст),
когда вычисление превышает предел того, что может обработать Excel,

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

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

Конкретное решение зависит от самой ошибки и обстоятельств ее возникновения.

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

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

Недопустимые аргументы функции

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

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

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

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

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

=РАЗНДАТ("9/7/2011"; "13/6/2024"; "d")

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

=РАЗНДАТ("9/7/2031"; "13/6/2024"; "d")

Вот еще несколько примеров:

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

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

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

В качестве альтернативы вы можете рассмотреть возможность разбить расчет на более мелкие части и использовать несколько ячеек для получения окончательного результата.

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

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

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

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

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

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

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

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

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

=КОРЕНЬ(25) – возвращает 5

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

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

=КОРЕНЬ(ABS(-25))

или

=КОРЕНЬ(ABS(A2))

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

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

Еще одна очень распространенная ошибка – попытка выполнения математической операции с текстом.

Особенно часто это случается, когда вы импортировали данные из другой программы и ваши даты записаны как текст. Любая попытка добавить дни или найти разность дат приведет к ошибке. В этом случае вам в помощь эта статья: Как преобразовать текст в дату Excel.

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

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

=ЕСЛИ(ЕЧИСЛО(A1); A1 * 2; "Ошибка: неверный тип данных")

Кроме того, важно убедиться, что все ячейки, используемые в формулах, содержат допустимые данные. Это можно сделать путем удаления лишних пробелов или преобразования текстовых значений в числовые. Для преобразования текстовых данных в числа можно использовать функцию ЗНАЧЕН:

=ЗНАЧЕН("1234")

Итерационная формула не может найти решение

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

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

Итерация — это функция Excel, которая позволяет формулам многократно пересчитываться до тех пор, пока не будет выполнено определенное условие. Другими словами, формула пытается достичь результата методом проб и ошибок.

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

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

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

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

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

Ошибка #ЧИСЛО в функции ВСД (IRR)

Причина : формула ВСД не может найти решение когда денежные потоки имеют неверные знаки.

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

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

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

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

Чтобы исправить эту проблему, убедитесь, что все исходящие платежи, включая первоначальные инвестиции, введены как отрицательные числа.

Excel предлагает ряд встроенных инструментов для диагностики и исправления ошибок, таких как «Проверка ошибок» и «Окно проверки формул». Активное использование этих инструментов позволяет оперативно обнаруживать и устранять проблемы, повышая точность расчетов. Также рекомендуется применять условное форматирование для визуального выделения потенциальных ошибок и аномалий в данных.

Кроме того, если вы хотите скрыть сообщения о возникшей ошибке, заменить их каким-то осмысленным текстом либо выполнить альтернативные вычисления, используйте функцию ЕСЛИОШИБКА. Подробнее читайте здесь: Функция ЕСЛИОШИБКА – примеры формул.

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

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

А вот здесь вы можете заказать разработку приложения для банка, в котором точно не будет ошибок: https://finative.ru/b2b/

Надеюсь, эта статья помогла вам понять эти причины и эффективно устранить ошибку.