ඇයි INDEX සහ MATCH Excel වල VLOOKUP වලට වඩා හොඳ

VLOOKUP හි මූලික කාර්යයන් භාවිතා කරන්නේ කෙසේදැයි අපි මීට පෙර ආරම්භකයින්ට පැහැදිලි කර ඇත්තෙමු (ඉංග්‍රීසි VLOOKUP, කෙටි යෙදුම "සිරස් බැලීමේ ශ්‍රිතය" යන්නයි). පළපුරුදු පරිශීලකයින්ට වඩාත් සංකීර්ණ සූත්‍ර කිහිපයක් පෙන්වා ඇත.

තවද මෙම ලිපියෙන් අපි සිරස් සෙවුම සමඟ වැඩ කිරීමේ තවත් ක්රමයක් පිළිබඳ තොරතුරු ලබා දීමට උත්සාහ කරමු.

ඔබ පුදුම විය හැකිය: "මෙය අවශ්ය වන්නේ ඇයි?". හැකි සියලුම සෙවුම් ක්‍රම පෙන්වීම සඳහා මෙය අවශ්‍ය වේ. මීට අමතරව, බොහෝ VLOOKUP සීමා කිරීම් බොහෝ විට අපේක්ෂිත ප්රතිඵලය ලබා ගැනීම වළක්වයි. මේ සම්බන්ධයෙන්, INDEX( ) MATCH( ) වඩා ක්‍රියාකාරී සහ විවිධ වන අතර ඒවාට සීමා කිරීම් ද අඩුය.

මූලික දර්ශක ගැලපීම

මෙම මාර්ගෝපදේශයේ අරමුණ මෙම විශේෂාංගය කෙතරම් හොඳද යන්න පෙන්වීමට බැවින්, අපි එහි ක්රියාකාරිත්වයේ මූලධර්ම පිළිබඳ මූලික තොරතුරු දෙස බලමු. අපි උදාහරණ පෙන්වන්නෙමු, එය VLOOKUP () ට වඩා හොඳ වන්නේ මන්දැයි සලකා බලන්න.

INDEX කාර්යය වාක්‍ය ඛණ්ඩය සහ භාවිතය

මෙම ශ්‍රිතය තීරුව හෝ රේඛා අංකය මත පදනම්ව නිශ්චිත සෙවුම් ප්‍රදේශ අතර අපේක්ෂිත අගය සොයා ගැනීමට උපකාරී වේ. වාක්‍ය ඛණ්ඩය:

=INDEX(අරාව, පේළි අංකය, තීරු අංකය):

  • අරාව - සෙවුම සිදු කරනු ලබන ප්රදේශය;
  • රේඛා අංකය - නිශ්චිත අරාව තුළ සෙවිය යුතු රේඛාවේ අංකය. පේළි අංකය නොදන්නා නම්, තීරු අංකය සඳහන් කළ යුතුය;
  • තීරු අංකය - නිශ්චිත අරාව තුළ සොයා ගත යුතු තීරු අංකය. අගය නොදන්නා නම්, රේඛා අංකයක් අවශ්‍ය වේ.

සරල සූත්‍රයක උදාහරණයක්:

=දර්ශකය(A1:S10,2,3)

ශ්‍රිතය A1 සිට C10 දක්වා පරාසය තුළ සොයනු ඇත. ඉලක්කම් මඟින් අපේක්ෂිත අගය පෙන්විය යුතු පේළිය (2) සහ තීරුව (3) පෙන්වයි. ප්රතිඵලය වනුයේ C2 සෛලයයි.

හරි සරලයි නේද? නමුත් ඔබ සත්‍ය ලේඛන සමඟ වැඩ කරන විට, තීරු අංක හෝ කොටු සම්බන්ධ තොරතුරු ඔබට තිබිය නොහැක. ඒකට තමයි MATCH() function එක තියෙන්නේ.

MATCH Function Syntax සහ භාවිතය

MATCH() ශ්‍රිතය අපේක්ෂිත අගය සොයන අතර නිශ්චිත සෙවුම් ප්‍රදේශයේ එහි ආසන්න අංකය පෙන්වයි.

searchpos() වාක්‍ය ඛණ්ඩය මේ ආකාරයට පෙනේ:

=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() සූත්‍රයේ ඇති අතර මෙම පේළියෙන් අගයක් මුද්‍රණය කිරීමට ශ්‍රිතයට කියයි.

