Jos olet lukenut Excel-vinkkejä jonkin aikaa, olet aina löytänyt jonkun, joka puhuu Excel INDEX () - ja MATCH () -funktioiden käytöstä Excel VLOOKUP -toiminnon sijaan. Itse puhuen oli aina liian vaikeaa yrittää hallita KAKSI uutta toimintoa samanaikaisesti. Mutta se on hieno temppu. Anna minulle viisi minuuttia, ja yritän selittää sen yksinkertaisella englannilla.
30 sekunnin arvostelu VLOOKUPista
Oletetaan, että sinulla on taulukko työntekijöiden tietueista. Ensimmäinen sarake on työntekijän numero, ja loput sarakkeet ovat erilaisia tietoja työntekijästä. Aina kun laskentataulukossa on työntekijän numero, voit palauttaa tietyn työntekijää koskevan datan VLOOKUPin avulla. Syntaksi on VLOOKUP (arvo, tietoalue, sarakkeen nro, EPÄTOSI). Se sanoo Excelille: "Siirry tietovälille. Etsi rivi, jolla on arvo (arvo) data-alueen ensimmäisestä sarakkeesta. Palauta rivin (sarakkeen nro) kolmas arvo. Kun olet saanut sen jumiin, se on hyvin yksinkertainen ja tehokas.
Ongelma
Eräänä päivänä sinulla on tilanne, jossa sinulla on työntekijän nimi, mutta tarvitset työntekijän numeron. Seuraavassa kuvassa sinulla on nimi A10: ssä ja sinun on löydettävä työntekijän numero kohdasta B10.
Kun avainkenttä on noudettavien tietojen oikealla puolella, VLOOKUP ei toimi. Jos vain VLOOKUP hyväksyisi sarakkeen numeroksi -1, ei olisi mitään ongelmaa. Mutta se ei ole. Yksi yleinen ratkaisu on lisätä uusi sarake A väliaikaisesti, kopioida nimisarake uuteen sarakkeeseen A, täyttää VLOOKUP, Paste Special Values ja poistaa sitten väliaikainen sarake A. Excel-ammattilaiset voivat todennäköisesti tehdä tämän siirron unessa.
Ehdotan, että otat haasteen ja yrität käyttää tätä yksivaiheista menetelmää. Kyllä, joudut kiinnittämään kaavan seinällesi muutaman viikon ajan, mutta teit sen myös VLOOKUPin kanssa jo kauan sitten, eikö niin?
Mielestäni syy tähän on niin vaikeaa, että käytät kahta toimintoa, joita et todennäköisesti ole koskaan ennen käyttänyt. Joten anna minun jakaa se kahteen osaan.
Ensinnäkin on INDEX () -toiminto. Tämä on kauheasti nimetty toiminto. Kun joku sanoo "indeksi", se ei luo mielestäni mitään samanlaista kuin tämä toiminto. Hakemisto vaatii kolme argumenttia.
=INDEX(data range, row number, column number)
Englanniksi Excel siirtyy tietoalueelle ja palauttaa sinulle arvon (rivin numero) kolmannen ja (sarakkeen numero) kolmannen sarakkeen risteyksessä. Hei, ajattele sitä - tämä on melko yksinkertaista, eikö? =INDEX($A$2:$C$6,4,2)antaa sinulle arvon B5.
Hakeminen INDEX () ongelmaamme, voit selvittää, että palata työntekijä numero väliltä, voit käyttää tätä: =INDEX($A$2:$A$6,?,1). Itse asiassa tämä pala näyttää niin triviaalilta, että se tuntuu hyödyttömältä. Mutta kun korvaat kysymysmerkin MATCH () -toiminnolla, sinulla on ratkaisu.
Tässä on syntaksi:
=MATCH(Value, Single-column data range, FALSE)
Se kertoo Excelille: "Hae data-alueelta ja kerro minulle suhteellinen rivinumero, josta löydät vastaavuuden (data). Joten etsit, mitä riviä työntekijä on A10: ssä =MATCH(A10,$B$2:$B$6,FALSE). Kyllä, tämä on monimutkaisempi kuin hakemisto , mutta sen pitäisi olla VLOOKUP-ammattilaisten kujalla. Jos A10 sisältää "Miller, Bob", tämä MATCH palauttaa, että hän on alueen B2: B6 kolmannella rivillä.
Siinä se on - MATCH () -toiminto kertoo hakemistofunktiolle, minkä rivin haluat katsoa - olet valmis. Ota Hakemisto-toiminto, korvaa kysymysmerkkimme MATCH-toiminnolla, ja voit nyt suorittaa VLOOKUP-vastaavan, kun avainkenttä ei ole vasemmassa sarakkeessa. Tässä on käytettävä toiminto:
=INDEX($A$2:$A$6,MATCH(A10,$B$2:$B$6,FALSE),1)
Seinällä oleva tarralapu osoittaa sen itse asiassa kahtena rivinä. Ensin kirjoitin selityksen MATCH (): lle. Sen alla kirjoitin selityksen hakemistolle (). Piirsin sitten suppilon muodon näiden kahden välille osoittamaan, että MATCH () -funktio putoaa INDEX () -funktion toiseen argumenttiin.
Muutaman ensimmäisen kerran, kun minun piti tehdä yksi näistä, minulla oli kiusaus vain lyödä uusi väliaikainen sarake A siellä, mutta kokin tuskan tekemällä sen tällä tavalla. Se on nopeampi ja vaatii vähemmän manipulointia. Joten seuraavalla kerralla, kun haluat lisätä negatiivisen luvun VLOOKUP-toimintoon, kokeile tätä outoa INDEX- ja MATCH-yhdistelmää ongelmiesi ratkaisemiseksi.
