ولې INDEX او MATCH په Excel کې د VLOOKUP څخه غوره دي

موږ دمخه پیل کونکو ته تشریح کړی چې څنګه د VLOOKUP اساسي دندې وکاروو (په انګلیسي VLOOKUP، لنډیز د "عمودي لټون فعالیت" لپاره ولاړ دی). او تجربه لرونکي کاروونکو ته څو نور پیچلي فارمولونه وښودل شول.

او پدې مقاله کې به موږ هڅه وکړو چې د عمودی لټون سره د کار کولو بل میتود په اړه معلومات ورکړو.

تاسو شاید حیران یاست: "ولې دا اړین دی؟" او دا د ټولو ممکنه لټون میتودونو ښودلو لپاره اړین دی. سربیره پردې، ډیری VLOOKUP محدودیتونه ډیری وختونه د مطلوب پایلې ترلاسه کولو مخه نیسي. په دې اړه، INDEX( ) MATCH( ) ډیر فعال او متنوع دي، او دوی لږ محدودیتونه هم لري.

اساسی INDEX میچ

څرنګه چې د دې لارښود موخه دا ده چې وښيي دا ځانګړتیا څومره ښه ده، موږ راځئ چې د دې د عملیاتو د اصولو په اړه لومړني معلومات وګورو. او موږ به مثالونه وښیو، او دا هم په پام کې ونیسئ چې ولې، دا د VLOOKUP څخه غوره دی ().

د INDEX فنکشن ترکیب او کارول

دا فنکشن د کالم یا لاین شمیرې پراساس د ټاکل شوي لټون ساحې ترمینځ د مطلوب ارزښت موندلو کې مرسته کوي. سنټیکس

= INDEX(سری، د قطار شمیره، د کالم شمیره):

  • صف - هغه ساحه چې لټون به یې ترسره شي؛
  • د کرښې شمیره - د کرښې شمیره چې په ټاکل شوي صف کې پلټل کیږي. که چیرې د قطار شمیره معلومه نه وي، د کالم شمیره باید مشخص شي؛
  • د کالم شمیره - د کالم شمیره چې په ټاکل شوي صف کې موندل کیږي. که ارزښت نامعلوم وي، د کرښې شمیره اړینه ده.

د ساده فورمول یوه بیلګه:

=INDEX(A1:S10,2,3)

فنکشن به د A1 څخه تر C10 پورې رینج کې لټون وکړي. شمیرې ښیې چې کوم قطار (2) او کالم (3) د مطلوب ارزښت ښودلو لپاره. پایله به د C2 حجره وي.

ډیر ساده، سمه ده؟ مګر کله چې تاسو د حقیقي اسنادو سره کار کوئ، تاسو احتمال نلري چې د کالم شمیرو یا حجرو په اړه معلومات ولرئ. دا هغه څه دي چې د MATCH() فعالیت د دې لپاره دی.

د MATCH فنکشن ترکیب او کارول

د MATCH() فنکشن د مطلوب ارزښت لټون کوي ​​او د لټون په ټاکل شوي ساحه کې د هغې نږدې شمیره ښیې.

د لټون پوسټ () ترکیب داسې ښکاري:

=MATCH(د کتلو ارزښت، د کتلو لپاره صف، د میچ ډول)

  • د لټون ارزښت - هغه شمیره یا متن چې موندل کیږي؛
  • د لټون سرې - هغه ساحه چیرې چې لټون به ترسره شي؛
  • د میچ ډول - مشخص کوي چې ایا دقیق ارزښت یا ارزښتونو ته نږدې وګورئ:
    • 1 (یا هیڅ ارزښت ندی مشخص شوی) – ترټولو لوی ارزښت بیرته راګرځوي چې د ټاکل شوي ارزښت سره مساوي یا لږ وي.
    • 0 - د لټون شوي ارزښت سره دقیق میچ ښیې. په ترکیب کې INDEX() MATCH() تاسو به نږدې تل دقیق میچ ته اړتیا ولرئ، نو موږ 0 لیکو؛
    • -1 - ترټولو کوچنی ارزښت ښیې چې په فورمول کې ټاکل شوي ارزښت څخه لوی یا مساوي وي. ترتیب په نزولي ترتیب سره ترسره کیږي.

