ආසන්නතම අංකය සොයා ගැනීම

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

• පරිමාව අනුව වට්ටම් ගණනය කිරීම.
  
• සැලැස්ම ක්රියාත්මක කිරීම අනුව බෝනස් ප්රමාණය ගණනය කිරීම.
  
• දුර ප්රමාණය අනුව නැව් ගාස්තු ගණනය කිරීම.
• භාණ්ඩ සඳහා සුදුසු බහාලුම් තෝරාගැනීම, ආදිය.

එපමණක් නොව, තත්වය මත පදනම්ව, ඉහළට සහ පහළට වට කිරීම අවශ්‍ය විය හැකිය.

එවැනි ගැටළුවක් විසඳීමට ක්රම කිහිපයක් තිබේ - පැහැදිලි සහ එතරම් පැහැදිලි නැත. අපි ඒවා අනුපිළිවෙලින් බලමු.

ආරම්භ කිරීම සඳහා, තොග සඳහා වට්ටම් ලබා දෙන සැපයුම්කරුවෙකු ගැන සිතමු, සහ වට්ටම් ප්‍රතිශතය මිලදී ගත් භාණ්ඩ ප්‍රමාණය මත රඳා පවතී. උදාහරණයක් ලෙස, කෑලි 5 කට වඩා මිලදී ගැනීමේදී, 2% ක වට්ටමක් ලබා දෙන අතර, කෑලි 20 කින් මිලදී ගැනීමේදී - දැනටමත් 6%, ආදිය.

මිලදී ගත් භාණ්ඩ ප්‍රමාණය ඇතුළත් කිරීමේදී වට්ටම් ප්‍රතිශතය ඉක්මනින් හා අලංකාර ලෙස ගණනය කරන්නේ කෙසේද?

ක්රමය 1: Nested IFs

මාලාවෙන් ක්රමයක් "සිතීමට ඇත්තේ කුමක්ද - ඔබ පැනීමට අවශ්යයි!". කැදලි කාර්යයන් භාවිතා කිරීම IF (නම්) සෛල අගය එක් එක් කාල පරතරයට වැටේදැයි අනුක්‍රමිකව පරීක්ෂා කර අදාළ පරාසය සඳහා වට්ටමක් ප්‍රදර්ශනය කිරීමට. නමුත් මෙම නඩුවේ සූත්රය ඉතා අපහසු විය හැකිය: 

මම හිතන්නේ එවැනි “රාක්ෂ බෝනික්කෙකු” නිදොස් කිරීම හෝ ටික වේලාවකට පසු එයට නව කොන්දේසි කිහිපයක් එක් කිරීමට උත්සාහ කිරීම විනෝදජනක බව පැහැදිලිය.

මීට අමතරව, Microsoft Excel හට IF ශ්‍රිතය සඳහා කැදලි සීමාවක් ඇත - පැරණි අනුවාද වල 7 වතාවක් සහ නව අනුවාද වල 64 වතාවක්. ඔබට තවත් අවශ්ය නම් කුමක් කළ යුතුද?

ක්රමය 2. VLOOKUP විරාම දසුන සමඟ

මෙම ක්රමය වඩාත් සංයුක්ත වේ. වට්ටම් ප්‍රතිශතය ගණනය කිරීමට, පුරාවෘත්ත ශ්‍රිතය භාවිතා කරන්න VPR (VLOOKUP) ආසන්න සෙවුම් ආකාරයෙන්:

එහිදී

• B4 - අපි වට්ටමක් සොයන පළමු ගනුදෙනුවේ භාණ්ඩ ප්‍රමාණයේ වටිනාකම
  
• $G$4:$H$8 - වට්ටම් වගුව වෙත සබැඳියක් - "ශීර්ෂකය" නොමැතිව සහ $ ලකුණ සමඟ ස්ථාවර ලිපිනයන් සමඟ.
  
