Excel

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

23 Things You Should Know About Vlookup

Kun haluat ottaa tietoja taulukosta, Excel VLOOKUP -toiminto on loistava ratkaisu. Kyky dynaamisesti hakea ja noutaa tietoja taulukosta on monille käyttäjille muutos, ja löydät VLOOKUPin kaikkialta.



Ja silti, vaikka VLOOKUP on suhteellisen helppokäyttöinen, on monia asioita, jotka voivat mennä pieleen. Yksi syy on se, että VLOOKUPilla on suuri suunnitteluvirhe - oletusarvoisesti oletetaan, että olet kunnossa likimääräisen ottelun kanssa. Mitä et todennäköisesti ole.

Tämä voi aiheuttaa seuraavia tuloksia näyttää täysin normaalilta , vaikka ovatkin täysin väärin . Luota minuun, tämä EI ole asia, jota haluat yrittää selittää pomollesi, kun hän on jo lähettänyt laskentataulukosi hallintoon :)





Lue alta oppia hallitsemaan tätä haastetta ja löydä muita vinkkejä haasteen hallintaan Excel VLOOKUP -toiminto .

1. Miten VLOOKUP toimii

VLOOKUP on toiminto, joka etsii ja hakee tietoja taulukosta. VLOOKUPin V -kirjain tarkoittaa pystysuoraa, mikä tarkoittaa, että taulukon tiedot on järjestettävä pystysuoraan ja tiedot riveille. (Katso vaakasuuntaiset tiedot kohdasta HLOOKUP ).



Jos sinulla on hyvin jäsennelty taulukko, jossa tiedot on järjestetty pystysuunnassa, ja vasemmalla sarake, jonka avulla voit sovittaa rivin, voit todennäköisesti käyttää VLOOKUP -toimintoa.

VLOOKUP edellyttää, että taulukko on rakennettu siten, että hakuarvot näkyvät vasemmassa sarakkeessa. Haettavat tiedot (tulosarvot) voivat näkyä missä tahansa sarakkeessa oikealla. Kun käytät VLOOKUPia, kuvittele, että taulukon jokainen sarake on numeroitu vasemmalta alkaen. Jos haluat saada arvon tietystä sarakkeesta, anna vain sopiva numero sarakeindeksinä. Alla olevassa esimerkissä haluamme etsiä sähköpostiosoitteen, joten käytämme sarakehakemistoa numerolla 4:

Katsaus VLOOKUPin toimintaan

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

VLOOKUPin käyttämiseksi annat 4 tietoa tai argumenttia:

  1. Etsimäsi arvo ( haku_arvo )
  2. Taulukon muodostavat solualueet ( pöytäryhmä )
  3. Sarakkeen numero, josta haetaan tulos ( sarake_indeksi )
  4. Ottelutila ( range_lookup , TRUE = likimääräinen, FALSE = tarkka)

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

Jos et vieläkään ymmärrä VLOOKUPin perusideaa, Jon Acamporalla Excel Campusilla on loistava selitys perustuu Starbucks -kahvivalikkoon.

2. VLOOKUP näyttää vain oikealta

Ehkä VLOOKUPin suurin rajoitus on se, että se voi katsoa vain oikealle noutaakseen tietoja.

Tämä tarkoittaa, että VLOOKUP voi saada tietoja vain taulukon ensimmäisen sarakkeen oikealla puolella olevista sarakkeista. Kun hakuarvot näkyvät ensimmäisessä (vasemmassa) sarakkeessa, tämä rajoitus ei merkitse paljon, koska kaikki muut sarakkeet ovat jo oikealla. Jos hakusarake näkyy kuitenkin jossain taulukon sisällä, voit etsiä arvoja vain sarakkeen oikealla puolella olevista sarakkeista. Sinun on myös toimitettava pienempi taulukko VLOOKUPille, joka alkaa hakusarakkeella.

VLOOKUP voi katsoa vain hakuarvo -sarakkeen oikealle puolelle

Voit voittaa tämän rajoituksen käyttämällä INDEX- ja MATCH -toimintoja VLOOKUP -toiminnon sijaan.

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

