Innhold
Jeg har allerede skrevet om hvordan du raskt kan lime tekst fra flere celler til én og omvendt analysere en lang tekststreng til komponenter. La oss nå se på en nær, men litt mer kompleks oppgave - hvordan lime tekst fra flere celler når en viss spesifisert betingelse er oppfylt.
La oss si at vi har en database med kunder, der ett firmanavn kan tilsvare flere forskjellige e-poster til de ansatte. Vår oppgave er å samle alle adressene etter firmanavn og sette dem sammen (atskilt med komma eller semikolon) for å lage for eksempel en e-postliste for kunder, dvs. få utdata som:
Med andre ord trenger vi et verktøy som limer (lenker) teksten i henhold til tilstanden – en analog av funksjonen SUMMESLI (SUMIF), men for tekst.
Metode 0. Formel
Ikke veldig elegant, men den enkleste måten. Du kan skrive en enkel formel som vil sjekke om selskapet i neste rad skiller seg fra den forrige. Hvis det ikke er forskjellig, lim neste adresse atskilt med komma. Hvis det er forskjellig, "tilbakestiller" vi det akkumulerte, og starter på nytt:
Ulempene med denne tilnærmingen er åpenbare: fra alle cellene i den ekstra kolonnen som er oppnådd, trenger vi bare de siste for hvert selskap (gul). Hvis listen er stor, må du legge til en annen kolonne ved å bruke funksjonen for raskt å velge dem DLSTR (LENN), sjekke lengden på de akkumulerte strengene:
Nå kan du filtrere ut de og kopiere nødvendig adresseliming for videre bruk.
Metode 1. Makrofunksjon av liming ved en betingelse
Hvis den opprinnelige listen ikke er sortert etter selskap, fungerer ikke ovennevnte enkle formel, men du kan enkelt komme deg rundt med en liten egendefinert funksjon i VBA. Åpne Visual Basic Editor ved å trykke på en hurtigtast Alt + F11 eller ved å bruke knappen Visual Basic tab utvikler (Utvikler). I vinduet som åpnes setter du inn en ny tom modul gjennom menyen Sett inn – modul og kopier teksten til funksjonen vår der:
Funksjon MergeIf(TextRange As Range, SearchRange As Range, Condition As String) Dim Delimeter As String, i As Long Delimeter = ", " liminger er ikke lik hverandre - vi avslutter med en feil Hvis SearchRange.Count <> TextRange.Count Da MergeIf = CVErr(xlErrRef) Avslutt funksjon Slutt Hvis 'gå gjennom alle cellene, sjekk betingelsen og samle teksten i variabelen OutText For i = 1 To SearchRange. Cells.Count If SearchRange.Cells(i) Like Condition Then OutText = OutText & TextRange.Cells(i) & Delimeter Next i 'vis resultater uten siste skilletegn MergeIf = Left(OutText, Len(OutText) - Len(Delimeter)) End funksjon
Hvis du nå går tilbake til Microsoft Excel, så i listen over funksjoner (knapp fx i formellinjen eller kategorien Formler – Sett inn funksjon) vil det være mulig å finne vår funksjon MergeIf i kategori Brukerdefinert (Brukerdefinert). Argumentene til funksjonen er som følger:
Metode 2. Sammenslå tekst etter unøyaktig tilstand
Hvis vi erstatter det første tegnet i den 13. linjen i makroen vår = til den omtrentlige kampoperatøren I likhet med, da vil det være mulig å utføre liming ved en unøyaktig match av de første dataene med utvalgskriteriet. For eksempel, hvis firmanavnet kan skrives i forskjellige varianter, kan vi sjekke og samle dem alle med én funksjon:
Standard jokertegn støttes:
- stjerne (*) – angir et hvilket som helst antall tegn (inkludert deres fravær)
- spørsmålstegn (?) – står for et enkelt tegn
- pundtegn (#) – står for et siffer (0-9)
Som standard er Like-operatøren skille mellom store og små bokstaver, dvs. forstår for eksempel "Orion" og "orion" som forskjellige selskaper. For å ignorere store og små bokstaver, kan du legge til linjen helt i begynnelsen av modulen i Visual Basic-editoren Alternativ Sammenlign tekst, som vil bytte Like til å være ufølsom for store og små bokstaver.
På denne måten kan du komponere svært komplekse masker for å kontrollere forhold, for eksempel:
- ?1##??777RUS – utvalg av alle skilt i 777-regionen, starter med 1
- LLC* – alle selskaper hvis navn begynner med LLC
- ##7## – alle produkter med en femsifret digital kode, der det tredje sifferet er 7
- ????? – alle navn på fem bokstaver osv.
Metode 3. Makrofunksjon for liming av tekst under to forhold
I arbeidet kan det oppstå et problem når du skal knytte teksten til mer enn én betingelse. La oss for eksempel forestille oss at i vår forrige tabell ble det lagt til en kolonne til med byen, og liming skal utføres ikke bare for et gitt selskap, men også for en gitt by. I dette tilfellet må funksjonen vår moderniseres litt ved å legge til en annen rekkeviddesjekk:
Funksjon MergeIfs(TextRange As Range, SearchRange1 As Range, Condition1 As String, SearchRange2 As Range, Condition2 As String) Dim Delimeter As String, i As Long Delimeter = ", " 'skilletegn (kan erstattes med mellomrom eller ; etc.) e.) 'hvis validerings- og limingsområdene ikke er lik hverandre, avslutt med en feil If SearchRange1.Count <> TextRange.Count Eller SearchRange2.Count <> TextRange.Count Then MergeIfs = CVErr(xlErrRef) Exit Function End If 'gå gjennom alle cellene, sjekk alle betingelser og samle teksten inn i variabelen 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 uten siste skilletegn MergeIfs = Left(OutText, Len(OutText) - Len(Delimeter)) End-funksjon
Det vil bli brukt på nøyaktig samme måte - bare argumenter må nå spesifiseres mer:
Metode 4. Gruppering og liming i Power Query
Du kan løse problemet uten å programmere i VBA, hvis du bruker det gratis Power Query-tillegget. For Excel 2010-2013 kan den lastes ned her, og i Excel 2016 er den allerede innebygd som standard. Rekkefølgen av handlinger vil være som følger:
Power Query vet ikke hvordan man jobber med vanlige tabeller, så det første trinnet er å gjøre bordet vårt til et "smart". For å gjøre dette, velg den og trykk på kombinasjonen Ctrl+T eller velg fra fanen Hjem – Formater som en tabell (Hjem — Formater som tabell). På fanen som da vises Constructor (Design) du kan angi tabellnavnet (jeg forlot standarden Tabell 1):
La oss nå laste inn tabellen i Power Query-tillegget. For å gjøre dette, på fanen Data (hvis du har Excel 2016) eller på Power Query-fanen (hvis du har Excel 2010-2013) klikker du Fra bordet (Data – fra tabell):
I spørringsredigeringsvinduet som åpnes, velg kolonnen ved å klikke på overskriften Organisasjon og trykk på knappen ovenfor Gruppe (Gruppe av). Skriv inn navnet på den nye kolonnen og typen operasjon i grupperingen – Alle linjer (Alle rader):
Klikk OK og vi får en minitabell med grupperte verdier for hvert selskap. Innholdet i tabellene er godt synlig hvis du venstreklikker på den hvite bakgrunnen til cellene (ikke på teksten!) i den resulterende kolonnen:
La oss nå legge til en kolonne til, der vi ved hjelp av funksjonen limer innholdet i adressekolonnene i hver av minitabellene, atskilt med komma. For å gjøre dette, på fanen Legg til kolonne vi trykker Egendefinert kolonne (Legg til kolonne – egendefinert kolonne) og i vinduet som vises, skriv inn navnet på den nye kolonnen og koblingsformelen på M-språket innebygd i Power Query:
Merk at alle M-funksjoner skiller mellom store og små bokstaver (i motsetning til Excel). Etter å ha klikket på OK vi får en ny kolonne med limte adresser:
Det gjenstår å fjerne den allerede unødvendige kolonnen Tabelladresser (høyreklikk på tittelen) Slett kolonne) og last opp resultatene til arket ved å klikke på fanen Hjem — Lukk og last ned (Hjem — Lukk og last):
Viktig nyanse: I motsetning til de tidligere metodene (funksjonene), oppdateres ikke tabeller fra Power Query automatisk. Hvis det i fremtiden vil være endringer i kildedataene, må du høyreklikke hvor som helst i resultattabellen og velge kommandoen Oppdater og lagre (Forfriske).
- Hvordan dele opp en lang tekststreng i deler
- Flere måter å lime tekst fra forskjellige celler til én
- Bruke Like-operatoren til å teste tekst mot en maske