Lataa esimerkkityökirja
Tämä opetusohjelma osoittaa, miten Excel VLOOKUP -toiminto Excelissä arvon etsimiseksi.
VLOOKUP -toiminnon yleiskatsaus
VLOOKUP -toiminto Vlookup tarkoittaa pystysuoraa hakua. Se etsii arvon taulukon vasemmasta sarakkeesta. Palauttaa sitten arvon määritetyn määrän sarakkeita oikealle löydetystä arvosta. Se on sama kuin hlookup, paitsi että se etsii arvoja pystysuunnassa vaakasuoran sijasta.
(Huomaa, miten kaavasyötteet näkyvät)
VLOOKUP -toiminnon syntaksi ja argumentit:
| 1 | = VLOOKUP (lookup_value, table_array, col_index_num, range_lookup) |
haku_arvo - Arvo, jota haluat hakea.
pöytäryhmä - Tietoalue, joka sisältää sekä haettavan arvon että arvon, jonka haluat vlookupin palauttavan. Hakuarvojen sisältävän sarakkeen on oltava vasemmanpuoleisin sarake.
col_index_num - sen tietoalueen sarakenumero, josta haluat palauttaa arvon.
range_lookup - Totta vai tarua. FALSE etsii tarkkaa vastaavuutta. TRUE etsii lähimmän osuman, joka on pienempi tai yhtä suuri kuin lookup_value. Jos TOSI valitaan, vasemmanpuoleisin sarake (hakusarake) on lajiteltava nousevasti (pienimmästä korkeimpaan).
Mikä on VLOOKUP -toiminto?
VLOOKUP -toiminto on yksi laskentataulukkojen maailman vanhimmista toiminnoista Vertinen Hakutulokset. Siinä on muutamia rajoituksia, jotka usein voitetaan muilla toiminnoilla, kuten INDEX/MATCH. Sen etuna on kuitenkin se, että se on yksi toiminto, joka voi tehdä tarkan haun haun itsestään. Se on myös yksi ensimmäisistä toiminnoista, joista ihmiset oppivat.
Perusesimerkki
Katsotaanpa otos arvosanakirjan tiedoista. Käsittelemme useita esimerkkejä tietojen keräämisestä tietyille opiskelijoille.
Jos haluamme löytää Bobin luokan, kirjoitamme kaavan:
| 1 | = VLOOKUP ("Bob", A2: C5, 2, FALSE) |
Tärkeää muistaa, että etsimämme kohteen (Bob) on oltava hakualueemme ensimmäisessä sarakkeessa (A2: C5). Olemme kertoneet toiminnolle, että haluamme palauttaa arvon 2: statoinen sarake, joka tässä tapauksessa on sarake B. Lopuksi ilmoitimme haluavamme tehdä tarkka ottelu asettamalla False viimeiseksi argumentiksi. Tässä vastaus on "lukeminen".
Sivuvinkki: Voit myös käyttää lopullisena argumenttina numeroa 0 epätosi sijaan, koska niillä on sama arvo. Jotkut pitävät tästä enemmän, koska kirjoittaminen on nopeampaa. Tiedä vain, että molemmat ovat hyväksyttäviä.
Siirretyt tiedot
Jotta voimme lisätä selvennystä ensimmäiseen esimerkkiimme, hakukohteen ei tarvitse olla laskentataulukon sarakkeessa A, vaan vain hakualueen ensimmäisessä sarakkeessa. Käytämme samaa tietojoukkoa:
Etsitään nyt luonnontieteiden luokan arvosana. Meidän kaava olisi
| 1 | = VLOOKUP ("Tiede", B2: C5, 2, EPÄTOSI) |
Tämä on edelleen pätevä kaava, koska hakualueemme ensimmäinen sarake on sarake B, josta hakutermimme "Tiede" löytyy. Palautamme arvon 2: statoinen hakualueen sarakkeeseen, joka tässä tapauksessa on sarake C. Vastaus on sitten "A-".
Yleismerkkien käyttö
VLOOKUP -toiminto tukee yleismerkkien "*" ja "?" Käyttöä hakuja tehdessä. Oletetaan esimerkiksi, että olimme unohtaneet Frankin nimen kirjoittamisen ja halusimme vain etsiä nimen, joka alkaa F -kirjaimella. Voisimme kirjoittaa kaavan
| 1 | = VLOOKUP ("F*", A2: C5, 2, FALSE) |
Tämä voisi löytää nimen Frank riviltä 5 ja palauttaa sitten arvon 2toinen suhteellinen sarake. Tässä tapauksessa vastaus on "Tiede".
Ei täsmällinen vastaavuus
Useimmiten haluat varmistaa, että VLOOKUPin viimeinen argumentti on epätosi (tai 0), jotta saat täsmällisen osuman. Joskus saatat kuitenkin etsiä ei-tarkkaa vastaavuutta. Jos sinulla on luettelo lajitelluista tiedoista, voit myös palauttaa VLOOKUP -toiminnolla saman tai seuraavan pienimmän kohteen tuloksen. Tätä käytetään usein käsiteltäessä kasvavia numeroalueita, kuten verotaulukossa tai provisioissa.
Oletetaan, että haluat löytää veroprosentin tulosta soluun D2. D4: n kaava voi olla:
| 1 | = HAKU (D2, A2: B6, 2, TRUE) |
Ero tässä kaavassa on, että viimeinen argumenttimme on "Tosi". Eräässä esimerkissämme voimme nähdä, että kun yksilömme syöttää 45 000 dollarin tulon, heidän verokantansa on 15%.
Huomautus: Vaikka yleensä haluamme tarkan vastaavuuden, jonka argumentti on False, unohdat määrittää 4th VLOOKUP -argumentissa, oletus on True. Tämä voi aiheuttaa odottamattomia tuloksia, varsinkin kun käsitellään tekstiarvoja.
Dynaaminen sarake
VLOOKUP edellyttää, että annat argumentin, jossa kerrotaan, mistä sarakkeesta haluat palauttaa arvon, mutta tilaisuus voi ilmetä, kun et tiedä, missä sarake on, tai haluat antaa käyttäjän muuttaa paluuta. Näissä tapauksissa voi olla hyödyllistä käyttää MATCH -funktiota sarakkeen numeron määrittämiseen.
Ajatellaanpa uudelleen arvosanaesimerkkiämme, jossa on joitakin syötteitä E2: ssa ja E4: ssä. Saadaksemme sarakkeen numeron voisimme kirjoittaa kaavan
| 1 | = MATCH (E2, A1: C1, 0) |
Tämä yrittää löytää tarkan "luokan" sijainnin alueella A1: C1. Vastaus on 3. Tämän tietäessä voimme liittää sen VLOOKUP -funktioon ja kirjoittaa kaavan E6: een seuraavasti:
| 1 | = HAKU (E4, A2: C5, MATCH (E2, A1: C1, 0), 0) |
Joten MATCH -toiminto arvioi arvoksi 3, ja se kertoo VLOOKUPille, että se palauttaa tuloksen kolmestard sarake A2: C5 -alueella. Kaiken kaikkiaan saamme sitten halutun tuloksen "C". Kaavamme on nyt dynaaminen, koska voimme muuttaa joko tarkasteltavaa saraketta tai etsittävää nimeä.
VLOOKUP -rajoitukset
Kuten artikkelin alussa mainittiin, VLOOKUPin suurin romahdus on se, että se vaatii hakutermin löytyvän hakualueen suurimmasta vasemmasta sarakkeesta. Vaikka voit tehdä joitakin hienoja temppuja voittaaksesi tämän, yleinen vaihtoehto on käyttää INDEX ja MATCH. Tämä yhdistelmä antaa sinulle enemmän joustavuutta, ja se voi joskus olla jopa nopeampi laskenta.
VLOOKUP Google Sheetsissä
VLOOKUP -toiminto toimii täsmälleen samalla tavalla Google Sheetsissä kuin Excelissä:
Lisämerkinnät
Käytä VLOOKUP -toimintoa pystysuoran haun suorittamiseen. VLOOKUP etsii tarkan vastaavuuden (range_lookup = FALSE) tai lähin vastaavuus, joka on yhtä suuri tai pienempi kuin lookup_value (range_lookup = TOSI, vain numeeriset arvot) taulukon_taulukon ensimmäisellä rivillä. Se palauttaa vastaavan arvon, n rivien määrän osuman alapuolella.
Kun etsit tarkkaa hakua VLOOKUPilla, määritä ensin tunnistusarvo, jota haluat etsiä haku_arvo. Tämä tunnistearvo voi olla SSN, työntekijän tunnus, nimi tai jokin muu yksilöllinen tunniste.
Seuraavaksi määrität alueen (nimeltään pöytäryhmä), joka sisältää ylärivin tunnisteet ja kaikki arvot, joita haluat lopulta etsiä sen alapuolella olevilta riveiltä. TÄRKEÄÄ: Ainutlaatuisten tunnisteiden on oltava ylärivillä. Jos ne eivät ole, sinun on joko siirrettävä rivi yläreunaan tai käytettävä MATCH / INDEX -painiketta VLOOKUPin sijaan.
Määritä kolmanneksi rivin numero (rivi_indeksi) pöytäryhmä että haluat palata. Muista, että ensimmäinen rivi, joka sisältää yksilölliset tunnisteet, on rivi 1. Toinen rivi on rivi 2 jne.
Lopuksi sinun on ilmoitettava, etsitkö täsmällistä hakua (EPÄTOSI) vai lähintä (TOSI) range_lookup. Jos tarkka hakuvaihtoehto on valittu eikä tarkkaa vastaavuutta löydy, virhe palautetaan (#N/A). Jos haluat, että kaava palauttaa tyhjän tai "ei löydy" tai minkä tahansa muun arvon virhearvon (#N/A) sijaan, käytä IFERROR -funktiota VLOOKUP -toiminnon kanssa.
VLOOKUP -toiminnon käyttäminen likimääräisen ottelusarjan palauttamiseen: range_lookup = TOSI. Tämä vaihtoehto on käytettävissä vain numeerisille arvoille. Arvot on lajiteltava nousevaan järjestykseen.
Voit tehdä vaakasuuntaisen haun käyttämällä HLOOKUP -toimintoa.
VLOOKUP Esimerkkejä VBA: sta
Voit myös käyttää VLOOKUP -toimintoa VBA: ssa. Tyyppi:application.worksheetfunction.vlookup (lookup_value, table_array, col_index_num, range_lookup)
Funktioargumenteille (haun_arvo jne.) Voit joko syöttää ne suoraan funktioon tai määrittää sen sijaan käytettäviä muuttujia.
Palaa Excelin kaikkien toimintojen luetteloon