Jos tarkan haun tilassa hakusarake sisältää päällekkäisiä arvoja, VLOOKUP vastaa vain ensimmäistä arvoa. Alla olevassa esimerkissä etsimme etunimen VLOOKUPilla, ja VLOOKUP on asetettu suorittamaan tarkka vastaavuus. Vaikka luettelossa on kaksi Janettia, VLOOKUP vastaa vain ensimmäistä:

VLOOKUP löytää aina ensimmäisen ottelun

Huomautus: käyttäytyminen voi muuttua, kun VLOOKUP -toimintoa käytetään likimääräisen haun tilassa. Tämä artikkeli selittää aiheen yksityiskohtaisesti.

4. VLOOKUP ei erota kirjainkokoa

Kun etsit arvoa, VLOOKUP ei käsittele isoja ja pieniä kirjaimia eri tavalla. VLOOKUPille tuotekoodi, kuten 'PQRF', on sama kuin 'pqrf'. Alla olevassa esimerkissä etsimme isoja kirjaimia 'JANET', mutta VLOOKUP ei erota kirjainta, joten se vastaa vain sanaa 'Janet', koska se on ensimmäinen löytö:

VLOOKUP EI ole kirjainkoolla

Tarjoamme myös maksettu koulutus VLOOKUP ja INDEX/MATCH

5. VLOOKUPissa on kaksi hakutilaa

VLOOKUPilla on kaksi toimintatilaa: tarkka haku ja likimääräinen vastaavuus. 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 tuotekoodiin perustuvat tuotetiedot tai elokuvan otsikkoon perustuvat elokuvatiedot:

VLOOKUP -tarkan haun esimerkki - vastaavat elokuvat

H6: n kaava hakuvuodelle elokuvan nimen tarkan vastaavuuden perusteella on:

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

Haluat kuitenkin käyttää likimääräistä tilaa tapauksissa, joissa et vastaa yksilöivää tunnusta, vaan etsit parasta vastaavuutta tai parasta luokkaa. Voit esimerkiksi etsiä postikuluja 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 tarjoaa sinulle parhaan haun tietylle hakuarvolle.

VLOOKUP likimääräinen otteluesimerkki - provisiot

D5: n kaava vastaa likimääräisesti oikean palkkion noutamista:

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

6. Varoitus: VLOOKUP käyttää oletusarvoisesti likimääräistä vastaavuutta

Tarkkaa ja likimääräistä vastaavuutta VLOOKUPissa ohjaa neljäs argumentti, nimeltään 'aluehaku'. Tämä nimi ei ole intuitiivinen, joten sinun on vain muistettava, miten se toimii.

Käytä tarkkaa vastaavuutta käyttämällä FALSE tai 0. Jos haluat likimääräisen haun, aseta range_lookup arvoksi TRUE tai 1:

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

Valitettavasti neljäs argumentti, range_lookup, on valinnainen ja oletusarvo on TRUE, mikä tarkoittaa, että VLOOKUP tekee oletusarvoisen likimääräisen osuman. Suorittaessaan likimääräistä vastaavuutta VLOOKUP olettaa, että taulukko on lajiteltu, ja suorittaa binäärihaun. Jos VLOOKUP löytää tarkan haun arvon binäärihaun aikana, se palauttaa arvon kyseiseltä riviltä. Jos VLOOKUP havaitsee kuitenkin suuremman arvon kuin hakuarvo, se palauttaa edellisen rivin arvon.

Tämä on vaarallinen oletusarvo, koska monet ihmiset jättävät tahattomasti VLOOKUPin oletustilaan, mikä voi aiheuttaa väärä tulos kun taulukkoa ei ole lajiteltu.

Välttääksesi tämän ongelman, käytä FALSE tai nollaa 4. argumenttina, kun haluat tarkan vastaavuuden.

7. Voit pakottaa VLOOKUPin tekemään tarkan haun

Jos haluat pakottaa VLOOKUPin etsimään tarkan vastaavuuden, aseta 4 -argumentiksi (alue_näkymä) arvoksi EPÄTOSI 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ä osoittaa selvästi, että arvoa ei löydy taulukosta.

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

Jos haluat käyttää VLOOKUPia likimääräisessä hakutilassa, jätä neljäs argumentti (alue_näkymä) pois 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 odotetusta ottelutilasta.

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

9. Jotta likimääräiset vastaavuudet saadaan, tiedot on lajiteltava

