Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1892to1896
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Ausgaben Kalenderwochen zuordnen

Ausgaben Kalenderwochen zuordnen
25.07.2022 12:53:13
Matthias
Liebe Profis,
ich möchte Ausgaben (ca. 400) dynamsich Kalenderwochen (derzeit 2. Halbjahr) zuordnen, um dann dort die Beträge je Woche zusammenzufassen und später grafisch auszuwerten.
Dazu habe ich die anliegende Datei als Muster vorbereitet. So kann ich diese aus einem Buchhaltungssystem im vorderen Bereich übernehmen. Beträge, die monatlich erscheinen, haben den Typ "monatlich"; es gibt noch vierteljähliche und jährliche Beträge. Die monatlichen und vierteljährlichen Beträge sollen in den Folgemonaten abhängig vom Zahldatum erneut erscheinen. Jährliche Beträge nur, wenn diese noch im Datumsraum 2. Halbjahr anfallen.
Also habe ich aus dem Startdatum zunächst für dier nächsten 6 Monate die Folgetermine errechnet (grauer Bereich) und daraus die KW (gründer Bereich) abgeleitet. Nun möchte ich in dem hellblauen Bereich die Werte aus Spalte D je KW anzeigen. Mit SVERWEIS komme ich nicht zur Lösung. Wie könnte ich es lösen?
https://www.herber.de/bbs/user/154359.xlsx
Ich freue mich sehr auf Hinweise.
Beste Grüße, Matthias

12
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: die KWs sollen nicht für D gelten? ...
25.07.2022 13:22:36
neopa
Hallo Matthias,
... denn da muss das 2. Argument von KALENDDERWOCHE() eine 21 sein. Und demzufolge in Spalte R auch mit KW 26 beginnen.
Gruß Werner
.. , - ...
AW: die KWs sollen nicht für D gelten? ...
25.07.2022 14:10:02
Matthias
Danke Werner, zur Erstellung der KW-Werte im grünen Bereich addieren ich die Spalte C dazu. Bei Jährlich nichts, bei Quartalszahlungen 3 (Monate) und bei monatlichen Zahlungen 1 (Monat). So bekomme ich aus dem Monatsdatum im grauen Bereich die KW-Nummer, in die später der Betrag eingetragen werden soll. Ist aber ne Krücke...geht vielleicht besser...?
AW: was ich meinte ist ...
25.07.2022 15:07:15
neopa
Hallo Matthias,
... dass die DIN-KW des 1.7.22 mit folgender Formel nicht mit =KALENDERWOCHE(F3)=27 ermittelt wird sondern mit =KALENDERWOCHE(F3;21)=26
Gruß Werner
.. , - ...
Anzeige
AW: für deutsche KWen ...
25.07.2022 13:34:58
neopa
Hallo Matthias,
... mit nachfolgender Formel, die auf Basis der (deutschen!) KWen Formel (die KALENDERWOCHE()-Funktion ist nicht matrixtauglich) von Franz Pölt und mit Hilfe von AGGREGAT() und SUMMENPRODUKT() in R3:
=SUMMENPRODUKT((AGGREGAT(15;6;KÜRZEN(($F3:$K3-DATUM(JAHR($F3:$K3+3-REST($F3:$K3-2;7));1;REST($F3:$K3-2;7)-9))/7);ZEILE($A$1:INDEX($A:$A;ANZAHL($F3:$K3))))=R$1)*$D3)
und diese Formel nach rechts und unten ziehend kopieren.
Gruß Werner
.. , - ...
Kalenderwoche klappt mit Summenprodukt..
25.07.2022 13:51:22
SF
..und zwar wenn man vor den Bereich ein + schreibt ;-)
Also zB:

=summenprodukt((Kalenderwoche(+A2:A10;21)=1)*(B2:B10="test"))

Anzeige
AW: damit wird meine Formel natürlich einfacher...
25.07.2022 14:02:10
neopa
Hallo,
... den Trick mit dem "+" vor der Bereichsangabe dieser Funktion kannte ich bis jetzt noch nicht. Danke für die Information.
Dann, Matthias, meine Formel in R3:
=SUMMENPRODUKT((AGGREGAT(15;6;KALENDERWOCHE(+$F3:$K3;21);ZEILE($A$1:INDEX($A:$A;ANZAHL($F3:$K3))))=R$1)*$D3)
und nach rechts und unten kopieren.
Gruß Werner
.. , - ...
AW: damit wird meine Formel natürlich einfacher...
25.07.2022 16:47:11
Matthias
Danke Werner. Die Formel funktioniert zuächst nicht bei mir, weil sie auf Zelle A1 verweist. Was muss dort stehen? LG, Matthias
AW: damit wird meine Formel natürlich einfacher...
25.07.2022 16:53:31
Matthias
Sorry - funktioniert doch.Damit entfällt auch der bisherige Bereich mit der Umwandlung der Datumswert in KW. Vielen Dank!!!
Anzeige
AW: bitteschön owT
25.07.2022 19:42:36
neopa
Gruß Werner
.. , - ...
AW: Ausgaben Kalenderwochen zuordnen
25.07.2022 14:16:57
Yal
Moin zusammen,
wie üblich versuche ich die Lösung mit Power Query aufzubauen. (VBA ist abgelutscht ;-)
https://www.herber.de/bbs/user/154360.xlsx
Ich verwende die Funktion List.Generate in Kombi mit Date.AddMonths, was nicht optimal, weil List.Generate ein festes "Stepping" erfordert. Ich kann die vorhandene Taktung nur mit einem unschöne If-Verschachtelung verwenden (Jahrlich ist übrigens 12 und nicht 0).
Vielleich gibt es bessere Lösung.
VG
Yal
AW: Ausgaben Kalenderwochen zuordnen
25.07.2022 16:57:07
Matthias
Hallo Yal, danke für die Beispieldatei. Die stürtzt bei mir ab...sorry. LG, Matthias
Anzeige
Bei mir auch, wenn...
25.07.2022 17:39:49
Yal
... die automatische Speicherung eingeschaltet ist.
"Optionen", "Speichern", "AutoWiederherstellen-Informationen speichern alle" ausschalten.
VG
Yal

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige