Mukati
Ini ndakatonyora nezve maitiro aunokwanisa kukurumidza kunama mavara kubva kune akati wandei maseru kuita rimwe uye, neimwe nzira, patsanura refu mavara tambo muzvikamu. Zvino ngatitarisei pakuvhara, asi basa rakanyanya kuoma - maitiro ekunamira mavara kubva kune akati wandei maseru kana imwe mamiriro akatsanangurwa asangana.
Ngatitii isu tine dhatabhesi yevatengi, uko zita rekambani rimwe rinogona kuenderana nemaemail akati wandei evashandi varo. Basa redu nderekuunganidza kero dzese nemazita ekambani uye kuabatanidza (akaparadzaniswa nemakoma kana semicolons) kuitira kugadzira, semuenzaniso, runyoro rwetsamba rwevatengi, kureva kuwana zvinobuda senge:
Mune mamwe mazwi, isu tinoda chishandiso chinonamira (link) chinyorwa zvinoenderana nemamiriro ezvinhu - analogue yebasa. SUMMESLI (SUMIF), asi nokuda kwemashoko.
Nzira 0. Formula
Kwete yakanyanya kunaka, asi nzira iri nyore. Iwe unogona kunyora fomura yakapfava iyo inoongorora kana kambani iri mumutsara unotevera inosiyana neyakapfuura. Kana zvisina kusiyana, ipapo namira kero inotevera yakapatsanurwa nekoma. Kana zvikasiyana, saka "tinotangazve" zvakaunganidzwa, kutanga zvakare:
Kuipa kweiyi nzira kuri pachena: kubva kumaseru ese ekoramu yakawedzerwa yakawanikwa, isu tinoda chete ekupedzisira kune imwe neimwe kambani (yero). Kana iyo rondedzero yakakura, saka kuti uvasarudzire nekukurumidza, iwe uchafanirwa kuwedzera imwe column uchishandisa basa DLSTR (LEN), kutarisa kureba kwetambo dzakaunganidzwa:
Iye zvino unogona kusefa kunze iwo uye kukopa inodiwa kero gluing kuti uwedzere kushandiswa.
Method 1. Macrofunction ye gluing neimwe mamiriro
Kana iyo yekutanga rondedzero isina kurongeka nekambani, saka iri pamusoro iri nyore formula haishande, asi iwe unogona nyore kutenderera nediki tsika basa muVBA. Vhura iyo Visual Basic Mharidzo nekudzvanya keyboard nzira Alt + F11 kana kushandisa bhatani Visual Basic Tab yokuvaka (Mugadziri). Muhwindo rinovhura, isa nyowani isina chinhu module kuburikidza nemenyu Pinza – Module uye kopira zvinyorwa zvebasa redu ipapo:
Function MergeIf(TextRange As Range, SearchRange As Range, Condition As String) Dim Delimeter Se String, i As Long Delimeter = ", " gluings haana kuenzana - tinobuda nekanganiso Kana SearchRange.Count <> TextRange.Count Wobva wapinda MergeIf = CVErr(xlErrRef) Buda Basa Kupera Kana 'famba nemaseru ese, tarisa mamiriro uye tora zvinyorwa mune inosiyana OutText For i = 1 KutsvagaRange. Cells.Count Kana SearchRange.Masero(i) Like Condition Then OutText = OutText & TextRange.Cells(i) & Delimeter Next i' shower results without last delimiter MergeIf = Left(OutText, Len(OutText) - Len(Delimeter)) End basa
Kana iwe zvino wadzokera kuMicrosoft Excel, ipapo mune runyorwa rwemabasa (bhatani fx mune formula bar kana tab Mafomura – Isa Basa) zvinozoita kuti tiwane basa redu MergeIf muchikwata Mushandisi Anotsanangurwa (Mushandisi Anotsanangurwa). Mapoka ebasa ndeaya anotevera:
Nzira 2. Batanidza zvinyorwa nemamiriro asina kunyatso
Kana tikatsiva hunhu hwekutanga mumutsara wechigumi nematatu wemacro yedu = kune anenge ari match operator se, zvino zvichave zvichigoneka kuita gluing neyakajeka match yedata rekutanga nechisarudzo chekusarudza. Semuenzaniso, kana zita rekambani richigona kunyorwa mumhando dzakasiyana, saka tinogona kutarisa nekuzviunganidza zvese nebasa rimwechete:
Standard wildcards anotsigirwa:
- asterisk (*) - inoreva chero nhamba yechero mavara (kusanganisira kusavapo kwavo)
- mubvunzo mucherechedzo (?) - inomirira chero munhu mumwe chete
- pound sign (#) - inomirira chero digit imwe (0-9)
Nekumisikidza, iyo Like opareta inobata nyaya, kureva kuti inonzwisisa, semuenzaniso, "Orion" uye "orion" semakambani akasiyana. Kufuratira nyaya, unogona kuwedzera mutsara pakutanga chaiko kwemodule muVisual Basic edhita Sarudzo Enzanisa Rugwaro, iyo inoshandura Like kuti isave nehanya.
Nenzira iyi, iwe unogona kugadzira masiki akaoma kwazvo ekutarisa mamiriro, semuenzaniso:
- ?1##??777RUS – kusarudzwa kwemarezinesi ma plates edunhu re777, kutanga ne1
- LLC * - makambani ese ane zita rinotanga ne LLC
- ##7## - zvese zvigadzirwa zvine shanu-manhamba edhijitari kodhi, uko yechitatu manhamba 7
- ????? - mazita ose emabhii mashanu, nezvimwewo.
Nzira 3. Macro basa re gluing zvinyorwa pasi pemamiriro maviri
Mubasa racho panogona kunge kune dambudziko kana iwe uchida kubatanidza zvinyorwa zvinopfuura imwe mamiriro. Semuenzaniso, ngatifungei kuti mune tafura yedu yapfuura, imwezve column neguta yakawedzerwa, uye gluing inofanira kuitwa kwete chete kune imwe kambani, asiwo kune rimwe guta. Muchiitiko ichi, basa redu richafanirwa kuvandudzwa zvishoma nekuwedzera imwe cheki cheki kwairi:
Function MergeIfs(TextRange As Range, SearchRange1 As Range, Condition1 Se String, SearchRange2 As Range, Condition2 As String) Dim Delimeter Se String, i As Long Delimeter = "," 'delimiter characters (inogona kutsiviwa nenzvimbo kana; nezvimwewo) e.) 'kana iyo yekusimbisa uye gluing range isina kuenzana, buda nekukanganisa Kana SearchRange1.Count <> TextRange.Count Kana SearchRange2.Count <> TextRange.Count Then MergeIfs = CVERr(xlErrRef) Exit Function End Kana 'enda nemaserungi ese, tarisa mamiriro ese uye unganidza zvinyorwa mushanduro OutText For i = 1 KutsvagaRange1.Masero.Count Kana SearchRange1.Masero(i) = Condition1 Uye SearchRange2.Masero(i) = Condition2 Then OutText = OutText & TextRange.Masero(i) & Delimeter End Kana Zvichitevera ini 'kuratidza mhinduro pasina yekupedzisira delimiter MergeIfs = Left(OutText, Len(OutText) - Len(Delimeter)) End Function
Ichashandiswa nenzira imwecheteyo - nharo chete zvino dzinoda kutsanangurwa zvakanyanya:
Nzira 4. Kubatana uye gluing muMasimba Query
Iwe unogona kugadzirisa dambudziko pasina hurongwa muVBA, kana iwe ukashandisa yemahara Power Query yekuwedzera-mukati. YeExcel 2010-2013 inogona kutorwa pano, uye muExcel 2016 yakatovakwa mukati nekukasira. Kutevedzana kwezviito kuchave seizvi:
Power Query haizive kushanda nematafura enguva dzose, saka danho rekutanga nderekushandura tafura yedu kuita "smart" imwe. Kuti uite izvi, sarudza iyo uye wodzvanya musanganiswa Ctrl+T kana sarudza kubva pane tab Kumba – Fomati setafura (Kumba - Fomati seTafura). Pane tab inobva yaonekwa Constructor (Dhizaini) unogona kuseta zita retafura (ndakasiya standard Tafura 1):
Zvino ngatiisei tafura yedu muPower Query yekuwedzera-mukati. Kuti uite izvi, pane tab Data (kana uine Excel 2016) kana pane Power Query tab (kana uine Excel 2010-2013) tinya Kubva patafura (Data - Kubva Tafura):
Muwindo remubvunzo wemupepeti rinovhurwa, sarudza iyo column nekudzvanya pane iyo musoro kambani wodzvanya bhatani riri pamusoro boka (Boka Ne). Isa zita rekoromo itsva uye rudzi rwekushanda muboka - Mitsetse yose (Mitsara Yese):
Dzvanya OK uye isu tinowana mini-tafura yeakaunganidzwa maitiro ekambani yega yega. Zviri mukati mematafura zvinonyatsooneka kana ukadzvanya-kuruboshwe pane chena kumashure kwemaseru (kwete pane zvinyorwa!) mukoramu inobuda:
Iye zvino ngatiwedzere imwe mbiru, apo, tichishandisa basa racho, tinonamira zviri mukati meKero columns mune imwe neimwe yematafura-matafura, akaparadzaniswa nemakoma. Kuti uite izvi, pane tab Wedzera ikholamu tinodzvanya Custom column (Wedzera koramu - Tsika mbiru) uye muhwindo rinoonekwa, isa zita rekoromo itsva uye yekubatanidza fomula mumutauro weM wakavakirwa muSimba Query:
Ziva kuti ese M-mafunctions ane case sensitive (kusiyana neExcel). Mushure mekudzvanya pa OK tinowana mbiru itsva ine glued kero:
Inoramba ichibvisa iyo yakatove isingakoshi column TableAddresses (tinya kurudyi pamusoro) Delete column) uye rodha zvawanikwa pabepa nekudzvanya pane tab Musha - Vhara uye dhawunirodha (Kumba - Vhara uye takura):
Nuance yakakosha: Kusiyana nemaitiro apfuura (mabasa), matafura kubva Power Query haana kuvandudzwa otomatiki. Kana mune ramangwana pachange paine shanduko mune sosi data, saka iwe unozofanirwa kudzvanya-kurudyi chero kupi patafura yemhedzisiro uye wosarudza iwo murairo. Gadziridza & Chengeta (Refresh).
- Maitiro ekutsemura tambo refu yemavara kuita zvikamu
- Nzira dzinoverengeka dzekunamira zvinyorwa kubva kune akasiyana maseru kuita rimwe
- Kushandisa Like opareta kuyedza mavara ne mask