Excel

23 asiaa, jotka sinun pitäisi tietää VLOOKUPista

23 Things You Should Know About Vlookup

Kun haluat hakea tietoja taulukosta, Excel VLOOKUP -toiminto on loistava ratkaisu. Kyky dynaamisesti etsiä ja hakea tietoja taulukosta on pelinvaihtaja monille käyttäjille, ja VLOOKUP löytyy kaikkialta.

Ja vaikka VLOOKUP on suhteellisen helppo käyttää, on paljon, mikä voi mennä pieleen. Yksi syy on se, että VLOOKUPilla on merkittävä suunnitteluvirhe - oletuksena oletetaan, että olet kunnossa likimääräisen ottelun kanssa. Mitä et todennäköisesti ole.



Tämä voi aiheuttaa tuloksia näyttää täysin normaalilta , vaikka he ovatkin täysin väärä . Luota minuun, tätä EI halua yrittää selittää pomollesi, kun hän on jo lähettänyt laskentataulukon johdolle :)



Alla on lisätietoja siitä, miten hallita tätä haastetta, ja löydä muita vinkkejä ongelman hallitsemiseksi Excel VLOOKUP -toiminto .

1. Kuinka VLOOKUP toimii

VLOOKUP on toiminto, jolla etsitään ja haetaan tietoja taulukosta. VLOOKUPissa oleva 'V' tarkoittaa pystysuoraa, mikä tarkoittaa, että taulukon tiedot on järjestettävä pystysuoraan, rivillä. (Katso horisontaalisesti jäsennetyt tiedot kohdasta TAKAISIN ).



Jos sinulla on hyvin jäsennelty taulukko, jonka tiedot on järjestetty pystysuunnassa, ja vasemmalla sarake, jolla voit sovittaa rivin, voit todennäköisesti käyttää VLOOKUPia.

VLOOKUP edellyttää, että taulukko on jäsennelty siten, että hakuarvot näkyvät vasemman reunan sarakkeessa. Haettavat tiedot (tulosarvot) voivat näkyä missä tahansa oikealla olevassa sarakkeessa. Kun käytät VLOOKUPia, kuvittele, että taulukon jokainen sarake on numeroitu vasemmalta alkaen. Saadaksesi arvon tietystä sarakkeesta, anna yksinkertainen numero sarakeindeksinä. Alla olevassa esimerkissä haluamme etsiä sähköpostiosoitteen, joten käytämme sarakeindeksiä numeroa 4:

Katsaus VLOOKUPin toimintaan



Yllä olevassa taulukossa työntekijätunnukset ovat vasemmalla sarakkeessa 1 ja sähköpostiosoitteet sarakkeessa 4 oikealla.

Voit käyttää VLOOKUP-toimintoa toimittamalla 4 tietoa tai argumentteja:

  1. Etsimäsi arvo ( hakuarvo )
  2. Taulukon muodostavien solujen alue ( pöytäryhmä )
  3. Sarakkeen numero, josta tulos haetaan ( column_index )
  4. Ottelutila ( range_lookup , TOSI = arvioitu, EPÄTOSI = tarkka)

Video: Kuinka käyttää VLOOKUPia (3 min)

Jos et vieläkään ymmärrä VLOOKUPin perusajatusta, Jon Acamporalla, joka on Excel-kampuksella, on hieno selitys perustuu Starbucks-kahvivalikkoon.

2. VLOOKUP näyttää vain oikealta

Ehkä VLOOKUPin suurin rajoitus on, että se voi vain etsiä oikeutta hakea tietoja.

Tämä tarkoittaa, että VLOOKUP voi saada tietoja vain taulukon ensimmäisen sarakkeen oikealta puolelta olevista sarakkeista. Kun hakuarvot näkyvät ensimmäisessä (vasemmassa) sarakkeessa, tämä rajoitus ei tarkoita paljon, koska kaikki muut sarakkeet ovat jo oikealla. Kuitenkin, jos hakusarake näkyy taulukon sisällä jossain, pystyt etsimään arvoja vain kyseisen sarakkeen oikealta puolelta olevista sarakkeista. Sinun on toimitettava myös pienempi taulukko VLOOKUPille, joka alkaa hakusarakkeesta.

