Formuła ogólna
SUMPRODUCT(--(A:A=A1))
Podsumowanie
Przedmowa
To irytująco długie wprowadzenie, ale kontekst jest ważny, przepraszam!
Jeśli spróbujesz policzyć bardzo długie liczby (ponad 16 cyfr) w zakresie z LICZ.JEŻELI, możesz zobaczyć nieprawidłowe wyniki z powodu błędu w sposobie, w jaki niektóre funkcje obsługują długie liczby, nawet jeśli te liczby są przechowywane jako tekst. Rozważ poniższy ekran. Wszystkie liczby w kolumnie D są niepoprawne - chociaż każda liczba w kolumnie B jest niepowtarzalna, liczba zwrócona przez COUNTIF sugeruje, że te liczby są duplikatami.
=COUNTIF(data,B5)
Ten problem jest związany ze sposobem obsługi liczb w programie Excel. Excel może obsłużyć tylko 15 cyfr znaczących, a jeśli wprowadzisz liczbę z więcej niż 15 cyframi w programie Excel, zobaczysz cyfry końcowe po cichu przekonwertowane na zero. Powyższy problem z liczeniem wynika z tego ograniczenia.
Zwykle można ominąć ten limit, wprowadzając długie liczby jako tekst, rozpoczynając liczbę od pojedynczego cudzysłowu („999999999999999999”) lub formatując komórki jako Tekst przed wprowadzeniem. Dopóki nie musisz wykonywać operacji matematycznych na liczbie, jest to dobre rozwiązanie i umożliwia wprowadzanie bardzo długich liczb takich jak numery kart kredytowych i numery seryjne bez utraty numerów.
Jeśli jednak spróbujesz użyć funkcji LICZ.JEŻELI do policzenia liczby zawierającej więcej niż 15 cyfr (nawet jeśli jest przechowywana jako tekst), wyniki mogą być niewiarygodne. Dzieje się tak, ponieważ LICZ.JEŻELI wewnętrznie konwertuje długą wartość z powrotem na liczbę w pewnym momencie podczas przetwarzania, wyzwalając 15-cyfrowy limit opisany powyżej. Bez wszystkich cyfr niektóre liczby mogą być liczone jak duplikaty, gdy są liczone za pomocą funkcji LICZ.JEŻELI.
Rozwiązanie
Jednym z rozwiązań jest zastąpienie formuły LICZ.JEŻELI formułą używającą SUMA lub SUMA PROCEDURY. W pokazanym przykładzie formuła w E5 wygląda następująco:
=SUMPRODUCT(--(data=B5))
Formuła używa nazwanego zakresu „dane” (B5: B9) i generuje poprawną liczbę dla każdej liczby za pomocą funkcji SUMPRODUCT.
Wyjaśnienie
Najpierw wyrażenie wewnątrz SUMPRODUCT porównuje wszystkie wartości w nazwanym zakresie „dane” z wartością z kolumny B w bieżącym wierszu. Powoduje to tablicę wyników TRUE / FALSE.
=SUMPRODUCT(--(data=B5)) =SUMPRODUCT(--((TRUE;FALSE;FALSE;FALSE;FALSE)))
Następnie podwójny ujemny konwertuje wartości PRAWDA / FAŁSZ na wartości 1/0.
=SUMPRODUCT((1;0;0;0;0))
Na koniec SUMPRODUCT po prostu sumuje elementy w tablicy i zwraca wynik.
Wariant formuły tablicowej
Możesz również użyć funkcji SUMA zamiast SUMPRODUCT, ale jest to formuła tablicowa i należy ją wprowadzić za pomocą Ctrl + Shift + Enter:
(=SUM(--(B:B=B5)))
Inne funkcje z tym problemem
Nie zweryfikowałem tego osobiście, ale wydaje się, że kilka funkcji ma ten sam problem, w tym SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF i AVERAGEIFS.