Jos käytät likimääräisen tilan vastaavuutta, tietosi täytyy lajitella nousevassa järjestyksessä hakuarvon mukaan. Muuten saatat saada vääriä tuloksia . Huomaa myös, että joskus tekstitiedot voivat Katso järjestetty, vaikka ei olekaan.

Felienne Hermans tässä on loistava esimerkki tästä ongelmasta , viileän analyysin perusteella, jonka hän teki todellisesta Enron laskentataulukot!

10. VLOOKUP voi yhdistää tietoja eri taulukoihin

VLOOKUPin yleinen käyttötapa on kahden tai useamman taulukon tietojen yhdistäminen. Esimerkiksi sinulla voi olla tilaustiedot yhdessä taulukossa ja asiakastiedot toisessa taulukossa ja haluat tuoda joitain asiakastietoja tilaustaulukkoon analysoitavaksi:

VLOOKUP yhdistää tiedot yhdistämällä taulukoita ennen

Koska asiakastunnus on molemmissa taulukoissa, voit käyttää tätä arvoa haluamiesi tietojen hakemiseen VLOOKUPilla. Määritä VLOOKUP vain käyttämään taulukon 1 id -arvoa ja taulukon 2 tietoja vaaditun sarakeindeksin kanssa. Alla olevassa esimerkissä käytämme kahta VLOOKUP -kaavaa. Toinen vetää asiakkaan nimen ja toinen asiakkaan tilan.

VLOOKUP yhdistää tiedot yhdistämällä taulukot -after

Linkki: Esimerkki sulautumisesta VLOOKUPin kanssa .

Video: Kuinka käyttää VLOOKUPia taulukoiden yhdistämiseen .

11. VLOOKUP voi luokitella tai luokitella tietoja

Jos sinun on joskus sovellettava mielivaltaisia ​​luokkia tietotietueisiin, voit tehdä sen helposti VLOOKUPin avulla käyttämällä taulukkoa, joka toimii 'avaimena' luokkien määrittämiseen.

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

VLOOKUP käytetään luokitteluun - arvosanojen antamiseen

Tässä tapauksessa VLOOKUP on määritetty likimääräiselle osumalle, joten on tärkeää, että taulukko on järjestetty nousevaan järjestykseen.

Mutta voit myös käyttää VLOOKUPia mielivaltaisten luokkien määrittämiseen. Alla olevassa esimerkissä laskemme VLOOKUP: n avulla kunkin osaston ryhmän käyttämällä pientä taulukkoa (nimeltä 'avain'), joka määrittelee ryhmittelyn.

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

12. Absoluuttiset viittaukset 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 hakuarvoon ja taulukkotaulukkoon. Tämän avulla voit kopioida kaavan ja muuttaa sitten vain sarakkeen hakemistonumeroa käyttämään samaa hakua arvon saamiseksi toisesta sarakkeesta.

Esimerkiksi, koska hakuarvo ja taulukkotaulukko ovat absoluuttisia, voimme kopioida kaavan sarakkeiden yli ja palata sitten takaisin ja muuttaa sarakehakemistoa tarpeen mukaan.

Absoluuttiset viittaukset tekevät VLOOKUP -kaavoista kannettavampia

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

Absoluuttiset alueet näyttävät melko rumailta, joten ne voivat tehdä VLOOKUP -kaavoistasi paljon puhtaampia ja helpommin luettavia korvaamalla absoluuttiset viittaukset nimetyillä alueilla, jotka ovat automaattisesti absoluuttisia.

Esimerkiksi yllä olevassa työntekijätietoesimerkissä voit nimetä syöttösolun 'id' ja sitten nimetä taulukon tiedot 'data', voit kirjoittaa kaavan seuraavasti:

Nimetyt alueet helpottavat VLOOKUP -kaavojen lukemista

Tämä kaava on paitsi helpompi lukea, myös kannettavampi, koska nimetyt alueet ovat automaattisesti absoluuttisia.

14. Sarakkeen lisääminen voi rikkoa olemassa olevat VLOOKUP -kaavat

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

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

Sarakkeen lisääminen taulukkoon voi rikkoa VLOOKUP -toiminnon

Tämän ongelman välttämiseksi voit laskea sarakeindeksin seuraavien kahden vihjeen mukaisesti.