VLOOKUP voi näyttää vain hakuarvosarakkeen oikealla puolella

Voit voittaa tämän rajoituksen käyttämällä hakemistoa ja vastaavuutta VLOOKUPin sijaan.

3. VLOOKUP löytää ensimmäisen ottelun

Jos hakusarakkeessa on tarkan haun tilassa päällekkäisiä arvoja, VLOOKUP vastaa vain ensimmäistä arvoa. Alla olevassa esimerkissä etsimen etsimiseen käytetään VLOOKUPia, ja VLOOKUP on asetettu suorittamaan tarkka haku. Vaikka luettelossa on kaksi Janetia, VLOOKUP vastaa vain ensimmäistä:

VLOOKUP löytää aina ensimmäisen ottelun

Huomaa: käyttäytyminen voi muuttua, kun VLOOKUPia käytetään likimääräisessä ottelutilassa. Tässä artikkelissa selitetään aihe yksityiskohtaisesti.

4. VLOOKUP ei eroa isoja ja pieniä kirjaimia

Kun etsit arvoa, VLOOKUP ei käsittele isoja ja pieniä kirjaimia eri tavalla. VLOOKUPille tuotekoodi, kuten 'PQRF', on identtinen 'pqrf': n kanssa. Alla olevassa esimerkissä etsimme isoja kirjaimia 'JANET', mutta VLOOKUP ei erota kirjaimia, joten se yksinkertaisesti vastaa 'Janet', koska se on ensimmäinen löytämä vastaavuus:

VLOOKUP EI eroa kirjainkokoja

Tarjoamme myös maksettu koulutus VLOOKUP ja INDEX / MATCH

5. VLOOKUPilla on kaksi yhteensopivaa tilaa

VLOOKUPilla on kaksi toimintatapaa: tarkka ottelu ja likimääräinen ottelu. Useimmissa tapauksissa haluat todennäköisesti käyttää VLOOKUPia tarkan haun tilassa. Tämä on järkevää, kun haluat hakea tietoja jonkinlaisen ainutlaatuisen avaimen perusteella, esimerkiksi tuotetiedot tuotekoodin perusteella tai elokuvan tiedot elokuvan nimen perusteella:

Esimerkki tarkan haun VLOOKUP -elokuvista

H6-hakuvuoden kaava, joka perustuu elokuvan nimen täsmälliseen vastaavuuteen, on:

 
= VLOOKUP (H4,B5:E9,2,FALSE) // FALSE = exact match

Haluat kuitenkin käyttää likimääräistä tilaa tapauksissa, joissa et täsmää yksilöllisen tunnuksen kanssa, vaan etsit parhaan vastaavuuden tai parhaan luokan. Ehkä etsit esimerkiksi postimaksua painon perusteella, veroprosenttia tulojen perusteella tai provisioprosenttia kuukausittaisen myyntiluvun perusteella. Näissä tapauksissa et todennäköisesti löydä tarkkaa hakuarvoa taulukosta. Sen sijaan haluat, että VLOOKUP saa sinulle parhaan vastaavuuden annetulle hakuarvolle.

Arvioitu esimerkki VLOOKUP-ottelusta - palkkiot

D5: n kaava tekee likimääräisen vastaavuuden oikean palkkion saamiseksi:

 
= VLOOKUP (C5,$G:$H,2,TRUE) // TRUE = approximate match

6. Varoitus: VLOOKUP käyttää oletuksena likimääräistä osumaa

Tarkkaa ja likimääräistä vastaavuutta VLOOKUPissa ohjaa neljäs argumentti, jota kutsutaan 'alueen hauksi'. Tämä nimi ei ole intuitiivinen, joten sinun täytyy vain muistaa, miten se toimii.

Käytä tarkkaa vastaavuutta käyttämällä FALSE tai 0. Arvioidun haun asettamiseksi range_lookup on TOSI tai 1:

 
= VLOOKUP (value,table,column,TRUE) // approximate match = VLOOKUP (value,table,column,FALSE) // exact match

