Jahreswerte auf Quartale verrteilen

[schuer]

  Beispiel.xls (Größe: 23,5 KB / Downloads: 8)
Hallo,

ich habe eine Vorschau von sagen wir mal Stückzahlen pro Jahr, die Unterschiedlich sind mal steigend aber auch fallend. Da ich aber für die einzelnen Quartale planen will möchte ich die Stückzahlen auf diese 4 aufteilen ohen allzugroße Sprünge beim Jahreswechsel zu haben.
Beispiel
 2017   2018      2019      2020     2021     2022     2023
 0         35.000   80.000  90.000  70.000  30.000  0
Bei der Aufteilung soll der Wert des Folgejahres berücksichtigt werden so das keine Sprünge entstehen also einfach durch 4 Teilen ist nicht zielführend.
In 2018 / 2019 könnte dies so aussehen:
Q1/18 = 5.000
Q2/18 =  7.500
Q3/18 = 10.000
Q4/18 = 12.500
Die Summe im Jahr soll sich nicht ändern
In 2019 könnte es dann so weitergehen
Q1/19 = 14.000
Q2/19 = 17.000
Q3/19 = 21.500
Q4/19 = 27.500
(Die Zahlen sind nicht gerechnet nur geschätzt)
Frage ist nun wie ich diese Umrechnug / Verteilung formelmäßig erstellen kann das eine Verteilung stattfindet ohne das sich die Jahresmenge ändert.

Danke für Tipps und Anregungen.

Schuer

[Guschti]

Hallo schuer

So geht es leider nicht. Irgend woher hast du ja die Jahreswerte.

Das geht nur, wenn du eine Datenbank mit Datum und Stückzahlen hast.

Mfg Guschti

[LCohen]

Erst mal ein großes Lob:

1. Interessante seltene Frage
2. xls-Datei beigefügt
3. Text von A bis Z verständlich

Vielen Dank dafür!

Behauptung: Da eine Lösung manuell möglich ist, geht dies auch maschinell. 

https://www.youtube.com/watch?v=tHjqW5ic0jM zeigt ein Add-In mit Splines (das sind nonlineare =TREND()s)

Mit Trendlinie einfügen, polynomisch, in diesem Beispiel Grad 2, bekommst Du auch die Funktion, die fast so gut wie die Splines ist.

Nun müsstest Du die geviertelten X-Werte ermitteln. Dafür fängst Du beim kleinsten X-Wert abzüglich anderthalb Vierteln an, damit symmetrisch die Viertel um die Jahre entstehen: 

2016,625, 2016,875, 2017,125, 2017,375, 2017,625, ... oder zum Verständnis 
2017-3/8, 2017-1/8, 2017+1/8, 2017+3/8, 2018-3/8 ... oder als Reihe
2016,625, =A1+1/4, ...

Die resultierenden Y-Werte des Splines oder der Funktion müsstest Du nun ebenfalls /4 teilen. Da deren Summe (4 Stück je um ein Jahr herum) nicht genau der Jahressumme entspricht, erhöhst/senkst Du die Werte um die prozentuale Gesamt-Abweichung.

Damit kann es in Grenzfällen trotzdem noch zur Verletzung Deiner beabsichtigten Kontinuität kommen. Die solltest Du akzeptieren, oder Du holst Dir das Spline-Add-In.

Möglicherweise kommst Du auch mit Bèzier-Funktionen weiter, die zumindest grafisch von Excel unterstützt werden (vermutlich auch mit Funktionen). Ich bin leider kein Mathematiker und kann dies nur vermuten.

Ich habe es mal - ganz billig - linear versucht (A2:F7), mit den Quartalswerten zu B:B in C:F: (Formel unten)

     A            B            C             D             E            F 
1
2 2017    00000    00000    00000    00000    00000
3 2018    35000    07000    08167    09333    10500
4 2019    80000    16000    18667    21333    24000
5 2020    90000    21000    24000    24000    21000
6 2021    70000    21000    18667    16333    14000
7 2022    00000    00000    00000    00000    00000

C2:F7: =TEIL(WAHL(($B2>$B1)*2+($B3>$B2)+1;9876;8778;7887;6789);SPALTE(A2);1)/30*$B2 

Kritik: Wie man sieht, ist bspw. C5 leider kleiner als F4. Man könnte die Formel nun noch entsprechend erweitern, indem man das prüft und die Quartalswerte der beiden benachbarten Jahre etwas glättet.

(wie man C:F formelmäßig in eine Spalte überführt, setze ich als bekannt voraus)

[LCohen]

Hier in Deiner Datei linear und polynomisch (beide nicht perfekt!).

Es wird übrigens schon hübscher mit A25: 0,3

(die Daten 1-4 = Q1-4 2017 kannst Du ja noch =0 setzen)

Weitergehende Möglichkeiten (von Klaus Kühnlein, verstorben 2014):
Splines xlfTips99
Polynome xlfTips39
Polynomregression (Ermittlung der Polynom-Formel anhand der Daten, also direkte Verwendung!) xlfTips13