Funktion "Bereich verschieben" für Saisonalisierung?

[RoenneB]

Hallo Community,

ich möchte gerne eine Absatz-Planung erstellen. Typischerweise hat ein Verlag in mehreren Monaten Neuerscheinungen und meistens in unterschiedlichen Ausgabeformen (z. B. Hardcover (HC) oder Taschenbuch (TB)). Beispielhaft habe ich eine Tabelle beigefügt, aus der der jeweilige Titel inkl. seiner Ausgabeform, seines Erscheinungsmonats und die geplante Absatzzahl über den Planungszeitraum hervorgeht.

Gleichzeitig habe ich mir einen (mehr oder weniger) typischen Verlauf für eine Neuerscheinung einmal für ein TB und einmal für ein HC berechnet (vgl. "Saisonalisierung" in der Datei).

Jetzt möchte ich Excel dazu bringen, dass in der Zelle des spezifischen Erscheinungsmonats des jeweiligen Titels (also für den ersten Titel im September, dem zweiten im Februar) die jeweilige Absatzmenge (Spalte D) gewichtet mit dem entsprechenden Prozentsatz der Saisonalisierung (in Abhängigkeit ob TB oder HC) steht. Aufgrund der hohen Anzahl von Titeln kann ich nicht jeden einzelnen Titel hinsichtlich seines Erscheinungsmonats prüfen und erst ab dem jeweiligen Monat die Formel hinterlegen, sondern es muss eine allgemein-gültige Formel über die gesamte Matrix sein, die entsprechende Werte in den Vormonaten vor Erscheinung bedingt ausschließt.

Mein Gedanke war über "Bereich verschieben", aber weitergekommen bin ich damit auch noch nicht...

Kann jemand helfen und hat kurzfristig eine Idee?

[BoskoBiati]

Hallo,

in E3 und nach rechts bzw. unten ziehen:

Code:

=WENN($C3>E$2;"";Deine Formel)

[Jockel]

Hallo,

in E3 und nach rechts bzw. unten ziehen:

Code:

=WENN($C3>E$2;"";Deine Formel)

Hallo Edgar, in der Datei gibt's keine Formel...

[BoskoBiati]

Hallo Jörg,

weiß ich, aber ich bin davon ausgegangen, dass er schon eine hat. Ich hab eine:

Code:

=WENN($C3>E$2;"";INDEX($E$10:$K$11;1+(CODE($B3)=84);DATEDIF($C3;E$2;"M")+1))

[Jockel]

Hallo Jörg,

weiß ich, aber ich bin davon ausgegangen, dass er schon eine hat. Ich hab eine...

Hi Edgar, der TE schrieb aber...:

Mein Gedanke war über "Bereich verschieben", aber weitergekommen bin ich damit auch noch nicht...

[RoenneB]

Hallo, ihr beiden,

danke für den Formelvorschlag und danke Jörg für die Richtigstellung.

Ja, ich hatte bislang nur eine Idee, war aber nicht schlau genug sie umzusetzen.

Jetzt habe ich zumindest eine, die ich ausprobieren kann. Danke Edgar. Ich melde mich danach mit feedback. Und vielleicht könntest du mir in der Zwischnenzeit einen Satz spendieren, was die Formel macht...?

Danke vorab!

[RoenneB]

So, erster Test sieht zumindest hinsichtlich des Ergebnisses gut aus. Kompliment und danke.

ABER...

Der erste Teil der Formel vergleicht ja die beiden Daten. Die beiden Daten (einmal in der Zeile und einmal als Spaltenüberschrift) sind in meiner Beispieldatei wegen der vereinfachten Darstellung identisch. Deswegen funktioniert ">". In meiner eigentlichen Planungsdatei sind die beiden Daten allerdings nicht identisch. Warum nicht? Nun, in meiner Planungsdatei habe ich die Spalten eineindeutig bezeichnet. Also z. B. AbsatzOkt16VS (Absatz = Absatz, Okt = Monat, 16 = Jahr und VS = Vorschau), denn es gibt z. B. auch Spalten UmsatzOkt17BUD (... BUD = Budget). Anders wäre m. E. eine spätere Auswertung über Pivot unhandlich, wenn nicht sogar unmöglich.

Wahrscheinlich müsste ich also im Rahmen der ">"-Prüfung das Okt16 aus dem Verbund AbsatzOkt16VS rauslösen und dann erst vergleichen. Aber geht das überhaupt oder habe ich mir selber mit der vorliegenden Bezeichnung die hier gewünschte Flexibilität genommen...?

Any idea?

[BoskoBiati]

Hallo,

ich liebe es, wenn ein Muster nicht der Realität entspricht!!!!! :28: :47:

Code:

=WENN($C3>--(1&TEIL(E$2;SUCHEN(1;E$2)-3;5));"";INDEX($E$10:$K$11;1+(CODE($B3)=84);DATEDIF($C3;--(1&TEIL(E$2;SUCHEN(1;E$2)-3;5));"M")+1))

 

ungetestet!

[Fennek]

Hallo,

ich bin eindeutig "VBA-Sympathisant", Bosko's Formel mag genial sein, aber innerhalb einer vernünftigen Zeit verstehe ich sie nicht.

Hier eine VBA-Variante:

Code:

Sub Test()

HC = Array(0.7, 0.09, 0.07, 0.04, 0.01, 0.01)
TB = Array(0.54, 0.07, 0.04, 0.03, 0.01, 0.01, 0.01)
lr = Cells(Rows.Count, "A").End(xlUp).Row

For i = 3 To lr
   Sp = Application.Match(Cells(i, "C"), Range("E2:ZZ2"))
   
   Select Case Cells(i, "B")
   Case "Hardcover"
       Season = HC
   Case "Taschenbuch"
       Season = TB
   End Select
   
   For j = 0 To UBound(Season)
       Cells(i, 4 + Sp + j) = Cells(i, "D") * Season(j)
   Next j
Next i
End Sub

mfg

[BoskoBiati]

Hallo,

dafür ist die Formel deutlich flexibler als der VBA-Code. Änderungen in den %-Zahlen bedürfen nur einer Änderung in der entsprechenden Zelle, während bei VBA der Code geändert werden muß.
Dem Code fehlt auch eine Variablendeklaration! Zudem funktioniert der Code unter den gegebenen Umständen eher nicht, da wohl in C ein Datum steht, in Zeile 2 aber nicht! Im Übrigen hat Excel eine Formelauswertung.