Kuunganisha maandishi kwa masharti

Tayari niliandika juu ya jinsi unaweza haraka gundi maandishi kutoka kwa seli kadhaa hadi moja na, kinyume chake, panga kamba ndefu ya maandishi kwenye vipengele. Sasa hebu tuangalie kwa karibu, lakini kazi ngumu zaidi - jinsi ya gundi maandishi kutoka kwa seli kadhaa wakati hali fulani maalum inafikiwa. 

Wacha tuseme kwamba tuna hifadhidata ya wateja, ambapo jina la kampuni moja linaweza kuendana na barua pepe tofauti za wafanyikazi wake. Jukumu letu ni kukusanya anwani zote kwa majina ya kampuni na kuziunganisha (zinazotenganishwa na koma au nusukoloni) ili kutengeneza, kwa mfano, orodha ya utumaji barua kwa wateja, yaani kupata pato kama vile:

Kwa maneno mengine, tunahitaji chombo ambacho kitaunganisha (kiungo) maandishi kulingana na hali - analog ya kazi. SUMMESLI (SUMIF), lakini kwa maandishi.

Njia 0. Mfumo

Sio kifahari sana, lakini njia rahisi zaidi. Unaweza kuandika fomula rahisi ambayo itaangalia ikiwa kampuni katika safu inayofuata inatofautiana na ile iliyotangulia. Ikiwa haina tofauti, basi gundi anwani inayofuata ikitenganishwa na koma. Ikiwa inatofautiana, basi "tunaweka upya" kusanyiko, kuanzia tena:

Hasara za mbinu hii ni dhahiri: kutoka kwa seli zote za safu ya ziada iliyopatikana, tunahitaji tu za mwisho kwa kila kampuni (njano). Ikiwa orodha ni kubwa, basi ili kuwachagua haraka, itabidi uongeze safu nyingine kwa kutumia kazi DLSTR (LEN), kuangalia urefu wa kamba zilizokusanywa:

Sasa unaweza kuchuja zile na kunakili gluing ya anwani inayofaa kwa matumizi zaidi.

Njia ya 1. Macrofunction ya gluing kwa hali moja

Ikiwa orodha ya asili haijapangwa na kampuni, basi formula rahisi hapo juu haifanyi kazi, lakini unaweza kuzunguka kwa urahisi na kazi ndogo ya desturi katika VBA. Fungua Kihariri cha Msingi cha Visual kwa kubonyeza njia ya mkato ya kibodi Alt + F11 au kutumia kitufe Visual Basic tab developer (Msanidi programu). Katika dirisha linalofungua, ingiza moduli mpya tupu kupitia menyu Ingiza - Moduli na unakili maandishi ya kazi yetu hapo:

Function MergeIf(TextRange As Runge, SearchRange Kama Masafa, Hali Kama Kamba) Dim Delimeter Kama Kamba, i As Long Delimeter = ", " gluings si sawa kwa kila mmoja - tunatoka na hitilafu Ikiwa SearchRange.Count <> TextRange.Count Kisha MergeIf = CVERr(xlErrRef) Toka Mwisho wa Kitendaji Kama 'pitia seli zote, angalia hali na kukusanya maandishi katika mabadiliko ya OutText For i = 1 To SearchRange. Cells.Hesabu Ikiwa SearchRange.Cells(i) Like Condition Then OutText = OutText & TextRange.Cells(i) & Delimeter Next 'ninaonyesha matokeo bila delimiter ya mwisho MergeIf = Left(OutText, Len(OutText) - Len(Delimeter)) End kazi  

Ikiwa sasa unarudi kwa Microsoft Excel, basi kwenye orodha ya kazi (kifungo fx kwenye upau wa fomula au kichupo Fomula - Ingiza Kazi) itawezekana kupata kazi yetu UnganishaIf katika kategoria Mtumiaji Amefafanuliwa (Mtumiaji Amefafanuliwa). Hoja za kazi ni kama ifuatavyo:

Njia ya 2. Unganisha maandishi kwa hali isiyo sahihi

Ikiwa tutabadilisha mhusika wa kwanza kwenye safu ya 13 ya macro yetu = kwa takriban opereta wa mechi kama, basi itawezekana kutekeleza gluing kwa mechi isiyo sahihi ya data ya awali na kigezo cha uteuzi. Kwa mfano, ikiwa jina la kampuni linaweza kuandikwa kwa lahaja tofauti, basi tunaweza kuangalia na kuzikusanya zote kwa kipengele kimoja cha kukokotoa:

Kadi pori za kawaida zinatumika:

