Excel–Mit SVERWEIS rechnen

Aus einer Referenztabelle soll Excel den passenden Prozentwert holen:

17.01.2018_0001

Verwendet wird SVERWEIS: das Suchkriterium ist der Umsatz in Spalte B; die Matrix ist die Referenztabelle in den Spalten F und G; der Spaltenindex muss in der zweiten Spalte nach dem Prozentwert suchen; gesucht wird ein Wert innerhalb eines Bereiches – die Bereichssuche ist also WAHR.

=SVERWEIS(B2;$F$1:$G$5;2;WAHR)

Sie sehen, dass der Bezug auf die Matrix absolut ist – ich möchte die Formel gleich nach unten für die weiteren Namen ausfüllen. Wenn ich die Matrix nicht absolut setze, “wandert” der Bezug und aus F1:G5 wird F2:G6 und dann F3:G7 und so fort. Das soll natürlich nicht sein! Darum fixiere ich den Bezug mit den Dollarzeichen.

smart_owl_reading_book_1600_clr_15127Achten Sie auch darauf, dass die erste Spalte der Matrix unbedingt sortiert sein muss, wenn Sie Zahlen aus einem Bereich suchen. Wenn Sie sich unsicher sind, lesen Sie zuerst hier und hier, wie ein SVERWEIS funktioniert.

Anschließend wird in Spalte D die Provision in Euro berechnet: =B2*C2

17.01.2018_0002

Was müssen Sie tun, wenn Sie in Spalte C die Provision direkt in Euro berechnen wollen? Also ohne den Umweg über die Ausgabe der Prozentzahl?

Lassen Sie zuerst beide Formeln stehen. In C2 steht der SVERWEIS, in D2 die Multiplikation. Sie müssen alles aus C2 in die Multiplikation kopieren – genau an die Stelle, an der in der Formel der Zellbezug C2 steht.

17.01.2018_0003

Markieren Sie in C2 alles nach dem =, drücken Sie STRG+C zum Kopieren und dann ESC, um die Zelle zu verlassen. Klicken Sie doppelt auf D2 und markieren Sie C2, drücken Sie STRG+V.

Bestätigen Sie mit ENTER und füllen Sie die Formeln nach unten aus. Stimmt alles? Dann löschen Sie anschließend Spalte C mit der Provision in %.

chalk_figure_lightbulb_idea_400_clr_5874

  1. Komplexe Formeln anfangs immer in Schritten erstellen
  2. Schreiben Sie jeden Berechnungsschritt in eine eigene Zelle
  3. Kopieren Sie die Formel ohne das Gleichheitszeichen und kopieren Sie sie an die Stelle des passenden Zellbezugs in die nächste Formel.
  4. Löschen Sie später die überflüssigen Berechnungsspalten.

Morgen sehen Sie ein weiteres Beispiel, wie Sie Schritt für Schritt eine Formel immer komplexer gestalten.

2 Kommentare

  1. Danke für die Erinnerung. Mir ist die Warnung vor dem VERWEIS in seiner Matrixschreibweise so in Fleisch und Blut übergegangen, dass ich sie kaum verwende. Sie funktioniert einwandfrei, aber Microsoft weist darauf hin, dass sie nur aus Kompatibilitätsgründen da sei und “ihre Funktionalität eingeschränkt” ist … was immer das heißen soll.

1 Trackback / Pingback

  1. Excel – Formeln schrittweise aufbauen – Pia Bork

Kommentar hinterlassen

E-Mail Adresse wird nicht veröffentlicht.


*