Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1380to1384
1380to1384
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Index mit ungefährer Übereinstimmung

Forumthread: Index mit ungefährer Übereinstimmung

Index mit ungefährer Übereinstimmung
23.09.2014 10:36:31
Dominic
Hi zusammen,
ich stehe gerade auf dem Schlauch.
Ich nutze folgende Funktion um aus dem Blatt Datenbasis eine Referenz/ Kundennummer heraussuchen.
INDEX(Datenbasis!A:A;VERGLEICH(Datensammlungen!X3;Datenbasis!B:B;0))
Nun ist es jedoch so, dass im Blatt Datensammlungen X3 ein Kundenname steht.
Im Blatt Datenbasis sieht es wie folgt aus:
Spalte A: Referenz/ Kundennummern
Spalte B: Kundennamen
Hinweis: Jeder Kunde kann in diesem Tabellenblatt mehrere hundert male aufgeführt werden (für jede Transaktion eine Zeile). Durch ein VBA Skript lasse ich mir bereits jeden Kunden der in diesem Blatt aufgeführt wird, EINMALIG in Spalte X im Blatt Datensammlungen ausgeben. Wichtig ist eben nun, das ich zu jedem Kunden dessen einmalige ID/Referenz eben auch benötige. Bisher habe ich das eben über o. g. Index Funktion versucht.
Zusätzliche Herausforderung: Es kann hier durchaus minimale Abweichungen in den Kundennamen geben. Das mal ein Leerzeichen mehr oder weniger zwischen Kundenname und Firmierung existiert. Wie kann ich die o. g. Index Funktion

Anzeige

4
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Index mit ungefährer Übereinstimmung
23.09.2014 11:11:53
Klaus
Hallo Dominic,
was für den Menschen eine minimale Abweichung ist, stell für den Computer eine Welt an Unterschied dar. Auch die unscharfen Verweise mit Bereich_Verweis = WAHR werden dir hier nicht helfen.
Um STRINGS unscharf auf Ähnlichkeiten zu prüfen, gibt es mehrere Ansätze. Mit dem SOUNDEX Ansatz werden Strings auf phonetische Ähnlichkeit geprüft. Das eignet sich insbesondere bei Namen, da im SOUNDEX die Firma "Meier Transporte" identisch mit der Firma "Meyer Transporte" wird.
Ein anderer Ansatz ist die LEVENSHTEIN-Distanz, welche angibt wie viele Änderungsschritte zwischen zwei Strings liegen.
Für beides hätte ich fertige VBA-Scripte hier rumliegen. Kannst du dir eine solche Lösung vorstellen?
Grüße,
Klaus M.vdT.

Anzeige
AW: Index mit ungefährer Übereinstimmung
23.09.2014 11:20:50
Dominic
Hi Klaus,
dessen war ich mir bewusst und habe aus diesem Grunde hier nachgefragt da ich keine Idee hatte diese kleinen Abweichungen mit zu berücksichtigen.
Die Soundex Methode klingt gut. Würde mich zu dem VBA Skript freuen! :)