එබැවින් ඉහත සූත්රය සම්මත සූත්රය බවට පත් වේ දර්ශකය(C2:C10,3). සූත්‍රය C2 සිට C10 දක්වා සෛල සොයන අතර මෙම පරාසයේ තුන්වන කොටුවෙන් දත්ත ලබා දෙයි, එනම් C4, ගණන් කිරීම ආරම්භ වන්නේ දෙවන පේළියෙන් බැවිනි.

සූත්‍රයේ නගරයේ නම නියම කිරීමට අවශ්‍ය නැද්ද? ඉන්පසු එය ඕනෑම කොටුවක ලියන්න, F1 කියන්න, සහ MATCH() සූත්‍රයේ එය යොමුවක් ලෙස භාවිතා කරන්න. තවද ඔබ ගතික සෙවුම් සූත්‍රයකින් අවසන් වේ:

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

ඇයි INDEX සහ MATCH Excel වල VLOOKUP වලට වඩා හොඳ

වැදගත්! පේළි ගණන අරාව INDEX() පේළි ගණනට සමාන විය යුතුය array ලෙස සැලකේ MATCH(), එසේ නොමැතිනම් ඔබට වැරදි ප්‍රතිඵලයක් ලැබෙනු ඇත.

විනාඩියක් ඉන්න, ඇයි VLOOKUP() සූත්‍රය භාවිතා නොකරන්නේ?

=VLOOKUP(F1, A2:C10, 3, False)

 INDEX MATCH හි මෙම සියලු සංකීර්ණතා හඳුනා ගැනීමට උත්සාහ කරමින් කාලය නාස්ති කිරීමේ තේරුම කුමක්ද?

මෙම අවස්ථාවේදී, කුමන කාර්යය භාවිතා කළ යුතුද යන්න ගැටළුවක් නොවේ. INDEX() සහ MATCH() ශ්‍රිතයන් එකට ක්‍රියා කරන ආකාරය තේරුම් ගැනීමට මෙය උදාහරණයක් පමණි. VLOOKUP බල රහිත අවස්ථාවන්හිදී මෙම කාර්යයන් කළ හැකි දේ වෙනත් උදාහරණ පෙන්වයි. 

INDEX MATCH හෝ VLOOKUP

කුමන සෙවුම් සූත්‍රය භාවිතා කළ යුතුද යන්න තීරණය කිරීමේදී, VLOOKUP ට වඩා INDEX() සහ MATCH() ඉතා උසස් බව බොහෝ දෙනා එකඟ වෙති. කෙසේ වෙතත්, බොහෝ අය තවමත් 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() සහ SUM() වැනි සූත්‍ර විශාල ප්‍රමාණයක් අඩංගු වන්නේ නම් VLOOKUP() හි කාර්ය සාධන බලපෑම විශේෂයෙන් කැපී පෙනෙනු ඇත. අරාවක එක් එක් අගය විග්‍රහ කිරීමට VLOOKUP() ශ්‍රිතවල වෙනම චෙක්පත් අවශ්‍ය වේ. එබැවින් Excel විසින් විශාල තොරතුරු ප්රමාණයක් සැකසීමට සිදු වන අතර, මෙම කාර්යය සැලකිය යුතු ලෙස මන්දගාමී වේ.

සූත්‍ර උදාහරණ 

මෙම කාර්යයන්හි ප්‍රයෝජනය අපි දැනටමත් සොයාගෙන ඇත, එබැවින් අපට වඩාත් සිත්ගන්නා කොටස වෙත යා හැකිය: දැනුම ප්‍රායෝගිකව යෙදීම.

දකුණේ සිට වමට සෙවීමට සූත්‍රය

දැනටමත් සඳහන් කර ඇති පරිදි, 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() සඳහා ආදේශකයක් ලෙස පෙර නිශ්චිත පේළි පරාසයකින් අගයන් ලබා දීමට භාවිතා කර ඇත. නමුත් ඔබට matrix හෝ ද්වි-පාර්ශ්වික සෙවුමක් කිරීමට අවශ්ය නම් කුමක් කළ යුතුද?

එය සංකීර්ණ බවක් පෙනේ, නමුත් එවැනි ගණනය කිරීම් සඳහා සූත්‍රය සම්මත INDEX() MATCH() සූත්‍රයට සමාන වේ, එකම වෙනසක් ඇත: MATCH() සූත්‍රය දෙවරක් භාවිතා කළ යුතුය. පේළි අංකය ලබා ගැනීමට පළමු වරට, සහ දෙවන වරට තීරු අංකය ලබා ගැනීමට:

