VLOOKUP je ena najpogosteje uporabljenih funkcij v Excelu. Išče vrednost v obsegu in vrne ustrezno vrednost v podani številki stolpca.
Zdaj sem naletel na težavo, ko sem moral poiskati celotno vrstico in vrniti vrednosti v vseh stolpcih iz te vrstice (namesto da bi vrnil eno samo vrednost).
Torej, tukaj sem moral narediti. V spodnjem nizu podatkov sem imel imena prodajnih predstavnikov in prodajo, ki so jo opravili v štirih četrtletjih leta 2012. Imel sem spustni meni z njihovimi imeni in želel sem v teh štirih četrtletjih izvleči največjo prodajo tega prodajnega predstavnika.
To lahko naredim na dva različna načina - z uporabo INDEX ali VLOOKUP.
Poiščite celotno vrstico / stolpec s formulo INDEX
Tukaj je formula, ki sem jo ustvaril za to z uporabo Indexa
= VELIKO (INDEKS ($ B $ 4: $ F $ 13, MATCH (H3, $ B $ 4: $ B $ 13,0), 0), 1)
Kako deluje:
Najprej poglejmo funkcijo INDEX, ki je zavita v funkcijo LARGE.
= INDEX ($ C $ 4: $ F $ 13, MATCH (H3, $ B $ 4: $ B $ 13,0), 0)
Natančno analiziramo argumente funkcije INDEX:
- Niz - $ B $ 4: $ F $ 1
- Številka vrstice - MATCH (H3, $ B $ 4: $ B $ 13,0)
- Številka stolpca - 0
Upoštevajte, da sem številko stolpca uporabil kot 0.
Trik tukaj je, da ko uporabite številko stolpca kot 0, vrne vse vrednosti v vseh stolpcih. Če torej v spustnem meniju izberem Johna, bo indeksna formula vrnila vse štiri prodajne vrednosti za Johna {91064,71690,67574,25427}.
Zdaj lahko s funkcijo Large izvlečem največjo vrednost
Nasvet profesionalca - Uporabite številko stolpca/vrstice kot 0 v formuli indeksa, da vrnete vse vrednosti v stolpcih/vrsticah.
Poiščite celotno vrstico / stolpec s formulo VLOOKUP
Medtem ko je formula Index čista, čista in robustna, je način VLOOKUP nekoliko zapleten. Prav tako postane funkcija nestanovitna. Vendar pa obstaja neverjeten trik, ki bi ga delil v tem razdelku. Tukaj je formula:
= VELIKO (VLOOKUP (H3, B4: F13, ROW (INDIRECT ("2:" & COUNTA ($ B $ 4: $ F $ 4))), FALSE), 1)
Kako deluje
- ROW (INDIRECT (“2:” & COUNTA ($ B $ 4: $ F $ 4))) - Ta formula vrne niz {2; 3; 4; 5}. Upoštevajte, da zaradi uporabe INDIRECT te formule niha.
- VLOOKUP (H3, B4: F13, ROW (INDIRECT (“2:” & COUNTA ($ B $ 4: $ F $ 4))), FALSE) - Tu je najboljši del. Ko to sestavite, postane VLOOKUP (H3, B4: F13, {2; 3; 4; 5}, FALSE). Zdaj opazite, da sem mu namesto ene številke stolpca dal niz številk stolpcev. In VLOOKUP poslušno išče vrednosti v vseh teh stolpcih in vrne matriko.
- Zdaj uporabite funkcijo LARGE, da izvlečete največjo vrednost.
Za uporabo te formule uporabite Control + Shift + Enter.
Nasvet za profesionalce - V VLOOKUP -u bo namesto uporabe ene same številke stolpca, če uporabljate niz številk stolpcev, vrnil niz iskalnih vrednosti.
