پرمختللي VLOOKUP مثالونه: د څو معیارونو لټون

د فنکشن په اړه زموږ د لوست په دویمه برخه کې VPR (VLOOKUP) په Excel کې، موږ به یو څو مثالونه تحلیل کړو چې تاسو سره به د ټول ځواک په لارښوونه کې مرسته وکړي VPR د خورا هوښیار Excel دندې حل کولو لپاره. مثالونه داسې انګیرل کیږي چې تاسو دمخه د دې خصوصیت کار کولو په اړه لومړني پوهه لرئ. که نه، تاسو ممکن د دې ټیوټوریل لومړۍ برخې سره پیل کولو کې علاقه ولرئ، کوم چې نحو او اساسي کارونې تشریح کوي. VPR. ښه، راځئ چې پیل وکړو.

• په Excel کې د ډیری معیارونو سره لټون وکړئ
• موږ د VLOOKUP په کارولو سره دوهم، دریم، او نور ارزښتونه استخراج کوو
• د مطلوب ارزښت ټول تکرارونه بیرته ترلاسه کړئ
• د پیژندل شوي قطار او کالم لخوا د XNUMXD لټون
• په یوه فورمول کې د ډیری VLOOKUPs کارول
• د مختلف جدولونو څخه د ډیټا متحرک ځای په ځای کول

په Excel کې د ډیری معیارونو سره لټون وکړئ

دنده VPR په Excel کې په ډیټابیس کې د ځانګړي ارزښت لپاره د لټونونو ترسره کولو لپاره واقعیا پیاوړې وسیله ده. په هرصورت، یو مهم محدودیت شتون لري - د دې ترکیب تاسو ته اجازه درکوي یوازې یو ارزښت وپلټئ. که تاسو غواړئ چې د ډیری شرایطو سره لټون وکړئ؟ تاسو به لاندې حل ومومئ.

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 د دوو معیارونو سره د میز سره په بله پاڼه کې لیدل کیږي

که تاسو اړتیا لرئ د دوهم جدول (د لټون جدول) څخه ډیټا اضافه کولو سره اصلي میز (مین جدول) تازه کړئ ، کوم چې په بل شیټ یا بل ایکسل کاري کتاب کې موقعیت لري ، نو تاسو کولی شئ مطلوب ارزښت په مستقیم ډول په فورمول کې راټول کړئ چې تاسو یې داخل کړئ. اصلي میز ته.

لکه څنګه چې په تیرو مثال کې، تاسو به د ګډو ارزښتونو سره د لټون جدول کې یو معاون کالم ته اړتیا ولرئ. دا کالم باید د لټون رینج کې ترټولو کیڼ کالم وي.

نو د فورمول سره 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) او MATCH (نور افشا شوي).

موږ د VLOOKUP په کارولو سره دوهم، دریم، او نور ارزښتونه استخراج کوو

تاسو لا دمخه پوهیږئ VPR یوازې یو مناسب ارزښت بیرته راګرځولی شي، په سمه توګه، لومړی موندل شوی. مګر څه که چیرې دا ارزښت په لیدل شوي صف کې څو ځله تکرار شي، او تاسو غواړئ د دوی دوهم یا دریم استخراج کړئ؟ که ټول ارزښتونه څه وي؟ ستونزه پیچلې ښکاري، مګر حل شتون لري!

فرض کړئ د جدول یوه کالم د پیرودونکو نومونه لري (د پیرودونکي نوم) ، او بل کالم هغه محصولات (محصول) لري چې دوی یې اخیستي. راځئ هڅه وکړو چې د ورکړل شوي پیرودونکي لخوا اخیستل شوي دوهم، دریم او څلورم توکي ومومئ.

