کړی
دا کوچنی ټیوټوریل تشریح کوي چې څنګه یو فنکشن جوړ کړئ VPR (VLOOKUP) د قضیې حساس، ډیری نور فورمولونه ښیي چې اکسل کولی شي د قضیې په حساس ډول لټون وکړي، او د هر فعالیت ځواک او ضعف په ګوته کوي.
زه اټکل کوم چې هر ایکسل کارونکی پوهیږي چې کوم فعالیت عمودی لټون ترسره کوي. دا سمه ده، دا یو فعالیت دی VPR. په هرصورت، لږ خلک پدې پوهیږي VPR د قضیې حساس نه دی، د بیلګې په توګه د ټیټ او پورتنۍ قضیې حروف د هغې لپاره یو شان دي.
دلته یو چټک مثال دی چې د وړتیا ښودنه کوي VPR راجستر پیژندل. فرض کړئ په یوه حجره کې A1 ارزښت "بل" او حجره لري A2 - "بل"، فورمول:
=VLOOKUP("Bill",A1:A10,2)
=ВПР("Bill";A1:A10;2)
… به خپل لټون په "بل" کې ودروي ځکه چې دا ارزښت په لیست کې لومړی راځي، او د حجرې څخه ارزښت استخراج کوي B1.
وروسته پدې مقاله کې ، زه به تاسو ته وښیم چې څنګه یې وکړم VPR حساس مقدمه. سربیره پردې، موږ به یو څو نور فعالیتونه زده کړو چې کولی شي په Excel کې د قضیې حساس لټونونه ترسره کړي.
موږ به د ساده سره پیل وکړو - VIEW (LOOKUP) او ضمیمه (SUMPRODUCT)، کوم چې، له بده مرغه، ډیری مهم محدودیتونه لري. بیا، موږ به یو څه ډیر پیچلي فورمول ته نږدې کتنه وکړو INDEX+MATCH (INDEX+MATCH)، کوم چې په هر حالت کې او د هر ډیټاسیټ سره بې عیب کار کوي.
د VLOOKUP فعالیت د قضیې حساس دی
لکه څنګه چې تاسو دمخه پوهیږئ، معمول فعالیت VPR قضیه غیر حساس ده. په هرصورت، د قضیې حساس کولو لپاره یوه لاره شتون لري. د دې کولو لپاره، تاسو اړتیا لرئ چې په میز کې یو معاون کالم اضافه کړئ، لکه څنګه چې په لاندې مثال کې ښودل شوي.
فرض کړئ په یوه کالم کې B دلته د محصول پیژندونکي (توکي) شتون لري او تاسو غواړئ د محصول قیمت او اړونده تبصره له کالمونو څخه راوباسئ C и D. ستونزه دا ده چې پیژندونکي دواړه کوچني او لوی توري لري. د مثال په توګه، د حجرو ارزښتونه B4 (001Tvci3u) او B5 (001Tvci3U) یوازې د وروستي کرکټر په قضیه کې توپیر لري، u и U په ترتیب سره.
لکه څنګه چې تاسو تصور کولی شئ، د معمول لټون فورمول
=VLOOKUP("001Tvci3U",$A$2:$C$7,2,FALSE)
=ВПР("001Tvci3U";$A$2:$C$7;2;ЛОЖЬ)
بیرته به راشي $ 90له ارزښت څخه 001Tvci3u د لټون لړۍ کې مخکې له مخکې دی 001Tvci3U. مګر دا هغه څه ندي چې موږ ورته اړتیا لرو، ایا دا دی؟
د فنکشن سره لټون کول VPR په ایکسل قضیه کې حساس، تاسو باید یو مرستندویه کالم اضافه کړئ او د هغې حجرې د لاندې فورمول سره ډک کړئ (چیرې چې B د لټون کالم دی):
=CODE(MID(B2,1,1)) & CODE(MID(B2,2,1)) & CODE(MID(B2,3,1)) & CODE(MID(B2,4,1)) & CODE(MID(B2,5,1)) & CODE(MID(B2,6,1)) & CODE(MID(B2,7,1)) & CODE(MID(B2,8,1)) & IFERROR(CODE(MID(B2,9,1)),"")
=КОДСИМВ(ПСТР(B2;1;1)) & КОДСИМВ(ПСТР(B2;2;1)) & КОДСИМВ(ПСТР(B2;3;1)) & КОДСИМВ(ПСТР(B2;4;1)) & КОДСИМВ(ПСТР(B2;5;1)) & КОДСИМВ(ПСТР(B2;6;1)) & КОДСИМВ(ПСТР(B2;7;1)) & КОДСИМВ(ПСТР(B2;8;1)) & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;9;1));"")
دا فورمول مطلوب ارزښت په جلا حروفونو ماتوي، هر کرکټر د خپل کوډ سره بدلوي (د مثال په توګه، پرځای A په 65 کې، پرځای a کوډ 97) او بیا دا کوډونه د شمیرو ځانګړي تار سره یوځای کوي.
له هغې وروسته، موږ یو ساده فعالیت کاروو VPR د قضیې حساس لټون لپاره:
=VLOOKUP($G$3,$A$2:$C$8,3,FALSE)
=ВПР($G$3;$A$2:$C$8;3;ЛОЖЬ)
د فعالیت مناسب عملیات VPR د قضیې حساسیت په دوو فکتورونو پورې اړه لري:
- مرستندویه کالم باید د لیدلو وړ حد کې ترټولو کیڼ کالم وي.
- هغه ارزښت چې تاسو یې په لټه کې یاست باید د اصلي ارزښت پرځای د کرکټر کوډ ولري.
د کوډ فعالیت په سمه توګه کارولو څرنګوالی
د معاون کالم په حجرو کې داخل شوي فورمول داسې انګیرل کیږي چې ستاسو د لټون ټول ارزښتونه ورته حروفونه لري. که نه، نو تاسو اړتیا لرئ تر ټولو کوچنی او لوی شمیر وپیژنئ او ډیری ځانګړتیاوې اضافه کړئ IFERROR (IFERROR) څومره حروف د لنډ او اوږد لټون شوي ارزښت ترمنځ توپیر لري.
د مثال په توګه، که د لټون ترټولو لنډ ارزښت 3 حروف وي او تر ټولو اوږد 5 حروف وي، دا فورمول وکاروئ:
=CODE(MID(B2,1,1)) & CODE(MID(B2,2,1)) & CODE(MID(B2,3,1)) & IFERROR(CODE(MID(B2,3,1)),"") & IFERROR(CODE(MID(B2,4,1)),"")
=КОДСИМВ(ПСТР(B2;1;1)) & КОДСИМВ(ПСТР(B2;2;1)) & КОДСИМВ(ПСТР(B2;3;1)) & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;3;1));"") & ЕСЛИОШИБКА(КОДСИМВ(ПСТР(B2;4;1));"")
د فعالیت لپاره پي ار ټي (MID) تاسو لاندې دلیلونه وړاندې کوئ:
- لومړی دلیل – متن (متن) د متن یا حجرې حواله ده چې حروفونه لري چې استخراج کیږي (زموږ په قضیه کې دا B2 دی)
- لومړی دلیل – پیل_ شمیره (start_position) د هغه حروفونو څخه د لومړي ځای موقعیت دی چې استخراج کیږي. تاسو داخل کړئ 1 په لومړي فعالیت کې پي ار ټي, 2 - په دوهم فعالیت کې پي ار ټي او داسې نور
- لومړی دلیل – شمېر_کرونه (نمبر_of_characters) - د متن څخه د ایستلو لپاره د حروفونو شمیر مشخص کوي. ځکه چې موږ هر وخت یوازې 1 کرکټر ته اړتیا لرو، په ټولو دندو کې موږ لیکو 1.
محدودیتونه: دنده VPR په Excel کې د قضیې حساس لټونونو لپاره غوره حل ندی. لومړی، د مرستندویه کالم اضافه کول اړین دي. دوهم، فورمول یوازې هغه وخت ښه کار کوي چې معلومات همغږي وي، یا په لټون شوي ارزښتونو کې د حروف دقیق شمیر معلوم وي. که دا ستاسو قضیه نه وي ، نو غوره به وي چې یو له هغه حلونو څخه کار واخلئ چې موږ یې لاندې ښیې.
د قضیې حساس لټون لپاره د LOOKUP فعالیت
دنده VIEW (LOOKUP) اړوند VPRکه څه هم د هغې نحو د معاون کالم اضافه کولو پرته د قضیې حساس لټونونو ته اجازه ورکوي. د دې کولو لپاره، وکاروئ VIEW د فنکشن سره یوځای بالکل ( دقیقا ).
که موږ د مخکیني مثال څخه ډاټا واخلو (د مرستندویه کالم پرته)، نو لاندې فورمول به د دندې سره مقابله وکړي:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
فورمول په رینج کې لټون کوي A2:A7 د حجرو ارزښت سره دقیق مطابقت F2 قضیه حساس او د ورته قطار B کالم څخه ارزښت بیرته راګرځوي.
په شان VPRدنده VIEW د متن او عددي ارزښتونو سره مساوي کار کوي، لکه څنګه چې تاسو په لاندې سکرین شاټ کې لیدلی شئ:
مهم! د فعالیت لپاره VIEW په سمه توګه کار شوی، د لټون کولو کالم کې ارزښتونه باید په پورته ترتیب کې ترتیب شي، د بیلګې په توګه له کوچني څخه لوی ته.
اجازه راکړئ په لنډه توګه تشریح کړم چې فعالیت څنګه کار کوي بالکل په پورته ښودل شوي فورمول کې، لکه څنګه چې دا کلیدي ټکی دی.
دنده بالکل په لومړي او دوهم دلیل کې دوه متن ارزښتونه پرتله کوي او ریښتیا بیرته راګرځوي که دوی په ریښتیا ورته وي، یا غلط که دوی نه وي. دا زموږ لپاره مهم دی چې فعالیت بالکل حساس مقدمه.
راځئ وګورو چې زموږ فورمول څنګه کار کوي وګوره + دقیق:
=LOOKUP(TRUE,EXACT($A$2:$A$7,$F$2),$B$2:$B$7)
=ПРОСМОТР(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);$B$2:$B$7)
- دنده بالکل د حجرو ارزښت پرتله کوي F2 په یوه کالم کې د ټولو عناصرو سره A (A2:A7). د ریښتیا بیرته راګرځي که چیرې دقیق میچ وموندل شي، که نه نو غلط.
- ځکه چې تاسو لومړی فنکشن دلیل ورکړئ VIEW ارزښت ریښتیا، دا د ټاکل شوي کالم (زموږ په قضیه کې، کالم B) څخه ورته ارزښت استخراج کوي یوازې که دقیق میچ وموندل شي، قضیه حساس وي.
زه امید لرم چې دا توضیح روښانه و او اوس تاسو په اصلي مفکوره پوهیږئ. که داسې وي، نو تاسو به د نورو دندو سره کومه ستونزه ونه لرئ چې موږ به یې نور تحلیل کړو، ځکه. دوی ټول په ورته اصولو کار کوي.
محدودیتونه: د لټون کولو کالم کې ډاټا باید په پورته ترتیب کې ترتیب شي.
SUMPRODUCT - د متن ارزښتونه لټوي، قضیه حساس، مګر یوازې شمیرې راګرځوي
لکه څنګه چې تاسو دمخه د سرلیک څخه پوهیږئ، ضمیمه (SUMPRODUCT) یو بل Excel فنکشن دی چې تاسو سره به د قضیې حساس لټون کولو کې مرسته وکړي، مګر یوازې شمیرې ارزښتونه به بیرته راولي. که دا اختیار ستاسو سره مناسب نه وي، نو تاسو کولی شئ سمدستي بنډل ته لاړ شئ INDEX+MATCH، کوم چې د هرې قضیې او هر ډول ډیټا ډولونو لپاره حل وړاندې کوي.
لومړی، اجازه راکړئ چې د دې فنکشن ترکیب په لنډه توګه تشریح کړم، دا به تاسو سره د قضیې حساس فورمول په ښه پوهیدو کې مرسته وکړي چې لاندې راځي.
دنده ضمیمه د ورکړل شوي صفونو عناصر ضربوي او د پایلو مجموعه بیرته راګرځوي. ترکیب داسې ښکاري:
SUMPRODUCT(array1,[array2],[array3],...)
СУММПРОИЗВ(массив1;[массив2];[массив3];…)
څرنګه چې موږ د قضیې حساس لټون ته اړتیا لرو، موږ فنکشن کاروو بالکل (مقدار) د مخکیني مثال څخه د یو ضرب کونکي په توګه:
=SUMPRODUCT((EXACT($A$2:$A$7,$F$2)*($B$2:$B$7)))
=СУММПРОИЗВ((СОВПАД($A$2:$A$7;$F$2)*($B$2:$B$7)))
لکه څنګه چې تاسو په یاد ولرئ ، بالکل د حجرو ارزښت پرتله کوي F2 په یوه کالم کې د ټولو عناصرو سره A. د ریښتیا بیرته راګرځي که چیرې دقیق میچ وموندل شي، که نه نو غلط. په ریاضیاتي عملیاتو کې، Excel د TRUE په توګه اخلي 1، او FALSE لپاره 0نور ضمیمه دا شمیرې ضربوي او پایلې یې راټولوي.
صفر نه شمیرل کیږي ځکه چې کله ضرب کیږي دوی تل ورکوي 0. راځئ چې نږدې وګورو چې څه پیښیږي کله چې په یوه کالم کې دقیق میچ وي A وموندل شو او بیرته راستانه شو 1… فعالیت ضمیمه په کالم کې شمیره ضربوي B on 1 او پایله بیرته راګرځوي - بالکل ورته شمیره! دا ځکه چې د نورو محصولاتو پایلې صفر دي، او دوی د پایلې مجموعه اغیزه نه کوي.
له بده مرغه فعالیت ضمیمه د متن ارزښتونو او نیټو سره کار نشي کولی ځکه چې دوی نشي ضرب کیدی. پدې حالت کې ، تاسو به د خطا پیغام ترلاسه کړئ #VALUE! (#VALUE!) لکه په یوه حجره کې F4 په لاندې انځور کې:
محدودیتونه: یوازې عددي ارزښتونه راګرځوي.
INDEX + MATCH - د هر ډول ډیټا لپاره د قضیې حساس لټون
په نهایت کې ، موږ د لامحدود او قضیې حساس لټون فارمول ته نږدې یو چې د هرډول ډیټا سیټ سره کار کوي.
دا مثال وروستی راځي، نه د دې لپاره چې غوره د خوږ لپاره پاتې شي، مګر دا چې د تیرو مثالونو څخه ترلاسه شوي پوهه به تاسو سره د قضیې حساس فورمول په ښه او چټک پوهیدو کې مرسته وکړي. INDEX+MATCH (INDEX+MATCH).
لکه څنګه چې تاسو شاید اټکل کړی، د دندو ترکیب نور افشا شوي и انډکس په Excel کې د ډیر انعطاف وړ او ځواکمن بدیل په توګه کارول کیږي VPR. مقاله د VLOOKUP پرځای د INDEX او MATCH کارول به په بشپړ ډول تشریح کړي چې دا افعال څنګه یوځای کار کوي.
زه به یوازې کلیدي ټکي تکرار کړم:
- دنده نور افشا شوي (MATCH) په ورکړل شوي حد کې د ارزښت لټون کوي او خپل نسبي موقعیت بیرته راولي، دا د قطار او / یا کالم شمیره ده؛
- بل، فعالیت انډکس (INDEX) د ټاکل شوي کالم او/یا قطار څخه یو ارزښت راګرځوي.
فورمول ته INDEX+MATCH د قضیې په حساس ډول لټون کولی شي، تاسو اړتیا لرئ یوازې یو فنکشن اضافه کړئ. دا ستونزمنه نه ده چې اټکل وکړئ چې دا بیا څه دي بالکل ( دقیق ):
=INDEX($B$2:$B$7,MATCH(TRUE,EXACT($A$2:$A$7,$F$2),0))
=ИНДЕКС($B$2:$B$7;ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$F$2);0))
په دې فورمول کې بالکل په ورته ډول کار کوي لکه څنګه چې د فنکشن سره یوځای کیږي VIEW، او ورته پایله ورکوي:
په یاد ولرئ چې فورمول INDEX+MATCH په منحل منحلاتو کې تړل د صف فارمول دی او تاسو باید دا په فشارولو سره بشپړ کړئ Ctrl + Shift + داخل کړئ.
ولې INDEX+MATCH د قضیې حساس لټون لپاره غوره حل دی؟
د بنډل اصلي ګټې انډکس и نور افشا شوي:
- د معاون کالم اضافه کولو ته اړتیا نلري، برعکس VPR.
- د لټون کالم ترتیب کولو ته اړتیا نلري، برعکس VIEW.
- د ټولو ډولونو ډیټا سره کار کوي - شمیرې ، متن او نیټې.
دا فورمول کامل ښکاري، نه دا؟ په حقیقت کې، دا نه ده. او له همدې امله.
فرض کړئ چې د بیرته ستنیدو ارزښت کالم کې حجره د لټون ارزښت سره تړلې خالي ده. کومه پایله به فورمول بیرته راشي؟ نه؟ راځئ وګورو چې فورمول واقعیا بیرته راستنیږي:
اوه، فورمول صفر راګرځوي! دا ممکن لویه ستونزه نه وي که تاسو د خالص متن ارزښتونو سره کار کوئ. په هرصورت، که چیرې جدول د "حقیقي" صفرونو په شمول شمیرې ولري، دا یوه ستونزه کیږي.
په حقیقت کې، د لټون نور ټول فورمولونه (VLOOKUP، LOOKUP، او SUMPRODUCT) چې موږ مخکې بحث کړی په ورته ډول چلند کوي. مګر تاسو بشپړ فارمول غواړئ، سمه ده؟
د فورمول قضیه حساس کولو لپاره INDEX+MATCH کامل، دا په فنکشن کې واچوئ IF IF
=IF(INDIRECT("B"&(1+MATCH(TRUE,EXACT($A$2:$A$7,$G$2),0)))<>"",INDEX($B$2:$B$7, MATCH(TRUE,EXACT($A$2:$A$7,$G$2),0)),"")
=ЕСЛИ(ДВССЫЛ("B"&(1+ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$G$2);0)))<>"";ИНДЕКС($B$2:$B$7; ПОИСКПОЗ(ИСТИНА;СОВПАД($A$2:$A$7;$G$2);0));"")
په دې فورمول کې:
- B د بیرته ستنیدو ارزښتونو سره یو کالم دی
- 1+ هغه شمیره ده چې د فنکشن لخوا بیرته راستانه شوي حجرې نسبي موقعیت بدلوي نور افشا شويد حجرې اصلي پته ته. د مثال په توګه، زموږ په فعالیت کې نور افشا شوي د لټون سرې ورکړل شوې A2:A7، دا د حجرې نسبي موقعیت دی A2 به 1ځکه چې دا په صف کې لومړی دی. مګر د حجرې اصلي موقعیت A2 په کالم کې دی 2، نو موږ اضافه کوو 1د توپیر رامینځته کولو او فعالیت کولو لپاره پیژندنه (غیر مستقیم) د مطلوب سیل څخه ارزښت ترلاسه کړ.
لاندې عکسونه سم شوي د قضیې حساس فورمول ښیې INDEX+MATCH په عمل کې. دا یو خالي پایله بیرته راګرځوي که چیرې بیرته راګرځیدلی حجره خالي وي.
ما فورمول په کالمونو کې بیا ولیکل ب: دپه سکرین شاټ کې د فارمول بار فټ کولو لپاره.
فورمول بیرته راګرځي 0که بیرته راګرځیدلی حجره صفر ولري.
که تاسو لینک غواړئ انډکس и نور افشا شوي ځینې پیغام ښکاره کوي کله چې د بیرته ستنیدو ارزښت خالي وي، تاسو کولی شئ دا د فورمول په وروستیو نرخونو ("") کې ولیکئ، د بیلګې په توګه، دا ډول:
=IF(INDIRECT("D"&(1+MATCH(TRUE,EXACT($B$2:$B$7,$G$2),0)))<>"",INDEX($D$2:$D$7, MATCH(TRUE,EXACT($B$2:$B$7,$G$2),0)),"There is nothing to return, sorry.")
=ЕСЛИ(ДВССЫЛ("D"&(1+ПОИСКПОЗ(ИСТИНА;СОВПАД($B$2:$B$7;$G$2);0)))<>"";ИНДЕКС($D$2:$D$7; ПОИСКПОЗ(ИСТИНА;СОВПАД($B$2:$B$7;$G$2);0));"There is nothing to return, sorry.")
