Dieses Forum nutzt Cookies
Dieses Forum verwendet Cookies, um deine Login-Informationen zu speichern, wenn du registriert bist, und deinen letzten Besuch, wenn du es nicht bist. Cookies sind kleine Textdokumente, die auf deinem Computer gespeichert werden. Die von diesem Forum gesetzten Cookies werden nur auf dieser Website verwendet und stellen kein Sicherheitsrisiko dar. Cookies aus diesem Forum speichern auch die spezifischen Themen, die du gelesen hast und wann du zum letzten Mal gelesen hast. Bitte bestätige, ob du diese Cookies akzeptierst oder ablehnst.

Ein Cookie wird in deinem Browser unabhängig von der Wahl gespeichert, um zu verhindern, dass dir diese Frage erneut gestellt wird. Du kannst deine Cookie-Einstellungen jederzeit über den Link in der Fußzeile ändern.

Höchste Anzahl gemeinsame Zeichen ermitteln
#1
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.


.xlsx   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
Antwortento top
#2
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
Antwortento top
#3
Moin

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

https://www.microsoft.com/en-us/download...x?id=15011
Wir sehen uns!
... shift-del
Hinweise zu meiner Hilfe in Foren
Excel Super-Funktionen: VERWEIS(), INDEX(), WAHL()
[-] Folgende(r) 1 Benutzer sagt Danke an shift-del für diesen Beitrag:
  • Thomas Ramseier
Antwortento top
#4
Guten Abend Elex

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

Liebe Grüsse aus Bern

Thomas
Antwortento top
#5
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
Antwortento top
#6
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.


.xlsx   Test Best_matching.xlsx (Größe: 14,61 KB / Downloads: 7)
Antwortento top
#7
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.


.xlsx   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
Antwortento top
#8
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
Antwortento top
#9
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
Antwortento top
#10
Ich hatte Beitrag 8 noch mal geändert. Hast du die letzte Formel Versucht?
Antwortento top


Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste