Dacă ați citit sfaturi Excel de o vreme, ați găsit invariabil pe cineva care vorbește despre utilizarea funcțiilor Excel INDEX () și MATCH () în loc de Excel VLOOKUP. Vorbind pentru mine, a fost întotdeauna prea greu să încerc să stăpânesc DOUĂ funcții noi simultan. Dar, este un truc grozav. Dă-mi cinci minute și voi încerca să-l explic în engleză simplă.
Cea de-a 30-a recenzie a VLOOKUP
Spuneți că aveți un tabel cu evidența angajaților. Prima coloană este un număr de angajat, iar coloanele rămase sunt diverse date despre angajat. De fiecare dată când aveți un număr de angajat în foaia de lucru, puteți utiliza VLOOKUP pentru a returna o anumită dată despre angajat. Sintaxa este VLOOKUP (valoare, interval de date, nr. Col., FALS). Îi spune lui Excel: „Mergeți la intervalul de date. Găsiți un rând care are (valoare) în prima coloană a intervalului de date. Întoarceți valoarea (col.) A treia din acel rând. este foarte simplu și puternic.
Problema
Într-o zi, aveți o situație în care aveți numele angajatului, dar aveți nevoie de numărul angajatului. În imaginea următoare, aveți un nume în A10 și trebuie să găsiți numărul angajatului în B10.
Când câmpul cheie este în dreapta datelor pe care doriți să le extrageți, VLOOKUP nu va funcționa. Dacă numai VLOOKUP ar accepta -1 ca număr de coloană, nu ar exista nicio problemă. Dar nu. O soluție obișnuită este să introduceți temporar o nouă coloană A, să copiați coloana de nume în noua coloană A, să completați cu VLOOKUP, Lipiți valori speciale, apoi să ștergeți coloana temporară A. Profesioniștii din Excel pot face această mișcare probabil în somn.
Vă voi sugera să luați provocarea și să încercați să utilizați această metodă cu un singur pas. Da, va trebui să puneți formula pe perete pentru câteva săptămâni, dar ați făcut asta și cu VLOOKUP, cu mult timp în urmă, nu-i așa?
Cred că motivul pentru care acest lucru este atât de dificil este că folosiți două funcții pe care probabil nu le-ați folosit până acum. Deci, lasă-mă să-l descompun în două bucăți.
În primul rând, există funcția INDEX (). Aceasta este o funcție denumită îngrozitor. Când cineva spune „index”, nu evocă nimic în mintea mea care să fie similar cu ceea ce face această funcție. Index necesită trei argumente.
=INDEX(data range, row number, column number)
În limba engleză, Excel merge la intervalul de date și vă returnează valoarea în intersecția dintre (numărul rândului) și rândul (numărul coloanei) a coloanei. Hei, gândește-te la asta - este destul de simplu, nu? =INDEX($A$2:$C$6,4,2)vă va oferi valoarea în B5.
Aplicarea INDEX () la problema noastră, vă puteți da seama că , pentru a returna numărul angajaților din gama, ar trebui să utilizați acest lucru: =INDEX($A$2:$A$6,?,1). De fapt, această bucată pare atât de banală încât pare inutilă. Dar, când înlocuiți semnul întrebării cu o funcție MATCH (), aveți soluția.
Iată sintaxa:
=MATCH(Value, Single-column data range, FALSE)
Îi spune lui Excel: „Căutați în intervalul de date și spuneți-mi numărul de rând relativ în care găsiți o potrivire pentru (date). Deci, pentru a găsi ce rând are angajatul în A10, l-ați folosi =MATCH(A10,$B$2:$B$6,FALSE). Da, acest lucru este mai complex decât Indexul , dar ar trebui să fie chiar pe aleea profesioniștilor VLOOKUP. Dacă A10 conține „Miller, Bob”, atunci acest MATCH va întoarce că se află pe al treilea rând din gama B2: B6.
Iată - funcția MATCH () spune funcției Index în ce rând să se uite - ați terminat. Luați funcția Index, înlocuiți semnul de întrebare cu funcția MATCH și acum puteți face echivalentul VLOOKUP-urilor când câmpul cheie nu se află în coloana din stânga. Iată funcția de utilizat:
=INDEX($A$2:$A$6,MATCH(A10,$B$2:$B$6,FALSE),1)
Nota lipicioasă de pe peretele meu o arată de fapt ca două rânduri. Mai întâi am scris explicația pentru MATCH (). Mai jos am scris explicația pentru INDEX (). Apoi am desenat o formă de pâlnie între cele două pentru a indica faptul că funcția MATCH () intră în al doilea argument al funcției INDEX ().
Primele câteva ori când a trebuit să fac una dintre acestea, am fost tentat doar să trag o nouă coloană temporară A acolo, dar am trecut prin durerea de a face acest lucru în schimb. Este mai rapid și necesită mai puțină manipulare. Deci, data viitoare când doriți să puteți pune un număr negativ în funcția VLOOKUP, încercați această combinație ciudată de INDEX și MATCH pentru a vă rezolva problemele.
