Kulumikiza mawu ndi chikhalidwe

Ndalemba kale za momwe mungalumikizire mwachangu mawu kuchokera pamaselo angapo kukhala amodzi, ndikuyikanso chingwe chachitali kukhala zigawo. Tsopano tiyeni tiyang'ane pafupi, koma ntchito yovuta kwambiri - momwe mungalumikizire malemba kuchokera ku maselo angapo pamene chikhalidwe china chake chakwaniritsidwa. 

Tinene kuti tili ndi nkhokwe yamakasitomala, pomwe dzina la kampani imodzi lingafanane ndi maimelo angapo a antchito ake. Ntchito yathu ndikusonkhanitsa maadiresi onse ndi mayina amakampani ndikuwaphatikiza (osiyanitsidwa ndi koma kapena ma semicolons) kuti tipange, mwachitsanzo, mndandanda wamakalata amakasitomala, mwachitsanzo, kupeza zotuluka monga:

Mwanjira ina, tikufuna chida chomwe chimamatira (kulumikiza) mawuwo malinga ndi momwe zilili - analogue ya ntchitoyi. SUMMESLI (SUMIF), koma palemba.

Njira 0. Fomula

Osati zokongola kwambiri, koma njira yosavuta. Mutha kulemba fomula yosavuta yomwe iwonetse ngati kampani yomwe ili pamzere wotsatira ikusiyana ndi yam'mbuyomu. Ngati sizikusiyana, sungani adilesi yotsatira yosiyanitsidwa ndi koma. Ngati zimasiyana, ndiye "tikukhazikitsanso" zomwe zasonkhanitsidwa, kuyambiranso:

Zoyipa za njirayi ndizodziwikiratu: kuchokera m'maselo onse a gawo lowonjezera lomwe adapeza, timafunikira omaliza okha pakampani iliyonse (yachikasu). Ngati mndandandawo ndi waukulu, ndiye kuti muwasankhe mwachangu, muyenera kuwonjezera gawo lina pogwiritsa ntchito ntchitoyi Chithunzi cha DLSTR (LEN), kuyang'ana kutalika kwa zingwe zomwe zasonkhanitsidwa:

Tsopano mutha kusefa ndikutengera adilesi yofunikira kuti mugwiritsenso ntchito.

Njira 1. Macrofunction ya gluing ndi chikhalidwe chimodzi

Ngati mndandanda wapachiyambi sunasankhidwe ndi kampani, ndiye kuti njira yosavuta yomwe ili pamwambayi siigwira ntchito, koma mukhoza kuyenda mosavuta ndi kachitidwe kakang'ono ka VBA. Tsegulani Visual Basic Editor mwa kukanikiza njira yachidule ya kiyibodi Alt + F11 kapena kugwiritsa ntchito batani Zooneka Basic tsamba Woyambitsa (Wolemba Mapulogalamu). Pazenera lomwe limatsegulidwa, ikani gawo latsopano lopanda kanthu kudzera pa menyu Ikani - Module ndikukopera zolemba za ntchito yathu pamenepo:

Function MergeIf(TextRange As Range, SearchRange As Range, Condition As String) Dim Delimeter As String, i As Long Delimeter = ", " gluings sali ofanana wina ndi mzake - timatuluka ndi cholakwika Ngati SearchRange.Count <> TextRange.Count Ndiye MergeIf = CVERr(xlErrRef) Tulukani Ntchito Mapeto Ngati 'dutsa m'maselo onse, yang'anani momwe zilili ndikusonkhanitsa malemba mu variable OutText For i = 1 To SearchRange. Cells.Count Ngati SearchRange.Cells(i) Like Condition Kenako OutText = OutText & TextRange.Cells(i) & Delimeter Next ndikuwonetsa zotsatira popanda delimiter yomaliza MergeIf = Left(OutText, Len(OutText) - Len(Delimeter)) End ntchito  

