Käytät todennäköisesti VLOOKUP()-funktiota tarkkojen vastaavuuksien etsimiseen Microsoft Excelissä, mutta voit myös etsiä lähimmän arvon, kun et tiedä tarkkaa arvoa.
” data-credit=”Image: Wachiwit/Shutterstock”>
Kuva: Wachiwit/Shutterstock 
Yksi Microsoft Excelin yleisimmin käytetyistä funktioista on VLOOKUP(). Se on hakutoiminto, jonka avulla voit hakea arvoja etsimällä vastaavaa arvoa. Voit esimerkiksi haluta palauttaa tuotteen hinnan käyttämällä tuotteen nimeä tai opiskelijan lopullisen arvosanan käyttämällä opiskelijan nimeä. Kyseessä on joustava ja erittäin hyödyllinen toiminto. Useimmiten haluat tarkan vastaavuuden, mutta joskus sinun on palautettava lähin vastaava arvo. Tässä artikkelissa tutustutaan VLOOKUP()-funktioon ja sitten käytetään sitä sellaisen vastaavuuden etsimiseen, joka lähes vastaa hakuarvoa.
KATSO: 83 Excel-vinkkiä, jotka jokaisen käyttäjän tulisi hallita (TechRepublic)
Käytän Microsoft 365: tä Windows 10 64-bittisessä järjestelmässä, mutta voit työskennellä aiemmilla versioilla. Voit työskennellä omilla tiedoillasi tai ladata esittelyn .xlsx- ja .xls-tiedostot. Tämä toiminto toimii samalla tavalla Excel for the webissä. Tässä artikkelissa oletetaan, että sinulla on Excelin perustaidot, kuten funktioiden syöttäminen ja lajittelu, mutta aloittelijankin pitäisi pystyä soveltamaan ohjeita onnistuneesti.
Miten VLOOKUP() toimii Excelissä?
Excelin VLOOKUP()-funktio palauttaa vastaavan arvon sen jälkeen, kun hakuarvoa on vastattu seuraavalla syntaksilla:
VLOOKUP(lookup_value, lookup_range, offset, is_sorted).
Taulukossa A selitetään nämä argumentit.
|
Argumentti |
Selitys |
Vaadittu/Vapaaehtoinen |
|
lookup_value |
Tämä on arvo, jota yrität verrata. |
Pakollinen |
|
lookup_range |
Tämä yksilöi data-alueen. |
Pakollinen |
|
offset |
Tämä numeerinen arvo yksilöi vastaavan sarakkeen lookup_valuen oikealla puolella. |
Vaadittu |
|
is_sorted |
Tämä on boolen arvo: TRUE tai FALSE. TRUE on oletusarvo ja osoittaa, että lookup_value-tiedot on lajiteltu. Käytä FALSE-arvoa osoittaaksesi, että lookup_value ei ole lajiteltu. |
Valinnainen |
Lyhyesti sanottuna VLOOKUP() käyttää hakuarvoa palauttaakseen toisessa sarakkeessa olevan arvon. Käytetään sitä nyt etsimään lähin vastaava arvo.
Lähimmän vastaavuuden löytäminen VLOOKUP()-olion avulla
Useimmiten käytät VLOOKUP()-ohjelmaa tarkan vastaavuuden etsimiseen, mutta voit käyttää sitä myös lähimmän vastaavuuden etsimiseen. Voit käyttää tätä tekniikkaa, kun et ole varma tarkasta arvosta tai kun sinun on löydettävä arvoalue. Käytetään nyt tätä funktiota palauttamaan tuotteen nimi hinnalle, kun hinta ei ole tarkka vastaavuus, käyttäen kuvassa A esitettyä tietosarjaa. Avain on syöttöarvo H2:ssa. Syötä arvo, jota yrität verrata, ja H3:ssa oleva funktio palauttaa lähimmän arvon, jonka se löytää H2:ssa olevaan syöttöarvoon nähden.
Kuva A
” data-credit=””> Etsimme lähimmät vastaavat hinnat.
Juuri nyt H3:ssa oleva funktio
=VLOOKUP(H2,C3:E47,2)
palauttaa arvon, jossa ei ole järkeä – se on väärä, mutta siitä lisää hetken kuluttua. Katsotaan nyt, miten tämä funktio toimii asiayhteydessä. Syötät arvaamasi hinnan kohtaan H2. Tämän jälkeen funktio käyttää tätä arvoa vastaamaan arvoa sarakkeessa C, joka on hakualueen ensimmäinen sarake (mutta ei välttämättä tietokokonaisuuden ensimmäinen sarake). Kun se ei löydä täsmällistä vastaavuutta, se lopettaa etsimisen, kun se löytää H2:n arvoa lähimmän arvon, joka on pienempi kuin kyseinen arvo, ja palauttaa sitten saman rivin tuotenimen.
Se ei toimi, koska tietosarjaa ei ole lajiteltu hintasarakkeen – hakuarvon – mukaan. Juuri nyt tietokokonaisuus on lajiteltu ID-sarakkeen mukaan. Korjaus on yksinkertainen: suorita yksikköhinta-sarakkeen nouseva lajittelu. Napsauta mitä tahansa yksikköhinta-sarakkeen solua ja valitse Lajittele pienimmästä suurimpaan -vaihtoehto Koti-välilehden Muokkausryhmän Lajittele ja suodata -pudotusvalikosta. Tai napsauta AZ Tiedot-välilehden Lajittele ja suodata -ryhmässä. Kuvassa B näkyvät tulokset. Kun toimintoa käytetään sellaisenaan, hakusarake, tässä tapauksessa Yksikköhinta-sarake, on lajiteltava nousevaan järjestykseen.
Kuva B
” data-credit=””> Lajittelun jälkeen VLOOKUP()-funktio palauttaa lähimmän vastaavan arvon.
Kuten näet, VLOOKUP() palauttaa tuotteen Konbu, jonka hinta on 6 dollaria. Tämä hinta on lähimpänä H2:n hakuarvoa 6,50, mutta ei kuitenkaan suurempi kuin 6,50. Jos muutat H2:n hakuarvon arvoksi 7 tai 8,99, se palauttaa edelleen Konbu. Jos muutat H2:n arvoksi 9,10, se palauttaa Tunnbrödin.
KATSO: Windows 10: Puheentunnistuksen ja sanelun äänikomentojen luettelot (ilmainen PDF). (TechRepublic)
Tässä vaiheessa saatat miettiä, mitä tapahtuu, jos syötät arvon, joka vastaa täsmälleen samaa arvoa. Toiminto palauttaa tarkan vastaavuuden tuotenimen. Jos vastaavia arvoja on kaksi, se palauttaa ensimmäisen. Näin VLOOKUP() toimii. Se, jonka avulla voimme löytää lähes vastaavan, on syöttöarvo H2:ssa. Tekniikka ei tee mitään erityistä; olen vain paljastanut tämän käyttäytymisen.
Muistatko sen valinnaisen argumentin is_sorted? Juuri nyt funktio luottaa oletusarvoon TRUE. Jos muutat argumentin arvoksi FALSE, funktio toimii vain yksikköhinta-sarakkeen tarkkojen vastaavuuksien osalta, eikä sillä ole väliä, onko kyseinen sarake lajiteltu. Se ei kuitenkaan toimi, jos sarake on lajiteltu laskevaan järjestykseen.
Tätä vähän tunnettua käyttäytymistä voidaan käyttää hyvin hyväksi, kun tarkkoja arvoja ei tiedetä ja tarvitset joustavuutta arvaamiseen.
Microsoftin viikoittainen uutiskirje
Ole yrityksesi Microsoft-insider lukemalla näitä Windows- ja Office-vinkkejä, niksejä ja huijauslomakkeita. Toimitetaan maanantaisin ja keskiviikkoisin.