Inhoud
Ik heb al geschreven over hoe je snel tekst uit verschillende cellen in één cel kunt plakken en omgekeerd een lange tekstreeks in componenten kunt ontleden. Laten we nu eens kijken naar een korte, maar iets complexere taak - hoe tekst uit verschillende cellen te plakken wanneer aan een bepaalde gespecificeerde voorwaarde is voldaan.
Laten we zeggen dat we een klantendatabase hebben, waarbij één bedrijfsnaam kan overeenkomen met verschillende e-mails van zijn werknemers. Het is onze taak om alle adressen op bedrijfsnaam te verzamelen en ze samen te voegen (gescheiden door komma's of puntkomma's) om bijvoorbeeld een mailinglijst voor klanten te maken, dat wil zeggen output zoiets als:
Met andere woorden, we hebben een tool nodig die de tekst zal lijmen (linken) volgens de voorwaarde - een analoog van de functie SUMMESLI (SOM ALS), maar voor tekst.
Methode 0. Formule
Niet erg elegant, maar wel de makkelijkste manier. U kunt een eenvoudige formule schrijven die controleert of het bedrijf in de volgende rij verschilt van de vorige. Als het niet verschilt, plak dan het volgende adres, gescheiden door een komma. Als het anders is, "resetten" we het verzamelde, opnieuw beginnend:
De nadelen van deze aanpak zijn duidelijk: van alle cellen van de extra verkregen kolom hebben we alleen de laatste nodig voor elk bedrijf (geel). Als de lijst groot is, moet u, om ze snel te selecteren, nog een kolom toevoegen met de functie DLSTR (LEN), controleer de lengte van de geaccumuleerde strings:
Nu kunt u die eruit filteren en de benodigde adresverlijming kopiëren voor verder gebruik.
Methode 1. Macrofunctie van lijmen door één voorwaarde
Staat de originele lijst niet op bedrijf gesorteerd, dan werkt bovenstaande simpele formule niet, maar met een kleine custom functie in VBA kom je wel makkelijk om de hoek. Open de Visual Basic Editor door op een sneltoets te drukken Alt + F11 of via de knop Visual Basic tab ontwikkelaar (Ontwikkelaar). Voeg in het geopende venster een nieuwe lege module in via het menu Invoegen – Module en kopieer de tekst van onze functie daar:
Functie MergeIf(TextRange As Range, SearchRange As Range, Condition As String) Dim Delimeter As String, i As Long Delimeter = ", " lijmingen zijn niet gelijk aan elkaar - we sluiten af met een fout If SearchRange.Count <> TextRange.Count Then MergeIf = CVErr(xlErrRef) Exit Functie End If 'ga door alle cellen, controleer de voorwaarde en verzamel de tekst in de variabele OutText For i = 1 To SearchRange. Cells.Count If SearchRange.Cells(i) Like Condition Then OutText = OutText & TextRange.Cells(i) & Delimeter Next i 'toon resultaten zonder laatste scheidingsteken MergeIf = Left(OutText, Len(OutText) - Len(Delimeter)) End functie
Als u nu terugkeert naar Microsoft Excel, dan in de lijst met functies (knop fx in de formulebalk of tab Formules – Functie invoegen) het zal mogelijk zijn om onze functie te vinden SamenvoegenAls in categorie User Defined (Gebruiker gedefinieerde). De argumenten voor de functie zijn als volgt:
Methode 2. Tekst samenvoegen op onnauwkeurige voorwaarde
Als we het eerste teken in de 13e regel van onze macro vervangen = naar de geschatte match-operator Like, dan is het mogelijk om te lijmen door een onnauwkeurige overeenkomst van de initiële gegevens met het selectiecriterium. Als de bedrijfsnaam bijvoorbeeld in verschillende varianten kan worden geschreven, dan kunnen we ze allemaal controleren en verzamelen met één functie:
Standaard jokertekens worden ondersteund:
- asterisk (*) - geeft een willekeurig aantal tekens aan (inclusief hun afwezigheid)
- vraagteken (?) – staat voor een enkel teken
- hekje (#) – staat voor een willekeurig cijfer (0-9)
Standaard is de Like-operator hoofdlettergevoelig, dat wil zeggen dat "Orion" en "orion" bijvoorbeeld als verschillende bedrijven worden beschouwd. Om hoofdletters te negeren, kunt u de regel helemaal aan het begin van de module toevoegen in de Visual Basic-editor Optie Vergelijk tekst, die zal veranderen Like om hoofdletterongevoelig te zijn.
Op deze manier kunt u zeer complexe maskers samenstellen voor het controleren van voorwaarden, bijvoorbeeld:
- ?1##??777RUS – selectie van alle kentekenplaten van de regio 777, te beginnen met 1
- LLC* – alle bedrijven waarvan de naam begint met LLC
- ##7## – alle producten met een vijfcijferige digitale code, waarbij het derde cijfer 7 . is
- ????? – alle namen van vijf letters, enz.
Methode 3. Macrofunctie voor het plakken van tekst onder twee voorwaarden
In het werk kan er een probleem zijn wanneer u de tekst meer dan één voorwaarde moet koppelen. Laten we ons bijvoorbeeld voorstellen dat in onze vorige tabel nog een kolom met de stad is toegevoegd en dat het lijmen niet alleen voor een bepaald bedrijf, maar ook voor een bepaalde stad moet worden uitgevoerd. In dit geval zal onze functie enigszins gemoderniseerd moeten worden door er nog een bereikcontrole aan toe te voegen:
Functie MergeIfs(TextRange As Range, SearchRange1 As Range, Condition1 As String, SearchRange2 As Range, Condition2 As String) Dim Delimeter As String, i As Long Delimeter = ", " 'scheidingstekens (kan worden vervangen door spatie of ; etc.) e.) 'als de validatie- en lijmbereiken niet gelijk zijn aan elkaar, sluit af met een fout If SearchRange1.Count <> TextRange.Count Of SearchRange2.Count <> TextRange.Count Then MergeIfs = CVErr(xlErrRef) Exit Function End If 'ga door alle cellen, controleer alle voorwaarden en verzamel de tekst in de variabele 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 'toon resultaten zonder laatste scheidingsteken MergeIfs = Left(OutText, Len(OutText) - Len(Delimeter)) End Functie
Het zal op precies dezelfde manier worden toegepast - alleen argumenten moeten nu meer worden gespecificeerd:
Methode 4. Groeperen en plakken in Power Query
U kunt het probleem oplossen zonder te programmeren in VBA, als u de gratis Power Query-invoegtoepassing gebruikt. Voor Excel 2010-2013 is het hier te downloaden, en in Excel 2016 is het al standaard ingebouwd. De volgorde van acties is als volgt:
Power Query weet niet hoe het met gewone tabellen moet werken, dus de eerste stap is om van onze tabel een "slimme" tabel te maken. Om dit te doen, selecteert u het en drukt u op de combinatie Ctrl+T of kies uit het tabblad Home – Opmaak als tabel (Home - Opmaken als tabel). Op het tabblad dat dan verschijnt Constructeur (Ontwerp) je kunt de tafelnaam instellen (ik heb de standaard verlaten) Tabel 1):
Laten we nu onze tabel in de Power Query-invoegtoepassing laden. Om dit te doen, op het tabblad Data (als u Excel 2016 hebt) of klik op het tabblad Power Query (als u Excel 2010-2013 hebt) Van de tafel (Gegevens — Uit tabel):
In het query-editorvenster dat wordt geopend, selecteert u de kolom door op de koptekst te klikken Bedrijf en druk op de knop hierboven Groep (groeperen op). Voer de naam van de nieuwe kolom en het type bewerking in de groepering in - Alle lijnen (Alle rijen):
Klik op OK en we krijgen een minitabel met gegroepeerde waarden voor elk bedrijf. De inhoud van de tabellen is duidelijk zichtbaar als je met de linkermuisknop op de witte achtergrond van de cellen (niet op de tekst!) in de resulterende kolom klikt:
Laten we nu nog een kolom toevoegen, waarbij we met behulp van de functie de inhoud van de adreskolommen in elk van de minitabellen plakken, gescheiden door komma's. Om dit te doen, op het tabblad Kolom toevoegen wij drukken op Aangepaste kolom (Kolom toevoegen — Aangepaste kolom) en voer in het venster dat verschijnt de naam in van de nieuwe kolom en de koppelingsformule in de M-taal die is ingebouwd in Power Query:
Merk op dat alle M-functies hoofdlettergevoelig zijn (in tegenstelling tot Excel). Na het klikken op OK we krijgen een nieuwe kolom met gelijmde adressen:
Het blijft om de toch al onnodige kolom te verwijderen TabelAdressen (rechtermuisklik op titel) Kolom verwijderen) en upload de resultaten naar het blad door op de tab . te klikken Home — Sluiten en downloaden (Home — Sluiten en laden):
Belangrijke nuance: In tegenstelling tot de vorige methoden (functies), worden tabellen uit Power Query niet automatisch bijgewerkt. Als er in de toekomst wijzigingen in de brongegevens zijn, moet u met de rechtermuisknop ergens in de resultatentabel klikken en de opdracht selecteren Bijwerken en opslaan (Vernieuwen).
- Hoe een lange tekstreeks in delen te splitsen
- Verschillende manieren om tekst uit verschillende cellen in één te plakken
- De operator Like gebruiken om tekst tegen een masker te testen