උසස් VLOOKUP උදාහරණ: බහු නිර්ණායක සෙවීම

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

• බහු නිර්ණායක මගින් Excel හි සොයන්න
• අපි VLOOKUP භාවිතයෙන් 2, 3 වැනි අගයන් උපුටා ගනිමු
• අපේක්ෂිත අගයේ සියලුම පුනරාවර්තන ලබා ගන්න
• දන්නා පේළිය සහ තීරුව අනුව XNUMXD සෙවීම
• එක් සූත්‍රයක බහු VLOOKUP භාවිතා කිරීම
• විවිධ වගු වලින් දත්ත ගතික ආදේශ කිරීම

බහු නිර්ණායක මගින් Excel හි සොයන්න

කාර්යයන් VPR Excel යනු දත්ත සමුදායක නිශ්චිත අගයක් සඳහා සෙවීම් සිදු කිරීම සඳහා සැබවින්ම බලවත් මෙවලමකි. කෙසේ වෙතත්, සැලකිය යුතු සීමාවක් තිබේ - එහි වාක්ය ඛණ්ඩය ඔබට එක් අගයක් පමණක් සෙවීමට ඉඩ සලසයි. ඔබට කොන්දේසි කිහිපයක් අනුව සෙවීමට අවශ්‍ය නම් කුමක් කළ යුතුද? ඔබට පහත විසඳුම සොයාගත හැකිය.

උදාහරණ 1: විවිධ නිර්ණායක 2කින් සොයන්න

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

නිතිපතා කාර්යය 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 තර්කයේ සංයුක්ත අගය අඩංගු වේ බලන්න_ වටිනාකම (සොයන්න_අගය) සහ 4 - තර්කය col_index_num (column_number), එනම් ලබා ගත යුතු දත්ත අඩංගු තීරු අංකය.

උදාහරණ 2: VLOOKUP නිර්ණායක දෙකකින් වෙනත් පත්‍රයක් මත වගුව බැලීම

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

පෙර උදාහරණයේ දී මෙන්, ඔබට ඒකාබද්ධ අගයන් සමඟ බැලීමේ වගුවේ සහායක තීරුවක් අවශ්ය වනු ඇත. මෙම තීරුව සෙවුම් පරාසයේ වම්පස තීරුව විය යුතුය.

එබැවින් සූත්රය සමඟ 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) සහ තරගය (වඩා නිරාවරණය).

අපි VLOOKUP භාවිතයෙන් 2, 3 වැනි අගයන් උපුටා ගනිමු

ඔබ දැනටමත් එය දන්නවා VPR එක් ගැලපෙන අගයක් පමණක් ආපසු ලබා දිය හැක, වඩාත් නිවැරදිව, සොයාගත් පළමු අගය. නමුත් බැලූ අරාව තුළ මෙම අගය කිහිප වතාවක් පුනරාවර්තනය වී, ඔබට ඒවායින් 2 වැනි හෝ 3 වැනි දේ උකහා ගැනීමට අවශ්‍ය නම් කුමක් කළ යුතුද? සියලු වටිනාකම් නම්? ගැටලුව සංකීර්ණ බව පෙනේ, නමුත් විසඳුම පවතී!

වගුවේ එක් තීරුවක පාරිභෝගිකයින්ගේ නම් (පාරිභෝගික නාමය) සහ අනෙක් තීරුවේ ඔවුන් මිලදී ගත් නිෂ්පාදන (නිෂ්පාදන) අඩංගු වේ යැයි සිතමු. දී ඇති පාරිභෝගිකයෙකු විසින් මිලදී ගත් 2 වන, 3 වන සහ 4 වන අයිතම සොයා ගැනීමට උත්සාහ කරමු.

පහසුම ක්රමය වන්නේ තීරුවට පෙර සහායක තීරුවක් එකතු කිරීමයි පාරිභෝගිකයාගේ නම සහ එක් එක් නමේ පුනරාවර්තන අංකය සමඟ පාරිභෝගික නම් වලින් එය පුරවන්න, උදාහරණයක් ලෙස, ජෝන් ඩෝ 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-ඊ පුනරාවර්තනය, වඩාත් සංකීර්ණ සූත්‍රයක් නිර්මාණය කිරීමෙන් ඔබට සහායක තීරුවකින් තොරව එය කළ හැකිය:

=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 - ගැනුම්කරුගේ නම අඩංගු සෛලයක් (එය නොවෙනස්ව පවතී, කරුණාකර සටහන් කරන්න - සබැඳිය නිරපේක්ෂ);
• $ බී $ - තීරුව පාරිභෝගිකයාගේ නම;
• වගුව - ඔබේ මේසය (මෙම ස්ථානය නිතිපතා පරාසයක් ද විය හැකිය);
• $C16 - ඔබේ මේසයේ හෝ පරාසයේ අවසාන කොටුව.

