Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1328to1332
1328to1332
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Km-Kosten automatisch berechnen lassen

Forumthread: Km-Kosten automatisch berechnen lassen

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

Anzeige

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

Betreff
Datum
Anwender
Anzeige
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

 AB
18KilometerErstattung
19Fahrer 110,00 €
20Fahrer 26,00 €
21Fahrer 312,00 €
22Summen28,00 €

Formeln der Tabelle
ZelleFormel
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

Anzeige
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

Anzeige
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

 AB
18KilometerErstattung
19Fahrer 180,00 €
20Fahrer 2B
21Fahrer 3T
22Summen80,00 €

Formeln der Tabelle
ZelleFormel
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

 AB
18KilometerErstattung
19Fahrer 180,00 €
20Fahrer 2B
21Fahrer 3T
22Summen80,00 €
23Hilfsberechnung  (Index - Zeile mit Eintrag) 
24 2
25 4
26 7

Formeln der Tabelle
ZelleFormel
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

Anzeige
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
Anzeige
Anzeige

Infobox / Tutorial

Km-Kosten automatisch berechnen lassen

Schritt-für-Schritt-Anleitung

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

  1. 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 €).

  2. 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).

  3. Summenformel:

    • In Zelle B22 kannst du die Gesamtsumme der Fahrtkosten berechnen: 
      =WENN(ANZAHL2(B$6:B$12)=0;"";SUMME(B19:B21))

  4. 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:

  1. Verwendung von Pivot-Tabellen:

    • Erstelle eine Pivot-Tabelle, die die gefahrenen Kilometer und die entsprechenden Kosten pro Fahrer zusammenfasst.

  2. Excel-Funktionen:

    • Nutze die Funktionen SUMMEWENN oder SUMMEWENNS, um die Fahrtkosten nach bestimmten Kriterien zu summieren.

  3. 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)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Anzeige