Werte aus Kalenderwochen addieren

16.08.2025 14:02:50

Hans

Hallo zusammen.

Ich versuche mich schon tagelang an einem Problem, komme aber nicht so recht weiter. Deswegen meine Anfrage bzgl. Unterstützung.

Mir liegen in den Spalten AJ2 bis AJ54 die Kalenderwochen vor (1-53). In Spalte D1 bis D54 stehen die verkauften Stückzahlen eines Kunden drin pro Kalenderwoche.

Ich benötige aber nicht die verkauften Stückzahlen der Kalenderwochen, sondern pro Monat.

Mit der folgenden Formel klappt es fast ganz gut, aber: Es wird dabei nicht berücksichtigt, wenn eine Kalenderwoche 2 verschiedene Monate enthält. (z.B. Kalenderwoche 31 enthält Mo bis Do den Juli, der Freitag ist der erste August.)

=SUMMEWENNS(Auswertung!D$2:D$54;Auswertung!$AJ$2:$AJ$54;VERGLEICH($A4; {"Januar";"Februar";"März";"April";"Mai";"Juni";"Juli";"August";"September";"Oktober";"November";"Dezember"}; 0))

Hat dazu jemand einen Idee bzw. Vorschlag, wie ich noch aus den Kalenderwochen die dazugehörigen Monatstage ermitteln kann, damit die Berechnung stimmt (Nur verkaufte Stückzahlen berechnen, die zu einem Monat gehören).

Ich sage schon mal danke für die Unterstützung.

Viele Grüße
Hans

AW: Werte aus Kalenderwochen addieren

16.08.2025 14:56:16

Uduuh

Hallo,
der Freitag ist der erste August.
und wenn du an den Tag nichts verkauft hast?
Den Montag einer KW ermittelst du so:
https://www.herber.de/excelformeln/pages/Datum_des_Montags_einer_vorgegebenen_Kalenderwoche.html

Oder leg dir eine Hilfstabelle an: Datum - KW

Gruß aus'm Pott
Udo

Mal als...

16.08.2025 15:07:46

Case

Moin Hans, :-)

... ersten Ansatz: ;-)
https://www.herber.de/bbs/user/178616.xlsx

Ist auch eine Formel für Excel 365 drin. Für Monat als Zahl und als Text.
Mach was draus - ich gehe jetzt in den Biergarten. ;-)

Servus
Case

AW: Mal als...

16.08.2025 18:24:08

Hans

Hallo Case,

erst mal meinen Dank, dass Du dich um mein Anliegen gekümmert hast.

Leider klappt es noch nicht so richtig. Ich habe in Deiner Beispieldatei mal von D2 bis D10 alle Werte auf =0 gesetzt, trotzdem wird in den Zellen J1 und J3 ein Wert von 83,29 ausgegeben.

Soll heißen, in den Kalenderwochen 1 - 10 wurde nichts verkauft, also müsste ja im Januar ein Wert null berechnet werden.

Ich bin in deiner Bespielformel nicht fit, um die komplett zu verstehen. Aber vielleicht schaust Du noch mal drüber nach deinem Biergartenbesuch.

Lieben Dank und Gruß

Hans

AW: Mal als...

16.08.2025 19:01:07

Sigi.21

Hallo Hans,

das Ganze ist nicht so einfach. Eine KW hat 7 Tage, die alle in den selben Monat fallen können.
Sie können aber auch verteilt sein in 2 Monate (6/1, 5/2, 4/3, 3/4, 2/5, 6/1). Wie aufteilen?
Dann stellt sich die Frage, ob Umsätze auch an Wochenenden waren oder nur an Arbeitstagen? Müssen ggf. auch Feiertage berücksichtigt werden?
(Bsp.-Mappe?)

Gruß Sigi

AW: Mal als...

16.08.2025 19:21:58

Hans

Hallo Siggi, hallo Yal,

danke für eure Rückmeldungen. Ja, auch am Wochenende könnten Umsätze anfallen (in de Regel aber von Mo - Frei), Genau so Feiertage. In der Regel aber nicht.