د مثال په توګه، د B1 په حد کې: ​​B3 نیویارک، پاریس، لندن راجستر شوي. لاندې فورمول به 3 شمیره وښیې ځکه چې لندن په لیست کې دریم دی:

= EXPOSE(لندن،B1:B3,0)

د INDEX MATCH فنکشن سره څنګه کار کول 

تاسو شاید دمخه په هغه اصولو پوهیدل پیل کړي چې د دې دندو ګډ کار رامینځته کیږي. په لنډه توګه، بیا INDEX() د ټاکل شویو قطارونو او کالمونو ترمنځ د مطلوب ارزښت لټون کوي. او MATCH() د دې ارزښتونو شمیرې ښیې:

=INDEX(هغه کالم چې له هغې څخه ارزښت بیرته راګرځول کیږي، MATCH (د لټون ارزښت، د لټون لپاره کالم، 0))

لاهم سخت وخت لرئ پوه شئ چې دا څنګه کار کوي؟ شاید یو مثال به ښه تشریح کړي. فرض کړئ چې تاسو د نړۍ پلازمینې او د دوی نفوس لیست لرئ:

د دې لپاره چې د یوې ټاکلې پلازمینې نفوس اندازه معلومه کړو، د بیلګې په توګه، د جاپان پلازمینه، موږ لاندې فورمول کاروو:

=INDEX(C2:C10, MATCH(جاپان, A2:A10,0))

وضاحت:

  • د MATCH() فنکشن د A2:A10 په صف کې د "جاپان" ارزښت لټوي او 3 شمیره بیرته راګرځوي، ځکه چې جاپان په لیست کې دریم ارزښت دی. 
  • دا ارقام ته ځيد کرښې شمیرهپه INDEX() فارمول کې او فنکشن ته وايي چې د دې قطار څخه ارزښت چاپ کړي.

نو پورته فورمول معیاري فورمول کیږي INDEX(C2:C10,3). فورمول له C2 څخه تر C10 حجرو لټون کوي ​​او په دې سلسله کې د دریمې حجرې څخه ډاټا بیرته راګرځوي، دا C4 دی، ځکه چې شمیرنه د دویم قطار څخه پیل کیږي.

نه غواړې چې د ښار نوم په فورمول کې ولیکی؟ بیا یې په هره حجره کې ولیکئ، F1 ووایاست، او د MATCH() فورمول کې یې د حوالې په توګه وکاروئ. او تاسو د متحرک لټون فارمول سره پای ته ورسیږئ:

=ИНДЕКС(С2:С10, ПОИСКПОЗ( )(F1,A2:A10,0))

ولې INDEX او MATCH په Excel کې د VLOOKUP څخه غوره دي

مهم! د کرښو شمیر سور INDEX() باید د قطارونو شمیر سره ورته وي په پام کې نیول شوی صف په MATCH() کې، که نه نو تاسو به غلط پایله ترلاسه کړئ.

یوه دقیقه انتظار وکړئ، ولې یوازې د VLOOKUP() فارمول نه کاروئ؟

=VLOOKUP(F1, A2:C10, 3, غلط)

 د INDEX میچ د دې ټولو پیچلتیاو په موندلو کې د وخت ضایع کول څه دي؟

په دې حالت کې، دا مهمه نده چې کوم فعالیت وکاروئ. دا یوازې یو مثال دی چې پوه شي چې څنګه د INDEX() او MATCH() افعال یوځای کار کوي. نور مثالونه به وښيي چې دا دندې په داسې شرایطو کې د وړتیا وړ دي چیرې چې VLOOKUP بې ځواکه وي. 

