Beitrag aus SmartTools Excel Weekly
Kilometergeld mit Excel nach Pauschalen berechnen
Excel 365 2024 2021 2019 2016 2013
Fahrtkosten berechnen sich aus unterschiedlichen Kilometerpauschalen: 30 Cent bis zu einer Entfernung von 20 Kilometern und 35 Cent ab dem 21. Kilometer. Einige Leser möchten wissen, wie Sie bei Eingabe der gefahrenen Gesamtkilometer diese beiden Pauschalen in eine Fahrtkostenberechnung einbinden. Dabei soll natürlich vermieden werden, manuell den Kilomenteranteil bis 20 km und den Anteil ab 20 km trennen zu müssen. Unser Tipp liefert die passende Formel.
Die Aufteilung der Gesamtkilometer in die Anzahl bis 20 Kilometer und die Anzahl ab 20 Kilometer können Sie durch MIN- und MAX-Funktionen erreichen. Dann müssen Sie die ermittelten Anteile nur noch mit den entsprechenden Pauschalen multiplizieren.
Nehmen Sie als Beispiel folgendes Tabellenmodell:
- In Zelle B4 haben Sie den Grenzwert für die Änderung der Kilometerpauschalen eingegeben – hier also den Wert "20".
- In Zelle C4 steht die Kilometerpauschale bis zu diesem Grenzwert – hier: 0,30 €.
- Und in Zelle C5 steht die Kilometerpauschale ab dem Grenzwert – hier: 0,35 €.
Aus diesen Basiswerten wollen Sie ab Tabellenzeile 8 die Fahrtkosten für die gefahrenen Gesamtkilometer berechnen. Dazu geben Sie in Spalte B die Kilometerzahlen ein, und in Spalte C sollen anschließend die Fahrtkostenbeträge erscheinen.
Den Anteil bis zum Grenzwert von 20 Kilometern erhalten Sie für eine Gesamtkilometerzahl in Zelle B8 mit folgender Formel:
=MIN(B8;$B$4)
Wenn die Gesamtkilometerzahl kleiner als 20 ist, liefert die Formel die Gesamtkilometerzahl, ansonsten den Grenzwert aus Zelle B4 – also 20.
Der Anteil ab 20 Kilometer ergibt sich durch folgende Formel:
=MAX(B8-$B$4;0)
Sie reduziert die Gesamtkilometerzahl um den Grenzwert in B4 (20 Kilometer). Damit bei kurzen Fahrstrecken keine negativen Werte eingerechnet werden, schließt die MAX-Funktion diesen Fall aus und setzt den Anteil auf 0.
Nun müssen Sie die Kilometeranteile nur noch mit den entsprechenden Pauschalen multiplizieren. Für eine Gesamtentfernung, die Sie in Zelle B8 eingeben, könnten Sie die Fahrtkosten dann in Zelle C8 mit folgender Formel berechnen:
=MIN(B8;$B$4)*$C$4+MAX(B8-$B$4;0)*$C$5
Wenn Sie sich über die $-Zeichen in der Formel wundern: Damit machen Sie die Verweise auf die jeweiligen Zellen zu "absoluten" Bezügen. Das heißt zum Beispiel, dass sich der Verweis "$B$4" immer auf Zelle B4 bezieht – egal, wohin Sie die Formelzelle kopieren. Bei dem Verweis "B8" handelt es sich dagegen um einen "relativen" Bezug. Excel passt solche Bezüge automatisch an, so dass sie sich beispielsweise immer auf die Zelle links von der Formelzelle beziehen.
So können Sie die Kilometergeld-Formel problemlos nach unten kopieren. Sie bezieht sich immer auf die Basiswerte in B4, C4 und C5, während die variable Gesamtkilometerzahl aus der Zelle neben der Formel einfließt.