Bondio testun yn ôl amod

Ysgrifennais eisoes am sut y gallwch chi gludo testun yn gyflym o sawl cell yn un ac, i'r gwrthwyneb, dosrannu llinyn testun hir yn gydrannau. Nawr, gadewch i ni edrych ar dasg agos, ond ychydig yn fwy cymhleth - sut i ludo testun o sawl cell pan fodlonir amod penodol. 

Gadewch i ni ddweud bod gennym gronfa ddata o gwsmeriaid, lle gall un enw cwmni gyfateb i nifer o wahanol negeseuon e-bost ei weithwyr. Ein tasg ni yw casglu'r holl gyfeiriadau yn ôl enwau cwmnïau a'u cydgadwynu (wedi'u gwahanu gan atalnodau neu hanner colon) er mwyn gwneud, er enghraifft, rhestr bostio ar gyfer cwsmeriaid, hy cael allbwn rhywbeth fel:

Mewn geiriau eraill, mae angen teclyn a fydd yn gludo (cyswllt) y testun yn ôl yr amod - analog o'r ffwythiant SYMIAU (SUMIF), ond am destun.

Dull 0. Fformiwla

Ddim yn gain iawn, ond y ffordd hawsaf. Gallwch ysgrifennu fformiwla syml a fydd yn gwirio a yw'r cwmni yn y rhes nesaf yn wahanol i'r un flaenorol. Os nad yw'n wahanol, gludwch y cyfeiriad nesaf wedi'i wahanu gan goma. Os yw'n wahanol, yna rydyn ni'n "ailosod" y cronedig, gan ddechrau eto:

Mae anfanteision y dull hwn yn amlwg: o holl gelloedd y golofn ychwanegol a gafwyd, dim ond y rhai olaf sydd eu hangen arnom ar gyfer pob cwmni (melyn). Os yw'r rhestr yn fawr, yna er mwyn eu dewis yn gyflym, bydd yn rhaid i chi ychwanegu colofn arall gan ddefnyddio'r swyddogaeth DLSTR (LEN), gwirio hyd y llinynnau cronedig:

Nawr gallwch chi hidlo'r rhai a chopïo'r gludo cyfeiriad angenrheidiol i'w ddefnyddio ymhellach.

Dull 1. Macrofunction o gludo gan un cyflwr

Os na chaiff y rhestr wreiddiol ei didoli yn ôl cwmni, yna nid yw'r fformiwla syml uchod yn gweithio, ond gallwch chi fynd o gwmpas yn hawdd gyda swyddogaeth arferiad bach yn VBA. Agorwch y Golygydd Visual Basic trwy wasgu llwybr byr bysellfwrdd Alt + F11 neu ddefnyddio'r botwm Visual Basic tab datblygwr (Datblygwr). Yn y ffenestr sy'n agor, mewnosodwch fodiwl gwag newydd trwy'r ddewislen Mewnosod – Modiwl a chopïo testun ein swyddogaeth yno:

Ffwythiant MergeIf(TextRange As Range, SearchRange As Range, Cyflwr Fel Llinyn) Dim Delimeter Fel Llinynnol, i As Long Delimeter = " , " nid yw gludion yn hafal i'w gilydd - rydym yn gadael gyda gwall Os SearchRange.Count <> TextRange.Count Yna MergeIf = CVErr(xlErrRef) Diwedd Swyddogaeth Gadael Os ' ewch drwy'r holl gelloedd, gwiriwch y cyflwr a chasglwch y testun yn y newidyn OutText Ar gyfer i = 1 I SearchRange. Cells.Count If SearchRange.Cells(i) Hoffi Cyflwr Yna OutText = OutText & TextRange.Cells(i) & Delimeter Nesaf i 'dangos canlyniadau heb amffinydd olaf MergeIf = Chwith(All-destun, Len(OutText) - Len(Delimeter))) Diwedd swyddogaeth  

