Пустая ячейка не регистрируется как ноль в Excel

Question

Я использую сложное уравнение ЕСЛИ в Excel 2003 (да, компьютеры на работе тоже старые), и для уравнения важно, чтобы пустые ячейки оценивались в ноль, то есть, как я понимаю, как Excel читает пустые ячейки. Рассматриваемые ячейки отформатированы на сегодняшний день (* 9/10/2014).

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

Я могу исправить эту проблему, вырезав и вставив пустую ячейку над ней, но я не знаю, почему это работает. Мне нужно знать, почему, чтобы исправить это, когда я использую уравнение, чтобы убедиться, что я не получаю ложные значения.

Answer 1

Пустая ячейка не совпадает с нулем в Excel. Excel распознает пустое как пустое, а не значение. Если вам нужно, чтобы пустая ячейка в формуле рассматривалась как ноль, сделайте что-то вроде этого (для этого примера я буду использовать A1 в качестве ссылочной ячейки):

    if(isblank(a1),0,a1)

Используйте это вместо a1 в вашей формуле.

РЕДАКТИРОВАТЬ: вы получили несколько ответов. Оба обращаются к "как это исправить", но ни один из них не затрагивает вторую часть вашего вопроса.

Excel распознает разницу между явным значением ноль и отсутствием значения. В некоторых случаях последствия отсутствия значения в формуле не имеют значения. 3 + 0 - это то же самое, что 3 + ничего. Если в вычислениях нет логической разницы, Excel будет обрабатывать пустую ячейку, как если бы она содержала ноль.

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

На момент этого редактирования существует несколько предложений по принудительному заполнению пустого значения. Простой метод: префикс ссылки на ячейку с парой минусов:

   --a1

Двойные минусы оставят числовое значение без изменений, вернут ноль, если оно пустое, или ошибку, если это что-то другое (например, пробел).

Относительно того, почему у вас есть ячейка, которая ведет себя по-разному, может быть несколько причин. Нам нужно увидеть электронную таблицу и узнать, как вы используете проблемную ячейку. Однако, это, вероятно, не форматирование. По крайней мере, в более поздних версиях Excel, даже если ячейка отформатирована в нечто вроде текста, если она пуста, она будет считаться нулевой для вычислений. Более вероятной причиной будет то, что ячейка на самом деле не пуста, а содержит что-то вроде символа пробела. Если это то, что происходит, ни одно из предложенных исправлений не будет работать (пробел не является пробелом или числом). Попробуйте удалить ячейку, которая удалит любой контент, который не виден.

Если вам интересно содержимое, используйте строковые функции, чтобы увидеть, что это такое. Лен (а1) скажет вам, если он пуст.

Answer 2

ФОРМУЛА:

=VALUE(...)

Эта формула будет иметь значение 0 для пробелов, и действительные значения не будут изменены.

Answer 3

У меня была та же проблема, и я нашел причину: клетки кажутся пустыми, но их не было - в клетках были пробелы (хотя это трудно увидеть). Таким образом, просто удалив пробелы (CTRL R: заменить "" пробелом), решил проблему.

Answer 4

Это похоже на ошибку в Excel 2013.

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

Вот результаты, которые я получаю, когда пытаюсь сослаться на ячейку или посмотреть ее содержимое:

• = IF(ISBLANK(a1), 1,2) ==> возвращает 2. (т. Е. Ячейка не пустая).

• = IF(A1 = 0,0,1) ==> Возвращает 1. (т. Е. Ячейка не содержит ноль).

• = A1 * 2 ==> Возвращает # ЗНАЧЕНИЕ! (т.е. ячейка не содержит числа).

• = IF((A1)> 1,1,0) ==> Возвращает 1 (т. Е. Ячейка содержит число больше 1!).

• = IF(A+> 9.99999999999999E+307,1,0) ==> Возвращает 1. (т. Е. Ячейка содержит очень большое число).

• = "X" & A1 & "X" ==> Возвращает "XX". (т.е. ячейка A1 действительно пуста.)

• = CELL("формат", A1) ==> Возвращает "G". (т.е. числовой формат ячейки является общим).

• = CELL("префикс", A1) ==> Возвращает «^». (т.е. ячейка отформатирована по центру для текста и чисел).

• = CELL("тип", A1) ==> Возвращает "I". (т.е. ячейка содержит текст!).

• = VALUE(A1) ==> Возвращает # VALUE! (т.е. вызов не содержит текстовой строки, которая выглядит как число).

• = LEN(A1) ==> Возвращает 0. (т. Е. Длина любой строки равна нулю).

• = T(A1) ==> ничего не возвращает (т. Е. Возвращает текстовую строку нулевой длины).

• = UNICODE(A1) ==> Возвращает # ЗНАЧЕНИЕ! (т.е. ячейка не содержит строку).

Для меня это происходит, когда я импортировал данные из другого приложения. (Телекурс Infinacials в моем случае). Функция = If (A1 = 0,0,1) сообщает, что ячейка a1 не равна нулю. Функция = A1+1 выдает # ЗНАЧЕНИЕ! ошибка. Несколько других видов математики в пустой ячейке выдают # ЗНАЧЕНИЕ! результат.

Логический вывод состоит в том, что ячейка не содержит числа. Однако это противоречит формуле = if(A1> 99,1,1), из которой следует, что в ячейке содержится большое число.

Я исключил возможность того, что содержимое ячейки скрыто или что ячейка содержит пробел.

(Вы можете проверить это, например, введя формулу наподобие = "X" & A1 & "" X ", где A1 - ячейка, которую вы подозреваете, не пуста. Если формула возвращает "XX", то ячейка не содержит ни числа, ни текста. В противном случае между двумя "X" будет пробел, число или текст.).

Ячейка тоже не пустая, потому что если вы используете формулу = IF(ISBLANK(a1), 1,2), она возвращает 2.

Таким образом, проблема в том, что Excel регистрирует ячейку как не пустую, не нулевую, но не содержащую ни числа, ни символа.

Переформатирование ячейки в числовой формат или в текст ничего не меняет. Помещение a1 в скобки, (a1), не возвращает никакого значения (ни ноль, ни пробел, ни какой-либо символ или пробел).

Единственное решение - щелкнуть ячейку, затем щелкнуть строку ввода и нажать ввод. Проблема исчезает, и она становится пустой ячейкой, которая также возвращает значение ноль.

Это решение хорошо, если у вас есть только одна ячейка, но если у вас есть электронная таблица с тысячами таких ячеек, это проблема. Поэтому в электронной таблице, которая содержит числа и в которой вы хотите, чтобы "очевидно" пустая ячейка была рассчитана как ноль, вам нужно использовать функцию iferror следующим образом:

Старая формула: = A1 * 2

Новая формула: = IFERROR((A1 * 1), 0)* 2