• 2 - අපට වට්ටම් අගය ලබා ගැනීමට අවශ්‍ය වට්ටම් වගුවේ තීරුවේ සාමාන්‍ය අංකය
• සැබෑ - මෙහි "බල්ලා" වළලනු ලැබේ. අවසාන ශ්‍රිත තර්කය ලෙස නම් VPR සඳහන් කරන්න බොරු කියනවා (අසත්‍ය) හෝ 0, එවිට ශ්‍රිතය සොයනු ඇත දැඩි තරගය ප්‍රමාණ තීරුවේ (සහ අපගේ නඩුවේදී එය #N/A දෝෂයක් ලබා දෙනු ඇත, වට්ටම් වගුවේ 49 අගයක් නොමැති බැවින්). නමුත් ඒ වෙනුවට නම් බොරු කියනවා ලියන්න සැබෑ (සැබෑ) හෝ 1, එවිට කාර්යය හරියටම සොයන්නේ නැත, නමුත් ආසන්නතම කුඩාම අගය සහ අපට අවශ්‍ය වට්ටම් ප්‍රතිශතය ලබා දෙනු ඇත.

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

ඒ අනුව, මෙම ප්රවේශය භාවිතා කළ හැක්කේ ආසන්නතම කුඩාම අගය සොයා ගැනීමට පමණි. ඔබට ආසන්නතම විශාලතම සොයා ගැනීමට අවශ්‍ය නම්, ඔබ වෙනත් ප්‍රවේශයක් භාවිතා කළ යුතුය.

ක්‍රමය 3. INDEX සහ MATCH ශ්‍රිත භාවිතයෙන් ආසන්නතම විශාලතම සොයා ගැනීම

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

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

මෙහිදී, අවසාන තර්කය -1 සමඟ ඇති MATCH ශ්‍රිතය ආසන්නතම විශාලතම අගය සෙවීමේ ක්‍රමයට ක්‍රියා කරන අතර, INDEX ශ්‍රිතය මඟින් අපට අවශ්‍ය ආදර්ශ නාමය යාබද තීරුවෙන් උපුටා ගනී.

ක්‍රමය 4. නව ශ්‍රිතය VIEW (XLOOKUP)

ඔබ සතුව සියලුම යාවත්කාලීන ස්ථාපනය කර ඇති Office 365 අනුවාදයක් තිබේ නම්, VLOOKUP වෙනුවට (VLOOKUP) ඔබට එහි ඇනලොග් භාවිතා කළ හැකිය - VIEW ශ්‍රිතය (XLOOKUP), මම දැනටමත් විස්තරාත්මකව විශ්ලේෂණය කර ඇත:

මෙහි:

• B4 - අප වට්ටමක් සොයන නිෂ්පාදනයේ ප්‍රමාණයේ ආරම්භක අගය
• $G$4:$G$8 - අපි තරඟ සොයන පරාසය
  
• $H$4:$H$8 - ඔබට වට්ටම් ආපසු ලබා දීමට අවශ්‍ය ප්‍රතිඵල පරාසය
• හතරවන තර්කය (-1) නිශ්චිත ගැළපීමක් වෙනුවට අපට අවශ්‍ය ආසන්නතම කුඩාම සංඛ්‍යාව සඳහා සෙවීම ඇතුළත් වේ.
  

මෙම ක්‍රමයේ ඇති වාසි නම්, වට්ටම් වගුව වර්ග කිරීමට අවශ්‍ය නොවන අතර, අවශ්‍ය නම්, ආසන්නතම කුඩාම පමණක් නොව, ආසන්නතම විශාලතම අගයද සෙවීමේ හැකියාවයි. මෙම නඩුවේ අවසාන තර්කය 1 වනු ඇත.

එහෙත්, අවාසනාවකට මෙන්, සෑම කෙනෙකුටම තවමත් මෙම විශේෂාංගය නොමැත - Office 365 හි සතුටුදායක හිමිකරුවන් පමණි.

ක්රමය 5. බල විමසුම

Excel සඳහා බලගතු සහ සම්පූර්ණයෙන්ම නොමිලේ Power Query add-in ඔබ තවමත් හුරුපුරුදු නැතිනම්, ඔබ මෙහි සිටී. ඔබ දැනටමත් හුරුපුරුදු නම්, අපගේ ගැටලුව විසඳීමට එය භාවිතා කිරීමට උත්සාහ කරමු.

අපි මුලින්ම සූදානම් වීමේ කටයුතු කිහිපයක් කරමු:

1. යතුරුපුවරු කෙටිමඟක් භාවිතයෙන් අපගේ මූලාශ්‍ර වගු ගතික (ස්මාර්ට්) බවට පරිවර්තනය කරමු Ctrl+T හෝ කණ්ඩායම නිවස - මේසයක් ලෙස හැඩතල ගැන්වීම (මුල් පිටුව - වගුවක් ලෙස ආකෘතිය).
2. පැහැදිලිකම සඳහා, අපි ඔවුන්ට නම් ලබා දෙමු. විකුණුම් и වට්ටම් ටැබ් ඉදිකිරීම්කරු (නිර්මාණ).
3. බොත්තම භාවිතයෙන් එක් එක් වගු බල විමසුමට පූරණය කරන්න වගුව/පරාසයෙන් ටැබ් දත්ත (දත්ත - වගුව/පරාසයෙන්). Excel හි මෑත සංස්කරණ වලදී, මෙම බොත්තම නැවත නම් කර ඇත කොළ සමග (පත්‍රයෙන්).
   
4. අපගේ උදාහරණයේ මෙන් ("භාණ්ඩ ප්‍රමාණය" සහ "පරිමාණයෙන් ...") වගු වල ප්‍රමාණ සහිත විවිධ තීරු නම් තිබේ නම්, ඒවා බල විමසුමේ නැවත නම් කර ඒවාම නම් කළ යුතුය.
5. ඊට පසු, ඔබට බල විමසුම් සංස්කාරක කවුළුවේ විධානය තේරීමෙන් නැවත Excel වෙත ආපසු යා හැක නිවස - වසන්න සහ පැටවීම - වසන්න සහ පූරණය කරන්න... (නිවස - වසන්න සහ පටවන්න - වසන්න සහ පූරණය කරන්න...) ඉන්පසු විකල්පය සම්බන්ධතාවයක් සාදන්න (සම්බන්ධතාවය පමණක් සාදන්න).

   
   

6. එවිට වඩාත් රසවත් ආරම්භ වේ. ඔබට Power Query පිළිබඳ අත්දැකීම් තිබේ නම්, මම උපකල්පනය කරන්නේ පෙර ක්‍රමයේදී මෙන්, මෙම වගු දෙක සම්බන්ධ කිරීමේ විමසුමක් (ඒකාබද්ධ කිරීම) a la VLOOKUP සමඟ ඒකාබද්ධ කිරීමේ දිශාවට තවදුරටත් චින්තන රේඛාව තිබිය යුතු බවයි. ඇත්ත වශයෙන්ම, අපට එකතු කිරීමේ මාදිලියේ ඒකාබද්ධ කිරීමට අවශ්‍ය වනු ඇත, එය මුලින්ම බැලූ බැල්මට පැහැදිලි නොවේ. Excel ටැබය තුළ තෝරන්න දත්ත - දත්ත ලබා ගන්න - ඉල්ලීම් ඒකාබද්ධ කරන්න - එකතු කරන්න (දත්ත - දත්ත ලබා ගන්න - විමසුම් ඒකාබද්ධ කරන්න - එකතු කරන්න) ඊට පස්සේ අපේ මේස විකුණුම් и වට්ටම් දිස්වන කවුළුවෙහි:

   

7. ක්ලික් කිරීමෙන් පසු OK අපගේ මේස තනි සමස්තයකට ඇලී ඇත - එකිනෙකා යටතේ. මෙම වගු වල භාණ්ඩ ප්‍රමාණය සහිත තීරු එකිනෙක යටට වැටී ඇති බව කරුණාවෙන් සලකන්න. ඔවුන්ට එකම නම ඇත:

   

8. විකුණුම් වගුවේ ඇති පේළිවල මුල් අනුපිළිවෙල ඔබට වැදගත් නම්, පසුව සිදු වූ සියලුම පරිවර්තනයන්ගෙන් පසුව ඔබට එය ප්‍රතිස්ථාපනය කළ හැකි වන පරිදි, විධානය භාවිතා කර අංක සහිත තීරුවක් අපගේ වගුවට එක් කරන්න. තීරුවක් එකතු කිරීම - දර්ශක තීරුව (තීරුව එක් කරන්න - දර්ශක තීරුව). රේඛා අනුපිළිවෙල ඔබට වැදගත් නොවේ නම්, ඔබට මෙම පියවර මඟ හැරිය හැක.
9. දැන්, වගුවේ ශීර්ෂයේ ඇති පතන ලැයිස්තුව භාවිතා කර, තීරුව අනුව එය වර්ග කරන්න ප්රමාණය ආරෝහණ:

   

10. සහ ප්රධාන උපක්රමය: තීරු ශීර්ෂය මත දකුණු-ක්ලික් කරන්න වට්ටමක් කණ්ඩායමක් තෝරන්න පුරවන්න - පහළට (පුරවන්න - පහළට). සමඟ හිස් සෛල ශුන්ය පෙර වට්ටම් අගයන් සමඟ ස්වයංක්‍රීයව පුරවා ඇත:

    

11. තීරුව අනුව වර්ග කිරීම මගින් පේළිවල මුල් අනුපිළිවෙල ප්රතිෂ්ඨාපනය කිරීමට ඉතිරිව ඇත දර්ශකය (ඔබට පසුව එය ආරක්ෂිතව මකා දැමිය හැක) සහ පෙරහනක් සමඟ අනවශ්ය රේඛා ඉවත් කරන්න ශුන්ය තීරුව මගින් ගනුදෙනු කේතය:

    
    

• දත්ත සෙවීමට සහ සෙවීමට VLOOKUP ශ්‍රිතය භාවිතා කිරීම
• VLOOKUP (VLOOKUP) භාවිතා කිරීම සිද්ධි සංවේදී වේ
• XNUMXD VLOOKUP (VLOOKUP)