Excel VLOOKUP -opastus aloittelijoille

Mikä on VLOOKUP?

VLOOKUP (V tarkoittaa Vertical) on Excelin sisäänrakennettu funktio, joka määrittää laskentataulukon sarakkeiden välisen suhteen. Sen avulla voit etsiä arvon yhdestä sarakkeesta ja palauttaa vastaavan arvon saman rivin toisesta sarakkeesta.

VLOOKUP-syntaksi ja argumentit

Ennen PHAKU-funktion käyttämistä on hyödyllistä ymmärtää kaavan rakenne. Funktio ottaa vastaan ​​neljä argumenttia ja noudattaa yhdenmukaista kaavaa kaikissa Excel-versioissa.

• hakuarvo — etsittävä arvo (soluviittaus tai literaali).
• pöytäryhmä — solualue, joka sisältää haku- ja palautussarakkeen.
• col_index_num — table_array-taulukon sarakkeen numero, josta arvo palautetaan (1 on vasemmanpuoleisin).
• range_lookup — EPÄTOSI täsmälleen oikealle, TOSI (tai jätetty pois) likimääräiselle oikealle lajitelluissa tiedoissa.

Tärkeää: Hakuarvon on oltava table_array-taulukon vasemmanpuoleisimmassa sarakkeessa, ja VLOOKUP etsii vain vasemmalta oikealle.

VLOOKUPin käyttö

Kun sinun on löydettävä tiettyjä tietoja suuresta laskentataulukosta tai haettava samanlaista arvoa toistuvasti, PHAKU säästää merkittävästi aikaa manuaaliseen suodattamiseen verrattuna.

Harkitse a Yrityksen palkkataulukko taloustiimin ylläpitämä. Aloitat tunnetusta tiedosta – indeksistä – ja noudat tuntemattoman arvon PHAKU-funktiolla.

Esimerkiksi tiedät jo työntekijän nimen:

Ja haluat tarkistaa työntekijän palkan:

Excel-taulukko yllä olevaa esimerkkiä varten:

Lataa yllä oleva Excel-tiedosto

Tuntemattoman työntekijän palkan löytämiseksi syötämme työntekijän Code joka on jo saatavilla.

Käyttämällä PHAKU-funktiota saadaan kyseistä työntekijää vastaava palkka-arvo. Code näkyy automaattisesti.

Kuinka käyttää VLOOKUP-toimintoa Excelissä

Käytä VLOOKUP-funktiota Excelissä seuraamalla tätä vaiheittaista opasta:

Vaihe 1) Siirry kohdesoluun

Napsauta solua, johon haluat valitun työntekijän palkan näkyvän – tässä esimerkissä solua H3.

Vaihe 2) Syötä PHAKU-funktio =PHAKU()

Kirjoita funktio soluun. Aloita yhtäsuuruusmerkillä (joka kertoo Excelille, että seuraava kaava on kaava) ja lisää sitten VLOOKUP-avainsana: =VHAKU().

Sulkeissa on joukon argumentteja (funktion tarvitsemat tiedot).

PHAKU vaatii neljä argumenttia:

Vaihe 3) Ensimmäinen argumentti — hakuarvo

Ensimmäinen argumentti on etsittävän arvon soluviittaus. Tässä tapauksessa Työntekijä Code on hakuarvo, joten ensimmäinen argumentti on H2 – solu, jonka sisällön Excelin tulisi vastata.

Vaihe 4) Toinen argumentti — taulukkomatriisi

Tämä viittaa haettavaan arvolohkoon, joka tunnetaan Excelissä nimellä pöytäryhmä tai hakutaulukko. Esimerkissämme hakutaulukko toimii B2:sta E25:een.

HUOMAUTUS: Hakusarakkeen on oltava taulukkomatriisin vasemmanpuoleisin sarake.

Vaihe 5) Kolmas argumentti — sarakkeen_indeksi

Tämä kertoo VLOOKUP-funktiolle, missä taulukon matriisin sarakkeessa paluuarvo on. Työntekijän palkka on neljännessä sarakkeessa, joten sarakeindeksi on 4.

Vaihe 6) Neljäs argumentti — tarkka tai likimääräinen vastaavuus

Viimeinen argumentti on aluehaun lippu. Se määrittää, palauttaako VLOOKUP tarkan vai likimääräisen vastineen. Tässä tapauksessa haluamme tarkan vastineen (FALSE).

1. VÄÄRÄ – täsmälleen sama osuma.
2. TOSI — likimääräinen vastaavuus.

Vaihe 7) Paina Enter

Paina Enter-näppäintä kaavan loppuun saattamiseksi. Näet aluksi virheen, koska työntekijää ei ole. Code ei ole vielä merkitty H2:een.

Kun olet syöttänyt kelvollisen työntekijän Code Solussa H2 palautetaan vastaava työntekijän palkka.

Lyhyesti sanottuna kaava kertoo Excelille, että tunnetut arvot sijaitsevat datan vasemmanpuoleisimmassa sarakkeessa (Työntekijä Code). PHAKU-funktio skannaa sitten taulukon ja palauttaa vastaavan rivin neljännen sarakkeen arvon – työntekijän palkan.

Tämä esimerkki käsitteli tarkkoja osumia (avainsana FALSE). Seuraavassa osiossa selitetään likimääräiset osumat.

VLOOKUP likimääräisille osumille (TOSI avainsana viimeisenä parametrina)

Tarkastellaan tilannetta, jossa taulukko laskee alennuksia asiakkaille, jotka eivät osta täsmälleen kymmeniä tai satoja tuotteita.

Kuten alla on esitetty, yritys soveltaa alennuksia määriin 1–10 000:

Lataa yllä oleva Excel-tiedosto

Asiakas ostaa harvoin täsmälleen 100 tai 1 000 yksikköä. Likimääräisen vastaavuuden tilassa PHAKU-funktio löytää lähimmän alemman arvon sen sijaan, että vaadittaisiin tarkkaa lukua. Vaiheet:

Vaihe 1) Napsauta solua, johon VLOOKUP-funktio tulee – soluviittaus I2.

Vaihe 2) Kirjoita soluun =PHAKU() ja lisää sulkeiden sisällä olevat argumentit.

Vaihe 3) Argumentti 1: Syötä soluviittaus, jonka arvoa tulisi verrata hakutaulukkoon.

Vaihe 4) Argumentti 2: Valitse hakutaulukko – tässä tapauksessa Määrä- ja Alennus-sarakkeet.

Vaihe 5) Argumentti 3: Syötä hakutaulukon sarakeindeksi, josta vastaava arvo palautetaan.

Vaihe 6) Argumentti 4: Aseta viimeiseksi argumentiksi TOSI likimääräisiä osumia varten.

Vaihe 7) Paina Enter-näppäintä. Kaava koskee nyt solua. Kun kirjoitat minkä tahansa määrän, Excel palauttaa alennuskaistan likimääräisen vastaavuuden perusteella.

HUOMAUTUS: Jos jätät neljännen argumentin tyhjäksi, Excelin oletusarvo on TOSI (likimääräinen vastine). Likimääräisten vastineiden tapauksessa hakusarake on lajiteltava nousevaan järjestykseen.

Vlookup-toimintoa käytetään 2 eri arkin välillä, jotka on sijoitettu samaan työkirjaan

Tarkastellaan nyt työkirjaa, jossa on kaksi taulukkoa. Taulukossa 1 luetellaan työntekijä Code, Nimi ja tehtävänimike; Arkilla 2 luetellaan työntekijä Code ja työntekijän palkka.

ARKKI 1:

ARKKI 2:

Lataa yllä oleva Excel-tiedosto

Tavoitteena on yhdistää kaikki tiedot taulukolle 1 alla olevan mukaisesti:

VLOOKUP voi koota tietoja, jotta työntekijä voi Code, Nimi ja Palkka näkyvät yhdessä samalla arkilla.

Aloitamme taulukosta 2, koska se tarjoaa kaksi argumenttia – työntekijän palkka -sarake on tässä ja sarakeindeksi on 2.

Haluamme löytää jokaiselle työntekijälle sopivan palkan. Code.

Data kulkee solusta A2 soluun B25 – eli taulukkomatriisiimme.

Vaihe 1) Siirry taulukkoon 1 ja kirjoita näytetyt otsikot.