ترټولو اسانه لاره د کالم څخه دمخه د معاون کالم اضافه کول دي پیریدونکي نوم او دا د هر نوم د تکرار شمیرې سره د پیرودونکو نومونو سره ډک کړئ، د بیلګې په توګه، جان دو 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 - یو حجره چې د پیرودونکي نوم لري (دا بدله نه ده، مهرباني وکړئ په یاد ولرئ - لینک مطلق دی)؛
• $ B $ – کالم پیریدونکي نوم;
• جدول4 - ستاسو میز (دا ځای هم یو منظم حد کیدی شي)؛
• $16 - ستاسو د میز یا رینج پای حجره.

دا فورمول یوازې دوهم مطابقت لرونکی ارزښت پیدا کوي. که تاسو د پاتې تکرارونو استخراج ته اړتیا لرئ، پخوانی حل وکاروئ.

که تاسو د ټولو میچونو لیست ته اړتیا لرئ - فنکشن VPR دا یو مرستندوی ندی، ځکه چې دا یوازې په یو وخت کې یو ارزښت بیرته راګرځوي - دوره. مګر Excel یو فعالیت لري انډکس (INDEX)، کوم چې کولی شي په اسانۍ سره دا کار سرته ورسوي. دا ډول فورمول به څنګه ښکاري، تاسو به په لاندې مثال کې زده کړئ.

د مطلوب ارزښت ټول تکرارونه بیرته ترلاسه کړئ

لکه څنګه چې پورته یادونه وشوه VPR د سکین شوي حد څخه ټول نقل شوي ارزښتونه نشي ایستل کیدی. د دې کولو لپاره، تاسو یو څه ډیر پیچلي فارمول ته اړتیا لرئ، د ډیری Excel افعال څخه جوړ شوی، لکه انډکس (INDEX) کوچني (کوچنی) او ROW (لاین)

د مثال په توګه، لاندې فورمول د B2:B2 په سلسله کې د حجرې F16 څخه د ارزښت ټول تکرارونه لټوي او په کالم 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 د اړونده کرښې شمیره راګرځوي (ارزښت -1 تاسو ته اجازه درکوي چې سرلیک لاین شامل نه کړئ). که چیرې هیڅ میچ شتون ونلري، فعالیت IF (IF) یو خالي تار راګرځوي.

د فعالیت پایله IF (IF) به داسې افقي صف وي: {1,"",3,"",5,"","","","","","",12,"","",""}

2 برخه:

ROW()-3

СТРОКА()-3

دلته فعالیت ROW (لاین) د اضافي کاونټر په توګه کار کوي. څرنګه چې فورمول په F4:F9 حجرو کې کاپي شوی، موږ شمیره کموو 3 د ارزښت ترلاسه کولو لپاره د فعالیت پایلې څخه 1 په حجره کې F4 (۴ کرښه، ۳ فرعي) تر لاسه کول 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))

دنده کوچني (کوچنی) بیرته راګرځي n-اوه د معلوماتو په لړۍ کې ترټولو کوچنی ارزښت. زموږ په قضیه کې ، کوم موقعیت (له کوچني څخه) بیرته راستنیدو لپاره د فنکشن لخوا ټاکل کیږي ROW (لین) (دوهمه برخه وګورئ). نو، د یوې حجرې لپاره F4 دنده کوچنی({لاری},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) په ساده ډول په یوه صف کې د ځانګړي حجرې ارزښت بیرته راګرځوي C2:C16. د حجرو لپاره F4 دنده INDEX($C$2:$C$16) بیرته به راشي مڼهد F5 دنده INDEX($C$2:$C$16) بیرته به راشي خواږه او همداسی پسی.

5 برخه:

IFERROR()

ЕСЛИОШИБКА()

په پای کې، موږ فورمول د فنکشن دننه کېښودو IFERROR (IFERROR)، ځکه چې تاسو د خطا پیغام څخه خوښ نه یاست #AT (#N/A) که چیرې د هغه حجرو شمیر چې فورمول یې کاپي شوی وي د لیدل شوي رینج کې د نقل شوي ارزښتونو شمیر څخه کم وي.

د پیژندل شوي قطار او کالم لخوا د XNUMXD لټون

