Formuła ogólna
=SUM(--(LEN(UNIQUE(FILTER(range,criteria,"")))>0))
Podsumowanie
Aby zliczyć unikalne wartości z jednym lub wieloma warunkami, możesz użyć formuły opartej na UNIQUE i FILTER. W pokazanym przykładzie formuła w H7 to:
=SUM(--(LEN(UNIQUE(FILTER(B6:B15,C6:C15=H6,"")))>0))
co zwraca 3, ponieważ w B6: B15 są trzy unikalne nazwy skojarzone z projektem Omega.
Uwaga: ta formuła wymaga formuł tablicowych dynamicznych, dostępnych tylko w programie Excel 365. W starszej wersji programu Excel można używać bardziej złożonych formuł alternatywnych.
Wyjaśnienie
Zasadniczo ta formuła wykorzystuje funkcję UNIQUE do wyodrębniania unikatowych wartości, a funkcja FILTER stosuje kryteria.
Działając od wewnątrz, funkcja FILTER służy do stosowania kryteriów i wyodrębniania tylko nazw powiązanych z projektem „Omega”:
FILTER(B6:B15,C6:C15=H6) // Omega names only
Rezultatem FILTER jest tablica taka:
("Jim";"Jim";"Carl";"Sue";"Carl")
Następnie funkcja UNIQUE służy do usuwania duplikatów:
UNIQUE(("Jim";"Jim";"Carl";"Sue";"Carl"))
co daje nową tablicę, taką jak ta:
("Jim";"Carl";"Sue") // after UNIQUE
W tym momencie mamy unikalną listę nazw powiązanych z Omegą i wystarczy je policzyć. Z powodów wyjaśnionych poniżej robimy to za pomocą funkcji DŁ i SUMA. Aby wszystko było jasne, najpierw przepisujemy formułę, tak aby zawierała unikalną listę:
=SUM(--(LEN(("Jim";"Carl";"Sue"))>0))
Funkcja LEN pobiera długość każdego elementu z listy i zwraca tablicę długości:
LEN(("Jim";"Carl";"Sue")) // returns (3;4;3)
Następnie sprawdzamy, czy długości są większe od zera:
LEN((3;4;3)>0 // returns (TRUE;TRUE;TRUE)
I użyj podwójnego ujemnego wyniku, aby przekształcić wartości PRAWDA i FAŁSZ na 1 i 0:
--((TRUE;TRUE;TRUE)) // returns (1;1;1)
Na koniec sumujemy wyniki za pomocą funkcji SUMA:
=SUM((1;1;1)) // returns 3
Ta tablica jest dostarczana bezpośrednio do funkcji COUNTA, która zwraca ostateczną liczbę:
=COUNTA(("Jim";"Carl";"Sue")) // returns 3
Zwróć uwagę, że ponieważ sprawdzamy długość każdego elementu zwróconego przez UNIQUE, puste lub puste komórki spełniające kryteria są ignorowane. Ta formuła jest dynamiczna i zostanie obliczona ponownie natychmiast po zmianie danych źródłowych.
Policz unikalne z wieloma kryteriami
Aby liczyć unikalne wartości w oparciu o wiele kryteriów, możesz rozszerzyć logikę „include” w filtrze FILTER. Na przykład, aby policzyć unikalne nazwy dla projektu Omega tylko w czerwcu, użyj:
=SUM(--(LEN(UNIQUE(FILTER(B6:B15,(C6:C15=H6)*(D6:D15="june"))))>0))
To jest przykład użycia logiki boolowskiej do zastosowania więcej niż jednego warunku. Podejście to jest wyjaśnione bardziej szczegółowo tutaj.
Aby uzyskać więcej informacji, zobacz ten film szkoleniowy: Jak filtrować za pomocą wielu kryteriów.
COUNTA
Można napisać prostszą formułę, która będzie odpowiadać na funkcję COUNTA. Jednak ważnym zastrzeżeniem jest to, że COUNTA zwróci 1, gdy nie ma pasujących wartości. Dzieje się tak, ponieważ funkcja FILTER zwraca błąd, gdy żadne dane nie spełniają kryteriów, a ten błąd jest zliczany przez funkcję COUNTA. Podstawowa formuła COUNTA wygląda następująco:
=COUNTA(UNIQUE(FILTER(B6:B15,C6:C15=H6)))
Ponownie, ta formuła zwróci 1, gdy nie ma pasujących danych. Będzie również zawierać puste komórki spełniające kryteria. Formuła oparta na LEN i SUM jest lepszą opcją.
Brak tablic dynamicznych
Jeśli używasz starszej wersji programu Excel bez obsługi tablic dynamicznych, możesz użyć bardziej złożonej formuły. Aby uzyskać bardziej ogólne omówienie alternatywnych tablic dynamicznych, zobacz: Alternatywy dla formuł tablic dynamicznych.