Soundex-VBA Script
23.09.2014 11:45:45
Klaus
Hi,
In A1 Steht "Batterie", in A2 ergibt die Formel
=SOUNDEX(A1;4)
den Wert B360
Damit kannst du dann Vergleiche in Hilfsspalten anstellen. Über die Vor-Nachteile des SOUNDEX Verfahrens googelst und wikipediast du dich bitte selber schlau.
Option Explicit
Function Soundex(Surname As String, iLen As Integer) As String
' Developed by Richard J. Yanco
' This function follows the Soundex rules given at
' http://home.utah-inter.net/ _
kinsearch/Soundex.html
Dim Result As String, c As String * 1
Dim Location As Integer
Surname = UCase(Surname)
' First character must be a letter
If Asc(Left(Surname, 1))  90 Then
Soundex = ""
Exit Function
Else
' St. is converted to Saint
If Left(Surname, 3) = "ST." Then
Surname = "SAINT" & Mid(Surname, 4)
End If
' Convert to Soundex: letters to their appropriate digit,
' A,E,I,O,U,Y ("slash letters") to slashes
' H,W, and everything else to zero-length string
Result = Left(Surname, 1)
For Location = 2 To Len(Surname)
Result = Result & Category(Mid(Surname, Location, 1))
Next Location
' Remove double letters
Location = 2
Do While Location  iLen
Soundex = Left(Result, iLen)
End Select
End If
End Function
Private Function Category(c) As String
'   Returns a Soundex code for a letter
Select Case True
Case c Like "[AEIOUY]"
Category = "/"
Case c Like "[BPFV]"
Category = "1"
Case c Like "[CSKGJQXZ]"
Category = "2"
Case c Like "[DT]"
Category = "3"
Case c = "L"
Category = "4"
Case c Like "[MN]"
Category = "5"
Case c = "R"
Category = "6"
Case Else 'This includes H and W, spaces, punctuation, etc.
Category = ""
End Select
End Function

Anzeige
Levenshtein VBA-Script
23.09.2014 11:48:56
Klaus
Hi nochmal,
falls dir SOUNDEX noch nicht hilft, hier die LEVENSHTEIN udf aus meiner Sammlung.
A1 = Batterie
A2 = Bakterie
A3 = Levenshtein3(A1;A2) ergibt, dass eine Batterie einer Bakterie zu 88% ähnlich ist.
Auch hier: Vor und Nachteile bitte selber ergooglen.
Option Explicit
' Levenshtein3 tweaked for UTLIMATE speed and CORRECT results
' Solution based on Longs
' Intermediate arrays holding Asc()make difference
' even Fixed length Arrays have impact on speed (small indeed)
' Levenshtein version 3 will return correct percentage
Function Levenshtein3(ByVal string1 As String, ByVal string2 As String) As Long
Dim i As Long, j As Long, string1_length As Long, string2_length As Long
Dim distance(0 To 60, 0 To 50) As Long, smStr1(1 To 60) As Long, smStr2(1 To 50) As Long
Dim min1 As Long, min2 As Long, min3 As Long, minmin As Long, MaxL As Long
string1_length = Len(string1):  string2_length = Len(string2)
distance(0, 0) = 0
For i = 1 To string1_length:    distance(i, 0) = i: smStr1(i) = Asc(LCase(Mid$(string1, i, 1))): _
Next
For j = 1 To string2_length:    distance(0, j) = j: smStr2(j) = Asc(LCase(Mid$(string2, j, 1))): _
Next
For i = 1 To string1_length
For j = 1 To string2_length
If smStr1(i) = smStr2(j) Then
distance(i, j) = distance(i - 1, j - 1)
Else
min1 = distance(i - 1, j) + 1
min2 = distance(i, j - 1) + 1
min3 = distance(i - 1, j - 1) + 1
If min2  MaxL Then MaxL = string2_length
Levenshtein3 = 100 - CLng((distance(string1_length, string2_length) * 100) / MaxL)
End Function

Anzeige
;
Anzeige
Anzeige

Infobox / Tutorial

Index mit ungefährer Übereinstimmung in Excel

Schritt-für-Schritt-Anleitung

Um eine Index-Funktion mit ungefährer Übereinstimmung in Excel zu erstellen, kannst du folgende Schritte befolgen:

  1. Vorbereitung der Daten: Stelle sicher, dass du zwei Blätter hast: "Datenbasis" mit den Kundennamen in Spalte B und den Referenznummern in Spalte A. Das zweite Blatt heißt "Datensammlungen" und enthält die Namen, die du abgleichen möchtest, in Spalte X.

  2. Soundex-Funktion einfügen: Nutze die Excel Soundex Funktion, um phonetische Ähnlichkeiten zwischen den Kundennamen zu prüfen. Du kannst die Funktion in einem VBA-Modul implementieren. 

    Option Explicit
