Formuła ogólna
(=SUM(--(FREQUENCY(IF(criteria,MATCH(vals,vals,0)),ROW(vals)-ROW(vals.first)+1)>0)))
Podsumowanie
Aby zliczyć unikatowe wartości tekstowe w zakresie z kryteriami, można użyć formuły tablicowej opartej na funkcjach CZĘSTOTLIWOŚĆ i PODAJ.POZYCJĘ. W przedstawionym przykładzie formuła w G6 to:
(=SUM(--(FREQUENCY(IF(C5:C11=G5,MATCH(B5:B11,B5:B11,0)),ROW(B5:B11)-ROW(B5)+1)>0)))
co zwraca 3, ponieważ nad projektem Omega pracowały trzy różne osoby.
Uwaga: to jest formuła tablicowa i należy ją wprowadzić za pomocą Ctrl + Shift + Enter.
Wyjaśnienie
To jest złożona formuła, która używa CZĘSTOTLIWOŚCI do zliczania wartości liczbowych uzyskiwanych za pomocą funkcji PODAJ.POZYCJĘ. Działając od wewnątrz, funkcja PODAJ.POZYCJĘ służy do uzyskania pozycji każdej wartości, która pojawia się w danych:
MATCH(B5:B11,B5:B11,0)
Wynikiem MATCH jest tablica taka:
(1;1;3;1;1;6;7)
Ponieważ PODAJ.POZYCJĘ zawsze zwraca pozycję pierwszego dopasowania, wartości, które pojawiają się w danych więcej niż raz, zwracają tę samą pozycję. Na przykład, ponieważ „Jan” pojawia się na liście 4 razy, pojawia się w tej tablicy 4 razy jako liczba 1.
Poza funkcją PODAJ.POZYCJĘ, funkcja JEŻELI służy do zastosowania kryteriów, co w tym przypadku obejmuje sprawdzenie, czy projekt jest „omega” (z komórki G5):
IF(C5:C11=G5 // filter on "omega"
Funkcja JEŻELI działa jak filtr, przepuszczając wartości z PODAJ.POZYCJĘ tylko wtedy, gdy są skojarzone z „omega”. Rezultatem jest tablica taka:
(FALSE;FALSE;FALSE;1;1;6;7) // after filtering
Filtrowana tablica jest dostarczana bezpośrednio do funkcji FREQUENCY jako argument data_array . Następnie funkcja WIERSZ służy do zbudowania sekwencyjnej listy liczb dla każdej wartości w danych:
ROW(B3:B12)-ROW(B3)+1
Spowoduje to utworzenie takiej tablicy:
(1;2;3;4;5;6;7;8;9;10)
który staje się argumentem bins_array w FILTER. W tym momencie mamy:
FREQUENCY((FALSE;FALSE;FALSE;1;1;6;7),(1;2;3;4;5;6;7))
FREQUENCY zwraca tablicę liczb wskazujących liczbę dla każdej wartości w tablicy danych, uporządkowaną według bin. Gdy liczba została już policzona, FREQUENCY zwróci zero. Wynik FREQUENCY to tablica taka:
(2;0;0;0;0;1;1;0) // result from FREQUENCY
Uwaga: FREQUENCY zawsze zwraca tablicę z jednym elementem więcej niż bins_array .
W tym momencie możemy przepisać formułę w ten sposób:
=SUM(--((2;0;0;0;0;1;1;0)>0))
Sprawdzamy wartości większe od zera, co konwertuje liczby na PRAWDA lub FAŁSZ:
=SUM(--((TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE)))
Następnie używamy podwójnej ujemnej wartości, aby przekształcić wartości logiczne na 1 i 0:
=SUM((1;0;0;0;0;1;1;0))
Na koniec funkcja SUMA zwraca 3 jako wynik końcowy.
Uwaga: to jest formuła tablicowa i należy ją wprowadzić za pomocą klawiszy Control + Shift + Enter.
Obsługa pustych komórek w zakresie
Jeśli jakiekolwiek komórki w zakresie są puste, musisz dostosować formułę, aby zapobiec przekazywaniu pustych komórek do funkcji PODAJ.POZYCJĘ, co spowoduje zgłoszenie błędu. Możesz to zrobić, dodając kolejną zagnieżdżoną funkcję JEŻELI w celu sprawdzenia pustych komórek:
(=SUM(--(FREQUENCY(IF(B5:B11"",IF(C5:C11=G5,MATCH(B5:B11,B5:B11,0))),ROW(B5:B11)-ROW(B5)+1)>0)))
Z dwoma kryteriami
Jeśli masz dwa kryteria, możesz rozszerzyć logikę formuły, dodając kolejny zagnieżdżony JEŻELI:
(=SUM(--(FREQUENCY(IF(c1,IF(c2,MATCH(vals,vals,0))),ROW(vals)-ROW(vals.1st)+1)>0)))
Gdzie c1 = criteria1, c2 = criteria2 i Vals = wartości wahają.
Z logiką boolowską
Logika boolowska pozwala zredukować zagnieżdżone wartości IF:
(=SUM(--(FREQUENCY(IF((criteria1)*(criteria2),MATCH(vals,vals,0)),ROW(vals)-ROW(vals.1st)+1)>0)))
Ułatwia to dodawanie dodatkowych kryteriów i zarządzanie nimi.