15. Voit laskea sarakeindeksin ROW- tai COLUMN -painikkeilla

Jos olet sellainen tyyppi, jota kaikki muokkaukset häiritsevät kaavan kopioinnin jälkeen, voit luoda dynaamisia sarakeindeksejä käyttämällä joko ROW- tai COLUMN -näppäintä. Jos saat tietoja peräkkäisistä sarakkeista, voit tämän temppun avulla määrittää yhden VLOOKUP -kaavan ja kopioida sen ilman muutoksia.

Esimerkiksi alla olevien työntekijöiden tietojen avulla voimme luoda COLUMN -funktion avulla dynaamisen sarakeindeksin. Solun C3 ensimmäisessä kaavassa COLUMN palauttaa itsessään 3 (koska sarake C on laskentataulukon kolmas), joten meidän on yksinkertaisesti vähennettävä yksi ja kopioitava kaava:

Esimerkki sarakkeen COLUMN käyttämisestä VLOOKUP -sarakeindeksin laskemiseen

Kaikki kaavat ovat identtisiä ilman jälkimuokkausta.

Käyttämämme kaava on seuraava:

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

16. Käytä VLOOKUP + MATCH täysin dynaamista sarakehakemistoa varten

Kun otat yllä olevan vihjeen askeleen pidemmälle, voit etsiä sarakkeen sijainnin taulukosta ja palauttaa täysin dynaamisen sarakeindeksin käyttämällä MATCH -toimintoa.

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

Esimerkki olisi etsiä myyjän myyntiä tiettynä kuukautena tai etsiä tietyn tuotteen hinta tietyltä toimittajalta.

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

VLOOKUP kaksisuuntainen haku - kuinka etsiä kuukausi?

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

VLOOKUP kaksisuuntainen haku käyttämällä MATCH -sarakehakemistoa

Huomaa, että annamme täsmälleen alueen, joka sisältää kaikki taulukon sarakkeet, jotta VLOOKUP: n käyttämät sarakenumerot 'synkronoidaan'.

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

Huomaa: näet usein kaksisuuntaisia ​​hakuja INDEX- ja MATCH -tekniikoilla. Tämä lähestymistapa tarjoaa enemmän joustavuutta ja paremman suorituskyvyn suurille tietojoukoille. Katso miten tästä nopeasta videosta: Kuinka tehdä kaksisuuntainen haku INDEXin ja MATCHin avulla .

17. VLOOKUP sallii jokerimerkinnät osittaiseen hakuun

Aina kun käytät VLOOKUPia tarkan haun tilassa, voit käyttää yleismerkkejä hakuarvossa. Se voi tuntua vastustamattomalta, mutta yleismerkkien avulla voit tehdä tarkan osuman osittaisen osuman perusteella :)

Excel sisältää kaksi jokerimerkkiä: tähti (*) vastaa yhtä tai useampaa merkkiä ja kysymysmerkki (?) Vastaa yhtä merkkiä.

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

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

Kaava tässä tapauksessa on yksinkertainen:

 
= VLOOKUP (val,data,1,0)

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

VLOOKUP ja jokerimerkit - tähti yhdistetään hakuarvoon

Tässä tapauksessa yhdistämme tähden hakuarvoon VLOOKUP -toiminnon sisällä:

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

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

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

Tarkan haun tilassa VLOOKUP näyttää virheen #N/A, jos vastaavuutta ei löydy. Eräällä tavalla tämä on hyödyllistä, koska se kertoo sinulle lopullisesti, että hakutaulukossa ei ole vastaavuutta. Kuitenkin #N/A -virheitä ei ole kovin hauska katsoa, ​​joten voit saada useita virheitä ja näyttää jotain muuta sen sijaan useilla tavoilla.

Kun aloitat VLOOKUPin käytön, sinun on ehdottomasti kohdattava virhe #N/A, joka ilmenee, kun VLOOKUP ei löydä vastaavuutta.

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 antaa #N/A -virheen

VLOOKUP, jossa on #N/A -virhe

Tässä tapauksessa kaava on täysin tavallinen täsmäytys:

 
= VLOOKUP (E6,data,2,0)

