Du bist hier::--Excel Formeln – Längste Serie eines Teilstrings ermitteln

Excel Formeln – Längste Serie eines Teilstrings ermitteln

In diesem Artikel werde ich ihnen zeigen, wie man es mit einer Formel in Excel schafft, dass aus einem String der längste Teilstring ausgegeben wird.

Wie man in Excel aus einer Zelle das am häufigsten vorkommende Zeichen wiedergibt, habe ich ihnen hier schon erklärt. Dabei werden aber alle Zeichen in der Zelle benutzt und nicht nur die zusammenhängenden. Wie man auch das bewerkstelligen kann, lesen Sie hier.

Beginn:

Zu aller Erst müssen Sie in die Zelle A2 den Text schreiben, der untersucht werden soll. Selbstverständlich können Sie auch eine andere Zelle wählen, jedoch müssen Sie die weiter unten aufgelisteten Formeln dann ändern. In meinem Fall habe ich eine String gewählt, der aus Ziffern besteht und durch ein Komma getrennt wird. In Zelle B2 folgt das Zeichen, nach welchem gesucht werden soll. Wie auch oben können Sie eine andere Zelle wählen, jedoch stimmen dann wieder die Zellbezügen aus den Musterformeln nicht. Bei mir war das Zeichen die Ziffer „2“. Anschließend müssen Sie noch angeben, welches Zeichen die Zahlen oder Wörter trennt. Bei mir war das das Komma. Schreiben Sie dieses Zeichen dann in die Zelle C2 oder eben wieder eine andere, wodurch die Zellbezüge wieder verändert werden müssen.

Matrixformel:

Nun folgt in Zelle D2 die Berechnung. Hier wird anschließend ausgegeben, wie oft das gesuchte Zeichen hintereinander auftritt. Dazu benötigen Sie natürlich eine Formel, welche leider nicht in Excel selbst verfügbar ist. Sie muss also aus mehreren Formeln erstellt werden. Die Zusammengesetze Formel lautet:

=MAX((LÄNGE(WECHSELN(A2&C2;WIEDERHOLEN(B2&C2;ZEILE($1:$1000));)) < LÄNGE(A2&C2))*ZEILE($1:$1000))

Diese Formel ist eine Matrixformel. Das bedeutet, dass Sie diese Formel nicht einfach durch eine Eingabe in die Zelle und anschließendes Drücken der Enter Taste bestätigen können. Dabei würde ein Fehler oder ein falscher Wert ausgegeben. Manchmal sieht man nicht einmal, dass der Wert falsch ist, dass liegt dann aber daran, dass es einfach ein günstiges Beispiel war. Um die Formel richtig auszuführen, müssen Sie nun nochmal die Zelle D2 anklicken und dann oben noch in die Bearbeitungszeile der Formel klicken. Anschließend müssen Sie anstelle von Enter nun STRG+SHIFT+Enter drücken. Nun wird hier der richtige Wert angezeigt und die Formel wird sich in folgende verändern:

{=MAX((LÄNGE(WECHSELN(A2&C2;WIEDERHOLEN(B2&C2;ZEILE($1:$1000));)) < LÄNGE(A2&C2))*ZEILE($1:$1000))}

(Zur Darstellung in Originalgröße Abbildungen anklicken)
01-excel-formeln-laengste-serie-eines-teilstrings-ermitteln-470.png?nocache=1308578742435

Schnellere Matrixformel:

Das Beispiel von oben war zwar schon eine Matrixformel, was häufig darauf hindeutet, dass die Formel schnell arbeitet, jedoch gibt es die Formel auch noch besser. Dazu müssen Sie die Formel aus Zelle D2 wieder löschen. Geben Sie anschließend nun die folgende Formel ein:

=MAX((LÄNGE(WECHSELN(A2&C2;WIEDERHOLEN(B2&C2;ZEILE(INDIREKT("1:"&LÄNGE(A2))));)) < LÄNGE(A2&C2))*ZEILE(INDIREKT("1:"&LÄNGE(A2))))

Da auch dies wieder eine Matrixformel ist, genügt das einfache Eintragen und bestätigen mit Enter nicht, Sie müssen es wieder mit STRG+SHIFT+Enter machen. Die Formel hat sich anschließend wieder so verändert:

{=MAX((LÄNGE(WECHSELN(A2&C2;WIEDERHOLEN(B2&C2;ZEILE(INDIREKT("1:"&LÄNGE(A2))));)) < LÄNGE(A2&C2))*ZEILE(INDIREKT("1:"&LÄNGE(A2))))}

Haben Sie alles richtig gemacht, so wird sich der angezeigte Wert nicht verändern. In meinem Fall wurde hier korrekterweise also immer noch die „5“ angezeigt, da die Ziffern „2“ fünfmal hintereinander in String der Zelle A2 auftritt.

02-excel-formeln-laengste-serie-eines-teilstrings-ermitteln-schneller-470.png?nocache=1308578760412

Ohne Matrixformel:

Neben den ersten zwei Möglichkeiten, gibt es noch eine dritte Formel, die verwendet werden kann, um die längste Serie des Strings zu ermitteln. Diesmal ist es aber keine Matrixformel. Den Text den Sie nun in die Zelle D2 eingeben müssen heißt:

=VERWEIS(2;1/(LÄNGE(WECHSELN(A2&C2;WIEDERHOLEN(B2&C2;ZEILE($1:$1000));)) < LÄNGE(A2&C2));ZEILE($1:$1000))

Haben Sie wieder alles richtig gemacht, so wird sich der Wert nicht verändern.

03-excel-formeln-laengste-serie-eines-teilstrings-ermitteln-ohne-array-470.png?nocache=1308578778531

Jetzt haben Sie drei verschiedene Formeln kennengelernt, die die gleiche Aufgabe erfüllen. Welche von denen Sie auswählen ist ihnen überlassen, jedoch ist die zweite Formel empfehlenswerter, da Sie „am Produktivsten“.

Von |2018-07-25T14:23:39+00:00Juni 21st, 2011|Kategorien: Tabellenkalkulation|0 Kommentare

Über den Autor:

Hinterlassen Sie einen Kommentar