අන්තර්ගතය
අපි මෑතකදී වඩාත් ප්‍රයෝජනවත් Excel ශ්‍රිතයක් සඳහා ලිපියක් කැප කළෙමු VPR සහ දත්ත සමුදායකින් අවශ්ය තොරතුරු වැඩ පත්‍රිකා කොටුවකට උකහා ගැනීමට එය භාවිතා කළ හැකි ආකාරය පෙන්වා දුන්නේය. කාර්යය සඳහා භාවිත අවස්ථා දෙකක් ඇති බව ද අපි සඳහන් කළෙමු VPR සහ ඒවායින් එකක් පමණක් දත්ත සමුදා විමසුම් සමඟ කටයුතු කරයි. මෙම ලිපියෙන්, ඔබ ශ්‍රිතය භාවිතා කිරීමට තවත් අඩු ප්‍රසිද්ධ ක්රමයක් ඉගෙන ගනු ඇත VPR එක්සෙල් හි.
ඔබ තවමත් මෙය කර නොමැති නම්, කාර්යය පිළිබඳ අවසාන ලිපිය කියවීමට වග බලා ගන්න VPR, මක්නිසාද යත් පහත දැක්වෙන සියලුම තොරතුරු උපකල්පනය කරන්නේ ඔබ පළමු ලිපියේ විස්තර කර ඇති මූලධර්ම සමඟ දැනටමත් හුරුපුරුදු බවයි.
දත්ත සමුදායන් සමඟ වැඩ කරන විට, කාර්යයන් VPR අද්විතීය හඳුනාගැනීමක් සම්මත කර ඇත, එය අපට සොයා ගැනීමට අවශ්ය තොරතුරු හඳුනා ගැනීමට භාවිතා කරයි (උදාහරණයක් ලෙස, නිෂ්පාදන කේතයක් හෝ පාරිභෝගික හඳුනාගැනීමේ අංකයක්). මෙම අද්විතීය කේතය දත්ත ගබඩාවේ තිබිය යුතුය, එසේ නොමැතිනම් VPR දෝෂයක් වාර්තා කරනු ඇත. මෙම ලිපියෙන් අපි මෙම කාර්යය භාවිතා කරන ආකාරය දෙස බලමු VPRහැඳුනුම්පත කිසිසේත්ම දත්ත ගබඩාවේ නොමැති විට. කාර්යය මෙන් VPR ආසන්න මාදිලියට මාරු වී, අපට යමක් සොයා ගැනීමට අවශ්ය වූ විට අපට ලබා දිය යුතු දත්ත තෝරා ගනී. සමහර තත්වයන් තුළ, මෙය හරියටම අවශ්ය වේ.
ජීවිතයෙන් උදාහරණයක්. අපි කාර්යය සකස් කළා
අපි මෙම ලිපිය සැබෑ ජීවිතයේ උදාහරණයකින් නිදර්ශනය කරමු - පුළුල් පරාසයක විකුණුම් ප්‍රමිතික මත පදනම්ව කොමිස් ගණනය කිරීම. අපි ඉතා සරල විකල්පයකින් ආරම්භ කරන්නෙමු, පසුව ගැටලුවට ඇති එකම තාර්කික විසඳුම ශ්‍රිතය භාවිතා කරන තෙක් අපි එය ක්‍රමයෙන් සංකීර්ණ කරන්නෙමු. VPR. අපගේ කල්පිත කාර්යය සඳහා ආරම්භක අවස්ථාව පහත පරිදි වේ: විකුණුම්කරුවෙකු වසරක් තුළ විකුණුම් ඩොලර් 30000 කට වඩා වැඩි නම්, ඔහුගේ කොමිස් මුදල 30% කි. නැතිනම් කොමිස් මුදල 20%යි. අපි එය වගුවක ස්වරූපයෙන් තබමු:
විකිණුම්කරු තම විකුණුම් දත්ත සෛල B1 හි ඇතුළත් කරන අතර, B2 කොටුවේ ඇති සූත්රය විකුණුම්කරුට අපේක්ෂා කළ හැකි නිවැරදි කොමිස් අනුපාතය තීරණය කරයි. අනෙක් අතට, විකිණුම්කරුට ලැබිය යුතු මුළු කොමිස් මුදල ගණනය කිරීම සඳහා ප්‍රතිඵලයක් ලෙස ලැබෙන අනුපාතය B3 සෛලයේ භාවිතා වේ (සරල වශයෙන් සෛල B1 සහ B2 ගුණ කිරීම).
වගුවේ වඩාත්ම සිත්ගන්නා කොටස B2 සෛලයේ අඩංගු වේ - මෙය කොමිස් අනුපාතය තීරණය කිරීමේ සූත්රයයි. මෙම සූත්‍රයේ Excel ශ්‍රිතයක් අඩංගු වේ IF (නම්). මෙම කාර්යය ගැන නොදන්නා පාඨකයන් සඳහා, එය ක්රියා කරන ආකාරය මම පැහැදිලි කරමි:
IF(condition, value if true, value if false)
ЕСЛИ(условие; значение если ИСТИНА; значение если ЛОЖЬ)
තත්වය යනු එක්කෝ අගය ගන්නා ශ්‍රිත තර්කයකි සත්ය කේතය (සත්ය), හෝ අසත්‍යය (FALSE). ඉහත උදාහරණයේ, ප්‍රකාශනය B1
B1 B5 ට වඩා අඩු බව ඇත්තද?
නැතහොත් ඔබට එය වෙනස් ලෙස පැවසිය හැකිය:
වසරේ මුළු විකුණුම් ප්‍රමාණය එළිපත්ත අගයට වඩා අඩු බව ඇත්තද?
අපි මෙම ප්රශ්නයට පිළිතුරු දෙන්නේ නම් ඔව් (TRUE), එවිට ශ්‍රිතය නැවත පැමිණේ සත්ය නම් වටිනාකම (අගය සත්ය නම්). අපගේ නඩුවේදී, මෙය සෛල B6 හි අගය වනු ඇත, එනම් මුළු විකුණුම් සීමාවට වඩා අඩු වන විට කොමිස් අනුපාතය. අපි ප්රශ්නයට පිළිතුරු දෙනවා නම් නැත (FALSE) පසුව නැවත පැමිණේ බොරු නම් වටිනාකම (අගය අසත්‍ය නම්). අපගේ නඩුවේදී, මෙය සෛල B7 හි අගයයි, එනම් මුළු විකුණුම් සීමාව ඉක්මවා ඇති විට කොමිස් අනුපාතයයි.
ඔබට පෙනෙන පරිදි, අපි මුළු විකුණුම් ඩොලර් 20000 ක් ගත්තොත්, අපට සෛල B2 හි 20% ක කොමිස් අනුපාතයක් ලැබේ. අපි ඩොලර් 40000 ක අගයක් ඇතුළත් කළහොත්, කොමිස් අනුපාතය 30% කින් වෙනස් වේ:
අපේ මේසය ක්රියා කරන ආකාරය මෙයයි.
අපි කාර්යය සංකීර්ණ කරමු
අපි දේවල් ටිකක් අමාරු කරමු. අපි තවත් සීමාවක් සකසමු: විකුණුම්කරු ඩොලර් 40000 ට වඩා උපයන්නේ නම්, කොමිස් අනුපාතය 40% දක්වා වැඩිවේ:
සෑම දෙයක්ම සරල සහ පැහැදිලි බව පෙනේ, නමුත් සෛල B2 හි අපගේ සූත්රය සැලකිය යුතු ලෙස වඩාත් සංකීර්ණ වේ. ඔබ සූත්රය දෙස හොඳින් බැලුවහොත්, ශ්‍රිතයේ තුන්වන තර්කය බව ඔබට පෙනෙනු ඇත IF (IF) තවත් සම්පූර්ණ කාර්යයක් බවට පත් විය IF (නම්). මෙම ඉදිකිරීම හැඳින්වෙන්නේ කාර්යයන් එකිනෙක තුළට කැදැල්ල ලෙසිනි. Excel සතුටින් මෙම ඉදිකිරීම් වලට ඉඩ සලසයි, ඒවා පවා වැඩ කරයි, නමුත් ඒවා කියවීමට සහ තේරුම් ගැනීමට වඩා දුෂ්කර ය.
අපි තාක්ෂණික තොරතුරු සොයා නොයන්නෙමු - එය ක්රියා කරන්නේ ඇයි සහ කෙසේද, සහ අපි කැදැලි ශ්‍රිත ලිවීමේ සූක්ෂ්මතාවයන් වෙත නොයන්නෙමු. සියල්ලට පසු, මෙය කාර්යය සඳහා කැප වූ ලිපියකි VPR, Excel සඳහා සම්පූර්ණ මාර්ගෝපදේශයක් නොවේ.
කුමක් වුවත්, සූත්රය වඩාත් සංකීර්ණ වේ! විකුණුම් වලින් ඩොලර් 50 කට වඩා උපයන විකුණුම්කරුවන් සඳහා අපි 50000% ක කොමිස් අනුපාතයක් සඳහා වෙනත් විකල්පයක් හඳුන්වා දෙන්නේ නම් කුමක් කළ යුතුද? අනික කවුරුහරි $60000කට වඩා විකුණුවොත් 60% කොමිස් එකක් දෙනවද?
දැන් B2 කොටුවේ ඇති සූත්රය දෝෂ රහිතව ලියා තිබුණද සම්පූර්ණයෙන්ම කියවිය නොහැකි තත්ත්වයට පත්ව ඇත. මම හිතන්නේ ඔවුන්ගේ ව්‍යාපෘතිවල කූඩු මට්ටම් 4ක් සහිත සූත්‍ර භාවිතා කිරීමට අවශ්ය අය ස්වල්ප දෙනෙක් සිටින බවයි. පහසු මාර්ගයක් තිබිය යුතුද?!
සහ එවැනි ක්රමයක් තිබේ! කාර්යය අපට උපකාර වනු ඇත VPR.
ගැටළුව විසඳීම සඳහා අපි VLOOKUP ශ්‍රිතය යොදන්නෙමු
අපි අපේ මේසයේ සැලසුම ටිකක් වෙනස් කරමු. අපි එකම ක්ෂේත්‍ර සහ දත්ත සියල්ලම තබා ගන්නෙමු, නමුත් ඒවා නව, වඩාත් සංයුක්ත ආකාරයකින් සකසන්න:
මොහොතක් ගත කර නව වගුව සහතික කර ගන්න අනුපාත වගුව පෙර එළිපත්ත වගුවට සමාන දත්ත ඇතුළත් වේ.
ප්රධාන අදහස වන්නේ කාර්යය භාවිතා කිරීමයි VPR වගුව අනුව අපේක්ෂිත ගාස්තු අනුපාතය තීරණය කිරීමට අනුපාත වගුව විකුණුම් පරිමාව මත පදනම්ව. වගුවේ ඇති සීමාවන් පහෙන් එකකට සමාන නොවන මුදලකට විකුණුම්කරුට භාණ්ඩ විකිණිය හැකි බව කරුණාවෙන් සලකන්න. උදාහරණයක් ලෙස, ඔහු ඩොලර් 34988 කට අලෙවි කළ හැකි නමුත් එවැනි මුදලක් නොමැත. අපි බලමු කොහොමද ක්‍රියාකාරිත්වය VPR එවැනි තත්වයක් සමඟ කටයුතු කළ හැකිය.
VLOOKUP ශ්‍රිතයක් ඇතුළත් කිරීම
කොටුව B2 තෝරන්න (අපට අපගේ සූත්රය ඇතුළු කිරීමට අවශ්ය තැන) සහ සොයා ගන්න VLOOKUP (VLOOKUP) Excel Functions පුස්තකාලයේ: සමීකරණ (සූත්‍ර) > කාර්ය පුස්තකාලය (කාර්ය පුස්තකාලය) > බැලීම සහ යොමු කිරීම (යොමු කිරීම් සහ අරා).
සංවාද කොටුවක් දිස්වේ ක්‍රියාකාරී තර්ක (ක්‍රියාකාරී තර්ක). අපි තර්ක වල අගයන් එකින් එක පුරවන්නෙමු බලන්න_අගය (Lookup_value). මෙම උදාහරණයේ දී, මෙය සෛල B1 වෙතින් විකුණුම් මුළු මුදල වේ. කර්සරය ක්ෂේත්‍රයට දමන්න බලන්න_අගය (Lookup_value) සහ කොටුව B1 තෝරන්න.
ඊළඟට, ඔබ විසින් කාර්යයන් නියම කළ යුතුය VPRදත්ත සෙවීමට කොහෙද. අපගේ උදාහරණයේ, මෙය වගුවකි අනුපාත වගුව. කර්සරය ක්ෂේත්‍රයට දමන්න වගු_අරාව (වගුව) සහ සම්පූර්ණ වගුව තෝරන්න අනුපාත වගුවශීර්ෂයන් හැර.
මීළඟට, අපගේ සූත්රය භාවිතයෙන් දත්ත උකහා ගත යුතු තීරුව සඳහන් කළ යුතුය. මේසයේ දෙවන තීරුවේ ඇති කොමිස් අනුපාතය ගැන අපි උනන්දු වෙමු. එබැවින්, තර්කය සඳහා Col_index_num (Column_number) අගය 2 ඇතුලත් කරන්න.
අවසාන වශයෙන්, අපි අවසාන තර්කය හඳුන්වා දෙන්නෙමු - පරාසය_සොයන්න (Interval_lookup).
වැදගත්: ශ්‍රිතය යෙදීමේ ක්‍රම දෙක අතර වෙනස ඇති කරන්නේ මෙම තර්කයේ භාවිතයයි VPR. දත්ත සමුදායන් සමඟ වැඩ කරන විට, තර්කය පරාසය_සොයන්න (range_lookup) සැමවිටම අගයක් තිබිය යුතුය අසත්‍යය (FALSE) හරියටම ගැළපීමක් සෙවීමට. අපගේ කාර්යය භාවිතා කිරීමේදී VPR, අපි මෙම ක්ෂේත්‍රය හිස්ව තැබිය යුතුය, නැතහොත් අගයක් ඇතුළත් කළ යුතුය සත්ය කේතය (සැබෑ). මෙම විකල්පය නිවැරදිව තෝරා ගැනීම අතිශයින්ම වැදගත්ය.
එය වඩාත් පැහැදිලි කිරීම සඳහා, අපි හඳුන්වා දෙන්නෙමු සත්ය කේතය (සත්ය) ක්ෂේත්රයේ පරාසය_සොයන්න (Interval_lookup). කෙසේ වෙතත්, ඔබ ක්ෂේත්‍රය හිස්ව තැබුවහොත්, මෙය දෝෂයක් නොවනු ඇත සත්ය කේතය එහි පෙරනිමි අගය:
අපි සියලු පරාමිතීන් පුරවා ඇත. දැන් අපි ඔබන්න OK, සහ Excel විසින් ශ්‍රිතයක් සමඟින් අපට සූත්‍රයක් නිර්මාණය කරයි VPR.
අපි මුළු විකුණුම් මුදල සඳහා විවිධ අගයන් කිහිපයක් සමඟ අත්හදා බැලුවහොත්, සූත්රය නිවැරදිව ක්‍රියාත්මක වන බවට අපි සහතික වෙමු.
නිගමනය
කාර්යය විට VPR දත්ත සමුදායන්, තර්ක සමඟ ක්රියා කරයි පරාසය_සොයන්න (range_lookup) පිළිගත යුතුය අසත්‍යය (FALSE). සහ ලෙස ඇතුළත් කළ අගය බලන්න_අගය (Lookup_value) දත්ත ගබඩාවේ තිබිය යුතුය. වෙනත් වචන වලින් කිවහොත්, එය නිශ්චිත ගැලපීමක් සොයමින් සිටී.
අපි මෙම ලිපියෙන් බැලූ උදාහරණයේ, නිශ්චිත ගැලපීමක් ලබා ගැනීමට අවශ්ය නොවේ. මෙම කාර්යය විට නඩුව වේ VPR අපේක්ෂිත ප්රතිඵලය ලබා දීමට ආසන්න මාදිලිය වෙත මාරු විය යුතුය.
උදාහරණයක් වශයෙන්: ඩොලර් 34988 ක විකුණුම් පරිමාවක් සහිත විකුණුම්කරුවෙකු සඳහා කොමිස් ගණනය කිරීමේදී කුමන අනුපාතයක් භාවිතා කළ යුතුද යන්න තීරණය කිරීමට අපට අවශ්‍යය. කාර්යය VPR අපට 30% ක අගයක් ලබා දෙයි, එය සම්පූර්ණයෙන්ම නිවැරදි ය. නමුත් සූත්රය 30% හෝ 20% නොව හරියටම 40% අඩංගු පේළිය තෝරා ගත්තේ ඇයි? ආසන්න සෙවීම යන්නෙන් අදහස් කරන්නේ කුමක්ද? අපි පැහැදිලිව කියමු.
තර්කය ඇති වූ විට පරාසය_සොයන්න (interval_lookup) අගයක් ඇත සත්ය කේතය (සත්ය) හෝ අතහැර දැමූ, ශ්‍රිතය VPR පළමු තීරුව හරහා පුනරාවර්තනය වන අතර බැලීමේ අගය නොඉක්මවන විශාලතම අගය තෝරා ගනී.
වැදගත් කරුණ: මෙම යෝජනා ක්‍රමය ක්‍රියාත්මක වීමට නම්, වගුවේ පළමු තීරුව ආරෝහණ අනුපිළිවෙලට වර්ග කළ යුතුය.