Sisällys
- VLOOKUP-toiminto Excelissä – yleinen kuvaus ja syntaksi
- VLOOKUP Esimerkkejä
- Tarkka tai likimääräinen vastaavuus VHAKU-funktiossa
- VLOOKUP Excelissä – sinun on muistettava tämä!
Tänään aloitamme artikkelisarjan, joka kuvaa yhtä Excelin hyödyllisimmistä ominaisuuksista VPR (VHAKU). Tämä toiminto on samalla yksi monimutkaisimmista ja vähiten ymmärretyistä.
Tässä opetusohjelmassa aiheesta VPR Yritän esittää perusasiat mahdollisimman yksinkertaisesti, jotta oppimisprosessi olisi mahdollisimman selkeä kokemattomille käyttäjille. Lisäksi tutkimme useita esimerkkejä Excel-kaavoilla, jotka havainnollistavat funktion yleisimmät käyttötapaukset VPR.
VLOOKUP-toiminto Excelissä – yleinen kuvaus ja syntaksi
Eli mikä se on VPR? No, ensinnäkin se on Excel-toiminto. Mitä hän tekee? Se etsii määrittämäsi arvon ja palauttaa vastaavan arvon toisesta sarakkeesta. Teknisesti ottaen VPR etsii arvon annetun alueen ensimmäisestä sarakkeesta ja palauttaa tuloksen toisesta saman rivin sarakkeesta.
Yleisimmässä sovelluksessa toiminto VPR etsii tietokannasta tiettyä yksilöllistä tunnistetta ja poimii tietokannasta joitakin siihen liittyviä tietoja.
Ensimmäinen kirjain funktion nimessä VPR (VLOOKUP) tarkoittaa Вpystysuora (Vpystysuora). Sen avulla voit erottaa VPR alkaen GPR (HLOOKUP), joka etsii arvoa alueen yläriviltä − Гvaakasuora (Hvaakatasossa).
Toiminto VPR saatavana Excel 2013:ssa, Excel 2010:ssä, Excel 2007:ssä, Excel 2003:ssa, Excel XP:ssä ja Excel 2000:ssa.
VLOOKUP-funktion syntaksi
Toiminto VPR (VLOOKUP) sisältää seuraavan syntaksin:
VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])
ВПР(искомое_значение;таблица;номер_столбца;[интервальный_просмотр])
Kuten näet, toiminto VPR Microsoft Excelissä on 4 vaihtoehtoa (tai argumenttia). Kolme ensimmäistä ovat pakollisia, viimeinen valinnainen.
- hakuarvo (lookup_value) – Etsittävä arvo. Tämä voi olla arvo (numero, päivämäärä, teksti) tai soluviittaus (joka sisältää hakuarvon) tai jonkin muun Excel-funktion palauttama arvo. Esimerkiksi tämä kaava etsii arvoa 40:
=VLOOKUP(40,A2:B15,2)=ВПР(40;A2:B15;2)
Jos hakuarvo on pienempi kuin etsittävän alueen ensimmäisen sarakkeen pienin arvo, funktio VPR ilmoittaa virheestä #AT (#N/A).
- pöytäryhmä (taulukko) – kaksi tai useampi tietosarake. Muista toiminto VPR etsii aina arvoa argumentissa annetun alueen ensimmäisestä sarakkeesta pöytäryhmä (pöytä). Näkyvä alue voi sisältää erilaisia tietoja, kuten tekstiä, päivämääriä, numeroita ja loogisia arvoja. Toiminto ei välitä kirjainkoosta, mikä tarkoittaa, että isoja ja pieniä kirjaimia pidetään samana. Joten kaavamme etsii arvoa 40 soluissa alkaen A2 että A15, koska A on argumentissa annetun alueen A2:B15 ensimmäinen sarake pöytäryhmä (pöytä):
=VLOOKUP(40,A2:B15,2)=ВПР(40;A2:B15;2)
- col_index_num (sarakkeen_numero) on sen sarakkeen numero annetulla alueella, josta löydetyn rivin arvo palautetaan. Annetun alueen vasemmanpuoleisin sarake on 1, toinen sarake on 2, kolmas sarake on 3 ja niin edelleen. Nyt voit lukea koko kaavan:
=VLOOKUP(40,A2:B15,2)=ВПР(40;A2:B15;2)Kaava etsii arvoa 40 alueella A2: A15 ja palauttaa vastaavan arvon sarakkeesta B (koska B on toinen sarake alueella A2:B15).
Jos argumentin arvo col_index_num (sarakkeen_numero) pienempi kuin 1sitten VPR ilmoittaa virheestä #ARVO! (#ARVO!). Ja jos se on enemmän kuin alueen sarakkeiden lukumäärä pöytäryhmä (taulukko), funktio palauttaa virheen #VIITE! (#LINKKI!).
- range_lookup (range_lookup) – määrittää, mitä etsiä:
- Tarkka vastaavuus, argumentin on oltava yhtä suuri VÄÄRÄ (VÄÄRÄ);
- likimääräinen vastaavuus, argumentti on yhtä suuri TOSI KOODI (TRUE) tai sitä ei ole määritelty ollenkaan.
Tämä parametri on valinnainen, mutta erittäin tärkeä. Myöhemmin tässä opetusohjelmassa VPR Näytän sinulle esimerkkejä, joissa selitetään, kuinka kirjoittaa kaavoja tarkan ja likimääräisen vastaavuuden löytämiseksi.
VLOOKUP Esimerkkejä
Toivottavasti toiminto VPR tulee sinulle hieman selvemmäksi. Katsotaanpa nyt joitain käyttötapauksia VPR kaavoissa todellisilla tiedoilla.
Kuinka käyttää VLOOKUP-toimintoa etsiäksesi toisesta Excel-taulukosta
Käytännössä kaavat funktiolla VPR käytetään harvoin tietojen etsimiseen samalta laskentataulukolta. Useimmiten etsit ja noutat vastaavat arvot toiselta arkilta.
Voidakseen käyttää VPR, etsi toisesta Microsoft Excel -taulukosta, sinun on argumentissa pöytäryhmä (taulukko) määritä arkin nimi huutomerkillä, jota seuraa solualue. Esimerkiksi seuraava kaava osoittaa, että alue A2: B15 on arkilla nimeltä Sheet2.
=VLOOKUP(40,Sheet2!A2:B15,2)
=ВПР(40;Sheet2!A2:B15;2)
Arkin nimeä ei tietenkään tarvitse syöttää manuaalisesti. Aloita vain kaavan kirjoittaminen ja kun se tulee argumenttiin pöytäryhmä (taulukko), vaihda haluamallesi arkille ja valitse haluamasi solualue hiirellä.
Alla olevassa kuvakaappauksessa näkyvä kaava etsii tekstiä "Tuote 1" sarakkeesta A (se on alueen A1:B2 ensimmäinen sarake) laskentataulukosta. Hinnat.
=VLOOKUP("Product 1",Prices!$A$2:$B$9,2,FALSE)
=ВПР("Product 1";Prices!$A$2:$B$9;2;ЛОЖЬ)
Muista, että kun etsit tekstiarvoa, sinun on laitettava se lainausmerkkeihin (“”), kuten yleensä Excel-kaavoissa tehdään.
Väitteeksi pöytäryhmä (taulukko) on suositeltavaa käyttää aina absoluuttisia viittauksia ($-merkillä). Tässä tapauksessa hakualue pysyy ennallaan, kun kaava kopioidaan muihin soluihin.
Hae toisesta työkirjasta VLOOKUPilla
Toimia VPR työskennellyt kahden Excel-työkirjan välillä, sinun on määritettävä työkirjan nimi hakasulkeissa ennen arkin nimeä.
Esimerkiksi alla on kaava, joka etsii arvoa 40 arkilla Sheet2 Kirjassa Numerot.xlsx:
=VLOOKUP(40,[Numbers.xlsx]Sheet2!A2:B15,2)
=ВПР(40;[Numbers.xlsx]Sheet2!A2:B15;2)
Tässä on helpoin tapa luoda kaava Excelissä VPRjoka linkittää toiseen työkirjaan:
- Avaa molemmat kirjat. Tätä ei vaadita, mutta kaava on helpompi luoda tällä tavalla. Etkö halua kirjoittaa työkirjan nimeä manuaalisesti? Lisäksi se suojaa sinua vahingossa tapahtuvilta kirjoitusvirheiltä.
- Aloita funktion kirjoittaminen VPRja kun tulee väittelyyn pöytäryhmä (taulukko), vaihda toiseen työkirjaan ja valitse siitä haluamasi hakualue.
Alla olevassa kuvakaappauksessa näkyy kaava, jossa haku on asetettu työkirjan alueelle Hintalista.xlsx arkilla Hinnat.
Toiminto VPR toimii myös silloin, kun suljet haetun työkirjan ja työkirjatiedoston koko polku tulee näkyviin kaavapalkkiin alla olevan kuvan mukaisesti:
Jos työkirjan tai taulukon nimi sisältää välilyöntejä, se on kirjoitettava heittomerkkeihin:
=VLOOKUP(40,'[Numbers.xlsx]Sheet2'!A2:B15,2)
=ВПР(40;'[Numbers.xlsx]Sheet2'!A2:B15;2)
Nimetyn alueen tai taulukon käyttäminen kaavoissa VLOOKUPilla
Jos aiot käyttää samaa hakualuetta useissa toiminnoissa VPR, voit luoda nimetyn alueen ja kirjoittaa sen nimen kaavaan argumenttina pöytäryhmä (pöytä).
Luodaksesi nimetyn alueen, valitse solut ja kirjoita sopiva nimi kenttään Etunimi, kaavapalkin vasemmalla puolella.
Nyt voit kirjoittaa muistiin seuraavan kaavan tuotteen hinnan löytämiseksi Tuote 1:
=VLOOKUP("Product 1",Products,2)
=ВПР("Product 1";Products;2)
Useimmat alueen nimet toimivat koko Excel-työkirjassa, joten argumentille ei tarvitse määrittää taulukon nimeä pöytäryhmä (taulukko), vaikka kaava ja hakualue olisivat eri laskentataulukoilla. Jos ne ovat eri työkirjoissa, ennen alueen nimeä sinun on määritettävä työkirjan nimi, esimerkiksi näin:
=VLOOKUP("Product 1",PriceList.xlsx!Products,2)
=ВПР("Product 1";PriceList.xlsx!Products;2)
Joten kaava näyttää paljon selkeämmältä, oletko samaa mieltä? Nimettyjen alueiden käyttäminen on myös hyvä vaihtoehto absoluuttisille viittauksille, koska nimetty alue ei muutu, kun kopioit kaavan muihin soluihin. Tämä tarkoittaa, että voit olla varma, että kaavan hakualue pysyy aina oikeana.
Jos muunnat solualueen täysimittaiseksi Excel-laskentataulukoksi komennolla Pöytä (Taulukko) -välilehti lisäys (Lisää), kun valitset alueen hiirellä, Microsoft Excel lisää automaattisesti sarakkeiden nimet (tai taulukon nimen, jos valitset koko taulukon) kaavaan.
Valmis kaava näyttää suunnilleen tältä:
=VLOOKUP("Product 1",Table46[[Product]:[Price]],2)
=ВПР("Product 1";Table46[[Product]:[Price]];2)
Tai ehkä jopa näin:
=VLOOKUP("Product 1",Table46,2)
=ВПР("Product 1";Table46;2)
Kun käytät nimettyjä alueita, linkit osoittavat samoihin soluihin riippumatta siitä, mihin kopioit funktion VPR työkirjan sisällä.
Jokerimerkkien käyttäminen VLOOKUP-kaavoissa
Kuten monet muutkin toiminnot, VPR Voit käyttää seuraavia jokerimerkkejä:
- Kysymysmerkki (?) – korvaa minkä tahansa yksittäisen merkin.
- Asterisk (*) – korvaa minkä tahansa merkkijonon.
Jokerimerkkien käyttö funktioissa VPR voi olla hyödyllistä monissa tapauksissa, esim.
- Kun et muista tarkalleen tekstiä, joka sinun on löydettävä.
- Kun haluat löytää sanan, joka on osa solun sisältöä. Tietää että VPR etsii koko solun sisällön perusteella, ikään kuin vaihtoehto olisi käytössä Yhdistä koko solun sisältö (Koko solu) tavallisessa Excel-haussa.
- Kun solu sisältää ylimääräisiä välilyöntejä sisällön alussa tai lopussa. Tällaisessa tilanteessa voit raahata aivojasi pitkään ja yrittää selvittää, miksi kaava ei toimi.
Esimerkki 1: Etsitään tekstiä, joka alkaa tai päättyy tietyillä merkeillä
Oletetaan, että haluat etsiä tiettyä asiakasta alla näkyvästä tietokannasta. Et muista hänen sukunimeään, mutta tiedät, että se alkaa "ack". Tässä on kaava, joka tekee työn hienosti:
=VLOOKUP("ack*",$A$2:$C$11,1,FALSE)
=ВПР("ack*";$A$2:$C$11;1;ЛОЖЬ)
Nyt kun olet varma, että olet löytänyt oikean nimen, voit käyttää samaa kaavaa löytääksesi tämän asiakkaan maksaman summan. Voit tehdä tämän muuttamalla funktion kolmatta argumenttia VPR haluttuun sarakkeen numeroon. Meidän tapauksessamme tämä on sarake C (3. alueella):
=VLOOKUP("ack*",$A$2:$C$11,3,FALSE)
=ВПР("ack*";$A$2:$C$11;3;ЛОЖЬ)
Tässä on lisää esimerkkejä jokerimerkeistä:
~ Etsi nimi, joka päättyy "mies":
=VLOOKUP("*man",$A$2:$C$11,1,FALSE)
=ВПР("*man";$A$2:$C$11;1;ЛОЖЬ)
~ Etsi nimi, joka alkaa sanalla "ad" ja päättyy "poika":
=VLOOKUP("ad*son",$A$2:$C$11,1,FALSE)
=ВПР("ad*son";$A$2:$C$11;1;ЛОЖЬ)
~ Löydämme etunimen luettelosta, joka koostuu 5 merkistä:
=VLOOKUP("?????",$A$2:$C$11,1,FALSE)
=ВПР("?????";$A$2:$C$11;1;ЛОЖЬ)
Toimia VPR jokerimerkit toimivat oikein, koska neljäntenä argumenttina sinun tulee aina käyttää VÄÄRÄ (VÄÄRÄ). Jos hakualue sisältää useamman kuin yhden arvon, joka vastaa hakutermejä jokerimerkeillä, ensimmäinen löydetty arvo palautetaan.
Esimerkki 2: Yhdistä jokerimerkit ja soluviittaukset VLOOKUP-kaavoissa
Katsotaanpa nyt hieman monimutkaisempaa esimerkkiä haun tekemisestä funktion avulla VPR arvon mukaan solussa. Kuvittele, että sarake A on luettelo lisenssiavaimista ja sarake B on luettelo nimistä, jotka omistavat lisenssin. Lisäksi sinulla on osa (useita merkkiä) jonkinlaisesta lisenssiavaimesta solussa C1 ja haluat löytää omistajan nimen.
Tämä voidaan tehdä käyttämällä seuraavaa kaavaa:
=VLOOKUP("*"&C1&"*",$A$2:$B$12,2,FALSE)
=ВПР("*"&C1&"*";$A$2:$B$12;2;FALSE)
Tämä kaava etsii arvon solusta C1 annetulta alueelta ja palauttaa vastaavan arvon sarakkeesta B. Huomaa, että ensimmäisessä argumentissa käytämme et-merkkiä (&) ennen ja jälkeen soluviittauksen linkittämään tekstimerkkijono.
Kuten alla olevasta kuvasta näet, toiminto VPR palauttaa "Jeremy Hill", koska hänen lisenssiavaimensa sisältää merkkijonon solusta C1.
Huomaa, että argumentti pöytäryhmä (taulukko) yllä olevassa kuvakaappauksessa sisältää taulukon nimen (Taulukko7) solualueen määrittämisen sijaan. Näin teimme edellisessä esimerkissä.
Tarkka tai likimääräinen vastaavuus VHAKU-funktiossa
Ja lopuksi tarkastellaan tarkemmin viimeistä funktiolle määritettyä argumenttia VPR - range_lookup (interval_view). Kuten oppitunnin alussa mainittiin, tämä argumentti on erittäin tärkeä. Voit saada täysin erilaisia tuloksia samalla kaavalla sen arvolla TOSI KOODI (TOSI) tai VÄÄRÄ (VÄÄRÄ).
Selvitetään ensin, mitä Microsoft Excel tarkoittaa tarkalla ja likimääräisellä vastaavuudella.
- Jos argumentti range_lookup (range_lookup) on yhtä suuri kuin VÄÄRÄ (EPÄTOSI), kaava etsii tarkkaa vastaavuutta, eli täsmälleen samaa arvoa kuin argumentissa on annettu hakuarvo (hakuarvo). Jos alueen t ensimmäisessä sarakkeessakykenevä_taulukko (taulukko) kohtaa kaksi tai useampia arvoja, jotka vastaavat argumenttia hakuarvo (hakuarvo), sitten valitaan ensimmäinen. Jos osumia ei löydy, toiminto ilmoittaa virheestä #AT (#N/A). Esimerkiksi seuraava kaava ilmoittaa virheen #AT (#N/A), jos arvoa ei ole alueella A2:A15 4:
=VLOOKUP(4,A2:B15,2,FALSE)=ВПР(4;A2:B15;2;ЛОЖЬ) - Jos argumentti range_lookup (range_lookup) on yhtä suuri kuin TOSI KOODI (TOSI), kaava etsii likimääräistä vastaavuutta. Tarkemmin sanottuna ensin toiminto VPR etsii tarkan vastaavuuden, ja jos sellaista ei löydy, valitsee likimääräisen. Likimääräinen vastaavuus on suurin arvo, joka ei ylitä argumentissa määritettyä arvoa. hakuarvo (hakuarvo).
Jos argumentti range_lookup (range_lookup) on yhtä suuri kuin TOSI KOODI (TRUE) tai ei määritetty, niin alueen ensimmäisen sarakkeen arvot tulee lajitella nousevaan järjestykseen, eli pienimmästä suurimpaan. Muuten toiminto VPR voi palauttaa virheellisen tuloksen.
Ymmärtääksesi paremmin valinnan tärkeyden TOSI KOODI (TOTUUS) tai VÄÄRÄ (EPÄTOSI), katsotaanpa vielä joitain funktion kaavoja VPR ja katso tuloksia.
Esimerkki 1: Tarkan vastaavuuden löytäminen VLOOKUP-toiminnolla
Kuten muistat, tarkan vastaavuuden etsimiseksi funktion neljäs argumentti VPR pitäisi olla merkitystä VÄÄRÄ (VÄÄRÄ).
Palataan taulukkoon aivan ensimmäisestä esimerkistä ja selvitetään, mikä eläin voi liikkua nopeudella 50 mailia tunnissa. Uskon, että tämä kaava ei aiheuta sinulle vaikeuksia:
=VLOOKUP(50,$A$2:$B$15,2,FALSE)
=ВПР(50;$A$2:$B$15;2;ЛОЖЬ)
Huomaa, että hakualueemme (sarake A) sisältää kaksi arvoa 50 – soluissa A5 и A6. Kaava palauttaa arvon solusta B5. Miksi? Koska kun etsit tarkkaa vastaavuutta, funktio VPR käyttää ensimmäistä löydettyä arvoa, joka vastaa haettua arvoa.
Esimerkki 2: VLOOKUPin käyttäminen likimääräisen vastaavuuden löytämiseen
Kun käytät toimintoa VPR etsiä likimääräistä vastaavuutta, eli kun argumentti range_lookup (range_lookup) on yhtä suuri kuin TOSI KOODI (TRUE) tai jätetty pois, sinun on ensin lajiteltava alue ensimmäisen sarakkeen mukaan nousevassa järjestyksessä.
Tämä on erittäin tärkeää, koska toiminto VPR palauttaa seuraavan suurimman arvon annetun arvon jälkeen, ja sitten haku pysähtyy. Jos laiminlyöt oikean lajittelun, saat hyvin outoja tuloksia tai virheilmoituksen. #AT (#N/A).
Nyt voit käyttää yhtä seuraavista kaavoista:
=VLOOKUP(69,$A$2:$B$15,2,TRUE) or =VLOOKUP(69,$A$2:$B$15,2)
=ВПР(69;$A$2:$B$15;2;ИСТИНА) or =ВПР(69;$A$2:$B$15;2)
Kuten näette, haluan selvittää, millä eläimistä on lähin nopeus 69 mailia tunnissa. Ja tässä on tulos, jonka funktio palautti minulle VPR:
Kuten näet, kaava palautti tuloksen Antilooppi (Antelope), jonka nopeus 61 mailia tunnissa, vaikka lista sisältää myös Gepardi (Cheetah), joka juoksee vauhdilla 70 mailia tunnissa, ja 70 on lähempänä 69:ää kuin 61, eikö niin? Miksi tämä tapahtuu? Koska toiminto VPR likimääräistä vastaavuutta etsiessään palauttaa suurimman arvon, joka ei ole suurempi kuin haettava arvo.
Toivon, että nämä esimerkit valaisevat funktion käyttöä VPR Excelissä, etkä katso häntä enää ulkopuolisena. Nyt ei ole haittaa toistaa lyhyesti tutkimamme materiaalin avainkohdat, jotta se pysyy paremmin muistissa.
VLOOKUP Excelissä – sinun on muistettava tämä!
- Toiminto VPR Excel ei voi katsoa vasemmalle. Se etsii aina arvoa argumentin antaman alueen vasemmanpuoleisimmalta sarakkeelta pöytäryhmä (pöytä).
- Toiminnassa VPR kaikki arvot eivät erota kirjainkoosta, eli pienet ja suuret kirjaimet ovat samanarvoisia.
- Jos etsimäsi arvo on pienempi kuin etsittävän alueen ensimmäisessä sarakkeessa oleva vähimmäisarvo, funktio VPR ilmoittaa virheestä #AT (#N/A).
- Jos kolmas argumentti col_index_num (sarakkeen_numero) pienempi kuin 1toiminto VPR ilmoittaa virheestä #ARVO! (#ARVO!). Jos se on suurempi kuin alueen sarakkeiden määrä pöytäryhmä (taulukko), toiminto ilmoittaa virheestä #VIITE! (#LINKKI!).
- Käytä argumentissa absoluuttisia soluviittauksia pöytäryhmä (taulukko), jotta oikea hakualue säilyy kaavaa kopioitaessa. Kokeile käyttää nimettyjä alueita tai taulukoita Excelissä vaihtoehtona.
- Kun teet likimääräistä hakua, muista, että etsimäsi alueen ensimmäinen sarake on lajiteltava nousevaan järjestykseen.
- Muista lopuksi neljännen argumentin merkitys. Käytä arvoja TOSI KOODI (TOTUUS) tai VÄÄRÄ (EPÄTOSI) tarkoituksella, niin pääset eroon monista päänsäryistä.
Seuraavissa toiminto-opetusohjelman artikkeleissa VPR Excelissä opimme edistyneempiä esimerkkejä, kuten erilaisten laskutoimitusten suorittamista käyttämällä VPR, arvojen poimiminen useista sarakkeista ja paljon muuta. Kiitos, että luit tämän opetusohjelman, ja toivottavasti nähdään taas ensi viikolla!