کړی
دا درس تشریح کوي چې څنګه په چټکۍ سره د داسې وضعیت سره معامله وکړئ چیرې چې یو فعالیت VPR (VLOOKUP) نه غواړي په Excel 2013، 2010، 2007 او 2003 کې کار وکړي، او د عام غلطیو پیژندلو او حل کولو څرنګوالی او محدودیتونه لرې کول VPR.
په څو تیرو مقالو کې، موږ د فعالیت مختلف اړخونه سپړلي دي VPR په Excel کې. که تاسو دوی په دقت سره لوستلي وي ، نو تاسو باید اوس پدې برخه کې ماهر اوسئ. په هرصورت، دا پرته له کوم دلیل نه دی چې د Excel ډیری ماهرین باور لري VPR یو له ډیرو پیچلو ځانګړتیاو څخه. دا یو شمیر محدودیتونه او ځانګړتیاوې لري چې د ډیری ستونزو او غلطیو سرچینه ګرځي.
پدې مقاله کې به تاسو د غلطیو ساده توضیحات ومومئ #AT (#N/A) #NAME؟ (#NAME؟) او #VALUE! (#VALUE!) چې د فنکشن سره کار کولو پرمهال څرګندیږي VPR، همدارنګه د هغوی سره د معاملې تخنیکونه او میتودونه. موږ به د ډیری عامو قضیو او خورا څرګند دلیلونو سره پیل وکړو. VPR کار نه کوي، نو دا به غوره وي چې مثالونه په هغه ترتیب کې مطالعه کړئ چې په مقاله کې ورکړل شوي.
په Excel کې د VLOOKUP فنکشن کې د #N/A تېروتنې حل کول
سره په فورمولونو کې VPR تېروتنه #AT (#N/A) معنی نشته (نه معلومات) - هغه وخت څرګندیږي کله چې ایکسل هغه ارزښت ونه موند چې تاسو یې په لټه کې یاست. دا د څو دلیلونو لپاره پیښ کیدی شي.
1. مطلوب ارزښت غلط لیکل شوی
ښه نظر دا دی چې لومړی دا توکي وګورئ! ټایپونه اکثرا هغه وخت رامینځته کیږي کله چې تاسو د ډیری لوی مقدار ډیټا سره کار کوئ ، په زرهاو لینونو مشتمل وي ، یا کله چې هغه ارزښت چې تاسو یې په لټه کې یاست په فورمول کې لیکل شوي وي.
2. #N/A تېروتنه کله چې د VLOOKUP سره د نږدې میچ لټون کول
که تاسو د نږدې میچ لټون حالت سره فارمول وکاروئ، د بیلګې په توګه دلیل range_lookup (range_lookup) سم دی یا مشخص شوی نه دی، ستاسو فورمول ممکن د یوې تېروتنې راپور ورکړي # N / A په دوو حالتونو کې:
- د کتلو ارزښت له هغه کوچني ارزښت څخه کم دی چې په صف کې لیدل کیږي.
- د لټون کالم په پورته ترتیب نه ترتیب شوی.
3. #N/A تېروتنه کله چې د VLOOKUP سره دقیق میچ په لټه کې وي
که تاسو د دقیق میچ په لټه کې یاست، یعنی دلیل range_lookup (range_lookup) غلط دی او کره ارزښت ونه موندل شو، فورمول به هم د تېروتنې راپور ورکړي # N / A. د فنکشن سره دقیق او نږدې میچونو لټون کولو څرنګوالي په اړه نور معلومات زده کړئ VPR.
4. د لټون کالم تر ټولو کیڼ نه دی
لکه څنګه چې تاسو شاید پوهیږئ، یو له خورا مهم محدودیتونو څخه VPR دا دی چې کیڼ اړخ ته مخامخ نشي، نو ستاسو په میز کې د لټون کالم باید تر ټولو کیڼ وي. په عمل کې، موږ ډیری وختونه د دې په اړه هیر کوو، کوم چې د غیر کار کولو فورمول او غلطی لامل کیږي. # N / A.
پریکړه: که دا ممکنه نه وي چې د معلوماتو جوړښت بدل کړئ ترڅو د لټون کالم تر ټولو پاتې وي، تاسو کولی شئ د دندو ترکیب وکاروئ انډکس (INDEX) او نور افشا شوي (MATCH) لپاره د ډیر انعطاف وړ بدیل په توګه VPR.
5. شمیرې د متن په توګه بڼه شوي
د غلطۍ بله سرچینه # N / A سره په فورمولونو کې VPR په اصلي جدول یا د لټون جدول کې د متن په شکل کې شمیرې دي.
دا معمولا هغه وخت پیښیږي کله چې تاسو د بهرني ډیټابیس څخه معلومات وارد کوئ، یا کله چې تاسو د صفر مخکښ ساتلو لپاره د شمیرې دمخه اپوسټروف ټایپ کړئ.
د متن په شکل کې د شمیرې خورا څرګندې نښې په لاندې شکل کې ښودل شوي:
سربیره پردې، شمیرې په بڼه کې زیرمه کیدی شي جنرال (جنرال). په دې حالت کې، یوازې یو د پام وړ ځانګړتیا شتون لري - شمیرې د حجرې کیڼ اړخ سره سمون لري، پداسې حال کې چې د ډیفالټ په واسطه دوی ښي څنډه سره سمون لري.
پریکړه: که دا یو واحد ارزښت وي، یوازې د تېروتنې آیکون باندې کلیک وکړئ او غوره کړئ شمیر ته بدل کړئ (نمبر ته بدل کړئ) د شرایطو مینو څخه.
که دا د ډیری شمیرو سره وضعیت وي، دوی غوره کړئ او په ټاکل شوي ساحه کې ښیې کلیک وکړئ. د شرایطو مینو کې چې څرګندیږي ، غوره کړئ د حجرو ب Forه (د حجرو بڼه) > ټب شمیره (شمیره) > بڼه شمیره (شمیری) او پریس OK.
6. په پیل یا پای کې یو ځای شتون لري
دا د خطا لپاره لږ تر لږه څرګند دلیل دی. # N / A په فعالیت کې VPR، ځکه چې د دې اضافي ځایونو لیدل د لید له پلوه ستونزمن دي ، په ځانګړي توګه کله چې د لوی میزونو سره کار کول ، کله چې ډیری ډیټا د سکرین څخه بهر وي.
1 حل: په اصلي جدول کې اضافي ځایونه (چیرې چې د VLOOKUP فعالیت دی)
که اضافي ځایونه په اصلي جدول کې ښکاره شي، تاسو کولی شئ ډاډ ترلاسه کړئ چې فورمول د دلیل په تړلو سره سم کار کوي کتنه _ ارزښت (lookup_value) په فنکشن کې ټریم (TRIM):
=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)
=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)
2 حل: د لټون جدول کې اضافي ځایونه (د لټون په کالم کې)
که اضافي ځایونه د لټون کالم کې وي - ساده لارې # N / A سره په فورمول کې VPR مخنیوی نشي کیدی. پرځای د VPR تاسو کولی شئ د دندو ترکیب سره د صف فارمول وکاروئ انډکس (INDEX) نور افشا شوي (میچ) и ټریم (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 سره
په ډیری مواردو کې، مایکروسافټ ایکسل د یوې تېروتنې راپور ورکوي #VALUE! (#VALUE!) کله چې په فورمول کې کارول شوي ارزښت د ډیټا ډول سره سمون نه خوري. په اړه VPR، بیا معمولا د خطا دوه لاملونه شتون لري #VALUE!.
1. هغه ارزښت چې تاسو یې په لټه کې یاست د 255 حروف څخه اوږد دی
محتاط اوسئ: فعالیت VPR نشي کولی د ارزښتونو لټون وکړي چې له 255 څخه ډیر حروف لري. که هغه ارزښت چې تاسو یې په لټه کې یاست له دې حد څخه تیریږي، تاسو به د خطا پیغام ترلاسه کړئ. #VALUE!.
پریکړه: د ځانګړتیاوو یوه ډله وکاروئ INDEX+MATCH (INDEX + MATCH). لاندې یو فورمول دی چې د دې کار لپاره به ښه کار وکړي:
=INDEX(C2:C7,MATCH(TRUE,INDEX(B2:B7=F$2,0),0))
=ИНДЕКС(C2:C7;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B2:B7=F$2;0);0))
2. د لټون کاري کتاب ته بشپړه لار نه ده مشخصه شوې
که تاسو د بل کاري کتاب څخه ډاټا ترلاسه کوئ، تاسو باید دې فایل ته بشپړه لاره مشخص کړئ. په ځانګړې توګه، تاسو باید د کاري کتاب نوم (د تمدید په شمول) په مربع قوسونو کې شامل کړئ [ ]، د شیټ نوم تعقیب کړئ، وروسته د عجب ټکي سره. دا ټول ساختمان باید په apostrophes کې وتړل شي، په هغه صورت کې چې د کتاب یا پاڼې نوم ځایونه ولري.
دلته د فنکشن بشپړ جوړښت دی 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 په پاڼه کې پاetه 1 په کاري کتاب کې نوي نرخونه او د کالم څخه ورته ارزښت راوباسئ D.
که چیرې د میز د لارې کومه برخه پریښودل شي، ستاسو فعالیت VPR کار نه کوي او د یوې تېروتنې راپور ورکوي #VALUE! (حتی که د لټون میز سره کاري کتاب اوس مهال خلاص وي).
د فعالیت په اړه د نورو معلوماتو لپاره VPRد بل Excel فایل حواله کول، درس وګورئ: د VLOOKUP په کارولو سره د بل کاري کتاب لټون.
3. د استدلال کالم نمبر له 1 څخه کم دی
دا سخته ده چې د داسې حالت تصور وکړئ چیرې چې یو څوک د ارزښت څخه لږ ارزښت ته ننوځي 1د هغه کالم په ګوته کول چې له هغې څخه ارزښت استخراج کړئ. که څه هم دا ممکنه ده که چیرې د دې دلیل ارزښت دننه د بل Excel فنکشن لخوا محاسبه شي VPR.
نو، که دا پیښ شي چې دلیل col_index_num (column_number) څخه لږ 1دنده VPR هم به د تېروتنې راپور ورکړي #VALUE!.
که دلیل col_index_num (column_number) په ورکړل شوي صف کې د کالمونو له شمیر څخه لوی دی، VPR د تېروتنې راپور ورکوي #REF! (#SSYL!).
تېروتنه #NAME؟ په VLOOKUP کې
تر ټولو ساده قضیه یوه تېروتنه ده #NAME؟ (#NAME؟) - به راښکاره شي که تاسو په ناڅاپي ډول د یوې خطا سره د فعالیت نوم ولیکئ.
حل څرګند دی - خپل املا چیک کړئ!
VLOOKUP کار نه کوي (محدودیتونه، احتیاطونه او پریکړې)
د خورا پیچلي ترکیب سربیره، VPR د هر بل Excel فعالیت په پرتله ډیر محدودیتونه لري. د دې محدودیتونو له امله، داسې ښکاري چې ساده فورمول سره VPR ډیری وختونه د غیر متوقع پایلو لامل کیږي. لاندې به تاسو د ډیری عام سناریوګانو لپاره حلونه ومومئ چیرې چې VPR غلط دی.
1. VLOOKUP قضیه حساس نه ده
دنده VPR د قضیې تر مینځ توپیر نه کوي او د ورته په توګه کوچني او لوی حروف مني. له همدې امله، که چیرې په جدول کې ډیری عناصر شتون ولري چې یوازې په قضیه کې توپیر ولري، د VLOOKUP فعالیت به د قضیې په پام کې نیولو پرته لومړی عنصر بیرته راولي.
پریکړه: د ایکسل بل فنکشن وکاروئ چې کولی شي د عمودی لټون (LOOKUP، SUMPRODUCT، INDEX، او MATCH) سره په ترکیب کې ترسره کړي. بالکلA چې قضیه توپیر کوي. د نورو جزیاتو لپاره، تاسو کولی شئ له درس څخه زده کړئ - په Excel کې د VLOOKUP قضیې حساس کولو لپاره 4 لارې.
2. VLOOKUP لومړی موندل شوی ارزښت بیرته راګرځوي
لکه څنګه چې تاسو دمخه پوهیږئ ، VPR د ورکړل شوي کالم څخه ارزښت بیرته راګرځي چې د موندل شوي لومړۍ میچ سره مطابقت لري. په هرصورت، تاسو کولی شئ دا د 2nd، 3rd، 4th، یا د هغه ارزښت کوم بل تکرار استخراج کړئ چې تاسو یې غواړئ. که تاسو اړتیا لرئ چې ټول نقل شوي ارزښتونه استخراج کړئ، تاسو به د دندو ترکیب ته اړتیا ولرئ انډکس (INDEX) لږ وروسته (کوچنی) او لین (ROW).
3. په میز کې یو کالم اضافه یا لیرې شوی
له بده مرغه، فورمولونه VPR هرکله چې د لټون میز ته نوی کالم اضافه شي یا لرې شي کار بند کړئ. دا پیښیږي ځکه چې ترکیب VPR تاسو ته اړتیا لري چې د لټون بشپړ لړۍ او د ډیټا استخراج لپاره د ځانګړي کالم شمیره مشخص کړئ. په طبیعي توګه، دواړه ورکړل شوي حد او د کالم شمیره بدلیږي کله چې تاسو یو کالم حذف کړئ یا یو نوی داخل کړئ.
پریکړه: او بیا افعال د مرستې لپاره په چټکۍ سره دي انډکس (INDEX) او نور افشا شوي (میچ). په فورمول کې INDEX+MATCH تاسو په جلا توګه د لټون او بیا ترلاسه کولو کالمونه تعریف کړئ، او د پایلې په توګه، تاسو کولی شئ د ټولو اړونده لټون فارمولونو تازه کولو په اړه اندیښنه پرته څومره چې تاسو غواړئ ډیری کالمونه حذف یا داخل کړئ.
4. د حجرو حوالې د فورمول کاپي کولو په وخت کې خرابیږي
دا سرلیک د ستونزې جوهر په بشپړ ډول تشریح کوي، سمه ده؟
پریکړه: تل د مطلق سیل حوالې وکاروئ (د سمبول سره $) د رینج ثبتولو کې، د بیلګې په توګه $A$2:$C$100 or $A:$C. په فورمول بار کې، تاسو کولی شئ په چټکۍ سره د لینک ډول په کلیک کولو سره بدل کړئ F4.
VLOOKUP - د IFERROR او ISERROR دندو سره کار کول
که تاسو نه غواړئ کاروونکي د خطا پیغامونو سره ویره کړئ # N / A, #VALUE! or #NAME؟تاسو کولی شئ یو خالي حجره یا خپل پیغام وښایئ. تاسو کولی شئ دا د ځای په ځای کولو سره ترسره کړئ VPR په فنکشن کې IFERROR (IFERROR) په Excel 2013، 2010 او 2007 کې یا د دندو یوه ډله وکاروئ IF+ISERROR (IF+ISERROR) په پخوانیو نسخو کې.
VLOOKUP: د IFERROR فنکشن سره کار کول
د فنکشن ترکیب IFERROR (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 فنکشن سره کار کول
د فعالیت راهیسې IFERROR په Excel 2007 کې راڅرګند شو، کله چې په پخوانیو نسخو کې کار کول تاسو باید ترکیب وکاروئ IF (IF) او EOSHIBKA (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 او خپل فورمولونه په سمه توګه کار کوي.