= countif mówi, że „0001234” nie jest równe „0001234” Jak zmusić countif do traktowania ciągów znaków jako tekstu, a nie liczb?

Mam niektóre kody produktów, z których niektóre są cyframi z zerami wiodącymi, zapisanymi w arkuszu kalkulacyjnym.

Wykonuję niektóre operacje, które opierają się na LICZNIKU, który nie jest równy funkcji (szczegóły w komentarzach, ale nie dotyczą pytania). Countif wydaje się łamać, gdy mamy do czynienia z ciągami zawierającymi liczby z wiodącymi zerami.

Dla prostej demonstracji

Na przykład, jeśli A1 zawiera „foo”, to:

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

zwraca 0. To znaczy, nie ma komórek w A1, które nie są równe A1 (oczywiście).

Ale jeśli A1 zawiera „0001234” jako tekst, wówczas zwraca 1, tzn. A1 NIE JEST RÓWNY DLA A1

Dalsze informacje: =countif(A1:"<>"&"1234")zwraca 0, więc wydaje się, że problem polega na tym, że excel automatycznie konwertuje ciąg „0001234” na liczbę 1234, ale tylko w kryteriach, a nie w zakresie, a następnie znajduje „0001234” = / = 1234.

OBEJŚCIE: utwórz kolumnę pomocniczą z niepotrzebnym znakiem tekstowym dołączonym do wszystkich kodów. Obecnie dodałem kolumnę pomocnika zawierającą = "@" i X1 do wszystkich moich list, ale nie jest to eleganckie rozwiązanie.

— Jakiś facet
źródło

nie powinna być taka formuła =Countif(A1:A100,"<>"&A1)?

— Scott Craner

Czy próbujesz zidentyfikować wszystkie produkty z wiodącymi zerami?

— Raystafarian

Cześć, ray, zawsze dobrze cię widzieć na pytanie. Rzeczywista funkcja, której użyłem to = LICZNIKI (A: A, A1, B: B, „<>” i B1) <1, która zwraca FAŁSZ, jeśli istnieje rekord o tym samym identyfikatorze w A, ale o innej wartości w B. B powinien być unikalny dla A (ale nie odwrotnie) w tych danych, sprawdzam błędy. (tzn. produkt 2345 może mieć atrybut 001. Mogą istnieć również inne produkty z atrybutem 001 na liście, ale 2345 nigdy nie powinien mieć zarówno atrybutu 001, jak i 002)

— Some_Guy

Nie umieściłem tego w pytaniu, ponieważ myślałem, że może to zaciemnić faktyczny problem, z jakim się spotykam. Zadanie, które wykonuję, może mieć bardziej eleganckie rozwiązanie niż moje, ale pod koniec dnia moja formuła robi to, co powinna (z wyjątkiem kodów z wiodącymi zerami). Mam obejście, ale wydaje się, że warto mieć ten problem „na płycie”.

— Some_Guy,

Czasami VBa jest lepszym wyborem, ale zakładam, że nie jest to dla ciebie opcja?

— Dave

EDYCJA : aby zmienić z dodawania NULna dodawanie *do kryteriów.

Podobne zachowanie zobaczysz w przypadku booleanów. Jednym ze sposobów, w jaki znalazłem „wymuszanie”, COUNTIFaby nie przeprowadzać konwersji, jest dodanie (lub oczekiwanie) *argumentu do kryteriów. Na przykład:

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

zwróci 0nawet z ciągiem 0001234w A1

„*” Zmusza COUNTIF do traktowania A1 jako tekstu w argumencie kryterium. Nie powiedzie się to oczywiście, jeśli A1 zawiera liczbę. Może być konieczne przetestowanie tego

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

Oto zrzut ekranu z wykorzystaniem danych opublikowanych w komentarzu:

Ponieważ używanie COUNTIFw ten sposób nie jest całkowicie kuloodporne, możesz również rozważyć tę EXACTfunkcję.

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

lub nawet prościej, jeśli rozróżnianie wielkości liter nie stanowi problemu:

=A2<>B2

zwróci takie same wyniki jak COUNTIFw przypadku symbolu wieloznacznego w kryteriach, a także będzie w stanie wykluczyć możliwość pojawienia się testowanego zakresu w ramach kryteriów.

Jeśli rozumiem COUNTIFSformułę zamieszczoną w powyższym komentarzu, poniższe informacje mogą dostarczyć odpowiednika, ale chciałbym zobaczyć dane z oczekiwanymi wynikami.

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

Ponadto formuła działałaby znacznie wydajniej (szybko), gdyby można ograniczyć odwołania do całej kolumny do mniejszej niż cała kolumna.

— Ron Rosenfeld
źródło

To wydaje się zawsze zwracać 0, nawet jeśli rzeczy nie są równe. Zobacz załączony obraz imgur.com/a/6VSYF

— Some_Guy

@Some_Guy Oczywiście masz rację. Nie wiem, dlaczego myślałem, że to działa. Zmieni się na prawidłowe rozwiązanie.

— Ron Rosenfeld

Jest to lepsze, jednak zależy ono od użycia symbolu wieloznacznego. Gdzie możesz zagwarantować, że jeden przedmiot będzie zawierał inny przedmiot, ale gdybym miał jeden kod 0002345 i inny kod 0002345A, spowodowałoby to problem. Zdecydowanie poprawa w stosunku do poprzedniej sytuacji.

— Some_Guy,

@Some_Guy Tak, to będzie ograniczenie. To, czy jest to śmiertelne, zależy od twojej bazy danych - tylko Ty możesz to ocenić. Jeśli nie lubisz używania COUNTIF, istnieją inne rozwiązania. Opublikuję jeden.

— Ron Rosenfeld