Office: Treibstoffverbrauchstabelle

Guten Abend liebe Excelfreunde und Profis
Ich habe mal wieder ein für Euch sicherlich kleines Problem.
Ich habe mir eine Sprittabelle angelegt in der ich alle nötigen Werte berechnen lassen möchte.
Soweit funktioniert die Datei gut. Nur bei einer Sache stehe ich mal wieder auf dem Schlauch.
Solange ich "Voll" tanke werden die Werte korrekt berechnet. Aber wenn ich mal nur wenig tanke gelingt mir es nicht einen korrekten Durchschnittsverbrauch zu erhalten. Ich möchte nämlich, dass Excel nur dann einen Durchschnitt berechnet wenn ich in der Spalte D ein x reingemacht habe um zu erkennen, dass ich wieder den Tank Randvoll gemacht habe. Hier soll das so berechnet werden, das nach dem letzten "Volltanken" alle Werte in denen kein X steht + den nächsten Wert in dem ein X steht zusammengezogen werden und dann der Durchschnitt ausgegeben wird. Jetzt könnte ich natürlich hingehen und das im Kopf ausrechnen und per Hand alles in die entsprechenden Spalten ( E und G) eintragen. Ich bin aber davon überzeugt, dass Excel das auch von alleine macht wenn ich die richtige Formel finde. Hierbei brauche ich Eure Unterstützung. Ich möchte nicht die richtige Formel von Euch haben sondern nur die Funktion die das Problem beheben könnte. Die hochgeladene Datei zeigt Euch drei Beispiele die ich unterschiedlich Farblich markiert habe. Der zu erwartende Wert sollte in der Zeile stehen der mit dem x unterhalb der leeren Zellen befüllt ist.

Habt Ihr da einen Lösungsansatz für mich?
Vielen Dank im voraus für Eure Mühe und Hilfe
Ralf B. aus B. an der E.

PS.: Für Fragen stehe ich wie immer gerne zur Verfügung.

 

Hallo,

meine Lösung ist eine benutzerdefinierte Funktion mittels VBA-Programmierung:
Die Funktion lautet in Zelle G2: =Verbrauch(C2;E2;D2)
Diese Formel ist in die Zellen darunter zu kopieren.
Der VBA-Code, der bereits in einem allgemeinen Codemodul enthalten ist, lautet:

Code:

Option Explicit
Public Function Verbrauch(rngLiter As Range, rngKm As Range, rngVoll As Range) As Double
  Dim Liter As Double, Km As Double
  On Error GoTo Err_Km
  If rngVoll.Value = "x" Then
    Liter = rngLiter.Value
    Km = rngKm.Value
    Do
      Set rngVoll = rngVoll.Offset(-1)
      If Len(rngVoll.Value) Then Exit Do
      Set rngLiter = rngLiter.Offset(-1)
      Set rngKm = rngKm.Offset(-1)
      Liter = Liter + rngLiter.Value
      Km = Km + rngKm.Value
    Loop
    Verbrauch = Liter / Km * 100#
  Else
    Verbrauch = 0
  End If
  Exit Function
 
Err_Km:
  MsgBox "Die gefahrenen 'km' müssen > 0 sein" & vbNewLine & _
         "in " & rngKm.Address & " - Abbruch!", vbCritical, "Fehler KM"
  Verbrauch = 0
End Function

Die Excel-Datei liegt bei.

 

Guten Abend Anton Exl
Eine fertige Lösung hatte ich zwar nicht erwünscht (und schon garnicht erwartet), weil ich mir selbst noch etwas beibringen möchte. Ich hoffte nur auf einen Lösungsansatz. Aber mit Deiner Arbeit und Mühe die Du Dir gemacht hast, hast Du mich nicht nur überrascht sondern auch Glücklich gemacht. Genau so sollte das Aussehen.
Ich habe jetzt nur noch ein kleines Problem. Wenn irgendwann einmal etwas nicht funktioniert, muss ich Euch wieder nach Hilfe fragen, weil ich mit VBA auf Kriegsfuss stehe. Ich habe NULL Ahnung von VBA und verstehe die Sprache nicht. Ich habe das mal versucht zu verstehen. Leider ohne Erfolg. Da fallen mir Formeln die man in Zellen schreibt schon wesentlich leichter, weil ich die einzeln überprüfen kann.

Ich werde Deine Lösung zwar gerne benutzen, hoffe aber darauf, dass es eine, für mich besser zu verstehende, Lösung gibt.

Ich Danke Dir für Deine Arbeit und hoffe dass Du auch weiterhin Leuten wie mir unter die Arme greifst.
mit freundlichen Grüßen
Ralf B. aus B. an der E.

 

Hallo,

im Prinzip lautet die Formel für Zelle G15 (wenn ich von deinen Juli-Werten ausgehe) folgendermaßen:
=WENNFEHLER(SUMME(C13:C15)/SUMME(E13:E15)*100;0)
bzw. wenn man noch abfragt, ob (k)ein "x" in Zelle D15 steht:
=WENNFEHLER(WENN(D15="x";SUMME(C13:C15)/SUMME(E13:E15)*100;0);0)
Meine benutzerdefinierte Funktion für Zelle G15: =Verbrauch(C15;E15;D15) tut nichts anderes als vorgenannte Funktion auszurechnen.

Das große (Rest)problem bei der letzten Arbeitsblattfunktion ist, dass die Funktion die beiden Summenbereiche C13:C15 bzw. E13:E15 automatisch erkennen muss. Um das zu umzusetzen, zählt man einfach die in Spalte D vorhandenen "x"-Werte und schreibt zB. in Spalte N, zu welchem x-Zeilennummernbereich eine Zeile gehört (graue Hilfsspalte). Es ergibt sich, dass die Zeilen 13-15 zum Nummernbereich 12 gehören. Die Formel, die das leistet, lautet zB. für Zelle N15:
=WENNFEHLER(VERWEIS(2;1/(D$1:D14="x");ZEILE(C$1:C14));1)
Die endgültige Formel in Zelle G15 muss 2 bedingte Summenformeln enthalten:
=WENNFEHLER(WENN(D15="x"; SUMMEWENN($N$2:$N15;$N15;$C$2:$C15) / SUMMEWENN($N$2:$N15;$N15;$E$2:$E15)*100; 0); 0)

Beide Formeln lassen sich in die weiteren Zellen unterhalb kopieren. Damit das funktioniert, sind die enthaltenen $-Zeichen genau zu beachten (speziell vor den Zeilennummern).
Im Arbeitsblatt "Tanktabelle (3)" habe ich diese Arbeitsblattfunktionen eingebaut und hoffe, genügend Anregungsstoff beim Analysieren der Formeln bereitgestellt zu haben.

 

Guten Abend Anton Exl

Bitte die nächsten 3 Sätze mit einem Lächeln lesen.
In der Schule wäre Deine Antwort eine klare 6 als Note. Die Begründung: Aufgabenstellung verfehlt! Die Aufgabe war, einen Lösungsansatz zu geben damit ich selbst was tüfteln und lernen kann.
Ab hier wieder ensthaft.
Vielen Dank, dass Du Dir noch eine andere Lösung hast einfallen lassen. Hiermit kann ich auch mehr anfangen. Vorallem wenn ich mal die Lust verspüre die Spalten umzustellen um der Tabelle ein anderes Erscheinungsbild zu geben. In den Formeln kann ich das entweder selbst ändern oder es geht eben automatisch was ja bei VBA gleich zu Problemen führen würde die ich dann nicht angleichen könnte.
Deshalb ist mir diese Lösung viel lieber als die erste.
Obwohl ich ja nach einem Lösungsansatz gefragt hatte, glaube ich, dass ich diese Lösung auch nicht gleich ohne weitere Hilfe zustande bekommen hätte. Da reicht mein Wissen dann doch nicht soweit. Aber hier kann ich wenigstens nachvollziehen was in der Formel abläuft.
Ich Bedanke mich bei Dir und verbleibe mit herzlichen Grüßen
Ralf B. aus B. an der E.