Valitettavasti 4. argumentti range_lookup on valinnainen ja sen oletusarvo on TOSI, mikä tarkoittaa, että VLOOKUP tekee oletuksena likimääräisen ottelun. Suoritettaessa likimääräistä ottelua VLOOKUP olettaa, että taulukko on lajiteltu ja suorittaa binäärisen haun. Jos VLOOKUP löytää tarkan haun arvon binaarihaun aikana, se palauttaa arvon tältä riviltä. Jos VLOOKUP kuitenkin kohtaa arvon, joka on suurempi kuin hakuarvo, se palauttaa edellisen rivin arvon.

Tämä on vaarallinen oletus, koska monet ihmiset jättävät VLOOKUPin vahingossa oletusasetuksiinsa, mikä voi aiheuttaa virheellinen tulos kun taulukkoa ei ole lajiteltu.

Tämän ongelman välttämiseksi muista käyttää FALSE tai nolla 4. argumenttina, kun haluat tarkan haun.

7. Voit pakottaa VLOOKUPin tekemään tarkan ottelun

Jos haluat pakottaa VLOOKUP: n etsimään tarkan vastaavuuden, muista asettaa 4 argumentin (range_lookup) arvoksi FALSE tai nolla. Nämä kaksi kaavaa vastaavat:

 
= VLOOKUP (value, data, column, FALSE) = VLOOKUP (value, data, column, 0)

Tarkan haun tilassa, kun VLOOKUP ei löydä arvoa, se palauttaa # N / A. Tämä on selkeä osoitus siitä, että arvoa ei löydy taulukosta.

8. Voit pyytää VLOOKUPia tekemään likimääräisen ottelun

Jos haluat käyttää VLOOKUPia likimääräisessä vastaavuustilassa, jätä joko 4. argumentti (range_lookup) tai anna se TOSI- tai 1. Nämä kolme kaavaa vastaavat:

 
= VLOOKUP (value, data, column) = VLOOKUP (value, data, column, 1) = VLOOKUP (value, data, column, TRUE)

Suosittelemme, että määrität aina range_lookup-argumentin nimenomaisesti, vaikka VLOOKUP ei vaadi sitä. Näin sinulla on aina visuaalinen muistutus odottamastasi ottelutilasta.

Video: Kuinka käyttää VLOOKUPia likimääräisiin otteluihin

9. Arvioita vastaavuuksia varten tiedot on lajiteltava

Jos käytät likimääräisen tilan hakua, tietosi on lajiteltava nousevassa järjestyksessä hakuarvon mukaan. Muuten saatat saada virheelliset tulokset . Huomaa myös, että joskus tekstitiedot voivat Katso lajiteltu, vaikka se ei olekaan.

Felienne Hermans on loistava esimerkki tästä ongelmasta , viileästä analyysistä, jonka hän teki todellisesta Enron taulukkolaskenta!

10. VLOOKUP voi yhdistää tietoja eri taulukoihin

VLOOKUPin yleinen käyttötapaus on kahden tai useamman taulukon tietojen yhdistäminen. Esimerkiksi sinulla voi olla tilaustietoja yhdessä taulukossa ja asiakastietoja toisessa taulukossa ja haluat tuoda joitain asiakastietoja tilaustaulukkoon analysointia varten:

VLOOKUP yhdistää tiedot liittymällä taulukoihin -Ennen

Koska asiakastunnus on olemassa molemmissa taulukoissa, voit käyttää tätä arvoa haluamiesi tietojen hakemiseen VLOOKUPin avulla. Määritä vain VLOOKUP käyttämään taulukon 1 id-arvoa ja taulukon 2 tietoja vaaditulla sarakeindeksillä. Alla olevassa esimerkissä käytämme kahta VLOOKUP-kaavaa. Yksi vetää sisään asiakkaan nimi ja toinen asiakkaan tilaan.

VLOOKUP yhdistää tiedot liittämällä taulukot jälkeen

Linkki: Esimerkki sulautumisesta VLOOKUPiin .

Video: Kuinka VLOOKUPia käytetään taulukoiden yhdistämiseen .

11. VLOOKUP voi luokitella tai luokitella tietoja

Jos joudut joskus soveltamaan mielivaltaisia ​​luokkia tietueisiin, voit tehdä sen helposti VLOOKUP-sovelluksella käyttämällä taulukkoa, joka toimii luokkana avaimena.

Klassinen esimerkki on arvosanat, joissa sinun on annettava arvosana pisteiden perusteella:

VLOOKUP käytetään luokittelemiseen - arvosanojen antaminen

Tässä tapauksessa VLOOKUP on määritetty likimääräiselle vastaavuudelle, joten on tärkeää, että taulukko lajitellaan nousevassa järjestyksessä.

Mutta voit käyttää VLOOKUPia myös mielivaltaisten luokkien määrittämiseen. Alla olevassa esimerkissä käytämme VLOOKUPia laskeaksesi ryhmän kullekin osastolle käyttämällä pientä taulukkoa (nimeltä 'avain'), joka määrittelee ryhmittelyn.

VLOOKUP käytetään luokittelemiseen - mielivaltaisten ryhmien määrittäminen

12. Absoluuttiset viitteet tekevät VLOOKUPista kannettavamman

Tilanteissa, joissa aiot noutaa tietoja useammasta kuin yhdestä taulukon sarakkeesta, tai jos sinun on kopioitava ja liitettävä VLOOKUP, voit säästää aikaa ja pahenemista käyttämällä absoluuttisia viittauksia hakuarvoihin ja taulukkoihin. Näin voit kopioida kaavan ja muuttaa sitten vain sarakeindeksinumeron käyttämään samaa hakua saadaksesi arvon toisesta sarakkeesta.

Esimerkiksi, koska hakuarvo ja taulukon taulukko ovat absoluuttisia, voimme kopioida kaavan sarakkeisiin, palata sitten takaisin ja muuttaa sarakeindeksiä tarpeen mukaan.

Absoluuttiset viitteet tekevät VLOOKUP-kaavoista kannettavampia

13. Nimetyt alueet tekevät VLOOKUPista helpommin luettavan (ja kannettavamman)

Absoluuttiset alueet ovat melko ruma näköisiä, joten voivat tehdä VLOOKUP-kaavoistasi paljon puhtaampia ja helpommin luettavia korvaamalla absoluuttiset viitteet nimetyillä alueilla, jotka ovat automaattisesti absoluuttisia.

Esimerkiksi yllä olevassa työntekijätietoesimerkissä voit nimetä syötesolun id: ksi ja sitten taulukon tiedot nimeksi data, voit kirjoittaa kaavan seuraavasti:

Nimetyt alueet helpottavat VLOOKUP-kaavojen lukemista

Sen lisäksi, että tämä kaava on helpommin luettavissa, se on myös kannettavampi, koska nimetyt alueet ovat automaattisesti absoluuttisia.

14. Sarakkeen lisääminen voi rikkoa olemassa olevia VLOOKUP-kaavoja

Jos laskentataulukossa on olemassa VLOOKUP-kaavoja, kaavat voivat rikkoutua, jos lisäät sarakkeen taulukkoon. Tämä johtuu siitä, että kovakoodatut sarakeindeksiarvot eivät muutu automaattisesti, kun sarakkeita lisätään tai poistetaan.

Tässä esimerkissä sijoituksen ja myynnin hakutoiminnot olivat rikki, kun vuosi ja sijoitus väliin lisättiin uusi sarake. Vuosi toimii edelleen, koska se on lisätyn sarakkeen vasemmalla puolella:

Sarakkeen lisääminen taulukkoon voi rikkoa VLOOKUP

Tämän ongelman välttämiseksi voit laskea sarakeindeksin seuraavissa kahdessa vinkissä kuvatulla tavalla.

15. Voit laskea sarakeindeksin käyttämällä RIVI tai SARAKE

Jos olet tyyppi, jota vaivaa kaikki muokkaukset kaavan kopioinnin jälkeen, voit luoda dynaamiset sarakeindeksit joko RIVI tai SARAKE avulla. Jos saat tietoja peräkkäisistä sarakkeista, tämän temppun avulla voit määrittää yhden VLOOKUP-kaavan ja kopioida sen sitten ilman muutoksia.

Esimerkiksi alla olevien työntekijätietojen avulla voimme käyttää COLUMN-funktiota dynaamisen sarakeindeksin luomiseen. Solun C3 ensimmäiselle kaavalle COLUMN itsessään palauttaa arvon 3 (koska sarake C on kolmas taulukossa), joten meidän on yksinkertaisesti vähennettävä yksi ja kopioitava kaava toiseen:

