Բովանդակություն
Այս դասը բացատրում է, թե ինչպես արագ վարվել մի իրավիճակի հետ, որտեղ գործառույթը VPR- ը (VLOOKUP) չի ցանկանում աշխատել Excel 2013, 2010, 2007 և 2003 թվականներում և ինչպես բացահայտել և ուղղել ընդհանուր սխալները և հաղթահարել սահմանափակումները: VPR- ը.
Նախորդ մի քանի հոդվածներում մենք ուսումնասիրել ենք ֆունկցիայի տարբեր կողմերը VPR- ը Excel-ում։ Եթե դուք ուշադիր կարդացել եք դրանք, ապա այժմ պետք է մասնագետ լինեք այս ոլորտում: Այնուամենայնիվ, Excel-ի շատ փորձագետներ կարծում են առանց պատճառի VPR- ը առավել բարդ հատկանիշներից մեկը: Այն ունի մի շարք սահմանափակումներ և առանձնահատկություններ, որոնք դառնում են բազմաթիվ խնդիրների և սխալների աղբյուր:
Այս հոդվածում դուք կգտնեք սխալների պարզ բացատրություններ #AT (# N/A), #ԱՆՈՒՆ? (#ԱՆՈՒՆ?) և #VALUE! (#VALUE!), որոնք հայտնվում են ֆունկցիայի հետ աշխատելիս VPR- ը, ինչպես նաև դրանց հետ վարվելու տեխնիկան և մեթոդները։ Մենք կսկսենք ամենատարածված դեպքերից և դրա համար առավել ակնհայտ պատճառներից: VPR- ը չի աշխատում, ուստի ավելի լավ է օրինակներն ուսումնասիրել հոդվածում տրված հերթականությամբ:
Excel-ում VLOOKUP ֆունկցիայի #N/A սխալի շտկում
հետ բանաձևերում VPR- ը սխալի հաղորդագրություն #AT (#N/A) նշանակում է անհասանելի (տվյալներ չկան) – հայտնվում է, երբ Excel-ը չի կարողանում գտնել ձեր փնտրած արժեքը: Դա կարող է տեղի ունենալ մի քանի պատճառներով.
1. Ցանկալի արժեքը սխալ է գրված
Լավ գաղափար է նախ ստուգել այս տարրը: Տառասխալները հաճախ տեղի են ունենում, երբ աշխատում եք շատ մեծ քանակությամբ տվյալների հետ՝ բաղկացած հազարավոր տողերից, կամ երբ ձեր փնտրած արժեքը գրված է բանաձևի մեջ:
2. #N/A սխալ VLOOKUP-ի հետ մոտավոր համընկնում փնտրելիս
Եթե դուք օգտագործում եք բանաձև՝ համապատասխանության որոնման մոտավոր պայմանով, այսինքն՝ փաստարկ միջակայքի_որոնում (range_lookup) ճշմարիտ է կամ նշված չէ, ձեր բանաձևը կարող է հայտնել սխալի մասին # N / A երկու դեպքում.
- Փնտրվող արժեքն ավելի փոքր է, քան փնտրվող զանգվածի ամենափոքր արժեքը:
- Որոնման սյունակը դասավորված չէ աճման կարգով:
3. #N/A սխալ VLOOKUP-ի հետ ճշգրիտ համընկնում փնտրելիս
Եթե դուք փնտրում եք ճշգրիտ համընկնում, այսինքն՝ փաստարկ միջակայքի_որոնում (range_lookup)-ը FALSE է, և ճշգրիտ արժեքը չի գտնվել, բանաձևը նույնպես կհաղորդի սխալի մասին # N / A. Իմացեք ավելին, թե ինչպես որոնել գործառույթի հետ ճշգրիտ և մոտավոր համընկնում VPR- ը.
4. Որոնման սյունակը ձախ կողմում չէ
Ինչպես հավանաբար գիտեք, ամենակարևոր սահմանափակումներից մեկը VPR- ը դա այն է, որ այն չի կարող ձախ կողմում լինել, հետևաբար ձեր աղյուսակի որոնման սյունակը պետք է լինի ամենաձախը: Գործնականում մենք հաճախ մոռանում ենք սրա մասին, ինչը հանգեցնում է չաշխատող բանաձեւի ու սխալի։ # N / A.
Որոշում: Եթե հնարավոր չէ փոխել տվյալների կառուցվածքն այնպես, որ որոնման սյունակը մնա ամենաձախը, կարող եք օգտագործել ֆունկցիաների համակցություն INDEX (INDEX) և ԱՎԵԼԻ ԲԱՑՎԱԾ (MATCH)՝ որպես ավելի ճկուն այլընտրանք VPR- ը.
5. Թվերը ձևակերպվում են որպես տեքստ
Մեկ այլ սխալի աղբյուր # N / A հետ բանաձեւերով VPR- ը հիմնական աղյուսակում կամ որոնման աղյուսակում տեքստային ձևաչափով թվեր են:
Սա սովորաբար տեղի է ունենում, երբ դուք տեղեկատվություն ներմուծում եք արտաքին տվյալների բազաներից, կամ երբ մուտքագրում եք ապաստրոֆ՝ թվից առաջ՝ առաջատար զրոը պահելու համար:
Տեքստի ձևաչափով թվի առավել ակնհայտ նշանները ներկայացված են ստորև բերված նկարում.
Բացի այդ, թվերը կարող են պահվել ձևաչափով ընդհանուր (Գեներալ): Այս դեպքում կա միայն մեկ նկատելի առանձնահատկություն՝ թվերը հավասարեցված են բջիջի ձախ եզրին, մինչդեռ լռելյայնորեն դրանք հավասարեցված են աջ եզրին:
Որոշում: Եթե դա մեկ արժեք է, պարզապես սեղմեք սխալի պատկերակի վրա և ընտրեք Փոխարկել համարին (Փոխակերպեք համարին) համատեքստի ընտրացանկից:
Եթե սա շատ թվերի հետ կապված իրավիճակ է, ընտրեք դրանք և աջ սեղմեք ընտրված տարածքի վրա: Համատեքստի ընտրացանկում, որը հայտնվում է, ընտրեք Ձևաչափել բջիջները (Ձևաչափել բջիջները) > ներդիր Թիվ (համար) > ձևաչափ Թիվ (Թվային) և սեղմել OK.
6. Սկզբում կամ վերջում կա բացատ
Սա սխալի ամենաքիչ ակնհայտ պատճառն է: # N / A ֆունկցիայի մեջ VPR- ը, քանի որ տեսողականորեն դժվար է տեսնել այս լրացուցիչ տարածքները, հատկապես մեծ սեղանների հետ աշխատելիս, երբ տվյալների մեծ մասը էկրանից դուրս է:
Լուծում 1. Լրացուցիչ բացատներ հիմնական աղյուսակում (որտեղ VLOOKUP ֆունկցիան է)
Եթե հիմնական աղյուսակում հայտնվեն լրացուցիչ բացատներ, կարող եք համոզվել, որ բանաձևերը ճիշտ են աշխատում՝ կցելով փաստարկը lookup_value (lookup_value) ֆունկցիայի մեջ TRIM- ը (TRIM):
=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)
=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)
Լուծում 2. Լրացուցիչ բացատներ որոնման աղյուսակում (որոնման սյունակում)
Եթե որոնման սյունակում լրացուցիչ տարածքներ կան, պարզ ուղիներ # N / A հետ բանաձեւում VPR- ը չի կարելի խուսափել. Փոխարեն VPR- ը Դուք կարող եք օգտագործել զանգվածի բանաձև՝ ֆունկցիաների համակցությամբ INDEX (INDEX), ԱՎԵԼԻ ԲԱՑՎԱԾ (ՀԱՄԱԽՄԲՈՒՄ) и 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))
Քանի որ սա զանգվածի բանաձև է, մի մոռացեք սեղմել Ctrl + Shift + Մուտք սովորականի փոխարեն Մտնելբանաձևը ճիշտ մուտքագրելու համար.
Սխալ #VALUE: VLOOKUP-ով բանաձևերում
Շատ դեպքերում Microsoft Excel-ը հաղորդում է սխալ #VALUE! (#VALUE!), երբ բանաձևում օգտագործվող արժեքը չի համընկնում տվյալների տեսակի հետ: Ինչ վերաբերում է VPR- ը, ապա սովորաբար սխալի երկու պատճառ կա #VALUE!.
1. Ձեր փնտրած արժեքը 255 նիշից ավելի է
Զգույշ եղեք՝ գործառույթ VPR- ը չի կարող որոնել ավելի քան 255 նիշ պարունակող արժեքներ: Եթե ձեր փնտրած արժեքը գերազանցում է այս սահմանը, դուք կստանաք սխալի հաղորդագրություն: #VALUE!.
Որոշում: Օգտագործեք մի շարք առանձնահատկություններ INDEX+MATCH (ԻԴԵՔՍ + ՄԱՏՉ): Ստորև բերված է մի բանաձև, որը լավ կկատարի այս առաջադրանքը.
=INDEX(C2:C7,MATCH(TRUE,INDEX(B2:B7=F$2,0),0))
=ИНДЕКС(C2:C7;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B2:B7=F$2;0);0))
2. Որոնման աշխատանքային գրքույկի ամբողջական ուղին նշված չէ
Եթե դուք տվյալներ եք առբերում մեկ այլ աշխատանքային գրքույկից, ապա պետք է նշեք այդ ֆայլի ամբողջական ուղին: Ավելի կոնկրետ՝ քառակուսի փակագծերում [ ] պետք է ներառեք աշխատանքային գրքի անվանումը (ներառյալ ընդլայնումը), որին հաջորդում է թերթի անունը, որին հաջորդում է բացականչական կետը: Այս ամբողջ կոնստրուկցիան պետք է փակցվի ապոստրոֆների մեջ, եթե գրքի կամ թերթիկի անվանումը բացատներ է պարունակում:
Ահա ֆունկցիայի ամբողջական կառուցվածքը VPR- ը մեկ այլ գրքում որոնելու համար.
=VLOOKUP(lookup_value,'[workbook name]sheet name'!table_array, col_index_num,FALSE)
=ВПР(искомое_значение;'[имя_книги]имя_листа'!таблица;номер_столбца;ЛОЖЬ)
Իրական բանաձևը կարող է այսպիսի տեսք ունենալ.
=VLOOKUP($A$2,'[New Prices.xls]Sheet1'!$B:$D,3,FALSE)
=ВПР($A$2;'[New Prices.xls]Sheet1'!$B:$D;3;ЛОЖЬ)
Այս բանաձևը կփնտրի բջջային արժեքը A2 սյունակում B թերթիկի վրա Sheet1 աշխատանքային գրքում Նոր գներ և սյունակից հանել համապատասխան արժեքը D.
Եթե աղյուսակի ճանապարհի որևէ հատված բաց է թողնված, ապա ձեր գործառույթը VPR- ը չի աշխատի և կհայտնի սխալի մասին #VALUE! (նույնիսկ եթե որոնման աղյուսակով աշխատանքային գիրքը ներկայումս բաց է):
Գործառույթի մասին լրացուցիչ տեղեկությունների համար VPR- ըհղում կատարելով մեկ այլ Excel ֆայլի, տես դասը. Որոնել մեկ այլ աշխատանքային գրքույկ VLOOKUP-ի միջոցով:
3. Արգումենտ Column_num-ը փոքր է 1-ից
Դժվար է պատկերացնել մի իրավիճակ, երբ ինչ-որ մեկը մուտքագրի ավելի քիչ արժեք 1նշելու այն սյունակը, որտեղից հանվում է արժեքը: Թեև դա հնարավոր է, եթե այս արգումենտի արժեքը հաշվարկվի ներսում տեղադրված մեկ այլ Excel ֆունկցիայի միջոցով VPR- ը.
Այսպիսով, եթե պատահի, որ փաստարկը col_index_num (սյունակի_համարը) պակաս քան 1ֆունկցիա VPR- ը կհայտնի նաև սխալի մասին #VALUE!.
Եթե փաստարկը col_index_num (column_number) ավելի մեծ է, քան տվյալ զանգվածի սյունակների թիվը, VPR- ը կհայտնի սխալի մասին # REF! (#SSYL!):
Սխա՞լ է #NAME: VLOOKUP-ում
Ամենապարզ դեպքը սխալ է #ԱՆՈՒՆ? (#NAME?) – կհայտնվի, եթե սխալմամբ ֆունկցիայի անունը սխալմամբ գրեք:
Լուծումն ակնհայտ է. ստուգեք ձեր ուղղագրությունը:
VLOOKUP-ը չի աշխատում (սահմանափակումներ, նախազգուշացումներ և որոշումներ)
Բացի բավականին բարդ շարահյուսությունից, VPR- ը ունի ավելի շատ սահմանափակումներ, քան ցանկացած այլ Excel ֆունկցիա: Այս սահմանափակումների պատճառով պարզ թվացող բանաձևերը հետ VPR- ը հաճախ հանգեցնում են անսպասելի արդյունքների: Ստորև դուք կգտնեք լուծումներ մի քանի ընդհանուր սցենարների համար, որտեղ VPR- ը սխալ է.
1. VLOOKUP-ը մեծատառերի զգայուն չէ
ֆունկցիա VPR- ը չի տարբերում մեծատառերը և ընդունում է փոքրատառ և մեծատառ նիշերը որպես նույնը: Հետևաբար, եթե աղյուսակում կան մի քանի տարրեր, որոնք տարբերվում են միայն պատյանով, ապա VLOOKUP ֆունկցիան կվերադարձնի հայտնաբերված առաջին տարրը՝ անկախ գործից։
Որոշում: Օգտագործեք Excel-ի մեկ այլ գործառույթ, որը կարող է կատարել ուղղահայաց որոնում (Փնտրել, SUMPRODUCT, INDEX և MATCH)՝ համակցված EXACT- ըA, որը տարբերակում է դեպքը. Լրացուցիչ մանրամասների համար կարող եք սովորել դասից – Excel-ում VLOOKUP-ը մեծատառերի զգայուն դարձնելու 4 եղանակ:
2. VLOOKUP-ը վերադարձնում է հայտնաբերված առաջին արժեքը
Ինչպես արդեն գիտեք, VPR- ը վերադարձնում է արժեքը տրված սյունակից, որը համապատասխանում է հայտնաբերված առաջին համընկնումին: Այնուամենայնիվ, դուք կարող եք այն հանել ձեր ուզած արժեքի 2-րդ, 3-րդ, 4-րդ կամ ցանկացած այլ կրկնություն: Եթե Ձեզ անհրաժեշտ է բոլոր կրկնօրինակ արժեքները հանել, ապա ձեզ անհրաժեշտ կլինի ֆունկցիաների համակցություն INDEX (INDEX), ԱՌԱՆՑ (ՓՈՔՐ) և LINE (ROW):
3. Սյունակ ավելացվեց կամ հեռացվեց աղյուսակում
Ցավոք, բանաձեւերը VPR- ը դադարեցնել աշխատանքը ամեն անգամ, երբ որոնման աղյուսակում նոր սյունակ ավելացվում կամ հեռացվում է: Դա տեղի է ունենում, քանի որ շարահյուսությունը VPR- ը պահանջում է նշել որոնման ողջ տիրույթը և տվյալների արդյունահանման հատուկ սյունակի համարը: Բնականաբար, և՛ տրված միջակայքը, և՛ սյունակի համարը փոխվում են, երբ ջնջում ես սյունակ կամ տեղադրում ես նորը։
Որոշում: Եվ նորից ֆունկցիաները շտապում են օգնել INDEX (INDEX) և ԱՎԵԼԻ ԲԱՑՎԱԾ (ՄԱՏՉ). Բանաձեւում INDEX+MATCH Դուք առանձին եք սահմանում որոնման և առբերման սյունակները, և արդյունքում կարող եք ջնջել կամ տեղադրել այնքան սյունակ, որքան ցանկանում եք՝ առանց անհանգստանալու, որ պետք է թարմացնեք բոլոր առնչվող որոնման բանաձևերը:
4. Բանաձևը պատճենելիս բջիջների հղումները խեղաթյուրվում են
Այս վերնագիրը սպառիչ կերպով բացատրում է խնդրի էությունը, այնպես չէ՞:
Որոշում: Միշտ օգտագործեք բացարձակ բջջային հղումներ (նշանով $) տիրույթի գրառումների վրա, օրինակ $ A $ 2: $ C $ 100 or $ A: $ C. Բանաձևի տողում կարող եք արագ փոխել հղման տեսակը՝ սեղմելով F4.
VLOOKUP – աշխատում է IFERROR և ISERROR ֆունկցիաների հետ
Եթե դուք չեք ցանկանում վախեցնել օգտվողներին սխալ հաղորդագրություններով # N / A, #VALUE! or #ԱՆՈՒՆ?, կարող եք ցույց տալ դատարկ բջիջ կամ ձեր սեփական հաղորդագրությունը: Դուք կարող եք դա անել տեղադրելով VPR- ը ֆունկցիայի մեջ ԻՐԱER (IFERROR) Excel 2013, 2010 և 2007 թվականներին կամ օգտագործեք մի շարք գործառույթներ ԵԹԵ + ISERROR (IF+ISERROR) ավելի վաղ տարբերակներում:
VLOOKUP. աշխատում է IFERROR ֆունկցիայի հետ
Ֆունկցիայի շարահյուսություն ԻՐԱER (IFERROR) պարզ է և ինքնին խոսում է.
IFERROR(value,value_if_error)
ЕСЛИОШИБКА(значение;значение_если_ошибка)
Այսինքն, առաջին արգումենտի համար դուք տեղադրում եք այն արժեքը, որը պետք է ստուգվի սխալի համար, իսկ երկրորդ արգումենտի համար նշում եք, թե ինչ պետք է վերադարձնել, եթե սխալ հայտնաբերվի:
Օրինակ, այս բանաձեւը վերադարձնում է դատարկ բջիջ, եթե ձեր փնտրած արժեքը չի գտնվել.
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"")
Եթե ցանկանում եք ցուցադրել ձեր սեփական հաղորդագրությունը ֆունկցիայի ստանդարտ սխալի հաղորդագրության փոխարեն VPR- ը, դրեք չակերտների մեջ, այսպես.
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"Ничего не найдено. Попробуйте еще раз!")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"Ничего не найдено. Попробуйте еще раз!")
VLOOKUP. աշխատում է ISERROR ֆունկցիայի հետ
Քանի որ գործառույթը ԻՐԱER հայտնվել է Excel 2007-ում, ավելի վաղ տարբերակներում աշխատելիս ստիպված կլինեք օգտագործել համակցությունը IF (ԵԹԵ) և ԷՈՇԻԲԿԱ (ISERROR) այսպիսին է.
=IF(ISERROR(VLOOKUP формула),"Ваше сообщение при ошибке",VLOOKUP формула)
=ЕСЛИ(ЕОШИБКА(ВПР формула);"Ваше сообщение при ошибке";ВПР формула)
Օրինակ, բանաձեւը IF + ISERROR + VLOOKUP, նման բանաձևին IFERROR+VLOOKUPցուցադրված է վերևում.
=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;ЛОЖЬ))
Այսօրվա համար այսքանը: Հուսով եմ, որ այս կարճ ձեռնարկը կօգնի ձեզ հաղթահարել բոլոր հնարավոր սխալները: VPR- ը և այնպես արեք, որ ձեր բանաձևերը ճիշտ աշխատեն: