VLOOKUP -toiminto - Johdanto
VLOOKUP -toiminto on vertailuarvo.
Tiedät jotain Excelissä, jos osaat käyttää VLOOKUP -toimintoa.
Jos et, sinun on parempi olla luettelematta Excelä yhdeksi vahvista alueistasi ansioluettelossasi.
Olen ollut mukana paneelihaastatteluissa, joissa heti kun ehdokas mainitsi Excelin osaamisalueenaan, ensimmäinen kysymys oli - sait sen - VLOOKUP -toiminto.
Nyt kun tiedämme, kuinka tärkeä tämä Excel -toiminto on, on järkevää ässyttää se kokonaan voidakseen ylpeänä sanoa - "Tiedän yhden asian Excelissä".
Tästä tulee massiivinen VLOOKUP -opetusohjelma (standardieni mukaan).
Käsittelen kaikkea siitä tiedettävää ja näytän sitten hyödyllisiä ja käytännöllisiä VLOOKUP -esimerkkejä.
Joten solki kiinni.
On nousun aika.
Milloin VLOOKUP -toimintoa käytetään Excelissä?
VLOOKUP -toiminto sopii parhaiten tilanteisiin, joissa etsit vastaavaa datapistettä sarakkeesta ja kun vastaava datapiste löytyy, siirryt kyseisen rivin oikealle puolelle ja haet arvon solusta, joka on määritetty määrä sarakkeet oikealle.
Otetaan tässä yksinkertainen esimerkki ymmärtääksemme, milloin käyttää Vlookupia Excelissä.
Muista, kun tenttipisteluettelo oli ulkona ja liitetty ilmoitustaululle, ja kaikilla oli tapana tulla hulluksi löytääkseen nimensä ja pisteet (ainakin näin tapahtui, kun olin koulussa).
Näin se toimi:
- Menet ilmoitustaululle ja alat etsiä nimeäsi tai ilmoittautumisnumeroa (liikutat sormeasi ylhäältä alas luettelossa).
- Heti kun huomaat nimesi, siirrät silmäsi nimen/ilmoittautumisnumeron oikealle puolelle nähdäksesi tulokset.
Ja juuri sitä Excel VLOOKUP -toiminto tekee sinulle (voit käyttää tätä esimerkkiä seuraavassa haastattelussa).
VLOOKUP -toiminto etsii määritettyä arvoa sarakkeesta (yllä olevassa esimerkissä se oli sinun nimesi) ja kun se löytää määritetyn vastaavuuden, se palauttaa arvon samalla rivillä (saamasi merkit).
Syntaksi
= VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
Syöttöargumentit
- lookup_value - tämä on hakuarvo, jota yrität löytää taulukon vasemmasta sarakkeesta. Se voi olla arvo, soluviittaus tai tekstimerkkijono. Pistetaulukkoesimerkissä tämä olisi nimesi.
- pöytäryhmä - tämä on taulukko, josta etsit arvoa. Tämä voi olla viittaus solualueeseen tai nimettyyn alueeseen. Pistetaulukkoesimerkissä tämä olisi koko taulukko, joka sisältää pisteet jokaiselle jokaiselle aiheelle
- col_index - tämä on sarakeindeksinumero, josta haluat hakea vastaavan arvon. Jos haluat tuloslaskelmaesimerkissä, että haluat matematiikan tulokset (joka on pisteet sisältävät taulukon ensimmäinen sarake), katso sarakkeesta 1. Jos haluat fysiikan pisteet, katso sarakkeesta 2.
- [range_lookup] - tässä määrität, haluatko tarkan haun vai likimääräisen osuman. Jos se jätetään pois, sen oletusarvo on TOSI - likimääräinen vastaavuus (katso lisätietoja alla).
Lisätietoja (Tylsää, mutta tärkeää tietää)
- Osuma voi olla tarkka (EPÄTOSI tai 0 alueen_näkymässä) tai likimääräinen (TOSI tai 1).
- Varmista likimääräisessä haussa, että luettelo on järjestetty nousevaan järjestykseen (ylhäältä alas), tai muuten tulos voi olla epätarkka.
- Kun range_lookup on TOSI (likimääräinen haku) ja tiedot lajitellaan nousevaan järjestykseen:
- Jos VLOOKUP -toiminto ei löydä arvoa, se palauttaa suurimman arvon, joka on pienempi kuin lookup_value.
- Se palauttaa virheen #N/A, jos haun_arvo on pienempi kuin pienin arvo.
- Jos lookup_value on teksti, voidaan käyttää jokerimerkkejä (katso alla oleva esimerkki).
Nyt toivoen, että sinulla on perustiedot siitä, mitä VLOOKUP -toiminto voi tehdä, kuori tämä sipuli ja katso joitain käytännön esimerkkejä VLOOKUP -toiminnosta.
10 Excel VLOOKUP -esimerkkiä (perus- ja edistynyt)
Tässä on 10 hyödyllistä esimerkkiä Excel Vlookupin käytöstä, jotka osoittavat, kuinka voit käyttää sitä päivittäisessä työssäsi.
Esimerkki 1 - Bradin matemaattisten pisteiden löytäminen
Alla olevassa VLOOKUP-esimerkissä minulla on luettelo opiskelijoiden nimistä vasemmassa sarakkeessa ja merkinnät eri aiheista sarakkeissa B-E.
Nyt ryhdytään töihin ja käytetään VLOOKUP -toimintoa parhaiten. Edellä olevista tiedoista minun on tiedettävä, kuinka paljon Brad teki matematiikan.
Edellä olevista tiedoista minun on tiedettävä, kuinka paljon Brad teki matematiikan.
Tässä on VLOOKUP -kaava, joka palauttaa Bradin matemaattiset pisteet:
= VLOOKUP ("Brad", $ A $ 3: $ E $ 10,2,0)
Yllä olevassa kaavassa on neljä argumenttia:
- "Brad: - tämä on hakuarvo.
- $ A $ 3: $ E $ 10 - Tämä on solualue, josta etsimme. Muista, että Excel etsii hakuarvoa vasemmasta sarakkeesta. Tässä esimerkissä se etsisi nimeä Brad kohdasta A3: A10 (joka on määritetyn taulukon vasen sarake).
- 2 - Kun toiminto havaitsee Bradin nimen, se siirtyy taulukon toiseen sarakkeeseen ja palauttaa arvon samalla rivillä kuin Brad. Arvo 2 osoitti tässä, että etsimme pisteytystä määritetyn taulukon toisesta sarakkeesta.
- 0 - tämä kertoo VLOOKUP -toiminnolle, että se etsii vain tarkkoja vastaavuuksia.
Näin VLOOKUP -kaava toimii yllä olevassa esimerkissä.
Ensinnäkin se etsii Brad-arvoa vasemmasta sarakkeesta. Se kulkee ylhäältä alas ja löytää arvon solusta A6.
Heti kun se löytää arvon, se siirtyy toisessa sarakkeessa oikealle ja hakee sen arvon.
Voit käyttää samaa kaavarakennetta saadaksesi kenenkään arvosanat mistä tahansa aiheesta.
Esimerkiksi löytääksesi Marian merkit kemiasta, käytä seuraavaa VLOOKUP -kaavaa:
= VLOOKUP ("Maria", $ A $ 3: $ E $ 10,4,0)
Yllä olevassa esimerkissä hakuarvo (oppilaan nimi) kirjoitetaan lainausmerkkeihin. Voit myös käyttää soluviittausta, joka sisältää hakuarvon.
Soluviittauksen hyödyksi on se, että se tekee kaavasta dynaamisen.
Jos sinulla on esimerkiksi solu, jossa on oppilaan nimi ja haet matematiikan pisteitä, tulos päivittyy automaattisesti, kun muutat oppilaan nimeä (kuten alla):
Jos syötät hakuarvon, jota ei löydy vasemmasta sarakkeesta, se palauttaa virheen #N/A.
Esimerkki 2 - Kaksisuuntainen haku
Yllä olevassa esimerkissä 1 koodasimme sarakkeen arvon. Näin ollen kaava palauttaa aina matematiikan pisteet, koska olemme käyttäneet sarakkeen indeksinumerona 2.
Mutta entä jos haluat tehdä sekä VLOOKUP -arvon että sarakeindeksin numeron dynaamisiksi. Esimerkiksi, kuten alla on esitetty, voit muuttaa joko opiskelijan nimen tai aiheen nimen, ja VLOOKUP -kaava hakee oikean pistemäärän. Tämä on esimerkki kaksisuuntaisesta VLOOKUP-kaavasta.
Tämä on esimerkki kaksisuuntaisesta VLOOKUP-toiminnosta.
Jotta voit tehdä tämän kaksisuuntaisen hakukaavan, sinun on myös tehtävä sarakkeesta dynaaminen. Joten kun käyttäjä vaihtaa aihetta, kaava valitsee automaattisesti oikean sarakkeen (2 matematiikan tapauksessa, 3 fysiikan tapauksessa jne.).
Tätä varten sinun on käytettävä MATCH -funktiota sarakeargumenttina.
Tässä on VLOOKUP -kaava, joka tekee tämän:
= VLOOKUP (G4, $ A $ 3: $ E $ 10, MATCH (H3, $ A $ 2: $ E $ 2,0), 0)
Yllä oleva kaava käyttää sarakkeen numerona MATCH (H3, $ A $ 2: $ E $ 2,0). MATCH -toiminto ottaa aiheen nimen hakuarvoksi (H3: ssa) ja palauttaa sijaintinsa A2: E2: ssa. Näin ollen, jos käytät matematiikkaa, se palauttaa 2, koska matematiikka löytyy B2: sta (joka on määritetyn matriisialueen toinen solu).
Esimerkki 3 - Pudotusvalikoiden käyttäminen hakuarvoina
Yllä olevassa esimerkissä meidän on syötettävä tiedot manuaalisesti. Se voi olla aikaa vievää ja virhealtista, varsinkin jos sinulla on valtava hakuarvojen luettelo.
Tällaisissa tapauksissa hyvä idea on luoda avattava luettelo hakuarvoista (tässä tapauksessa se voi olla opiskelijoiden nimet ja oppiaineet) ja valita sitten yksinkertaisesti luettelosta.
Valinnan perusteella kaava päivittää tuloksen automaattisesti.
Jotain alla olevan kuvan mukaisesti:
Tämä on hyvä koontinäyttökomponentti, koska sinulla voi olla valtava tietojoukko, jossa on satoja oppilaita takapäässä, mutta loppukäyttäjä (esimerkiksi opettaja) voi nopeasti saada oppilaan arvosanat yksinkertaisesti tekemällä valinnat pudotusvalikko.
Kuinka tehdä tämä:
Tässä tapauksessa käytetty VLOOKUP -kaava on sama kuin esimerkissä 2.
= VLOOKUP (G4, $ A $ 3: $ E $ 10, MATCH (H3, $ A $ 2: $ E $ 2,0), 0)
Hakuarvot on muutettu avattaviksi luetteloiksi.
Voit luoda avattavan luettelon seuraavasti:
- Valitse solu, johon haluat avattavan luettelon. Tässä esimerkissä G4: ssä haluamme oppilaiden nimet.
- Siirry kohtaan Data -> Datatyökalut -> Tietojen vahvistus.
- Valitse Tietojen validointi -valintaikkunan asetusvälilehden avattavasta Salli-luettelosta Lista.
- Valitse lähteestä $ A $ 3: $ A $ 10
- Napsauta OK.
Nyt sinulla on avattava luettelo solussa G4. Voit myös luoda sellaisen H3: ssa aiheille.
Esimerkki 4 - Kolmitiehaku
Mikä on kolmisuuntainen haku?
Esimerkissä 2 olemme käyttäneet yhtä hakutaulukkoa, jossa on pisteitä eri oppiaineiden opiskelijoille. Tämä on esimerkki kaksisuuntaisesta hausta, kun käytämme pisteiden noutamiseen kahta muuttujaa (opiskelijan nimi ja aiheen nimi).
Oletetaan nyt, että vuoden kuluttua opiskelijalla on kolme eri tasoa kokeita, yksikkötesti, välitesti ja loppukokeet (näin minulla oli opiskellessani).
Kolmitiehaku olisi kyky saada oppilaan arvosanat tietystä aiheesta tietystä tenttitasosta.
Jotain alla olevan kuvan mukaisesti:
Yllä olevassa esimerkissä VLOOKUP -toiminto voi etsiä kolmesta eri taulukosta (yksikkötesti, välitesti ja loppukokeen) ja palauttaa määrätyn oppilaan pisteet.
Tässä on solussa H4 käytetty kaava:
= VLOOKUP (G4, CHOOSE (IF (H2 = "Unit Test", 1, IF (H2 = "Midterm", 2,3))), $ A $ 3: $ E $ 7, $ A $ 11: $ E $ 15, $ A $ 19: $ E $ 23), MATCH (H3, $ A $ 2: $ E $ 2,0), 0)
Tämä kaava käyttää VALITSE -toimintoa varmistaakseen, että viitataan oikeaan taulukkoon. Analysoidaan kaavan VALITSE -osa:
VALITSE (JOS (H2 = ”Yksikkötesti”, 1, JOS (H2 = ”Väliaika”, 2,3))), $ A $ 3: $ E $ 7, $ A $ 11: $ E $ 15, $ A $ 19: $ E $ 23 )
Kaavan ensimmäinen argumentti on JOS (H2 = ”Yksikkötesti”, 1, JOS (H2 = ”Väliaika”, 2,3)), joka tarkistaa solun H2 ja näkee, mihin kokeeseen viitataan. Jos se on yksikkötesti, se palauttaa $ 3 $: $ 7 $, jolla on yksikkötestin pisteet. Jos se on puolivälissä, se palauttaa $ 11 $: $ 15 $, muuten se palauttaa $ 19 $: $ 23 $.
Tämä tekee VLOOKUP-taulukkotaulukosta dynaamisen ja tekee siitä kolmisuuntaisen haun.
Esimerkki 5 - Viimeisen arvon saaminen luettelosta
Voit luoda VLOOKUP -kaavan saadaksesi viimeisen numeerisen arvon luettelosta.
Suurin positiivinen luku, jota voit käyttää Excelissä, on 9.99999999999999E+307. Tämä tarkoittaa myös sitä, että VLOOKUP -numeron suurin haunumero on sama.
En usko, että tarvitsisit koskaan mitään laskemista, joka käsittää näin suuren määrän. Ja juuri sitä voimme käyttää luettelon viimeisen numeron saamiseen.
Oletetaan, että sinulla on tietojoukko (A1: A14) alla olevan kuvan mukaisesti ja haluat saada luettelon viimeisen numeron.
Tässä on kaava, jota voit käyttää:
= VLOOKUP (9.99999999999999E+307, $ A $ 1: $ A $ 14,TOTTA)
Huomaa, että yllä oleva kaava käyttää likimääräistä vastaavuutta VLOOKUP (huomaa kaavan lopussa TOSI, epätosi tai 0 sijaan). Huomaa myös, että luetteloa ei tarvitse lajitella, jotta tämä VLOOKUP -kaava toimisi.
Näin likimääräinen VLOOKUP -toiminto toimii. Se skannaa vasemmanpuoleisimman sarakkeen ylhäältä alas.
- Jos se löytää tarkan vastaavuuden, se palauttaa arvon.
- Jos se löytää arvon, joka on suurempi kuin hakuarvo, se palauttaa arvon sen yläpuolella olevassa solussa.
- Jos hakuarvo on suurempi kuin kaikki luettelon arvot, se palauttaa viimeisen arvon.
Yllä olevassa esimerkissä kolmas skenaario on käynnissä.
Siitä asti kun 9.99999999999999E+307 on suurin numero, jota voidaan käyttää Excelissä, kun tätä käytetään hakuarvona, se palauttaa luettelon viimeisen numeron.
Samalla tavalla voit käyttää sitä myös viimeisen tekstikohteen palauttamiseen luettelosta. Tässä on kaava, jolla se voidaan tehdä:
= VLOOKUP ("zzz", $ A $ 1: $ A $ 8,1,TOTTA)
Sama logiikka seuraa. Excel etsii kaikki nimet läpi, ja koska zzz: n katsotaan olevan suurempi kuin mikään nimi/teksti, joka alkaa aakkosilla ennen zzz: tä, se palauttaisi luettelon viimeisen kohteen.
Esimerkki 6 - Osittainen haku käyttämällä jokerimerkkejä ja VLOOKUP
Excel -yleismerkit voivat olla todella hyödyllisiä monissa tilanteissa.
Se on se maaginen juoma, joka antaa kaavoillesi supervoimia.
Osittainen haku on tarpeen, kun sinun on etsittävä arvo luettelosta, eikä tarkkaa vastaavuutta ole.
Oletetaan esimerkiksi, että sinulla on alla olevan kuvan mukainen tietojoukko ja haluat etsiä luettelosta yrityksen ABC, mutta luettelossa on ABC Ltd ABC: n sijaan.
Et voi käyttää ABC: tä hakuarvona, koska sarakkeessa A ei ole tarkkaa vastaavuutta. Arvioitu vastaavuus johtaa myös virheellisiin tuloksiin ja edellyttää, että luettelo on lajiteltava nousevaan järjestykseen.
Voit kuitenkin käyttää VLOOKUP -toiminnon sisällä olevaa jokerimerkkiä haun saamiseksi.
Kirjoita seuraava kaava soluun D2 ja vedä se muihin soluihin:
= VLOOKUP ("*" & C2 & "*", $ A $ 2: $ A $ 8,1, FALSE)
Kuinka tämä kaava toimii?
Yllä olevassa kaavassa sen sijaan, että käytettäisiin hakuarvoa sellaisenaan, sen molemmin puolin on yleismerkki tähti (*) - “*” & C2 & ”*”
Tähti on Excelin yleismerkki ja voi edustaa mitä tahansa määrää merkkejä.
Tähden käyttäminen hakuarvon molemmilla puolilla kertoo Excelille, että sen on etsittävä tekstiä, joka sisältää sanan C2: sta. Siinä voi olla mikä tahansa määrä merkkejä ennen tai jälkeen C2: n tekstin.
Esimerkiksi solussa C2 on ABC, joten VLOOKUP -toiminto etsii A2: A8: n nimet ja etsii ABC: tä. Se löytää osuman solusta A2, koska se sisältää ABC: n ABC Ltd: ssä. Ei ole väliä, onko ABC: n vasemmalla vai oikealla puolella merkkejä. Ennen kuin tekstimerkkijonossa on ABC, sitä pidetään osumana.
Huomautus: VLOOKUP -toiminto palauttaa aina ensimmäisen vastaavan arvon ja lakkaa etsimästä kauemmas. Joten jos sinulla on ABC Oy., ja ABC Corporation luettelossa, se palauttaa ensimmäisen ja jättää huomiotta loput.
Esimerkki 7 - VLOOKUP Virheen palauttaminen haun arvon vastaavuudesta huolimatta
Se voi saada sinut hulluksi, kun huomaat, että hakuarvo on vastaava ja VLOOKUP -toiminto palauttaa virheen.
Esimerkiksi alla olevassa tapauksessa on täsmäys (Matt), mutta VLOOKUP -funktio palauttaa edelleen virheen.
Nyt kun voimme nähdä ottelun olevan olemassa, emme voi nähdä paljaalla silmällä sitä, että siellä voi olla edessä tai takana olevia välilyöntejä. Jos sinulla on näitä ylimääräisiä välilyöntejä hakuarvojen edessä, jälkeen tai niiden välissä, se EI ole tarkka vastaavuus.
Näin on usein silloin, kun tuot tietoja tietokannasta tai hankit ne joltain muulta. Näillä johtavilla/perillä olevilla tiloilla on taipumus hiipiä sisään.
Ratkaisu on TRIM -toiminto. Se poistaa kaikki edessä tai takana olevat välilyönnit tai ylimääräiset välilyönnit sanojen välillä.
Tässä on kaava, joka antaa sinulle oikean tuloksen.
= VLOOKUP ("Matt", TRIM ($ A $ 2: $ A $ 9), 1,0)
Koska tämä on taulukkokaava, käytä Ctrl + Vaihto + Enter vain Enter -näppäimen sijaan.
Toinen tapa voisi olla käsitellä ensin hakusarjaasi TRIM -toiminnolla varmistaaksesi, että kaikki lisätilat ovat poissa, ja sitten käyttää VLOOKUP -toimintoa tavalliseen tapaan.
Esimerkki 8 - Kirjainkokoherkän haun tekeminen
Oletuksena VLOOKUP -toiminnon hakuarvo ei erota kirjainkokoa. Jos esimerkiksi hakuarvo on MATT, matt tai Matt, se on sama VLOOKUP -toiminnolle. Se palauttaa ensimmäisen vastaavan arvon tapauksesta riippumatta.
Mutta jos haluat tehdä kirjainkoolla merkitsevän haun, sinun on käytettävä EXACT-toimintoa VLOOKUP-toiminnon kanssa.
Tässä on esimerkki:
Kuten näette, on kolme solua, joilla on sama nimi (A2, A4 ja A5), mutta joilla on eri aakkoset. Oikealla on kolme nimeä (Matt, MATT ja matt) sekä niiden tulokset matematiikassa.
Nyt VLOOKUP-toiminto ei ole varustettu käsittelemään kirjainkokoherkkiä hakuarvoja. Tässä yllä olevassa esimerkissä se palauttaisi aina 38, mikä on Mattin pisteet A2: ssa.
Jotta kirjainkoolla olisi merkitystä, meidän on käytettävä apupylvästä (kuten alla):
Jos haluat saada arvot auttaja -sarakkeesta, käytä = ROW () -funktiota. Se yksinkertaisesti saa rivin numeron soluun.
Kun sinulla on auttaja-sarake, tässä on kaava, joka antaa kirjainkokoherkän hakutuloksen.
= VLOOKUP (MAX (EXACT (E2, $ A $ 2: $ A $ 9)*(ROW ($ A $ 2: $ A $ 9))), $ B $ 2: $ C $ 9,2,0)
Nyt hajotetaan ja ymmärretään, mitä tämä tekee:
- TÄYDELLINEN (E2, $ A $ 2: $ A $ 9) - Tässä osassa vertaillaan E2: n hakuarvoa kaikkiin A2: A9: n arvoihin. Se palauttaa joukon TOSIA/EPÄTOSIA, joissa TOSI palautetaan, jos täsmällinen vastaavuus on. Tässä tapauksessa se palauttaisi seuraavan taulukon: {TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}.
- EXACT (E2, $ A $ 2: $ A $ 9)*(ROW ($ A $ 2: $ A $ 9) - Tämä osa kertoo TRUE/FALSE -matriisin rivinumerolla. Jos on TOSI, se antaa rivin numeron , muuten se antaa arvon 0. Tässä tapauksessa se palauttaisi {2; 0; 0; 0; 0; 0; 0; 0}.
- MAX (EXACT (E2, $ A $ 2: $ A $ 9)*(RIVI ($ A $ 2: $ A $ 9)))) - Tämä osa palauttaa suurimman arvon numerojärjestyksestä. Tässä tapauksessa se palauttaa 2 (joka on rivinumero, jossa on tarkka vastaavuus).
- Käytämme nyt vain tätä numeroa hakuarvona ja käytä hakuvaihtoehtoa B2: C9
Huomautus: Koska tämä on taulukkokaava, käytä näppäinyhdistelmää Ctrl + Vaihto + Enter kirjoittamisen sijaan.
Esimerkki 9 - VLOOKUPin käyttö useiden ehtojen kanssa
Excel VLOOKUP -toiminto voi perusmuodossaan etsiä yhden hakuarvon ja palauttaa vastaavan arvon määritetystä rivistä.
Mutta usein on tarpeen käyttää VLOOKUPia Excelissä useilla kriteereillä.
Oletetaan, että sinulla on tietoja, joissa on opiskelijoiden nimi, koketyyppi ja matematiikkapisteet (kuten alla):
VLOOKUP -toiminnon käyttäminen kunkin oppilaan matematiikkapistemäärän saamiseksi vastaavalle tentitasolle voi olla haaste.
Jos esimerkiksi yrität käyttää VLOOKUPia Mattin kanssa hakuarvona, se palauttaa aina 91, joka on pistemäärä ensimmäisen Mattin esiintymisestä luettelossa. Jos haluat saada Mattin pistemäärän kustakin tenttityypistä (yksikkötesti, väliaika ja lopullinen), sinun on luotava ainutlaatuinen hakuarvo.
Tämä voidaan tehdä käyttämällä apupylvästä. Ensimmäinen askel on lisätä auttajasarake pisteiden vasemmalle puolelle.
Luo nyt yksilöllinen tarkentaja jokaiselle nimen esiintymälle käyttämällä seuraavaa kaavaa kohdassa C2: = A2 & ”|” & B2
Kopioi tämä kaava kaikkiin auttajasarakkeen soluihin. Tämä luo yksilöllisiä hakuarvoja jokaiselle nimen esiintymälle (kuten alla):
Nyt kun nimiä toistettiin, ei toistoa tapahdu, kun nimi yhdistetään tutkinnon tasoon.
Tämä tekee siitä helpon, koska nyt voit käyttää auttajasarakkeen arvoja hakuarvoina.
Tässä on kaava, joka antaa sinulle tuloksen G3: I8: ssa.
= VLOOKUP ($ F3 & "|" & G $ 2, $ C $ 2: $ D $ 19,2,0)
Tässä olemme yhdistäneet opiskelijan nimen ja kokeen tason saadaksesi hakuarvon, ja käytämme tätä hakuarvoa ja tarkistamme sen auttaja -sarakkeesta vastaavan tietueen saamiseksi.
Huomautus: Yllä olevassa esimerkissä olemme käyttäneet | erottimena, kun liität tekstin auttajasarakkeeseen. Joissakin poikkeuksellisen harvinaisissa (mutta mahdollisissa) olosuhteissa sinulla voi olla kaksi kriteeriä, jotka ovat erilaisia, mutta antavat saman tuloksen yhdistettynä. Tässä on esimerkki:
Huomaa, että vaikka A2 ja A3 ovat erilaisia ja B2 ja B3 ovat erilaisia, yhdistelmät ovat lopulta samat. Mutta jos käytät erotinta, jopa yhdistelmä olisi erilainen (D2 ja D3).
Tässä on opetusohjelma VLOOKUPin käyttämisestä useiden ehtojen kanssa ilman apusarakkeita. Voit myös katsoa opetusvideoni täältä.
Esimerkki 10 - Virheiden käsittely VLOOKUP -toimintoa käytettäessä
Excel VLOOKUP -toiminto palauttaa virheen, kun se ei löydä määritettyä hakuarvoa. Et ehkä halua, että ruma virhearvo häiritsee tietojesi estetiikkaa, jos VLOOKUP ei löydä arvoa.
Voit helposti poistaa virhearvot millä tahansa merkityksellä koko tekstin, kuten "Ei saatavilla" tai "Ei löydy".
Esimerkiksi alla olevassa esimerkissä, kun yrität löytää Bradin pistemäärän luettelosta, se palauttaa virheen, koska Bradin nimeä ei ole luettelossa.
Jos haluat poistaa tämän virheen ja korvata sen jollakin mielekkäällä, kääri VLOOKUP -toiminto IFERROR -toimintoon.
Tässä on kaava:
= IFERROR (VLOOKUP (D2, $ A $ 2: $ B $ 7,2,0), "Not Found")
IFERROR -toiminto tarkistaa, onko ensimmäisen argumentin (tässä tapauksessa VLOOKUP -funktio) palauttama arvo virhe vai ei. Jos se ei ole virhe, se palauttaa arvon VLOOKUP -funktiolla, muuten se palauttaa Not Found.
IFERROR -toiminto on käytettävissä Excel 2007: stä alkaen. Jos käytät aiempia versioita, käytä seuraavaa toimintoa:
= JOS (ISERROR (VLOOKUP (D2, $ A $ 2: $ B $ 7,2,0)), "Not Found", VLOOKUP (D2, $ A $ 2: $ B $ 7,2,0))
Katso myös: VLOOKUP -virheiden käsittely Excelissä.
Se on tässä VLOOKUP -opetusohjelmassa.
Olen yrittänyt kattaa tärkeitä esimerkkejä Vlookup -toiminnon käyttämisestä Excelissä. Jos haluat nähdä lisää esimerkkejä tähän luetteloon, kerro siitä kommenttiosassa.
Huomautus: Olen yrittänyt parhaani mukaan korjata tämän opetusohjelman, mutta jos löydät virheitä tai kirjoitusvirheitä, ilmoita siitä minulle 🙂
Käyttämällä VLOOKUP -toiminto Excelissä - Video
- Excel HLOOKUP -toiminto.
- Excel XLOOKUP -toiminto
- Excel INDEX -toiminto.
- Excel Epäsuora toiminto.
- Excel MATCH -toiminto.
- Excel OFFSET -toiminto.