• nyota (*) - inaashiria idadi yoyote ya wahusika wowote (pamoja na kutokuwepo kwao)
• alama ya kuuliza (?) - inasimamia mhusika yeyote
• alama ya pauni (#) - inasimamia tarakimu yoyote (0-9)

Kwa chaguomsingi, Opereta ya Like ni nyeti kwa ukubwa, yaani, inaelewa, kwa mfano, "Orion" na "orion" kama kampuni tofauti. Ili kupuuza kesi, unaweza kuongeza mstari mwanzoni mwa moduli kwenye kihariri cha Visual Basic Chaguo Linganisha Maandishi, ambayo itabadilisha Like kuwa haisikii.

Kwa njia hii, unaweza kutunga masks ngumu sana kwa kuangalia hali, kwa mfano:

• ?1##??777RUS - uteuzi wa nambari zote za leseni za mkoa wa 777, kuanzia 1
• LLC* - makampuni yote ambayo jina linaanza na LLC
• ##7## - bidhaa zote zilizo na msimbo wa tarakimu tano, ambapo tarakimu ya tatu ni 7
• ????? - majina yote ya herufi tano, nk.
  

Njia ya 3. Kazi ya macro kwa maandishi ya gluing chini ya hali mbili

Katika kazi kunaweza kuwa na tatizo wakati unahitaji kuunganisha maandishi zaidi ya hali moja. Kwa mfano, hebu fikiria kwamba katika meza yetu ya awali, safu moja zaidi na jiji iliongezwa, na gluing inapaswa kufanyika si tu kwa kampuni iliyotolewa, bali pia kwa jiji fulani. Katika kesi hii, kazi yetu italazimika kusasishwa kidogo kwa kuongeza ukaguzi mwingine wa anuwai kwake:

Uunganisho wa Utendaji(TextRange Kama Masafa, Msururu wa Utafutaji1 Kama Masafa, Hali1 Kama Mfuatano, Msururu wa Utafutaji2 Kama Msururu, Masharti2 Kama Kamba) Punguza Delimita Kama Kamba, i As Long Delimeter = ", " 'herufi za delimiter (zinaweza kubadilishwa na nafasi au; nk.) e.) 'ikiwa safu za uthibitishaji na gluing hazilingani, ondoka na hitilafu Ikiwa SearchRange1.Count <> TextRange.Count Au SearchRange2.Count <> TextRange.Count Then MergeIfs = CVERr(xlErrRef) Toka Mwisho wa Kazi Ikiwa 'pitia seli zote, angalia hali zote na kukusanya maandishi katika mabadiliko ya OutText For i = 1 To SearchRange1.Cells.Hesabu Ikiwa SearchRange1.Cells(i) = Condition1 Na SearchRange2.Cells(i) = Condition2 Kisha OutText = OutText & TextRange.Cells(i) & Delimeter End Ikiwa Inayofuata 'nitaonyesha matokeo bila delimiter ya mwisho MergeIfs = Left(OutText, Len(OutText) - Len(Delimeter)) End Function  

Itatumika kwa njia sawa kabisa - ni hoja tu zinazohitaji kubainishwa zaidi:

Njia ya 4. Kuweka vikundi na kuunganisha katika Swala la Nguvu

Unaweza kutatua tatizo bila programu katika VBA, ikiwa unatumia nyongeza ya Power Query bila malipo. Kwa Excel 2010-2013 inaweza kupakuliwa hapa, na katika Excel 2016 tayari imejengwa kwa default. Mlolongo wa vitendo utakuwa kama ifuatavyo:

Swali la Nguvu hajui jinsi ya kufanya kazi na meza za kawaida, hivyo hatua ya kwanza ni kugeuza meza yetu kuwa "smart" moja. Ili kufanya hivyo, chagua na bonyeza mchanganyiko Ctrl+T au chagua kutoka kwa kichupo Nyumbani - Fomati kama meza (Nyumbani - Umbizo kama Jedwali). Kwenye kichupo kinachoonekana kisha kuujenga (Ubunifu) unaweza kuweka jina la jedwali (niliacha kiwango Meza 1):

Sasa wacha tupakie jedwali letu kwenye programu jalizi ya Hoja ya Nguvu. Ili kufanya hivyo, kwenye kichupo Data (ikiwa unayo Excel 2016) au kwenye kichupo cha Hoja ya Nguvu (ikiwa unayo Excel 2010-2013) bonyeza Kutoka kwa meza (Data - Kutoka Jedwali):

Katika dirisha la mhariri wa swala linalofungua, chagua safu kwa kubofya kichwa kampuni na bonyeza kitufe hapo juu Group (Kundi Kwa). Ingiza jina la safu mpya na aina ya operesheni katika kikundi - Mistari yote (Safu Mlalo Zote):

Bonyeza Sawa na tunapata jedwali ndogo la maadili yaliyowekwa kwa kila kampuni. Yaliyomo kwenye jedwali yanaonekana wazi ikiwa utabofya-kushoto kwenye usuli mweupe wa seli (sio kwenye maandishi!) kwenye safu wima inayosababisha:

Sasa hebu tuongeze safu moja zaidi, ambapo, kwa kutumia kazi, tunaweka yaliyomo kwenye safu za Anwani katika kila meza ndogo, ikitenganishwa na koma. Ili kufanya hivyo, kwenye kichupo Ongeza safu tunabonyeza Safu wima maalum (Ongeza safu wima - Safu wima maalum) na kwenye kidirisha kinachoonekana, ingiza jina la safu wima mpya na fomula ya kuunganisha katika lugha ya M iliyojengwa kwenye Hoja ya Nguvu:

Kumbuka kuwa kazi zote za M ni nyeti kwa ukubwa (tofauti na Excel). Baada ya kubofya OK tunapata safu mpya iliyo na anwani zilizowekwa alama:

Inabakia kuondoa safu ambayo tayari sio lazima Anwani za Jedwali (bonyeza kulia kwenye kichwa) Futa safu wima) na upakie matokeo kwenye laha kwa kubofya kichupo Nyumbani - Funga na upakue (Nyumbani - Funga na upakie):

Nuance muhimu: Tofauti na mbinu za awali (kazi), jedwali kutoka kwa Hoja ya Nguvu hazisasishwa kiotomatiki. Ikiwa katika siku zijazo kutakuwa na mabadiliko yoyote katika data ya chanzo, basi utahitaji kubofya kulia mahali popote kwenye jedwali la matokeo na uchague amri. Sasisha na Uhifadhi (Onyesha upya).

• Jinsi ya kugawanya kamba ndefu ya maandishi katika sehemu
• Njia kadhaa za gundi maandishi kutoka kwa seli tofauti hadi moja
• Kutumia opereta kama vile kujaribu maandishi dhidi ya barakoa