Formuła ogólna
(=SUM(--(FREQUENCY(IF(criteria,values),values)>0)))
Podsumowanie
Aby zliczyć unikatowe wartości liczbowe w zakresie, możesz użyć formuły opartej na funkcjach CZĘSTOTLIWOŚĆ, SUMA i JEŻELI. W przedstawionym przykładzie numery pracowników pojawiają się w zakresie B5: B14. Wzór w G6 to:
=SUM(--(FREQUENCY(IF(C5:C14="A",B5:B14),B5:B14)>0))
co zwraca 2, ponieważ w budynku A. są 2 unikalne identyfikatory pracowników.
Uwaga: jest to formuła tablicowa i należy ją wprowadzić za pomocą Ctrl + Shift + Enter, chyba że używasz programu Excel 365.
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, będący tabelą podsumowującą zawierającą częstość wartości liczbowych, zorganizowaną w „pojemnikach”. Używamy go tutaj jako okrężnego sposobu zliczania unikalnych wartości liczbowych. Aby zastosować kryteria, używamy funkcji JEŻELI.
Pracując od wewnątrz, najpierw filtrujemy wartości za pomocą funkcji JEŻELI:
IF(C5:C14="A",B5:B14) // filter on building A
Wynikiem tej operacji jest tablica taka:
(905;905;905;905;773;773;FALSE;FALSE;FALSE;FALSE)
Zauważ, że wszystkie identyfikatory w budynku B są teraz FALSE. Ta tablica jest dostarczana bezpośrednio do funkcji FREQUENCY jako data_array . W przypadku tablicy bins_array podajemy same identyfikatory:
FREQUENCY((905;905;905;905;773;773;FALSE;FALSE;FALSE;FALSE),(905;905;905;905;773;773;801;963;963;963))
W tej konfiguracji FREQUENCY zwraca tablicę poniżej:
(4;0;0;0;2;0;0;0;0;0;0)
Wynik jest nieco tajemniczy, ale znaczenie jest takie, że 905 pojawia się cztery razy, a 773 pojawia się dwa razy. Wartości FALSE są automatycznie ignorowane.
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. To jest funkcja, która pozwala na to podejście.
Następnie każda z tych wartości jest testowana jako większa od zera:
(4;0;0;0;2;0;0;0;0;0;0)>0
Rezultatem jest tablica taka:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Każda wartość PRAWDA na liście reprezentuje unikalną liczbę na liście i wystarczy dodać wartości PRAWDA do SUMA. Jednak SUMA nie sumuje wartości logicznych w tablicy, więc musimy najpierw przekonwertować wartości na 1 lub zero. Odbywa się to za pomocą podwójnego ujemnego (-). Wynik jest tablicą tylko 1 lub 0:
(1;0;0;0;1;0;0;0;0;0;0)
Na koniec funkcja SUMA dodaje te wartości i zwraca sumę, która w tym przypadku wynosi 2.
Wiele kryteriów
Możesz rozszerzyć formułę, aby obsługiwała wiele kryteriów, takich jak to:
(=SUM(--(FREQUENCY(IF((criteria1)*(criteria2),values),values)>0)))
