Splošna formula
(=INDEX(list,MATCH(0,COUNTIF(uniques,list),0)))
Povzetek
Če želite s seznama ali stolpca izluščiti samo enolične vrednosti, lahko uporabite matrično formulo, ki temelji na INDEX, MATCH in COUNTIF. V prikazanem primeru je formula v D5, kopirana navzdol,:
(=INDEX(list,MATCH(0,COUNTIF($D$4:D4,list),0)))
kjer je "seznam" imenovani obseg B5: B11.
Opomba: to je matrična formula, ki jo je treba vnesti z uporabo control + shift + enter.
Pojasnilo
Jedro te formule je osnovno iskanje z INDEX:
=INDEX(list,row)
Z drugimi besedami, indeksu dajte seznam in številko vrstice, INDEX pa bo pridobil vrednost, ki jo bo dodal na edinstveni seznam.
Težko delo je ugotoviti ROW številko, da dobimo INDEX, tako da dobimo samo edinstvene vrednosti. To se naredi z MATCH in COUNTIF, glavni trik pa je tukaj:
COUNTIF($D$4:D4,list)
Tu COUNTIF šteje, kolikokrat so elementi, ki so že na edinstvenem seznamu, prikazani na glavnem seznamu, z uporabo razširitvene reference za obseg, $ D $ 4: D4.
Razširjena referenca je na eni strani absolutna, na drugi pa relativna. V tem primeru se ob kopiranju formule sklic razširi in vključi več vrstic na edinstveni seznam.
Upoštevajte, da se sklic začne na D4, eno vrstico nad prvim unikatnim vnosom, na edinstvenem seznamu. To je namerno - na edinstvenem seznamu želimo šteti elemente * že * in trenutne celice ne moremo vključiti, ne da bi ustvarili krožno sklic. Torej, začeli smo v zgornji vrstici.
Pomembno: naslov edinstvenega seznama se ne prikaže na glavnem seznamu.
Za merila v COUNTIF uporabljamo sam glavni seznam. Če dobi več meril, bo COUNTIF vrnil več rezultatov v matriki. V vsaki novi vrstici imamo drugačno matriko, kot je 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
Opomba: COUNTIF obravnava več meril z razmerjem "ALI" (tj. COUNTIF (obseg, ("rdeče", "modro", "zeleno")) šteje rdeče, modre ali zelene.
Zdaj imamo polja, ki jih potrebujemo za iskanje položajev (številk vrstic). Za to uporabimo MATCH, nastavljen za natančno ujemanje, da najdemo nič vrednosti. Če matrike, ki jih je ustvaril zgoraj COUNTIF, damo v MATCH, dobimo naslednje:
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 najde elemente tako, da poišče število nič (tj. Išče elemente, ki še niso na edinstvenem seznamu). To deluje, ker MATCH vedno vrne prvo ujemanje, če obstajajo dvojniki.
Na koncu se položaji podajo v INDEX kot številke vrstic in INDEX vrne ime na tem mestu.
Različica brez matrike s POGLEDOM
S funkcijo prilagodljivega POGLEDA lahko zgradite formulo, ki ni matrike, da izvlečete unikatne predmete:
=LOOKUP(2,1/(COUNTIF($D$4:D4,list)=0),list)
Konstrukcija formule je podobna zgornji formuli INDEX MATCH, toda LOOKUP lahko z matrično obdelavo matrike.
- COUNTIF vrne štetje vsake vrednosti s "seznama" v razširjenem obsegu $ D $ 4: D4
- Primerjava z nič ustvari niz TRUE in FALSE vrednosti
- Številka 1 je deljena z matriko in ustvari matriko 1s in # DIV / 0 napak
- Ta matrika postane lookup_vector znotraj LOOKUP
- Iskalna vrednost 2 je večja od vseh vrednosti v lookup_vector
- LOOKUP se bo ujemal z zadnjo vrednostjo brez napak v iskalnem polju
- LOOKUP vrne ustrezno vrednost v result_vector, imenovani obseg "seznam"
Izvlecite predmete, ki se pojavijo samo enkrat
Zgornjo formulo LOOKUP je enostavno razširiti z logično logiko. Če želite izvleči seznam edinstvenih elementov, ki se pojavijo samo enkrat v izvornih podatkih, lahko uporabite naslednjo formulo:
=LOOKUP(2,1/((COUNTIF($D$4:D4,list)=0)*(COUNTIF(list,list)=1)),list)
Edini dodatek je drugi izraz COUNTIF:
COUNTIF(list,list)=1
Tu COUNTIF vrne matriko štetja elementov, kot je ta:
(2;2;2;2;2;1;2)
ki se primerjajo z 1, kar ima za posledico niz TRUE / FALSE vrednosti:
(FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE)
ki delujejo kot "filter", da omejijo izhod na postavke, ki se pojavijo samo enkrat v izvornih podatkih.