Im Anhang mal eine einfach aufgebaute Beispielmappe. Zur Veranschaulichung habe ich die Tage und Daten händisch in die Spalten E und F eingetragen, damit man sieht, wie die Tage in den Kalenderwochen verteilt sind.

Mit meiner jetzigen Formel klappt es, die Kalenderwochen zu addieren, aber Tage vom Folgemonat werden aber schon in der alten Kalenderwoche mit berechnet!

Es muss also noch eine Formel geben, die erst in den Kalenderwochen prüft, ob die Tage noch in den gewünschten Monat fallen.

@Yal. Ja, vom PQ habe ich gehört, leider keinerlei Erfahrung damit!

https://www.herber.de/bbs/user/178617.xlsx

Gruß

Hans

AW: Mal als...

16.08.2025 21:32:24

Sigi.21

Hallo Hans,

s. Mappe.
https://www.herber.de/bbs/user/178618.xlsx

Gruß Sigi

AW: Mal als...

16.08.2025 23:11:00

Eifeljoi 5

Hallo

Mein Vorschlag mit PQ würde etwa so aussehen.
Bitte das Jahr in A2 nach deinen Wünschen ändern und abschließend PQ-Tabelle Aktualisieren.
https://www.herber.de/bbs/user/178620.xlsx

AW: Mal als...

17.08.2025 00:20:57

Yal

Moin,

@Eifeljoi: ich es schon mal gesagt, aber ich kann es nicht anders als mich zu wiederholen: deine komische Gewohnheit, dir unnötige Arbeit zu geben, um aus einen ganz normalen, per Klick erzeugten Code ein unlesbaren und unerweiterbaren Kuddelmuddel zu machen, ist höchst kontraproduktiv. Eigentlich bist Du aufgrund deiner Kompetenz eine Bereicherung für das Forum, aber was Du da lieferst, kann nur der Absicht haben, die Fragenden von Power Query abzuschrecken. Höre bitte auf damit.

@Hans:
Da ich Eifeljois Power Query ("PQ") Lösung nicht begutachten könnte und ihm am Ende wahrscheinlich applaudieren, musste ich eine PW-Lösung komplett nachbauen. Ich habe auf die Lösung von Case aufgebaut, weil er die Montags anhand der KW errechnet hat.

Was habe ich gemacht:
- in der Quelle habe ich die Zeile unter der Überschrift darüber versetzt, sodass der Inhalt der Tabelle schlüssig: in "KW"-Spalte nur KW-Zahlen, in "Umsatz pro KW" nur umsätze, usw.
- dann den Bereich A4:D58 markiert und daraus eine Tabelle gemacht (Menü "Einfügen", "Tabelle")
- über Menü "Daten", "Aus Tabelel/Bereich" diese Tabelle als Datenquelle in Power Query gebracht
- dort 5 Tage pro Woche erzeugt (Montag bis Freitag) und jede Tag ein Fünftel der Wochenumsatz zugewiesen
- das Ergebnis in einer Pivottable als neues Blatt "Auswertung" gebracht.

Ergebnis sieht so aus:
https://www.herber.de/bbs/user/178621.xlsx

Power Query ist gar nicht so schwer zu lernen und zu verstehen (es sei denn Eifeljoi hat es rumgebastelt). Hier ein Tutorial vom Grund auf: https://excelhero.de/power-query/power-query-ganz-einfach-erklaert

VG
Yal

AW: Mal als...

18.08.2025 04:58:33

Luschi

Hallo Yal,