INDEX MATCH یا VLOOKUP

کله چې پریکړه وکړئ چې د لټون کوم فارمول وکاروئ، ډیری خلک موافق دي چې INDEX() او MATCH() د VLOOKUP څخه خورا لوړ دي. په هرصورت، ډیری خلک اوس هم VLOOKUP(). لومړی، VLOOKUP() ساده دی، او دویم، کاروونکي د INDEX() او MATCH() سره د کار کولو ټولې ګټې په بشپړه توګه نه پوهیږي. د دې پوهې پرته، هیڅوک به موافق نه وي چې خپل وخت د پیچلي سیسټم په مطالعه مصرف کړي.

دلته د VLOOKUP() په پرتله د INDEX() او MATCH() کلیدي ګټې دي:

 

  • له ښي څخه کیڼ ته لټون وکړئ. VLOOKUP() نشي کولی له ښیې څخه کیڼ ته لټون وکړي، نو هغه ارزښتونه چې تاسو یې په لټه کې یاست باید تل د میز په کیڼ اړخ کې وي. مګر INDEX() او MATCH() کولی شي دا پرته له کومې ستونزې اداره کړي. دا مقاله به تاسو ته ووایي چې دا په عمل کې څه ښکاري: په ښي خوا کې د مطلوب ارزښت موندلو څرنګوالی.

 

  1. د کالمونو خوندي اضافه یا لرې کول. د VLOOKUP() فورمول د کالمونو د لرې کولو یا اضافه کولو په وخت کې غلطې پایلې ښیې ځکه چې VLOOKUP() د بریالیتوب لپاره دقیق کالم شمیرې ته اړتیا لري. په طبیعي توګه، کله چې کالمونه اضافه یا لیرې شي، د دوی شمیر هم بدلیږي. 

او په INDEX() او MATCH() فورمولونو کې، د کالمونو لړۍ مشخص شوې، نه انفرادي کالمونه. د پایلې په توګه، تاسو کولی شئ په خوندي توګه کالمونه اضافه او لرې کړئ پرته له دې چې هر ځل فورمول تازه کړئ.

  1. د لټون حجمونو باندې هیڅ محدودیت نشته. کله چې VLOOKUP() وکاروئ، د لټون معیارونو ټولټال شمیر باید له 255 حروفونو څخه ډیر نه وي یا تاسو به #VALUE ترلاسه کړئ! نو که ستاسو ډاټا لوی شمیر حروف ولري، INDEX() او MATCH() غوره انتخاب دی.
  2. د لوړ پروسس سرعت. که ستاسو میزونه نسبتا کوچني وي، نو تاسو احتمال نه لرئ چې کوم توپیر وګورئ. مګر، که چیرې جدول په سلګونو یا زرګونو قطارونه ولري، او په وینا یې، په سلګونو او زرګونه فورمولونه شتون لري، INDEX () او MATCH () به د VLOOKUP () په پرتله خورا ګړندي مقابله وکړي. حقیقت دا دی چې اکسل به د ټول جدول پروسس کولو پرځای یوازې په فورمول کې مشخص شوي کالمونه پروسس کړي. 

د VLOOKUP() د فعالیت اغیزه به په ځانګړې توګه د پام وړ وي که ستاسو کاري پاڼه ډیری فورمولونه لکه VLOOKUP() او SUM(). د VLOOKUP() دندو جلا چیکونو ته اړتیا ده چې هر ارزښت په یوه صف کې تجزیه کړي. نو ایکسل باید د معلوماتو لوی مقدار پروسس کړي ، او دا کار د پام وړ ورو کوي.

د فورمول مثالونه 

موږ دمخه د دې دندو ګټورتیا موندلې ، نو موږ کولی شو ترټولو په زړه پورې برخې ته لاړ شو: په عمل کې د پوهې پلي کول.

د ښي څخه کیڼ لور ته د لټون کولو فورمول

