Formuła ogólna
=SUMPRODUCT(1/COUNTIF(data,data))
Podsumowanie
Aby zliczyć liczbę unikatowych wartości w zakresie komórek, możesz użyć formuły opartej na funkcjach LICZ.JEŻELI i SUMA.WYKŁAD. W pokazanym przykładzie formuła w F6 to:
=SUMPRODUCT(1/COUNTIF(B5:B14,B5:B14))
Wyjaśnienie
Działając od wewnątrz, funkcja LICZ.JEŻELI jest konfigurowana na wartości z zakresu B5: B14, przy użyciu tych samych wartości jako kryteriów:
COUNTIF(B5:B14,B5:B14)
Ponieważ podajemy 10 wartości kryteriów, otrzymujemy tablicę z 10 wynikami w następujący sposób:
(3;3;3;2;2;3;3;3;2;2)
Każda liczba reprezentuje liczbę - „Jim” pojawia się 3 razy, „Sue” pojawia się 2 razy i tak dalej.
Ta tablica jest skonfigurowana jako dzielnik z 1 jako licznikiem. Po podzieleniu otrzymujemy kolejną tablicę:
(0.333333333333333;0.333333333333333;0.333333333333333;0.5;0.5;0.333333333333333;0.333333333333333;0.333333333333333;0.5;0.5)
Wszelkie wartości występujące tylko raz w zakresie będą wyświetlane jako 1, ale wartości, które występują wielokrotnie, będą wyświetlane jako wartości ułamkowe odpowiadające wielokrotności. (tj. wartość, która pojawi się 4 razy w danych, wygeneruje 4 wartości = 0,25).
Na koniec funkcja SUMPRODUCT sumuje wszystkie wartości w tablicy i zwraca wynik.
Obsługa pustych komórek
Jednym ze sposobów obsługi pustych lub pustych komórek jest dostosowanie formuły w następujący sposób:
=SUMPRODUCT(1/COUNTIF(data,data&""))
Łącząc pusty ciąg („”) z danymi, zapobiegamy umieszczaniu zera w tablicy utworzonej przez funkcję LICZ.JEŻELI, gdy w danych znajdują się puste komórki. Jest to ważne, ponieważ zero w dzielniku spowoduje, że formuła zgłosi błąd # DIV / 0. Działa, ponieważ użycie pustego ciągu („”) jako kryterium spowoduje zliczenie pustych komórek.
Jednak chociaż ta wersja formuły nie zgłosi błędu # DIV / 0 w przypadku pustych komórek, będzie uwzględniać puste komórki w liczbie. Jeśli chcesz wykluczyć puste komórki z liczby, użyj:
=SUMPRODUCT((data"")/COUNTIF(data,data&""))
Powoduje to anulowanie zliczania pustych komórek przez ustawienie licznika na zero dla powiązanych zliczeń.
Powolne działanie?
To fajna i elegancka formuła, ale oblicza znacznie wolniej niż formuły, które używają CZĘSTOTLIWOŚCI do zliczania unikalnych wartości. W przypadku większych zestawów danych warto przełączyć się na formułę opartą na funkcji CZĘSTOŚĆ. Oto wzór na wartości liczbowe i jeden na wartości tekstowe.
