Höchste Anzahl gemeinsame Zeichen ermitteln

[Thomas Ramseier]

Hallo zusammen

Ich versuche, eine "Best-Matching"-Logik mittels einer Array-Formel zu erstellen, scheitere aber hartnäckig daran. 

In der Beispieldatei steht in Feld $D$1 eine beliebige Zeichenkette. Diese Soll nun gegen die Spalte "A" komplett durchgetestet werden und den Wert in Spalte "B" für den "besten Treffer", also für jene Zelle in Spalte A, welche am meisten gemeinsame Zeichen (von links aus) mit dem Suchbegriff hat, zurückgeben - also im aktuellen Beispiel den Wert "5" - da "abcdex12" den besten Match mit der Zelle A5 hat ("abcde") und in B5 demetsprechend die Zahl 5 steht. Der Suchbegriff darf länger sein als der Wert in Spalte A, aber nicht umgekehrt - also der Wert in A8 ist *kein* Treffer. 
In der Spalte "e" konnte ich zumindest einen Teilerfolg verbuchen, aber es gelingt mir nicht, den Maximalwert aller dieser Ergebnisse dieser Spalte in eine einzelne Berechnung resp. Zelle hineinzukriegen. Die höchste Qualität eines Treffers wäre eine genaue Übereinstimmung, deswegen wird auch zuerst mit "normalem" SVERWEIS getestet, ob ggf. eine 1:1-Entsprechung existiert.


  Test Best_matching.xlsx (Größe: 33,45 KB / Downloads: 11)

Hat jemand von Euch einen Tipp, wie das gelöst werden könnte?
Besten Dank und freundliche Grüsse

Thomas

[Elex]

Hi

Versuche es mal so.

Formel mit strg+shift+enter schließen
=REST(MAX(ISTZAHL(FINDEN(A1:A9;D1))*LÄNGE(A1:A9)+ZEILE(A1:A9)%%);1)*10000

Gruß Elex

[shift-del]

Moin

https://www.mrexcel.com/board/threads/fu...on.195635/

https://www.microsoft.com/en-us/download...x?id=15011

[Thomas Ramseier]

Guten Abend Elex

Wow - super! - Herzlichen Dank. Jetzt werde ich "nur noch" verstehen müssen, wie die Formel funktioniert

Liebe Grüsse aus Bern

Thomas

[Elex]

Wenn du sagst es passt schon dann wäre ja gut.
Nach nochmaligen Lesen denke ich aber es wird eher die Formel.

Nutzbarkeit der Formel hängt aber von der größe des Datenbereichs und den Textlängen ab.
Max Textlänge in A = 79 Zeichen. Für mehr Zeichen den Teil anpassen   ->     SPALTE(A1:CA1).

=REST(MAX((LÄNGE(A1:A9)<=LÄNGE(D1))*ISTZAHL(FINDEN(LINKS(A1:A9;SPALTE(A1:CA1));D1))*LÄNGE(LINKS(A1:A9;SPALTE(A1:CA1)))+ZEILE(A1:A9)%%);1)*10000

Gruß Elex

[LCohen]

Datei für beide Excel-Welten:

XL365: Da es mir mit einem dynamischen Bereich nicht gelungen ist, eine dynamische Zeilensumme vor diesen Bereich zu setzen, die sich mit diesem Bereich ausdehnt (wird konzeptuell auch nicht gehen, wegen Zirkel und so), blieb mir als letzte Möglichkeit eine intelligente Tabelle, die eine solche Summe auf andere Weise erlaubt.

Altes Excel: Man kann die xl365-FILTER()-Lösung auch für altes Excel haben mit Autofilter. Vorteil: Kein extra Blatt, kein extra Speicher.


  Test Best_matching.xlsx (Größe: 14,61 KB / Downloads: 7)

[Thomas Ramseier]

Hallo Elex

Danke für die Ergänzung und nochmals besten Dank für die Lösung dieses Problems, welches mich schon einige Zeit beschäftigt. Hatte bisher mit einem Workaround für max. 16 Zeichen Länge mittels eines n-fach verschachtelten WENN-Konstrukts arbeiten müssen.

Liebe Grüsse

Thomas

Nachtrag:

@Elex: Habe nun versucht, die Tabelle zu erweitern und die Formeln dementsprechend anzupassen (die angepasste Test-Datei liegt bei). Dabei ist mir aufgefallen, dass lediglich die Ursprungsformel (in Zelle G1) den richtigen Wert zurückliefert, sofern die Tabelle den Bereich A1:A9 umfasst. Mit der erweiterten Tabelle A1:A19 (Zelle H1) funktioniert sie auch, ausser, es gibt gar keinen Treffer, dann wird ein Teiltreffer (hier der Buchstabe "x" in Zelle A13) als Resultat zurückgeliefert - dies ist aber kein Treffer, da "x" in der Mitte des Test-strings in D1 steht, nicht am Anfang. Es sollte der Wert "19" zurückgegeben werden (oder wenn möglich die Zahl Null, das wäre optimal)

Die erweiterte Formel Deines zweiten Posts (Zellen I1 und J1) hat auch damit zu kämpfen und liefert je nach Tabellenlänge auch unterschiedliche Werte zurück.


  Test Best_matching_2.xlsx (Größe: 32,46 KB / Downloads: 2)



Liebe Grüsse

Thomas

---

Hallo LCohen

Danke für Deine Antwort und die Beispieldatei mit der Lösung. Die Tabelle mit der umgekehrten Herangehensweise (jede Zeile der Tabelle errechnet selbst, wie gross die Trefferzahl ist) funktioniert gut mit *einem* Eingabewert (wie hier in A1). Leider benötige ich eine Lösung, welche auch aus einer Tabelle mit n Zeilen heraus erfragt werden kann. Sorry, dass ich das nicht bereits im ursprünglichen Post erwähnt hatte.

Liebe Grüsse

Thomas

[Elex]

Hi Thomas

Versuchen wir es damit.

Edit:
=WENNFEHLER(MAX((1/(1/(MAX((LÄNGE(A1:A19)<=LÄNGE(D1))*SPALTE(A1:CA1)*(LINKS(A1:A19;SPALTE(A1:CA1))=LINKS(D1;SPALTE(A1:CA1))))))=(LÄNGE(A1:A19)<=LÄNGE(D1))*SPALTE(A1:CA1)*(LINKS(A1:A19;SPALTE(A1:CA1))=LINKS(D1;SPALTE(A1:CA1))))*ZEILE(A1:A19));0)

'shift+strg+enter

Gruß Elex

[Thomas Ramseier]

Hi Elex

Sorry - leider dasselbe Resultat wie vorher - bei einem Treffer oder Teiltreffer funktioniert es tiptop, aber bei keinem Treffer leider nicht - das wäre noch "the Cherry on the cake" :19:

[Elex]

Ich hatte Beitrag 8 noch mal geändert. Hast du die letzte Formel Versucht?