Inhalte
Ich habe bereits darüber geschrieben, wie Sie Text aus mehreren Zellen schnell in eine Zelle zusammenfügen und umgekehrt eine lange Textzeichenfolge in Komponenten zerlegen können. Schauen wir uns nun eine ähnliche, aber etwas komplexere Aufgabe an – wie man Text aus mehreren Zellen zusammenfügt, wenn eine bestimmte festgelegte Bedingung erfüllt ist.
Nehmen wir an, wir haben eine Kundendatenbank, in der ein Firmenname mehreren verschiedenen E-Mail-Adressen seiner Mitarbeiter entsprechen kann. Unsere Aufgabe ist es, alle Adressen nach Firmennamen zu sammeln und zu verketten (getrennt durch Kommas oder Semikolons), um beispielsweise eine Mailingliste für Kunden zu erstellen, dh eine Ausgabe wie folgt zu erhalten:
Mit anderen Worten, wir brauchen ein Werkzeug, das den Text gemäß der Bedingung klebt (verlinkt) – ein Analogon der Funktion SUMMESLI (SUMME), aber für Text.
Methode 0. Formel
Nicht sehr elegant, aber der einfachste Weg. Sie können eine einfache Formel schreiben, die überprüft, ob sich die Firma in der nächsten Zeile von der vorherigen unterscheidet. Wenn es sich nicht unterscheidet, dann kleben Sie die nächste Adresse durch ein Komma getrennt. Wenn es sich unterscheidet, setzen wir die kumulierten Werte zurück und beginnen von vorne:
Die Nachteile dieses Vorgehens liegen auf der Hand: Von allen erhaltenen Zellen der zusätzlichen Spalte benötigen wir nur die letzten für jedes Unternehmen (gelb). Wenn die Liste groß ist, müssen Sie mit der Funktion eine weitere Spalte hinzufügen, um sie schnell auswählen zu können DLSTR (LEN), Überprüfung der Länge der akkumulierten Zeichenfolgen:
Nun können Sie diese herausfiltern und die notwendige Adressverklebung zur weiteren Verwendung kopieren.
Methode 1. Makrofunktion des Klebens durch eine Bedingung
Wenn die ursprüngliche Liste nicht nach Unternehmen sortiert ist, dann funktioniert die obige einfache Formel nicht, aber Sie können mit einer kleinen benutzerdefinierten Funktion in VBA leicht umgehen. Öffnen Sie den Visual Basic-Editor, indem Sie eine Tastenkombination drücken Alt + F11 oder über die Schaltfläche Visual Basic Tab Entwickler (Entwickler). Fügen Sie im sich öffnenden Fenster über das Menü ein neues leeres Modul ein Einfügen – Modul und kopiere dort den Text unserer Funktion:
Funktion MergeIf(TextRange As Range, SearchRange As Range, Condition As String) Dim Delimeter As String, i As Long Delimeter = ", " Kleben sind nicht gleich - wir enden mit einem Fehler If SearchRange.Count <> TextRange.Count Then MergeIf = CVErr(xlErrRef) Exit Function End If 'alle Zellen durchlaufen, Bedingung prüfen und Text in Variable OutText sammeln For i = 1 To SearchRange. Cells.Count If SearchRange.Cells(i) Like Condition Then OutText = OutText & TextRange.Cells(i) & Delimeter Next i 'Ergebnisse ohne letztes Trennzeichen anzeigen MergeIf = Left(OutText, Len(OutText) - Len(Delimeter)) End Funktion
Wenn Sie nun zu Microsoft Excel zurückkehren, dann ist in der Liste der Funktionen (Button fx in der Formelleiste oder Registerkarte Formeln – Funktion einfügen) wird es möglich sein, unsere Funktion zu finden MergeIf in der Kategorie Benutzerdefiniert (Benutzerdefinierten). Die Argumente für die Funktion lauten wie folgt:
Methode 2. Text durch ungenaue Bedingung verketten
Wenn wir das erste Zeichen in der 13. Zeile unseres Makros ersetzen = zum Näherungs-Match-Operator Like, dann kann durch eine ungenaue Übereinstimmung der Ausgangsdaten mit dem Auswahlkriterium eine Verklebung durchgeführt werden. Wenn zum Beispiel der Firmenname in verschiedenen Varianten geschrieben werden kann, dann können wir diese alle mit einer Funktion prüfen und sammeln:
Standardplatzhalter werden unterstützt:
- Stern (*) – bezeichnet eine beliebige Anzahl beliebiger Zeichen (einschließlich deren Fehlen)
- Fragezeichen (?) – steht für ein beliebiges einzelnes Zeichen
- Nummernzeichen (#) – steht für eine beliebige Ziffer (0-9)
Standardmäßig unterscheidet der Like-Operator zwischen Groß- und Kleinschreibung, versteht also beispielsweise „Orion“ und „orion“ als unterschiedliche Unternehmen. Um die Groß-/Kleinschreibung zu ignorieren, können Sie die Zeile ganz am Anfang des Moduls im Visual Basic-Editor hinzufügen Option Text vergleichen, wodurch Like so umgeschaltet wird, dass die Groß- und Kleinschreibung nicht berücksichtigt wird.
Auf diese Weise können Sie sehr komplexe Masken zum Prüfen von Bedingungen zusammenstellen, zum Beispiel:
- ?1##??777RUS – Auswahl aller Kennzeichen der 777-Region, beginnend mit 1
- LLC* – alle Unternehmen, deren Name mit LLC beginnt
- ##7## – alle Produkte mit einem fünfstelligen digitalen Code, wobei die dritte Ziffer eine 7 ist
- ????? – alle Namen mit fünf Buchstaben usw.
Methode 3. Makrofunktion zum Kleben von Text unter zwei Bedingungen
In der Arbeit kann es ein Problem geben, wenn Sie den Text mit mehr als einer Bedingung verknüpfen müssen. Stellen wir uns zum Beispiel vor, dass in unserer vorherigen Tabelle eine weitere Spalte mit der Stadt hinzugefügt wurde und das Kleben nicht nur für eine bestimmte Firma, sondern auch für eine bestimmte Stadt durchgeführt werden soll. In diesem Fall muss unsere Funktion leicht modernisiert werden, indem ihr eine weitere Reichweitenprüfung hinzugefügt wird:
Function MergeIfs(TextRange As Range, SearchRange1 As Range, Condition1 As String, SearchRange2 As Range, Condition2 As String) Dim Delimeter As String, i As Long Delimeter = ", " 'Trennzeichen (können durch Leerzeichen oder ; etc. ersetzt werden) e.) 'wenn die Validierungs- und Klebebereiche nicht gleich sind, mit einem Fehler beenden If SearchRange1.Count <> TextRange.Count Or SearchRange2.Count <> TextRange.Count Then MergeIfs = CVErr(xlErrRef) Exit Function End If 'gehe alle Zellen durch, überprüfe alle Bedingungen und sammle den Text in die Variable OutText For i = 1 To SearchRange1.Cells.Count If SearchRange1.Cells(i) = Condition1 And SearchRange2.Cells(i) = Condition2 Then OutText = OutText & TextRange.Cells(i) & Delimiter End If Next i 'Ergebnisse ohne letztes Trennzeichen anzeigen MergeIfs = Left(OutText, Len(OutText) - Len(Delimeter)) End Function
Es wird auf genau die gleiche Weise angewendet – nur Argumente müssen jetzt mehr angegeben werden:
Methode 4. Gruppieren und Kleben in Power Query
Sie können das Problem ohne Programmierung in VBA lösen, wenn Sie das kostenlose Power Query-Add-In verwenden. Für Excel 2010-2013 kann es hier heruntergeladen werden, und in Excel 2016 ist es bereits standardmäßig integriert. Die Reihenfolge der Aktionen wird wie folgt sein:
Power Query weiß nicht, wie man mit regulären Tabellen arbeitet, also besteht der erste Schritt darin, unsere Tabelle in eine „intelligente“ Tabelle zu verwandeln. Wählen Sie es dazu aus und drücken Sie die Kombination Ctrl+T oder wählen Sie auf der Registerkarte aus Startseite – Als Tabelle formatieren (Home — Als Tabelle formatieren). Auf dem dann erscheinenden Reiter Bauherr (Design) Sie können den Tabellennamen festlegen (ich habe den Standard verlassen Tabelle 1):
Lassen Sie uns nun unsere Tabelle in das Power Query-Add-In laden. Dazu auf der Registerkarte Datum (wenn Sie Excel 2016 haben) oder klicken Sie auf die Registerkarte Power Query (wenn Sie Excel 2010-2013 haben). Vom Tisch (Daten — Aus Tabelle):
Wählen Sie im sich öffnenden Fenster des Abfrageeditors die Spalte aus, indem Sie auf die Überschrift klicken Firma und drücken Sie die Taste oben Gruppe an (Gruppiere nach). Geben Sie den Namen der neuen Spalte und die Art der Operation in der Gruppierung ein – Alle Zeilen (Alle Zeilen):
Klicken Sie auf OK und wir erhalten eine Minitabelle mit gruppierten Werten für jedes Unternehmen. Die Inhalte der Tabellen sind gut sichtbar, wenn Sie in der resultierenden Spalte mit der linken Maustaste auf den weißen Hintergrund der Zellen (nicht auf den Text!) klicken:
Jetzt fügen wir eine weitere Spalte hinzu, in der wir mit der Funktion die Inhalte der Adressspalten in jede der Minitabellen kleben, getrennt durch Kommas. Dazu auf der Registerkarte Spalte hinzufügen wir drücken Benutzerdefinierte Spalte (Spalte hinzufügen – Benutzerdefinierte Spalte) Geben Sie im angezeigten Fenster den Namen der neuen Spalte und die Kopplungsformel in der in Power Query integrierten M-Sprache ein:
Beachten Sie, dass bei allen M-Funktionen zwischen Groß- und Kleinschreibung unterschieden wird (im Gegensatz zu Excel). Nach dem Anklicken OK wir bekommen eine neue Spalte mit eingeklebten Adressen:
Es bleibt die bereits unnötige Spalte zu entfernen Tabellenadressen (Rechtsklick auf den Titel) Spalte löschen) und laden Sie die Ergebnisse in das Blatt hoch, indem Sie auf die Registerkarte klicken Startseite — Schließen und herunterladen (Home — Schließen und laden):
Wichtige NuanceHinweis: Im Gegensatz zu den bisherigen Methoden (Funktionen) werden Tabellen aus Power Query nicht automatisch aktualisiert. Wenn sich in Zukunft Änderungen an den Quelldaten ergeben, müssen Sie mit der rechten Maustaste irgendwo in die Ergebnistabelle klicken und den Befehl auswählen Aktualisieren & Speichern (Aktualisierung).
- So teilen Sie eine lange Textzeichenfolge in Teile auf
- Mehrere Möglichkeiten, Text aus verschiedenen Zellen in eine zu kleben
- Verwenden des Like-Operators zum Testen von Text anhand einer Maske