Ich bin für ein Flottenmanagment auf der Suche nach einer Formel, bei der Wochenweise (MO-SO) Zeilen addiert werden, die sich anhand der Kalenderwoche (Montags oder Erster Tag des Monats) orientieren soll. Falls zum Monatsanfang die Woche bereits angefangen hat, so sind nur die fehlenden Tage (1. bis Sonntag) zum Übertrag hinzuzuaddieren. Zum Monatsende soll eine angefangene Woche addiert werden, aber nur sofern sie nicht komplett ist, diese Summe wird in den nächsten Monat übertragen. Je nach Monat ist die Position der Woche/Kalenderwoche logischerweise variabel. Siehe Anhang.
Den Übertrag zum nächsten Monat werde ich per Makro lösen, das bekomme ich hin. Ich wollte nur den kompletten Vorgang erklären, um Missverständnisse auszuschliessen.
Ich brauche nur die 3 Formeln, bei der SUMMEWENN plus weitere Zeilen, sowie ggf. Monatsanfang/-ende. Ich würde das lieber per Formel als per Makro lösen, da ich die gleiche Formel für weitere Daten anwenden möchte.
Ich habe die angehängte Datei auf das Wesentliche reduziert und zur besseren Verständlichkeit farblich markiert. Der leere Bereich rechts ist im Original durch weitere Daten belegt, daher müssen die wöchentlichen Zwischensummen unter der Tabelle stehen.
Des Weiteren habe ich in diesem Zusammenhang noch ein weiteres Problem, ich will in der Wochensumme unten die Kalenderwochen von oben mit KKleinste(1-5) auslesen, jedoch wird der Wert nicht übertragen. Warum?
wenn ich die Beispiel Tabelle richtig verstanden habe gibt es mehrere LKW wo die Summe eingefügt werden muss.
Das macht dieses Programm in einem Rutsch, für alle LKWs. Das Blatt muss dafür mit dem Text "LKW" beginnen!
Bitte mal Testen ob es so klappt. Notiz: Bei der Formel kann es sein das man die engl. Schreibweise nehmen muss: "=SUM("
in meinem Beispiel klappte die KW Formel nicht! Ich habe sie umgestellt: "=GROSS($A$4:$A$34,5) in umgekehrter Reihenfolge. 5 Oben, 1 Unten.
mfg Gast 123
Code:
Option Explicit '12.8.2017 Gast 123
Dim Anf As String, Ende As String
'Modul für Wochen Kilometer Summe
Sub Kilometer_Wochen_Summe()
Dim AC As Range, k, n, z As Integer
'alls Tabellen mit Name: "LKW"
For k = 1 To Worksheets.Count
With Worksheets(k)
'1. Zeile für KW Formel ermitteln
z = .Range("A36").End(xlDown).Row
If Left(.Name, 3) = "LKW" Then
For Each AC In .Range("A4:A34")
If AC.Text <> "" Then Anf = AC.Offset(0, 4).Address
If AC.Offset(0, 2).Text = "Fr" Or AC.Row = 34 Then
z = z + 1 'next Zeile + 1
Ende = AC.Offset(0, 4).Address
.Cells(z, "E").FormulaLocal = "=SUMME(" & Anf & ":" & Ende & ")"
End If
Next AC
n = n + 1 'Anzahl Sheets
End If
End With
Next k
MsgBox n & " Tabelle/n ausgefüllt"
End Sub
Ich konnte mit euren Lösungen leider nichts anfangen. Aber ich habe das nun selbst gelöst mit weiteren Formeln in Hilfszellen. Über die Eleganz läßt sich streiten. Da ich nun weiß, dass bei KKLEINSTE die Nullwerte mitgezählt werden, zähle ich einfach die Anzahl der Nullen, und addiere für KKLEINSTE einfach eine Zahl hinzu für den kleinsten Wert ohne Null. Und für die Summe aller Tage innerhalb einer Kalenderwoche lasse ich mir die KW einfach für jede Zeile anzeigen und addiere diese Werte. Die Spalte wird ausgeblendet, so dass man sie nicht sieht.
@Lupo
Ich war auf der Suche nach einer Formel, nicht auf der Suche nach der kompletten Umgestaltung meiner Tabelle, die Arbeit hättest Du Dir sparen können. Trotzdem Danke für deine Mühe! Wie bereits erwähnt habe ich die restliche Tabelle (also viele weitere Spalten mit notwendigen Daten) für das Beispiel entfernt, und das wesentliche, um das es geht zu zeigen, deine Idee passt leider so gar nicht zu meinem Konzept, da sie zu viel Platz einnimmt und umständlich rechnet. Ich habe lange gebraucht um deine Tabelle überhaupt zu verstehen, was u.a. daran lag, dass sie Fehler enthielt, ausserdem ist eine fortlaufende Tabelle nicht gewünscht. Dass Dir nicht gefällt, dass ich zuviele Spalten für das Datum nutze tut mir leid, aber ich finds wegen der Ästhetik so besser, als wenn es in eine Zelle geschrieben wird, was technisch kein Aufwand wäre, aber das muß ja nicht dein Problem sein.
@Gast123
Danke für dein Makro, ich habs probiert, aber leider nicht hinbekommen, deswegen war mir der Weg per Formel lieber, davon verstehe ich mehr, allerdings gibts bei mir auch den Unterschied, dass jedes Blatt ein Fahrzeug umfasst und somit die Blätter verschiedene Namen haben. Dennoch vielen Dank für deine Mühe.
20.08.2017, 00:08 (Dieser Beitrag wurde zuletzt bearbeitet: 20.08.2017, 00:09 von Jockel.)
Hallo, wenn ich dich richtig verstanden habe geht hier ff. auch ohne Hilfsspalte ... (die Formel ab E39 habe ich nach einem Tipp von shift-del "kreiert"...)
Danke, das war genau die Formel, die ich ursprünglich gesucht hatte und ist die etwas elegantere Lösung im Gegensatz zu meiner.
Auf die Idee in der Datum Spalte nach der Kalenderwoche zu suchen bin ich ja auch nicht gekommen, ist aber gut.
Ich verstehe allerdings die Logik von "Zeile(A1)" nicht. Und da leider Kalenderwoche "21" nicht mit allen Excel Versionen kompatibel ist, habe ich noch eine Abfrage eingebaut, um welche Excel Version es sich handelt, um Fehlermeldungen zu vermeiden, ältere Versionen müßen hier dann leider die "2" einsetzen, was in manchen Jahren leider zu Fehlern führt, oder kennt hier jemand nen besseren Trick? Gibts auf der Basis der Formel "Summenprodukt" auch eine Option den Mittelwert (ohne Null) in Bezug auf die KW zu ermitteln?
@Lupo
Irgendwie versuchst Du ständig dein Kalenderwochen Problem mit meinem Problem zu verbinden, da wir aber nicht das gleiche Problem haben, finden wir hier vermutlich auch keine gemeinsame Lösung!?
21.08.2017, 02:51 (Dieser Beitrag wurde zuletzt bearbeitet: 21.08.2017, 02:51 von lupo1.)
In #6 schrieb ich schon: Nicht nur für Dich, sondern für alle. "Dein Thread" ist ja auch für die Nachwelt. Ob Du ihn so übernimmst oder ihn nicht willst, ist mir da also eher egal.
Grundsätzlich würde ich z.B.
- am Tag den Ende-Km-Stand ausweisen
- mit Pivot arbeiten, wo immer möglich (mittlerweile gibt es Power Query)
- nicht nur einen Monat auf einem Blatt haben wollen (die meisten Auswertungsfragen gehen über 1 Jahr, oder mit Vorjahresvergleich sogar über 2. Die Frage: "Wieviele km sind wir zu dieser Zeit letztes Jahr gefahren bei welchem Umsatz, kumuliert oder im letzten Monat?" wäre eine meiner Fragen als Chef)
- mich niemals an optische Erfordernisse von irgendeinem Laien-Anwender bei Daten klammern, sondern immer nur sachgerecht strukturieren! Optik ist ausschließlich für Klicki-Bunti-Bonbon-Blättchen an anderer Stelle.
