Sisällys
Tämä oppitunti selittää, kuinka nopeasti käsitellä tilannetta, jossa toiminto VPR (VLOOKUP) ei halua toimia Excel 2013:ssa, 2010:ssä, 2007:ssä ja 2003:ssa ja kuinka tunnistaa ja korjata yleisiä virheitä ja voittaa rajoituksia VPR.
Useissa aiemmissa artikkeleissa olemme tutkineet toiminnon eri puolia VPR Excelissä. Jos olet lukenut ne huolellisesti, sinun pitäisi nyt olla tämän alan asiantuntija. Monet Excel-asiantuntijat eivät kuitenkaan turhaan usko VPR yksi monimutkaisimmista ominaisuuksista. Siinä on joukko rajoituksia ja ominaisuuksia, joista tulee monien ongelmien ja virheiden lähde.
Tästä artikkelista löydät yksinkertaiset selitykset virheistä #AT (#N/A), # NIMI? (#NAME?) ja #ARVO! (#ARVO!), jotka näkyvät funktiota käytettäessä VPR, sekä tekniikat ja menetelmät niiden käsittelemiseksi. Aloitamme yleisimmistä tapauksista ja ilmeisimmistä syistä. VPR ei toimi, joten on parempi tutkia esimerkkejä siinä järjestyksessä, jossa ne on annettu artikkelissa.
#N/A-virheen korjaaminen Excelin VLOOKUP-funktiossa
Kaavoissa kanssa VPR virheilmoitus #AT (#N/A) tarkoittaa ei ole käytettävissä (ei tietoja) – tulee näkyviin, kun Excel ei löydä etsimääsi arvoa. Tämä voi tapahtua useista syistä.
1. Haluttu arvo on kirjoitettu väärin
Hyvä idea tarkistaa tämä kohde ensin! Kirjoitusvirheitä esiintyy usein, kun työskentelet erittäin suurten tietomäärien kanssa, jotka koostuvat tuhansista riveistä, tai kun etsimäsi arvo kirjoitetaan kaavaan.
2. #N/A-virhe haettaessa likimääräistä vastaavuutta VLOOKUP:lla
Jos käytät kaavaa, jossa on likimääräinen hakuehto eli argumentti range_lookup (alue_haku) on TOSI tai sitä ei ole määritetty, kaavasi saattaa ilmoittaa virheestä # N / A kahdessa tapauksessa:
- Haettava arvo on pienempi kuin etsittävän taulukon pienin arvo.
- Hakusaraketta ei ole lajiteltu nousevaan järjestykseen.
3. #N/A -virhe, kun etsitään tarkkaa vastaavuutta VHAKU:n kanssa
Jos etsit tarkkaa vastinetta eli argumenttia range_lookup (range_lookup) on EPÄTOSI ja tarkkaa arvoa ei löytynyt, kaava ilmoittaa myös virheen # N / A. Lisätietoja siitä, kuinka voit etsiä tarkkoja ja likimääräisiä vastaavuuksia funktion kanssa VPR.
4. Hakusarake ei ole vasemmalla
Kuten luultavasti tiedät, yksi merkittävimmistä rajoituksista VPR se ei voi olla vasemmalle, joten taulukon hakusarakkeen on oltava vasemmanpuoleisin. Käytännössä usein unohdamme tämän, mikä johtaa toimimattomaan kaavaan ja virheeseen. # N / A.
Päätös: Jos tietorakennetta ei ole mahdollista muuttaa siten, että hakusarake on vasemmanpuoleisin, voit käyttää toimintojen yhdistelmää INDEX (INDEKSI) ja LISÄTIETOJA (MATCH) joustavampi vaihtoehto VPR.
5. Numerot muotoillaan tekstinä
Toinen virhelähde # N / A kaavoissa kanssa VPR ovat numeroita tekstimuodossa päätaulukossa tai hakutaulukossa.
Tämä tapahtuu yleensä, kun tuot tietoja ulkoisista tietokannoista tai kun kirjoitat heittomerkin ennen numeroa säilyttääksesi etunollan.
Selkeimmät merkit tekstimuodossa olevasta numerosta on esitetty alla olevassa kuvassa:
Lisäksi numerot voidaan tallentaa muodossa general (Yleinen). Tässä tapauksessa on vain yksi havaittavissa oleva ominaisuus – numerot on tasattu solun vasempaan reunaan, kun taas oletusarvoisesti ne on tasattu oikeaan reunaan.
Päätös: Jos se on yksittäinen arvo, napsauta virhekuvaketta ja valitse Muunna numeroksi (Muunna numeroksi) pikavalikosta.
Jos näin on monen numeron kohdalla, valitse ne ja napsauta valittua aluetta hiiren kakkospainikkeella. Valitse näkyviin tulevasta kontekstivalikosta formaatti Cells (Muotoile solut) > välilehti numero (Numero) > muoto numero (Numero) ja paina OK.
6. Alussa tai lopussa on välilyönti
Tämä on vähiten ilmeinen syy virheeseen. # N / A toiminnassa VPR, koska näitä ylimääräisiä tiloja on visuaalisesti vaikea nähdä, varsinkin kun työskennellään suurten taulukoiden kanssa, kun suurin osa tiedoista on näytön ulkopuolella.
Ratkaisu 1: Ylimääräiset välilyönnit päätaulukossa (missä VLOOKUP-toiminto on)
Jos päätaulukossa näkyy ylimääräisiä välilyöntejä, voit varmistaa, että kaavat toimivat oikein liittämällä argumentin mukaan hakuarvo (lookup_value) funktioksi TRIM (TRIMMATA):
=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)
=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)
Ratkaisu 2: Ylimääräiset välilyönnit hakutaulukossa (hakusarakkeessa)
Jos hakusarakkeessa on ylimääräisiä välilyöntejä – yksinkertaisia tapoja # N / A kaavassa kanssa VPR ei voida välttää. Sijasta VPR Voit käyttää taulukkokaavaa funktioiden yhdistelmällä INDEX (INDEKSI), LISÄTIETOJA (MATCH) и TRIM (TRIMMATA):
=INDEX($C$2:$C$10,MATCH(TRUE,TRIM($A$2:$A$10)=TRIM($F$2),0))
=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(ИСТИНА;СЖПРОБЕЛЫ($A$2:$A$10)=СЖПРОБЕЛЫ($F$2);0))
Koska tämä on taulukkokaava, älä unohda painaa Ctrl + Vaihto + Enter tavallisen sijasta entersyöttääksesi kaavan oikein.
Virhe #VALUE! kaavoissa VLOOKUPilla
Useimmissa tapauksissa Microsoft Excel raportoi virheestä #ARVO! (#ARVO!), kun kaavassa käytetty arvo ei vastaa tietotyyppiä. Mitä tulee VPR, virheeseen on yleensä kaksi syytä #ARVO!.
1. Etsimäsi arvo on pidempi kuin 255 merkkiä
Ole varovainen: toiminto VPR ei voi etsiä arvoja, jotka sisältävät yli 255 merkkiä. Jos etsimäsi arvo ylittää tämän rajan, saat virheilmoituksen. #ARVO!.
Päätös: Käytä joukko ominaisuuksia INDEKSI+OTUMINEN (INDEKSI + OSTO). Alla on kaava, joka sopii hyvin tähän tehtävään:
=INDEX(C2:C7,MATCH(TRUE,INDEX(B2:B7=F$2,0),0))
=ИНДЕКС(C2:C7;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B2:B7=F$2;0);0))
2. Hakutyökirjan koko polkua ei ole määritetty
Jos haet tietoja toisesta työkirjasta, sinun on määritettävä koko polku kyseiseen tiedostoon. Tarkemmin sanottuna sinun on sisällytettävä työkirjan nimi (mukaan lukien tunniste) hakasulkeisiin [ ], jonka jälkeen on taulukon nimi ja huutomerkki. Kaikki tämä rakenne on jätettävä heittomerkkeihin, jos kirjan tai arkin nimi sisältää välilyöntejä.
Tässä on toiminnon täydellinen rakenne VPR etsiä toisesta kirjasta:
=VLOOKUP(lookup_value,'[workbook name]sheet name'!table_array, col_index_num,FALSE)
=ВПР(искомое_значение;'[имя_книги]имя_листа'!таблица;номер_столбца;ЛОЖЬ)
Todellinen kaava voi näyttää tältä:
=VLOOKUP($A$2,'[New Prices.xls]Sheet1'!$B:$D,3,FALSE)
=ВПР($A$2;'[New Prices.xls]Sheet1'!$B:$D;3;ЛОЖЬ)
Tämä kaava etsii solun arvon A2 sarakkeessa B arkilla Sheet1 työkirjassa Uudet hinnat ja poimi vastaava arvo sarakkeesta D.
Jos jokin osa taulukon polusta jätetään pois, funktiosi VPR ei toimi ja ilmoittaa virheestä #ARVO! (vaikka hakutaulukon sisältävä työkirja olisi tällä hetkellä auki).
Lisätietoja toiminnosta VPRviitata toiseen Excel-tiedostoon, katso oppitunti: Haku toisesta työkirjasta VLOOKUPilla.
3. Argumentin sarakkeen_numero on pienempi kuin 1
On vaikea kuvitella tilannetta, jossa joku syöttää arvon, joka on pienempi kuin 1ilmaisemaan sarakkeen, josta arvo poimitaan. Vaikka se on mahdollista, jos tämän argumentin arvon laskee toinen sisäkkäinen Excel-funktio VPR.
Joten, jos se tapahtuu, että väite col_index_num (sarakkeen_numero) pienempi kuin 1toiminto VPR ilmoittaa myös virheestä #ARVO!.
Jos argumentti col_index_num (sarakkeen_numero) on suurempi kuin annetun taulukon sarakkeiden määrä, VPR ilmoittaa virheestä #VIITE! (#SSYL!).
Virhe #NAME? VLOOKUPissa
Yksinkertaisin tapaus on virhe # NIMI? (#NAME?) – tulee näkyviin, jos kirjoitat vahingossa funktion nimen, jossa on virhe.
Ratkaisu on ilmeinen – tarkista oikeinkirjoitus!
VLOOKUP ei toimi (rajoitukset, varoitukset ja päätökset)
Melko monimutkaisen syntaksin lisäksi VPR on enemmän rajoituksia kuin millään muulla Excel-toiminnolla. Näiden rajoitusten vuoksi näennäisesti yksinkertaisia kaavoja VPR johtavat usein odottamattomiin tuloksiin. Alta löydät ratkaisut useisiin yleisiin skenaarioihin, joissa VPR on väärin.
1. VLOOKUP ei erota isot ja pienet kirjaimet
Toiminto VPR ei tee eroa kirjainten välillä ja hyväksyy pienet ja isot kirjaimet samana. Siksi, jos taulukossa on useita elementtejä, jotka eroavat vain kirjainkoosta, VLOOKUP-funktio palauttaa ensimmäisen löydetyn elementin kirjainkoosta riippumatta.
Päätös: Käytä toista Excel-toimintoa, joka voi suorittaa pystyhaun (HAKU, SUMMATULO, INDEX ja MATCH) yhdessä TARKKAA joka erottaa tapauksen. Saat lisätietoja oppitunnista – 4 tapaa tehdä VLOOKUPista isot ja pienet kirjaimet Excelissä.
2. VLOOKUP palauttaa ensimmäisen löydetyn arvon
Kuten jo tiedät, VPR palauttaa arvon annetusta sarakkeesta, joka vastaa ensimmäistä löydettyä osumaa. Voit kuitenkin saada sen poimimaan 2., 3., 4. tai minkä tahansa muun haluamasi arvon toiston. Jos haluat purkaa kaikki päällekkäiset arvot, tarvitset funktioiden yhdistelmän INDEX (INDEKSI), VÄHITEN (PIENI) ja LINE (RIVI).
3. Sarake lisättiin tai poistettiin taulukkoon
Valitettavasti kaavat VPR lakkaa toimimasta aina, kun uusi sarake lisätään tai poistetaan hakutaulukkoon. Tämä johtuu syntaksista VPR edellyttää, että määrität koko haun alueen ja tietyn sarakkeen numeron tietojen poimimista varten. Luonnollisesti sekä annettu alue että sarakkeen numero muuttuvat, kun poistat sarakkeen tai lisäät uuden.
Päätös: Ja taas toiminnoilla on kiire auttamaan INDEX (INDEKSI) ja LISÄTIETOJA (OTTELU). Kaavassa INDEKSI+OTUMINEN Voit määrittää haku- ja hakusarakkeet erikseen, minkä seurauksena voit poistaa tai lisätä niin monta saraketta kuin haluat ilman, että sinun tarvitsee päivittää kaikkia asiaan liittyviä hakukaavoja.
4. Soluviittaukset ovat sekaisin kaavaa kopioitaessa
Tämä otsikko selittää ongelman ytimen tyhjentävästi, eikö?
Päätös: Käytä aina absoluuttisia soluviittauksia (symbolin kanssa $) tallentaa esimerkiksi alueen 2 dollaria: 100 dollaria or $A:$C. Kaavapalkissa voit vaihtaa linkin tyyppiä nopeasti napsauttamalla F4.
VLOOKUP – työskentely funktioiden IFERROR ja ISERROR kanssa
Jos et halua pelotella käyttäjiä virheilmoituksilla # N / A, #ARVO! or # NIMI?, voit näyttää tyhjän solun tai oman viestisi. Voit tehdä tämän asettamalla VPR funktioksi IFERROR (IFERROR) Excel 2013:ssa, 2010:ssä ja 2007:ssä tai käytä joukko toimintoja JOS+VIRHE (JOS+VIRHE) aiemmissa versioissa.
VLOOKUP: Työskentely IFERROR-toiminnon kanssa
Funktioiden syntaksi IFERROR (IFERROR) on yksinkertainen ja puhuu puolestaan:
IFERROR(value,value_if_error)
ЕСЛИОШИБКА(значение;значение_если_ошибка)
Toisin sanoen ensimmäiselle argumentille lisäät arvon, joka tarkistetaan virheen varalta, ja toiselle argumentille määrität, mitä palautetaan, jos virhe löytyy.
Tämä kaava palauttaa esimerkiksi tyhjän solun, jos etsimääsi arvoa ei löydy:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"")
Jos haluat näyttää oman viestisi toiminnon normaalin virheilmoituksen sijaan VPR, laita se lainausmerkkeihin, kuten näin:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"Ничего не найдено. Попробуйте еще раз!")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"Ничего не найдено. Попробуйте еще раз!")
VHAKU: työskentelee VIRHE-toiminnon kanssa
Toiminnosta lähtien IFERROR ilmestyi Excel 2007:ssä, kun työskentelet aiemmissa versioissa, sinun on käytettävä yhdistelmää IF (IF) ja Eoshibka (ISERROR) näin:
=IF(ISERROR(VLOOKUP формула),"Ваше сообщение при ошибке",VLOOKUP формула)
=ЕСЛИ(ЕОШИБКА(ВПР формула);"Ваше сообщение при ошибке";ВПР формула)
Esimerkiksi kaava JOS+VIRHE+VHAKU, samanlainen kuin kaava IFERROR+VLOOKUPnäkyy yllä:
=IF(ISERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE)),"",VLOOKUP($F$2,$B$2:$C$10,2,FALSE))
=ЕСЛИ(ЕОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ));"";ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ))
Siinä kaikki tältä päivältä. Toivon, että tämä lyhyt opetusohjelma auttaa sinua selviytymään kaikista mahdollisista virheistä. VPR ja saat kaavasi toimimaan oikein.