Përmbajtje
Ky mësim shpjegon se si të trajtojmë shpejt një situatë ku një funksion VPR (VLOOKUP) nuk dëshiron të punojë në Excel 2013, 2010, 2007 dhe 2003 dhe si të identifikojë dhe rregullojë gabimet e zakonshme dhe të kapërcejë kufizimet VPR.
Në disa artikuj të mëparshëm, ne kemi eksploruar aspekte të ndryshme të funksionit VPR në Excel. Nëse i keni lexuar me kujdes, tani duhet të jeni ekspert në këtë fushë. Megjithatë, nuk është pa arsye që shumë ekspertë të Excel besojnë VPR një nga karakteristikat më komplekse. Ka një sërë kufizimesh dhe veçorish që bëhen burim i shumë problemeve dhe gabimeve.
Në këtë artikull do të gjeni shpjegime të thjeshta të gabimeve #AT (# N/A), #NAME? (#EMRI?) dhe #VALUE! (#VLERË!) që shfaqen kur punoni me funksionin VPR, si dhe teknikat dhe metodat e trajtimit të tyre. Ne do të fillojmë me rastet më të zakonshme dhe arsyet më të dukshme pse. VPR nuk funksionon, kështu që është më mirë të studiohen shembujt në rendin në të cilin janë dhënë në artikull.
Rregullimi i gabimit #N/A në funksionin VLOOKUP në Excel
Në formulat me VPR mesazh gabimi #AT (#N/A) do të thotë i padisponueshem (pa të dhëna) – shfaqet kur Excel nuk mund të gjejë vlerën që kërkoni. Kjo mund të ndodhë për disa arsye.
1. Vlera e dëshiruar është shkruar gabim
Ide e mirë për të kontrolluar këtë artikull së pari! Gabimet e shtypit shpesh ndodhin kur punoni me sasi shumë të mëdha të dhënash, të përbërë nga mijëra rreshta, ose kur vlera që kërkoni është shkruar në një formulë.
2. #N/A gabim kur kërkoni për një përputhje të përafërt me VLOOKUP
Nëse përdorni një formulë me një kusht të përafërt të kërkimit të përputhjes, p.sh. argument kërkimi_rangut (range_lookup) është E VËRTETË ose nuk është specifikuar, formula juaj mund të raportojë një gabim # N / A në dy raste:
- Vlera për të kërkuar është më e vogël se vlera më e vogël në grupin që po shikohet.
- Kolona e kërkimit nuk është renditur në rend rritës.
3. #N/Një gabim kur kërkoni një përputhje të saktë me VLOOKUP
Nëse jeni duke kërkuar për një përputhje të saktë, dmth kërkimi_rangut (range_lookup) është FALSE dhe vlera e saktë nuk u gjet, formula do të raportojë gjithashtu një gabim # N / A. Mësoni më shumë se si të kërkoni për përputhje të sakta dhe të përafërta me një funksion VPR.
4. Kolona e kërkimit nuk është më e majta
Siç e dini ndoshta, një nga kufizimet më domethënëse VPR është se nuk mund të kthehet në të majtë, prandaj kolona e kërkimit në tabelën tuaj duhet të jetë më e majta. Në praktikë, ne shpesh e harrojmë këtë, gjë që çon në një formulë jofunksionale dhe një gabim. # N / A.
Vendimi: Nëse nuk është e mundur të ndryshoni strukturën e të dhënave në mënyrë që kolona e kërkimit të jetë në të majtë, mund të përdorni një kombinim funksionesh INDEKSI (INDEKS) dhe ME TE EKSPOZUAR (MATCH) si një alternativë më fleksibël për VPR.
5. Numrat janë formatuar si tekst
Një tjetër burim gabimi # N / A në formula me VPR janë numra në format teksti në tabelën kryesore ose në tabelën e kërkimit.
Kjo zakonisht ndodh kur importoni informacion nga bazat e të dhënave të jashtme, ose kur shkruani një apostrof përpara një numri për të mbajtur zeron e parë.
Shenjat më të dukshme të një numri në formatin e tekstit tregohen në figurën më poshtë:
Përveç kësaj, numrat mund të ruhen në format i përgjithshëm (Gjeneral). Në këtë rast, ka vetëm një veçori të dukshme - numrat janë rreshtuar në skajin e majtë të qelizës, ndërsa si parazgjedhje ato janë të rreshtuara në skajin e djathtë.
Vendimi: Nëse është një vlerë e vetme, thjesht klikoni në ikonën e gabimit dhe zgjidhni Konverto në numër (Konverto në Numër) nga menyja e kontekstit.
Nëse kjo është situata me shumë numra, zgjidhni ato dhe kliko me të djathtën në zonën e zgjedhur. Në menynë e kontekstit që shfaqet, zgjidhni Qelizat e formatit (Formatoni qelizat) > tab Numër (Numër) > format Numër (Numerike) dhe shtypni OK.
6. Ka një hapësirë në fillim ose në fund
Kjo është arsyeja më pak e dukshme për gabimin. # N / A në funksion VPR, pasi është e vështirë vizualisht të shihen këto hapësira shtesë, veçanërisht kur punoni me tabela të mëdha, kur shumica e të dhënave janë jashtë ekranit.
Zgjidhja 1: Hapësira shtesë në tabelën kryesore (ku është funksioni VLOOKUP)
Nëse në tabelën kryesore shfaqen hapësira shtesë, mund të siguroheni që formulat funksionojnë siç duhet duke bashkangjitur argumentin lookup_value (lookup_value) në një funksion TRIM (TRIM):
=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)
=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)
Zgjidhja 2: Hapësira shtesë në tabelën e kërkimit (në kolonën e kërkimit)
Nëse ka hapësira shtesë në kolonën e kërkimit - mënyra të thjeshta # N / A në formulën me VPR nuk mund të shmanget. Në vend të VPR Ju mund të përdorni një formulë grupi me një kombinim funksionesh INDEKSI (INDEKS), ME TE EKSPOZUAR (ndeshje) dhe TRIM (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))
Meqenëse kjo është një formulë grupi, mos harroni të shtypni Ctrl + Shift + Enter në vend të të zakonshmes Shkruanipër të futur saktë formulën.
Gabim #VALUE! në formula me VLOOKUP
Në shumicën e rasteve, Microsoft Excel raporton një gabim #VALUE! (#VALUE!) kur vlera e përdorur në formulë nuk përputhet me llojin e të dhënave. në lidhje me VPR, atëherë zakonisht ka dy arsye për gabimin #VALUE!.
1. Vlera që kërkoni është më e gjatë se 255 karaktere
Kini kujdes: funksion VPR nuk mund të kërkojë vlera që përmbajnë më shumë se 255 karaktere. Nëse vlera që kërkoni tejkalon këtë kufi, do të merrni një mesazh gabimi. #VALUE!.
Vendimi: Përdorni një sërë veçorish INDEKS+Përputhje (INDEKS + NDESHJE). Më poshtë është një formulë që do të funksionojë mirë për këtë detyrë:
=INDEX(C2:C7,MATCH(TRUE,INDEX(B2:B7=F$2,0),0))
=ИНДЕКС(C2:C7;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B2:B7=F$2;0);0))
2. Rruga e plotë për në librin e punës të kërkimit nuk është specifikuar
Nëse po merrni të dhëna nga një libër tjetër pune, duhet të specifikoni shtegun e plotë drejt atij skedari. Më konkretisht, duhet të përfshini emrin e librit të punës (duke përfshirë shtesën) në kllapa katrore [ ], të ndjekur nga emri i fletës, i ndjekur nga një pikëçuditëse. I gjithë ky ndërtim duhet të mbyllet me apostrofa, në rast se emri i librit ose i fletës përmban hapësira.
Këtu është struktura e plotë e funksionit VPR për të kërkuar në një libër tjetër:
=VLOOKUP(lookup_value,'[workbook name]sheet name'!table_array, col_index_num,FALSE)
=ВПР(искомое_значение;'[имя_книги]имя_листа'!таблица;номер_столбца;ЛОЖЬ)
Formula e vërtetë mund të duket si kjo:
=VLOOKUP($A$2,'[New Prices.xls]Sheet1'!$B:$D,3,FALSE)
=ВПР($A$2;'[New Prices.xls]Sheet1'!$B:$D;3;ЛОЖЬ)
Kjo formulë do të kërkojë vlerën e qelizës A2 në një kolonë B në fletë Sheet1 në fletoren e punës Çmimet e reja dhe nxirrni vlerën përkatëse nga kolona D.
Nëse ndonjë pjesë e shtegut të tabelës hiqet, funksioni juaj VPR nuk do të funksionojë dhe do të raportojë një gabim #VALUE! (edhe nëse libri i punës me tabelën e kërkimit është aktualisht i hapur).
Për më shumë informacion rreth funksionit VPRduke iu referuar një skedari tjetër Excel, shihni mësimin: Kërkimi i një libri tjetër pune duke përdorur VLOOKUP.
3. Argumenti Column_num është më i vogël se 1
Është e vështirë të imagjinohet një situatë ku dikush fut një vlerë më të vogël se 1për të treguar kolonën nga e cila do të nxirret vlera. Edhe pse është e mundur nëse vlera e këtij argumenti llogaritet nga një funksion tjetër Excel i vendosur brenda VPR.
Pra, nëse ndodh që argumenti col_index_num (numri_kolona) më pak se 1funksion VPR do të raportojë gjithashtu një gabim #VALUE!.
Nëse argumenti col_index_num (numri_kolona) është më i madh se numri i kolonave në grupin e dhënë, VPR do të raportojë një gabim # REF! (#SSYL!).
Gabim #NAME? në VLOOKUP
Rasti më i thjeshtë është një gabim #NAME? (#EMRI?) – do të shfaqet nëse shkruani aksidentalisht një emër funksioni me një gabim.
Zgjidhja është e qartë - kontrolloni drejtshkrimin tuaj!
VLOOKUP nuk funksionon (kufizime, paralajmërime dhe vendime)
Përveç sintaksës mjaft të ndërlikuar, VPR ka më shumë kufizime se çdo funksion tjetër Excel. Për shkak të këtyre kufizimeve, formula në dukje të thjeshta me VPR shpesh çojnë në rezultate të papritura. Më poshtë do të gjeni zgjidhje për disa skenarë të zakonshëm ku VPR eshte gabim.
1. VLOOKUP nuk është sensitive
funksion VPR nuk bën dallimin midis shkronjave të mëdha dhe i pranon shkronjat e vogla dhe të mëdha si të njëjta. Prandaj, nëse ka disa elementë në tabelë që ndryshojnë vetëm sipas rastit, funksioni VLOOKUP do të kthejë elementin e parë të gjetur, pavarësisht nga rasti.
Vendimi: Përdorni një funksion tjetër të Excel-it që mund të kryejë një kërkim vertikal (LOOKUP, SUMPRODUCT, INDEX dhe MATCH) në kombinim me saktëNjë rast që dallon. Për më shumë detaje, mund të mësoni nga mësimi – 4 mënyra për ta bërë VLOOKUP-in të ndjeshëm ndaj shkronjave të vogla në Excel.
2. VLOOKUP kthen vlerën e parë të gjetur
Siç e dini tashmë, VPR kthen vlerën nga kolona e dhënë që korrespondon me përputhjen e parë të gjetur. Sidoqoftë, mund ta bëni atë të nxjerrë 2, 3, 4 ose çdo përsëritje tjetër të vlerës që dëshironi. Nëse keni nevojë të nxirrni të gjitha vlerat e kopjuara, do t'ju duhet një kombinim funksionesh INDEKSI (INDEKS), pAKTËN (E VOGLA) dhe LINE (ROW).
3. Një kolonë u shtua ose u hoq në tabelë
Fatkeqësisht, formulat VPR ndaloni së punuari sa herë që një kolonë e re shtohet ose hiqet në tabelën e kërkimit. Kjo ndodh për shkak të sintaksës VPR kërkon që ju të specifikoni gamën e plotë të kërkimit dhe numrin specifik të kolonës për nxjerrjen e të dhënave. Natyrisht, si diapazoni i dhënë ashtu edhe numri i kolonës ndryshojnë kur fshini një kolonë ose futni një të re.
Vendimi: Dhe përsëri funksionet janë me nxitim për të ndihmuar INDEKSI (INDEKS) dhe ME TE EKSPOZUAR (NDESHJE). Në formulë INDEKS+Përputhje Ju përcaktoni veçmas kolonat e kërkimit dhe rikthimit, dhe si rezultat, mund të fshini ose futni sa më shumë kolona që dëshironi pa u shqetësuar se duhet të përditësoni të gjitha formulat e kërkimit përkatës.
4. Referencat e qelizave janë të ngatërruara kur kopjoni një formulë
Ky titull shpjegon thelbin e problemit në mënyrë shteruese, apo jo?
Vendimi: Përdorni gjithmonë referenca absolute të qelizave (me simbolin $) në regjistrimet e diapazonit, për shembull 2 $ A $: $ C $ 100 or $A:$C. Në shiritin e formulës, mund të ndërroni shpejt llojin e lidhjes duke klikuar F4.
VLOOKUP – duke punuar me funksionet IFERROR dhe ISERROR
Nëse nuk doni të trembni përdoruesit me mesazhe gabimi # N / A, #VALUE! or #NAME?, mund të shfaqni një qelizë të zbrazët ose mesazhin tuaj. Ju mund ta bëni këtë duke vendosur VPR në një funksion IFERROR (IFERROR) në Excel 2013, 2010 dhe 2007 ose përdorni një sërë funksionesh NËSE+ISERROR (IF+ISERROR) në versionet e mëparshme.
VLOOKUP: duke punuar me funksionin IFERROR
Sintaksa e funksionit IFERROR (IFERROR) është e thjeshtë dhe flet vetë:
IFERROR(value,value_if_error)
ЕСЛИОШИБКА(значение;значение_если_ошибка)
Kjo do të thotë, për argumentin e parë ju vendosni vlerën që do të kontrollohet për një gabim, dhe për argumentin e dytë ju specifikoni se çfarë të ktheni nëse gjendet një gabim.
Për shembull, kjo formulë kthen një qelizë boshe nëse vlera që po kërkoni nuk gjendet:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"")
Nëse dëshironi të shfaqni mesazhin tuaj në vend të mesazhit standard të gabimit të funksionit VPR, vendoseni në thonjëza, si kjo:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"Ничего не найдено. Попробуйте еще раз!")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"Ничего не найдено. Попробуйте еще раз!")
VLOOKUP: duke punuar me funksionin ISERROR
Që nga funksioni IFERROR u shfaq në Excel 2007, kur punoni në versionet e mëparshme do të duhet të përdorni kombinimin IF (NËSE) dhe EOSHIBKA (ISERROR) si kjo:
=IF(ISERROR(VLOOKUP формула),"Ваше сообщение при ошибке",VLOOKUP формула)
=ЕСЛИ(ЕОШИБКА(ВПР формула);"Ваше сообщение при ошибке";ВПР формула)
Për shembull, formula IF+ISERROR+VLOOKUP, e ngjashme me formulën IFERROR+VLOOKUPtreguar më sipër:
=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;ЛОЖЬ))
Kaq për sot. Shpresoj se ky tutorial i shkurtër do t'ju ndihmojë të përballeni me të gjitha gabimet e mundshme. VPR dhe bëni që formulat tuaja të funksionojnë siç duhet.