Indhold
Jeg har allerede skrevet om, hvordan du hurtigt kan lime tekst fra flere celler ind i én og omvendt parse en lang tekststreng til komponenter. Lad os nu se på en tæt, men lidt mere kompleks opgave – hvordan man limer tekst fra flere celler, når en bestemt specificeret betingelse er opfyldt.
Lad os sige, at vi har en database med kunder, hvor ét firmanavn kan svare til flere forskellige e-mails fra dets medarbejdere. Vores opgave er at samle alle adresser efter firmanavne og sammenkæde dem (adskilt med kommaer eller semikolon) for at lave f.eks. en mailingliste til kunder, dvs. få output noget som:
Med andre ord har vi brug for et værktøj, der limer (linker) teksten i henhold til betingelsen - en analog af funktionen SUMMESLI (SUMIF), men til tekst.
Metode 0. Formel
Ikke særlig elegant, men den nemmeste måde. Du kan skrive en simpel formel, der kontrollerer, om virksomheden i næste række adskiller sig fra den foregående. Hvis det ikke adskiller sig, så lim den næste adresse adskilt af et komma. Hvis det afviger, så "nulstiller" vi det akkumulerede og starter igen:
Ulemperne ved denne tilgang er indlysende: Fra alle cellerne i den opnåede ekstra kolonne har vi kun brug for de sidste for hver virksomhed (gul). Hvis listen er stor, skal du tilføje en anden kolonne ved hjælp af funktionen for hurtigt at vælge dem DLSTR (LEN), kontrollerer længden af de akkumulerede strenge:
Nu kan du filtrere dem fra og kopiere den nødvendige adresselimning til videre brug.
Metode 1. Makrofunktion af limning ved én betingelse
Hvis den originale liste ikke er sorteret efter firma, så virker ovenstående simple formel ikke, men du kan nemt komme rundt med en lille brugerdefineret funktion i VBA. Åbn Visual Basic Editor ved at trykke på en tastaturgenvej Alt + F11 eller ved at bruge knappen Visual Basic fanen developer (Udvikler). Indsæt et nyt tomt modul gennem menuen i vinduet, der åbnes Indsæt – Modul og kopier teksten til vores funktion der:
Funktion MergeIf(TextRange As Range, SearchRange As Range, Condition As String) Dim Delimeter As String, i As Long Delimeter = ", " limninger er ikke lig med hinanden - vi afslutter med en fejl Hvis SearchRange.Count <> TextRange.Count Så MergeIf = CVErr(xlErrRef) Afslut Funktion End Hvis 'gå gennem alle cellerne, tjek betingelsen og saml teksten i variablen OutText For i = 1 To SearchRange. Cells.Count If SearchRange.Cells(i) Like Condition Then OutText = OutText & TextRange.Cells(i) & Delimeter Next i 'viser resultater uden sidste delimiter MergeIf = Left(OutText, Len(OutText) - Len(Delimeter)) End fungere
Hvis du nu vender tilbage til Microsoft Excel, så i listen over funktioner (knap fx i formellinjen eller fanen Formler – Indsæt funktion) vil det være muligt at finde vores funktion MergeIf i kategori Brugerdefineret (Brugerdefineret). Argumenterne til funktionen er som følger:
Metode 2. Sammenkæd tekst efter upræcis tilstand
Hvis vi erstatter det første tegn i 13. linje i vores makro = til den omtrentlige kampoperatør lignende, så vil det være muligt at udføre limning ved en upræcis match af de indledende data med udvælgelseskriteriet. For eksempel, hvis firmanavnet kan skrives i forskellige varianter, så kan vi kontrollere og samle dem alle med én funktion:
Standard jokertegn understøttes:
- stjerne (*) – angiver et vilkårligt antal tegn (inklusive deres fravær)
- spørgsmålstegn (?) – står for ethvert enkelt tegn
- pundtegn (#) – står for et vilkårligt ciffer (0-9)
Som standard er Like-operatøren skelet mellem store og små bogstaver, dvs. forstår f.eks. "Orion" og "orion" som forskellige virksomheder. For at ignorere store og små bogstaver kan du tilføje linjen helt i begyndelsen af modulet i Visual Basic-editoren Mulighed Sammenlign tekst, som vil skifte Like til at være ufølsom mellem store og små bogstaver.
På denne måde kan du sammensætte meget komplekse masker til kontrol af forhold, for eksempel:
- ?1##??777RUS – udvalg af alle nummerplader i 777-regionen, startende med 1
- LLC* – alle virksomheder, hvis navn begynder med LLC
- ##7## – alle produkter med en femcifret digital kode, hvor det tredje ciffer er 7
- ????? – alle navne på fem bogstaver osv.
Metode 3. Makrofunktion til limning af tekst under to forhold
I værket kan der være et problem, når du skal sammenkæde teksten mere end én betingelse. Lad os for eksempel forestille os, at der i vores tidligere tabel blev tilføjet en kolonne mere med byen, og limning skal udføres ikke kun for en given virksomhed, men også for en given by. I dette tilfælde skal vores funktion moderniseres en smule ved at tilføje endnu en rækkekontrol til den:
Funktion 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 tegn (kan erstattes med mellemrum eller ; osv.) e.) 'hvis validerings- og limningsintervallerne ikke er lig med hinanden, afsluttes med en fejl Hvis SearchRange1.Count <> TextRange.Count Eller SearchRange2.Count <> TextRange.Count Then MergeIfs = CVErr(xlErrRef) Afslut funktion End If 'gå alle celler igennem, tjek alle betingelser og saml teksten i variablen 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 'viser resultater uden sidste delimiter MergeIfs = Left(OutText, Len(OutText) - Len(Delimeter)) End-funktion
Det vil blive anvendt på nøjagtig samme måde - kun argumenter skal nu specificeres mere:
Metode 4. Gruppering og limning i Power Query
Du kan løse problemet uden at programmere i VBA, hvis du bruger det gratis Power Query-tilføjelsesprogram. Til Excel 2010-2013 kan den downloades her, og i Excel 2016 er den allerede indbygget som standard. Rækkefølgen af handlinger vil være som følger:
Power Query ved ikke, hvordan man arbejder med almindelige tabeller, så det første skridt er at gøre vores tabel til en "smart" en. For at gøre dette skal du vælge det og trykke på kombinationen Ctrl+T eller vælg fra fanen Hjem – Formater som en tabel (Hjem — Formater som tabel). På den fane, der så vises Constructor (Design) du kan indstille tabelnavnet (jeg forlod standarden Tabel 1):
Lad os nu indlæse vores tabel i Power Query-tilføjelsesprogrammet. For at gøre dette, på fanen data (hvis du har Excel 2016) eller på fanen Power Query (hvis du har Excel 2010-2013) klik Fra bordet (Data – fra tabel):
I forespørgselsredigeringsvinduet, der åbnes, skal du vælge kolonnen ved at klikke på overskriften Om os og tryk på knappen ovenfor gruppe (Gruppe efter). Indtast navnet på den nye kolonne og typen af operation i grupperingen – Alle linjer (Alle rækker):
Klik på OK, og vi får en minitabel med grupperede værdier for hver virksomhed. Indholdet af tabellerne er tydeligt synligt, hvis du venstreklikker på den hvide baggrund af cellerne (ikke på teksten!) i den resulterende kolonne:
Lad os nu tilføje en kolonne mere, hvor vi ved hjælp af funktionen limer indholdet af adressekolonnerne i hver af minitabellerne adskilt af kommaer. For at gøre dette, på fanen Tilføj kolonne vi trykker på Brugerdefineret kolonne (Tilføj kolonne – tilpasset kolonne) og i vinduet, der vises, skal du indtaste navnet på den nye kolonne og koblingsformlen på M-sproget, der er indbygget i Power Query:
Bemærk, at alle M-funktioner skelner mellem store og små bogstaver (i modsætning til Excel). Efter at have klikket på OK vi får en ny kolonne med påklistrede adresser:
Det er tilbage at fjerne den allerede unødvendige kolonne Tabeladresser (højreklik på titlen) Slet kolonne) og upload resultaterne til arket ved at klikke på fanen Hjem — Luk og download (Hjem - Luk og indlæs):
Vigtig nuance: I modsætning til de tidligere metoder (funktioner) opdateres tabeller fra Power Query ikke automatisk. Hvis der i fremtiden vil være ændringer i kildedataene, skal du højreklikke hvor som helst i resultattabellen og vælge kommandoen Opdater & Gem (Opdater).
- Sådan opdeles en lang tekststreng i dele
- Flere måder at lime tekst fra forskellige celler ind i én
- Brug af Like-operatoren til at teste tekst mod en maske