په Excel کې د 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);
• دقیق میچ بیرته راګرځول - دلیل match_type (match_type).

د کارولو 0 په دریم دلیل کې، تاسو وظیفې وایئ نور افشا شوي د لومړي ارزښت لپاره وګورئ چې دقیقا د هغه ارزښت سره سمون لري چې تاسو یې په لټه کې یاست. دا د ارزښت سره برابر دی غلط (FALSE) د څلورم دلیل لپاره VPR.

دا څنګه تاسو کولی شئ په Excel کې د دوه اړخیز لټون فارمول رامینځته کړئ ، چې د دوه اړخیز لټون یا دوه اړخیز لټون په نوم هم پیژندل کیږي.

د 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))

نومول شوي سلسلې او د تقاطع چلونکی

که تاسو په دې ټولو پیچلي ایکسل فارمولونو کې نه یاست، نو تاسو ممکن دا بصری او یادګار لاره خوښ کړئ:

1. میز غوره کړئ، ټب خلاص کړئ فورمول (فارمول) او کلیک وکړئ له انتخاب څخه جوړ کړئ (له انتخاب څخه جوړ کړئ).
2. بکسونه چیک کړئ پورته قطار (په پورتنۍ کرښه کې) او کیڼ کالم (په کیڼ اړخ کې په کالم کې). مایکروسافټ ایکسل به ستاسو د سپریډ شیټ په پورتنۍ قطار او کیڼ کالم کې د ارزښتونو څخه سلسلو ته نومونه وټاکي. اوس تاسو کولی شئ د دې نومونو په کارولو سره مستقیم د فورمولونو جوړولو پرته لټون وکړئ.
3. په هر خالي حجره کې، ولیکئ = قطار_نوم کالم_نومد مثال په توګه دا:

   = لیمو مار

   یا برعکس:

   = مار لیمون

   په یاد ولرئ چې د قطار او کالم نومونه باید د ځای په واسطه جلا شي، کوم چې پدې حالت کې د تقاطع آپریټر په څیر کار کوي.

کله چې تاسو یو نوم داخل کړئ، مایکروسافټ اکسل به د ورته نومونو لیست سره یو اوزار ټیک وښیې، لکه څنګه چې تاسو یو فورمول داخل کړئ.

4. مطبوعاتي وليکئ او پایله وګورئ

په عموم کې، هر یو له پورته میتودونو څخه چې تاسو یې غوره کوئ، د دوه اړخیز لټون پایله به ورته وي:

په یوه فورمول کې د ډیری VLOOKUPs کارول

داسې پیښیږي چې اصلي جدول او د لټون جدول یو واحد کالم په ګډه نلري ، او دا تاسو د معمول فعالیت کارولو مخه نیسي. VPR. په هرصورت، یو بل جدول شتون لري چې هغه معلومات نلري چې موږ یې لیوالتیا لرو، مګر د اصلي میز او د لټون میز سره یو ګډ کالم لري.

راځئ چې لاندې مثال ته یو نظر وکړو. موږ د کالم سره اصلي میز لرو SKU (نوی)، چیرې چې تاسو غواړئ د بل میز څخه د ورته نرخونو سره کالم اضافه کړئ. برسېره پردې، موږ 2 د لټون میزونه لرو. لومړی (د لټون جدول 1) تازه شمیرې لري SKU (نوی) او د محصول نومونه، او دوهم (د لټون جدول 2) - د محصول نومونه او زاړه شمیرې SKU (زاړه).

د دویم لید جدول څخه اصلي میز ته د قیمتونو اضافه کولو لپاره، تاسو باید یو عمل ترسره کړئ چې د ډبل په نوم پیژندل کیږي VPR یا ځړول شوی VPR.

1. یو فنکشن ولیکئ VPR، کوم چې په جدول کې د محصول نوم موندلی د کتلو جدول 1د کارولو SKUد مطلوب ارزښت په توګه:

   =VLOOKUP(A2,New_SKU,2,FALSE)

   =ВПР(A2;New_SKU;2;ЛОЖЬ)

   دلته نوی_SKU - نومول شوی سلسله $A:$B په میز کې د کتلو جدول 1, 2 - دا B کالم دی، کوم چې د توکو نومونه لري (پورته انځور وګورئ)