Esimerkki sarakkeen käytöstä sarakkeen indeksin laskemiselle VLOOKUP

Kaikki kaavat ovat identtisiä ilman jälkitoimitusta.

Käytämme kaava on seuraava:

 
= VLOOKUP (id,data, COLUMN ()-1,0)

16. Käytä VLOOKUP + MATCH täysin dynaamiseen sarakeindeksiin

Ottaen yllä olevan kärjen askeleen pidemmälle, voit käyttää MATCH-näppäintä etsimään sarakkeen sijaintia taulukossa ja palauttamaan täysin dynaamisen sarakeindeksin.

Tätä kutsutaan joskus kaksisuuntaiseksi hauksi, koska etsit sekä riviä että saraketta.

Esimerkki on myyjän myynnin etsiminen tietyssä kuussa tai tietyn tuotteen hinnan haku tietyltä toimittajalta.

Oletetaan esimerkiksi, että sinulla on myynti kuukaudessa myyjän mukaan eriteltynä:

VLOOKUP kaksisuuntainen haku - miten etsiä kuukautta?

VLOOKUP löytää helposti myyjän, mutta sillä ei ole mitään tapaa käsitellä kuukauden nimeä automaattisesti. Temppu on käyttää MATCH-funktiota staattisen sarakehakemiston sijasta.

VLOOKUP kaksisuuntainen haku MATCH-toiminnolla sarakehakemiston saamiseksi

Huomaa, että annamme alueelle, joka sisältää kaikki taulukon sarakkeet, jotta voimme synkronoida VLOOKUPin käyttämät sarakkeiden numerot.

 
= VLOOKUP (H2,data, MATCH (H3,months,0),0)

Huomaa: Näet usein kaksisuuntaisia ​​hakuja, jotka on tehty INDEX- ja MATCH-tekniikoilla, jotka tarjoavat enemmän joustavuutta ja parempaa suorituskykyä isoille tietojoukoille. Katso miten tässä pikavideossa: Kuinka tehdä kaksisuuntainen haku hakemistolla ja MATCH .

17. VLOOKUP sallii jokerimerkkien osittaisen sovituksen

Aina kun käytät VLOOKUPia tarkan haun tilassa, sinulla on mahdollisuus käyttää yleismerkkejä hakuarvossa. Se voi tuntua vastalauseelta, mutta jokerimerkit antavat sinun tehdä tarkka ottelu osittaisen ottelun perusteella :)

Excel tarjoaa kaksi jokerimerkkiä: tähti (*) vastaa yhtä tai useampaa merkkiä ja kysymysmerkki (?) Yhtä merkkiä.

Voit esimerkiksi kirjoittaa tähden suoraan soluun ja viitata siihen hakuarvoksi VLOOKUP-toiminnolla. Alla olevassa näytössä olemme kirjoittaneet 'Mon *' H3: een, joka on nimetty alue nimeltä 'val'. Tämän vuoksi VLOOKUP vastaa nimeä 'Monet'.

VLOOKUP-jokerimerkit - käyttämällä tähtiä suoraan

Kaava on tässä tapauksessa yksinkertainen:

 
= VLOOKUP (val,data,1,0)

Jos haluat, voit säätää VLOOKUP-kaavaa käyttämään sisäänrakennettua jokerimerkkiä, kuten alla olevassa esimerkissä, jossa yksinkertaisesti yhdistetään H3: n arvo tähdellä.

VLOOKUP-jokerimerkit - tähti on liitetty hakuarvoon

Tässä tapauksessa yhdistetään tähti VLOOKUP-toiminnon sisällä olevaan hakuarvoon:

 
= VLOOKUP (val&'*',data,1,0)

Huomaa: Ole varovainen jokerimerkkien ja VLOOKUPin kanssa. Ne antavat sinulle helpon tavan luoda 'laiska ottelu', mutta ne myös helpottavat väärän ottelun löytämistä.

18. Voit ansaita # N / A-virhettä ja näyttää ystävällisen viestin

Tarkan haun tilassa VLOOKUP näyttää # N / A -virheen, kun vastaavuutta ei löydy. Yhdellä tavalla tämä on hyödyllistä, koska se kertoo sinulle lopullisesti, että hakutaulukossa ei ole vastaavuutta. # N / A-virheet eivät kuitenkaan ole kovin hauskoja tarkastella, joten on useita tapoja ansaita tämä virhe ja näyttää jotain muuta sen sijaan.