Os ydych chi nawr yn dychwelyd i Microsoft Excel, yna yn y rhestr o swyddogaethau (botwm fx yn y bar fformiwla neu'r tab Fformiwlâu – Mewnosod Swyddogaeth) bydd yn bosibl dod o hyd i'n swyddogaeth UnoIf yn y categori Diffiniwyd y Defnyddiwr (Defnyddiwr Diffiniedig). Mae'r dadleuon i'r swyddogaeth fel a ganlyn:

Dull 2. Concatenate testun gan gyflwr anfanwl

Os byddwn yn disodli'r cymeriad cyntaf yn llinell 13eg ein macro = i weithredwr y gêm yn fras Fel, yna bydd yn bosibl gludo trwy gydweddiad anfanwl o'r data cychwynnol â'r maen prawf dethol. Er enghraifft, os gellir ysgrifennu enw'r cwmni mewn gwahanol amrywiadau, yna gallwn eu gwirio a'u casglu i gyd gydag un swyddogaeth:

Cefnogir cardiau gwyllt safonol:

• seren (*) – yn dynodi unrhyw nifer o gymeriadau (gan gynnwys eu habsenoldeb)
• marc cwestiwn (?) – yn sefyll am unrhyw nod unigol
• arwydd punt (#) - mae'n sefyll am unrhyw un digid (0-9)

Yn ddiofyn, mae'r gweithredwr Like yn sensitif i achosion, hy yn deall, er enghraifft, “Orion” a “orion” fel cwmnïau gwahanol. I anwybyddu achos, gallwch ychwanegu'r llinell ar ddechrau'r modiwl yn y golygydd Visual Basic Opsiwn Cymharu Testun, a fydd yn newid Like to be case insensitive.

Yn y modd hwn, gallwch chi gyfansoddi masgiau cymhleth iawn ar gyfer gwirio amodau, er enghraifft:

• ?1##??777RUS – detholiad o holl blatiau trwydded rhanbarth 777, gan ddechrau gydag 1
• LLC * - pob cwmni y mae ei enw'n dechrau gyda LLC
• ##7## - pob cynnyrch gyda chod digidol pum digid, lle mae'r trydydd digid yn 7
• ????? — pob enw ar bum llythyren, etc.
  

Dull 3. Swyddogaeth macro ar gyfer gludo testun o dan ddau amod

Yn y gwaith efallai y bydd problem pan fydd angen cysylltu'r testun â mwy nag un amod. Er enghraifft, gadewch i ni ddychmygu, yn ein tabl blaenorol, bod un golofn arall gyda'r ddinas wedi'i hychwanegu, a dylid gludo nid yn unig ar gyfer cwmni penodol, ond hefyd ar gyfer dinas benodol. Yn yr achos hwn, bydd yn rhaid i'n swyddogaeth gael ei moderneiddio ychydig trwy ychwanegu gwiriad amrediad arall ato:

Ffwythiant MergeIfs(TextRange As Range, SearchRange1 Fel Ystod, Cyflwr1 Fel Ystod, Cyflwr2 Fel Ystod, Cyflwr 2 Fel Ystod, Cyflwr1 Fel Llinynnol) Dim Delimeter Fel Llinyn, i As Long Delimeter = ", " ' nodau amffinydd (gellir eu disodli gyda gofod neu; ac ati) e.) 'os nad yw'r ystodau dilysu a gludo yn hafal i'w gilydd, gadewch gyda gwall Os SearchRange2.Count <> TextRange.Count Neu SearchRange1.Count <> TextRange.Count Then MergeIfs = CVErr(xlErrRef) Gadael Swyddogaeth Diwedd Os 'ewch trwy bob cell, gwiriwch yr holl amodau a chasglwch y testun i mewn i'r newidyn OutText Ar Gyfer i = 1 I SearchRange1.Cells.Count If SearchRange1.Cells(i) = Cyflwr2 A SearchRange2.Cells(i) = ConditionXNUMX Yna OutText = OutText & TextRange.Cells(i) & Delimeter End If Next i 'dangos canlyniadau heb amffinydd olaf MergeIfs = Chwith(OutText, Len(OutText) - Len(Delimeter)) End Function  

Bydd yn cael ei gymhwyso yn union yr un ffordd – dim ond dadleuon sydd angen eu nodi’n fwy:

Dull 4. Grwpio a gludo mewn Power Query

Gallwch chi ddatrys y broblem heb raglennu yn VBA, os ydych chi'n defnyddio'r ategyn Power Query am ddim. Ar gyfer Excel 2010-2013 gellir ei lawrlwytho yma , ac yn Excel 2016 mae eisoes wedi'i gynnwys yn ddiofyn. Bydd y dilyniant o gamau gweithredu fel a ganlyn:

Nid yw Power Query yn gwybod sut i weithio gyda thablau rheolaidd, felly y cam cyntaf yw troi ein bwrdd yn un “clyfar”. I wneud hyn, dewiswch ef a gwasgwch y cyfuniad Ctrl+T neu dewiswch o'r tab Cartref - Fformat fel bwrdd (Cartref - Fformat fel Tabl). Ar y tab sy'n ymddangos wedyn Constructor (Dylunio) gallwch chi osod enw'r tabl (gadewais y safon Tabl 1):

Nawr, gadewch i ni lwytho ein bwrdd i mewn i'r ategyn Power Query. I wneud hyn, ar y tab Dyddiad (os oes gennych Excel 2016) neu ar y tab Power Query (os oes gennych Excel 2010-2013) cliciwch O'r bwrdd (Data — O'r Tabl):

Yn y ffenestr golygydd ymholiad sy'n agor, dewiswch y golofn trwy glicio ar y pennawd Cwmni a gwasgwch y botwm uchod grŵp (Grŵp gan). Rhowch enw'r golofn newydd a'r math o weithrediad yn y grŵp - Pob llinell (Pob rhes):

Cliciwch OK a chawn dabl bach o werthoedd wedi'u grwpio ar gyfer pob cwmni. Mae cynnwys y tablau i'w gweld yn glir os gwnaethoch chi glicio ar gefndir gwyn y celloedd (nid ar y testun!) yn y golofn ganlynol:

Nawr, gadewch i ni ychwanegu un golofn arall, lle, gan ddefnyddio'r swyddogaeth, rydyn ni'n gludo cynnwys y colofnau Cyfeiriad ym mhob un o'r tablau bach, wedi'u gwahanu gan atalnodau. I wneud hyn, ar y tab Ychwanegu colofn rydym yn pwyso Colofn personol (Ychwanegu colofn - colofn personol) ac yn y ffenestr sy'n ymddangos, rhowch enw'r golofn newydd a'r fformiwla gyplu yn yr iaith M sydd wedi'i chynnwys yn Power Query:

Sylwch fod pob swyddogaeth M yn sensitif i achosion (yn wahanol i Excel). Ar ôl clicio ar OK rydym yn cael colofn newydd gyda chyfeiriadau wedi'u gludo:

Mae'n parhau i gael gwared ar y golofn sydd eisoes yn ddiangen Cyfeiriadau Bwrdd (cliciwch ar y dde ar y teitl) Dileu colofn) a lanlwythwch y canlyniadau i'r ddalen trwy glicio ar y tab Hafan - Cau a lawrlwytho (Cartref - Cau a llwytho):

Nuance pwysig: Yn wahanol i'r dulliau blaenorol (swyddogaethau), nid yw tablau o Power Query yn cael eu diweddaru'n awtomatig. Os bydd unrhyw newidiadau yn y data ffynhonnell yn y dyfodol, yna bydd angen i chi dde-glicio unrhyw le yn y tabl canlyniadau a dewis y gorchymyn Diweddaru ac Arbed (Adnewyddu).

• Sut i rannu llinyn testun hir yn rhannau
• Sawl ffordd o ludo testun o wahanol gelloedd yn un
• Defnyddio'r gweithredwr Hoffi i brofi testun yn erbyn mwgwd