Kuitenkin #N/A -virheitä ei ole kovin hauska katsoa, ​​joten sinun kannattaa ehkä havaita tämä virhe ja näyttää ystävällisempi viesti.

Helpoin tapa vangita virheet VLOOKUPilla on kääriä VLOOKUP IFERROR -toimintoon. IFERRORin avulla voit 'havaita' kaikki virheet ja palauttaa valitsemasi tuloksen.

Jos haluat vangita tämän virheen ja näyttää 'ei löydy' -viestin virheen sijasta, voit yksinkertaisesti kääriä alkuperäiskaavan IFERRORin sisään ja asettaa haluamasi tuloksen:

VLOOKUP #N/A virhe jumissa IFERRORin kanssa

Jos hakuarvo löytyy, virhe ei ilmene ja VLOOKUP -toiminto palauttaa normaalin tuloksen. Tässä on kaava:

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

19. Numerot tekstinä voivat aiheuttaa osumavirheen

Joskus taulukko, jonka kanssa käytät VLOOKUP -ohjelmaa, saattaa sisältää tekstinä syötettyjä numeroita. Jos haet vain numeroita tekstinä taulukon sarakkeesta, sillä ei ole väliä. Mutta jos taulukon ensimmäinen sarake sisältää tekstinä syötettyjä numeroita, saat #N/A -virheen, jos hakuarvo ei myöskään ole tekstiä.

Seuraavassa esimerkissä planeetataulukon tunnukset ovat numeroita kirjoitettu tekstinä , joka saa VLOOKUP palauttamaan virheen, koska hakuarvo on määrä 3:

Esimerkit tekstin VLOOKUP -virhevirheestä

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ä pastata.

Jos lähdetaulukkoa ei ole helppo hallita, voit myös säätää VLOOKUP -kaavaa, jotta hakuarvo muutetaan tekstiksi yhdistämällä '' arvoksi seuraavasti:

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

Numerot syötetty tekstinä VLOOKUP -virheratkaisu

Jos et voi olla varma, milloin sinulla on numeroita ja milloin sinulla on tekstiä, voit vastata molempiin vaihtoehtoihin kääntämällä VLOOKUP IFERROR -kenttään ja kirjoittamalla kaavan molempien tapausten käsittelyä varten:

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

20. Voit käyttää VLOOKUP -toimintoa sisäkkäisten IF -käskyjen korvaamiseen

Yksi VLOOKUPin mielenkiintoisimmista käyttötarkoituksista on sisäkkäisten IF -käskyjen korvaaminen. Jos olet koskaan rakentanut sarjan sisäkkäisiä IF: itä, tiedät, että ne toimivat hyvin, mutta vaativat vähän sulkeita. Sinun on myös oltava varovainen järjestyksessäsi, jossa työskentelet, jotta et aiheuta loogista virhettä.

Esimerkiksi sisäkkäisten sijoitusrahastojen yleinen käyttö on arvosanojen antaminen jonkinlaisen pistemäärän perusteella. Alla olevassa esimerkissä näet, että kaava on rakennettu sisäkkäisillä IF: llä juuri niin, käyttämällä oppaana oikealla olevaa arvosana -avainta.

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

Koko 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 pisteet leivotaan suoraan kaavaan. Jos pisteet muuttuvat jostain syystä, sinun on päivitettävä yksi kaava huolellisesti ja kopioitava se sitten koko taulukkoon.

Sitä vastoin VLOOKUP voi antaa samat arvosanat yksinkertaisella kaavalla. Sinun tarvitsee vain varmistaa, että arvosanojen taulukko on määritetty VLOOKUPille (eli se on useimmiten lajiteltu pisteiden mukaan ja sisältää sulkeet kaikkien pisteiden käsittelyyn).

Kun VLOOKUP -kaava on määritetty luokan avaintataulukolle nimetty alue -avain, se on hyvin yksinkertainen ja generoi samat arvosanat kuin alkuperäinen sisäkkäinen IF -kaava:

Arvosanojen antaminen yksinkertaisella VLOOKUP -kaavalla

Luokkaavaintaulukolla nimeltä 'avain' 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 arvosanojen taulukkoon. 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 VLOOKUPilla

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

Suunnitelman mukaan VLOOKUP voi löytää vain yhden kriteerin perusteella arvot, jotka toimitetaan hakuarvona, joka löytyy taulukon ensimmäisestä sarakkeesta (hakusarake).

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

