VLOOKUP este una dintre cele mai utilizate funcții din Excel. Se caută o valoare într-un interval și returnează o valoare corespunzătoare într-un număr de coloană specificat.
Acum am întâlnit o problemă în care trebuia să caut întregul rând și să returnez valorile din toate coloanele din acel rând (în loc să returnez o singură valoare).
Iată deci ce a trebuit să fac. În setul de date de mai jos, aveam nume de reprezentanți de vânzări și vânzările pe care le-au realizat în 4 trimestre în 2012. Am avut o listă cu numele lor și am vrut să extrag vânzările maxime pentru acel reprezentant de vânzări în aceste patru trimestre.
Aș putea veni cu 2 moduri diferite de a face acest lucru - Folosind INDEX sau VLOOKUP.
Căutați întregul rând / coloană folosind formula INDEX
Iată formula pe care am creat-o pentru a face acest lucru folosind Index
= LARGE (INDEX ($ B $ 4: $ F $ 13, MATCH (H3, $ B $ 4: $ B $ 13,0), 0), 1)
Cum functioneaza:
Să vedem mai întâi funcția INDEX care este înfășurată în funcția LARGE.
= INDEX ($ C $ 4: $ F $ 13, MATCH (H3, $ B $ 4: $ B $ 13,0), 0)
Să analizăm îndeaproape argumentele funcției INDEX:
- Matrice - $ B $ 4: $ F $ 1
- Număr rând - MATCH (H3, $ B $ 4: $ B $ 13,0)
- Număr coloană - 0
Observați că am folosit numărul coloanei ca 0.
Trucul aici este că atunci când utilizați numărul coloanei ca 0, acesta returnează toate valorile din toate coloanele. Deci, dacă îl selectez pe John în meniul derulant, formula indicelui va returna toate cele 4 valori de vânzare pentru John {91064,71690,67574,25427}.
Acum pot folosi funcția Large pentru a extrage cea mai mare valoare
Sfat Pro - Utilizați numărul Coloanei / Rândurilor ca 0 în formula Index pentru a returna toate valorile din Coloane / Rânduri.
Căutați întregul rând / coloană folosind formula VLOOKUP
În timp ce formula Index este îngrijită, curată și robustă, modul VLOOKUP este puțin complex. De asemenea, ajunge să facă funcția volatilă. Cu toate acestea, există un truc uimitor pe care l-aș împărtăși în această secțiune. Iată formula:
= LARGE (VLOOKUP (H3, B4: F13, ROW) (INDIRECT ("2:" & COUNTA ($ B $ 4: $ F $ 4))), FALSE), 1)
Cum functioneaza
- ROW (INDIRECT („2:” & COUNTA ($ B $ 4: $ F $ 4))) - Această formulă returnează o matrice {2; 3; 4; 5}. Rețineți că, deoarece folosește INDIRECT, aceasta face această formulă volatilă.
- VLOOKUP (H3, B4: F13, ROW (INDIRECT („2:” & COUNTA ($ B $ 4: $ F $ 4))), FALS) - Iată cea mai bună parte. Când le puneți împreună, devine VLOOKUP (H3, B4: F13, {2; 3; 4; 5}, FALSE). Acum observați că, în loc de un singur număr de coloană, i-am dat o serie de numere de coloane. Și VLOOKUP caută ascultător valori în toate aceste coloane și returnează o matrice.
- Acum, utilizați doar funcția LARGE pentru a extrage cea mai mare valoare.
Nu uitați să utilizați Control + Shift + Enter pentru a utiliza această formulă.
Sfat Pro - În VLOOKUP, în loc să utilizați un singur număr de coloană, dacă utilizați o matrice de numere de coloane, va returna o serie de valori de căutare.
