Efnisyfirlit
Ég skrifaði þegar um hvernig þú getur fljótt límt texta úr nokkrum frumum í eina og öfugt, flokkað langan textastreng í íhluti. Nú skulum við líta á loka, en örlítið flóknara verkefni - hvernig á að líma texta úr nokkrum frumum þegar tiltekið tiltekið skilyrði er uppfyllt.
Segjum að við höfum gagnagrunn yfir viðskiptavini þar sem eitt fyrirtækisnafn getur samsvarað nokkrum mismunandi tölvupóstum starfsmanna þess. Verkefni okkar er að safna öllum heimilisföngum eftir fyrirtækjanöfnum og sameina þau (aðskilin með kommum eða semíkommum) til að búa til td póstlista fyrir viðskiptavini, þ.e. fá úttak eins og:
Með öðrum orðum, við þurfum tól sem mun líma (tengja) textann í samræmi við ástandið - hliðstæða fallsins SUMMESLI (SUMIF), en fyrir texta.
Aðferð 0. Formúla
Ekki mjög glæsilegur, en auðveldasta leiðin. Þú getur skrifað einfalda formúlu sem athugar hvort fyrirtækið í næstu röð sé frábrugðið þeirri fyrri. Ef það er ekki frábrugðið, límdu þá næsta heimilisfang aðskilið með kommu. Ef það er mismunandi, þá „endurstillum“ við uppsafnaðan og byrjum aftur:
Ókostirnir við þessa nálgun eru augljósir: úr öllum frumum viðbótardálksins sem fæst, þurfum við aðeins þær síðustu fyrir hvert fyrirtæki (gult). Ef listinn er stór, þá verður þú að bæta við öðrum dálki með aðgerðinni til að velja þá fljótt DLSTR (LEN), athugaðu lengd uppsafnaðra strengja:
Nú geturðu síað þær út og afritað nauðsynlega heimilisfangslímingu til frekari notkunar.
Aðferð 1. Fjölvirkni límingar með einu skilyrði
Ef upprunalegi listinn er ekki flokkaður eftir fyrirtækjum, þá virkar ofangreind einföld formúla ekki, en þú getur auðveldlega komist um með lítilli sérsniðinni aðgerð í VBA. Opnaðu Visual Basic Editor með því að ýta á flýtilykla Alt + F11 eða með því að nota hnappinn Visual Basic flipi verktaki (hönnuður). Í glugganum sem opnast skaltu setja nýja tóma einingu í gegnum valmyndina Settu inn - Eining og afritaðu texta fallsins okkar þangað:
Fall Sameining Ef(Textsvið sem svið, leitarsvið sem svið, ástand sem strengur) Dimmt delimeter sem strengur, i eins langur delimeter = ", " límingar eru ekki jafnar hver annarri - við hættum með villu Ef SearchRange.Count <> TextRange.Count Þá MergeIf = CVErr(xlErrRef) Hætta aðgerð End Ef 'farðu í gegnum allar frumur, athugaðu ástandið og safnaðu textanum í breytunni OutText For i = 1 To SearchRange. Cells.Count If SearchRange.Cells(i) Like Condition Then OutText = OutText & TextRange.Cells(i) & Delimeter Next i 'birta niðurstöður án síðasta afmörkunar SameinaIf = Left(OutText, Len(OutText) - Len(Delimeter)) End virka
Ef þú ferð aftur í Microsoft Excel, þá á listanum yfir aðgerðir (hnappur fx í formúlustikunni eða flipanum Formúlur – Insert Function) það verður hægt að finna aðgerðina okkar SameinaEf í flokki Notandi skilgreindur (Notandi skilgreindur). Rökin fyrir fallinu eru sem hér segir:
Aðferð 2. Sameina texta eftir ónákvæmu ástandi
Ef við skiptum út fyrsta stafnum í 13. línunni í fjölvi okkar = til áætlaðs leikstjóra eins, þá verður hægt að framkvæma límingu með ónákvæmri samsvörun fyrstu gagna við valviðmiðið. Til dæmis, ef hægt er að skrifa nafn fyrirtækis í mismunandi afbrigðum, þá getum við athugað og safnað þeim öllum með einni aðgerð:
Stöðluð jokertákn eru studd:
- stjörnu (*) – táknar hvaða fjölda stafa sem er (þar á meðal fjarvera þeirra)
- spurningarmerki (?) – stendur fyrir hvern einasta staf
- pundamerki (#) – stendur fyrir einn tölustaf (0-9)
Sjálfgefið er að rekstraraðili Like er hástafaviðkvæmur, þ.e. skilur til dæmis „Orion“ og „orion“ sem mismunandi fyrirtæki. Til að hunsa tilfelli geturðu bætt við línunni í byrjun einingarinnar í Visual Basic ritlinum Valkostur Berðu saman texta, sem mun skipta á Like til að vera hástafaónæmir.
Á þennan hátt geturðu samið mjög flóknar grímur til að athuga aðstæður, til dæmis:
- ?1##??777RUS – úrval af öllum númeraplötum á 777 svæðinu, byrjar á 1
- LLC* - öll fyrirtæki með nafn sem byrjar á LLC
- ##7## – allar vörur með fimm stafa stafrænum kóða, þar sem þriðji talan er 7
- ????? – öll nöfn fimm stafa o.s.frv.
Aðferð 3. Fjölvafall til að líma texta við tvær aðstæður
Í verkinu getur verið vandamál þegar tengja þarf textann við fleiri en eitt skilyrði. Við skulum til dæmis ímynda okkur að í fyrri töflunni okkar hafi verið bætt við einum dálki til viðbótar með borginni og líming ætti ekki aðeins að fara fram fyrir tiltekið fyrirtæki, heldur einnig fyrir tiltekna borg. Í þessu tilviki verður að nútímavæða aðgerðina okkar örlítið með því að bæta öðru sviðsávísun við hana:
Aðgerð MergeIfs(TextRange As Range, SearchRange1 As Range, Condition1 As String, SearchRange2 As Range, Condition2 As String) Dim delimmeter As String, i As Long Delimeter = ", " 'askilunarstafir (hægt að skipta út fyrir bil eða ; o.s.frv.) e.) 'ef löggildingar- og límingarsviðin eru ekki jöfn hvort öðru, farðu út með villu Ef SearchRange1.Count <> TextRange.Count Eða SearchRange2.Count <> TextRange.Count Then MergeIfs = CVErr(xlErrRef) Hætta aðgerð End If 'farðu í gegnum allar frumur, athugaðu öll skilyrði og safnaðu textanum í breytuna OutText For i = 1 To SearchRange1.Cells.Count If SearchRange1.Cells(i) = Condition1 Og SearchRange2.Cells(i) = Condition2 Then OutText = OutText & TextRange.Cells(i) & Delimeter End If Next i 'birta niðurstöður án síðasta afmörkunar MergeIfs = Left(OutText, Len(OutText) - Len(Delimeter)) End Function
Það verður beitt á nákvæmlega sama hátt - aðeins þarf að tilgreina rök núna:
Aðferð 4. Flokkun og líming í Power Query
Þú getur leyst vandamálið án þess að forrita í VBA, ef þú notar ókeypis Power Query viðbótina. Fyrir Excel 2010-2013 er hægt að hlaða því niður hér, og í Excel 2016 er það nú þegar innbyggt sjálfgefið. Röð aðgerða verður sem hér segir:
Power Query veit ekki hvernig á að vinna með venjulegar töflur, svo fyrsta skrefið er að breyta borðinu okkar í „snjallt“ borð. Til að gera þetta, veldu það og ýttu á samsetninguna Ctrl+T eða veldu af flipanum Heim - Snið sem töflu (Heima - Snið sem töflu). Á flipanum sem þá birtist Framkvæmdaaðili (Hönnun) þú getur stillt töfluheitið (ég skildi eftir staðalinn Tafla 1):
Nú skulum við hlaða töflunni okkar inn í Power Query viðbótina. Til að gera þetta, á flipanum Gögn (ef þú ert með Excel 2016) eða á Power Query flipanum (ef þú ert með Excel 2010-2013) smelltu á Frá borði (Gögn — úr töflu):
Í fyrirspurnarritaraglugganum sem opnast velurðu dálkinn með því að smella á hausinn fyrirtæki og ýttu á hnappinn hér að ofan Group (Hópur eftir). Sláðu inn heiti nýja dálksins og gerð aðgerðarinnar í hópnum – Allar línur (Allar raðir):
Smelltu á OK og við fáum smátöflu yfir flokkuð gildi fyrir hvert fyrirtæki. Innihald taflnanna sést vel ef þú vinstrismellir á hvíta bakgrunninn í reitunum (ekki á textann!) í dálkinum sem myndast:
Nú skulum við bæta við einum dálki til viðbótar, þar sem, með því að nota aðgerðina, límum við innihald Address dálkanna í hverja smátöflu, aðskilin með kommum. Til að gera þetta, á flipanum Bæta við dálki við ýtum á Sérsniðinn dálkur (Bæta við dálki - sérsniðinn dálkur) og í glugganum sem birtist skaltu slá inn nafn nýja dálksins og tengiformúluna á M tungumálinu sem er innbyggt í Power Query:
Athugaðu að allar M-föll eru hástafanæmir (ólíkt Excel). Eftir að hafa smellt á OK við fáum nýjan dálk með límdum heimilisföngum:
Það er eftir að fjarlægja þegar óþarfa dálkinn TableAddresses (hægri smelltu á titil) Eyða dálki) og hlaðið niðurstöðunum inn á blaðið með því að smella á flipann Heima — Loka og hlaða niður (Heima - Loka og hlaða):
Mikilvæg blæbrigði: Ólíkt fyrri aðferðum (aðgerðum), eru töflur frá Power Query ekki uppfærðar sjálfkrafa. Ef í framtíðinni verða einhverjar breytingar á upprunagögnunum, þá þarftu að hægrismella hvar sem er í niðurstöðutöflunni og velja skipunina Uppfærðu og vistaðu (Endurnýja).
- Hvernig á að skipta löngum textastreng í hluta
- Nokkrar leiðir til að líma texta úr mismunandi frumum í eina
- Notkun Like-aðgerðarinnar til að prófa texta á móti grímu