- da ich auch neugierig war, was 'Eifeljoi 5' da pq-mäßig ausgebrütet hat
- habe ich diesen M-Code ein bißchen aufgedröselt, ohne am Inhalt des M-Codes etwas zu verändern
- das Problem einer solchen M-Schreibweise ist, daß man auf vorherige PQ-Schritte nicht verweisen kann
- sondern diesen PQ-Schritt erneut aufrufen muß!!!
- z.B.: Table.TransformColumnTypes(Excel.CurrentWorkbook(){[Name="Jahr"]}[Content], {{"Column1", Int64.Type}}){0}[Column1] gleich 5 mal
- und das Lesen von Daten aus dem Tabellenblatt ist aber als entscheidender Zeitfaktor eine Bremse
- trotzdem schaue ich mir seine/ihre PQ-Lösungen gern an, denn da sind immer wieder interessante Teillösungsschritte dabei
- wie hier die Erstellung der Kalendertabelle mittels List.Generate() in den Schritten 'b_1' und 'b_2'
- bitte nicht wundern über die merkwürdigen Bezeichnungen der Schrittfolgen
- aus Ärgern über diese bescheuerte M-Codierung habe ich vor über 3 Jahren meine C#-Kenntnisse wieder reaktiviert
- und entschlüßle den zusammengequetschten M-Code damit
- ist zwar noch nicht ganz perfekt, aber sehr nah dran

https://www.herber.de/bbs/user/178625.xlsx

Gruß von Luschi
aus klein-Paris

PS: ich nehme ja an, daß der PQ-M Autor ebenfalls ein Tool benutzt, um diesen, für den normalen PQ-Anwender nicht brauchbaren Code zu produzieren!
Bisher habe ich auf meine Einwände noch NIE 1 Antwort erhalten!

AW: Mal als...

18.08.2025 13:45:43

Yal

Hallo Luschi,

viele Punkte von dir könnte ich sofort unterschreiben.

- trotzdem schaue ich mir seine/ihre PQ-Lösungen gern an, denn da sind immer wieder interessante Teillösungsschritte dabei
Ich bin auch sehr interessiert, an was meinen PQ-Mitstreiter produzieren. So kann man lernen. Ich bin dementsprechend enttäuscht, wenn alles gemacht wird, dass man es nicht lesen kann.

- habe ich diesen M-Code ein bißchen aufgedröselt, ohne am Inhalt des M-Codes etwas zu verändern
habe ich versucht, aber schnell aufgegeben, weil per Hand.

- und das Lesen von Daten aus dem Tabellenblatt ist aber als entscheidender Zeitfaktor eine Bremse
Jein. Es ist bekannt, dass PQ eine Optimierung des Codes vornimmt. Es wäre zu testen, ob diese Wiederholung tatsächlich 5 mal gemacht werden.

- bitte nicht wundern über die merkwürdigen Bezeichnungen der Schrittfolgen
hast ja keine Wahl. Wenn ich ein Code von null auf schreibe (Gott bewahre!), bin ich auch nicht lyrisch kreativ.

PS: ich nehme ja an, daß der PQ-M Autor ebenfalls ein Tool benutzt, um diesen, für den normalen PQ-Anwender nicht brauchbaren Code zu produzieren!
Das wäre die nächste Stufe der Nutzlosigkeit. Geht es hier nicht im Forum darum, dass wir die Fragenden nicht nur "ein Fisch geben, sondern das Hangeln beibringen"?

Zu der PQ-Lösung selbst (wenn ich jetzt sie verstehen kann):
Power Query Kompetenz auf alle Fälle vorhanden, aber komplizierte Lösungsweg. Warum über Join auf einer aufwendig herstellten Liste gehen, wenn die Information direkt extrahiert werden können?

Meine -daraus resultierende- Vorschlag:

let

    Jahr = Excel.CurrentWorkbook(){[Name="Jahr"]}[Content]{0}[Column1],

    Quelle = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],

    #"Hinzugefügte benutzerdefinierte Spalte" = Table.AddColumn(Quelle, "ErsteWochentag", each Date.AddWeeks(Date.StartOfWeek(#date(Jahr, 1, 4), Day.Monday), [KW] -1)),

    #"Name des Monats eingefügt" = Table.AddColumn(#"Hinzugefügte benutzerdefinierte Spalte", "Monatsname", each Date.MonthName([ErsteWochentag]), type text),

    #"Gruppierte Zeilen" = Table.Group(#"Name des Monats eingefügt", {"Monatsname"}, {{"Umsatz pro Monat", each List.Sum([Umsatz pro KW]), type nullable number}})

