Trendbetrachtung

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

Hallo, Frank,
mir scheint, ohne den Aufbau Deiner Tabelle und Deines Diagramms tiefgreifend zu verändern, das Einfügen eines zusätzlichen "Trendblockes" eine nachvollziehbare Lösung, in dem die (liniearen) Trendlinien der Produkte A bis D auf der Basis der bisher bekannten Jahreswerte (hier 2008) für die Monate des Jahres ermittelt werden (Bild 1).


Bild 1: Tabelle	Bild 2: Diagramm

Die Werte werden als zusätzliche Datenreihen in das Diagramm eingefügt, welches dann so aussieht und sich mit zusätzlichen Daten der ausstehenden Monate des Jahres (Eintragungen in die Spalten F bis I) anpassen wird, wenn die obere Bereichsgrenze in der TREND-Formel angepaßt wird oder eine dynamische Adressierung in dieser Formel vorgesehen wird.
Hier zuerst der Weg mit manueller Anpassung des Datenbereichs in der Trend-Formel:

	A	B	C	D	E	F	G	H	I
1					Produktionsstätte 4711
2	Trend					2008
3	lin.Trend(Prod.A)	lin.Trend(Prod.B)	lin.Trend(Prod.C)	lin.Trend(Prod.D)		Produkt A	Produkt B	Produkt C	Produkt D
4	19,8	21,5	20,1	25,1	Januar	20	22	20	25
5	20,1	22,5	20,7	23,7	Februar	20	22	21	25
6	20,4	23,5	21,3	22,3	März	20	23	21	20
7	20,7	24,5	21,9	20,9	April	21	25	22	22
8	21	25,5	22,5	19,5	Mai
9	21,3	26,5	23,1	18,1	Juni
10	21,6	27,5	23,7	16,7	Juli
11	21,9	28,5	24,3	15,3	August
12	22,2	29,5	24,9	13,9	September
13	22,5	30,5	25,5	12,5	Oktober
14	22,8	31,5	26,1	11,1	November
15	23,1	32,5	26,7	9,7	Dezember
16					Ø 2008	20	23	21	23
17					Ø 2007	12	13	12	13
18					Ø 2006	11	16	13	14

	A	B	C	D
4	19,5	20,5	19,5	26,5

Formeln der Tabelle

Zelle	Formel
A4	=TREND(F$4:F$7;;ZEILE()-4)
B4	=TREND(G$4:G$7;;ZEILE()-4)
C4	=TREND(H$4:H$7;;ZEILE()-4)
D4	=TREND(I$4:I$7;;ZEILE()-4)

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Bei Veränderung der Eintragungen im Tabellenbereich F4:I15, ist in der Trendzellenformel die entsprechende Zellenzahl einzutragen, wo derzeit auf die Zeile $7 bezug genommen wird.
Um diesen Vorgang zu automatisieren (dynamische Anpassung), ist es m.E. empfehlenswert, die Anzahl der gültigen Zeilen für das zu untersuchende Produkt in einen Hilfszelle zu schreiben. Ich benutze den Tabellenbereich A16:D16, in den folgende Formeln eingetragen werden:

	A	B	C	D
16	4	4	4	4

Formeln der Tabelle

Zelle	Formel
A16	=ANZAHL(F4:F15)
B16	=ANZAHL(G4:G15)
C16	=ANZAHL(H4:H15)
D16	=ANZAHL(I4:I15)

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Damit ändern sich die Zellenformeln im Bereich A4:D4 wie folgt:

	A	B	C	D
4	19,5	20,5	19,5	26,5

Formeln der Tabelle

Zelle	Formel
A4	=TREND(INDIREKT("F$4:F$"&3+A$16);;ZEILE()-4)
B4	=TREND(INDIREKT("G$4:G$"&3+B$16);;ZEILE()-4)
C4	=TREND(INDIREKT("H$4:H$"&3+C$16);;ZEILE()-4)
D4	=TREND(INDIREKT("I$4:I$"&3+D$16);;ZEILE()-4)

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
In beiden Fällen sind die Formeln A4:D4 durch Ziehen über ven Bereich von A4:D15 zu kopieren.
Die dynamische Variante habe ich in der EXCEL-Mappe eingearbeitet.
Über das Einfügen von weiteren Jahrengängen habe ich mir keine besonderen Gedanken gemacht. Manuell würde eine um einen Jahrgang versetzte Werte-Kopie des Datenbereichs F4: [..]15 nach J4:[...+4]15 möglich sein, um so den Bereich F4:I15 für den neuen Jahrgang nutzen zu können.
Gruß,
Uwe

Hallo, @Frank,
bei der Adresse zu EXCEL-Mappe wurde der Anker wohl von mir nicht ordnungsgemäß gesetzt, denn die es wurde meine Eingabe um einen automatisch generierten Zusatz erweitert (Anker mit Rechtsklick der Maustaste anwählen und Eigenschaften anzeigen lassen).
Hier nun ein erneuter Versuch: EXCEL-Mappe
Die "-4" in der Zellenformel erklärt sich aus der erten Zeile in der die Formel mit der Teilformel "Zeile()", die die akltuelle Zeilennummer liefert, und der Formel für dien linearen Trend.
Aus den gegebenen Datenwerten wird eine Gerade bestimmt, die so liegt, dass die Summe das Quadrat der Entfernungen zwischen dem bekannten Punkten und der gelegten Linie, so klein wie möglich wird. Die Trendgerade wird in dieser optimalen Lage durch eine Steigung m und einen Schnittpunkt b mit der Werteachse (Y-Achse) vollständig beschrieben.
Y_Trend = m*X_i + b
Die X_i-Werte werden durch die "Punkte" der Rubrikachse bestimmt, in Deinem Fall also die Monate, beginnend mit Januar X₁ = 0, der mit Null gezählt wird. Jeder weitere Monat wird demnach durch den um 1 gegenüber dem Vormonat erhöhten X_i+1 in der oben angeschriebenen Gleichung des linearen Trends dargestellt.
Sind also die beiden Konstanten m und b für die Trendgerade bekannt, kann für jeden Monat ein Trendwert (Y-Wert) berechnet werden (sie Punkte der Geraden im Diagramm). Da in der vierten Zeile der Januar steht, liefert Zeile()-4 den Wert Null, denn Zeile() liefert hier die 4.
Für den Januar erhalten wir also den Trendwert vom Wert b. Einen Monat Weiter, wieder steht dorrt in der fünften Zeile der Parameter Zeile()-4, für den nun allerdings der Parameterwert 1 ist, da Zeile() den Wert 5 liefert von dem die vier abgezogen wird, also der X-Wert, für den der Trendwert bestimmt werden soll. Dieser ist gegenüber dem Ausgangswert b um genau m*X_i verändert.
Die Zellenfunktion TREND führt genau diese Aufgabe durch, indem sie auf der Grundlage der Basisdaten INDIREKT("...."&...") intern die Konstanten der Trendgeraden bestimmt und dann für den Paramter X den zugehörigen Trendwert auf der Geraden berechnet und in die Zelle schreibt.
Ich hoffe, das ist so ein wenig nachvollziehbar und führt nicht zu zusätzlich Verwirrung, denn es ist ein wenig schwierig für mich, die Ausführungsschritte zu beschreiben, wenn nicht beurteit werden kann, wie weit die zusätzlichen Kenntnisse im Vorfeld bzw. im Nachgang erarbeitet wurden. Aber ggf. bitte einfach noch einmal nachgragen.
Gruß,
Uwe

Beliebteste Forumthreads (12 Monate)

5 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

5
Beiträge zum Forumthread

Beiträge zu diesem Forumthread