Kun käytät VLOOKUP -kaavaa Excelissä, joskus saatat saada ruman #N/A -virheen. Näin tapahtuu, kun kaava ei löydä hakuarvoa.
Tässä opetusohjelmassa näytän sinulle erilaisia tapoja käyttää IFERROR -funktiota VLOOKUPin kanssa näiden #N/A -virheiden käsittelyyn laskentataulukossasi.
IFERRORin ja VLOOKUP -yhdistelmän avulla voit näyttää jotain merkityksellistä #N/A -virheen (tai minkä tahansa muun virheen) sijasta.
Ennen kuin ryhdymme tämän yhdistelmän käytön yksityiskohtiin, käydään ensin nopeasti läpi IFERROR -toiminto ja katsotaan, miten se toimii.
IFERROR -toiminto selitetty
IFERROR -toiminnolla voit määrittää, mitä pitäisi tapahtua, jos kaava tai soluviittaus palauttaa virheen.
Tässä on IFERROR -funktion syntaksi.
= IFERROR (arvo, arvo_ja_virhe)
- arvo - tämä on argumentti, joka tarkistetaan virheen varalta. Useimmissa tapauksissa se on joko kaava tai soluviittaus. Kun käytät VLOOKUPia IFERRORin kanssa, VLOOKUP -kaava olisi tämä argumentti.
- value_if_error - tämä arvo palautetaan, jos tapahtuu virhe. Seuraavat virhetyypit arvioitiin: #N/A, #REF !, #DIV/0!, #VALUE !, #NUM !, #NAME? Ja #NULL !.
Mahdolliset syyt VLOOKUP -virheen palauttamiseen
VLOOKUP -toiminto voi palauttaa #N/A -virheen seuraavista syistä:
- Hakuarvoa ei löydy hakutaulukosta.
- Hakuarvossa (tai taulukkotaulukossa) on edessä, takana tai kaksinkertainen välilyönti.
- Hakuarvossa tai hakutoiminnon arvoissa on kirjoitusvirhe.
Voit hoitaa kaikki nämä virheiden syyt IFERROR- ja VLOOKUP -yhdistelmällä. Sinun on kuitenkin pidettävä silmällä syitä #2 ja #3 ja korjattava ne lähdetiedoissa sen sijaan, että annat IFERRORin käsitellä niitä.
Huomautus: IFERROR käsittelee virheen riippumatta siitä, mikä sen aiheutti. Jos haluat käsitellä vain virheitä, jotka johtuvat siitä, että VLOOKUP ei löydä hakuarvoa, käytä sen sijaan IFNA: ta. Tämä varmistaa, että muita kuin #N/A -virheitä ei käsitellä, ja voit tutkia nämä muut virheet.
Voit käsitellä etu-, loppu- ja kaksoisvälit TRIM -toiminnolla.
VLOOKUP #N/A -virheen korvaaminen merkityksellisellä tekstillä
Oletetaan, että sinulla on alla näkyvä tietojoukko:
Kuten näet, VLOOKUP -kaava palauttaa virheen, koska hakuarvo ei ole luettelossa. Etsimme Glenin pisteitä, joita ei ole pistetaulukossa.
Vaikka tämä on hyvin pieni tietojoukko, saatat saada valtavia tietojoukkoja, joissa sinun on tarkistettava monien kohteiden esiintyminen. Jokaisessa tapauksessa, kun arvoa ei löydy, saat virheen #N/A.
Tässä on kaava, jonka avulla voit saada jotain mielekästä #N/A -virheen sijasta.
= IFERROR (VLOOKUP (D2, $ A $ 2: $ B $ 10,2,0), "Not Found")
Yllä oleva kaava palauttaa tekstin "Ei löydy" virheen #N/A sijaan. Voit myös käyttää samaa kaavaa palauttaaksesi tyhjän, nollan tai minkä tahansa muun mielekkään tekstin.
VLOOKUP -pesä IFERROR -toiminnolla
Jos käytät VLOOKUPia ja hakutaulukkosi on pirstoutunut samaan laskentataulukkoon tai eri laskentataulukoihin, sinun on tarkistettava VLOOKUP -arvo kaikista näistä taulukoista.
Esimerkiksi alla olevassa tietojoukossa on kaksi erillistä taulukkoa opiskelijoiden nimistä ja pisteistä.
Jos minun on löydettävä Grace -pisteet tästä tietojoukosta, minun on käytettävä VLOOKUP -toimintoa ensimmäisen taulukon tarkistamiseen, ja jos arvoa ei löydy siitä, tarkista toinen taulukko.
Tässä on sisäkkäinen IFERROR -kaava, jota voin käyttää arvon etsimiseen:
= IFERROR (VLOOKUP (G3, $ A $ 2: $ B $ 5,2,0), IFERROR (VLOOKUP (G3, $ D $ 2: $ E $ 5,2,0), "Not Found"))
VLOOKUPin käyttö IF: n ja ISERRORin kanssa (versiot ennen Excel 2007: tä)
IFERROR -toiminto otettiin käyttöön Excel 2007 for Windowsissa ja Excel 2016 Macissa.
Jos käytät aiempia versioita, IFERROR -toiminto ei toimi järjestelmässäsi.
Voit toistaa IFERROR -toiminnon toiminnot käyttämällä IF- ja ISERROR -funktion yhdistelmää.
Näytän sinulle nopeasti, miten IF- ja ISERROR -yhdistelmää käytetään IFERRORin sijaan.
Yllä olevassa esimerkissä IFERRORin sijaan voit käyttää myös solussa B3 esitettyä kaavaa:
= JOS (ISERROR (A3), ”Not Found”, A3)
Kaavan ISERROR -osa tarkistaa virheet (mukaan lukien #N/A -virhe) ja palauttaa arvon TRUE, jos virhe havaitaan, ja FALSE, jos ei.
- Jos se on TOSI (mikä tarkoittaa, että on virhe), IF -funktio palauttaa määritetyn arvon (tässä tapauksessa ei löydy).
- Jos se on EPÄTOSI (mikä tarkoittaa, ettei virheitä ole), IF -funktio palauttaa arvon (A3 yllä olevassa esimerkissä).
IFERROR vs IFNA
IFERROR käsittelee kaikenlaisia virheitä, kun taas IFNA käsittelee vain #N/A -virhettä.
Kun käsittelet VLOOKUPin aiheuttamia virheitä, sinun on varmistettava, että käytät oikeaa kaavaa.
Käytä IFERRORia kun haluat käsitellä kaikenlaisia virheitä. Nyt virhe voi johtua useista tekijöistä (kuten väärä kaava, väärin kirjoitettu nimetty alue, hakuarvon löytämättä jättäminen ja virhearvon palauttaminen hakutaulukosta). Sillä ei ole väliä IFERRORille ja se korvaa kaikki nämä virheet määritetyllä arvolla.
Käytä IFNA: ta kun haluat käsitellä vain #N/A -virheitä, jotka johtuvat todennäköisemmin siitä, että VLOOKUP -kaava ei löydä hakuarvoa.
Saatat myös pitää hyödyllisenä seuraavia Excel -opetusohjelmia:
- Kuinka tehdä VLOOKUP -kirjainkoosta erottava.
- VLOOKUP vs. INDEX/MATCH - Keskustelu päättyy tähän!
- Käytä VLookupia saadaksesi viimeisen numeron luettelosta Excelissä.
- Kuinka käyttää VLOOKUPia useiden ehtojen kanssa
- #NAME -virhe Excelissä - mistä se johtuu ja kuinka korjata se!