Function Soundex(Surname As String) As String
   ' Deine Soundex-Logik hier
End Function

  3. Verwendung der Levenshtein-Distanz: Alternativ kannst du die Levenshtein-Distanz verwenden, um die Ähnlichkeit zwischen den Namen zu messen. Füge ebenfalls eine entsprechende VBA-Funktion hinzu.

    Function Levenshtein(string1 As String, string2 As String) As Long
   ' Deine Levenshtein-Logik hier
End Function

  4. Index-Funktion anpassen: Ersetze die Standard-INDEX- und VERGLEICH-Funktionen durch die Soundex- oder Levenshtein-Funktionen, um die Referenznummern basierend auf der Ähnlichkeit der Kundennamen abzurufen.

Häufige Fehler und Lösungen

  • Fehler: Keine Übereinstimmung gefunden
    Lösung: Überprüfe, ob die Namen in Spalte B von "Datenbasis" korrekt und ohne zusätzliche Leerzeichen eingegeben sind. Verwende die TRIM-Funktion, um unerwünschte Leerzeichen zu entfernen.

  • Fehler: VBA funktioniert nicht
    Lösung: Stelle sicher, dass Makros in Excel aktiviert sind. Gehe zu "Datei" -> "Optionen" -> "Trust Center" -> "Einstellungen für das Trust Center" und aktiviere die Makros.

Alternative Methoden

Zusätzlich zur Verwendung der Soundex-Funktion und der Levenshtein-Distanz kannst du auch die folgenden Methoden in Betracht ziehen:

  • SVERWEIS mit ungefährer Übereinstimmung: Benutze die Option WAHR im SVERWEIS, um ungefähre Übereinstimmungen zu erhalten. Beachte, dass dies nicht so präzise ist wie die vorherigen Methoden.

  • Fuzzy Lookup Add-In: Microsoft bietet ein Add-In, das Fuzzy Matching unterstützt. Es kann hilfreich sein, um ähnliche Einträge zu finden.

Praktische Beispiele

  1. Soundex-Beispiel: Wenn in Zelle A1 "Batterie" steht und in A2 "Bakterie", kannst du die folgende Formel verwenden, um die Soundex-Codes zu vergleichen:

    =SOUNDEX(A1) = SOUNDEX(A2)

  2. Levenshtein-Distanz Beispiel: Um die Ähnlichkeit zwischen zwei Strings zu berechnen, kannst du die Funktion in Excel so verwenden:

    =Levenshtein(A1; A2)

Diese Beispiele zeigen, wie du die Soundex- und Levenshtein-Distanz-Methoden in Excel effektiv nutzen kannst.

Tipps für Profis

  • Optimiere deine VBA-Funktionen: Achte darauf, dass deine VBA-Skripte effizient sind, um die Performance zu verbessern, insbesondere bei großen Datenmengen.

  • Nutze Hilfsspalten: Verwende Hilfsspalten für die Berechnung von Soundex-Codes oder Levenshtein-Distanzen, bevor du die Hauptformel anwendest. Dies kann die Berechnungsgeschwindigkeit erhöhen.

  • Dokumentation: Halte deine VBA-Funktionen gut dokumentiert, um die Wartung und Anpassung zu erleichtern.

FAQ: Häufige Fragen

1. Was ist der Unterschied zwischen Soundex und Levenshtein?
Soundex prüft die phonetische Ähnlichkeit, während die Levenshtein-Distanz die Anzahl der Änderungen zwischen zwei Strings zählt.

2. Wie kann ich die Levenshtein-Distanz in Excel verwenden?
Du kannst eine benutzerdefinierte VBA-Funktion erstellen, um den Abstand zwischen zwei Strings zu berechnen, und diese dann in deinen Excel-Formeln verwenden.

3. Funktioniert dies in allen Excel-Versionen?
Die beschriebenen Methoden funktionieren in Excel 2010 und neueren Versionen, die VBA unterstützen. Achte darauf, dass Makros aktiviert sind.

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Anzeige