Gestern habe ich gezeigt, dass Sie mit dem Ergebnis eines SVERWEIS direkt weiterrechnen können und wie Sie aus zwei Formeln eine machen. Das wird die erste Version unserer Tabelle werden: PLZ, PLZ-Kreis, Wert und schlussendlich die Stadt.
Verwendet werden drei Funktionen: LINKS, WERT und SVERWEIS
Heute geht es einen Schritt weiter. In diesem Beispiel werden wir mit dem SVERWEIS aus einer Postleitzahl den PLZ-Kreis ermitteln – so hat Düsseldorf z. B. den Kreis 40 oder Essen den Kreis 45. Die ersten beiden Ziffern bestimmen diesen Kreis – und aus diesem Kreis wiederum die Stadt.
Aus der PLZ 45133 soll also in einer Spalte 45 ermittelt werden und dann daneben “Essen” stehen.
Die ersten beiden Ziffern ermitteln wir mit der Funktion LINKS: aus welcher Zelle sollen wie viele Stellen von links her gezählt ausgegeben werden?
=LINKS(A2;2)
Die Formel nach unten ausfüllen. Die Funktion LINKS gibt keine Ziffer zurück, mit der ich weiterarbeiten kann. Ich wandle also in der Spalte daneben die Ziffer in einen Wert. Dazu gibt es verschiedene Methoden, ich kann den Eintrag aus B2 mit eins multiplizieren oder mit WERT umwandeln. Ich habe mich für WERT entschieden.
=WERT(B2)
Die Formel nach unten ausfüllen.
In der nächsten Spalte kommt jetzt der SVERWEIS, der nach dem Wert aus Zelle C2 in der Matrix (F1:G5) sucht und den Wert der zweiten Spalte ausgibt.
Achten Sie auf den absoluten Bezug bei der Matrix. Das Bereichsargument ist FALSCH, ich suche nach genauen Ergebnissen.
Anschließend will ich auf die Spalten B und C verzichten. Zum Schluss soll nur noch der SVERWEIS übrigbleiben.
Ersetzen wir zuerst die Spalte B – in C2 soll sofort der Wert ausgegeben werden.
Die Formel aus B2 muss in die Formel von C2 kopiert werden werden – genau an die Stelle, an der B2 steht. Markieren Sie in B2 alles nach dem Gleichheitszeichen, kopieren Sie es und beenden Sie mit ESC die Bearbeitung der Zelle. Klicken Sie doppelt auf C2 und markieren Sie B2 zwischen den Klammern. Drücken Sie STRG+V. Jetzt muss dort stehen:
=WERT(LINKS(A2;2))
Beenden Sie mit ENTER die Bearbeitung der Zelle und füllen Sie die neue Formel nach unten aus. Stimmt alles? Wenn nicht, kontrollieren Sie, ob Sie beim Kopieren keine Klammer vergessen haben und ob Sie an der richtigen Stelle eingefügt haben.
Jetzt wird die gesamte Formel aus C2 in den SVERWEIS in der Zelle D2 kopiert – wo steht im SVERWEIS der Bezug auf C2? Genau da muss diese Formel rein!
Markieren Sie in C2 alles nach dem Gleichheitszeichen, kopieren Sie es und beenden Sie wieder mit ESC die Bearbeitung der Zelle. Klicken Sie doppelt auf D2, markieren Sie C2 und drücken Sie STRG+V. Jetzt muss der SVERWEIS so aussehen:
=SVERWEIS(WERT(LINKS(A2;2));$F$1: $G$5;2;FALSCH)
Mit ENTER beenden und die Formel nach unten ausfüllen. Kontrollieren Sie ob alles stimmt und löschen Sie dann die Spalten B und C.
Bork.Blog 