Excel – Portoberechnung 1

Am Beispiel einer Portoberechnung zeige ich Ihnen heute und morgen zwei Techniken:

– den schon oft gezeigten SVERWEIS, dieses Mal mit einem VERWEIS für die Spaltenberechnung

– die Verwendung der Datenüberprüfung als Ausfüllhilfe für den Anwender

Beginnen wir mit der Tabelle. Es soll eine einfache Portoberechnung für Briefe erstellt werden. Unterschieden wird nur nach Briefen innerhalb von Deutschland und International. Pakete und Päckchen spielen hier keine Rolle. Die Tabelle soll später so aussehen:

08.06.2015_ 0000

Eine Bezeichnung (Brief D oder Brief International) soll gewählt werden. Dann wird das Gewicht und eine Menge eingetragen. Das Porto wird aus einer Referenztabelle mit einem SVERWEIS ermittelt und die Summe schlussendlich ausgerechnet.

Die Referenztabelle sieht so aus:

08.06.2015_ 0002

Die Gewichte werden aufgelistet (0, 21, 51, 501, 1001) und sind immer als “ab 0 g – bis zum nächsten Wert” zu lesen. Die Zeile 2 zeigt also an, wie viel Porto für Briefe von 0 g bis 20 g zu zahlen sind. Der nächste Werte ist 21 – die Zeile 3 gilt also für Briefe zwischen 21 g und 50 g.

Die Referenztabelle muss aufsteigend sortiert sein, sie muss mit 0 beginnen.

Beide Tabellen sind bei mir formatierte Tabellen, das wirkt sich gleich auf meine Formeln aus. Die Referenztabelle hat auch diesen Namen bekommen: “”Referenz”. Sie sehen keine Bezüge in Form von A7 oder B3, sondern “Referenz[#Alle]”. Mehr zu formatierten Tabellen und den Namen lesen Sie im Excel-Lexikon.

Wie sehen jetzt die Formeln in der Portotabelle aus?

In Spalte D wird ein SVERWEIS zur Berechnung verwendet.

08.06.2015_ 0008

 

=SVERWEIS([Gewicht];Referenz[#Alle];VERGLEICH([Bezeichnung];Referenz[#Kopfzeilen];0))

Mit normalen Bezügen geschrieben sieht das so aus:
=SVERWEIS(B2;Referenz!$A$1:$C$6;VERGLEICH(A2;Referenz!$A$1:$A$3;0))

Sie erinnern sich?
Der SVERWEIS verlangt die Argumente: Suchkriterium Matrix Spaltenindex

Das Suchkriterium ist in diesem Beispiel B2 – das Gewicht. Da es sich um eine formatierte Tabelle handelt, verwendet Excel die Bezeichnung der Spalte “Gewicht”. Die Matrix die (ebenfalls formatierte) Referenztabelle, die auch “Referenz” heißt. Da ich alle Zeilen und Spalten markiere, lautet der Bezug “Referenz[#Alle]”.

Den Spaltenindex muss ich errechnen. Bei “Brief D” soll die zweite Spalte der Referenztabelle, bei “Brief International” die dritte Spalte verwendet werden. Das erreiche ich mit der Funktion VERGLEICH. Sie finden zwei Beispiele dazu: im Lexikon und im Blog.

Mit VERGLEICH suche ich nach der Bezeichnung (das ist der Eintrag aus A2 – also “Brief International”) in der Kopfzeile der Referenztabelle. Der Wert 0 steht für eine exakte Übereinstimmung; es darf nicht einfach der nächste passende Eintrage verwendet werden, wenn Excel den Eintrag nicht findet. Excel liefert mir für den Suchbegriff “Brief D” die zweite Spalte zurück und für “Brief International” die dritte. Die Bezeichnung “Paket” erzeugt einen Fehler.

In Spalte E frage ich mehrere Dinge ab, weil ich dem Anwender ermöglichen möchte, die Spalte C für die Menge dann freizulassen, wenn es sich um einen Brief handelt. Da es in erster Linie ein Brief ist, wird es bequemer, wenn ich keine “1” eintragen muss.

08.06.2015_ 0009

=WENN(UND([Bezeichnung]=””;[Menge]=””);””;WENN([Menge]=””;1*[Porto];[Menge]*[Porto]))

Zuerst wird abgefragt, ob Bezeichnung Menge gleichermaßen  leer sind (=””) – dann soll nichts geschehen. Zwei Bedingungen werden mit UND gekoppelt.

Dann wird abgefragt, ob nur die Menge leer ist ist – in diesem Fall soll das Porto mit 1 multipliziert werden.

In allen anderen Fällen soll einfach Menge * Porto gerechnet werden.

Morgen geht es weiter mit der Datenprüfung für die  Spalte A, einer Ausfüllhilfe für D und dem Format für Spalte B.

Hinterlasse jetzt einen Kommentar

Kommentar hinterlassen

E-Mail Adresse wird nicht veröffentlicht.


*