VLOOKUP on üks Exceli enimkasutatavaid funktsioone. See otsib väärtust vahemikust ja tagastab vastava väärtuse määratud veerunumbriga.
Nüüd puutusin kokku probleemiga, kus pidin otsima terve rea ja tagastama selle rea kõikide veergude väärtused (mitte ühe väärtuse tagastamise asemel).
Nii et siin ma pidin tegema. Allpool olevas andmekogumis olid mul müügiesindajate nimed ja müük, mille nad 2012. aastal 4 kvartalis tegid. Mul oli nende nimedega rippmenüü ja tahtsin välja tuua selle müügiesindaja maksimaalse müügi nende nelja kvartali jooksul.
Ma võiksin välja pakkuda 2 erinevat võimalust selleks - INDEXi või VLOOKUPi kasutamine.
Otsige kogu rida / veerg INDEX -valemi abil
Siin on valem, mille ma selle indeksi abil lõin
= SUUR (INDEKS ($ B $ 4: $ F $ 13, MATCH (H3, $ B $ 4: $ B $ 13,0), 0), 1)
Kuidas see töötab:
Vaatame kõigepealt funktsiooni INDEX, mis on pakitud funktsiooni LARGE sisse.
= INDEKS ($ C $ 4: $ F $ 13, MATCH (H3, $ B $ 4: $ B $ 13,0), 0)
Analüüsime põhjalikult funktsiooni INDEX argumente:
- Massiiv - $ B $ 4: $ F $ 1
- Rida number - MATCH (H3, $ B $ 4: $ B $ 13,0)
- Veeru number - 0
Pange tähele, et kasutasin veeru numbrit 0.
Siin on trikk selles, et kui kasutate veeru numbrit 0 -na, tagastab see kõikide veergude kõik väärtused. Seega, kui valin rippmenüüst John, tagastab indeksi valem kõik John 4 müügiväärtused {91064,71690,67574,25427}.
Nüüd saan suurima funktsiooni saamiseks kasutada funktsiooni Suur
Pro näpunäide. Kasutage veeru/rea numbrit kui 0 indeksi valemis, et taastada kõik veergude/ridade väärtused.
Otsige kogu rida / veerg, kasutades VLOOKUP valemit
Kuigi indeksi valem on puhas, puhas ja vastupidav, on VLOOKUP -viis natuke keeruline. See muudab ka funktsiooni lenduvaks. Siiski on hämmastav trikk, mida ma selles osas jagan. Siin on valem:
= LARGE (VLOOKUP (H3, B4: F13, ROW (KAUDSE ("2:" & COUNTA ($ B $ 4: $ F $ 4))), FALSE), 1)
Kuidas see töötab
- ROW (KAUDNE („2:” & COUNTA ($ B $ 4: $ F $ 4))) - see valem tagastab massiivi {2; 3; 4; 5}. Pange tähele, et kuna see kasutab kaudset, muudab see valemi lenduvaks.
- VLOOKUP (H3, B4: F13, ROW (KAUDNE (“2:” & COUNTA ($ B $ 4: $ F $ 4))), FALSE) - Siin on parim osa. Kui need kokku panna, muutub see VLOOKUP (H3, B4: F13, {2; 3; 4; 5}, FALSE). Pange nüüd tähele, et ühe veerunumbri asemel olen andnud sellele veerunumbrite massiivi. Ja VLOOKUP otsib kuulekalt kõigi nende veergude väärtusi ja tagastab massiivi.
- Nüüd kasutage suurima väärtuse leidmiseks funktsiooni LARGE.
Ärge unustage selle valemi kasutamiseks kasutada klahve Control + Shift + Enter.
Pro näpunäide. Kui kasutate VLOOKUP -is ühe veerunumbri asemel veerunumbrite massiivi, tagastab see otsinguväärtuste massiivi.