لکه څنګه چې مخکې یادونه وشوه، VLOOKUP نشي کولی د لټون دا بڼه ترسره کړي. نو، که مطلوب ارزښتونه په کیڼ اړخ کې نه وي، VLOOKUP() به پایله ونه کړي. د INDEX() او MATCH() افعال ډیر متنوع دي، او د ارزښتونو موقعیت د دوی د کار کولو لپاره لوی رول نه لوبوي.

د مثال په توګه، موږ به د خپل میز کیڼ اړخ ته د رتبو کالم اضافه کړو او هڅه وکړو چې معلومه کړو چې زموږ د هیواد پلازمینه د نفوس له مخې کومه درجه لري.

په حجره G1 کې، موږ د موندلو ارزښت لیکو، او بیا د C1:C10 حد کې د لټون لپاره لاندې فورمول وکاروو او د A2:A10 څخه ورته ارزښت بیرته راګرځوو:

=ИНДЕКС(А2:А10, ПОИСКПОЗ(G1,C1:C10,0))

ولې INDEX او MATCH په Excel کې د VLOOKUP څخه غوره دي

سمدستي. که تاسو پلان لرئ چې دا فارمول د څو حجرو لپاره وکاروئ، ډاډ ترلاسه کړئ چې تاسو د مطلق ادرس په کارولو سره سلسلې تنظیم کړئ (د مثال په توګه، $A$2: $A$10 او $C$2: 4C$10).

Index MORE EXPOSED MORE EXPOSED  په کالمونو او قطارونو کې لټون کول

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

دا پیچلې ښکاري، مګر د داسې محاسبې فورمول د معیاري INDEX() MATCH() فارمول سره ورته دی، یوازې یو توپیر سره: د MATCH() فورمول باید دوه ځله وکارول شي. لومړی ځل د قطار نمبر ترلاسه کولو لپاره، او دویم ځل د کالم نمبر ترلاسه کولو لپاره:

=INDEX(سری، MATCH(عمودی لټون ارزښت، د لټون کالم، 0)، MATCH(افقی لټون ارزښت، د لټون قطار، 0))

راځئ چې لاندې جدول ته وګورو او هڅه وکړو چې یو فورمول جوړ کړو INDEX() EXPRESS() EXPRESS() د یو ټاکل شوي کال لپاره په ځانګړي هیواد کې د ډیموګرافیک ښودلو لپاره.

هدف هیواد په حجره G1 کې دی (عمودی لید) او هدف کال په حجره G2 کې دی (افقی لید). فورمول به داسې ښکاري:

=ИНДЕКС(B2:D11, ПОИСКПОЗ(G1,A2:A11,0), ПОИСКПОЗ(G2,B1:D1,0))

ولې INDEX او MATCH په Excel کې د VLOOKUP څخه غوره دي

دا فورمول څنګه کار کوي

د نورو پیچلو فارمولونو په څیر، دوی د انفرادي مساواتو په ویشلو سره د پوهیدلو لپاره اسانه دي. او بیا تاسو کولی شئ پوه شئ چې هر انفرادي فعالیت څه کوي:

  • لوبه(G1,A2:A11,0) - د A1:A2 په حد کې د ارزښت (G11) لټون کوي ​​او د دې ارزښت شمیره ښیې، زموږ په قضیه کې دا 2 دی؛
  • لټون(G2,B1:D1,0) - د B2:D1 په حد کې ارزښت (G1) لټوي. په دې حالت کې، پایله 3 وه.

موندل شوي قطار او کالم شمیرې د INDEX() فورمول کې ورته ارزښت ته لیږل کیږي:

=INDEX(B2:D11,2,3)

د پایلې په توګه، موږ یو ارزښت لرو چې د B2:D3 په حد کې د 2 قطارونو او 11 کالمونو په تقاطع کې په یوه حجره کې دی. او فورمول مطلوب ارزښت ښیي، کوم چې په D3 حجره کې دی.

د INDEX او میچ سره د ډیری شرایطو سره لټون وکړئ