Vaihe 2) Napsauta Työntekijän palkka -kohdan vieressä olevaa solua – solua F3 – johon PHAKU-kaava tulee.

Syötä PHAKU-funktio: =PHAKU().

Vaihe 3) Argumentti 1: Syötä F2 – solu, joka sisältää työntekijän tiedot. Code vastaamaan hakutaulukossa.

Vaihe 4) Argumentti 2: Hakutaulukko sijaitsee toisella taulukolla, joten viittaa siihen taulukon nimellä: Taul2!A2:B25.

Vaihe 5) Argumentti 3: Syötä hakutaulukon sarakeindeksi, joka sisältää paluuarvon.

Vaihe 6) Argumentti 4: Käytä FALSE-arvoa täsmälleen oikealle osumalle, koska haluamme tarkalleen kutakin työntekijää vastaavan palkan. Code.

Vaihe 7) Paina Enter-näppäintä. Kun syötät työntekijän Code, solu palauttaa vastaavan palkan, joka on poimittu taulukosta 2.

Yleisiä PHAKU-virheitä ja korjauksia

Kokeneetkin käyttäjät kohtaavat VLOOKUP-virheitä. Yleisimmät virheet ja niiden nopeat korjaukset:

• # N / A — PHAKU ei löydä hakuarvoa. Tarkista, onko taulukossa ylimääräisiä välilyöntejä, yhteensopimattomia tietotyyppejä (tekstinä tallennettuja numeroita) tai että arvo todella on taulukko-taulukon ensimmäisessä sarakkeessa.
• #VIITE! — sarakkeen_indeksin_num on suurempi kuin taulukkotaulukon sarakkeiden lukumäärä. Pienennä sarakeindeksiä tai laajenna aluetta.
• #ARVO! — sarakkeen_indeksin_num on pienempi kuin 1 tai argumentti on virheellinen. Tarkista kaavan syntaksi.
• Väärä tulos palautettu — neljäs argumentti on TOSI tai jätetty pois, mutta hakusarake on lajittelematon. Vaihda arvoon EPÄTOSI tai lajittele sarake nousevaan järjestykseen.
• Lukitut viitteet — Kun kopioit kaavaa alaspäin, käytä absoluuttisia viittauksia (esimerkiksi $B$2:$E$25), jotta table_array ei siirry pois.

VLOOKUP vs. XLOOKUP: Kumpaa kannattaa käyttää?

Microsoft esitteli XLOOKUPin vuonna Microsoft 365 ja Excel 2021 VLOOKUPin nykyaikaisena korvaajana. Se poistaa useita VLOOKUP-rajoituksia ja on nyt suositeltu valinta tuetuissa versioissa.

Ominaisuus	VLOOKUP	XHAKU
Hakusuunta	Vain vasemmalta oikealle	Mikä tahansa suunta (vasen, oikea, ylös, alas)
Oletushakutyyppi	Arvioitu (TOSI)	Tarkka
Jos ei löydy -käsittely	Palauttaa #N/A	Sisäänrakennettu if_not_found-argumentti
Sarakeindeksi	Kovakoodattu numero	Viittaa paluusarakkeen alueeseen
Saatavuus:	Kaikki Excel-versiot	Microsoft 365, Excel 2021, Excelin verkkoversio

Milloin valita PHAKU: Työkirjan on oltava suoritettavissa Excel 2019:ssä tai sitä vanhemmassa versiossa, tai säilytät vanhoja kaavoja. Milloin valita XLOOKUP: olet luomassa uusia työkirjoja modernissa Excelissä ja haluat vasemmanpuoleisia hakuja, selkeämmän virheenkäsittelyn ja tarkan vastineen oletusarvoisesti. Lue lisää hakufunktioista kohdasta Excel-opetusohjelmat sarja.

Yhteenveto

Yllä olevat kolme skenaariota selittävät, miten PHAKU toimii tarkkojen osumien, likimääräisten osumien ja ristiintaulukoitujen viittausten kohdalla. Harjoittele omilla tietojoukoillasi parantaaksesi sujuvuutta. PHAKU on edelleen tärkeä ominaisuus MS-Excel tietojen tehokkaaseen hallintaan, ja XLOOKUP laajentaa tätä työkalupakkia modernissa Excelissä.

UKK