Formuła ogólna
(=INDEX(list,MATCH(0,COUNTIF(uniques,list),0)))
Podsumowanie
Aby wyodrębnić tylko unikatowe wartości z listy lub kolumny, możesz użyć formuły tablicowej opartej na INDEKSIE, PODAJNIKU i LICZ.JEŻELI. W pokazanym przykładzie formuła w D5, skopiowana w dół, to:
(=INDEX(list,MATCH(0,COUNTIF($D$4:D4,list),0)))
gdzie „lista” to nazwany zakres B5: B11.
Uwaga: to jest formuła tablicowa i należy ją wprowadzić za pomocą klawiszy Ctrl + Shift + Enter.
Wyjaśnienie
Rdzeniem tej formuły jest podstawowe wyszukiwanie za pomocą INDEKSU:
=INDEX(list,row)
Innymi słowy, podaj INDEX listę i numer wiersza, a INDEX pobierze wartość do dodania do unikalnej listy.
Ciężka praca polega na ustaleniu numeru WIERSZA, aby uzyskać INDEKS, tak abyśmy otrzymali tylko unikalne wartości. Odbywa się to za pomocą MATCH i COUNTIF, a główna sztuczka jest tutaj:
COUNTIF($D$4:D4,list)
W tym przypadku funkcja LICZ.JEŻELI zlicza, ile razy elementy znajdujące się już na liście unikatowej pojawiają się na liście głównej, używając rozszerzającego się odwołania do zakresu, $ D $ 4: D4.
Rozszerzające się odniesienie jest absolutne z jednej strony, względne z drugiej. W takim przypadku w miarę kopiowania formuły odwołanie rozszerzy się, aby uwzględnić więcej wierszy na unikalnej liście.
Zauważ, że odwołanie zaczyna się w D4, jeden wiersz powyżej pierwszego unikalnego wpisu na liście unikatowych. Jest to zamierzone - chcemy liczyć elementy * już * na unikalnej liście i nie możemy uwzględnić bieżącej komórki bez tworzenia cyklicznego odwołania. Więc zaczynamy od wiersza powyżej.
Ważne: upewnij się, że nagłówek unikalnej listy nie pojawia się na liście głównej.
Dla kryteriów w LICZ.JEŻELI używamy samej listy głównej. W przypadku podania wielu kryteriów LICZ.JEŻELI zwróci wiele wyników w tablicy. W każdym nowym wierszu mamy inną tablicę, taką jak ta:
(0;0;0;0;0;0;0) // row 5 (1;0;0;0;1;0;0) // row 6 (1;1;0;0;1;0;1) // row 7 (1;1;1;1;1;0;1) // row 8
Uwaga: LICZ.JEŻELI obsługuje wiele kryteriów z relacją „LUB” (tj. LICZ.JEŻELI (zakres, („czerwony”, „niebieski”, „zielony”)) liczy kolor czerwony, niebieski lub zielony.
Teraz mamy tablice, których potrzebujemy, aby znaleźć pozycje (numery wierszy). W tym celu używamy funkcji MATCH, ustawionej na dokładne dopasowanie, aby znaleźć wartości zerowe. Jeśli umieścimy tablice utworzone przez COUNTIF powyżej w MATCH, otrzymamy:
MATCH(0,(0;0;0;0;0;0;0),0) // 1 (Joe) MATCH(0,(1;0;0;0;1;0;0),0) // 2 (Bob) MATCH(0,(1;1;0;0;1;0;1),0) // 3 (Sue) MATCH(0,(1;1;1;1;1;0;1),0) // 6 (Aya)
PODAJ.POZYCJĘ lokalizuje elementy, szukając liczby zerowej (tj. Wyszukując elementy, które nie pojawiają się jeszcze na unikalnej liście). To działa, ponieważ PODAJ.POZYCJĘ zawsze zwraca pierwsze dopasowanie, gdy są duplikaty.
Na koniec pozycje są wprowadzane do INDEKSU jako numery wierszy, a INDEKS zwraca nazwę na tej pozycji.
Wersja bez tablicy z WYSZUKAJ
Możesz utworzyć formułę inną niż tablicowa, aby wyodrębnić unikalne elementy za pomocą elastycznej funkcji WYSZUKAJ:
=LOOKUP(2,1/(COUNTIF($D$4:D4,list)=0),list)
Konstrukcja formuły jest podobna do powyższej formuły DOPASOWANIE INDEKSU, ale funkcja WYSZUKAJ może obsługiwać operacje tablicowe natywnie.
- LICZ.JEŻELI zwraca liczbę każdej wartości z „listy” w rozszerzającym się zakresie $ D $ 4: D4
- Porównanie do zera tworzy tablicę wartości PRAWDA i FAŁSZ
- Liczba 1 jest dzielona przez tablicę, tworząc tablicę błędów 1 i # DIV / 0
- Ta tablica staje się wektorem wyszukiwania wewnątrz funkcji LOOKUP
- Wartość wyszukiwania 2 jest większa niż jakiekolwiek wartości w lookup_vector
- WYSZUKAJ dopasuje ostatnią wartość niebędącą błędem w tablicy odnośników
- WYSZUKAJ zwraca odpowiednią wartość w wektorze wyniku, nazwanym zakresie „lista”
Wyodrębnij elementy, które pojawiają się tylko raz
Powyższy wzór LOOKUP można łatwo rozszerzyć za pomocą logiki boolowskiej. Aby wyodrębnić listę unikatowych elementów, które pojawiają się tylko raz w danych źródłowych, możesz użyć następującego wzoru:
=LOOKUP(2,1/((COUNTIF($D$4:D4,list)=0)*(COUNTIF(list,list)=1)),list)
Jedynym dodatkiem jest drugie wyrażenie COUNTIF:
COUNTIF(list,list)=1
W tym przypadku funkcja LICZ.JEŻELI zwraca tablicę z liczbą elementów w następujący sposób:
(2;2;2;2;2;1;2)
które są porównywane z 1, co daje tablicę wartości PRAWDA / FAŁSZ:
(FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE)
które działają jak „filtr” ograniczający dane wyjściowe do elementów, które występują tylko raz w danych źródłowych.
