Formuła ogólna
=UNIQUE(FILTER(data,COUNTIF(data,data)>n))
Podsumowanie
Aby wyodrębnić listę unikatowych wartości z zestawu danych, przefiltrowanych według liczby lub wystąpienia, możesz użyć UNIKALNE z FILTREM i zastosować kryteria za pomocą funkcji LICZ.JEŻELI. W przedstawionym przykładzie formuła w D5 to:
=UNIQUE(FILTER(data,COUNTIF(data,data)>1))
co daje 3 unikatowe wartości, które pojawiają się więcej niż raz w nazwanym zakresie „dane” (B5: B16).
Uwaga: W tym przykładzie wyodrębniamy unikalną listę wartości, które pojawiają się więcej niż raz. Innymi słowy, tworzymy listę duplikatów :) Język jest nieco zagmatwany.
Wyjaśnienie
W tym przykładzie użyto funkcji UNIQUE wraz z funkcją FILTER. Możesz zobaczyć bardziej podstawowy przykład tutaj.
Sztuczka w tym przypadku polega na zastosowaniu kryteriów do funkcji FILTER, aby zezwolić tylko na wartości oparte na liczbie wystąpień. Pracując od wewnątrz, odbywa się to za pomocą funkcji LICZ.JEŻELI i funkcji FILTR:
FILTER(data,COUNTIF(data,data)>1)
Wynik funkcji LICZ.JEŻELI to tablica zliczeń w następujący sposób:
(3;1;3;3;2;1;1;3;1;2;3;3)
które są sprawdzane przez porównanie logiczne> 1, aby uzyskać tablicę lub wartości PRAWDA / FAŁSZ:
(TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;TRUE;TRUE)
Uwaga PRAWDA odpowiada wartościom danych, które pojawiają się więcej niż raz. Ta tablica jest zwracana do FILTER jako argument włączający , używany do filtrowania danych. FILTER zwraca jako wynik następną tablicę:
("red";"green";"green";"blue";"red";"blue";"red";"green")
Ta tablica jest zwracana bezpośrednio do funkcji UNIQUE jako argument tablicy . Uwaga z 12 oryginalnych wartości, tylko 8 przetrwało.
UNIQUE następnie usuwa duplikaty i zwraca ostateczną tablicę:
("red";"green";"blue")
Jeśli wartości w B5: B16 zmienią się, dane wyjściowe zostaną natychmiast zaktualizowane.
Liczba> 2
Formuła w F5, która wymienia kolory pojawiające się co najmniej 2 razy w danych źródłowych, to:
=UNIQUE(FILTER(data,COUNTIF(data,data)>2))
Dynamiczny zakres źródła
Ponieważ dane (B5: B15) to normalny nazwany zakres, nie zmieni on rozmiaru, jeśli zostaną dodane lub usunięte dane. Aby użyć zakresu dynamicznego, który w razie potrzeby automatycznie zmieni rozmiar, możesz użyć tabeli programu Excel lub utworzyć dynamiczny nazwany zakres z formułą.