Kun aloitat VLOOKUPin käytön, sinun on pakko törmätä # N / A -virheeseen, joka tapahtuu, kun VLOOKUP ei löydä vastausta.

kuinka käyttää stdevia Excelissä

Tämä on hyödyllinen virhe, koska VLOOKUP kertoo sinulle selvästi, että se ei löydä hakuarvoa. Tässä esimerkissä 'Latte' ei ole juomana taulukossa, joten VLOOKUP heittää # N / A -virheen

VLOOKUP # N / A-virhe

Kaava on tässä tapauksessa täysin vakio tarkka täsmäys:

 
= VLOOKUP (E6,data,2,0)

# N / A-virheet eivät kuitenkaan ole kovin hauskoja tarkastella, joten kannattaa saada kiinni tämä virhe ja näyttää ystävällisempi viesti.

Helpoin tapa ansaita virheitä VLOOKUPin avulla on kääriä VLOOKUP IFERROR-funktioon. IFERROR-toiminnon avulla voit havaita virheet ja palauttaa valitsemasi tuloksen.

Voit vangita tämän virheen ja näyttää 'ei löydy' -sanoman virheen sijasta yksinkertaisesti käärimällä orignaalikaavan IFERRORin sisälle ja asettamalla haluamasi tuloksen:

VLOOKUP # N / A -virhe loukussa IFERROR

Jos hakuarvo löytyy, virheitä ei tapahdu ja VLOOKUP-funktio palauttaa normaalin tuloksen. Tässä on kaava:

 
= IFERROR ( VLOOKUP (E6,data,2,0),'Not found')

19. Numerot tekstinä voivat aiheuttaa hakuvirheen

Joskus taulukko, jonka kanssa työskentelet VLOOKUPissa, saattaa sisältää numeroita, jotka on kirjoitettu tekstinä. Jos haet vain numeroita tekstinä taulukon sarakkeesta, sillä ei ole merkitystä. Mutta jos taulukon ensimmäinen sarake sisältää tekstinä syötettyjä numeroita, saat # N / A -virheen, jos hakuarvo ei ole myöskään teksti.

Seuraavassa esimerkissä planeettataulukon tunnukset ovat numeroita syötetty tekstinä , joka saa VLOOKUP palauttamaan virheen, koska hakuarvo on määrä 3:

Numerot syötetään tekstinä VLOOKUP-virheesimerkki

Tämän ongelman ratkaisemiseksi sinun on varmistettava, että hakuarvo ja taulukon ensimmäinen sarake ovat molemmat samaa tietotyyppiä (joko molemmat numerot tai molemmat tekstit).

Yksi tapa tehdä tämä on muuntaa hakusarakkeen arvot numeroiksi. Helppo tapa tehdä tämä on lisätä nolla käyttämällä erityistä liimaa.

Jos sinulla ei ole helppoa hallita lähdetaulukkoa, voit myös säätää VLOOKUP-kaavaa muuntamaan hakuarvo tekstiksi liittämällä '' arvoon näin:

 
= VLOOKUP (id&'',planets,2,0)

Numerot syötetään tekstinä VLOOKUP-virheratkaisu

Jos et voi olla varma, milloin sinulla on numeroita ja kun sinulla on tekstiä, voit huolehtia molemmista vaihtoehdoista käärimällä VLOOKUP IFERRORiin ja kirjoittamalla kaavan molempien tapausten käsittelemiseksi:

 
= IFERROR ( VLOOKUP (id,planets,3,0), VLOOKUP (id&'',planets,3,0))

20. Voit korvata sisäkkäiset IF-käskyt VLOOKUP-toiminnolla

Yksi VLOOKUPin mielenkiintoisimmista käyttötavoista on korvata sisäkkäiset IF-käskyt. Jos olet koskaan rakentanut sarjan sisäkkäisiä IF: itä, tiedät, että ne toimivat hyvin, mutta ne vaativat hieman sulkeita. Sinun on myös oltava varovainen järjestyksessä, jossa työskentelet, jotta et aiheuttaisi loogista virhettä.

