Formuła ogólna
=SUM(--(FREQUENCY(data,data)>0))
Podsumowanie
Aby zliczyć unikatowe wartości liczbowe w zakresie, można użyć formuły opartej na funkcjach CZĘSTOTLIWOŚĆ i SUMA. W przedstawionym przykładzie numery pracowników pojawiają się w zakresie B5: B14. Wzór w F5 to:
=SUM(--(FREQUENCY(B5:B14,B5:B14)>0))
co zwraca 4, ponieważ na liście znajdują się 4 unikalne identyfikatory pracowników.
Wyjaśnienie
Uwaga: Przed wersją Excel 365 program Excel nie miał dedykowanej funkcji zliczania unikatowych wartości. Ta formuła przedstawia jeden sposób zliczania unikatowych wartości, o ile są one liczbowe. Jeśli masz wartości tekstowe lub kombinację tekstu i liczb, musisz użyć bardziej złożonej formuły.
Funkcja CZĘSTOTLIWOŚĆ programu Excel zwraca rozkład częstotliwości, czyli tabelę podsumowującą, która pokazuje częstotliwość wartości liczbowych, zorganizowanych w „pojemniki”. Używamy go tutaj jako okrężnego sposobu zliczania unikalnych wartości liczbowych.
Pracując od wewnątrz, dostarczamy ten sam zestaw liczb zarówno dla tablicy danych, jak i tablicy bins do FREQUENCY:
FREQUENCY(B5:B14,B5:B14)
FREQUENCY zwraca tablicę z liczbą wszystkich wartości liczbowych w zakresie:
(4;0;0;0;2;0;1;3;0;0;0)
Wynik jest nieco tajemniczy, ale znaczenie jest takie, że 905 pojawia się cztery razy, 773 pojawia się dwa razy, 801 pojawia się raz, a 963 pojawia się trzy razy.
FREQUENCY ma specjalną funkcję, która automatycznie zwraca zero dla wszystkich liczb, które już pojawiły się w tablicy danych, dlatego wartości są równe zeru po napotkaniu liczby.
Następnie każda z tych wartości jest testowana jako większa od zera:
(4;0;0;0;2;0;1;3;0;0;0)>0
Rezultatem jest tablica taka:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE)
Każda PRAWDA reprezentuje unikalną liczbę na liście. SUMA domyślnie ignoruje wartości logiczne, więc wymuszamy wartości PRAWDA i FAŁSZ na 1 i 0 za pomocą podwójnego ujemnego (-), co daje:
=SUM((1;0;0;0;1;0;1;1;0;0;0))
Na koniec funkcja SUMA dodaje te wartości i zwraca sumę, która w tym przypadku wynosi 4.
Uwaga: możesz również użyć SUMPRODUCT, aby zsumować elementy w tablicy.
Używanie LICZ.JEŻELI zamiast CZĘSTOTLIWOŚCI do liczenia unikatowych wartości
Innym sposobem zliczania unikatowych wartości liczbowych jest użycie LICZ.JEŻELI zamiast CZĘSTOTLIWOŚCI. Jest to prostsza formuła, ale pamiętaj, że użycie funkcji LICZ.JEŻELI na większych zestawach danych do zliczania unikatowych wartości może powodować problemy z wydajnością. Formuła FREQUENCY, choć bardziej skomplikowana, oblicza znacznie szybciej.
