Formuła ogólna
=SUMPRODUCT(--(FREQUENCY(MATCH(data,data,0),ROW(data)-ROW(data.firstcell)+1)>0))
Podsumowanie
Aby policzyć unikalne wartości tekstowe w zakresie, możesz użyć formuły, która korzysta z kilku funkcji: CZĘSTOTLIWOŚĆ, PODAJ.POZYCJĘ, WIERSZ i SUMPRODUCT W przedstawionym przykładzie formuła w klawiszu F5 to:
=SUMPRODUCT(--(FREQUENCY(MATCH(B5:B14,B5:B14,0),ROW(B5:B14)-ROW(B5)+1)>0))
co zwraca 4, ponieważ w B5: B14 są 4 unikalne nazwy.
Uwaga: Innym sposobem zliczania unikatowych wartości jest użycie funkcji LICZ.JEŻELI. Jest to znacznie prostsza formuła, ale może działać wolno na dużych zestawach danych. Dzięki Excel 365 możesz użyć prostszej i szybszej formuły opartej na UNIQUE.
Wyjaśnienie
Ta formuła jest bardziej skomplikowana niż podobna formuła, która używa FREQUENCY do zliczania unikatowych wartości liczbowych, ponieważ FREQUENCY nie działa z wartościami nienumerycznymi. W rezultacie duża część formuły po prostu przekształca dane nienumeryczne na dane liczbowe, które może obsłużyć FREQUENCY.
Działając od wewnątrz, funkcja PODAJ.POZYCJĘ służy do określenia pozycji każdego elementu, który pojawia się w danych:
MATCH(B5:B14,B5:B14,0)
Wynikiem MATCH jest tablica taka:
(1;1;1;4;4;6;6;6;9;9)
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 3 razy, pojawia się w tej tablicy 3 razy jako liczba 1.
Ta tablica jest wprowadzana do FREQUENCY jako argument data_array . Bins_array argumentu jest wykonana z tej części o wzorze:
ROW(B5:B14)-ROW(B5)+1)
który tworzy sekwencyjną listę liczb dla każdej wartości w danych:
(1;2;3;4;5;6;7;8;9;10)
W tym momencie FREQUENCY jest skonfigurowana w następujący sposób:
FREQUENCY((1;1;1;4;4;6;6;6;9;9),(1;2;3;4;5;6;7;8;9;10))
FREQUENCY zwraca tablicę liczb wskazujących liczbę dla każdej liczby w tablicy danych, uporządkowaną według bin. Gdy liczba została już policzona, FREQUENCY zwróci zero. Jest to kluczowa cecha działania tej formuły. Wynik FREQUENCY to tablica taka:
(3;0;0;2;0;3;0;0;2;0;0) // output from FREQUENCY
Uwaga: FREQUENCY zawsze zwraca tablicę z jednym elementem więcej niż bins_array .
Możemy teraz przepisać formułę w następujący sposób:
=SUMPRODUCT(--((3;0;0;2;0;3;0;0;2;0;0)>0))
Następnie sprawdzamy wartości większe od zera (> 0), co konwertuje liczby na PRAWDA lub FAŁSZ, a następnie używamy podwójnego ujemnego (-), aby przekonwertować wartości PRAWDA i FAŁSZ na 1 i 0. Teraz mamy:
=SUMPRODUCT((1;0;0;1;0;1;0;0;1;0;0))
Na koniec SUMPRODUCT po prostu dodaje liczby i zwraca sumę, która w tym przypadku wynosi 4.
Obsługa pustych komórek
Puste komórki w zakresie spowodują, że formuła zwróci błąd # N / D. Aby obsłużyć puste komórki, możesz użyć bardziej skomplikowanej formuły tablicowej, która używa funkcji JEŻELI do odfiltrowywania pustych wartości:
(=SUM(IF(FREQUENCY(IF(data"", MATCH(data,data,0)),ROW(data)-ROW(data.firstcell)+1),1)))
Uwaga: dodanie JEŻELI powoduje, że jest to formuła tablicowa, która wymaga Ctrl-Shift-Enter.
Więcej informacji można znaleźć na tej stronie.
Inne sposoby liczenia unikatowych wartości
Jeśli masz program Excel 365, możesz użyć funkcji UNIQUE do zliczania unikatowych wartości za pomocą znacznie prostszej formuły.
Tabela przestawna to także doskonały sposób zliczania unikalnych wartości.