Ngati tsopano mubwerera ku Microsoft Excel, ndiye pamndandanda wantchito (batani fx mu bar ya formula kapena tabu Mafomula - Ikani Ntchito) zidzatheka kupeza ntchito yathu MergeIf mu gulu Kumasulira Womasulira (Kutanthauzira Wogwiritsa). Zotsutsana za ntchitoyi ndi izi:

Njira 2. Gwirizanitsani mawu ndi chikhalidwe chosayenera

Ngati tisintha munthu woyamba pamzere wa 13 wa macro athu = kwa wogwiritsa ntchito machesi ngati, ndiye kuti kudzakhala kotheka kuchita gluing ndi mafananidwe osagwirizana ndi deta yoyamba ndi muyeso wosankha. Mwachitsanzo, ngati dzina la kampani likhoza kulembedwa mumitundu yosiyanasiyana, ndiye kuti titha kuyang'ana ndikusonkhanitsa zonse ndi ntchito imodzi:

Makhadi akutchire amtundu amathandizidwa:

• asterisk (*) - amatanthauza nambala iliyonse ya zilembo (kuphatikiza kusapezeka kwawo)
• funso (?) - limayimira munthu m'modzi
• chizindikiro cha paundi (#) - chimayimira manambala aliwonse (0-9)

Mwachikhazikitso, Wogwiritsa ntchito ngati ali ndi vuto, mwachitsanzo, amamvetsetsa, mwachitsanzo, "Orion" ndi "orion" monga makampani osiyanasiyana. Kuti musanyalanyaze mlandu, mutha kuwonjezera mzere kumayambiriro kwa gawoli mu Visual Basic editor Yerekezerani Malemba, zomwe zisinthana ndi Like kuti zikhale zosakhudzidwa.

Mwanjira iyi, mutha kupanga masks ovuta kwambiri kuti muwone zinthu, mwachitsanzo:

• ?1##??777RUS - kusankha kwa ziphaso zonse zachigawo cha 777, kuyambira ndi 1
• LLC* - makampani onse omwe dzina lawo limayamba ndi LLC
• ##7## - zinthu zonse zokhala ndi manambala asanu, pomwe nambala yachitatu ndi 7
• ????? - mayina onse a zilembo zisanu, etc.
  

Njira 3. Macro ntchito kwa gluing malemba pansi pa zikhalidwe ziwiri

Mu ntchito pakhoza kukhala vuto pamene muyenera kulumikiza malemba oposa chikhalidwe chimodzi. Mwachitsanzo, tiyerekeze kuti patebulo lathu lapitalo, gawo limodzi linanso ndi mzinda linawonjezeredwa, ndipo gluing iyenera kuchitidwa osati kwa kampani yokhayo, komanso kwa mzinda womwe wapatsidwa. Pankhaniyi, ntchito yathu iyenera kusinthidwa pang'ono powonjezera cheke china kwa icho:

Function MergeIfs(TextRange As Range, SearchRange1 As Range, Condition1 As String, SearchRange2 As Range, Condition2 As String) Dim Delimeter As String, i As Long Delimeter = "," 'delimiter zilembo (zitha kusinthidwa ndi danga kapena; etc.) e.) 'ngati magawo otsimikizira ndi gluing sali ofanana, tulukani ndi cholakwika Ngati SearchRange1.Count <> TextRange.Count Kapena SearchRange2.Count <> TextRange.Count Kenako MergeIfs = CVERr(xlErrRef) Tulukani Ntchito Yatha Ngati 'Pitani m'maselo onse, fufuzani zonse ndi kusonkhanitsa malembawo mu variable OutText For i = 1 To SearchRange1.Cells.Count If SearchRange1.Cells(i) = Condition1 And SearchRange2.Cells(i) = Condition2 Kenako OutText = OutText & TextRange.Cells(i) & Delimeter End Ngati Kenako 'ndiwonetsa zotsatira popanda delimiter yomaliza MergeIfs = Left(OutText, Len(OutText) - Len(Delimeter)) End Function  

Idzagwiritsidwa ntchito chimodzimodzi - mikangano yokhayo yomwe ikufunika kufotokozedwa mowonjezereka:

Njira 4. Kuyika magulu ndi gluing mu Power Query

Mutha kuthana ndi vutoli popanda kupanga mapulogalamu mu VBA, ngati mugwiritsa ntchito pulogalamu yowonjezera ya Power Query. Kwa Excel 2010-2013 ikhoza kutsitsidwa apa, ndipo mu Excel 2016 idamangidwa kale mwachisawawa. Ndondomeko ya zochita idzakhala motere:

Power Query sadziwa momwe angagwiritsire ntchito ndi matebulo okhazikika, kotero sitepe yoyamba ndikusandutsa tebulo lathu kukhala "wanzeru". Kuti muchite izi, sankhani ndikusindikiza kuphatikiza Ctrl+T kapena sankhani kuchokera pa tabu Kunyumba - Pangani ngati tebulo (Kunyumba - Pangani Monga Table). Pa tabu yomwe ikuwonekera Constructor (Kapangidwe) mutha kuyika dzina la tebulo (ndinasiya muyezo Gulu 1):

Tsopano tiyeni tiyike tebulo lathu muzowonjezera za Power Query. Kuti muchite izi, dinani pa tabu Deta (ngati muli ndi Excel 2016) kapena pa Power Query tabu (ngati muli ndi Excel 2010-2013) dinani Kuchokera patebulo (Deta - Kuchokera patebulo):

Pazenera la query editor lomwe limatsegulidwa, sankhani gawoli podina pamutu Company ndi kukanikiza batani pamwamba gulu (Gulu Lolemba). Lowetsani dzina lagawo latsopano ndi mtundu wa ntchito m'magulu - Mizere yonse (Mizere Yonse):

Dinani Chabwino ndipo timapeza tebulo laling'ono lamagulu a kampani iliyonse. Zomwe zili m'matebulo zimawoneka bwino ngati mutadina kumanzere chakumbuyo koyera kwa ma cell (osati pamawuwo!)

Tsopano tiyeni tiwonjezere ndime ina, pomwe, pogwiritsa ntchito ntchitoyi, timamatira zomwe zili m'magawo a Adilesi patebulo lililonse laling'ono, lolekanitsidwa ndi koma. Kuti muchite izi, dinani pa tabu Onjezani mzati timasindikiza Mzati mwamakonda (Onjezani gawo - Mzere Wamakonda) ndipo pazenera lomwe likuwoneka, lowetsani dzina lagawo latsopano ndi fomula yolumikizira mu chilankhulo cha M chopangidwa mu Power Query:

Dziwani kuti ntchito zonse za M ndizovuta (mosiyana ndi Excel). Pambuyo kuwonekera pa OK timapeza gawo latsopano lokhala ndi ma adilesi omatira:

Zimatsalira kuchotsa mzere wosafunikira kale TableAdiresi (dinani pomwe pamutu) Chotsani ndime) ndikukweza zotsatira papepala podina pa tabu Kunyumba - Tsekani ndikutsitsa (Kunyumba - Tsekani ndikunyamula):

Nuance yofunika: Mosiyana ndi njira zam'mbuyomu (ntchito), matebulo ochokera ku Power Query sasinthidwa zokha. Ngati m'tsogolomu padzakhala kusintha kulikonse mu deta yochokera, ndiye kuti muyenera kudina-kumanja kulikonse pa tebulo lazotsatira ndikusankha lamulo. Sinthani & Sungani (Bwezeretsani).

• Momwe mungagawire chingwe chachitali kukhala zigawo
• Njira zingapo zomata zolemba kuchokera kumagulu osiyanasiyana kukhala amodzi
• Kugwiritsa ntchito Like operator kuyesa mawu ndi chigoba