Esimerkiksi sisäkkäisten sijoitusrahastojen yleinen käyttö on arvosanojen osoittaminen jonkinlaisten pisteiden perusteella. Alla olevasta esimerkistä näet, että kaava on rakennettu sisäkkäisillä IF: llä tekemään niin, käyttämällä oikeassa reunassa olevaa näppäintä oppaana.

Arvosanojen määrittäminen pitkällä sisäkkäisellä IF-kaavalla

Täysi sisäkkäinen IF-kaava näyttää tältä:

 
= IF (C5<64,'F', IF (C5<73,'D', IF (C5<85,'C', IF (C5<95,'B','A'))))

Tämä toimii hyvin, mutta huomaa, että sekä logiikka että todelliset tulokset leivotaan suoraan kaavaan. Jos pisteytys jostain syystä muuttuu, sinun on päivitettävä yksi kaava huolellisesti ja kopioitava se sitten koko taulukko.

Sen sijaan VLOOKUP voi antaa samat arvosanat yksinkertaisella kaavalla. Sinun tarvitsee vain varmistaa, että arvosanan avaintaulukko on määritetty VLOOKUPia varten (ts. Se lajitellaan eniten pisteiden mukaan ja sisältää hakasulkeita kaikkien pisteiden käsittelemiseksi).

Kun nimetty alue-avain on määritetty arvosanan avaintaulukkoon, VLOOKUP-kaava on hyvin yksinkertainen ja tuottaa samat arvosanat kuin alkuperäinen sisäkkäisten IF-kaava:

Arvosanojen määrittäminen yksinkertaisella VLOOKUP-kaavalla

Avain-nimisen palkkaluokka-taulukon avulla meillä on hyvin yksinkertainen VLOOKUP-kaava:

 
= VLOOKUP (C5,key,2,TRUE)

Hieno bonus tästä lähestymistavasta on, että sekä logiikka että pisteet on rakennettu suoraan palkkaluokkaan. Jos jotain muuttuu, voit yksinkertaisesti päivittää taulukon suoraan ja VLOOKUP-kaavat päivittyvät automaattisesti - muokkausta ei tarvita.

Video: Kuinka korvata sisäkkäiset IF: t VLOOKUP: lla

21. VLOOKUP voi käsitellä vain yhtä kriteeriä

Suunnittelun mukaan VLOOKUP voi löytää vain yhteen kriteeriin perustuvia arvoja, jotka toimitetaan hakuarvoina taulukon ensimmäisestä sarakkeesta (hakusarakkeesta).

Tämä tarkoittaa, että et voi helposti tehdä asioita, kuten etsiä työntekijää, jonka sukunimi on 'Smith', kohdassa '' Kirjanpito '', tai etsiä työntekijää etu- ja sukunimien perusteella erillisistä sarakkeista.

On kuitenkin tapoja voittaa tämä rajoitus. Yksi kiertotapa on luoda apusarake, joka yhdistää arvot eri sarakkeista, luoda haun arvoja, jotka käyttäytyvät kuin useat olosuhteet. Esimerkiksi täällä haluamme löytää osaston ja ryhmän työntekijälle, mutta etu- ja sukunimi näkyvät erillisissä sarakkeissa. Kuinka voimme etsiä molempia kerralla?

Useiden kriteerien VLOOKUP-ongelma - miten etsiä sekä etu- että sukunimeä?

Lisää ensin auttaja sarake, joka yksinkertaisesti yhdistää etu- ja sukunimet yhteen:

VLOOKUP useita ehtoja vaihe 2 - lisää auttaja sarake, joka yhdistää useita ehtoja

Määritä sitten VLOOKUP käyttämään taulukkoa, joka sisältää tämän uuden sarakkeen, ja yhdistä etu- ja sukunimet hakuarvoon:

VLOOKUP useita ehtoja vaihe 3 - Liitä kriteerit muodostaaksesi hakuarvon

Lopullinen VLOOKUP-kaava etsii etu- ja sukunimet yhdessä avustaja-sarakkeen avulla:

 
= VLOOKUP (C3&D3,data,4,0)

22. Kaksi VLOOKUPSia on nopeampi kuin yksi VLOOKUP

