Km-Kosten automatisch berechnen lassen

Forumthread: Km-Kosten automatisch berechnen lassen

24.08.2013 23:29:46

Burghard

Hallo,
ich suche eine Lösung für eine automatische Berechnung der Fahrtkosten bei wechselnden Fahrern. Ich habe dazu eine Musterdatei hochgeladen.
https://www.herber.de/bbs/user/87010.xls
In den Spalten/Zeilen B6 bis K12 kann man sehen, dass die Fahrer ständig wechseln. Ich möchte nun, dass z.B. in der Spalte B für die erste Zahl (= 50) dieser Wert in die Zelle B19 übertragen und mit der Zelle A1 multipliziert wird.
Der zweite Wert in Spalte B (= 30) soll in die Zelle B20 übertragen und mit der Zelle A1 multipliziert werden.
Der dritte Wert in Spalte B (= 60) soll in die Zelle B21 übertragen und mit der Zelle A1 multipliziert werden.
Ich habe die Berechnung in den Zellen B19:B21 manuell gemacht, würde dies aber gerne automatisch berechnen lassen.
Ich habe die Km-Zahlen als Beispiel nur für die Spalten B bis D dargestellt.
Das Problem der Berechnung in den Zellen B19 bis B21 besteht darin, dass die Fahrer bei den Fahrten wechseln. Hat jemand eine Lösung?
Grüße
Burghard

4
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: Km-Kosten automatisch berechnen lassen

25.08.2013 00:29:48

fcs
AW: Funktioniert bestens! Erweiterung möglich?

25.08.2013 08:08:57

Burghard
AW: Erweiterung

25.08.2013 12:17:55

fcs
AW: Erweiterung funktioniert!

25.08.2013 22:24:39

Burghard

AW: Km-Kosten automatisch berechnen lassen

25.08.2013 00:29:48

fcs

Hallo Burghard,
eine Lösungsmöglichkeit. Die Formeln kannst du dann nach rechts kopieren.
Fahrten

	A	B
18	Kilometer	Erstattung
19	Fahrer 1	10,00
20	Fahrer 2	6,00
21	Fahrer 3	12,00
22	Summen	28,00

Formeln der Tabelle

Zelle	Formel
B19	{=WENN(ANZAHL2(B$6:B$12)<1;"";INDEX(B$1:B$12;KKLEINSTE(WENN(B$6:B$12>0;ZEILE(B$6:B$12); ZEILE(B$12)); 1))*$A$1)}
B20	{=WENN(ANZAHL2(B$6:B$12)<2;"";INDEX(B$1:B$12;KKLEINSTE(WENN(B$6:B$12>0;ZEILE(B$6:B$12); ZEILE(B$12)); 2))*$A$1)}
B21	{=WENN(ANZAHL2(B$6:B$12)<3;"";INDEX(B$1:B$12;KKLEINSTE(WENN(B$6:B$12>0;ZEILE(B$6:B$12); ZEILE(B$12)); 3))*$A$1)}
B22	=WENN(ANZAHL2(B$6:B$12)=0;"";SUMME(B19:B21))

Enthält Matrixformel:
Umrandende { } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!

Matrix verstehen

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß
Franz

AW: Funktioniert bestens! Erweiterung möglich?

25.08.2013 08:08:57

Burghard

Hallo Franz,
funktioniert 100% wie gewünscht. Danke!
Ich habe doch noch eine Frage: Lässt sich Deine Formel, wie unten beschrieben, noch erweitern bzw. kombinieren?
{=WENN(ZEILEN($1:1)>ZÄHLENWENN(B$6:B$12;"x");"";INDEX($A$6:$A$12;KKLEINSTE(WENN(B$6:B$12="x"; ZEILE($1:$7));ZEILEN($1:1)))) }
{=WENN(ANZAHL2(B$6:B$12)<1;"";INDEX(B$1:B$12;KKLEINSTE(WENN(B$6:B$12>0;ZEILE(B$6:B$12); ZEILE(B$12)); 1))*$A$1) }~f~
~f~
Beide funktionieren für sich. Lassen sie sich auch kombinieren? In etwa so:
~f~{=WENN(ZEILEN($1:1)>ZÄHLENWENN(B$6:B$12;"x");"";INDEX($A$6:$A$12;KKLEINSTE(WENN(B$6:B$12="x";ZEILE($1:$7));ZEILEN($1:1))));WENN(ANZAHL2(B$6:B$12)<1;"";INDEX(B$1:B$12;KKLEINSTE(WENN(B$6:B$12>0;ZEILE(B$6:B$12); ZEILE(B$12)); 1))*$A$1)}
Aber diese Kombination funktioniert so nicht.
Grüße
Burghard

AW: Erweiterung

25.08.2013 12:17:55

fcs