=INDEX(array, MATCH(සිරස් සෙවුම් අගය, සෙවුම් තීරුව, 0), MATCH(තිරස් සෙවුම් අගය, සෙවුම් පේළිය, 0))

අපි පහත වගුව දෙස බලා සූත්‍රයක් සෑදීමට උත්සාහ කරමු INDEX() EXPRESS() EXPRESS() තෝරාගත් වසරක් සඳහා නිශ්චිත රටක ජනවිකාස ප්‍රදර්ශනය කිරීම සඳහා.

ඉලක්කගත රට සෛල G1 (සිරස් බැල්ම) හි වන අතර ඉලක්කගත වර්ෂය සෛල G2 (තිරස් බැල්ම) හි ඇත. සූත්රය මේ ආකාරයෙන් පෙනෙනු ඇත:

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

ඇයි INDEX සහ MATCH Excel වල VLOOKUP වලට වඩා හොඳ

මෙම සූත්‍රය ක්‍රියාත්මක වන ආකාරය

වෙනත් ඕනෑම සංකීර්ණ සූත්‍ර මෙන්, ඒවා තනි සමීකරණවලට කැඩීමෙන් තේරුම් ගැනීමට පහසු වේ. එවිට ඔබට එක් එක් කාර්යය කරන්නේ කුමක්ද යන්න තේරුම් ගත හැකිය:

  • MATCH(G1,A2:A11,0) – A1:A2 පරාසයේ අගයක් (G11) සොයන අතර මෙම අගයේ අංකය පෙන්වයි, අපගේ නඩුවේදී එය 2 වේ;
  • සොයන්න(G2,B1:D1,0) – B2:D1 පරාසයේ අගයක් (G1) සොයයි. මෙම අවස්ථාවේ දී, ප්රතිඵලය 3 විය.

සොයාගත් පේළි සහ තීරු අංක INDEX() සූත්‍රයේ අනුරූප අගයට යවනු ලැබේ:

=දර්ශකය(B2:D11,2,3)

එහි ප්‍රතිඵලයක් වශයෙන්, B2:D3 පරාසයේ පේළි 2ක් සහ තීරු 11ක් ඡේදනය වන කොටුවක ඇති අගයක් අපට ඇත. සූත්‍රය D3 සෛලයේ ඇති අපේක්ෂිත අගය පෙන්වයි.

INDEX සහ MATCH සමඟ කොන්දේසි කිහිපයක් අනුව සොයන්න

ඔබ VLOOKUP() වෙත අපගේ මාර්ගෝපදේශය කියවා ඇත්නම්, ඔබ බොහෝ සෙවුම් සූත්‍ර කිහිපයක් උත්සාහ කර ඇත. නමුත් මෙම සෙවුම් ක්රමයට එක් සැලකිය යුතු සීමාවක් ඇත - සහායක තීරුවක් එකතු කිරීමේ අවශ්යතාව.

නමුත් ශුභාරංචිය එයයි INDEX() සහ MATCH() සමඟින් ඔබට ඔබේ වැඩ පත්‍රිකාව සංස්කරණය කිරීම හෝ වෙනස් කිරීමකින් තොරව බහුවිධ කොන්දේසි සෙවිය හැක.

INDEX() MATCH() සඳහා වන සාමාන්‍ය බහු-කොන්දේසි සෙවුම් සූත්‍රය මෙන්න:

