Einer meiner Freunde arbeitet in einem Unternehmen für Gesundheitsanalysen. Er kontaktiert mich oft zu einigen der realen Probleme, mit denen er bei der Arbeit mit Daten in Excel konfrontiert ist.
Oft wandele ich seine Abfragen auf dieser Seite in Excel-Tutorials um, da dies auch für meine anderen Leser hilfreich sein könnte.
Dies ist auch ein solches Tutorial.
Mein Freund hat mich letzte Woche mit folgendem Problem angerufen:
Es gibt Adressdaten in einer Spalte in Excel, und ich möchte Zellen identifizieren / filtern, in denen die Adresse doppelte Textzeichenfolgen (Wörter) enthält.
Hier ist der ähnliche Datensatz, in dem er Zellen filtern wollte, die eine doppelte Textzeichenfolge enthalten (die mit roten Pfeilen):
Was dies jetzt erschwert, ist, dass diese Daten keine Konsistenz aufweisen. Da es sich um eine Zusammenstellung von Datensätzen handelt, die manuell von Vertriebsmitarbeitern erstellt wurden, kann es zu Abweichungen im Datensatz kommen.
Bedenken Sie:
- Jede Textzeichenfolge kann sich in diesem Dataset wiederholen. Dies kann beispielsweise der Name des Gebiets oder der Stadt oder beides sein.
- Die Wörter werden durch ein Leerzeichen getrennt, und es ist nicht einheitlich, ob der Städtename nach sechs oder acht Wörtern stehen würde.
- Es gibt Tausende solcher Datensätze, und die Notwendigkeit besteht darin, die Datensätze zu filtern, bei denen es doppelte Textzeichenfolgen gibt.
Nachdem ich viele Optionen in Betracht gezogen hatte (wie Text in Spalten und Formeln), entschied ich mich schließlich, VBA zu verwenden, um dies zu erledigen.
Also habe ich eine benutzerdefinierte VBA-Funktion ('IdDuplicate') erstellt, um diese Zellen zu analysieren und mir TRUE zu geben, wenn es ein doppeltes Wort in der Textzeichenfolge gibt, und FALSE, falls es keine Wiederholungen gibt (wie unten gezeigt):
Diese benutzerdefinierte Funktion analysiert jedes Wort in der Textzeichenfolge und prüft, wie oft es im Text vorkommt. Wenn der Zähler mehr als 1 beträgt, wird TRUE zurückgegeben; andernfalls wird FALSE zurückgegeben.
Außerdem wurde es entwickelt, um nur Wörter mit mehr als drei Zeichen zu zählen.
Sobald ich die TRUE/FALSE-Daten habe, kann ich leicht alle Datensätze filtern, die WAHR sind.
Lassen Sie mich Ihnen nun zeigen, wie Sie dies in Excel tun.
VBA-Code für die benutzerdefinierte Funktion
Dies geschieht durch Erstellen einer benutzerdefinierten Funktion in VBA. Diese Funktion kann dann wie jede andere Arbeitsblattfunktion in Excel verwendet werden.
Hier der VBA-Code dafür:
Funktion IdDuplicates(rng As Range) As String Dim StringtoAnalyze As Variant Dim i As Integer Dim j As Integer Const minWordLen As Integer = 4 StringtoAnalyze = Split(UCase(rng.Value), " ") For i = UBound(StringtoAnalyze) To 0 Schritt -1 If Len(StringtoAnalyze(i)) < minWordLen Then GoTo SkipA For j = 0 To i - 1 If StringtoAnalyze(j) = StringtoAnalyze(i) Then IdDuplicates = "TRUE" GoTo SkipB End If Next j SkipA: Next i IdDuplicates = "FALSE" SkipB: Funktion beenden
Danke Walter für den Vorschlag einer besseren Herangehensweise an diesen Code!
So verwenden Sie diesen VBA-Code
Nachdem Sie nun den VBA-Code haben, müssen Sie ihn im Backend von Excel platzieren, damit er als normale Arbeitsblattfunktion verwendet werden kann.
Nachfolgend finden Sie die Schritte zum Einfügen des VBA-Codes in das Backend:
- Wechseln Sie zur Registerkarte Entwickler.
- Klicken Sie auf Visual Basic (Sie können auch die Tastenkombination ALT + F11 verwenden)
- Klicken Sie im sich öffnenden VB-Editor-Back-End mit der rechten Maustaste auf eines der Arbeitsmappenobjekte.
- Gehen Sie zu „Einfügen“ und klicken Sie auf „Modul“. Dadurch wird das Modulobjekt für die Arbeitsmappe eingefügt.
- Kopieren Sie im Fenster Modulcode den oben genannten VBA-Code und fügen Sie ihn ein.
Sobald Sie den VBA-Code im Backend haben, können Sie die Funktion "IdDuplicates" wie jede andere normale Arbeitsblattfunktion verwenden.
Diese Funktion nimmt ein einzelnes Argument entgegen, nämlich den Zellbezug der Zelle, in der sich der Text befindet.
Das Ergebnis der Funktion ist TRUE (wenn es doppelte Wörter gibt) oder FALSE (wenn es keine Duplikate gibt). Sobald Sie diese Liste von TRUE/FALSE haben, können Sie die mit TRUE filtern, um alle Zellen zu erhalten, die doppelte Textzeichenfolgen enthalten.
Hinweis: Ich habe den Code nur erstellt, um Wörter zu berücksichtigen, die länger als drei Zeichen sind. Dadurch wird sichergestellt, dass Wörter mit einer Länge von 1, 2 oder 3 Zeichen (z. B. 12 A, K G M oder L D A) in der Textzeichenfolge beim Zählen der Duplikate ignoriert werden. Wenn Sie möchten, können Sie dies einfach im Code ändern.
Diese Funktion steht nur in der Arbeitsmappe zur Verfügung, in die Sie den Code in das Modul kopiert haben. Falls dies auch in anderen Arbeitsmappen verfügbar sein soll, müssen Sie diesen Code kopieren und in diese Arbeitsmappen einfügen. Alternativ können Sie auch ein Add-In erstellen (Aktivierung, die diese Funktion in allen Arbeitsmappen Ihres Systems verfügbar machen würde).
Denken Sie auch daran, diese Arbeitsmappe in der Erweiterung .xlsm zu speichern (da sie einen Makrocode enthält).
