අන්තර්ගතය
ඔබට සෛල කිහිපයකින් පෙළ එකකට ඉක්මනින් ඇලවිය හැකි ආකාරය ගැන මම දැනටමත් ලියා ඇති අතර, අනෙක් අතට, දිගු පෙළ පෙළක් සංරචක වලට විග්‍රහ කළ හැකිය. දැන් අපි සමීප නමුත් තරමක් සංකීර්ණ කාර්යයක් දෙස බලමු - නිශ්චිත කොන්දේසියක් සපුරා ඇති විට සෛල කිහිපයකින් පෙළ ඇලවිය යුතු ආකාරය.
එක් සමාගමේ නමක් එහි සේවකයින්ගේ විවිධ ඊමේල් කිහිපයකට අනුරූප විය හැකි පාරිභෝගිකයින්ගේ දත්ත සමුදායක් අප සතුව ඇති බව කියමු. අපගේ කර්තව්‍යය වන්නේ සමාගම් නම්වලින් සියලුම ලිපින එකතු කර ඒවා (කොමාවෙන් හෝ අර්ධ කොමා වලින් වෙන් කර) ඒකාබද්ධ කිරීම සඳහා, උදාහරණයක් ලෙස, පාරිභෝගිකයින් සඳහා තැපැල් ලැයිස්තුවක් සෑදීම, එනම් ප්‍රතිදානය ලබා ගැනීම:
වෙනත් වචන වලින් කිවහොත්, අපට කොන්දේසිය අනුව පෙළ ඇලවිය හැකි (සබැඳිය) මෙවලමක් අවශ්ය වේ - ශ්‍රිතයේ ප්‍රතිසමයක් SUMMESLI (SUMIF), නමුත් පෙළ සඳහා.
ක්රමය 0. සූත්රය
ඉතා අලංකාර නොවේ, නමුත් පහසුම ක්රමය. ඔබට සරල සූත්‍රයක් ලිවිය හැකි අතර එය ඊළඟ පේළියේ සමාගම පෙරට වඩා වෙනස් දැයි පරීක්ෂා කරයි. එය වෙනස් නොවේ නම්, ඊළඟ ලිපිනය කොමාවකින් වෙන් කරන්න. එය වෙනස් නම්, අපි නැවත ආරම්භ කරමින් සමුච්චිත "නැවත සකසමු":
මෙම ප්රවේශයේ අවාසි පැහැදිලිය: ලබාගත් අතිරේක තීරුවේ සියලුම සෛල වලින්, අපට අවශ්ය වන්නේ එක් එක් සමාගම (කහ) සඳහා අවසාන ඒවා පමණි. ලැයිස්තුව විශාල නම්, ඒවා ඉක්මනින් තෝරා ගැනීම සඳහා, ඔබට ශ්රිතය භාවිතයෙන් තවත් තීරුවක් එක් කිරීමට සිදුවේ DLSTR (LEN), සමුච්චිත නූල් වල දිග පරීක්ෂා කිරීම:
දැන් ඔබට ඒවා පෙරීමට සහ වැඩිදුර භාවිතය සඳහා අවශ්ය ලිපින ඇලවීම පිටපත් කළ හැකිය.
ක්රමය 1. එක් කොන්දේසියක් මගින් ඇලවීමේ macrofunction
මුල් ලැයිස්තුව සමාගම විසින් වර්ග කර නොමැති නම්, ඉහත සරල සූත්රය ක්රියා නොකරයි, නමුත් ඔබට VBA හි කුඩා අභිරුචි ශ්‍රිතයක් සමඟ පහසුවෙන් ගමන් කළ හැකිය. යතුරුපුවරු කෙටිමඟක් එබීමෙන් දෘශ්ය මූලික සංස්කාරකය විවෘත කරන්න Alt + F11 හෝ බොත්තම භාවිතයෙන් දෘශ්ය මූලික ටැබ් සංවර්ධක (සංවර්ධක). විවෘත වන කවුළුව තුළ, මෙනුව හරහා නව හිස් මොඩියුලයක් ඇතුල් කරන්න ඇතුල් කරන්න - මොඩියුලය සහ අපගේ කාර්යයේ පෙළ එහි පිටපත් කරන්න:
Function MergeIf(TextRange as Range, SearchRange as Range, Condition as Range) Dim Delimeter String ලෙස, i As Long Delimeter = ", " gluings එකිනෙක සමාන නොවේ - අපි දෝෂයකින් පිටවෙමු නම් SearchRange.Count <> TextRange.Count එවිට MergeIf = CVErr(xlErrRef) Exit Function End 'සියලු සෛල හරහා ගොස්, තත්ත්වය පරීක්ෂා කර, SearchRange සඳහා i = 1 සඳහා OutText විචල්‍යයේ පෙළ එකතු කරන්න. Cells.Count නම් SearchRange.Cells(i) Condition කැමති නම් OutText = OutText & TextRange.Cells(i) & Delimeter ඊළඟට i 'අවසාන පරිසීමකය නොමැතිව ප්‍රතිඵල පෙන්වන්න MergeIf = වම්(OutText, Len(OutText) - Len(Delimeter)) කාර්යය
ඔබ දැන් මයික්‍රොසොෆ්ට් එක්සෙල් වෙත ආපසු යන්නේ නම්, ශ්‍රිත ලැයිස්තුවේ (බොත්තම fx සූත්ර තීරුවේ හෝ පටිත්තෙහි සූත්ර - ඇතුළත් කිරීමේ කාර්යය) එය අපගේ කාර්යය සොයා ගැනීමට හැකි වනු ඇත MergeIf කාණ්ඩයේ පරිශීලක අර්ථ දක්වා ඇත (පරිශීලක අර්ථ දක්වා ඇත). කාර්යය සඳහා තර්ක පහත පරිදි වේ:
ක්රමය 2. නොගැලපෙන තත්ත්‍වයෙන් පෙළ සංයුක්ත කරන්න
අපි අපේ මැක්‍රෝවේ 13 වැනි පේළියේ පළමු අක්ෂරය ප්‍රතිස්ථාපනය කළහොත් = ආසන්න තරඟ ක්රියාකරු වෙත වැනි, එවිට තෝරා ගැනීමේ නිර්ණායකය සමඟ ආරම්භක දත්තවල නොගැලපීමකින් ඇලවීම සිදු කළ හැකිය. උදාහරණයක් ලෙස, සමාගමේ නම විවිධ ප්‍රභේදවලින් ලිවිය හැකි නම්, අපට ඒවා සියල්ලම එක් කාර්යයකින් පරීක්ෂා කර එකතු කළ හැකිය:
සම්මත වයිල්ඩ්කාඩ් සඳහා සහය දක්වයි:
- තරු ලකුණ (*) - ඕනෑම අක්ෂර ගණනක් (ඔවුන් නොමැති වීම ඇතුළුව) දක්වයි
- ප්‍රශ්න ලකුණ (?) - ඕනෑම තනි චරිතයක් නියෝජනය කරයි
- රාත්තල් ලකුණ (#) - ඕනෑම එක් ඉලක්කමක් සඳහා (0-9)
පෙරනිමියෙන්, Like ක්රියාකරු සිද්ධි සංවේදී වේ, එනම්, උදාහරණයක් ලෙස, "Orion" සහ "orion" විවිධ සමාගම් ලෙස තේරුම් ගනී. නඩුව නොසලකා හැරීම සඳහා, ඔබට දෘශ්ය මූලික සංස්කාරකයේ මොඩියුලයේ ආරම්භයේදීම රේඛාව එක් කළ හැකිය විකල්පය පෙළ සංසන්දනය කරන්න, එය සංවේදී නොවන ලෙසට කැමැත්ත මාරු කරනු ඇත.
මේ ආකාරයෙන්, ඔබට කොන්දේසි පරීක්ෂා කිරීම සඳහා ඉතා සංකීර්ණ වෙස් මුහුණු සෑදිය හැකිය, උදාහරණයක් ලෙස:
- ?1##??777RUS – 777 කලාපයේ සියලුම බලපත්‍ර තහඩු තෝරාගැනීම, 1 න් ආරම්භ වේ
- LLC* - LLC වලින් ආරම්භ වන සියලුම සමාගම්
- ##7## - තුන්වන ඉලක්කම් 7 වන ඉලක්කම් පහේ ඩිජිටල් කේතයක් සහිත සියලුම නිෂ්පාදන
- ????? - අකුරු පහේ සියලුම නම්, ආදිය.
ක්රමය 3. කොන්දේසි දෙකක් යටතේ පෙළ ඇලවීම සඳහා මැක්රෝ ශ්රිතය
කාර්යයේදී ඔබට පෙළ එක කොන්දේසියකට වඩා සම්බන්ධ කිරීමට අවශ්ය වූ විට ගැටළුවක් ඇති විය හැක. උදාහරණයක් ලෙස, අපගේ පෙර වගුවේ, නගරය සමඟ තවත් තීරුවක් එකතු කර ඇති අතර, දී ඇති සමාගමක් සඳහා පමණක් නොව, දී ඇති නගරයක් සඳහා ද ඇලවීම සිදු කළ යුතු යැයි සිතමු. මෙම අවස්ථාවෙහිදී, අපගේ කාර්යයට තවත් පරාස චෙක්පතක් එක් කිරීමෙන් තරමක් නවීකරණය කිරීමට සිදුවනු ඇත:
Function MergeIfs(TextRange as Range, SearchRange1 Range ලෙස, Condition1 String ලෙස, SearchRange2 As Range, Condition2 ලෙස String) Dim Delimeter String ලෙස, i As Long Delimeter = ", " 'delimiter characters (ඉඩ සමඟ ප්‍රතිස්ථාපනය කළ හැක හෝ ; etc.) e.) 'වලංගුකරණය සහ ඇලවීමේ පරාස එකිනෙක සමාන නොවේ නම්, SearchRange1.Count <> TextRange.Count හෝ SearchRange2.Count <> TextRange.Count එවිට MergeIfs = CVErr(xlErrRef) ක්‍රියාකාරීත්වය අවසන් වුවහොත් දෝෂයකින් පිටවන්න. 'සියලු සෛල හරහා ගොස්, සියලු කොන්දේසි පරීක්ෂා කර, i = 1 සඳහා OutText විචල්‍යයට පෙළ එකතු කරන්න. SearchRange1.Cells වෙත ගණන් කරන්න. SearchRange1.Cells(i) = Condition1 සහ SearchRange2.Cells(i) = Condition2 ඉන්පසු OutText = OutText & TextRange.Cells(i) & Delimeter End ඊලගට i 'අවසාන පරිසීමකය නොමැතිව ප්‍රතිඵල පෙන්වන්න MergeIfs = Left(OutText, Len(OutText) - Len(Delimeter)) End Function
එය හරියටම එකම ආකාරයකින් යෙදෙනු ඇත - තර්ක පමණක් දැන් වැඩිපුර සඳහන් කළ යුතුය:
ක්රමය 4. බල විමසුමේ සමූහගත කිරීම සහ ඇලවීම
ඔබ නොමිලේ Power Query add-in භාවිතා කරන්නේ නම්, VBA හි වැඩසටහන් කිරීමකින් තොරව ඔබට ගැටළුව විසඳා ගත හැකිය. Excel 2010-2013 සඳහා එය මෙතැනින් බාගත කළ හැකි අතර, Excel 2016 හි එය දැනටමත් පෙරනිමියෙන් ගොඩනගා ඇත. ක්රියා අනුපිළිවෙල පහත පරිදි වනු ඇත:
Power Query සාමාන්‍ය වගු සමඟ වැඩ කරන්නේ කෙසේදැයි නොදනී, එබැවින් පළමු පියවර වන්නේ අපගේ මේසය “ස්මාර්ට්” එකක් බවට පත් කිරීමයි. මෙය සිදු කිරීම සඳහා, එය තෝරාගෙන සංයෝජනය ඔබන්න Ctrl+T හෝ ටැබ් එකෙන් තෝරන්න නිවස - මේසයක් ලෙස හැඩතල ගැන්වීම (මුල් පිටුව - වගුවක් ලෙස ආකෘතිය). එවිට දිස්වන ටැබ් එක මත ඉදිකිරීම්කරු (නිර්මාණ) ඔබට වගුවේ නම සැකසිය හැකිය (මම සම්මතයෙන් ඉවත්ව ගියෙමි වගුව 1):
දැන් අපි අපේ වගුව Power Query add-in එකට පූරණය කරමු. මෙය සිදු කිරීම සඳහා, ටැබය මත දත්ත (ඔබට Excel 2016 තිබේ නම්) හෝ Power Query ටැබය මත (ඔබට Excel 2010-2013 තිබේ නම්) ක්ලික් කරන්න මේසයෙන් (දත්ත - වගුවෙන්):
විවෘත වන විමසුම් සංස්කාරක කවුළුවෙහි, ශීර්ෂය මත ක්ලික් කිරීමෙන් තීරුව තෝරන්න සමාගම සහ ඉහත බොත්තම ඔබන්න සමූහ (කණ්ඩායම් අනුව). කණ්ඩායම්කරණයේ නව තීරුවේ නම සහ මෙහෙයුම් වර්ගය ඇතුළත් කරන්න - සියලුම රේඛා (සියලු පේළි):
හරි ක්ලික් කරන්න, එවිට අපට එක් එක් සමාගම සඳහා කාණ්ඩගත අගයන් කුඩා වගුවක් ලැබේ. ප්‍රතිඵලයක් ලෙස ලැබෙන තීරුවේ ඇති සෛලවල සුදු පසුබිම (පෙළ මත නොවේ!) මත වම්-ක්ලික් කළහොත් වගුවල අන්තර්ගතය පැහැදිලිව දැකගත හැකිය:
දැන් අපි තවත් තීරුවක් එකතු කරමු, එහිදී, ශ්රිතය භාවිතා කරමින්, අපි එක් එක් කුඩා වගු වල ලිපින තීරු වල අන්තර්ගතය කොමාවෙන් වෙන් කරමු. මෙය සිදු කිරීම සඳහා, ටැබය මත තීරුව එක් කරන්න අපි ඔබන්න අභිරුචි තීරුව (තීරුව එක් කරන්න - අභිරුචි තීරුව) සහ දිස්වන කවුළුවෙහි, නව තීරුවේ නම සහ බල විමසුමට ගොඩනගා ඇති M භාෂාවෙන් සම්බන්ධ කිරීමේ සූත්රය ඇතුළත් කරන්න:
සියලුම M-කාර්යයන් සිද්ධි සංවේදී (Excel මෙන් නොව) බව සලකන්න. ක්ලික් කිරීමෙන් පසු OK ඇලවූ ලිපින සහිත නව තීරුවක් අපට ලැබේ:
දැනටමත් අනවශ්ය තීරුව ඉවත් කිරීමට එය ඉතිරිව ඇත වගු ලිපින (මාතෘකාව මත දකුණු ක්ලික් කරන්න) තීරුව මකන්න) සහ ටැබ් එක මත ක්ලික් කිරීමෙන් ප්‍රතිඵල පත්‍රයට උඩුගත කරන්න මුල් පිටුව - වසන්න සහ බාගන්න (නිවස - වසා දමා පැටවීම):
වැදගත් nuance: පෙර ක්‍රම (කාර්යයන්) මෙන් නොව Power Query වෙතින් වගු ස්වයංක්‍රීයව යාවත්කාලීන නොවේ. අනාගතයේදී මූලාශ්‍ර දත්තවල යම් වෙනසක් සිදුවේ නම්, ඔබට ප්‍රතිඵල වගුවේ ඕනෑම තැනක දකුණු ක්ලික් කර විධානය තෝරාගත යුතුය. යාවත්කාලීන කර සුරකින්න (නැවුම් කරන්න).
- දිගු පෙළ පෙළක් කොටස් වලට බෙදන ආකාරය
- විවිධ සෛල වලින් පෙළ එකකට ඇලවීමට ක්‍රම කිහිපයක්
- වෙස් මුහුණකට එරෙහිව පෙළ පරීක්ෂා කිරීමට Like ක්රියාකරු භාවිතා කිරීම