Mit Hilfe von INDEX, VERGLEICH, SVERWEIS wollen wir den Wert einer bestimmte Zelle in einem Tabellenblatt zurückgeben.
und danach das Ergebnis mit den Formeln LINKS, ERSETZEN, RUNDEN, LÄNGE spezifizieren.

Excel ist auf Grund seiner großen Anzahl vorhandener Formeln ein mächtiges Tool um Tabellen auslesen und analysieren zu können. Wie man diese Formeln geschickt für einen arbeiten lassen kann, ist unser Ziel dieses Artikels.

Die Situation: Es gibt in einem Blatt (der Name des Blattes ist England) zwei Tabellen in einer Spalte, mit gleichen Vereinsnamen. Wir wollen aus der unteren Tabelle die Reihenfolge ermitteln und in einem anderen Tabellenblatt ausgeben.

Als erstes wollen wir die Zeilennummer ermitteln, in der das Wort “VEREIN” aus Spalte “D” vorkommt. Dazu nutzen wir die Formel “VERGLEICH”
Vergleich gibt die Zeilennummer zurück.
=VERGLEICH(“Verein”;England!D:D;0)

Sprechende Erklärung:
Vergleiche mit dem Suchkriterium “Verein” die Einträge in dem Blatt “England” im Bereich der Spalten “D:D” und das “genau“

Für das Suchkriterium “Verein” kann man auch eine Formel nehmen, z.B. SVerweis.
=SVERWEIS(“Verein”;England!D:D;1;0)

Sprechende Erklärung:
Spaltenverweis mit dem Suchkriterium “Verein” im Blatt “England” in den Spalten “D:D” und gib aus der 1 Spalte, wenn Treffer genau gleich ist, den Inhalt der Zelle zurück. SVerweis gibt immer den Inhalt einer Zelle zurück.

Wenn man diese Formeln jetzt verschachtelt, kann ma auch schreiben:

=VERGLEICH(SVERWEIS("Verein";England!D:D;1;0);England!D:D;0)

Das Ergebnis ist in beiden Fällen = 161 (Zeile)

Wir wollen den ersten Vereinsnamen unterhalb “Verein” ausgeben lassen, aus Zeile 162. Dazu erweitern wir die Formel mit INDEX. Index gibt auch den Inhalt einer gefundenen Zelle zurück, benötigt dazu die Vorgabe eines Bereiches (Matrix) und eine Zeile und eine Spalte.
Die Zeile haben wir schon mit der obigen Formel gefunden. Jetzt bauen wir INDEX um unsere Formel herum:

=INDEX(England!A:D;VERGLEICH(SVERWEIS("Verein";England!D:D;1;0);England!D:D;0)+1;4)

Sprechende Erklärung: (wenn man nur das Ergebnis unserer schon entwickelten Formel mit Vergleich und SVerweis berücksichtigt (also die Zahl 162))
Index gibt den Wert zurück aus dem Blatt England, des Bereiches der Spalten “A:D” aus der Zeile 161 + 1 und genau aus 4. Spalte
Das Ergebnis lautet “Logo Manchester CityManchester City”

Verkürzt, nur mit den Ergebnissen der vorigen Formel Vergleich und SVerweis würde diese Formel so aussehen:

=INDEX(England!A:D;161+1;4)

Die +1 habe ich nicht farblich markiert, da sie nur ein Zusatz ist, um die nächste Zeile (161 + 1 = 162) zu erreichen.

Das Ergebnis dieser Zelle “Logo Manchester CityManchester City” ist noch nicht ganz das, was wir haben wollen. Das Ergebnis soll der Verein “Manchester City” sein. Da in allen anderen Zellen das Wort “Logo” davor steht und in allen anderen Zellen der Vereinsname zweimal hintereinander geschrieben wird, können wir mit folgenden Formeln ans Ziel gelangen.

Zum Einsatz kommen jetzt die Formeln LINKS, ERSETZEN, RUNDEN, LÄNGE

