مواد
فنڪشن تي اسان جي سبق جي ٻئي حصي ۾ VPR (VLOOKUP) Excel ۾، اسان ڪجھ مثالن جو تجزيو ڪنداسين جيڪي توھان جي مدد ڪنديون توھان جي سموري طاقت کي سڌو ڪرڻ ۾ VPR سڀ کان وڌيڪ امڪاني Excel ڪمن کي حل ڪرڻ لاء. مثال فرض ڪن ٿا ته توهان وٽ اڳ ۾ ئي بنيادي ڄاڻ آهي ته هي خاصيت ڪيئن ڪم ڪري ٿي. جيڪڏهن نه، ته توهان هن سبق جي پهرين حصي سان شروع ڪرڻ ۾ دلچسپي رکو ٿا، جيڪو نحو ۽ بنيادي استعمال جي وضاحت ڪري ٿو. VPR. خير، اچو ته شروع ڪريون.
ڪيترن ئي معيارن سان Excel ۾ ڳولا ڪريو
فعل VPR ايڪسل ۾ هڪ واقعي طاقتور اوزار آهي هڪ ڊيٽابيس ۾ هڪ خاص قدر جي ڳولا کي انجام ڏيڻ لاءِ. بهرحال، اتي هڪ اهم حد آهي - ان جي نحو توهان کي صرف هڪ قدر ڳولڻ جي اجازت ڏئي ٿي. ڇا جيڪڏھن توھان چاھيو ٿا ڳولھيو گھڻن شرطن سان؟ توھان ھيٺ ڏنل حل ڳوليندا.
مثال 1: 2 مختلف معيارن سان ڳولھيو
فرض ڪريو اسان وٽ آرڊر جي هڪ فهرست آهي ۽ اسان ڳولڻ چاهيون ٿا سامان جو مقدار (قدر)، ٻن معيارن جي بنياد تي - ڪسٽمر جو نالو (ڪسٽمر) ۽ پيداوار جو نالو (پيداوار). معاملو پيچيده آهي حقيقت اها آهي ته هر خريد ڪندڙ ڪيترن ئي قسمن جي سامان جو حڪم ڏنو، جيئن هيٺ ڏنل جدول مان ڏسي سگهجي ٿو:
باقاعده فنڪشن VPR هن منظرنامي ۾ ڪم نه ڪندو ڇو ته اها پهرين قيمت واپس ڪندي جيڪا ان کي ملي ٿي جيڪا ڏنل نظر جي قيمت سان ملي ٿي. مثال طور، جيڪڏهن توهان هڪ شيء جي مقدار کي ڄاڻڻ چاهيو ٿا مٺايونخريد ڪندڙ طرفان حڪم ڏنو ويو آهي جريمي هيلهيٺ ڏنل فارمولا لکو:
=VLOOKUP(B1,$A$5:$C$14,3,FALSE)
=ВПР(B1;$A$5:$C$14;3;ЛОЖЬ)
- هي فارمولا نتيجو واپس ڪندو 15پيداوار سان لاڳاپيل مثال, ڇاڪاڻ ته اهو پهريون قدر آهي جيڪو ملندو آهي.
اتي ھڪڙو سادو ڪم آھي - ھڪڙو اضافي ڪالم ٺاھيو جنھن ۾ سڀني گهربل معيار کي گڏ ڪرڻ لاء. اسان جي مثال ۾، اهي ڪالمن آهن ڪسٽمر جو نالو (ڪسٽمر) ۽ پيداوار جو نالو (پيداوار). اهو نه وساريو ته ضم ٿيل ڪالم هميشه ڳولا جي حد ۾ سڀ کان کاٻي ڪالم هجڻ گهرجي، ڇاڪاڻ ته اهو کاٻي ڪالم آهي جيڪو فنڪشن VPR نظر اچي ٿو جڏهن قيمت ڳولي رهيو آهي.
تنهن ڪري، توهان ٽيبل تي هڪ معاون ڪالم شامل ڪريو ۽ هيٺ ڏنل فارمولا کي نقل ڪريو ان جي سڀني سيلن تي: =B2&C2. جيڪڏھن توھان چاھيو ٿا ته اسٽرنگ وڌيڪ پڙھي سگھجي ٿو، توھان ھڪڙي جڳھ سان گڏيل قدرن کي الڳ ڪري سگھو ٿا: =B2&»«&C2. ان کان پوء، توهان هيٺ ڏنل فارمولا استعمال ڪري سگهو ٿا:
=VLOOKUP("Jeremy Hill Sweets",$A$7:$D$18,4,FALSE)
=ВПР("Jeremy Hill Sweets";$A$7:$D$18;4;ЛОЖЬ)
or
=VLOOKUP(B1,$A$7:$D$18,4,FALSE)
=ВПР(B1;$A$7:$D$18;4;ЛОЖЬ)
سيل ڪٿي آهي B1 دليل جي گڏيل قدر تي مشتمل آهي ڳوله_ قدر (lookup_value) ۽ 4 - دليل col_index_num (column_number)، يعني ڪالمن جو تعداد جنهن ۾ ڊيٽا حاصل ڪئي وڃي.
مثال 2: VLOOKUP ٻن معيارن سان ٽيبل سان گڏ ٻي شيٽ تي ڏٺو پيو وڃي
جيڪڏهن توهان کي ٻئي ٽيبل (ڏسڻ واري ٽيبل) مان ڊيٽا شامل ڪندي مکيه جدول (مين ٽيبل) کي اپڊيٽ ڪرڻ جي ضرورت آهي، جيڪا ڪنهن ٻئي شيٽ تي يا ڪنهن ٻئي Excel ورڪ بڪ ۾ موجود آهي، ته پوءِ توهان گهربل قدر گڏ ڪري سگهو ٿا سڌو فارمولا ۾ جيڪو توهان داخل ڪيو. مکيه ٽيبل ۾.
جيئن پوئين مثال ۾، توهان کي ضرورت پوندي هڪ معاون ڪالمن جي گڏيل قدرن سان گڏ ڏسندڙ جدول ۾. ھي ڪالم ضرور ھوندو سڀ کان کاٻي ڪالم ۾ ڳولا جي حد ۾.
تنهنڪري فارمولا سان VPR هن طرح ٿي سگهي ٿو:
=VLOOKUP(B2&" "&C2,Orders!$A&$2:$D$2,4,FALSE)
=ВПР(B2&" "&C2;Orders!$A&$2:$D$2;4;ЛОЖЬ)
هتي، ڪالمن B ۽ C ۾ ڪسٽمر جا نالا ۽ پراڊڪٽ جا نالا، ترتيب سان، ۽ لنڪ شامل آهن آرڊر!$A&$2:$D$2 ٻئي شيٽ ۾ ڏسڻ لاءِ ٽيبل جي وضاحت ڪري ٿو.
فارمولا کي وڌيڪ پڙهڻ لائق بنائڻ لاءِ، توهان ڏيک جي حد کي هڪ نالو ڏئي سگهو ٿا، ۽ پوءِ فارمولا وڌيڪ سادو نظر ايندو:
=VLOOKUP(B2&" "&C2,Orders,4,FALSE)
=ВПР(B2&" "&C2;Orders;4;ЛОЖЬ)
فارمولا ڪم ڪرڻ لاءِ، جدول جي کاٻي پاسي واري ڪالم ۾ جيڪي قدر توھان ڏسي رھيا آھيو، انھن کي لازمي طور تي ساڳيءَ طرح گڏ ڪيو وڃي جيئن ڳولا جي معيار ۾. مٿي ڏنل شڪل ۾، اسان قدرن کي گڏ ڪيو u2bu2band انهن جي وچ ۾ هڪ جاء رکي، ساڳئي طرح توهان کي فنڪشن جي پهرين دليل ۾ ڪرڻ جي ضرورت آهي (BXNUMX& “” & CXNUMX).
ياد رکو فعل VPR 255 اکرن تائين محدود، اهو 255 اکرن کان وڌيڪ ڊگھي قيمت جي ڳولا نٿو ڪري سگهي. هن کي ذهن ۾ رکو ۽ پڪ ڪريو ته گهربل قدر جي ڊيگهه هن حد کان وڌيڪ نه آهي.
مان متفق آهيان ته هڪ معاون ڪالم شامل ڪرڻ تمام خوبصورت ۽ هميشه قابل قبول حل ناهي. توھان ھي ڪم ڪري سگھوٿا بغير مددگار ڪالمن جي، پر اھو ڪمن جي ميلاپ سان وڌيڪ پيچيده فارمولا جي ضرورت پوندي INDEX (INDEX) ۽ MATCH (وڌيڪ بي نقاب).
اسان VLOOKUP استعمال ڪندي 2nd، 3rd، وغيره قدر ڪڍيون ٿا
توھان اڳ ۾ ئي اھو knowاڻو ٿا VPR صرف ھڪڙي ملندڙ قيمت واپس ڪري سگھي ٿو، وڌيڪ واضح طور تي، پھريون ھڪڙو مليو. پر ڇا ٿيندو جيڪڏهن هي قدر ڏٺو ويو صف ۾ ڪيترائي ڀيرا بار بار ڪيو وڃي، ۽ توهان انهن مان 2nd يا 3rd ڪڍڻ چاهيو ٿا؟ ڇا جيڪڏھن سڀ قدر؟ مسئلو پيچيده لڳي ٿو، پر حل موجود آهي!
فرض ڪريو ٽيبل جي ھڪڙي ڪالمن ۾ گراهڪن جا نالا شامل آھن (ڪسٽمر جو نالو)، ۽ ٻئي ڪالمن ۾ شامل آھن پراڊڪٽ (پراڊڪٽ) جيڪي انھن خريد ڪيا آھن. اچو ته 2nd، 3rd ۽ 4th شيون ڳولڻ جي ڪوشش ڪريون جيڪي ڏنل صارف طرفان خريد ڪيون ويون آهن.
سڀ کان آسان طريقو ڪالمن کان اڳ هڪ معاون ڪالم شامل ڪرڻ آهي گراهڪ جو نالو ۽ ان کي ڀريو گراهڪ جي نالن سان هر نالي جي ورجائي نمبر سان، مثال طور، جان دو 1, جان دو 2 وغيره. اسان فنڪشن کي استعمال ڪندي نمبرنگ جي چال ڪنداسين COUNTIF (COUNTIF)، ڏنو ويو ته گراهڪ جا نالا ڪالمن B ۾ آهن:
=B2&COUNTIF($B$2:B2,B2)
=B2&СЧЁТЕСЛИ($B$2:B2;B2)
ان کان پوء توهان عام فنڪشن استعمال ڪري سگهو ٿا VPRگهربل آرڊر ڳولڻ لاء. مثال طور:
- مان ڳولا ڪريو 2-ٿ ڪسٽمر طرفان ترتيب ڏنل شيون دان لغاري:
=VLOOKUP("Dan Brown2",$A$2:$C$16,3,FALSE)=ВПР("Dan Brown2";$A$2:$C$16;3;ЛОЖЬ) - مان ڳولا ڪريو 3-ٿ ڪسٽمر طرفان ترتيب ڏنل شيون دان لغاري:
=VLOOKUP("Dan Brown3",$A$2:$C$16,3,FALSE)=ВПР("Dan Brown3";$A$2:$C$16;3;ЛОЖЬ)
حقيقت ۾، توهان ٽيڪسٽ جي بدران هڪ سيل ريفرنس داخل ڪري سگهو ٿا لوڪ اپ قدر، جيئن هيٺ ڏنل شڪل ۾ ڏيکاريل آهي:
جيڪڏهن توهان صرف ڳولي رهيا آهيو 2-e ورجائي، توهان اهو ڪري سگهو ٿا بغير معاون ڪالمن کان وڌيڪ پيچيده فارمولا ٺاهي:
=IFERROR(VLOOKUP($F$2,INDIRECT("$B$"&(MATCH($F$2,Table4[Customer Name],0)+2)&":$C16"),2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;ДВССЫЛ("$B$"&(ПОИСКПОЗ($F$2;Table4[Customer Name];0)+2)&":$C16");2;ИСТИНА);"")
هن فارمولا ۾:
- $F$2 - هڪ سيل جنهن ۾ خريد ڪندڙ جو نالو هجي (اها ڪا تبديلي ناهي، مهرباني ڪري نوٽ ڪريو - لنڪ مطلق آهي)؛
- $ب $ - ڪالم گراهڪ جو نالو;
- جدول4 - توهان جي ٽيبل (هي جڳهه پڻ ٿي سگهي ٿو باقاعده رينج)؛
- $16 - توهان جي ٽيبل يا رينج جو آخري سيل.
هي فارمولا صرف ٻيو ملندڙ قدر ڳولي ٿو. جيڪڏهن توهان کي باقي ورهاست ڪڍڻ جي ضرورت آهي، اڳوڻو حل استعمال ڪريو.
جيڪڏھن توھان کي ضرورت آھي سڀني ميچن جي ھڪڙي فهرست - فنڪشن VPR هي مددگار نه آهي، ڇاڪاڻ ته اهو صرف هڪ وقت ۾ هڪ قدر واپس ڪري ٿو - مدت. پر Excel ۾ هڪ فنڪشن آهي INDEX (INDEX)، جيڪو آساني سان هن ڪم کي منهن ڏئي سگهي ٿو. اهڙو فارمولو ڪيئن نظر ايندو، توهان هيٺ ڏنل مثال ۾ سکندا.
مطلوب قدر جي سڀني ورجائي ٻيهر حاصل ڪريو
جيئن مٿي ڄاڻايل آهي VPR اسڪين ٿيل رينج مان سڀ نقلي قدر ڪڍي نه سگھندا. هن کي ڪرڻ لاء، توهان کي ٿورڙي وڌيڪ پيچيده فارمولا جي ضرورت آهي، ڪيترن ئي Excel افعال مان ٺهيل آهي، جهڙوڪ INDEX (INDEX) SMALL (ننڍو) ۽ قطار (لائن)
مثال طور، هيٺ ڏنل فارمولا سيل F2 مان قيمت جي سڀني ورجائي کي رينج B2:B16 ۾ ڳولي ٿو ۽ نتيجو واپس ڪري ٿو ساڳين قطارن مان ڪالمن C ۾.
{=IFERROR(INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3)),"")}
{=ЕСЛИОШИБКА(ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3));"")}
گھڻن ويجھي سيلن ۾ ھن صف جو فارمولا داخل ڪريو، جھڙوڪ سيلز F4: F8جيئن هيٺ ڏنل شڪل ۾ ڏيکاريل آهي. سيلن جو تعداد ڳولھيل قدر جي ورجائي جي وڌ ۾ وڌ ممڪن تعداد جي برابر يا ان کان وڌيڪ هجڻ گھرجي. ڪلڪ ڪرڻ نه وساريو Ctrl + Shift + داخلصفا فارمولا داخل ڪرڻ لاءِ.
جيڪڏهن توهان اهو سمجهڻ ۾ دلچسپي رکو ٿا ته اهو ڪيئن ڪم ڪري ٿو، اچو ته فارمولا جي تفصيلن ۾ ٿورو غور ڪريو:
حصو 1:
IF($F$2=B2:B16,ROW(C2:C16)-1,"")
ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"")
$F$2=B2:B16 - سيل F2 ۾ قيمت جي ڀيٽ ڪريو رينج B2:B16 ۾ هر هڪ قدر سان. جيڪڏهن هڪ ميلاپ ملي، پوء اظهار STRING(C2:C16)-1 ملندڙ لڪير جو تعداد ڏي ٿو (value -1 توهان کي هيڊر لائن شامل نه ڪرڻ جي اجازت ڏئي ٿي). جيڪڏهن ڪو ميلاپ نه آهي، فنڪشن IF (IF) هڪ خالي اسٽرنگ موٽائي ٿو.
فنڪشن جو نتيجو IF (IF) اتي ھڪڙو افقي صف ھوندو: {1,"",3,"",5,"","","","","","",12,"","",""}
حصو 2:
ROW()-3
СТРОКА()-3
هتي فنڪشن قطار (لائن) هڪ اضافي انسداد جي طور تي ڪم ڪري ٿو. جيئن ته فارمولا سيلز F4:F9 ۾ نقل ڪيو ويو آهي، اسان نمبر کي ختم ڪريون ٿا 3 فنڪشن جي نتيجي کان قدر حاصل ڪرڻ لاء 1 سيل ۾ F4 (لائن 4، گھٽائڻ 3) حاصل ڪرڻ لاء 2 سيل ۾ F5 (لائن 5، ذيلي 3) وغيره.
حصو 3:
SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))
НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))
فعل SMALL (SMALL) واپسي ن - اوه ڊيٽا صف ۾ سڀ کان ننڍو قدر. اسان جي حالت ۾، ڪهڙي پوزيشن (ننڍي کان) واپسي جي فنڪشن طرفان طئي ڪئي وئي آهي قطار (لائن) (ڏسو حصو 2). تنهن ڪري، سيل لاء F4 فعل SMALL({array},1) واپسي 1-ٿ (ننڍو) صف جو عنصر، يعني 1. سيل لاء F5 واپسي 2-ٿ صف ۾ ننڍڙو عنصر، اهو آهي 3وغيره
حصو 4:
INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))
ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))
فعل INDEX (INDEX) صرف ھڪڙي مخصوص سيل جي قيمت ھڪڙي صف ۾ ڏئي ٿو سي 2: سي 16. سيل لاء F4 فعل انڊيڪس ($C$2:$C$16) موٽي ويندو مثاللاء F5 فعل انڊيڪس ($C$2:$C$16) موٽي ويندو مٺايون ۽ ايئن.
حصو 5:
IFERROR()
ЕСЛИОШИБКА()
آخر ۾، اسان فارمولا کي فنڪشن اندر رکون ٿا IFERROR (IFERROR)، ڇاڪاڻ ته توهان ممڪن ناهي ته غلطي جي پيغام سان خوش ٿيڻ #AT (#N/A) جيڪڏهن سيلن جو تعداد جنهن ۾ فارمولا نقل ڪيو ويو آهي ان حد کان گهٽ آهي نقل ڪيل قدرن جي تعداد کان جنهن کي ڏٺو پيو وڃي.
ڄاڻايل قطار ۽ ڪالمن طرفان XNUMXD ڳولها
ايڪسل ۾ XNUMXD ڳولا کي انجام ڏيڻ ۾ شامل آهي قدر جي ڳولا هڪ ڄاڻايل قطار ۽ ڪالمن نمبر ذريعي. ٻين لفظن ۾، توهان هڪ خاص قطار ۽ ڪالمن جي چونڪ تي سيل جي قيمت ڪڍي رهيا آهيو.
تنهن ڪري، اچو ته اسان جي ٽيبل ڏانهن وڃو ۽ هڪ فنڪشن سان هڪ فارمولا لکو VPR، جنهن ۾ مارچ ۾ وڪرو ٿيل ليمن جي قيمت بابت معلومات ملندي.
XNUMXD ڳولا انجام ڏيڻ جا ڪيترائي طريقا آهن. اختيارن کي چيڪ ڪريو ۽ ھڪڙو چونڊيو جيڪو توھان لاء مناسب آھي.
VLOOKUP ۽ MATCH افعال
توھان استعمال ڪري سگھوٿا فنڪشن جو ھڪڙو گروپ VPR (VLOOKUP) ۽ وڌيڪ بي نقاب (MATCH) قيمت ڳولڻ لاءِ فيلڊ جي چونڪ تي پيداوار جو نالو (تار) ۽ مهينو (ڪالمن) سوال ۾ صف جو:
=VLOOKUP("Lemons",$A$2:$I$9,MATCH("Mar",$A$1:$I$1,0),FALSE)
=ВПР("Lemons";$A$2:$I$9;ПОИСКПОЗ("Mar";$A$1:$I$1;0);ЛОЖЬ)
مٿي ڏنل فارمولا هڪ باقاعده فنڪشن آهي VPR، جيڪو A2 کان A9 سيلز ۾ "ليمن" جي قدر جو صحيح ميل ڳولي ٿو. پر جيئن توهان کي خبر ناهي ته مارچ جي سيلز ڪهڙي ڪالمن ۾ آهي، توهان ٽين فنڪشن جي دليل لاءِ ڪالمن نمبر سيٽ ڪرڻ جي قابل نه هوندا. VPR. ان جي بدران، فنڪشن استعمال ڪيو ويندو آهي وڌيڪ بي نقابهن ڪالمن جي وضاحت ڪرڻ لاء.
MATCH("Mar",$A$1:$I$1,0)
ПОИСКПОЗ("Mar";$A$1:$I$1;0)
انساني ٻولي ۾ ترجمو ٿيل، هن فارمولا جو مطلب آهي:
- اسان ڪردارن کي ڳولي رهيا آهيون "مار" - دليل ڳوله_ قدر (lookup_value)؛
- A1 کان I1 تائين سيلز ۾ ڏسي رهيا آهيو - دليل lookup_array (lookup_array)؛
- واپسي صحيح ميلاپ- دليل ميچ_قسم (match_type).
استعمال 0 ٽئين دليل ۾، توهان چئو افعال وڌيڪ بي نقاب پهرين قدر جي ڳولا ڪريو جيڪا بلڪل ان قيمت سان ملائي جيڪا توهان ڳولي رهيا آهيو. اهو قدر جي برابر آهي فالس (FALSE) چوٿين دليل لاءِ VPR.
اهو ڪيئن آهي توهان هڪ ٻه طرفي ڳولا فارمولا ٺاهي سگهو ٿا Excel ۾، پڻ سڃاتو وڃي ٿو ٻه طرفي ڳولا يا ٻه طرفي ڳولا.
SUMPRODUCT فنڪشن
فعل SUMPRODUCT (SUMPRODUCT) چونڊيل صفن جي شين جو مجموعو واپس ڏئي ٿو:
=SUMPRODUCT(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar"),$A$2:$I$9)
=СУММПРОИЗВ(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar");$A$2:$I$9)
INDEX ۽ MATCH افعال
ايندڙ آرٽيڪل ۾ آئون انهن ڪمن کي تفصيل سان بيان ڪندس، تنهنڪري هاڻي توهان صرف هن فارمولا کي نقل ڪري سگهو ٿا:
=INDEX($A$2:$I$9,MATCH("Lemons",$A$2:$A$9,0),MATCH("Mar",$A$1:$I$1,0))
=ИНДЕКС($A$2:$I$9;ПОИСКПОЗ("Lemons";$A$2:$A$9;0);ПОИСКПОЗ("Mar";$A$1:$I$1;0))
نالي واريون حدون ۽ چونڪ آپريٽر
جيڪڏهن توهان انهن سڀني پيچيده ايڪسل فارمولن ۾ نه آهيو، توهان شايد هي بصري ۽ يادگار طريقو پسند ڪيو:
- ٽيبل چونڊيو، ٽيب کوليو فارمولين (فارمول) ۽ ڪلڪ ڪريو چونڊ مان ٺاهيو (چونڊ مان ٺاهيو).
- خانه چيڪ ڪريو مٿين قطار (مٿي ڏنل لائن تي) ۽ کاٻي ڪالم (کاٻي پاسي واري ڪالمن ۾). Microsoft Excel توهان جي اسپريڊ شيٽ جي مٿين قطار ۽ کاٻي ڪالمن ۾ قدرن مان حد تائين نالا تفويض ڪندو. ھاڻي توھان انھن نالن کي استعمال ڪندي ڳولي سگھوٿا سڌو سنئون فارمولا ٺاھيو.
- ڪنهن به خالي خاني ۾، لکو = قطار جو نالو ڪالمن جو نالو، مثال طور هن طرح:
= ليمون مار
... يا ان جي برعڪس:
= مار ليمون
ياد رهي ته قطار ۽ ڪالمن جا نالا هڪ اسپيس سان الڳ ٿيڻ گهرجن، جيڪو هن معاملي ۾ چونڪ آپريٽر وانگر ڪم ڪري ٿو.
جڏهن توهان نالو داخل ڪندا آهيو، Microsoft Excel هڪ ٽول ٽپ ڏيکاريندو جنهن سان ملندڙ نالن جي فهرست، جيئن توهان فارمولا داخل ڪندا آهيو.
- پريس شامل ڪريو ۽ نتيجو چيڪ ڪريو
عام طور تي، مٿين طريقن مان جيڪو به توهان چونڊيو ٿا، هڪ ٻه طرفي ڳولا جو نتيجو ساڳيو هوندو:
ھڪڙي فارمولا ۾ گھڻن VLOOKUPs استعمال ڪندي
ائين ٿئي ٿو ته مکيه جدول ۽ ڏسڻ واري جدول ۾ هڪ به ڪالم مشترڪ نه آهي، ۽ اهو توهان کي معمولي فنڪشن استعمال ڪرڻ کان روڪي ٿو. VPR. بهرحال، اتي هڪ ٻيو ٽيبل آهي جنهن ۾ اها معلومات نه آهي جنهن ۾ اسان دلچسپي رکون ٿا، پر هڪ عام ڪالمن سان گڏ مکيه جدول ۽ ڏسڻ واري جدول آهي.
اچو ته هيٺ ڏنل مثال تي هڪ نظر وٺو. اسان وٽ هڪ ڪالم سان هڪ مکيه ٽيبل آهي SKU (نئون), جتي توهان هڪ ڪالم شامل ڪرڻ چاهيو ٿا ساڳئي قيمتن سان ٻئي ٽيبل تان. ان کان علاوه، اسان وٽ 2 ڏسندڙ جدول آھن. پهرين هڪ (ڏسڻ واري جدول 1) ۾ تازه ڪاري نمبر شامل آهن SKU (نئون) ۽ پراڊڪٽ جا نالا، ۽ ٻيو (ڏسو جدول 2) - پراڊڪٽ جا نالا ۽ پراڻا نمبر SKU (پراڻو).
قيمتون شامل ڪرڻ لاءِ ٻئي ڏسندڙ جدول مان مکيه جدول ۾، توھان کي لازمي طور تي عمل ڪرڻ گھرجي جنھن کي ڊبل طور سڃاتو وڃي ٿو VPR يا nested VPR.
- هڪ فنڪشن لکو VPR، جيڪو جدول ۾ پيداوار جو نالو ڳولي ٿو ڳولهي ٽيبل 1استعمال ڪرڻ SKU، جيئن گهربل قدر:
=VLOOKUP(A2,New_SKU,2,FALSE)=ВПР(A2;New_SKU;2;ЛОЖЬ)هتي نئون_SKU - نالي جي حد $A:$B ٽيبل ۾ ڳولهي ٽيبل 1, 2 - هي ڪالم B آهي، جنهن ۾ سامان جا نالا آهن (مٿي ڏنل تصوير ڏسو)
- ٽيبل تان قيمتون داخل ڪرڻ لاءِ فارمولا لکو ڳولهي ٽيبل 2 معروف پيداوار جي نالن جي بنياد تي. هن کي ڪرڻ لاءِ، پيسٽ ڪيو فارمولا جيڪو توهان اڳ ۾ ٺاهيو هو جيئن نئين فنڪشن لاءِ ڳولهڻ جي قيمت VPR:
=VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Price,3,FALSE)=ВПР(ВПР(A2;New_SKU;2;ЛОЖЬ);Price;3;ЛОЖЬ)هتي قيمت - نالي جي حد $A:$C ٽيبل ۾ ڳولهي ٽيبل 2, 3 ڪالم C قيمتن تي مشتمل آهي.
هيٺ ڏنل انگ اکر ڏيکاري ٿو نتيجو جيڪو اسان ٺاهيل فارمولا طرفان موٽايو ويو آهي:
VLOOKUP ۽ INDIRECT استعمال ڪندي مختلف جدولن مان ڊيٽا جي متحرڪ متبادل
پهرين، اچو ته واضح ڪريون ته ”مختلف جدولن مان ڊيٽا جي متحرڪ متبادل“ جي اظهار مان اسان جو مطلب ڇا آهي ان ڳالهه کي يقيني بڻائڻ لاءِ ته اسان هڪ ٻئي کي صحيح طرح سمجهون ٿا.
اتي حالتون آهن جڏهن هڪ ئي فارميٽ جي ڊيٽا سان گڏ ڪيترائي شيٽ آهن، ۽ اهو ضروري آهي ته هڪ خاص شيٽ مان ضروري معلومات ڪڍڻ لاء، ڏنل سيل ۾ داخل ڪيل قيمت تي منحصر آهي. مان سمجهان ٿو ته ان کي هڪ مثال سان بيان ڪرڻ آسان آهي.
تصور ڪريو ته توهان وٽ ڪيترن ئي علائقن لاءِ سيلز رپورٽون آهن ساڳيا پروڊڪٽس ۽ ساڳئي فارميٽ ۾. توھان چاھيو ٿا ھڪڙي مخصوص علائقي لاءِ سيلز جا انگ اکر:
جيڪڏهن توهان وٽ صرف ٻه اهڙيون رپورٽون آهن، ته پوء توهان استعمال ڪري سگهو ٿا ذلت سان سادي فارمولا افعال سان VPR и IF (IF) ڳولڻ لاء گهربل رپورٽ چونڊڻ لاء:
=VLOOKUP($D$2,IF($D3="FL",FL_Sales,CA_Sales),2,FALSE)
=ВПР($D$2;ЕСЛИ($D3="FL";FL_Sales;CA_Sales);2;ЛОЖЬ)
ڪٿي:
- $ 2 $ هڪ سيل آهي جنهن ۾ پيداوار جو نالو آهي. نوٽ ڪريو ته اسان هتي مطلق حوالا استعمال ڪريون ٿا لُڪ اپ قدر کي تبديل ڪرڻ کان بچڻ لاءِ جڏهن فارمولا کي ٻين سيلن ڏانهن نقل ڪيو وڃي.
- ڊي3 علائقي جي نالي سان هڪ سيل آهي. اسان استعمال ڪري رهيا آهيون هڪ مطلق ڪالمن جو حوالو ۽ هڪ لاڳاپو قطار جو حوالو ڇاڪاڻ ته اسان هڪ ئي ڪالمن ۾ فارمولا کي ٻين سيلن ڏانهن نقل ڪرڻ جو منصوبو ٺاهيون ٿا.
- FL_سالes и CA_سيلز - جدولن جا نالا (يا نالي واريون حدون) جن ۾ لاڳاپيل سيلز رپورٽون شامل آھن. توھان ڪري سگھو ٿا، يقينا، عام شيٽ جا نالا ۽ سيل رينج حوالا استعمال ڪريو، مثال طور 'FL Sheet'!$A$3:$B$10, پر نامزد ٿيل حدون وڌيڪ آسان آهن.
بهرحال، جڏهن اهڙا ڪيترائي ٽيبل آهن، فنڪشن IF بهترين حل نه آهي. ان جي بدران، توهان فنڪشن استعمال ڪري سگهو ٿا اڻ سڌي طرح (INDIRECT) مطلوب ڳولا جي حد کي واپس ڪرڻ لاءِ.
جئين توهان شايد ڄاڻو ٿا، فنڪشن اڻ سڌي طرح استعمال ڪيو ويندو آھي ھڪڙي لنڪ کي واپس ڪرڻ لاءِ جيڪو متن جي اسٽرنگ پاران ڏنل آھي، جيڪو بلڪل اھو آھي جيڪو اسان کي ھاڻي گھربل آھي. تنهن ڪري، جرئت سان مٿين فارمولا ۾ اظهار کي فعل سان تبديل ڪريو IF فنڪشن سان ڳنڍڻ اڻ سڌي طرح. هتي هڪ ميلاپ آهي VPR и اڻ سڌي طرح سان سٺو ڪم ڪري ٿو
=VLOOKUP($D$2,INDIRECT($D3&"_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"_Sales");2;ЛОЖЬ)
ڪٿي:
- $ 2 $ - هي هڪ سيل آهي جنهن جي پيداوار جي نالي سان آهي، اهو اڻڄاتل آهي مڪمل لنڪ جي ڪري.
- ڊي3 سيل آھي جنھن ۾ علائقي جي نالي جو پھريون حصو آھي. اسان جي مثال ۾، هي FL.
- _ وڪرو - سڀني نالي جي حدن يا جدولن جي نالي جو عام حصو. جڏهن سيل D3 ۾ قيمت سان گڏ، اهو گهربل حد جي مڪمل طور تي قابليت وارو نالو ٺاهي ٿو. هيٺ انهن لاءِ ڪجهه تفصيل آهن جيڪي فنڪشن ۾ نوان آهن اڻ سڌي طرح.
ڪيئن INDIRECT ۽ VLOOKUP ڪم
پهرين، مون کي توهان کي فنڪشن جي نحو ياد ڏياريو اڻ سڌي طرح (غير سڌي):
INDIRECT(ref_text,[a1])
ДВССЫЛ(ссылка_на_текст;[a1])
پهريون دليل ٿي سگهي ٿو سيل ريفرنس (A1 يا R1C1 انداز)، هڪ حد جو نالو، يا ٽيڪسٽ اسٽرنگ. ٻيو دليل اهو طئي ڪري ٿو ته ڪڙي جو انداز پهرين دليل ۾ شامل آهي:
- A1جيڪڏهن دليل آهي صحيح ڪوڊ (TRUE) يا نه ڄاڻايل؛
- آر 1 سي 1، جيڪڏهن FAS E (FALSE).
اسان جي حالت ۾، لنڪ جو انداز آهي A1، تنهنڪري توهان ٻئي دليل کي ڇڏي ڏيو ۽ پهرين تي ڌيان ڏئي سگهو ٿا.
تنهن ڪري اچو ته اسان جي سيلز رپورٽن ڏانهن واپس وڃو. جيڪڏهن توهان کي ياد آهي، ته پوء هر رپورٽ هڪ الڳ شيٽ تي واقع هڪ الڳ جدول آهي. فارمولا صحيح طريقي سان ڪم ڪرڻ لاءِ، توھان کي پنھنجي جدولن (يا حدن) کي نالو ڏيڻ گھرجي، ۽ سڀني نالن جو ھڪڙو گڏيل حصو ھئڻ گھرجي. مثال طور، هن طرح: CA_سيلز, FL_Sales, TX_Sales ۽ ايئن. جئين توهان ڏسي سگهو ٿا، "_Sales" سڀني نالن ۾ موجود آهي.
فعل اڻ سڌي طرح ڪالم ڊي ۾ قدر کي ڳنڍي ٿو ۽ ٽيڪسٽ اسٽرنگ "_Sales"، انهي سان گڏ ٻڌائي ٿو VPR جنهن ٽيبل ۾ ڳولڻ لاءِ. جيڪڏهن سيل D3 ۾ "FL" قدر شامل آهي، فارمولا ٽيبل کي ڳوليندو FL_Sales، جيڪڏهن "CA" - ٽيبل ۾ CA_سيلز ۽ ايئن.
افعال جو نتيجو VPR и اڻ سڌي طرح هيٺيون هونديون:
جيڪڏهن ڊيٽا مختلف Excel ڪتابن ۾ واقع آهي، ته پوء توهان کي ڪتاب جو نالو شامل ڪرڻ جي ضرورت آهي نالي جي حد کان اڳ، مثال طور:
=VLOOKUP($D$2,INDIRECT($D3&"Workbook1!_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"Workbook1!_Sales");2;ЛОЖЬ)
جيڪڏهن فنڪشن اڻ سڌي طرح هڪ ٻئي ورڪ بڪ ڏانهن اشارو ڪري ٿو، اهو ڪم بڪ کليل هجڻ گهرجي. جيڪڏهن اهو بند آهي، فنڪشن هڪ غلطي جي رپورٽ ڪندو. # ڊي آر ايف! (#SSYL!).