vsebina
Pisal sem že o tem, kako lahko hitro lepite besedilo iz več celic v eno in, nasprotno, razčlenite dolg besedilni niz na komponente. Zdaj pa poglejmo bližnjo, a nekoliko bolj zapleteno nalogo – kako zlepiti besedilo iz več celic, ko je izpolnjen določen pogoj.
Recimo, da imamo bazo strank, kjer lahko eno ime podjetja ustreza več različnim elektronskim naslovom zaposlenih. Naša naloga je, da zberemo vse naslove po imenih podjetij in jih povežemo (ločene z vejicami ali podpičji), da naredimo na primer poštni seznam za stranke, tj. dobimo nekaj takega:
Z drugimi besedami, potrebujemo orodje, ki bo lepilo (povezalo) besedilo glede na pogoj – analog funkcije SUMMESLI (SUMIF), ampak za besedilo.
Metoda 0. Formula
Ni zelo eleganten, a najlažji način. Lahko napišete preprosto formulo, ki bo preverila, ali se podjetje v naslednji vrstici razlikuje od prejšnjega. Če se ne razlikuje, prilepite naslednji naslov, ločen z vejico. Če se razlikuje, potem "ponastavimo" nakopičeno in začnemo znova:
Slabosti tega pristopa so očitne: od vseh dobljenih celic dodatnega stolpca potrebujemo le zadnje za vsako podjetje (rumeno). Če je seznam velik, boste morali za hitro izbiro dodati še en stolpec s funkcijo DLSTR (LEN), preverjanje dolžine zbranih nizov:
Zdaj lahko filtrirate tiste in kopirate potrebno lepljenje naslovov za nadaljnjo uporabo.
Metoda 1. Makrofunkcija lepljenja z enim pogojem
Če prvotni seznam ni razvrščen po podjetjih, zgornja preprosta formula ne deluje, vendar se lahko zlahka premikate z majhno funkcijo po meri v VBA. Odprite urejevalnik Visual Basic s pritiskom na bližnjico na tipkovnici Alt + F11 ali z uporabo gumba Visual Basic tab razvijalec (Razvijalec). V oknu, ki se odpre, skozi meni vstavite nov prazen modul Vstavi – Modul in tja kopirajte besedilo naše funkcije:
Funkcija MergeIf(TextRange As Range, SearchRange As Range, Condition As String) Dim Delimeter As String, i As Long Delimeter = ", " lepljenja med seboj niso enaka - izstopimo z napako If SearchRange.Count <> TextRange.Count Nato MergeIf = CVErr(xlErrRef) Izhod iz funkcije End If 'pojdite skozi vse celice, preverite pogoj in zberite besedilo v spremenljivki OutText For i = 1 To SearchRange. Cells.Count If SearchRange.Cells(i) Like Condition Then OutText = OutText & TextRange.Cells(i) & Delimeter Next i 'prikaži rezultate brez zadnjega ločila MergeIf = Left(OutText, Len(OutText) - Len(Delimeter)) End funkcijo
Če se zdaj vrnete v Microsoft Excel, potem na seznamu funkcij (gumb fx v vrstici s formulami ali zavihku Formule – Vstavi funkcijo) bo mogoče najti našo funkcijo MergeIf v kategoriji Uporabnik definiran (Uporabnik definiran). Argumenti funkcije so naslednji:
Metoda 2. Povežite besedilo z nenatančnim pogojem
Če zamenjamo prvi znak v 13. vrstici našega makra = operaterju približnega ujemanja Kot, potem bo mogoče izvesti lepljenje z nenatančnim ujemanjem začetnih podatkov z izbirnim merilom. Na primer, če je ime podjetja mogoče zapisati v različnih variantah, potem jih lahko vse preverimo in zberemo z eno funkcijo:
Podprti so standardni nadomestni znaki:
- zvezdica (*) – označuje poljubno število poljubnih znakov (vključno z njihovo odsotnostjo)
- vprašaj (?) – pomeni kateri koli posamezen znak
- znak za funt (#) – pomeni eno številko (0-9)
Operater Like je privzeto občutljiv na velike in male črke, kar pomeni, da na primer »Orion« in »orion« razume kot različni podjetji. Če želite prezreti velike in male črke, lahko dodate vrstico na samem začetku modula v urejevalniku Visual Basic Možnost Primerjaj besedilo, ki bo preklopil Like tako, da bo neobčutljiv na velike in male črke.
Na ta način lahko sestavite zelo zapletene maske za preverjanje pogojev, na primer:
- ?1##??777RUS – izbor vseh registrskih tablic regije 777, začenši z 1
- LLC* – vsa podjetja, katerih ime se začne z LLC
- ##7## – vsi izdelki s petmestno digitalno kodo, kjer je tretja številka 7
- ?????? – vsa imena iz petih črk itd.
Metoda 3. Makro funkcija za lepljenje besedila pod dvema pogojema
Pri delu lahko pride do težave, ko morate besedilo povezati z več kot enim pogojem. Na primer, predstavljajmo si, da je bil v naši prejšnji tabeli dodan še en stolpec z mestom, lepljenje pa je treba izvesti ne samo za dano podjetje, ampak tudi za dano mesto. V tem primeru bo treba našo funkcijo nekoliko posodobiti tako, da ji dodamo še eno preverjanje obsega:
Funkcija MergeIfs(TextRange kot obseg, SearchRange1 kot obseg, Condition1 kot niz, SearchRange2 kot obseg, Condition2 kot niz) Dim Delimeter kot niz, i kot dolg delimeter = ", " 'ločilni znaki (lahko jih zamenjate s presledkom ali ; itd.) e.) 'če obsega preverjanja in lepljenja nista enaka drug drugemu, zapustite z napako If SearchRange1.Count <> TextRange.Count Or SearchRange2.Count <> TextRange.Count Then MergeIfs = CVErr(xlErrRef) Exit Function End If 'pojdite skozi vse celice, preverite vse pogoje in zberite besedilo v spremenljivko OutText For i = 1 To SearchRange1.Cells.Count If SearchRange1.Cells(i) = Condition1 And SearchRange2.Cells(i) = Condition2 Then OutText = OutText & TextRange.Cells(i) & Delimeter End If Next i 'prikaži rezultate brez zadnjega ločila MergeIfs = Left(OutText, Len(OutText) - Len(Delimeter)) End Function
Uporabljeno bo na povsem enak način – le argumente je treba zdaj podrobneje določiti:
4. način. Združevanje in lepljenje v Power Query
Težavo lahko rešite brez programiranja v VBA, če uporabite brezplačen dodatek Power Query. Za Excel 2010-2013 ga lahko prenesete tukaj, v Excelu 2016 pa je že privzeto vgrajen. Zaporedje dejanj bo naslednje:
Power Query ne zna delati z običajnimi tabelami, zato je prvi korak, da svojo tabelo spremenimo v »pametno«. Če želite to narediti, ga izberite in pritisnite kombinacijo Ctrl+T ali izberite na zavihku Domov – Oblikuj kot tabelo (Domov — Oblikuj kot tabelo). Na zavihku, ki se nato prikaže Konstruktor (Oblikovanje) lahko nastavite ime tabele (pustil sem standard Tabela 1):
Zdaj pa naložimo našo tabelo v dodatek Power Query. Če želite to narediti, na zavihku datum (če imate Excel 2016) ali na zavihku Power Query (če imate Excel 2010-2013) kliknite Iz mize (Podatki — iz tabele):
V oknu urejevalnika poizvedb, ki se odpre, izberite stolpec s klikom na glavo Podjetje in pritisnite zgornji gumb skupina (Združi po). Vnesite ime novega stolpca in vrsto operacije v skupino – Vse vrstice (Vse vrstice):
Kliknite V redu in dobili bomo mini tabelo združenih vrednosti za vsako podjetje. Vsebina tabel je jasno vidna, če levo kliknete na belo ozadje celic (ne na besedilo!) v nastalem stolpcu:
Zdaj pa dodamo še en stolpec, kjer s pomočjo funkcije zlepimo vsebino stolpcev Naslov v vsako od mini tabel, ločenih z vejicami. Če želite to narediti, na zavihku Dodaj stolpec pritisnemo Stolpec po meri (Dodaj stolpec — stolpec po meri) in v okno, ki se prikaže, vnesite ime novega stolpca in formulo za spajanje v jeziku M, vgrajenem v Power Query:
Upoštevajte, da so vse M-funkcije občutljive na velike in male črke (za razliko od Excela). Po kliku na OK dobimo nov stolpec z zlepljenimi naslovi:
Ostaja še odstranitev že nepotrebnega stolpca Naslovi tabele (desni klik na naslov) Izbriši stolpec) in naložite rezultate na list s klikom na zavihek Domov — Zaprite in prenesite (Domov — Zapri in naloži):
Pomemben odtenek: Za razliko od prejšnjih metod (funkcij) se tabele iz Power Query ne posodabljajo samodejno. Če bo v prihodnosti prišlo do kakršnih koli sprememb v izvornih podatkih, boste morali z desno tipko miške klikniti kjer koli v tabeli rezultatov in izbrati ukaz Posodobi in shrani (Osveži).
- Kako razdeliti dolg besedilni niz na dele
- Več načinov za lepljenje besedila iz različnih celic v eno
- Uporaba operatorja Like za testiranje besedila glede na masko