in

    #"Gruppierte Zeilen"

Bis auf der doppelten Datenquelle und dem "Date.AddWeeks" fast alles nur per Klick einzurichten.

VG
Yal

AW: Mal als...

16.08.2025 22:33:32

daniel

Wie soll den verfahren werden, wenn wie mne Kalenderwoche in zwei Monaten liegt?

a) der Betrag wird vollständig dem Monat zugewiesen, in dem die KW beginnt
b) der Betrag wird vollständig dem Monat zugewiesen, in dem die KW endet
c) der Betrag wird dem Monat zugewiesen, in dem der größte Teil der KW liegt
d) der Betrag wird anteilsmäßig zwischen den beiden Monaten entsprechend der Anzahl Tage aufgeteilt

Wichtig wäre dann noch zu wissen, ob nur die Arbeitswoche (Mo-Fr) oder die ganze Woche (Mo-So) betrachtet werden soll.

Gruß Daniel

AW: Werte aus Kalenderwochen addieren

16.08.2025 17:52:47

Yal

Hallo Hans,

ohne eine Beispieldatei zum Probieren wird es schwierig. (Kommt ein Mann allein zum Arzt und sagt "Mein Sohne ist krank"...)

Du könntest aus jeder KW den Montag und die 4 Tage danach extrahieren (das Datum wird der Monatswechsel wahrnehmen) und jeden diesen Tag einen Fünftel des Verkaufs zuordnen.

Mit Power Query geht es am besten.

Eine Auswertung kann man übrigens mit Pivottable machen (flexibler als Formeln).

VG
Yal

AW: Werte aus Kalenderwochen addieren

16.08.2025 19:16:00

RPP63

Moin!
Es gibt ja das Sprichwort mit dem Fisch, dessen Kopf sowie einem strengen Geruch …
Wenn man Bestellungen nur nach KWs ermittelt, ist es schlicht seriös nicht möglich, wieder auf das Datum und somit den Monat herunterzubrechen!
Beispiel:

	A	B	C	D	E
1	Datum	KW	Stück	Durchschn.	Differenz
2	Di\|01.07.25	27	627	538	85,81%
3	Mi\|02.07.25	27	618	538	87,06%
4	Do\|03.07.25	27	603	538	89,22%
5	Fr\|04.07.25	27	526	538	102,28%
6	Sa\|05.07.25	27	316	538	170,25%
7		Summe	2690

Zelle	Formel
D2	=C$7/5
E2	=D2/C2
C7	=SUMME(C2:C6)

Wenn man pro Wochentag 1/5 nimmt, erhält man für den Freitag 70,25 % des Tagesdurchschnitts.
Das hat nichts mehr mit Mathematik zu tun!

Gruß Ralf

AW: Werte aus Kalenderwochen addieren

16.08.2025 22:47:37

Yal

Moin,

Ich kenne eher den Spruch:
Daten verdichten heißt Daten vernichten.

In den Fall bedeutet es, dass Daten nach Kalenderwoche zu erfassen oder zusammenfassen, dazu führt, dass es nicht mehr tagesgenau ist, und dementsprechend nicht mehr auf Monat aggregierbar ist.

Entweder gibt es eine Möglichkeit, die Verkäufe tagesgenau zu haben, oder muss man sich damit abfinden, dass die Monatswert eine Unschärfe haben (je ein Fünftel auf Mo bis Fr). Die Gesamtheit bleibt weiterhin erhalten.

Die Frage ist, wie wichtig diese monatliche Genauigkeit ist. Wenn es richtig wichtig wäre, hätte man längst auf einer tagesgenauen Erfassung umgestellt.

VG
Yal

Werte aus Kalenderwochen addieren

Forumthread: Werte aus Kalenderwochen addieren

14
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Werte aus Kalenderwochen addieren

Forumthread: Werte aus Kalenderwochen addieren

14 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

14
Beiträge zum Forumthread

Beiträge zu diesem Forumthread