Generička formula
(=INDEX(list,MATCH(0,COUNTIF(uniques,list),0)))
Sažetak
Da biste iz popisa ili stupca izvukli samo jedinstvene vrijednosti, možete upotrijebiti formulu niza koja se temelji na INDEX, MATCH i COUNTIF. U prikazanom primjeru, formula u D5, kopirana dolje, je:
(=INDEX(list,MATCH(0,COUNTIF($D$4:D4,list),0)))
gdje je "lista" imenovani raspon B5: B11.
Napomena: ovo je formula niza i mora se unijeti pomoću control + shift + enter.
Obrazloženje
Jezgra ove formule je osnovno traženje s INDEX-om:
=INDEX(list,row)
Drugim riječima, INDEX-u dajte popis i broj retka, a INDEX će dohvatiti vrijednost koju će dodati na jedinstveni popis.
Naporan rad je pronalaženje RED broja koji daje INDEKS, tako da dobivamo samo jedinstvene vrijednosti. To se radi s MATCH i COUNTIF, a glavni trik je ovdje:
COUNTIF($D$4:D4,list)
Ovdje COUNTIF broji koliko se puta stavke na jedinstvenom popisu pojavljuju na glavnom popisu, koristeći proširivu referencu za raspon, $ D $ 4: D4.
Referenca koja se širi apsolutna je s jedne, a relativna s druge strane. U ovom slučaju, dok se formula kopira, referenca će se proširiti tako da uključuje više redaka na jedinstveni popis.
Imajte na umu da referenca počinje u D4, jedan redak iznad prvog jedinstvenog unosa, na jedinstvenom popisu. To je namjerno - želimo brojati stavke * već * na jedinstvenom popisu i ne možemo uključiti trenutnu ćeliju bez stvaranja kružne reference. Dakle, započinjemo s gornjim redom.
Važno: vodite računa da se naslov jedinstvenog popisa ne pojavljuje na glavnom popisu.
Za kriterije u COUNTIF-u koristimo sam glavni popis. Kada se zada višestruki kriterij, COUNTIF će vratiti više rezultata u polju. U svakom novom retku imamo drugačiji niz poput ovog:
(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
Napomena: COUNTIF obrađuje više kriterija s odnosom "ILI" (tj. COUNTIF (raspon, ("crveno", "plavo", "zeleno")) broji crveno, plavo ili zeleno.
Sada imamo nizove koji su nam potrebni za pronalaženje položaja (brojeva redaka). Za to koristimo MATCH, postavljen za točno podudaranje, kako bismo pronašli nulte vrijednosti. Ako stavimo nizove stvorene od strane COUNTIF gore u MATCH, evo što ćemo dobiti:
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 pronalazi stavke traženjem broja nula (tj. Traženjem predmeta koji se još ne pojavljuju na jedinstvenom popisu). To djeluje, jer MATCH uvijek vrati prvo podudaranje kad postoje duplikati.
Napokon, pozicije se unose u INDEX kao brojevi redaka, a INDEX vraća ime na toj poziciji.
Verzija bez niza s LOOKUP-om
Možete izraditi formulu koja ne sadrži niz za izdvajanje jedinstvenih predmeta pomoću fleksibilne funkcije LOOKUP:
=LOOKUP(2,1/(COUNTIF($D$4:D4,list)=0),list)
Konstrukcija formule slična je gornjoj formuli INDEX MATCH, ali LOOKUP može nativno obraditi operaciju niza.
- COUNTIF vraća brojeve svake vrijednosti s "popisa" u opsegu koji se širi $ D $ 4: D4
- Usporedbom s nulom stvara se niz TRUE i FALSE vrijednosti
- Broj 1 podijeljen je nizom, stvarajući niz od 1s i # DIV / 0 pogreške
- Ovaj niz postaje lookup_vector unutar LOOKUP-a
- Vrijednost pretraživanja 2 veća je od bilo koje vrijednosti u lookup_vectoru
- LOOKUP podudarat će se s posljednjom vrijednošću koja nije pogreška u nizu pretraživanja
- LOOKUP vraća odgovarajuću vrijednost u result_vector, imenovani raspon "popis"
Izdvojite stavke koje se pojavljuju samo jednom
Gornju formulu LOOKUP lako je proširiti logičkom logikom. Da biste izdvojili popis jedinstvenih predmeta koji se samo jednom pojavljuju u izvornim podacima, možete upotrijebiti formulu poput ove:
=LOOKUP(2,1/((COUNTIF($D$4:D4,list)=0)*(COUNTIF(list,list)=1)),list)
Jedini dodatak je drugi izraz COUNTIF:
COUNTIF(list,list)=1
Ovdje COUNTIF vraća niz brojanja stavki poput ovog:
(2;2;2;2;2;1;2)
koji se uspoređuju s 1, što rezultira nizom TRUE / FALSE vrijednosti:
(FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE)
koji djeluju kao "filtar" kako bi ograničili izlaz na stavke koje se pojavljuju samo jednom u izvornim podacima.