Se voi tuntua täysin hullulta, mutta kun sinulla on iso joukko tietoja ja sinun on tehtävä tarkka ottelu, voit nopeuttaa VLOOKUPia lisäämällä toisen VLOOKUP kaavaan!

Tausta: kuvittele, että sinulla on paljon tilausdataa, esimerkiksi yli 10000 tietuetta ja haet tilauksen kokonaismäärää VLOOKUPin avulla tilaustunnuksen perusteella. Joten käytät jotain tällaista:

 
= VLOOKUP (order_id,order_data, 5, FALSE)

Lopussa oleva FALSE pakottaa VLOOKUPin tekemään tarkan ottelun. Haluat tarkan haun, koska on mahdollista, että tilausnumeroa ei löydy. Tässä tapauksessa tarkka hakuasetus saa VLOOKUP palauttamaan # N / A -virheen.

Ongelmana on, että tarkat osumat ovat todella hitaita, koska Excelin on edettävä lineaarisesti kaikkien arvojen läpi, kunnes se löytää vastaavuuden.

Sitä vastoin likimääräiset ottelut ovat salamannopeat, koska Excel pystyy tekemään niin kutsutun a binaarihaku .

Binaarihakujen ongelma (ts. VLOOKUP likimääräisessä vastaavuustilassa) on, että VLOOKUP voi palauttaa väärän tuloksen, kun arvoa ei löydy. Mikä pahempaa, tulos voi näyttää täysin normaalilta, joten sitä voi olla hyvin vaikea havaita.

Ratkaisu on käyttää VLOOKUPia kahdesti, molemmat kertaa likimääräisessä ottelutilassa. Ensimmäinen instanssi yksinkertaisesti tarkistaa, että arvo on todella olemassa. Jos näin on, suoritetaan toinen VLOOKUP (jälleen likimääräisessä ottelutilassa) haluamiesi tietojen hakemiseksi. Jos ei, voit palauttaa minkä tahansa arvon, jonka haluat ilmaista, että tulosta ei löydy.

Lopullinen kaava näyttää tältä:

 
= IF ( VLOOKUP (order_id,order_data,1,TRUE)=order_id,  VLOOKUP (order_id,order_data,5,TRUE), 'Missing')

Olen oppinut tämän lähestymistavan FastExcelin Charles Williamsilta, jolla on upea, yksityiskohtainen artikkeli täällä: Miksi 2 VLOOKUPSia ovat parempia kuin 1 VLOOKUP .

Huomaa: Tietosi on lajiteltava, jotta tätä temppua voidaan käyttää. Se on yksinkertaisesti tapa suojata puuttuvalta hakuarvolta, samalla kun ylläpidetään nopeaa hakua.

23. INDEKSI ja MATCH yhdessä voivat tehdä kaiken, mitä VLOOKUP voi tehdä ja paljon muuta

Jos seuraat Exceliä verkossa, törmäät todennäköisesti VLOOKUP vs. INDEX / MATCH -keskustelu. Väite voi tulla yllättävän kuumaksi :)

Ydin on tämä: INDEX + MATCH voi tehdä kaiken, mitä VLOOKUP (ja HLOOKUP) pystyy, paljon enemmän joustavuutta hieman monimutkaisemmalla hinnalla. Joten ne, jotka kannattavat INDEX + MATCHia, väittävät (hyvin järkevästi), että voit myös aloittaa oppimisen INDEX ja MATCH, koska se antaa sinulle lopulta paremman työkalupaketin.

Argumentti INDEX + MATCHia vastaan ​​on, että se vaatii kaksi toimintoa yhden sijasta, joten käyttäjien (erityisesti uusien käyttäjien) on luonnostaan ​​monimutkaisempi oppia ja hallita.

Kaksi senttiäni on, että jos käytät Exceliä usein, haluat oppia käyttämään INDEXia ja MATCHia. Se on erittäin tehokas yhdistelmä.

Mutta mielestäni sinun pitäisi myös oppia VLOOKUP, johon törmäät kaikkialla, usein laskentataulukoissa, jotka perit muilta. Yksinkertaisissa tilanteissa VLOOKUP saa työn aikaan hienosti ilman hätää.

Jos haluat lisätietoja INDEKSISTA ja MATCH: sta, katso tämä artikkeli .

Kirjailija Dave Bruns


^