Saturs
Šajā nodarbībā ir izskaidrots, kā ātri rīkoties situācijā, kad funkcija VPR (VLOOKUP) nevēlas darboties programmā Excel 2013, 2010, 2007 un 2003, kā arī to, kā noteikt un labot izplatītākās kļūdas un pārvarēt ierobežojumus. VPR.
Vairākos iepriekšējos rakstos mēs esam izpētījuši dažādus funkcijas aspektus VPR programmā Excel. Ja esat tos rūpīgi izlasījis, jums tagad vajadzētu būt ekspertam šajā jomā. Tomēr ne velti uzskata daudzi Excel eksperti VPR viena no sarežģītākajām funkcijām. Tam ir virkne ierobežojumu un funkciju, kas kļūst par daudzu problēmu un kļūdu avotu.
Šajā rakstā jūs atradīsit vienkāršus kļūdu skaidrojumus # AT (#N/A), #name? (#NAME?) un #VĒRTĪBA! (#VALUE!), kas parādās, strādājot ar funkciju VPR, kā arī metodes un metodes, kā ar tiem tikt galā. Sāksim ar visbiežāk sastopamajiem gadījumiem un acīmredzamākajiem iemesliem. VPR nedarbojas, tāpēc labāk ir izpētīt piemērus tādā secībā, kādā tie norādīti rakstā.
Kļūdas #N/A labošana funkcijā VLOOKUP programmā Excel
Formulās ar VPR kļūdas ziņojums # AT (#N/A) nozīmē nav pieejams (nav datu) — parādās, kad Excel nevar atrast meklēto vērtību. Tas var notikt vairāku iemeslu dēļ.
1. Vēlamā vērtība ir uzrakstīta nepareizi
Laba ideja vispirms pārbaudīt šo vienumu! Drukāšanas kļūdas bieži rodas, strādājot ar ļoti lielu datu apjomu, kas sastāv no tūkstošiem rindu, vai ja meklētā vērtība ir ierakstīta formulā.
2. #N/A kļūda, meklējot aptuvenu atbilstību ar VLOOKUP
Ja izmantojat formulu ar aptuvenu atbilstības meklēšanas nosacījumu, ti, argumentu diapazona_uzmeklēšana (range_lookup) ir TRUE vai nav norādīts, jūsu formula var ziņot par kļūdu # N / A divos gadījumos:
- Vērtība, kas jāmeklē, ir mazāka par mazāko vērtību meklējamā masīvā.
- Meklēšanas kolonna nav sakārtota augošā secībā.
3. #N/A kļūda, meklējot precīzu atbilstību ar VLOOKUP
Ja meklējat precīzu atbilstību, ti, argumentu diapazona_uzmeklēšana (range_lookup) ir FALSE, un precīza vērtība netika atrasta, formula ziņos arī par kļūdu # N / A. Uzziniet vairāk par to, kā meklēt precīzas un aptuvenas atbilstības ar funkciju VPR.
4. Meklēšanas kolonna nav vistālāk pa kreisi
Kā jūs droši vien zināt, viens no būtiskākajiem ierobežojumiem VPR tas ir tāds, ka tas nevar būt pavērsts pa kreisi, tāpēc uzmeklēšanas kolonnai jūsu tabulā jābūt vistālāk pa kreisi. Praksē mēs par to bieži aizmirstam, kas noved pie nestrādājošas formulas un kļūdas. # N / A.
Lēmums: Ja nav iespējams mainīt datu struktūru tā, lai meklēšanas kolonna būtu vistālāk pa kreisi, varat izmantot funkciju kombināciju INDEX (INDEKSS) un VAIRĀK ATKLĀTA (MATCH) kā elastīgāku alternatīvu VPR.
5. Cipari tiek formatēti kā teksts
Vēl viens kļūdu avots # N / A formulās ar VPR ir skaitļi teksta formātā galvenajā tabulā vai uzmeklēšanas tabulā.
Tas parasti notiek, importējot informāciju no ārējām datu bāzēm vai ievadot apostrofu pirms skaitļa, lai saglabātu sākuma nulli.
Acīmredzamākās skaitļa zīmes teksta formātā ir parādītas zemāk esošajā attēlā:
Turklāt skaitļus var saglabāt formātā vispārējs (Vispārīgi). Šajā gadījumā ir tikai viena pamanāma iezīme – skaitļi ir līdzināti šūnas kreisajai malai, savukārt pēc noklusējuma tie tiek izlīdzināti ar labo malu.
Lēmums: Ja tā ir viena vērtība, vienkārši noklikšķiniet uz kļūdas ikonas un atlasiet Konvertēt uz numuru (Konvertēt uz numuru) konteksta izvēlnē.
Ja tā ir situācija ar daudziem cipariem, atlasiet tos un ar peles labo pogu noklikšķiniet uz atlasītā apgabala. Parādītajā konteksta izvēlnē atlasiet Formatēt šūnas (Formatēt šūnas) > cilne Skaits (Numurs) > formāts Skaits (Cipars) un nospiediet OK.
6. Sākumā vai beigās ir atstarpe
Tas ir vismazāk acīmredzamais kļūdas iemesls. # N / A funkcijā VPR, jo ir vizuāli grūti saskatīt šīs papildu vietas, īpaši strādājot ar lielām tabulām, kad lielākā daļa datu ir ārpus ekrāna.
1. risinājums: papildu atstarpes galvenajā tabulā (kur ir funkcija VLOOKUP)
Ja galvenajā tabulā parādās papildu atstarpes, varat nodrošināt, ka formulas darbojas pareizi, pievienojot argumentu lookup_value (meklēšanas_vērtība) par funkciju TRIM (TRIM):
=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)
=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)
2. risinājums: papildu atstarpes uzmeklēšanas tabulā (uzmeklēšanas kolonnā)
Ja meklēšanas kolonnā ir papildu atstarpes - vienkārši veidi # N / A formulā ar VPR nevar izvairīties. Tā vietā VPR Varat izmantot masīva formulu ar funkciju kombināciju INDEX (INDEX), VAIRĀK ATKLĀTA (MATCH) и 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))
Tā kā šī ir masīva formula, neaizmirstiet nospiest Ctrl + Shift + Enter ierastā vietā ienāktlai pareizi ievadītu formulu.
Kļūda #VALUE! formulās ar VLOOKUP
Vairumā gadījumu programma Microsoft Excel ziņo par kļūdu #VĒRTĪBA! (#VALUE!), ja formulā izmantotā vērtība neatbilst datu tipam. Kas attiecas uz VPR, tad parasti kļūdai ir divi iemesli #VĒRTĪBA!.
1. Meklētā vērtība ir garāka par 255 rakstzīmēm
Esiet uzmanīgi: funkcija VPR nevar meklēt vērtības, kas satur vairāk nekā 255 rakstzīmes. Ja meklētā vērtība pārsniedz šo ierobežojumu, jūs saņemsit kļūdas ziņojumu. #VĒRTĪBA!.
Lēmums: Izmantojiet vairākas funkcijas INDEX+MATCH (INDEKSS + MATCH). Zemāk ir formula, kas lieliski noderēs šim uzdevumam:
=INDEX(C2:C7,MATCH(TRUE,INDEX(B2:B7=F$2,0),0))
=ИНДЕКС(C2:C7;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B2:B7=F$2;0);0))
2. Nav norādīts pilns ceļš uz meklēšanas darbgrāmatu
Ja izgūstat datus no citas darbgrāmatas, jums jānorāda pilns ceļš uz šo failu. Konkrētāk, kvadrātiekavās [ ] ir jāiekļauj darbgrāmatas nosaukums (ieskaitot paplašinājumu), kam seko lapas nosaukums, kam seko izsaukuma zīme. Visa šī konstrukcija jāiekļauj apostrofos, ja grāmatas vai lapas nosaukumā ir atstarpes.
Šeit ir visa funkcijas struktūra VPR lai meklētu citā grāmatā:
=VLOOKUP(lookup_value,'[workbook name]sheet name'!table_array, col_index_num,FALSE)
=ВПР(искомое_значение;'[имя_книги]имя_листа'!таблица;номер_столбца;ЛОЖЬ)
Reālā formula varētu izskatīties šādi:
=VLOOKUP($A$2,'[New Prices.xls]Sheet1'!$B:$D,3,FALSE)
=ВПР($A$2;'[New Prices.xls]Sheet1'!$B:$D;3;ЛОЖЬ)
Šī formula meklēs šūnas vērtību A2 kolonnā B uz lapas Sheet1 darba burtnīcā Jaunas cenas un izņemiet atbilstošo vērtību no kolonnas D.
Ja kāda tabulas ceļa daļa ir izlaista, jūsu funkcija VPR nedarbosies un ziņos par kļūdu #VĒRTĪBA! (pat ja darbgrāmata ar uzmeklēšanas tabulu pašlaik ir atvērta).
Lai iegūtu papildinformāciju par funkciju VPRatsaucoties uz citu Excel failu, skatiet nodarbību: Citas darbgrāmatas meklēšana, izmantojot VLOOKUP.
3. Argumenta kolonnas_numurs ir mazāks par 1
Grūti iedomāties situāciju, kad kāds ievada vērtību, kas ir mazāka par 1lai norādītu kolonnu, no kuras iegūt vērtību. Lai gan tas ir iespējams, ja šī argumenta vērtību aprēķina cita Excel funkcija, kas ir ligzdota VPR.
Tātad, ja tas notiek, ka arguments col_index_num (kolonnas_numurs) mazāks par 1funkcija VPR ziņos arī par kļūdu #VĒRTĪBA!.
Ja arguments col_index_num (column_number) ir lielāks par kolonnu skaitu dotajā masīvā, VPR ziņos par kļūdu #REF! (#SSYL!).
Kļūda #NAME? pakalpojumā VLOOKUP
Vienkāršākais gadījums ir kļūda #name? (#NAME?) — parādīsies, ja nejauši ierakstīsit funkcijas nosaukumu ar kļūdu.
Risinājums ir acīmredzams – pārbaudiet pareizrakstību!
VLOOKUP nedarbojas (ierobežojumi, brīdinājumi un lēmumi)
Papildus diezgan sarežģītajai sintaksei, VPR ir vairāk ierobežojumu nekā jebkurai citai Excel funkcijai. Šo ierobežojumu dēļ šķietami vienkāršas formulas ar VPR bieži noved pie negaidītiem rezultātiem. Zemāk atradīsit risinājumus vairākiem izplatītiem scenārijiem, kur VPR ir nepareizi.
1. VLOOKUP nav reģistrjutīga
funkcija VPR neatšķir reģistrus un pieņem mazos un lielos burtus kā vienus un tos pašus. Tāpēc, ja tabulā ir vairāki elementi, kas atšķiras tikai gadījuma ziņā, funkcija VLOOKUP atgriezīs pirmo atrasto elementu neatkarīgi no reģistra.
Lēmums: Izmantojiet citu Excel funkciju, kas var veikt vertikālu meklēšanu (LOOKUP, SUMPRODUCT, INDEX un MATCH) kopā ar TIKAIA, kas atšķir gadījumu. Lai iegūtu sīkāku informāciju, varat mācīties no nodarbības — 4 veidi, kā programmā Excel padarīt VLOOKUP reģistrjutīgu.
2. VLOOKUP atgriež pirmo atrasto vērtību
Kā jūs jau zināt, VPR atgriež vērtību no dotās kolonnas, kas atbilst pirmajai atrastajai atbilstībai. Tomēr varat likt tai iegūt 2., 3., 4. vai jebkuru citu vajadzīgās vērtības atkārtojumu. Ja nepieciešams iegūt visas dublētās vērtības, jums būs nepieciešama funkciju kombinācija INDEX (INDEX), Vismazāk (MAZĀ) un LINE (RINDA).
3. Tabulai tika pievienota vai noņemta kolonna
Diemžēl formulas VPR pārtrauciet darbu katru reizi, kad uzmeklēšanas tabulai tiek pievienota vai noņemta jauna kolonna. Tas notiek tāpēc, ka sintakse VPR jums ir jānorāda pilns meklēšanas diapazons un konkrēts kolonnas numurs datu ieguvei. Protams, dzēšot kolonnu vai ievietojot jaunu, mainās gan dotais diapazons, gan kolonnas numurs.
Lēmums: Un atkal funkcijas steidzas palīgā INDEX (INDEKSS) un VAIRĀK ATKLĀTA (MATCH). Formulā INDEX+MATCH Jūs atsevišķi definējat meklēšanas un izguves kolonnas, kā rezultātā varat dzēst vai ievietot tik daudz kolonnu, cik vēlaties, neuztraucoties par visu saistīto meklēšanas formulu atjaunināšanu.
4. Kopējot formulu, šūnu atsauces tiek izkropļotas
Šis virsraksts izsmeļoši izskaidro problēmas būtību, vai ne?
Lēmums: Vienmēr izmantojiet absolūtās šūnu atsauces (ar simbolu $), piemēram, ieraksta diapazonu 2 $: 100 C$ or $A:$C. Formulas joslā varat ātri pārslēgt saites veidu, noklikšķinot uz F4.
VLOOKUP – darbs ar funkcijām IFERROR un ISERROR
Ja nevēlaties biedēt lietotājus ar kļūdu ziņojumiem # N / A, #VĒRTĪBA! or #name?, varat parādīt tukšu šūnu vai savu ziņojumu. To var izdarīt, ievietojot VPR par funkciju IFERROR (IFERROR) programmā Excel 2013, 2010 un 2007 vai izmantojiet vairākas funkcijas JA+ATKLĀJUMS (IF+ISERROR) iepriekšējās versijās.
VLOOKUP: darbs ar funkciju IFERROR
Funkciju sintakse IFERROR (IFERROR) ir vienkāršs un runā pats par sevi:
IFERROR(value,value_if_error)
ЕСЛИОШИБКА(значение;значение_если_ошибка)
Tas nozīmē, ka pirmajam argumentam jūs ievietojat vērtību, kas jāpārbauda, vai nav kļūdas, un otrajam argumentam norādāt, ko atgriezt, ja tiek atrasta kļūda.
Piemēram, šī formula atgriež tukšu šūnu, ja meklētā vērtība nav atrasta:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"")
Ja vēlaties parādīt savu ziņojumu, nevis funkcijas standarta kļūdas ziņojumu VPR, ievietojiet to pēdiņās, piemēram:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"Ничего не найдено. Попробуйте еще раз!")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"Ничего не найдено. Попробуйте еще раз!")
VLOOKUP: darbs ar funkciju ISERROR
Kopš funkcijas IFERROR parādījās programmā Excel 2007, strādājot ar iepriekšējām versijām, jums būs jāizmanto kombinācija IF (IF) un EOSHIBKA (ISERROR) kā šis:
=IF(ISERROR(VLOOKUP формула),"Ваше сообщение при ошибке",VLOOKUP формула)
=ЕСЛИ(ЕОШИБКА(ВПР формула);"Ваше сообщение при ошибке";ВПР формула)
Piemēram, formula IF+ISERROR+VLOOKUP, līdzīgi kā formula IFERROR+VLOOKUPparādīts iepriekš:
=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;ЛОЖЬ))
Tas šodienai viss. Es ceru, ka šī īsā apmācība palīdzēs jums tikt galā ar visām iespējamām kļūdām. VPR un liek savām formulām darboties pareizi.