که تاسو د VLOOKUP() لپاره زموږ لارښود لوستلی وي، تاسو شاید د لټون ډیری فارمولونه هڅه کړې وي. مګر د لټون دا طریقه یو مهم محدودیت لري - د مرستندویه کالم اضافه کولو اړتیا.

مګر ښه خبر دا دی د INDEX() او MATCH() سره تاسو کولی شئ ډیری شرایط وپلټئ پرته لدې چې خپل کاري پاڼه ترمیم یا بدل کړئ.

دلته د INDEX() میچ() لپاره د څو حالتونو عمومي لټون فارمول دی:

{=ИНДЕКС(диапазон поиска, ПОИСКПОЗ(1,условие1=диапазон1)*(условвие2=диапазон2),0))}

یادونه: دا فورمول باید د کیبورډ شارټ کټ سره یوځای وکارول شي CTRL+SHIFT+ENTER.

فرض کړئ چې تاسو اړتیا لرئ هغه ارزښت ومومئ چې تاسو یې د 2 شرایطو پراساس په لټه کې یاست: اخیستونکي и محصول.

دا لاندې فورمول ته اړتیا لري:

=ИНДЕКС(С2:С10, ПОИСКПОЗ(1,(F1=A2:A10)*(F2=B1:B10),0))

په دې فورمول کې، C2:C10 هغه حد دی چې لټون به یې ترسره شي، F1 - دا حالت، A2:A10 — د حالت پرتله کولو حد دی، F2 - شرط 2 V2:V10 - د 2 حالت پرتله کولو لپاره حد.

د فارمول سره د کار په پای کې د ترکیب فشار مه هیروئ CTRL+SHIFT+ENTER - ایکسل به په اوتومات ډول فارمول د منحل منحلاتو سره وتړي ، لکه څنګه چې په مثال کې ښودل شوي:

ولې INDEX او MATCH په Excel کې د VLOOKUP څخه غوره دي

که تاسو نه غواړئ د خپل کار لپاره د صف فارمول وکاروئ، نو بیا بل INDEX() فورمول ته اضافه کړئ او ENTER فشار ورکړئ، دا به د مثال په څیر ښکاري:

ولې INDEX او MATCH په Excel کې د VLOOKUP څخه غوره دي

دا فورمولونه څنګه کار کوي

دا فورمول د معیاري INDEX() MATCH() فورمول په څیر کار کوي. د ډیری شرایطو لټون کولو لپاره، تاسو په ساده ډول ډیری غلط او ریښتین شرایط رامینځته کوئ چې د سم او ناسم انفرادي شرایطو استازیتوب کوي. او بیا دا شرایط د صف په ټولو اړوندو عناصرو باندې پلي کیږي. فورمول غلط او ریښتیني دلیلونه په ترتیب سره 0 او 1 ته بدلوي، او یو سري راوباسي چیرې چې 1 هغه مناسب ارزښتونه دي چې په تار کې موندل شوي. MATCH() به لومړی ارزښت ومومي چې د 1 سره سمون لري او د INDEX() فورمول ته به یې انتقال کړي. او دا به په بدل کې د مطلوب کالم څخه په ټاکل شوې کرښه کې دمخه مطلوب ارزښت بیرته راولي.

یو فارمول پرته له صف څخه د INDEX() په وړتیا پورې اړه لري ترڅو دوی پخپله اداره کړي. په فورمول کې دوهم INDEX() د غلط (0) سره سمون لري، نو دا د دې ارزښتونو سره د MATCH() فورمول ته ټول صف تیریږي. 

دا د دې فورمول تر شا د منطق یو ډیر اوږد توضیح دی. د نورو معلوماتو لپاره مقاله ولولئ "INDEX د ډیری شرایطو سره میچ".

په INDEX او MATCH کې اوسط، MAX او MIN