{=ИНДЕКС(диапазон поиска, ПОИСКПОЗ(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 + මාරු + ඇතුළු කරන්න - උදාහරණයේ පෙන්වා ඇති පරිදි Excel ස්වයංක්‍රීයව රැලි සහිත වරහන් සමඟ සූත්‍රය වසා දමයි:

ඇයි INDEX සහ MATCH Excel වල VLOOKUP වලට වඩා හොඳ

ඔබගේ කාර්යය සඳහා අරා සූත්‍රයක් භාවිතා කිරීමට ඔබට අවශ්‍ය නැතිනම්, සූත්‍රයට තවත් INDEX() එකක් එකතු කර ENTER ඔබන්න, එය උදාහරණයේ ඇති ආකාරයට පෙනෙනු ඇත:

ඇයි INDEX සහ MATCH Excel වල VLOOKUP වලට වඩා හොඳ

මෙම සූත්‍ර ක්‍රියා කරන ආකාරය

මෙම සූත්‍රය සම්මත INDEX() MATCH() සූත්‍රය මෙන් ක්‍රියා කරයි. බහු කොන්දේසි සෙවීමට, ඔබ සරලව නිවැරදි සහ වැරදි තනි කොන්දේසි නියෝජනය කරන බහු අසත්‍ය සහ සත්‍ය තත්වයන් නිර්මාණය කරයි. ඉන්පසු මෙම කොන්දේසි අරාවේ සියලුම අනුරූප අංග සඳහා අදාළ වේ. සූත්‍රය අසත්‍ය සහ සත්‍ය විස්තාරක 0 සහ 1 බවට පරිවර්තනය කරයි, සහ 1 යනු තන්තුවෙහි ඇති ගැළපෙන අගයන් වන අරාවක් ප්‍රතිදානය කරයි. MATCH() විසින් 1 ට ගැළපෙන පළමු අගය සොයාගෙන එය INDEX() සූත්‍රය වෙත ලබා දෙනු ඇත. එය අනෙක් අතට, අපේක්ෂිත තීරුවෙන් නිශ්චිත රේඛාවේ දැනටමත් අපේක්ෂිත අගය ආපසු ලබා දෙනු ඇත.

අරාවක් නොමැති සූත්‍රයක් INDEX() හට ඒවා තනිවම හැසිරවීමට ඇති හැකියාව මත රඳා පවතී. සූත්‍රයේ දෙවන INDEX() අසත්‍ය (0) ට ගැලපේ, එබැවින් එය එම අගයන් සහිත සම්පූර්ණ අරාව MATCH() සූත්‍රය වෙත යවයි. 

මෙය මෙම සූත්‍රය පිටුපස ඇති තර්කය පිළිබඳ තරමක් දීර්ඝ පැහැදිලි කිරීමකි. වැඩි විස්තර සඳහා ලිපිය කියවන්න "බහු කොන්දේසි සහිත INDEX MATCH".

INDEX සහ MATCH හි AVERAGE, MAX සහ MIN

සාමාන්‍ය, උපරිම සහ අවම සොයා ගැනීම සඳහා Excel හට තමන්ගේම විශේෂ කාර්යයන් ඇත. නමුත් ඔබට එම අගයන් හා සම්බන්ධ සෛලයෙන් දත්ත ලබා ගැනීමට අවශ්‍ය නම් කුමක් කළ යුතුද? මේ අවස්ථාවේ දී AVERAGE, MAX සහ MIN INDEX සහ MATCH සමඟ ඒකාබද්ධව භාවිතා කළ යුතුය.

INDEX MATCH සහ MAX

D තීරුවේ විශාලතම අගය සොයා ගැනීමට සහ එය C තීරුවේ පෙන්වීමට, සූත්‍රය භාවිතා කරන්න: 

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

INDEX MATCH සහ 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 සමඟ AVERAGE, MIN සහ MAX.

INDEX MATCH සහ ESND/IFERROR

සූත්‍රයට අපේක්ෂිත අගය සොයාගත නොහැකි නම්, එය දෝෂයක් ඇති කරන බව ඔබ දැනටමත් දැක ඇති # එන් / ඒ. ඔබට සම්මත දෝෂ පණිවිඩය වඩාත් තොරතුරු සහිත යමක් සමඟ ප්‍රතිස්ථාපනය කළ හැකිය. උදාහරණයක් ලෙස, සූත්‍රයේ තර්කය සකසන්න XNUMX වෙනිදා:

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

මෙම සූත්‍රය සමඟ, ඔබ වගුවේ නොමැති දත්ත ඇතුළත් කළහොත්, පෝරමය මඟින් ඔබට නිශ්චිත පණිවිඩය ලබා දෙනු ඇත.

ඇයි INDEX සහ MATCH Excel වල VLOOKUP වලට වඩා හොඳ

ඔබට සියලු දෝෂ අල්ලා ගැනීමට අවශ්‍ය නම්, හැර XNUMX වෙනිදා භාවිතා කළ හැකිය IFERROR:

=IFERROR(දර්ශකය(C2:C10,MATCH(F1,A2:A10,0)), "යමක් වැරදී ඇත!")

නමුත් මේ ආකාරයෙන් දෝෂ ආවරණය කිරීම හොඳ අදහසක් නොවන බව මතක තබා ගන්න, මන්ද සම්මත දෝෂ සූත්‍රයේ උල්ලංඝනයන් වාර්තා කරයි.

INDEX MATCH() ශ්‍රිතය භාවිතා කිරීම සඳහා අපගේ මාර්ගෝපදේශය ඔබට ප්‍රයෝජනවත් විය.

ඔබමයි