Všeobecný vzorec
(=INDEX(list,MATCH(0,COUNTIF(uniques,list),0)))
Zhrnutie
Ak chcete zo zoznamu alebo stĺpca extrahovať iba jedinečné hodnoty, môžete použiť maticový vzorec založený na indexoch INDEX, MATCH a COUNTIF. V zobrazenom príklade je vzorec v D5, skopírovaný nadol,:
(=INDEX(list,MATCH(0,COUNTIF($D$4:D4,list),0)))
kde „zoznam“ je pomenovaný rozsah B5: B11.
Poznámka: toto je vzorec poľa a musí sa zadávať pomocou klávesov Control + Shift + Enter.
Vysvetlenie
Jadrom tohto vzorca je základné vyhľadávanie s indexom INDEX:
=INDEX(list,row)
Inými slovami, uveďte INDEX zoznam a číslo riadku a INDEX vyhľadá hodnotu, ktorá sa má pridať do jedinečného zoznamu.
Tvrdou prácou je prísť na ROW číslo, ktoré dá INDEX, aby sme dostali iba jedinečné hodnoty. Toto sa deje s MATCH a COUNTIF a hlavný trik je tu:
COUNTIF($D$4:D4,list)
COUNTIF tu počíta, koľkokrát sa položky, ktoré sa už nachádzajú v jedinečnom zozname, objavia v hlavnom zozname pomocou rozbaľovacej referencie pre rozsah $ D $ 4: D4.
Rozširujúci sa odkaz je na jednej strane absolútny, na druhej strane relatívny. V takom prípade, keď sa vzorec skopíruje nadol, referencia sa rozšíri tak, aby zahŕňala viac riadkov v jedinečnom zozname.
Všimnite si, že referencia začína v D4, jeden riadok nad prvou jedinečnou položkou, v jedinečnom zozname. Toto je zámerné - chceme spočítať položky * už * v jedinečnom zozname a nemôžeme zahrnúť aktuálnu bunku bez vytvorenia kruhového odkazu. Začíname teda v riadku vyššie.
Dôležité: uistite sa, že nadpis jedinečného zoznamu sa nenachádza v hlavnom zozname.
Pre kritériá v COUNTIF používame samotný hlavný zoznam. Ak je zadaných viac kritérií, funkcia COUNTIF vráti viac výsledkov do poľa. V každom novom riadku máme iné pole, ako je toto:
(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
Poznámka: COUNTIF spracováva viac kritérií so vzťahom „ALEBO“ (tj. COUNTIF (rozsah, („červená“, „modrá“, „zelená“)) počíta červené, modré alebo zelené.
Teraz máme polia, ktoré potrebujeme na nájdenie pozícií (čísla riadkov). Na tento účel používame MATCH, nastavený na presnú zhodu, na nájdenie nulových hodnôt. Ak dáme polia vytvorené vyššie uvedeným príkazom COUNTIF do MATCH, dostaneme toto:
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)
MATCH vyhľadáva položky hľadaním nulového počtu (tj hľadaním položiek, ktoré sa ešte nenachádzajú v jedinečnom zozname). To funguje, pretože MATCH vždy vráti prvú zhodu, ak existujú duplikáty.
Nakoniec sa pozície vložia do INDEXU ako čísla riadkov a INDEX vráti názov na tejto pozícii.
Verzia bez poľa s VYHĽADÁVANÍM
Pomocou flexibilnej funkcie VYHĽADÁVANIE môžete vytvoriť vzorec bez poľa na extrahovanie jedinečných položiek:
=LOOKUP(2,1/(COUNTIF($D$4:D4,list)=0),list)
Konštrukcia vzorca je podobná vyššie uvedenému vzorcu INDEX MATCH, ale VYHĽADÁVANIE môže operáciu poľa zvládnuť natívne.
- COUNTIF vráti počty jednotlivých hodnôt zo zoznamu v rozširujúcom sa rozsahu $ D $ 4: D4
- Pri porovnaní s nulou sa vytvorí pole hodnôt TRUE a FALSE
- Číslo 1 je vydelené poľom, čím sa vytvorí pole 1 s a chyby # DIV / 0
- Toto pole sa stáva vyhľadávacím vektorom vo vnútri VYHĽADÁVANIA
- Vyhľadávacia hodnota 2 je väčšia ako akékoľvek hodnoty v lookup_vector
- LOOKUP sa bude zhodovať s poslednou bezchybnou hodnotou vo vyhľadávacom poli
- LOOKUP vráti zodpovedajúcu hodnotu v result_vector, pomenovanom rozsahu "list"
Extrahujte položky, ktoré sa zobrazia iba raz
Vyššie uvedený vzorec LOOKUP sa dá ľahko rozšíriť pomocou logickej logiky. Ak chcete extrahovať zoznam jedinečných položiek, ktoré sa v zdrojových dátach nachádzajú iba raz, môžete použiť nasledujúci vzorec:
=LOOKUP(2,1/((COUNTIF($D$4:D4,list)=0)*(COUNTIF(list,list)=1)),list)
Jediným doplnkom je druhý výraz COUNTIF:
COUNTIF(list,list)=1
COUNTIF tu vráti pole počtov položiek takto:
(2;2;2;2;2;1;2)
ktoré sa porovnávajú s 1, výsledkom je pole hodnôt TRUE / FALSE:
(FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE)
ktoré fungujú ako „filter“ na obmedzenie výstupu na položky, ktoré sa v zdrojových dátach vyskytujú iba raz.
