Wcześniej dowiedzieliśmy się, jak liczyć unikalne wartości w zakresie. Nauczyliśmy się również, jak wyodrębnić unikalne wartości z zakresu. W tym artykule dowiemy się jak liczyć unikalną wartość w zakresie z warunkiem w Excelu.
Formuła ogólna
{=SUM(--(CZĘSTOTLIWOŚĆ(JEŻELI(warunek,PODZIEL(zakres,zakres,0)),WIERSZ(zakres)-WIERSZ(pierwszaKomórka w zakresie)+1)>0))}
Jest to formuła tablicowa, użyj CTRL+SHIFT+ENTER
Stan: schorzenie : Kryteria, na podstawie których chcesz uzyskać unikalne wartości.
Zasięg : zakres, w którym chcesz uzyskać unikalne wartości.
pierwsza komórka w zasięgu: Jest to odniesienie do pierwszej komórki w zasięg. Jeśli zakres wynosi A2:A10, to jest to A2.
Przykład:
Tutaj mam te dane nazwisk. Odpowiednie klasy są wymienione w sąsiedniej kolumnie. Musimy liczyć unikalne nazwy w każdej klasie.
Używając powyższej ogólnej formuły, wpisz tę formułę w E2
{=SUMA(--(CZĘSTOTLIWOŚĆ(JEŻELI(B2:B19="Klasa 1",MATCH(A2:A19,A2:A19,0)),WIERSZ(A2:A19)-WIERSZ(A2)+1)>0 ))}
Powyższa formuła zwraca unikalną wartość z zakresu Excela A2:A19 pod warunkiem, że B2:B19="Klasa 1".
Aby uzyskać unikalne wartości w różnych klasach, zmień kryteria. Zakodowaliśmy to tutaj na stałe, ale możesz również podać odwołanie do komórki. Użyj nazwanych zakresów lub odwołań bezwzględnych dla zakresów, jeśli nie chcesz, aby one też się zmieniły.
Jak to działa?
Rozbijmy to od środka.
JEŚLI(B2:B19="Klasa 1",MECZ(A2:A19,A2:A19,0))
B2:B19="Klasa 1": Ta część zwróci tablicę prawdy i fałszu. TRUE dla każdego meczu.
{PRAWDA;FAŁSZ;PRAWDA;FAŁSZ;PRAWDA;PRAWDA;FAŁSZ… .}
MECZ(A2:A19,A2:A19,0): ta część zwróci pierwszą lokalizację każdej wartości w zakresie A2:A19 zgodnie z właściwością MATCH.
{1;2;1;4;5;4;1;8;9;1;2;1;4;5;4;1;8;9}.
Teraz dla każdej wartości TRUE otrzymamy pozycję, a dla false otrzymamy FALSE. Tak więc dla całego oświadczenia IF otrzymamy
{1;FAŁSZ;1;FAŁSZ;5;4;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;2;FAŁSZ;FAŁSZ;5;FAŁSZ;1;8;FAŁSZ}.
Następnie przechodzimy do części częstotliwości.
CZĘSTOTLIWOŚĆ(JEŚLI(B2:B19="Klasa 1",MECZ(A2:A19,A2:A19,0)),WIERSZ(A2:A19)-WIERSZ(A2)+1)
WIERSZ(A2:A19): Zwraca numer wiersza każdej komórki w zakresie A2:A19.
{2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19}
ROW(A2:A19)-ROW(A2): Teraz odejmujemy numer pierwszego wiersza od każdego numeru wiersza. Zwraca tablicę numerów seryjnych zaczynając od 0.
{0;1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17}
Ponieważ chcemy mieć numer seryjny zaczynający się od 1, dodajemy do niego 1.
WIERSZ(A2:A19)-WIERSZ(A2)+1. To daje nam tablicę numerów seryjnych zaczynając od 1.
{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18}
Pomoże nam to w uzyskaniu wyjątkowego liczenia na kondycję.
Teraz mamy: CZĘSTOTLIWOŚĆ({1;FAŁSZ;1;FAŁSZ;5;4;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;2;FAŁSZ;FAŁSZ;5;FAŁSZ;1;8;FAŁSZ},{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18})
Zwraca częstotliwość każdej liczby w danej tablicy.{3;1;0;1;2;0;0;1;0;0;0;0;0;0;0;0;0;0;0;0}
Tutaj każda liczba dodatnia wskazywała na wystąpienie unikalnej wartości, gdy spełnione są kryteria. Musimy zliczyć wartości większe niż 0 w tej tablicy. W tym celu sprawdzamy to przez >0. To zwróci TRUE i FALSE. Konwertujemy prawdę na fałsz za pomocą -- (operator podwójnego binarnego).
SUMA(--({3;1;0;1;2;0;0;1;0;0;0;0;0;0;0;0;0;0;0})>0) to przekłada się na SUMA({1;1;0;1;1;0;0;1;0;0;0;0;0;0;0;0;0;0;0;0})
I na koniec otrzymujemy unikalną liczbę nazw w zakresie kryteriów jako 5.
Wiem, że zrozumienie tego jest trochę skomplikowane, ale sprawdzasz to z opcji oceny formuły.
Aby policzyć unikalne wartości z wieloma kryteriami, możemy użyć logiki logicznej:
Policz unikalną wartość z wieloma kryteriami i logiką
{=SUMA(--(CZĘSTOTLIWOŚĆ(JEŻELI(warunek1 * warunek2,PODZIEL(zakres,zakres,0)),WIERSZ(zakres)-WIERSZ(pierwszaKomórka w zakresie)+1)>0))}
Powyższa ogólna formuła może liczyć unikalne wartości w wielu warunkach i gdy wszystkie z nich są prawdziwe.
Policz unikalną wartość z wieloma kryteriami z lub logiką
{=SUMA(--(CZĘSTOTLIWOŚĆ(JEŻELI(warunek1 + warunek2,PODZIEL(zakres,zakres,0)),WIERSZ(zakres)-WIERSZ(pierwszaKomórka w zakresie)+1)>0))}
Ta ogólna formuła może służyć do zliczania unikalnych wartości za pomocą logiki Or. Oznacza to, że będzie się liczył, czy którykolwiek z warunków jest spełniony.
Tak więc, tak, w ten sposób liczysz unikalne wartości w zakresie w wielu warunkach. To trochę skomplikowane, ale szybkie. Gdy zaczniesz go używać, dowiesz się, jak to działa.
Jeśli masz jakiekolwiek wątpliwości dotyczące tego artykułu o formule Excela, daj mi znać w sekcji komentarzy poniżej.
Pobieranie pliku:
Jak liczyć unikalne wartości w Excelu za pomocą kryteriówFormuła Excel do wyodrębniania unikalnych wartości z listy
Policz unikalne wartości w Excelu
Popularne artykuły:
Funkcja WYSZUKAJ.PIONOWO w programie Excel
LICZ.JEŻELI w Excelu 2016
Jak korzystać z funkcji SUMIF w programie Excel?