Wir könnten ganz einfach die Formel “RECHTS” verwenden, die uns eine bestimmte Anzahl von Zeichen eines Textes von RECHTS ausgibt, wir kennen aber nicht die genaue Länge. Eigentlich schon, von der einen Zelle aber wenn wir diese Formel kopieren und in weiteren Zellen benutzen wollen, muss sie auch gültig sein für Vereinsnamen mit unterschiedlicher Länge.

Als erstes wollen wir uns des Wortes “Logo” und des darauffolgenden Leerzeichen entledigen. Wir benutzen die Formel ERSETZEN, die einen “Alten Text” , ab eines bestimmten Startpunktes, für eine Anzahl Zeichen durch einen “Neuen Text” ersetzt.
Wie man z.B. den 3., 4., 5. und 6. Buchstaben aus ABCDEFG durch 1234 ersetzt: ERSETZEN(“ABCDEFG”;3;4;”1234″) Ergebnis: “AB1234G
Auf unsere bis jetzt entwickelte Formel angewendet würde das so aussehen:

ERSETZEN(“Logo Manchester CityManchester City”;1;5;””)
Sprechende Erklärung: Ersetze ab dem 1 Zeichen für 5 Zeichen lang, durch NICHTS
Und wenn wir jetzt für den Text unsere Formel einsetzen, sieht das folgendermaßen aus:

ERSETZEN(INDEX(England!A:D;VERGLEICH(SVERWEIS("Verein";England!D:D;1;0);England!D:D;0)+1;4);1;5;"")

Das Ergebnis lautet: “Manchester CityManchester City”

Jetzt könnten wir die Formel anwenden LINKS und uns die ersten 15 Zeichen von Links ausgeben lassen. Die Formel würde so aussehen:
LINKS(“Manchester CityManchester City”,15)

Das Ergebnis wäre “Manchester City” und wir wären fertig.

Aber wir wollen die Formel auch für andere Vereinsnamen mit unterschiedlichen Längen verwenden, also müssen wir uns für die 15 etwas einfallen lassen.

Bezeichnend hier ist, dass jeder Vereinsname doppelt vorkommt, hier sollte uns “Halbieren” weiter helfen. Wir können nur die Anzahl der Zeichen eines Textes halbieren, also nutzen wir hier die Formel “LÄNGE”.
Die Kurzform ist: LÄNGE(“Manchester CityManchester City”) und das Ergebnis ist 30
Die 15 durch 2 zu teilen, geht zwar aber dann kommen 17,5 Zeichen heraus.

Dabei hilft uns dann die Formel “RUNDEN“.

RUNDEN rundet eine Zahl auf die nächste GANZZAHL auf oder ab, wenn man als Nachkommastelle 0 eingibt. Bei einstelligen Nachkommastellen wird bei 0-4 ab- und bei 5-9 aufgerundet. Da bei unserer Teilung nur bei ungeraden TEXT-LÄNGEN eine ,5 entsteht, wird hier immer aufgerundet. Das würde dann bei unserem Beispiel so aussehen:
RUNDEN(LÄNGE(“Manchester CityManchester City”)/2;0)
OK wir setzen unsere komplette Formel nun zusammen.
Wir ersetzen aus: LINKS(“Manchester CityManchester City”,15) den Text mit unserer obigen Formel und die 15 mit der gerade entwickelten Formel:
RUNDEN(LÄNGE(“Manchester CityManchester City”)/2;0) zusammen, wobei wir hier auch den Text durch dir obige Formel ersetzen müssen.

LINKS(ERSETZEN(INDEX(England!A:D;VERGLEICH(SVERWEIS("Verein";England!D:D;1;0);England!D:D;0)+1;4);1;5;"");RUNDEN(LÄNGE(ERSETZEN(INDEX(England!A:D;VERGLEICH(SVERWEIS("Verein";England!D:D;1;0);England!D:D;0)+1;4);1;5;""))/2;0))

Wer dazu noch Fragen oder Anregungen hat, gerne über die Kommentarfunktion.

LG John