On kuitenkin olemassa tapoja voittaa tämä rajoitus. Yksi kiertotapa on luoda apusarake, joka yhdistää eri sarakkeiden arvot ja luo hakuarvoja, jotka toimivat kuin useat ehdot. Esimerkiksi tässä haluamme löytää työntekijän osaston ja ryhmän, mutta etu- ja sukunimi näkyvät erillisissä sarakkeissa. Kuinka voimme etsiä molemmat kerralla?

VLOOKUP useiden kriteerien ongelma - kuinka etsiä sekä etu- että sukunimi?

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

VLOOKUP useiden ehtojen vaihe 2 - lisää auttajasarake, 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 useiden ehtojen vaihe 3 - muodosta hakuehdot yhdistämällä kriteerit

Lopullinen VLOOKUP -kaava etsii etu- ja sukunimet yhdessä käyttäen apulaista saraketta avaimena:

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

22. Kaksi VLOOKUPSia ovat nopeampia kuin yksi VLOOKUP

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

Tausta: kuvittele, että sinulla on paljon tilaustietoja, esimerkiksi yli 10000 tietuetta ja käytät VLOOKUP -toimintoa tilauksen kokonaismäärän etsimiseen tilaustunnuksen perusteella. Käytät siis jotain tällaista:

 
= VLOOKUP (order_id,order_data, 5, FALSE)

Lopussa oleva EPÄTOSI pakottaa VLOOKUPin tekemään tarkan ottelun. Haluat tarkan vastaavuuden, koska on mahdollista, että tilausnumeroa ei löydy. Tässä tapauksessa tarkan haun asetus saa VLOOKUP palauttamaan virheen #N/A.

Ongelmana on, että tarkat vastaavuudet ovat todella hitaita, koska Excelin on edettävä lineaarisesti kaikkien arvojen läpi, kunnes se löytää osuman tai ei.

Sitä vastoin likimääräiset ottelut ovat salamannopeita, koska Excel pystyy tekemään ns binaarihaku .

Ongelma binäärihauissa (eli VLOOKUP likimääräisessä hakutilassa) on kuitenkin se, että VLOOKUP voi palauttaa väärän tuloksen, kun arvoa ei löydy. Mikä pahempaa, tulos saattaa näyttää täysin normaalilta, joten sen havaitseminen voi olla erittäin vaikeaa.

Ratkaisu on käyttää VLOOKUPia kahdesti, molemmilla kerroilla likimääräisessä ottelutilassa. Ensimmäinen tapaus yksinkertaisesti tarkistaa, että arvo on todella olemassa. Jos näin on, suoritetaan toinen VLOOKUP (jälleen likimääräisessä hakutilassa) haluttujen tietojen noutamiseksi. Jos ei, voit palauttaa minkä tahansa arvon, jonka haluat ilmoittaa, että tulosta ei löytynyt.

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

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

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

Huomautus: Tietosi on lajiteltava käyttääksesi tätä temppua. Se on yksinkertaisesti tapa suojautua puuttuvilta hakuarvoilta samalla, kun haku on nopea.

23. INDEX 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äittely voi kuumentua yllättävän paljon :)

Pääasia on tämä: INDEX + MATCH voi tehdä kaiken, mitä VLOOKUP (ja HLOOKUP) voi tehdä, paljon enemmän joustavuutta ja hieman monimutkaisemman hinnan. Joten INDEX + MATCH: n kannattajat väittävät (erittäin järkevästi), että voit yhtä hyvin aloittaa INDEXin ja MATCHin oppimisen, koska se antaa sinulle paremman työkalusarjan.

Väite INDEX + MATCHia vastaan ​​on, että se vaatii kaksi toimintoa yhden sijasta, joten käyttäjien (erityisesti uusien käyttäjien) on luonteeltaan monimutkaisempaa oppia ja hallita niitä.

Kaksi senttiä on, että jos käytät Exceliä usein, haluat oppia käyttämään INDEX- ja MATCH -sovelluksia. Se on erittäin voimakas yhdistelmä.

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

Jos haluat lisätietoja INDEXistä ja MATCHista, katso tämä artikkeli .

Kirjailija Dave Bruns


^