2. د میز څخه د قیمتونو داخلولو لپاره فورمول ولیکئ د کتلو جدول 2 د مشهور محصول نومونو پر بنسټ. د دې کولو لپاره، هغه فورمول پیسټ کړئ چې تاسو مخکې د نوي فعالیت لپاره د لټون ارزښت په توګه جوړ کړی VPR:

   =VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Price,3,FALSE)

   =ВПР(ВПР(A2;New_SKU;2;ЛОЖЬ);Price;3;ЛОЖЬ)

   دلته د بیې - نومول شوی سلسله $A:$C په میز کې د کتلو جدول 2, 3 کالم C دی چې قیمتونه لري.

لاندې انځور د هغه فورمول لخوا بیرته راګرځیدلې پایله ښیې چې موږ یې جوړ کړی:

د VLOOKUP او غیر مستقیم په کارولو سره د مختلف جدولونو څخه د ډیټا متحرک ځای په ځای کول

لومړی، راځئ چې روښانه کړو چې موږ د "مختلف جدولونو څخه د ډیټا متحرک بدیل" د بیان څخه څه معنی لرو ترڅو ډاډ ترلاسه کړو چې موږ یو بل په سمه توګه پوهیږو.

داسې شرایط شتون لري کله چې د ورته ب formatې ډیټا سره څو شیټونه شتون ولري ، او دا اړینه ده چې اړین معلومات له یو ځانګړي شیټ څخه راوباسئ ، د هغه ارزښت پورې اړه لري چې په ورکړل شوي حجره کې داخل شوي. زما په اند دا د مثال په واسطه تشریح کول اسانه دي.

تصور وکړئ چې تاسو د ورته محصولاتو سره او په ورته شکل کې د څو سیمو لپاره د پلور راپورونه لرئ. تاسو غواړئ د یوې ځانګړې سیمې لپاره د پلور ارقام ومومئ:

که تاسو یوازې دوه داسې راپورونه لرئ، نو تاسو کولی شئ د دندو سره د شرم وړ ساده فورمول وکاروئ VPR и IF (IF) د لټون لپاره مطلوب راپور غوره کولو لپاره:

=VLOOKUP($D$2,IF($D3="FL",FL_Sales,CA_Sales),2,FALSE)

=ВПР($D$2;ЕСЛИ($D3="FL";FL_Sales;CA_Sales);2;ЛОЖЬ)

ځای:

• $D $2 یو حجره ده چې د محصول نوم لري. په یاد ولرئ چې موږ دلته مطلق حوالې کاروو ترڅو د لید ارزښت بدلولو څخه مخنیوی وشي کله چې فارمول نورو حجرو ته کاپي کیږي.
• $D3 یوه حجره ده چې د سیمې نوم لري. موږ د مطلق کالم حواله او د اړونده قطار حواله کاروو ځکه چې موږ پلان لرو چې فورمول په ورته کالم کې نورو حجرو ته کاپي کړو.
• FL_سالes и CA_ خرڅلاو - د میزونو نومونه (یا نومول شوي سلسلې) چې د پلور اړوند راپورونه لري. تاسو کولی شئ، البته، د معمول پاڼې نومونه او د سیل رینج حوالې وکاروئ، د بیلګې په توګه 'FL پاڼه'!$A$3:$B$10، مګر نومول شوي سلسلې خورا اسانه دي.

په هرصورت، کله چې ډیری داسې میزونه شتون ولري، فعالیت IF غوره حل نه دی. پرځای یې، تاسو کولی شئ فنکشن وکاروئ پیژندنه (غیر مستقیم) د مطلوب لټون رینج بیرته راستنیدو لپاره.

