Ovaj će članak govoriti o tome kako se sve vrijednosti podudaraju iz tablice i dohvatiti u različitim ćelijama. Ovo je slično traženju više vrijednosti.
Opća formula
{= INDEX (nazivi, SMALL (IF (groups = group_name, ROW (names) -MIN (ROW (names))+1), COLUMNS (proširujući rasponi)))), “--List Ends--”)}
Previše funkcija i varijabli !!!. Pogledajmo koje su to varijable.
Imena: Ovo je popis imena.
Grupe: Popis grupa kojima pripadaju i ova imena.
Grupno ime: upućivanje na naziv grupe.
Proširenje raspona: ovo je proširujući raspon koji se koristi za povećanje broja kada se kopira udesno.
Primjer: Izdvojite imena zaposlenika u različite stupce ovisno o njihovoj tvrtki.
Recimo da imate tablicu zaposlenika koja je grupirana prema njihovoj tvrtki. Prvi stupac sadrži imena zaposlenika, a drugi stupac sadrži naziv tvrtke.
Sada moramo unijeti ime svakog zaposlenika u različite stupce prema njihovoj tvrtki. Drugim riječima, moramo ih razgrupirati.
Ovdje sam imenovao A2: A10 kao zaposlenika, a B2: B10 kao tvrtku, tako da se ta formula lako čita.
Napišite ovu formulu niza u F2. Koristite CTRL+SHIFT+ENTER za unos ove formule.
{= INDEKS (Zaposlenik, MALI (AKO (Tvrtka = $ E2, ROW (Zaposlenik) -MIN (ROW (Zaposlenik))+1), KOLONCI ($ E $ 1: E1)))), "--Lista završava--" )}
Kopirajte ovu formulu u sve ćelije. Izdvojit će svaki pojedinačni naziv u različite stupce prema njihovoj skupini.
Kao što možete vidjeti na gornjoj slici, svaki zaposlenik je odvojen u različite ćelije.
Dakle, kako funkcionira ova formula?
Da bismo razumjeli formulu, pogledajmo formulu u G2
Što je = IFERROR (INDEX (Zaposlenik, MALO (AKO (Tvrtka = E3 USD,RED(Zaposlenik) -MIN (RED (Zaposlenik))+1), COLUMNS ($ E $ 1: F2))), "-List završava--")
Mehanika je jednostavna i gotovo ista kao i više formula VLOOKUP. Trik je u tome da dobijete indeksni broj svakog zaposlenika iz različitih grupa i proslijedite ga formuli INDEX. To se postiže ovim dijelom formule.
AKO(Društvo=E3 USD,RED(Zaposlenik) -MIN (RED (Zaposlenik))+1):
Ovaj dio vraća niz indeksa i netočnih za naziv tvrtke u $ E3, koji sadrži "Rankwatch".
{FALSE; 2; FALSE; 4; FALSE; FALSE; 7; FALSE; 9}.
Kako? Srušimo ga iznutra.
Ovdje usklađujemo naziv tvrtke u $ E3 sa svakom vrijednošću u Raspon tvrtke (Tvrtka = E3 USD).
Ovo vraća niz vrijednosti true i false. {FALSE;PRAVI; FALSE;PRAVI; FALSE; FALSE;PRAVI; FALSE;PRAVI}.
Sada funkcija IF izvodi svoje TRUE izraze za TRUE, što je ROW (zaposlenik) -MIN (ROW (Zaposlenik))+1. Ovaj dio vraća ovaj dio vraća niz indeksa počevši od 1 do broja zaposlenici {1; 2; 3; 4; 5; 6; 7; 8; 9}. Funkcija if prikuplja vrijednosti samo za TRUE što nam daje {FALSE; 2; FALSE; 4; FALSE; FALSE; 7; FALSE; 9}.
Trenutna formula pojednostavljena je do
= IFERROR (INDEKS (Zaposlenik, MALI ({FALSE; 2; FALSE; 4; FALSE; FALSE; 7; FALSE; 9},KOLONE ($ E $ 1: F2))), "-Popis završava--"). Kao što znamo, mala funkcija vraća n -tu najmanju vrijednost iz niza. KOLONE ($ E $ 1: F2) ovo vraća 2. SMALL funkcija vraća drugu najmanju vrijednost iz gornjeg niza, a to je 4.
Sada je formula pojednostavljena = IFERROR (INDEKS (Zaposlenik, 4), "-Popis završava--"). Sada funkcija INDEX jednostavno vraća četvrti naziv iz zaposlenik niz koji nam daje “Sam”.
Pa da, ovako se izdvajaju imena iz grupa u različitim stupcima pomoću funkcije INDEX, SMALL, ROW, COLUMNS i IF. Ako imate bilo kakvih nedoumica u vezi s ovom funkcijom ili ako vam ne radi, javite mi odjeljak s komentarima u nastavku.
Preuzmi datoteku:
Povezani članci:
VLOOKUP Više vrijednosti
Upotrijebite INDEX i MATCH za traženje vrijednosti
Traženje vrijednosti s više kriterija
Popularni članci:
Funkcija VLOOKUP u Excelu
COUNTIF u Excelu 2016
Kako koristiti funkciju SUMIF u Excelu