Efnisyfirlit
Þessi lexía útskýrir hvernig á að bregðast fljótt við aðstæðum þar sem aðgerð VPR (VLOOKUP) vill ekki virka í Excel 2013, 2010, 2007 og 2003 og hvernig á að bera kennsl á og laga algengar villur og sigrast á takmörkunum VPR.
Í nokkrum fyrri greinum höfum við kannað hinar ýmsu hliðar aðgerðarinnar VPR í Excel. Ef þú hefur lesið þær vandlega ættirðu nú að vera sérfræðingur á þessu sviði. Hins vegar er það ekki að ástæðulausu sem margir Excel sérfræðingar telja VPR einn af flóknari eiginleikum. Það hefur fullt af takmörkunum og eiginleikum sem verða uppspretta margra vandamála og villna.
Í þessari grein finnur þú einfaldar skýringar á villum #AT (#N/A), # NAME? (#NAFN?) og #VALUE! (#VALUE!) sem birtast þegar unnið er með aðgerðina VPR, sem og tækni og aðferðir til að takast á við þær. Við byrjum á algengustu tilfellunum og augljósustu ástæðunum fyrir því. VPR virkar ekki, svo það er betra að rannsaka dæmin í þeirri röð sem þau eru gefin í greininni.
Lagað #N/A villu í VLOOKUP virka í Excel
Í formúlum með VPR villu skilaboð #AT (#N/A) þýðir ekki í boði (engin gögn) – birtist þegar Excel finnur ekki gildið sem þú ert að leita að. Þetta getur gerst af ýmsum ástæðum.
1. Æskilegt gildi er rangt stafsett
Góð hugmynd að athuga þetta atriði fyrst! Innsláttarvillur koma oft fram þegar unnið er með mjög mikið gagnamagn, sem samanstendur af þúsundum lína, eða þegar gildið sem þú ert að leita að er skrifað í formúlu.
2. #N/A villa þegar leitað er að áætlaðri samsvörun við VLOOKUP
Ef þú notar formúlu með áætluðu samsvörunarskilyrði, þ.e. rök range_lookup (range_lookup) er TRUE eða ekki tilgreint, formúlan þín gæti tilkynnt um villu # N / A í tveimur tilvikum:
- Gildið sem á að fletta upp er minna en minnsta gildið í fylkinu sem verið er að fletta upp.
- Leitardálkurinn er ekki flokkaður í hækkandi röð.
3. #N/A villa þegar leitað er að nákvæmri samsvörun við VLOOKUP
Ef þú ert að leita að nákvæmri samsvörun, þ.e. rök range_lookup (range_lookup) er FALSE og nákvæmt gildi fannst ekki, formúlan mun einnig tilkynna um villu # N / A. Lærðu meira um hvernig á að leita að nákvæmum og áætluðum samsvörun með falli VPR.
4. Leitardálkur er ekki lengst til vinstri
Eins og þú veist líklega, ein mikilvægasta takmörkunin VPR það er að það getur ekki snúið til vinstri, þess vegna verður uppfletti dálkurinn í töflunni þinni að vera lengst til vinstri. Í reynd gleymum við þessu oft, sem leiðir til formúlu sem virkar ekki og villu. # N / A.
Ákvörðun: Ef ekki er hægt að breyta gagnaskipulaginu þannig að leitardálkurinn sé lengst til vinstri er hægt að nota samsetningu aðgerða INDEX (VÍSITALA) og FYRIR MEIRA (MATCH) sem sveigjanlegri valkostur fyrir VPR.
5. Tölur eru sniðnar sem texti
Önnur uppspretta villu # N / A í formúlum með VPR eru tölur á textasniði í aðaltöflu eða uppflettitöflu.
Þetta gerist venjulega þegar þú flytur inn upplýsingar úr utanaðkomandi gagnagrunnum, eða þegar þú slærð inn villustaf á undan tölu til að halda fremsta núlli.
Augljósustu táknin um tölu á textasniði eru sýnd á myndinni hér að neðan:
Að auki er hægt að geyma tölur á formi almennt (Almennt). Í þessu tilviki er aðeins einn áberandi eiginleiki - tölurnar eru stilltar við vinstri brún reitsins, en sjálfgefið eru þær stilltar við hægri brún.
Ákvörðun: Ef það er eitt gildi, smelltu bara á villutáknið og veldu Umbreyta í númer (Breyta í númer) úr samhengisvalmyndinni.
Ef þetta er staðan með margar tölur skaltu velja þær og hægrismella á valið svæði. Í samhengisvalmyndinni sem birtist skaltu velja Sniðið frumur (Sníða frumur) > flipi Númer (Númer) > snið Númer (Tölugildi) og ýttu á OK.
6. Það er bil í upphafi eða í lok
Þetta er minnsta augljósa ástæðan fyrir villunni. # N / A í virkni VPR, þar sem það er sjónrænt erfitt að sjá þessi aukarými, sérstaklega þegar unnið er með stórar töflur, þegar flest gögnin eru utan skjás.
Lausn 1: Aukabil í aðaltöflunni (þar sem VLOOKUP aðgerðin er)
Ef aukabil birtast í aðaltöflunni geturðu tryggt að formúlurnar virki rétt með því að láta rökstuðninginn fylgja með lookup_value (uppflettingargildi) í fall Snyrta (TRIM):
=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)
=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)
Lausn 2: Aukabil í uppflettitöflunni (í uppflettisdálki)
Ef aukabil eru í leitardálknum – einfaldar leiðir # N / A í formúlunni með VPR ekki hægt að forðast. Í staðinn fyrir VPR Þú getur notað fylkisformúlu með blöndu af aðgerðum INDEX (VÍSITALA), FYRIR MEIRA (MATCH) og Snyrta (TRIM):
=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))
Þar sem þetta er fylkisformúla, ekki gleyma að ýta á Ctrl + Shift + Sláðu inn í stað þess venjulega Sláðu inntil að slá inn formúluna rétt.
Villa #VALUE! í formúlum með VLOOKUP
Í flestum tilfellum tilkynnir Microsoft Excel um villu #VALUE! (#VALUE!) þegar gildið sem notað er í formúlunni passar ekki við gagnagerðina. Varðandi VPR, þá eru venjulega tvær ástæður fyrir villunni #VALUE!.
1. Gildið sem þú ert að leita að er lengra en 255 stafir
Verið varkár: virka VPR getur ekki leitað að gildum sem innihalda meira en 255 stafi. Ef gildið sem þú ert að leita að fer yfir þessi mörk færðu villuboð. #VALUE!.
Ákvörðun: Notaðu fullt af eiginleikum INDEX+MATCH (INDEX + MATCH). Hér að neðan er formúla sem mun duga vel fyrir þetta verkefni:
=INDEX(C2:C7,MATCH(TRUE,INDEX(B2:B7=F$2,0),0))
=ИНДЕКС(C2:C7;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B2:B7=F$2;0);0))
2. Full slóð að leitarvinnubókinni er ekki tilgreind
Ef þú ert að sækja gögn úr annarri vinnubók verður þú að tilgreina alla slóðina að þeirri skrá. Nánar tiltekið, þú verður að setja nafn vinnubókarinnar (þar á meðal framlenginguna) í hornklofa [ ], á eftir nafni blaðsins, fylgt eftir með upphrópunarmerki. Öll þessi smíði verður að vera bundin með postulum, ef bókin eða blaðnafnið inniheldur bil.
Hér er heildaruppbygging aðgerðarinnar VPR til að leita í annarri bók:
=VLOOKUP(lookup_value,'[workbook name]sheet name'!table_array, col_index_num,FALSE)
=ВПР(искомое_значение;'[имя_книги]имя_листа'!таблица;номер_столбца;ЛОЖЬ)
Hin raunverulega formúla gæti litið svona út:
=VLOOKUP($A$2,'[New Prices.xls]Sheet1'!$B:$D,3,FALSE)
=ВПР($A$2;'[New Prices.xls]Sheet1'!$B:$D;3;ЛОЖЬ)
Þessi formúla mun fletta upp hólfgildinu A2 í dálki B á blaðinu Sheet1 í vinnubókinni Nýtt verð og draga samsvarandi gildi úr dálknum D.
Ef einhverjum hluta af töfluslóðinni er sleppt, er aðgerðin þín VPR mun ekki virka og mun tilkynna villu #VALUE! (jafnvel þótt vinnubókin með uppflettitöflunni sé opin eins og er).
Fyrir frekari upplýsingar um aðgerðina VPRtilvísun í aðra Excel skrá, sjá lexíuna: Leita í annarri vinnubók með VLOOKUP.
3. Rök Dálkur_númer er minna en 1
Það er erfitt að ímynda sér aðstæður þar sem einhver slær inn gildi sem er minna en 1til að gefa til kynna dálkinn sem á að draga gildið úr. Þó það sé mögulegt ef gildi þessarar röksemdar er reiknað út af annarri Excel falli sem er hreiður inn í VPR.
Svo, ef það gerist að rökin col_index_numr (dálkanúmer) minna en 1virka VPR mun einnig tilkynna villu #VALUE!.
Ef rökin col_index_numr (column_number) er meiri en fjöldi dálka í tilteknu fylki, VPR mun tilkynna villu #REF! (#SSYL!).
Villa #NAME? í VLOOKUP
Einfaldasta málið er mistök # NAME? (#NAME?) – birtist ef þú skrifar óvart heiti falls með villu.
Lausnin er augljós - athugaðu stafsetninguna þína!
VLOOKUP virkar ekki (takmarkanir, fyrirvarar og ákvarðanir)
Til viðbótar við frekar flókna setningafræði, VPR hefur fleiri takmarkanir en nokkur önnur Excel aðgerð. Vegna þessara takmarkana, að því er virðist einfaldar formúlur með VPR leiða oft til óvæntra niðurstaðna. Hér að neðan finnur þú lausnir fyrir nokkrar algengar aðstæður þar sem VPR er rangt.
1. VLOOKUP er ekki hástafaviðkvæmt
virka VPR gerir ekki greinarmun á hástöfum og tekur við lágstöfum og hástöfum eins. Þess vegna, ef það eru nokkrir þættir í töflunni sem eru aðeins frábrugðnir í tilfellum, mun VLOOKUP fallið skila fyrsta þættinum sem fannst, óháð tilfelli.
Ákvörðun: Notaðu aðra Excel aðgerð sem getur framkvæmt lóðrétta leit (LOOKUP, SUMPRODUCT, INDEX og MATCH) ásamt NÁKVÆMLEGAA sem greinir tilfelli. Fyrir frekari upplýsingar geturðu lært af lexíunni - 4 leiðir til að gera VLOOKUP hástafanæm í Excel.
2. VLOOKUP skilar fyrsta gildinu sem fannst
Eins og þú veist nú þegar, VPR skilar gildinu úr tilteknum dálki sem samsvarar fyrstu samsvörun sem fannst. Hins vegar geturðu látið það draga út 2., 3., 4. eða hvaða aðra endurtekningu sem þú vilt. Ef þú þarft að draga út öll tvítekin gildi þarftu blöndu af aðgerðum INDEX (VÍSITALA), LESTA (LÍTILL) og LINE (RÖÐ).
3. Dálki var bætt við eða fjarlægður í töfluna
Því miður, formúlurnar VPR hætta að virka í hvert sinn sem nýjum dálki er bætt við eða fjarlægður í uppflettitöfluna. Þetta gerist vegna þess að setningafræði VPR krefst þess að þú tilgreinir allt svið leitarinnar og tiltekið dálknúmer fyrir gagnaútdrátt. Auðvitað breytast bæði tiltekið svið og dálknúmer þegar þú eyðir dálki eða setur inn nýjan.
Ákvörðun: Og aftur eru aðgerðir að flýta sér að hjálpa INDEX (VÍSITALA) og FYRIR MEIRA (LEIKUR). Í formúlunni INDEX+MATCH Þú skilgreinir sérstaklega leitar- og endurheimtardálka og þar af leiðandi geturðu eytt eða sett inn eins marga dálka og þú vilt án þess að hafa áhyggjur af því að þurfa að uppfæra allar tengdar leitarformúlur.
4. Frumutilvísanir eru rangar þegar formúla er afrituð
Þessi fyrirsögn útskýrir kjarna vandamálsins tæmandi, ekki satt?
Ákvörðun: Notaðu alltaf algjörar frumutilvísanir (með tákninu $) á skrám svið, til dæmis $A$2:$C$100 or $A:$C. Í formúlustikunni geturðu fljótt skipt um tengitegund með því að smella F4.
VLOOKUP – vinna með aðgerðirnar IFERROR og ISERROR
Ef þú vilt ekki hræða notendur með villuboðum # N / A, #VALUE! or # NAME?, þú getur sýnt tóman reit eða eigin skilaboð. Þú getur gert þetta með því að setja VPR í fall IFERROR (IFERROR) í Excel 2013, 2010 og 2007 eða notaðu fullt af aðgerðum EF+VILLA (IF+ERROR) í fyrri útgáfum.
VLOOKUP: vinna með IFERROR aðgerðina
Fallasetningafræði IFERROR (IFERROR) er einfalt og segir sig sjálft:
IFERROR(value,value_if_error)
ЕСЛИОШИБКА(значение;значение_если_ошибка)
Það er að segja að fyrir fyrstu röksemdin seturðu inn gildið sem á að athuga fyrir villu og fyrir seinni röksemdin tilgreinirðu hverju á að skila ef villa finnst.
Til dæmis, þessi formúla skilar tómum reit ef gildið sem þú ert að leita að finnst ekki:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"")
Ef þú vilt birta þín eigin skilaboð í stað staðlaðra villuboða aðgerðarinnar VPR, settu það innan gæsalappa, svona:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"Ничего не найдено. Попробуйте еще раз!")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"Ничего не найдено. Попробуйте еще раз!")
VLOOKUP: vinna með ISERROR aðgerðina
Þar sem aðgerðin IFERROR birtist í Excel 2007, þegar þú vinnur í fyrri útgáfum verður þú að nota samsetninguna IF (IF) og EOSHIBKA (ISERROR) svona:
=IF(ISERROR(VLOOKUP формула),"Ваше сообщение при ошибке",VLOOKUP формула)
=ЕСЛИ(ЕОШИБКА(ВПР формула);"Ваше сообщение при ошибке";ВПР формула)
Til dæmis formúlan EF+VILLA+ÚTLIT, svipað formúlunni IFERROR+FLOOKUPsýnt hér að ofan:
=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;ЛОЖЬ))
Það er allt í dag. Ég vona að þetta stutta námskeið hjálpi þér að takast á við öll hugsanleg mistök. VPR og láttu formúlurnar þínar virka rétt.