මෙම සූත්‍රය සොයා ගන්නේ දෙවන ගැළපෙන අගය පමණි. ඔබට ඉතිරි පුනරාවර්තන උපුටා ගැනීමට අවශ්‍ය නම්, පෙර විසඳුම භාවිතා කරන්න.

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

අපේක්ෂිත අගයේ සියලුම පුනරාවර්තන ලබා ගන්න

ඉහතින් සඳහන් කළ පරිදි VPR ස්කෑන් කළ පරාසයෙන් සියලුම අනුපිටපත් අගයන් උකහා ගත නොහැක. මෙය සිදු කිරීම සඳහා, ඔබට Excel ශ්‍රිත කිහිපයකින් සමන්විත තරමක් සංකීර්ණ සූත්‍රයක් අවශ්‍ය වේ ඉන්ඩෙක්ස් (INDEX), කුඩා (කුඩා) සහ Row (LINE)

උදාහරණයක් ලෙස, පහත සූත්‍රය 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 (LINE) අතිරේක කවුන්ටරයක් ​​ලෙස ක්රියා කරයි. සූත්‍රය F4:F9 සෛල වලට පිටපත් කර ඇති බැවින්, අපි අංකය අඩු කරමු 3 අගය ලබා ගැනීමට ශ්‍රිත ප්‍රතිඵලයෙන් 1 සෛලය තුළ F4 (පේළිය 4, 3 අඩු කරන්න) ලබා ගැනීමට 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-oh දත්ත අරාවේ කුඩාම අගය. අපගේ නඩුවේදී, කුමන ස්ථානය (කුඩාම සිට) ආපසු යා යුතුද යන්න තීරණය කරනු ලබන්නේ ශ්රිතය මගිනි Row (LINE) (2 කොටස බලන්න). ඉතින්, සෛලයක් සඳහා F4 ක්රියාව කුඩා({array},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) හුදෙක් අරාවක නිශ්චිත කොටුවක අගය ලබා දෙයි සී 2: සී 16. සෛල සඳහා F4 ක්රියාව දර්ශකය($C$2:$C$16) නැවත පැමිණේ ඇපල්සදහා F5 ක්රියාව දර්ශකය($C$2:$C$16) නැවත පැමිණේ රසකැවිලි' සහ යනාදි.

5 කොටස:

IFERROR()

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

අවසාන වශයෙන්, අපි සූත්‍රය ශ්‍රිතය තුළට තබමු IFERROR (IFERROR), ඔබ දෝෂ පණිවිඩය ගැන සතුටු විය නොහැකි නිසා #හිදී (#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)

මිනිස් භාෂාවට පරිවර්තනය කර ඇති මෙම සූත්‍රය යනු:

• අපි "Mar" අක්ෂර සොයමින් සිටිමු - තර්කය බලන්න_ වටිනාකම (සොයන්න_අගය);
• A1 සිට I1 දක්වා සෛල දෙස බැලීම - තර්කය බලන්න_අරාව (සොයන්න_අරාව);
• නිවැරදි ගැළපීම ආපසු ලබා දීම - තර්කය ගැලපෙන_වර්ගය (ගැලපෙන_වර්ගය).

භාවිතා කරමින් 0 තුන්වන තර්කයේ, ඔබ කියනවා functions වඩාත් නිරාවරණය ඔබ සොයන අගයට හරියටම ගැලපෙන පළමු අගය සොයන්න. මෙය වටිනාකමට සමාන වේ අසත්‍යය (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))

නම් කළ පරාස සහ මංසන්ධි ක්‍රියාකරු

ඔබ එම සංකීර්ණ Excel සූත්‍ර සියල්ලටම සම්බන්ධ නොවන්නේ නම්, ඔබ මෙම දෘශ්‍යමය සහ අමතක නොවන ආකාරයට කැමති විය හැක:

1. වගුව තෝරන්න, ටැබ් එක විවෘත කරන්න සමීකරණ (සූත්ර) සහ ක්ලික් කරන්න තේරීමෙන් සාදන්න (තෝරාගැනීමෙන් සාදන්න).
2. පෙට්ටි පරීක්ෂා කරන්න ඉහළ පේළිය (ඉහත රේඛාවේ) සහ වම් තීරුව (වමේ තීරුවේ). Microsoft Excel විසින් ඔබේ පැතුරුම්පතෙහි ඉහළ පේළියේ සහ වම් තීරුවේ ඇති අගයන්ගෙන් පරාසයන්ට නම් පවරනු ඇත. දැන් ඔබට සූත්‍ර නිර්මාණය නොකර කෙලින්ම මෙම නම් භාවිතා කර සෙවිය හැක.
3. ඕනෑම හිස් කොටුවක, ලියන්න =පේළිය_නම තීරු_නම, උදාහරණයක් ලෙස මේ වගේ:

   =ලෙමන් මාර්

   … හෝ අනෙක් අතට:

   =මාර් ලෙමන්

   පේළියේ සහ තීරු නාම අවකාශයකින් වෙන් කළ යුතු බව මතක තබා ගන්න, මෙම අවස්ථාවේ දී මංසන්ධි ක්රියාකරු මෙන් ක්රියා කරයි.