Excel د اوسط، اعظمي او لږترلږه موندلو لپاره خپل ځانګړي دندې لري. مګر څه که تاسو غواړئ د دې ارزښتونو سره تړلي حجرې څخه ډاټا ترلاسه کړئ؟ په دې صورت کې اوسط، MAX او MIN باید د INDEX او میچ سره په ګډه وکارول شي.

INDEX میچ او MAX

د D په کالم کې ترټولو لوی ارزښت موندلو او په C کالم کې ښودلو لپاره، فورمول وکاروئ: 

=ИНДЕКС(С2:С10, ПОИСКПОЗ(МАКС(D2:D10),D2:D10,0))

د انډیکس میچ او MIN

د D په کالم کې د کوچني ارزښت موندلو او په C کالم کې د ښودلو لپاره، لاندې فورمول وکاروئ:

=ИНДЕКС(С2:С10,ПОИСКПОЗ(МИН(D2:D10),D2:D10,0))

د لټون شاخص او سرپنټ

د D په کالم کې د اوسط ارزښت موندلو لپاره او دا ارزښت په C کې ښکاره کړئ:

=ИНДЕКС(С2:С10,ПОИСКПОЗ(СРЗНАЧ(D2:D10),D2:D10,-1))

د دې پورې اړه لري چې ستاسو ډاټا څنګه لیکل کیږي، د MATCH() دریم دلیل یا 1، 0، یا -1 دی:

  • که کالمونه په پورته ترتیب کې ترتیب شوي وي، 1 ترتیب کړئ (نو فورمول به اعظمي ارزښت محاسبه کړي، کوم چې د اوسط ارزښت څخه کم یا مساوي وي)؛
  • که ترتیب ښکته وي، نو -1 (فارمول به لږترلږه ارزښت تولید کړي چې له اوسط څخه لوی یا مساوي وي)؛
  • که د لټون سرې یو ارزښت ولري چې دقیقا د اوسط سره مساوي وي ، نو بیا یې 0 ته تنظیم کړئ. 

 زموږ په مثال کې، نفوس په نزولي ترتیب کې ترتیب شوی، نو موږ -1 واچوو. او پایله یې توکیو ده، ځکه چې د نفوس ارزښت (13,189) د اوسط ارزښت (000) ته نږدې دی.

ولې INDEX او MATCH په Excel کې د VLOOKUP څخه غوره دي

VLOOKUP() هم کولی شي دا ډول محاسبې ترسره کړي، مګر یوازې د صف فورمول په توګه: VLOOKUP د اوسط، MIN او MAX سره.

د انډیکس میچ او ESND/IFERROR

تاسو شاید دمخه یادونه کړې چې که فورمول مطلوب ارزښت ونه موندل شي، دا یوه تېروتنه کوي # N / A. تاسو کولی شئ د معیاري غلطۍ پیغام یو څه نور معلوماتي سره بدل کړئ. د مثال په توګه، په فورمول کې دلیل ترتیب کړئ په XNUMX کې:

=ЕСНД(ИНДЕКС(С2:С10,ПОИСКПОЗ(F1,A2:A10,0)),значение не найдено)

د دې فورمول سره، که تاسو هغه معلومات داخل کړئ چې په جدول کې نه وي، فورمه به تاسو ته مشخص پیغام درکړي.

ولې INDEX او MATCH په Excel کې د VLOOKUP څخه غوره دي

که تاسو غواړئ ټولې خطاګانې ونیسئ، نو پرته له دې په XNUMX کې کارول کیدی شي IFERROR:

=IFERROR(INDEX(C2:C10,MATCH(F1,A2:A10,0)), "یو څه غلط شوي!")

مګر په یاد ولرئ چې پدې لاره کې د غلطیو ماسک کول ښه نظر ندی، ځکه چې معیاري تېروتنې په فورمول کې د سرغړونو راپور ورکوي.

موږ امید لرو چې تاسو د INDEX MATCH() فنکشن کارولو لپاره زموږ لارښود ګټور موندلی.

یو ځواب ورکړئ ووځي