VLOOKUP je jedna od najčešće korištenih funkcija u Excelu. Traži vrijednost u rasponu i vraća odgovarajuću vrijednost u navedenom broju stupca.
Sada sam naišao na problem u kojem sam morao pregledati cijeli redak i vratiti vrijednosti u svim stupcima iz tog retka (umjesto vraćanja jedne vrijednosti).
Pa evo što sam morao učiniti. U donjem skupu podataka imao sam imena prodajnih predstavnika i prodaju koju su obavili u 4 tromjesečja 2012. Imao sam padajući popis s njihovim imenima i htio sam izvući maksimalnu prodaju za tog prodajnog predstavnika u ta četiri tromjesečja.
Mogao bih smisliti 2 različita načina za to - pomoću INDEX -a ili VLOOKUP -a.
Potražite cijeli redak / stupac pomoću formule INDEX
Evo formule koju sam stvorio za to pomoću Indexa
= VELIKI (INDEKS ($ B $ 4: $ F $ 13, MATCH (H3, $ B $ 4: $ B $ 13,0), 0), 1)
Kako radi:
Pogledajmo prvo funkciju INDEX koja je omotana unutar funkcije LARGE.
= INDEKS ($ C $ 4: $ F $ 13, MATCH (H3, $ B $ 4: $ B $ 13,0), 0)
Pomno analiziramo argumente funkcije INDEX:
- Niz - $ B $ 4: $ F $ 1
- Broj retka - MATCH (H3, $ B $ 4: $ B $ 13,0)
- Broj stupca - 0
Uočite da sam koristio broj stupca kao 0.
Trik je u tome što kada broj stupca koristite kao 0, on vraća sve vrijednosti u svim stupcima. Dakle, ako u padajućem izborniku odaberem Johna, formula indeksa vratila bi sve 4 prodajne vrijednosti za Ivana {91064,71690,67574,25427}.
Sada mogu koristiti funkciju Large za izdvajanje najveće vrijednosti
Savjet stručnjaka - Koristite broj stupca/retka kao 0 u formuli indeksa da biste vratili sve vrijednosti u stupcima/retcima.
Potražite cijeli redak / stupac pomoću formule VLOOKUP
Iako je Index formula uredna, čista i robusna, način VLOOKUP -a je pomalo složen. Također završava time što je funkcija promjenjiva. Međutim, postoji nevjerojatan trik koji bih podijelio u ovom odjeljku. Evo formule:
= VELIKO (VLOOKUP (H3, B4: F13, ROW (INDIREKTNO ("2:" & COUNTA ($ B $ 4: $ F $ 4))), FALSE), 1)
Kako radi
- ROW (INDIRECT (“2:” & COUNTA ($ B $ 4: $ F $ 4))) - Ova formula vraća niz {2; 3; 4; 5}. Imajte na umu da, budući da koristi INDIRECT, ovu formulu čini nestabilnom.
- VLOOKUP (H3, B4: F13, ROW (INDIREKTNO (“2:” & COUNTA ($ B $ 4: $ F $ 4))), FALSE) - Ovdje je najbolji dio. Kad ih spojite, postaje VLOOKUP (H3, B4: F13, {2; 3; 4; 5}, FALSE). Sada primijetite da sam mu umjesto jednog broja stupca dao niz brojeva stupaca. A VLOOKUP poslušno traži vrijednosti u svim tim stupcima i vraća niz.
- Sada samo upotrijebite LARGE funkciju za izdvajanje najveće vrijednosti.
Ne zaboravite upotrijebiti Control + Shift + Enter za upotrebu ove formule.
Savjet za profesionalce - U VLOOKUP -u, umjesto korištenja jednog broja stupca, ako koristite niz brojeva stupaca, vratit će niz vrijednosti traženja.
