Kui olete mõnda aega Exceli näpunäiteid lugenud, olete alati leidnud kellegi, kes räägib Exceli VLOOKUPi asemel funktsioonide Excel INDEX () ja MATCH () kasutamisest. Enda eest rääkides oli alati liiga raske proovida KAKS uut funktsiooni korraga. Aga see on lahe trikk. Andke mulle viis minutit ja ma püüan seda selgitada lihtsas inglise keeles.
VLOOKUPi 30 sekundiline ülevaade
Oletame, et teil on töötajate arvestuste tabel. Esimene veerg on töötaja number ja ülejäänud veerud on erinevad andmed töötaja kohta. Alati, kui töölehel on töötaja number, saate VLOOKUP-iga töötaja kohta konkreetse tugipunkti tagastada. Süntaks on VLOOKUP (väärtus, andmevahemik, veeru nr, FALSE). See ütleb Excelile: "Minge andmevahemikku. Leidke rida, millel on (väärtus) andmevahemiku esimesest veerust. Tagastage selle rea (veeru nr) th väärtus. Kui olete selle kinni pidanud, see on väga lihtne ja võimas.
Probleem
Ühel päeval on teil olukord, kus teil on töötaja nimi, kuid vajate töötaja numbrit. Järgmisel pildil on teil nimi A10-s ja peate leidma töötaja numbri B10-st.
Kui võtmeväli asub andmetest, mida soovite taastada, paremal, ei tööta VLOOKUP. Kui ainult VLOOKUP aktsepteerib veeru numbrina -1, pole probleemi. Kuid see pole nii. Üheks levinud lahenduseks on uue veeru A ajutine sisestamine, nimede veeru kopeerimine uude veergu A, asustamine VLOOKUP, Paste Special Values ja seejärel ajutise veeru A kustutamine. Exceli profid saavad seda sammu tõenäoliselt teha ka unes.
Soovitan teil väljakutse vastu võtta ja proovida seda üheastmelist meetodit kasutada. Jah, peate mõne nädala jooksul valemi oma seinale kleepima, kuid tegite seda ka VLOOKUPiga juba ammu, kas pole?
Ma arvan, et see on nii keeruline põhjus, et kasutate kahte funktsiooni, mida te pole kunagi varem kasutanud. Nii et lubage mul see kaheks tükiks jagada.
Esiteks on funktsioon INDEX (). See on jube nime saanud funktsioon. Kui keegi ütleb "indeks", ei tekita see minu meelest midagi sarnast selle funktsiooniga. Indeks nõuab kolme argumenti.
=INDEX(data range, row number, column number)
Inglise keeles läheb Excel andmevahemikku ja tagastab väärtuse (rea number) kolmanda ja (veeru number) kolmanda veeru ristumiskohas. Kuule, mõtle sellele - see on ju päris lihtne, eks? =INDEX($A$2:$C$6,4,2)annab väärtuse B5-s.
Rakendades meie probleemile INDEX (), võite arvata, et töötaja numbri vahemikust tagastamiseks kasutaksite järgmist =INDEX($A$2:$A$6,?,1). Tegelikult tundub see tükk sellest nii tühine, et tundub kasutu. Kuid kui asendate küsimärgi funktsiooniga MATCH (), on teil lahendus olemas.
Siin on süntaks:
=MATCH(Value, Single-column data range, FALSE)
See ütleb Excelile: "Otsige andmevahemikku ja öelge mulle suhteline rea number, kust leiate vaste (andmetele). Nii et kasutaksite, millise rea leidmiseks on töötaja A10-s =MATCH(A10,$B$2:$B$6,FALSE). Jah, see on keerulisem kui Index , kuid see peaks olema otse VLOOKUPi proffide alleel. Kui A10 sisaldab "Miller, Bob", tagastab see MATCH, et ta on vahemiku B2: B6 3. real.
Seal see on - funktsioon MATCH () ütleb funktsioonile Indeks, millisesse ritta vaadata - olete valmis. Võtke funktsioon Indeks, asendage meie küsimärk funktsiooniga MATCH ja saate nüüd teha samaväärse VLOOKUP-iga, kui võtmeväli pole vasakpoolses veerus. Siin on kasutatav funktsioon:
=INDEX($A$2:$A$6,MATCH(A10,$B$2:$B$6,FALSE),1)
Mu seina kleepuv märge näitab seda tegelikult kahe reana. Kõigepealt kirjutasin välja MATCH () selgituse. Selle alla kirjutasin INDEXi () selgituse. Seejärel joonistasin nende kahe vahele lehtrikuju, mis näitab, et funktsioon MATCH () langeb funktsiooni INDEX () teise argumendi juurde.
Esimesed paar korda, kui pidin ühte neist tegema, tekkis mul kiusatus lihtsalt uus ajutine veerg A sinna sisse lüüa, kuid mul oli selle asemel nii valus. See on kiirem ja nõuab vähem manipuleerimist. Nii et järgmine kord, kui soovite, et saaksite funktsiooni VLOOKUP lisada negatiivse numbri, proovige oma probleemide lahendamiseks seda kummalist INDEXi ja MATCHi kombinatsiooni.