لکه څنګه چې تاسو شاید پوهیږئ، فعالیت پیژندنه د متن تار لخوا ورکړل شوي لینک بیرته راستنولو لپاره کارول کیږي، کوم چې په سمه توګه هغه څه دي چې موږ ورته اړتیا لرو. نو، په زړورتیا سره په پورتنۍ فورمول کې بیان د فنکشن سره بدل کړئ IF د فعالیت سره نښلول پیژندنه. دلته یو ترکیب دی VPR и پیژندنه سره ښه کار کوي:

=VLOOKUP($D$2,INDIRECT($D3&"_Sales"),2,FALSE)

=ВПР($D$2;ДВССЫЛ($D3&"_Sales");2;ЛОЖЬ)

ځای:

• $D $2 - دا د محصول نوم سره حجره ده ، دا د مطلق لینک له امله بدله نه ده.
• $D3 هغه حجره ده چې د سیمې نوم لومړۍ برخه لري. زموږ په مثال کې، دا FL.
• _ پلورل - د ټولو نومول شویو رینجونو یا جدولونو د نوم ګډه برخه. کله چې په D3 حجره کې د ارزښت سره یوځای شي، دا د اړتیا وړ حد بشپړ وړ نوم جوړوي. لاندې د هغو کسانو لپاره ځینې توضیحات دي چې فعالیت ته نوي دي پیژندنه.

INDIRECT او VLOOKUP څنګه کار کوي

لومړی، اجازه راکړئ تاسو ته د فنکشن ترکیب یاد کړم پیژندنه (مستقیم):

INDIRECT(ref_text,[a1])

ДВССЫЛ(ссылка_на_текст;[a1])

لومړی دلیل کیدای شي د حجرې حواله وي (A1 یا R1C1 سټایل)، د حد نوم، یا د متن تار. دوهم دلیل ټاکي چې په لومړي دلیل کې د لینک کوم ډول ډول شتون لري:

• A1که دلیل وي ریښتینی کوډ (رښتیا) یا نه مشخص شوی؛
• R1C1، که FAS E (FALSE).

زموږ په قضیه کې، لینک سټایل لري A1نو تاسو کولی شئ دوهم دلیل پریږدئ او په لومړي تمرکز وکړئ.

نو راځئ چې بیرته زموږ د پلور راپورونو ته ورشو. که تاسو په یاد ولرئ، نو هر راپور یو جلا جدول دی چې په جلا پاڼه کې موقعیت لري. د دې لپاره چې فورمول په سمه توګه کار وکړي، تاسو باید خپل میزونه (یا سلسلې) نوم کړئ، او ټول نومونه باید یو ګډ برخه ولري. د مثال په توګه، دا ډول: CA_ خرڅلاو, FL_Sells, TX_Sells او همداسی پسی. لکه څنګه چې تاسو لیدلی شئ، "_ پلور" په ټولو نومونو کې شتون لري.

دنده پیژندنه ارزښت په D کالم کې او د متن تار "_Sales" سره نښلوي، په دې توګه ویل کیږي VPR په کوم میز کې لټون کول. که چیرې حجره D3 د "FL" ارزښت ولري، فورمول به جدول وپلټي FL_Sells، که "CA" - په جدول کې CA_ خرڅلاو او همداسی پسی.

د دندو پایله VPR и پیژندنه لاندې به وي:

که ډاټا په مختلف Excel کتابونو کې موقعیت لري، نو تاسو اړتیا لرئ چې د نوم شوي حد څخه مخکې د کتاب نوم اضافه کړئ، د بیلګې په توګه:

=VLOOKUP($D$2,INDIRECT($D3&"Workbook1!_Sales"),2,FALSE)

=ВПР($D$2;ДВССЫЛ($D3&"Workbook1!_Sales");2;ЛОЖЬ)

که فعالیت پیژندنه بل کاري کتاب ته اشاره کوي، هغه کاري کتاب باید خلاص وي. که دا بند وي، فنکشن به د یوې تېروتنې راپور ورکړي. #REF! (#SSYL!).