Hallo Burghard,
man kann es in einer Formel kombinieren (wenn x eingetragen, dann Name aus Spalte A, wenn Betrag eingetragen, dann Betrag.
Ich rate hier aber zu Hilfszeilen, in denen der Indexwert berechnet wird. Das macht das ganze Formelgerüst etwas übersichtlicher. Die Hilfszeilen kann man ja ausblenden.
Die Formeln in B19 bzw. B24 kann man nach unten und rechts kopieren.
Die Summenformel ist auch noch leicht modifiziert.
Gruß
Franz
Ohne Hilfszeilen:
Fahrten

	A	B
18	Kilometer	Erstattung
19	Fahrer 1	80,00
20	Fahrer 2	B
21	Fahrer 3	T
22	Summen	80,00

Formeln der Tabelle

Zelle	Formel
B19	{=WENN(ANZAHL2(B$6:B$12)<ZEILE(1:1); "";WENN(INDEX(B$6:B$12;KKLEINSTE(WENN(B$6:B$12<>"";ZEILE($1:$7); 7); ZEILE(1:1)))="x";INDEX($A$6:$A$12;KKLEINSTE(WENN(B$6:B$12<>"";ZEILE($1:$7); 7); ZEILE(1:1))); INDEX(B$6:B$12;KKLEINSTE(WENN(B$6:B$12<>"";ZEILE($1:$7); 7); ZEILE(1:1)))*$A$1))}
B20	{=WENN(ANZAHL2(B$6:B$12)<ZEILE(2:2); "";WENN(INDEX(B$6:B$12;KKLEINSTE(WENN(B$6:B$12<>"";ZEILE($1:$7); 7); ZEILE(2:2)))="x";INDEX($A$6:$A$12;KKLEINSTE(WENN(B$6:B$12<>"";ZEILE($1:$7); 7); ZEILE(2:2))); INDEX(B$6:B$12;KKLEINSTE(WENN(B$6:B$12<>"";ZEILE($1:$7); 7); ZEILE(2:2)))*$A$1))}
B21	{=WENN(ANZAHL2(B$6:B$12)<ZEILE(3:3); "";WENN(INDEX(B$6:B$12;KKLEINSTE(WENN(B$6:B$12<>"";ZEILE($1:$7); 7); ZEILE(3:3)))="x";INDEX($A$6:$A$12;KKLEINSTE(WENN(B$6:B$12<>"";ZEILE($1:$7); 7); ZEILE(3:3))); INDEX(B$6:B$12;KKLEINSTE(WENN(B$6:B$12<>"";ZEILE($1:$7); 7); ZEILE(3:3)))*$A$1))}
B22	=WENN(ANZAHL(B$6:B$12)=0;"";SUMME(B19:B21))

Enthält Matrixformel:
Umrandende { } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!

Matrix verstehen

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
mit Hilfszeilen:
Fahrten mit Hilfszeilen

	A	B
18	Kilometer	Erstattung
19	Fahrer 1	80,00
20	Fahrer 2	B
21	Fahrer 3	T
22	Summen	80,00
23	Hilfsberechnung (Index - Zeile mit Eintrag)
24		2
25		4
26		7

Formeln der Tabelle

Zelle	Formel
B19	=WENN(ANZAHL2(B$6:B$12)<ZEILE(1:1); "";WENN(INDEX(B$6:B$12;B24)="x";INDEX($A$6:$A$12;B24); INDEX(B$6:B$12;B24)*$A$1))
B20	=WENN(ANZAHL2(B$6:B$12)<ZEILE(2:2); "";WENN(INDEX(B$6:B$12;B25)="x";INDEX($A$6:$A$12;B25); INDEX(B$6:B$12;B25)*$A$1))
B21	=WENN(ANZAHL2(B$6:B$12)<ZEILE(3:3); "";WENN(INDEX(B$6:B$12;B26)="x";INDEX($A$6:$A$12;B26); INDEX(B$6:B$12;B26)*$A$1))
B22	=WENN(ANZAHL(B$6:B$12)=0;"";SUMME(B19:B21))
B24	{=KKLEINSTE(WENN(B$6:B$12<>"";ZEILE($1:$7); 7); ZEILE(1:1))}
B25	{=KKLEINSTE(WENN(B$6:B$12<>"";ZEILE($1:$7); 7); ZEILE(2:2))}
B26	{=KKLEINSTE(WENN(B$6:B$12<>"";ZEILE($1:$7); 7); ZEILE(3:3))}

Enthält Matrixformel:
Umrandende { } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!

Matrix verstehen

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4

AW: Erweiterung funktioniert!

25.08.2013 22:24:39

Burghard

Hallo Franz,
das sind Super-Lösungen, die Du mir da geschickt hast. Funktionieren genau wie gewünscht! Ich bin mehr als begeistert! Danke, danke!
Grüße
Burghard

Infobox / Tutorial

Schritt-für-Schritt-Anleitung

Um die Fahrtkosten automatisch in Excel zu berechnen, folge diesen Schritten:

Vorbereitung der Tabelle:
- Erstelle eine Excel-Tabelle mit den Spalten für die Fahrer und die gefahrenen Kilometer.
- Setze in Zelle A1 den Preis pro Kilometer (z.B. 0,30 €).
Einfügen der Formeln:
- In Zelle B19 gib folgende Formel ein, um die Kosten für den ersten Fahrer zu berechnen:
```
=WENN(ANZAHL2(B$6:B$12)<1;"";INDEX(B$1:B$12;KKLEINSTE(WENN(B$6:B$12>0;ZEILE(B$6:B$12);ZEILE(B$12));1))*$A$1)
```
- Kopiere diese Formel nach unten für die Zellen B20 und B21, indem du die Zeilenanzahl in der Formel entsprechend anpasst (KKLEINSTE(...;2) für B20 und KKLEINSTE(...;3) für B21).
Summenformel:
- In Zelle B22 kannst du die Gesamtsumme der Fahrtkosten berechnen:
```
=WENN(ANZAHL2(B$6:B$12)=0;"";SUMME(B19:B21))
```
Matrixformeln:
- Achte darauf, die Matrixformeln mit STRG + SHIFT + RETURN abzuschließen, um die korrekten Werte zu erhalten.

Häufige Fehler und Lösungen

Fehler: #NAME?:
- Überprüfe, ob die Formel korrekt eingegeben wurde. Dies passiert oft, wenn Excel bestimmte Funktionsnamen nicht erkennt.
Fehler: #WERT!:
- Achte darauf, dass die Zellen, auf die verwiesen wird, die richtigen Datentypen enthalten (z.B. keine Textwerte in Zahlenfeldern).
Formel funktioniert nicht wie gewünscht:
- Stelle sicher, dass die Zellbezüge korrekt sind und die Daten in den Zellen B6 bis B12 tatsächlich Zahlen sind.

Alternative Methoden

Falls die oben genannten Formeln nicht deinen Anforderungen entsprechen, kannst du auch eine Excel-Tabelle berechnen lassen:

Verwendung von Pivot-Tabellen:
- Erstelle eine Pivot-Tabelle, die die gefahrenen Kilometer und die entsprechenden Kosten pro Fahrer zusammenfasst.
Excel-Funktionen:
- Nutze die Funktionen SUMMEWENN oder SUMMEWENNS, um die Fahrtkosten nach bestimmten Kriterien zu summieren.
Makros:
- Wenn du häufig ähnliche Berechnungen durchführst, kannst du ein Makro erstellen, das diese Schritte automatisch ausführt.

Praktische Beispiele

Beispiel 1:
- Du hast 50 km für Fahrer 1 und 30 km für Fahrer 2. Setze in A1 den Preis von 0,30 €/km und wende die oben genannten Formeln an. Die Ergebnisse in B19 und B20 sollten 15,00 € und 9,00 € anzeigen.
Beispiel 2:
- Wenn du eine Fahrtkosten Tabelle in Excel für mehrere Fahrer hast, kannst du die Formeln leicht anpassen, um die Kosten für jeden Fahrer und jede Fahrt zu berechnen.

Tipps für Profis

Verwende benannte Bereiche:
- Dies erleichtert die Lesbarkeit deiner Formeln, z.B. nenne den Bereich für die Kilometer „Kilometer“ und verwende diesen Namen in deinen Formeln.
Automatisierung:
- Nutze Excel-Tabellen und -Diagramme, um die Daten visuell darzustellen.
Datenvalidierung:
- Setze Datenvalidierung ein, um sicherzustellen, dass nur gültige Werte in die Kilometer-Spalte eingegeben werden.

FAQ: Häufige Fragen

1. Wie kann ich die Kosten pro km berechnen?
Du kannst die Formel =Kosten / Kilometer verwenden, um die Kosten pro Kilometer zu berechnen.

2. Funktioniert das auch in älteren Excel-Versionen?
Ja, die beschriebenen Formeln sollten in den meisten Excel-Versionen funktionieren, jedoch können sich einige Funktionen geringfügig unterscheiden.

3. Gibt es eine Vorlage für eine km Geld Abrechnung?
Ja, viele kostenlose Vorlagen für km Kosten berechnen sind online verfügbar, die du anpassen kannst.

4. Wie kann ich die Ergebnisse in einer Tabelle zusammenfassen?
Verwende die Funktion SUMME oder erstelle eine Pivot-Tabelle, um die Ergebnisse effizient zusammenzufassen.

Beliebteste Forumthreads (12 Monate)

Km-Kosten automatisch berechnen lassen

Forumthread: Km-Kosten automatisch berechnen lassen

4 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Infobox / Tutorial

Km-Kosten automatisch berechnen lassen

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

4
Beiträge zum Forumthread

Beiträge zu diesem Forumthread