ඔබ නමක් ඇතුළත් කරන විට, Microsoft Excel ඔබ සූත්‍රයක් ඇතුළු කරන විට මෙන් ගැලපෙන නම් ලැයිස්තුවක් සහිත මෙවලම් ඉඟියක් පෙන්වයි.

4. මාධ්ය ඇතුලත් කරන්න සහ ප්රතිඵලය පරීක්ෂා කරන්න

සාමාන්‍යයෙන්, ඔබ තෝරා ගන්නා ඉහත ක්‍රමවලින් කුමක් වුවත්, ද්විමාන සෙවුමක ප්‍රතිඵලය සමාන වනු ඇත:

එක් සූත්‍රයක බහු VLOOKUP භාවිතා කිරීම

ප්‍රධාන වගුවේ සහ බැලීමේ වගුවේ එකම තීරුවක් පොදු නොවන අතර මෙය ඔබව සාමාන්‍ය කාර්යය භාවිතා කිරීමෙන් වළක්වයි VPR. කෙසේ වෙතත්, අප උනන්දු වන තොරතුරු අඩංගු නොවන තවත් වගුවක් ඇත, නමුත් ප්රධාන වගුව සහ බැලීමේ වගුව සමඟ පොදු තීරුවක් ඇත.

අපි පහත උදාහරණය දෙස බලමු. අපට තීරුවක් සහිත ප්‍රධාන වගුවක් තිබේ SKU (නව), ඔබට වෙනත් වගුවකින් අදාළ මිල ගණන් සමඟ තීරුවක් එක් කිරීමට අවශ්‍ය තැන. ඊට අමතරව, අපට සෙවීම් වගු 2 ක් ඇත. පළමු එකෙහි (Lookup table 1) යාවත්කාලීන අංක අඩංගු වේ SKU (නව) සහ නිෂ්පාදන නම්, සහ දෙවන (Lookup table 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 සහ INDIRECT භාවිතයෙන් විවිධ වගු වලින් දත්ත ගතික ආදේශ කිරීම

පළමුව, අපි එකිනෙකා නිවැරදිව තේරුම් ගැනීමට වග බලා ගැනීම සඳහා “විවිධ වගු වලින් දත්ත ගතික ආදේශනය” යන ප්‍රකාශයෙන් අප අදහස් කරන්නේ කුමක්ද යන්න පැහැදිලි කරමු.

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

ඔබට එකම නිෂ්පාදන සහ එකම ආකෘතියෙන් කලාප කිහිපයක් සඳහා විකුණුම් වාර්තා ඇති බව සිතන්න. ඔබට නිශ්චිත කලාපයක් සඳහා විකුණුම් සංඛ්‍යා සොයා ගැනීමට අවශ්‍ය වේ:

ඔබට එවැනි වාර්තා දෙකක් පමණක් තිබේ නම්, ඔබට කාර්යයන් සහිත නින්දිත සරල සූත්‍රයක් භාවිතා කළ හැකිය VPR и IF (IF) සෙවීමට අවශ්‍ය වාර්තාව තේරීමට:

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

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

කොහේද:

• ඩොලර් ඩී ඩොලර් 2 යි නිෂ්පාදනයේ නම අඩංගු සෛලයකි. සූත්‍රය වෙනත් කොටුවලට පිටපත් කිරීමේදී බැලීමේ අගය වෙනස් වීම වැළැක්වීමට අපි මෙහි නිරපේක්ෂ යොමු භාවිත කරන බව සලකන්න.
• $D3 කලාපයේ නම සහිත සෛලයකි. අපි සූත්‍රය එම තීරුවේම වෙනත් කොටුවලට පිටපත් කිරීමට සැලසුම් කර ඇති නිසා අපි නිරපේක්ෂ තීරු යොමුවක් සහ සාපේක්ෂ පේළි යොමුවක් භාවිත කරන්නෙමු.
• FL_සල්es и CA_ විකුණුම් - අනුරූප විකුණුම් වාර්තා අඩංගු වගු (හෝ නම් කළ පරාස) නම්. ඔබට, ඇත්ත වශයෙන්ම, සාමාන්‍ය පත්‍ර නම් සහ සෛල පරාසයේ යොමු කිරීම් භාවිතා කළ හැකිය 'FL Sheet'!$A$3:$B$10, නමුත් නම් කළ පරාසයන් වඩාත් පහසු වේ.

කෙසේ වෙතත්, එවැනි වගු බොහොමයක් ඇති විට, කාර්යය IF හොඳම විසඳුම නොවේ. ඒ වෙනුවට, ඔබට කාර්යය භාවිතා කළ හැකිය වක්ර (INDIRECT) අපේක්ෂිත සෙවුම් පරාසය ආපසු ලබා දීමට.

ඔබ බොහෝ විට දන්නා පරිදි, කාර්යය වක්ර පෙළ තන්තුවකින් ලබා දී ඇති සබැඳියක් ආපසු ලබා දීමට භාවිතා කරයි, එය හරියටම අපට දැන් අවශ්‍ය වේ. එබැවින්, ඉහත සූත්‍රයේ ප්‍රකාශනය ශ්‍රිතය සමඟ නිර්භීතව ප්‍රතිස්ථාපනය කරන්න IF කාර්යය සමඟ සම්බන්ධ කිරීමට වක්ර. මෙන්න සංයෝජනයක් VPR и වක්ර සමඟ හොඳින් ක්‍රියා කරයි:

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

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

කොහේද:

• ඩොලර් ඩී ඩොලර් 2 යි - මෙය නිෂ්පාදනයේ නම සහිත සෛලයකි, නිරපේක්ෂ සබැඳිය හේතුවෙන් එය නොවෙනස්ව පවතී.
• $D3 කලාපයේ නමේ පළමු කොටස අඩංගු කොටුව වේ. අපගේ උදාහරණයේ, මෙය FL.
• _විකුණුම් - සියලු නම් කරන ලද පරාස හෝ වගු වල නමේ පොදු කොටස. සෛල D3 හි අගය සමඟ සංකලනය වූ විට, එය අවශ්ය පරාසයේ සම්පූර්ණ සුදුසුකම් ලත් නම සාදයි. මෙම කාර්යයට අලුතින් පැමිණෙන අය සඳහා විස්තර කිහිපයක් පහත දැක්වේ වක්ර.

INDIRECT සහ VLOOKUP ක්‍රියා කරන ආකාරය

පළමුව, මම ඔබට ශ්‍රිතයේ වාක්‍ය ඛණ්ඩය මතක් කරමි වක්ර (වක්ර):

INDIRECT(ref_text,[a1])

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

පළමු තර්කය සෛල යොමුවක් (A1 හෝ R1C1 විලාසය), පරාසයක නමක් හෝ පෙළ තන්තුවක් විය හැක. දෙවන තර්කය පළමු තර්කයේ අඩංගු සබැඳි විලාසය තීරණය කරයි:

• A1තර්කය නම් සත්‍ය කේතය (සත්ය) හෝ නිශ්චිතව දක්වා නැත;
• ආර් 1 සී 1, නම් Fඒඑස් ඊ (FALSE).

අපගේ නඩුවේදී, සබැඳිය ශෛලිය ඇත A1, එබැවින් ඔබට දෙවන තර්කය අත්හැර පළමු තර්කය කෙරෙහි අවධානය යොමු කළ හැකිය.

එබැවින් අපි අපගේ විකුණුම් වාර්තා වෙත ආපසු යමු. ඔබට මතක නම්, එක් එක් වාර්තාව වෙනම පත්රයක පිහිටා ඇති වෙනම වගුවකි. සූත්‍රය නිවැරදිව ක්‍රියා කිරීම සඳහා, ඔබ ඔබේ වගු (හෝ පරාස) නම් කළ යුතු අතර, සියලුම නම්වලට පොදු කොටසක් තිබිය යුතුය. උදාහරණයක් ලෙස, මේ වගේ: CA_ විකුණුම්, FL_Sales, TX_Sales සහ යනාදි. ඔබට පෙනෙන පරිදි, "_Sales" සියලු නම්වල පවතී.

කාර්යයන් වක්ර D තීරුවේ ඇති අගය සහ “_Sales” පෙළ පෙළ සම්බන්ධ කරයි, එමගින් කියයි VPR සෙවිය යුතු වගුවේ. සෛල D3 "FL" අගය අඩංගු නම්, සූත්රය වගුව සොයනු ඇත FL_Sales, "CA" නම් - වගුවේ CA_ විකුණුම් සහ යනාදි.

කාර්යයන්හි ප්රතිඵලය VPR и වක්ර පහත සඳහන් වනු ඇත:

දත්ත විවිධ එක්සෙල් පොත්වල පිහිටා තිබේ නම්, ඔබ නම් කළ පරාසයට පෙර පොතේ නම එක් කළ යුතුය, උදාහරණයක් ලෙස:

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

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

කාර්යය නම් වක්ර වෙනත් වැඩපොතකට යොමු කරයි, එම වැඩපොත විවෘතව තිබිය යුතුය. එය වසා ඇත්නම්, කාර්යය දෝෂයක් වාර්තා කරයි. #REF! (#SSYL!).