"සෘජු බවේ" විවිධ මට්ටම් සහිත මූලික දත්ත ලියා ඇති ලැයිස්තුවක් ඔබ සතුව ඇතැයි සිතමු - උදාහරණයක් ලෙස, ලිපිනයන් හෝ සමාගම් නම්:
 |  |
එකම නගරයක් හෝ සමාගමක් මෙහි මොට්ලි ප්‍රභේදවල ඇති බව පැහැදිලිව පෙනේ, එය පැහැදිලිවම අනාගතයේදී මෙම වගු සමඟ වැඩ කිරීමේදී ගැටළු රාශියක් ඇති කරයි. ඔබ ටිකක් සිතන්නේ නම්, ඔබට වෙනත් ප්‍රදේශවලින් සමාන කාර්යයන් සඳහා උදාහරණ රාශියක් සොයාගත හැකිය.
දැන් සිතන්න, එවැනි වංක දත්ත ඔබ වෙත නිතිපතා පැමිණෙන බව, එනම් මෙය එක් වරක් "අතින් එය නිවැරදි කරන්න, එය අමතක කරන්න" කථාවක් නොව, නිතිපතා සහ සෛල විශාල සංඛ්යාවක් තුළ ගැටලුවක් වේ.
කුමක් කරන්න ද? "සොයන්න සහ ප්රතිස්ථාපනය කරන්න" කොටුව හරහා හෝ ක්ලික් කිරීමෙන් වංක පෙළ 100500 වතාවක් නිවැරදි එක සමඟ අතින් ප්රතිස්ථාපනය නොකරන්න. Ctrl+H?
එවැනි තත්වයක් තුළ මතකයට එන පළමු දෙය නම් වැරදි සහ නිවැරදි විකල්ප ගැලපීම පිළිබඳ පූර්ව සම්පාදනය කරන ලද විමර්ශන පොතකට අනුව ස්කන්ධ ප්රතිස්ථාපනය කිරීමයි - මේ වගේ:
අවාසනාවකට මෙන්, එවැනි කාර්යයක් පැහැදිලිවම පැතිරීමත් සමඟ, මයික්රොසොෆ්ට් එක්සෙල් එය විසඳීම සඳහා සරල ගොඩනඟන ලද ක්රම නොමැත. ආරම්භ කිරීම සඳහා, VBA හෝ Power Query හි මැක්‍රෝස් ආකාරයෙන් “බර කාලතුවක්කු” සම්බන්ධ නොකර, සූත්‍ර සමඟ මෙය කරන්නේ කෙසේදැයි සොයා බලමු.
නඩුව 1. තොග සම්පූර්ණ ප්රතිස්ථාපනය
සාපේක්ෂව සරල නඩුවකින් පටන් ගනිමු - ඔබට පැරණි වංක පෙළ නව එකක් සමඟ ප්රතිස්ථාපනය කිරීමට අවශ්‍ය තත්වයක්. පූර්ණ.
අපි හිතමු අපිට මේස දෙකක් තියෙනවා කියලා.
පළමුවැන්න නම් - සමාගම්වල මුල් විචිත්‍රවත් නම්. දෙවැන්නෙහි - ලිපි හුවමාරු පොතක්. සමාගමේ නමේ පළමු වගුවේ තීරුවෙන් ඕනෑම වචනයක් අපට හමු වුවහොත් සොයා ගැනීමට, එවිට ඔබට මෙම වංක නම සම්පූර්ණයෙන් නිවැරදි එකක් සමඟ ප්රතිස්ථාපනය කළ යුතුය - තීරුවෙන් ආදේශ කරන්න දෙවන සෙවීම් වගුව.
පහසුව සඳහා:
- යතුරුපුවරු කෙටිමඟක් භාවිතයෙන් වගු දෙකම ගතික ("ස්මාර්ට්") බවට පරිවර්තනය වේ Ctrl+T හෝ කණ්ඩායම ඇතුල් කරන්න - වගුව (ඇතුළු කරන්න - වගුව).
- දිස්වන ටැබය මත ඉදිකිරීම්කරු (නිර්මාණ) පළමු වගුව නම් කර ඇත දත්ත, සහ දෙවන යොමු වගුව - ආදේශක.
සූත්‍රයේ තර්කය පැහැදිලි කිරීම සඳහා, අපි ටිකක් දුරින් යමු.
A2 කොටුවේ පළමු සමාගම උදාහරණයක් ලෙස ගෙන ඉතිරි සමාගම් ගැන තාවකාලිකව අමතක කර, තීරුවෙන් කුමන විකල්පය තීරණය කිරීමට උත්සාහ කරමු. සොයා ගැනීමට එහිදී හමුවෙයි. මෙය සිදු කිරීම සඳහා, පත්රයේ නිදහස් කොටසෙහි ඕනෑම හිස් කොටුවක් තෝරා එහි කාර්යය ඇතුල් කරන්න සොයා ගැනීමට (සොයා ගන්න):
මෙම ශ්රිතය ලබා දී ඇති උපස්ථරය ඇතුළත් වේද යන්න තීරණය කරයි (පළමු තර්කය තීරුවේ ඇති සියලුම අගයන් වේ සොයා ගැනීමට) මූලාශ්‍ර පෙළට (දත්ත වගුවෙන් පළමු සමාගම) සහ පෙළ සොයාගත් අක්ෂරයේ සාමාන්‍ය අංකය ප්‍රතිදානය කළ යුතුය, නැතහොත් උප තන්තුව සොයාගත නොහැකි නම් දෝෂයක්.
මෙහි ඇති උපක්රමය නම්, අපි පළමු තර්කය ලෙස එකක් නොව අගයන් කිහිපයක් සඳහන් කළ බැවින්, මෙම ශ්රිතය ද ප්‍රතිඵලයක් ලෙස එක් අගයක් නොව මූලද්‍රව්‍ය 3 කින් යුත් අරාවක් ලෙස නැවත පැමිණෙනු ඇත. ගතික අරා සඳහා සහය දක්වන Office 365 හි නවතම අනුවාදය ඔබ සතුව නොමැති නම්, මෙම සූත්‍රය ඇතුළත් කර ක්ලික් කිරීමෙන් පසුව ඇතුලත් කරන්න ඔබට මෙම අරාව පත්‍රයේම පෙනෙනු ඇත:
ඔබට Excel හි පෙර අනුවාද තිබේ නම්, ක්ලික් කිරීමෙන් පසුව ඇතුලත් කරන්න අපි ප්‍රතිඵල අරාවෙන් පළමු අගය පමණක් දකිමු, එනම් දෝෂය #VALUE! (#අගය!).
ඔබ බිය විය යුතු නැත 🙂 ඇත්ත වශයෙන්ම, අපගේ සූත්‍රය ක්‍රියාත්මක වන අතර ඔබ සූත්‍ර තීරුවේ ඇතුළත් කළ ශ්රිතය තෝරා යතුර එබුවහොත් ඔබට තවමත් සම්පූර්ණ ප්‍රතිඵල මාලාවම දැක ගත හැක. F9(ඔබන්න අමතක කරන්න එපා එස්.සී.සූත්‍රය වෙත ආපසු යාමට):
ප්‍රතිඵලයක් ලෙස ලැබෙන ප්‍රතිඵල මාලාවෙන් අදහස් වන්නේ මුල් වංචනික සමාගමේ නාමයෙන් බවයි (GK Morozko OAO) තීරුවක ඇති සියලුම අගයන් සොයා ගැනීමට සොයා ගත්තේ දෙවැන්න පමණි (මොරොස්කෝ), සහ පේළියේ 4 වන අක්ෂරයෙන් ආරම්භ වේ.
දැන් අපි අපේ සූත්‍රයට ශ්‍රිතයක් එකතු කරමු දැක්ම(සොයන්න):
මෙම කාර්යයට තර්ක තුනක් ඇත:
- අපේක්ෂිත වටිනාකම - ඔබට ප්රමාණවත් තරම් විශාල සංඛ්යාවක් භාවිතා කළ හැකිය (ප්රධාන දෙය නම් එය මූලාශ්‍ර දත්තවල ඇති ඕනෑම පෙළක දිග ඉක්මවීමයි)
- බැලූ_දෛශිකය - අපි අපේක්ෂිත අගය සොයන පරාසය හෝ අරාව. මෙන්න කලින් හඳුන්වා දුන් කාර්යය සොයා ගැනීමට, එය අරාවක් ආපසු ලබා දෙයි {#VALUE!:4:#VALUE!}
- දෛශිකය_ප්රතිපල – අපේක්ෂිත අගය අදාළ කොටුවේ තිබේ නම්, අපට අගය ආපසු ලබා දීමට අවශ්‍ය පරාසය. තීරුවෙන් නිවැරදි නම් මෙන්න ආදේශ කරන්න අපගේ යොමු වගුව.
මෙහි ඇති ප්රධාන සහ නොපැහැදිලි ලක්ෂණය වන්නේ ශ්‍රිතයයි දැක්ම නිශ්චිත ගැලපීමක් නොමැති නම්, සෑම විටම ආසන්නතම කුඩාම (පෙර) අගය සොයයි. එබැවින්, ඕනෑම විශාල අංකයක් (උදාහරණයක් ලෙස, 9999) අපේක්ෂිත අගය ලෙස සඳහන් කිරීමෙන්, අපි බල කරන්නෙමු දැක්ම {#VALUE!:4:#VALUE!} අරාවේ ළඟම ඇති කුඩාම අංකය (4) ඇති කොටුව සොයා ගෙන ප්‍රතිඵල දෛශිකයෙන් අනුරූප අගය ආපසු දෙන්න, එනම් තීරුවෙන් නිවැරදි සමාගමේ නම ආදේශ කරන්න.
දෙවන සූක්ෂ්මතාවය නම්, තාක්ෂණික වශයෙන්, අපගේ සූත්‍රය අරාව සූත්‍රයකි, මන්ද ශ්රිතය සොයා ගැනීමට ප්‍රතිඵලයක් ලෙස ප්රතිපල එකක් නොව අගයන් තුනක අරාවක් ලබා දෙයි. නමුත් කාර්යයේ සිට දැක්ම කොටුවෙන් පිටත අරා සඳහා සහය දක්වයි, එවිට අපට මෙම සූත්‍රය සම්භාව්‍ය අරා සූත්‍රයක් ලෙස ඇතුළත් කිරීමට අවශ්‍ය නොවේ - යතුරුපුවරු කෙටිමඟක් භාවිතයෙන් Ctrl+මාරුව+ඇතුලත් කරන්න. සරල එකක් ප්රමාණවත් වනු ඇත ඇතුලත් කරන්න.
එච්චරයි. ඔබ තර්කනය ලබා ගනී යැයි බලාපොරොත්තු වෙනවා.
නිමි සූත්‍රය තීරුවේ පළමු කොටුව B2 වෙත මාරු කිරීමට එය ඉතිරිව ඇත ස්ථාවර - සහ අපගේ කාර්යය විසඳා ඇත!
ඇත්ත වශයෙන්ම, සාමාන්‍ය (බුද්ධිමත් නොවන) වගු සමඟ, මෙම සූත්‍රය ද විශිෂ්ට ලෙස ක්‍රියා කරයි (යතුර ගැන අමතක නොකරන්න F4 සහ අදාළ සබැඳි සවි කිරීම):
නඩුව 2. තොග අර්ධ ප්රතිස්ථාපනය
මෙම නඩුව ටිකක් උපක්රමයකි. නැවතත් අපට "ස්මාර්ට්" වගු දෙකක් තිබේ:
නිවැරදි කළ යුතු වංක ලෙස ලියා ඇති ලිපින සහිත පළමු වගුව (මම එය ඇමතුවෙමි දත්ත 2) දෙවන වගුව යොමු පොතකි, ඒ අනුව ඔබට ලිපිනය තුළ ඇති උපස්ථරයක් අර්ධ වශයෙන් ප්රතිස්ථාපනය කළ යුතුය (මම මෙම වගුව ලෙස හැඳින්වුවෙමි. ආදේශන2).
මෙහි ඇති මූලික වෙනස නම් ඔබට මුල් දත්තවල කොටසක් පමණක් ප්රතිස්ථාපනය කිරීමට අවශ්‍ය වීමයි - උදාහරණයක් ලෙස, පළමු ලිපිනයේ වැරදි ඇත “ශාන්ත. පීටර්ස්බර්ග්" දකුණේ “ශාන්ත. පීටර්ස්බර්ග්", ඉතිරි ලිපිනය (සිප් කේතය, වීදිය, නිවස) එලෙසම තබන්න.
නිමි සූත්‍රය මේ ආකාරයෙන් පෙනෙනු ඇත (සංජානනයේ පහසුව සඳහා, මම එය භාවිතා කරන රේඛා කීයකට බෙදුවෙමි alt+ඇතුලත් කරන්න):
මෙහි ප්රධාන කාර්යය සම්මත Excel පෙළ ශ්රිතය මගින් සිදු කෙරේ විෂය (ආදේශක), තර්ක 3 ක් ඇත:
- මූලාශ්‍ර පෙළ - ලිපින තීරුවෙන් පළමු වංක ලිපිනය
- අපි සොයන්නේ කුමක්ද - මෙන්න අපි කාර්යය සමඟ උපක්රමය භාවිතා කරමු දැක්ම (සොයන්න)තීරුවෙන් අගය අදින්න පෙර මාර්ගයෙන් සොයා ගැනීමට, වක්‍ර ලිපිනයක ඛණ්ඩයක් ලෙස ඇතුළත් කර ඇත.
- ප්රතිස්ථාපනය කළ යුතු දේ - ඒ ආකාරයෙන්ම අපි තීරුවෙන් එයට අනුරූප නිවැරදි අගය සොයා ගනිමු ආදේශ කරන්න.
සමඟ මෙම සූත්‍රය ඇතුළු කරන්න Ctrl+මාරුව+ඇතුලත් කරන්න ඇත්ත වශයෙන්ම එය අරා සූත්‍රයක් වුවද මෙහි ද අවශ්‍ය නොවේ.
තවද එය පැහැදිලිව පෙනේ (පෙර පින්තූරයේ #N/A දෝෂ බලන්න) එවැනි සූත්‍රයක, එහි සියලු අලංකාරය සඳහා, අඩුපාඩු කිහිපයක් ඇති බව:
- කාර්යයන් SUBSTITUTE සිද්ධි සංවේදී වේ, එබැවින් අවසාන පේළියේ "Spb" ප්රතිස්ථාපන වගුවේ සොයාගත නොහැකි විය. මෙම ගැටළුව විසඳීම සඳහා, ඔබට කාර්යය භාවිතා කළ හැකිය ZAMENIT (ප්රතිස්ථාපනය), හෝ මූලික වශයෙන් වගු දෙකම එකම ලේඛනයට ගෙන එන්න.
- පෙළ මුලින් නිවැරදි නම් හෝ එහි තිබේ නම් ප්රතිස්ථාපනය කිරීමට කිසිදු කොටසක් නොමැත (අවසාන පේළිය), එවිට අපගේ සූත්‍රය දෝෂයක් ඇති කරයි. ශ්රිතය භාවිතයෙන් දෝෂ වලට බාධා කිරීම සහ ප්රතිස්ථාපනය කිරීමෙන් මෙම මොහොත උදාසීන කළ හැක IFERROR (පෙර):
- මුල් පෙළ අඩංගු නම් නාමාවලියෙන් කොටස් කිහිපයක් එකවර, එවිට අපගේ සූත්‍රය ප්රතිස්ථාපනය කරන්නේ අවසාන එක පමණි (8 වන පේළියේ, ලිගොව්ස්කි «මාවත« වෙත වෙනස් කරන ලදි "pr-t", ඒත් "S-Pb" on “ශාන්ත. පීටර්ස්බර්ග්" තවදුරටත්, මන්ද “එස්-පීබීඩිරෙක්ටරියේ ඉහළයි). අපගේම සූත්‍රය නැවත ධාවනය කිරීමෙන් මෙම ගැටළුව විසඳා ගත හැකිය, නමුත් දැනටමත් තීරුව දිගේ ස්ථාවර:
තැන් වලදී පරිපූර්ණ සහ අපහසු නැත, නමුත් එකම අතින් ආදේශනයට වඩා හොඳයි, හරිද? 🙂
PS
මීළඟ ලිපියෙන් අපි macros සහ Power Query භාවිතයෙන් එවැනි තොග ආදේශනයක් ක්‍රියාත්මක කරන්නේ කෙසේදැයි සොයා බලමු.
- පෙළ ප්රතිස්ථාපනය කිරීමට SUBSTITUTE ශ්රිතය ක්‍රියා කරන ආකාරය
- EXACT කාර්යය භාවිතා කරමින් නිවැරදි පෙළ ගැළපීම් සොයා ගැනීම
- සිද්ධි සංවේදී සෙවීම සහ ආදේශනය (අවස්ථා සංවේදී VLOOKUP)