Kuinka VLOOKUP-kaavaa käytetään tietojen hakemiseen Excel-luettelosta. VLOOKUP-vianmääritys, kun asiat menevät pieleen. Vaiheittaiset videot ja Excel-työkirjat, joissa on VLOOKUP-esimerkkejä.
VLOOKUP Video
Katso alla olevat ohjeet VLOOKUP-kaavan luomiseksi Tuotteen hinta -hakuvideossa. Kirjalliset ohjeet ovat videon alla. Hanki tuotteen hintahaun näytetiedosto seurattavaksi videon kanssa (lataa tiedosto # 4).
Valitse hakutaulukon sijainti
Kukin haku kannattaa tallentaa taulukko erillisessä laskentataulukossa työkirjassa. Sitten kun lisäät ja poistat rivejä hakutaulukoihin, et vahingossa lisää tai poista rivejä missään muussa taulukossa.
Tässä esimerkissä hakutaulukko on Tuotteet-nimisellä taulukolla.
Luo hakutaulukko
Hakukaavat voivat toimia pystysuunnassa, etsimällä arvoja sarakkeesta tai ne voivat toimia vaakasuunnassa ja etsiä arvoja riviltä . Tässä esimerkissä tiedot tallennetaan pystysuoraan, arvot sarakkeen alapuolella, ja myöhemmin käytämme VLookup-kaavaa pystysuuntaisen haun suorittamiseen.
- Kirjoita otsikot ensimmäiselle riville
- Ensimmäisen sarakkeen tulee sisältää yksilölliset avainarvot, joihin haku perustuu. Tässä esimerkissä on hinta tietylle tuotekoodille.
- Jos laskentataulukossa on muita tietoja, jätä vähintään yksi tyhjä rivi taulukon alaosaan ja yksi tyhjä sarake taulukon oikealle puolelle erottaaksesi hakutaulukko muista tiedoista.
Huomaa: Voit helpottaa taulukon viittaamista nimeämällä alueen. Tässä on ohjeita: Alueen nimeäminen ▲ YLÖS
VLOOKUP-funktion argumentit
VLOOKUP-funktiolla on neljä argumenttia:
- lookup_value : Mitä arvoa haluat etsiä? Tässä esimerkissä tuotekoodi on solussa A7 ja haluat löytää sen pr oduct-nimi.
- table_array: Missä hakutaulukko on? Jos käytät absoluuttista viittausta ($ A $ 2: $ C $ 5) suhteellisen viitteen (A2: C5) sijaan, on helpompaa kopioida kaavaksi muihin soluihin. Tai nimeä hakutaulukko ja viittaa siihen nimellä.
- col_index_num: Millä sarakkeella on palautettava arvo? Tässä esimerkissä tuotenimet ovat hakutaulukon toisessa sarakkeessa.
- : Haluatko tarkan haun? Onko arvioitu ottelu kunnossa?
Jos käytät TOSI viimeisenä argumenttina tai jätät viimeisen argumentin pois, voidaan palauttaa likimääräinen vastaavuus. Tässä esimerkissä FALSE on viimeinen argumentti, joten jos tuotekoodia ei löydy, tulos on # N / A. (Huomaa: Excel on melko anteeksiantava ja hyväksyy 0 arvon FALSE sijasta ja 1 arvon TRUE sijasta.) ▲ YLÖS
Luo VLookup-kaava
Kun olet luonut hakutaulukon, voit luoda työkirjaan muita kaavoja ja hakea tietoja tuoteluettelosta. Esimerkiksi laskun laatimiseksi voit antaa tuotekoodin, ja kaavat saavat tuotenimen tai hinnan tuotetaulukosta.
Tässä esimerkissä lasku luodaan laskulle. VLOOKUP-kaavan pitäisi löytää tarkka vastaavuus tuotekoodille ja palauttaa tuotteen nimi.
Voit luoda tuotteen hinnan laskevan VLOOKUP-kaavan seuraavasti:
- Valitse laskulomake
- Syötä tuotekoodi A23 soluun A7
- Käynnistä solussa B7 VLOOKUP-kaava:
= VLOOKUP ( - Napsauta solu A7 syöttääksesi viitteensä.
- Kirjoita pilkku ensimmäisen argumentin lopettamiseksi
- Valitse Tuotteet-sivu
- Valitse solut A2: C5 (tuoteluettelo) )
- Muuta soluviittaukset absoluuttisiksi painamalla F4-näppäintä. Kaavan pitäisi nyt näyttää tältä:
= VLOOKUP (A7, Tuotteet! $ A $ 2: $ C $ 5 - Kirjoita pilkku toisen argumentin lopettamiseksi.
- Kirjoita 2, joka on sarake hakutaulukossa, joka sisältää tuotenimen.
- Kirjoita pilkku lopettaaksesi argumentin. kolmas argumentti.
- Kirjoita FALSE, tarkista, että tuotekoodille löytyy tarkka haku, ja lisää sulku.
- Kaavan pitäisi nyt näyttää l näin:
= VLOOKUP (A7, tuotteet! $ A $ 2: $ C $ 5,2, EPÄTOSI) - Täytä kaava painamalla Enter-näppäintä. Tuotteen nimi tulee näkyviin.
Huomaa: Palataksesi tuotteen hinnan, luo VLOOKUP-kaava, joka viittaa hakutaulukon sarakkeeseen 3. Kirjoita esimerkiksi seuraava kaava soluun C7:
= VLOOKUP (A7, Tuotteet! $ A $ 2: $ C $ 5,3, EPÄTOSI) ▲ YLÖS
VLOOKUP-kaava arvojen alueelle
Joissakin tilanteissa suositellaan likimääräistä vastaavuutta, joten useat arvot palauttavat saman tuloksen. Esimerkiksi luokitellessaan opiskelijapaperia kaikkien paperien, joiden arvosana on vähintään 85, tulisi saada A-arvosana.
Jos haluat nähdä tämän kaavan luomisen vaiheet, katso alla oleva VLOOKUP-video. Kirjalliset ohjeet ovat videon alapuolella.
Tässä esimerkissä hakutaulukko luodaan Arvosanat-nimiselle arkille. Luo hakutaulukko kirjoittamalla kunkin luokan vähimmäispisteet sarakkeeseen A. Syötä vastaava arvosana sarakkeeseen B. Lajittele tulokset nousevassa järjestyksessä.
Solut A2: B6 nimettiin GradeListiksi.
Pisteet syötetään taulukolle nimeltä Report Card, jossa VLOOKUP-kaava laskee arvosanan.
alla oleva näyttökuva, kaava on kopioitu alas riville 6, ja kaava näkyy solussa C6.
Yhdistä VLOOKUP ja MATCH
Sen sijaan, että kirjoitat sarakkeen numeron VLOOKUP-kaavaksi, etsi oikea sarake hakutaulukosta MATCH-toiminnon avulla. Tällä on pari etua:
- Tekee kaavasta joustavan, joten kaavan kopioiminen taulukkoon on helpompaa.
- Voi estää ongelmia, jos uusia sarakkeita lisätään. hakutaulukossa tai jos hakusarakkeet on järjestetty uudelleen.
Tämä video näyttää vaiheet, ja videon alla on kirjalliset ohjeet toiselle esimerkille.
Selaimesi ei voi näyttää tätä kehystä. Tässä on linkki sivulle
VLOOKUP With MATCH for Order Details
Tässä esimerkissä VLOOKUP-kaava palauttaa tilaustiedot hakutaulukosta tilausnumeron perusteella. . Tässä on hakutaulukko, nimeltään tblOrders.
HUOMAUTUS: Tämä esimerkki on esimerkkityökirjassa # 1, taulukossa nimeltä OrdersMATCH.
Tässä on taulukko, jossa on VLOOKUP-kaavat. Haluamme jokaiselle tilaukselle alueen, tilauksen päivämäärän ja tilauksen määrän, joten tarvitaan 3 VLOOKUP-kaavaa.
Jos sarakkeiden numerot kirjoitetaan kaavaan, jokaisessa sarakkeessa tarvitaan eri kaava:
- Alue: = VLOOKUP ($ B6, tblOrdersALL, 2,0)
- OrderDate: = VLOOKUP ($ B6, tblOrdersALL, 3,0)
- OrderAmt: = VLOOKUP ($ B6, tblOrdersALL, 4,0)
MATCH-funktio
Sen sijaan, että kirjoitat sarakkeen numeron VLOOKUP-kaavaan, voimme käyttää MATCH-toimintoa. MATCH-toiminto etsii kohteen sijainnin luettelosta ja palauttaa sijainnin numeron.
Alla olevassa kuvakaappauksessa MATCH-kaava palauttaa 2 alueen ”sijainniksi” hakutaulukon otsikkosoluissa (A1: D1).
= MATCH ( C5, Tilaukset_ALL! $ A $ 1: $ D $ 1, 0)
HUOMAUTUS: Jotta tämä tekniikka toimisi oikein, VLOOKUP-taulukon otsikoiden on vastattava tarkalleen hakutaulukon otsikoita. Tarkan vastaavuuden varmistamiseksi VLOOKUP-otsikkosolut linkitetään hakutaulukon otsikkosoluihin.
Lisää MATCH VLOOKUPiin
Jos haluat lisätä MATCH-funktion VLOOKUP-kaavaan, korvaa vain kirjoitti sarakkeen numeron
= VLOOKUP ($ B6, tblOrdersALL, 2,0)
MATCH-kaavalla:
= VLOOKUP ($ B6, tblOrdersALL, MATCH (C5, Tilaukset_ALL! $ A $ 1: $ D $ 1, 0), 0)
Kopioi VLOOKUP-kaava vierekkäin
Sen sijaan, että tarvitset eri kaavaa jokaisessa sarakkeessa, voit kopioida kaavan läpi ja käyttää samaa kaavaa kaikissa sarakkeissa. Jokaisessa sarakkeessa se viittaa kyseisen sarakkeen otsikkosoluun ja löytää sen sijainnin hakutaulukosta.
HUOMAUTUS: Jos täytät kaavaa sarakkeissa, joissa on erilainen muotoilu, toimi seuraavasti:
- Valitse solu, jolla on kaava, jonka haluat kopioida
- Osoita valitun solun täyttökahvaa (pieni neliö oikeassa alakulmassa)
- Paina OIKEA hiiren painiketta ja vedä viimeiseen soluun, joka tarvitsee kaavan
- Vapauta hiiren painike ja napsauta Täytä ilman muotoilua
Yhdistä IF ja VLOOKUP
Voit käyttää IF-kaavaa VLookup-kaavan kanssa, jotta voit palauttaa tarkat arvot, jos ne löytyvät, ja tyhjän merkkijonon jos sitä ei löydy.
Jos haluat nähdä IF- ja VLOOKUP-kaavojen määrittämisen vaiheet, voit katsoa tämän lyhyen videon. Kirjalliset ohjeet ovat videon alla.
Piilota virheet yhdistämällä IF ja VLOOKUP seuraavasti:
- Kirjoita tuote Lasku-taulukon soluun A8. koodi A28. Jos solun B8 VLookup-kaavalla on FALSE neljäntenä argumenttina, tulos on # N / A, koska hakutaulukossa ei ole tarkkaa vastaavuutta tuotekoodille.
- Kääri VLookup-kaava IF-kaava (tässä esimerkissä tuoteluettelo on nimetty), ISNA-funktion avulla # N / A -virheen tarkistamiseksi:
- Paina Enter-näppäintä ja solu näkyy tyhjä. Koska tarkkaa vastaavuutta ei löytynyt, VLookup-kaava palautti # N / A, joten ISNA-funktion tulos on TOSI. IF-kaava muunsi tämän tyhjäksi merkkijonoksi.
= IF (ISNA (VLOOKUP (A8, ProductList, 2, FALSE) ), ””, VLOOKUP (A8, ProductList, 2, FALSE))
Yhdistä IFERROR ja VLOOKUP
Kiitos Chip Pearsonille tämän kaavan ehdottamisesta.
Excel 2007: ssä otettiin käyttöön uusi funktio IFERROR . Voit käyttää IFERROR-kaavaa VLookupin kanssa tarkistamaan useita taulukoita arvon ja palauttamaan niihin liittyvät tiedot, kun ne löytyvät. Tässä esimerkissä kolmella alueella, lännessä, idässä ja keskellä, on tilausarkit. Jokaisella arkilla on nimetty alue – OrdersW, OrdersE ja OrdersC.
Tilaukset-nimiselle arkille voit antaa tilaustunnuksen ja tarkistaa sitten VLOOKUPin IFERROR-toiminnon avulla jokaisen nimetyn alueen ja tarkastele valitun tilauksen tietoja.
- Kirjoita Tilaus-taulukon soluun B6 OrderID: ksi 4. Tämä järjestys sijoitettiin keskialueelle.
- Yksinkertaisesti itäisen alueen taulukon tarkistamiseksi VLOOKUP-kaava solussa C6 olisi:
= VLOOKUP (B6, OrdersE, 2, FALSE) - Paina Enter-näppäintä ja VLOOKUP-kaava palauttaa # N / A, koska tilaustunnusta 4 ei ole itäisten alueiden tilaustaulukossa.
- Koska tilauksella voi olla on sijoitettu mille tahansa kolmesta alueesta, tarvitset kaavan, joka tarkistaa jokaisen taulukon.
Jos tilaustunnusta ei löydy ensimmäisestä taulukosta, kaavan tulisi tarkistaa toinen taulukko. Jos tilaustunnusta ei ole toisessa taulukossa taulukon, sen tulisi tarkistaa kolmas taulukko. Jos tilaustunnusta ei ole ”t” kolmannessa taulukossa, soluun pitäisi ilmestyä ”Ei löydy” -viesti. - IFERROR-kaavan avulla voit tarkistaa arvon, määritä sitten mitä tehdä, jos virhe löytyy. Jos käytät IFERRORia nykyisen kaavan kanssa, voit näyttää ”Ei löydy” # N / A -virheen sijaan:
= VIRHE (VLOOKUP (B6, OrdersE, 2, FALSE), ”Ei löydy”) - Voit tarkistaa kaikki kolme taulukkoa seuraamalla IFERROR- ja VLOOKUP-kaavoja:
Tämä tarkistaa OrdersE-taulukon ja jos virhe löytyy, OrdersW-taulukko ja sitten OrdersC. Jos OrderID-tunnusta ei löydy mistään kolmesta taulukosta, solussa näkyy Ei löydy -viesti.
Voit myös tarkistaa useita taulukoita vanhemmissa Excel-versioissa, joissa IFERROR ei ole käytettävissä, käyttämällä pidempi kaava:
VLOOKUP yhdistetyille arvoille
Joissakin taulukoissa ei ehkä ole yksilöllisiä arvoja mikä tahansa hakutaulukon sarake. Esimerkiksi alla olevassa taulukossa Jacket on lueteltu kahdesti sarakkeessa A. Jokaiselle takille ja kokoyhdistelmälle on kuitenkin vain yksi ennätys – Jacket Medium rivillä 4 ja Jacket Large rivillä 5.
Jos sinun on löydettävä hinta suurelle takille, vain sarakkeeseen A perustuva VLOOKUP palauttaisi ensimmäisen luetellun takin hinnan (Medium ). Olisit alihinnoittanut takin – myydä se 60,00: lle 65,00: n sijaan.
Voit luoda ainutlaatuisia hakuarvoja voi lisätä uuden sarakkeen taulukon vasempaan reunaan ja käyttää kaavaa tuotteen ja koon yhdistämiseen. Solussa A2 kaava yhdistää arvon B2 ja putkimerkin sekä arvon C2.
= B2 & ”|” & C2
Kopioi kaava viimeiselle tietoriville, joten jokaisella rivillä on ainutlaatuinen arvo sarakkeessa A.
Huomaa : Pipe-merkin sijaan voit käyttää toista merkkiä, jota ei sisälly tietoihisi.
Sitten, VLOOKUP-kaava yhdistää tuotteen ja koon hakuarvoksi. Solussa H1 kaava yhdistää arvon F1: ssä olevan arvon ja putkimerkin ja arvon G1: ssä.
= VLOOKUP (F1 &” | ”& G1, $ A $ 2: $ D $ 5,4, FALSE)
Huomaa: hinta on nyt sarake 4, sarakkeen 3 sijaan. ▲ YLÖS
VLOOKUP-kaavan vianmääritys
VLOOKUP-kaava voi palauttaa # N / A, vaikka etsimäsi arvo näyttää olevan hakutaulukossa. Yleisiä syitä tähän ovat:
Teksti vs. luku
Välilyönnit
Muut merkit
Teksti vs. luku
Tämän virheen yleinen syy on, että yksi arvoista on luku ja toinen teksti. Esimerkiksi hakutaulukossa voi olla ”123 (teksti), ja etsittävä arvo on 123 (luku).
- Muunna teksti, jos mahdollista, numeroksi käyttämällä jotakin tässä esitetyt menetelmät: Muunna teksti numeroiksi
- Jos tietoja ei voi muuntaa, voit muuntaa hakuarvon VLOOKUP-kaavassa käyttämällä jotakin seuraavista ratkaisuista:
- hakuarvot ovat tekstiä, taulukossa on numeroita
- hakuarvot ovat numeroita, taulukossa on tekstiä
- selvittääksesi, mitkä arvot ovat tekstiä ja mitkä numeroita, katso yksityiskohtaiset numeroiden tai tekstin vianmääritysvaiheet
1. Haun arvot ovat Teksti, ja taulukko sisältää Numerot
Jos hakutaulukko sisältää numeroita ja etsittävä arvo on teksti, käytä seuraavan kaltaista kaavaa:
= VLOOKUP (–A7, Tuotteet!$ A $ 2: $ C $ 5,3, EPÄTOSI)
Kaksinkertainen unary (-) muuntaa tekstin luvuksi ja toimii oikein, vaikka hakuarvot olisivatkin numeroita.
2. Haun arvot ovat Numerot ja taulukko sisältää tekstiä
Jos hakutaulukko sisältää tekstiä ja haettava arvo on numeerinen, käytä seuraavan kaltaista kaavaa:
= VLOOKUP (A7 & ””), tuotteet! $ A $ 2: $ C $ 5,3, EPÄTOSI)
TAI
= VLOOKUP (TEKSTI (A7, ”00000”) , Tuotteet! $ A $ 2: $ C $ 5,3, EPÄTOSI)
TEXT-funktio muuntaa luvun tekstiksi ja toimii oikein, vaikka hakuarvot olisivatkin tekstiä. Ensimmäisessä esimerkissä & -operaattori luo tekstimerkkijonon muotoilemattomasta numerosta. Toisessa esimerkissä numero, joka on muotoiltu etunollilla (esim. 00123), vastaa tekstiä ”numero” etunollilla.
Näet vaiheet VLOOKUP-ongelman korjaamiseksi, kun hakutaulukossa on teksti-arvoja, Katso tämä lyhyt video-opetusohjelma.
Yhden arvon välilyönnit eivätkä toisen
Toinen mahdollinen syy siihen, että vastaavaa arvoa ei löydy, on välilyönnit. Yksi arvoista voi sisältää etutiloja (tai perässä olevia tai upotettuja välilyöntejä), ja toinen ei ”t”. Voit testata arvoja LEN-toiminnolla ja tarkistaa kunkin arvon pituuden.
Esimerkiksi: = LEN (A7) palauttaa merkkien lukumäärän solussa A7. Sen on oltava yhtä suuri kuin hakutaulukon vastaavan solun merkkien lukumäärä.
Poista mahdolliset tarpeettomat välilyönnit, ja VLOOKUP-kaavan pitäisi toimia oikein. Jos et voi poistaa välilyöntejä, käytä VLOOKUPin TRIM-toimintoa poistaaksesi edelliset, jäljessä olevat tai päällekkäiset välilyönnit. Esimerkiksi:
= VLOOKUP (TRIM (A7), ProductList, 2, FALSE)
Muut merkit
Jos pelkkä TRIM-toiminto ei ratkaise ongelmaa , voit kokeilla yhtä seuraavista ehdotuksista:
- SUBSTITUTE-funktio
- CLEAN-toiminto
- Makro verkkomerkkien poistamiseksi
SUBSTITUTE-funktio
Käytä SUBSTITUTE-funktiota poistaaksesi ei-toivotut merkit. Contextures-blogissa on esimerkki: Excel-tietojen puhdistaminen TRIMillä ja SUBSTITUTE
CLEAN-toiminto
Toinen tapa korjata VLOOKUP-ongelmat on CLEAN-toiminto, joka voi poistaa joitain ei-toivottuja merkkejä tekstistä. Lisätietoja PYYHTI-toiminnosta on tässä Contextures-blogiviestissä: 30 Excel-toimintoa 30 päivässä: 29 – PUHDISTA
Verkkomerkit yhdessä arvossa eivätkä toisessa
Jos kopioit tietoja verkkosivulta, se voi sisältää HTML-tilaa, joka ei ole katkeamatonta (& nbsp) merkkejä. David McRitchie on kirjoittanut makron niiden poistamiseksi yhdessä o: n kanssa välilyönnit
Ongelmat lajitellessasi VLOOKUP-kaavaa
VLOOKUP-kaava saattaa palauttaa oikeat tulokset aluksi, mutta näyttää sitten virheelliset tulokset, jos kohteiden luettelo on lajiteltu. Näin voi käydä, jos viittaus hakuarvoon sisältää taulukon nimen. Esimerkiksi:
= VLOOKUP (”Tilauslomake”! B5, Tuotteet! $ B $ 2: $ C $ 6,2, EPÄTOSI)
HUOMAUTUS: Tämä ongelma voi ilmetä muissa toiminnoissa myös INDEX / MATCH-hakukaava.
Katso tämä video nähdäksesi ongelman korjaamisen vaiheet ja lataa seurattavan VLOOKUP-lajittelun ongelmanäytetiedosto. Kirjalliset ohjeet ovat videon alla.
Viitteessä olevien arkkien nimet
Tämän tyyppinen viite luodaan, jos napsautat toista taulukkoa kaavaa rakennettaessa. Heti kun teet tämän, Excel lisää taulukon nimen kaavan myöhempiin viitteisiin.
Näyttökuvassa Yllä, mekko on solussa B9, ja solu C9 näyttää oikean hinnan 30 dollaria.
Kuitenkin tuotteiden AZ lajittelun jälkeen mekko siirtyy soluun B5, mutta solun C5 kaava viittaa edelleen soluun B9. Viitteissä olevien taulukoiden nimien vuoksi Excel säilyttää alkuperäiset viitteet sen sijaan, että säilyttäisi viittaus nykyiseen riviin. Cell C5 näyttää villapaidan hinnan mekon sijasta. ▲ YLÖS
Korjaa ongelma
Voit ratkaista ongelman poistamalla tarpeettomat taulukoiden nimet VLOOKUP-soluviitteet. Tässä on tarkistettu kaava solulle C5:
= VLOOKUP (B5, Tuotteet! $ B $ 2: $ C $ 6,2, EPÄTOSI)
Kun tarpeettomat taulukoiden nimet on poistettu, luettelo voidaan lajitella turvallisesti, ja oikeat tulokset näkyvät jokaiselle kohteelle.
Lataa mallitiedostot
1. Hanki VLOOKUP-esimerkkityökirja (Excel 2007 ja uudemmat). Pakattu tiedosto on xlsx-muodossa, eikä siinä ole makroja.
2. Hanki Excel 2003: lle tämä versio VLOOKUP-näytetiedostosta. Pakattu tiedosto on xls-muodossa, eikä siinä ole makroja.
3. Jos haluat nähdä ongelman, jota voi esiintyä lajitellessa VLOOKUPin avulla, hanki tämä VLOOKUP-lajittelun ongelmanäytetiedosto. Pakattu tiedosto on xlsx-muodossa, eikä siinä ole makroja.
4. Hanki tuotteen opas -työkirja, jota käytetään video-oppaassa.Pakattu tiedosto on xlsx-muodossa, eikä siinä ole makroja. ▲ YLÖS
Lisää oppaita
VLOOKUP-numeron / tekstin vianetsintä
VLOOKUP toisesta työkirjasta
HLOOKUP
VALITSE Funktio
Haku – 2 ehtoa
KATSELU
HAKEMISTO / MATCH
Vertaa hakutoimintoja
Funktioluettelo