= countif говорит, что "0001234" не равно "0001234" Как заставить countif рассматривать строки как текст, а не числа?

Question

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

Я делаю некоторые операции, которые полагаются на COUNTIF, не равный функции (подробности в комментариях, но не имеют отношения к вопросу). Кажется, что при работе со строками, содержащими числа с ведущими нулями, прерывание происходит

Для простой демонстрации

Например, если A1 содержит "foo", тогда:

=COUNTIF(A1,"<>"&A1)

возвращает 0 То есть в A1 нет ячеек, не равных A1 (конечно).

Но если A1 содержит "0001234" в качестве текста, то он возвращает 1, т. Е. A1 НЕ РАВНО А1

Дополнительная информация: =countif(A1:"<>"&"1234") возвращает 0, поэтому проблема заключается в том, что Excel автоматически преобразует строку "0001234" в число 1234, но только в критерии, а не в диапазон а затем находит "0001234" =/= 1234.

ВОЗМОЖНОЕ РЕШЕНИЕ: создайте вспомогательный столбец с символом нежелательного текста, добавленным ко всем кодам. В настоящее время я добавил вспомогательный столбец, содержащий = "@" & X1 во все мои списки, но это не элегантное решение.

Answer 1

РЕДАКТИРОВАТЬ: перейти от добавления NUL к добавлению * к критериям.

Вы увидите похожее поведение с логическими значениями. Я обнаружил, что один из способов "заставить" COUNTIF не выполнять преобразования - это добавить (или предварительно ожидать) * к аргументу критерия. Например:

=COUNTIF(A1,"<>" & A1 & "*" )

вернет 0 даже со строкой 0001234 в A1

«*» Заставляет COUNTIF обрабатывать A1 как текст в аргументе критерия. Конечно, это не удастся, если A1 содержит число. Так что вам может понадобиться проверить это

=COUNTIF(A1,"<>" &  A1 & IF(ISNUMBER(A1),"","*"))

А вот снимок экрана с использованием данных, которые вы разместили в своем комментарии:

Поскольку использование COUNTIF таким образом не является пуленепробиваемым, вы также можете рассмотреть функцию EXACT .

=N(NOT(EXACT(A2,B2)))

или даже, проще говоря, если чувствительность к регистру не является проблемой:

=A2<>B2

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

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

=SUMPRODUCT((A:A=A2)*(B:B<>B2))<1

Кроме того, формула будет работать намного эффективнее (быстрее), если вы сможете ограничить ссылки на весь столбец до уровня меньше целого столбца.