Trendsfunktion in VBA (lineare Regression)

Forumthread: Trendsfunktion in VBA (lineare Regression)

08.01.2008 18:07:02

paul

Hallo bin mit meinem Latain am Ende
Ich will den Trendwert einer linearen Regression in VBA abbilden und bin in diesem Forum auch schon auf die Funtion "Application.WorksheetFunction.LinEst" gestoßen:
Allerdings liefert die Funktion nicht den einen Trendwert sondern die Steigung der linearen Regression. Man kann als Argument auch gar nicht den "neuen x-Wert" angeben.
Ich würde sehr ungern die Parameter (Steigung, y-Achsen-Abschnitt) der linearen Funktion manuell ausrechnen um dann mit dem neuen x-Wert einen Trendwert zu berechnen.
Kennt jemand eine VBA-Funktion, die gleich den Trendwert berechnet (geht in Excel doch auch!)?
Mein Beispiel:
x-Werte: Cells(3,7) bis Cells(3,17) z.b: 1,2,3,4
y-Werte: Cells(1,7) bis Cells(1,17) z.b: 12, 26, 30, 62
neuer x-Werte für den Trend Cells(1,18) z.B. 5
Worksheets(Sheet_Daten).Cells(3, 18) = Application.WorksheetFunction.LinEst(Range(Sheets(Sheet_Daten).Cells(3, 7), Sheets(Sheet_Daten).Cells(3, 17)), _
Range(Sheets(Sheet_Daten).Cells(1, 7), Sheets(Sheet_Daten).Cells(1, 17)), True, True)
Syntax ist in etwa:
Worksheets(Zielzelle) = Application.WorksheetFunction.LinEst(x-Werte), (y-Werte), True, True)
Wie gesagt, da fehlt das Argument neuer x-Wert und es leifert nur die Steigung, nicht den neuen Trendwert?
In Excel gibt es die Funktion "=(Trend("x-Werte","y-Werte","neuer x-Wert"), die als Ergebnis den Trendwert liefert.
Als Makro-Auzeichnung sieht das so aus:
Worksheets(Sheet_Daten).Cells(3, 18) = "=TREND(RC[-11]:RC[-1],R[-2]C[-11]:R[-2]C[-1],R[-2]C)
Das kann ich aber nicht verwenden, da ich die Zeilen(x-Werte) später dynamische ansprechen muss.
Hiiiiiiiiiiiiiiiiiiiiiilfe - kann mir jemand weiterhelfen?

2
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: Trendsfunktion in VBA (lineare Regression)

08.01.2008 23:44:00

ingUR
AW: Nachtrag:

09.01.2008 14:34:59

ingUR

AW: Trendsfunktion in VBA (lineare Regression)

08.01.2008 23:44:00

ingUR

Hallo, @paul,
aus Deiner Aufgabenstellung kann ich nicht ersehen, welche Aufrufart Du für Dein Projekt vorsehen möchstest.
In meiner Basislösung übernehme ich Deine Anordung der Datenserie.

	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1		y	12	22	48	78	35	108	134	116	79	60	204	156,73
2	Testdaten
3		x	1	2	3	4	5	6	7	8	9	10	11	12
4
5	Trendlinie		19	31,3	43,8	56,4	68,9	81,45	94	107	119,1	131,6	144	156,72727
6														156,72727

Formeln der Tabelle

Zelle	Formel
R1	=CalcTrend(G1:Q1;R3)
G5	=12,5454545455*G3+6,1818181818
H5	=12,5454545455*H3+6,1818181818
I5	=12,5454545455*I3+6,1818181818
J5	=12,5454545455*J3+6,1818181818
K5	=12,5454545455*K3+6,1818181818
L5	=12,5454545455*L3+6,1818181818
M5	=12,5454545455*M3+6,1818181818
N5	=12,5454545455*N3+6,1818181818
O5	=12,5454545455*O3+6,1818181818
P5	=12,5454545455*P3+6,1818181818
Q5	=12,5454545455*Q3+6,1818181818
R5	=R1
R6	=12,5454545455*R3+6,1818181818

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Schwarze Trendlinie und Trendgleichung im Diagramm sind mit der Diagrammfunktionalität "Trendlinie..." mit der Datenbasis der Werte G1:Q1;G3:Q3 erstellt, während die Trendlinie, die den X-Punkt R13 einbindet, über die Berechnung der Gleichung erfolgt. Das Ergebnis ist für den Datenpunkt X=12 identisch mit dem VBA-Funktionsergebnis.
Die Zellenfomel CalcTrend erwartet als Argument den Bereich der bekannten Y-Werte, hier G1:Q1, und die Referenz auf die Zelle mit dem vorgegebenen X-Wert, also hier R3, für den der Trendwert mit der VBA-Funktion berechnet werden soll (Eintrag in R1 als Rückgabewert).
Diese Funktion (CalcTrend) kann jedoch auch benutzt werden, um die Werte innerhalb einer Prozedur, hier z.B. CalcNewTrendValue zu berechnen.
Sub CalcNewTrendValue() Dim newValue As Double newValue = CalcTrend(Range("G1:Q1"), Range("R3")) End Sub Public Function CalcTrend(ByRef DataR As Range, newX as Range) As Double Dim maxData As Long, ws As Worksheet Dim XR As Range, newX As Range Set ws = Worksheets("Tabelle1") maxData = DataR.Count Set XR = ws.Range(Replace(DataR.Address, "$1", "$3")) CalcTrend = CDbl(Application.WorksheetFunction.Trend(DataR, XR, newX)(1)) End Function

Vielleicht gelingt es Dir, diese Basislösung so zu verändern, dass Du daraus einen Baustein für Dein Projekt erstellen kannst.
Viel Erfolg
Uwe

AW: Nachtrag:

09.01.2008 14:34:59

ingUR

Hallo, @paul,
auf die Datentypumwandlung des Rückgabewertes der WorkSheetFunction.Trend kann verzichtet werden,
da der spezifizierte Rückgabewert bereist von Datenryp Double ist:
statt:
CalcTrend = CDbl(Application.WorksheetFunction.Trend(DataR, XR, newX)(1))
mit Typenumwandlungsfunktion CDbl(...), die nur überflüsssig aber nicht falsch ist, genügt:
CalcTrend = Application.WorksheetFunction.Trend(DataR, XR, newX)(1)
Gruß,
Uwe

Infobox / Tutorial

Trendsfunktion in VBA für lineare Regression

Schritt-für-Schritt-Anleitung
Häufige Fehler und Lösungen
Alternative Methoden
Praktische Beispiele
Tipps für Profis
FAQ: Häufige Fragen

Schritt-für-Schritt-Anleitung

Um den Trendwert einer linearen Regression in VBA zu berechnen, kannst du die Application.WorksheetFunction.Trend-Funktion verwenden. Hier ist eine Schritt-für-Schritt-Anleitung:

Daten vorbereiten: Lege deine x-Werte und y-Werte in den Zellen fest. Zum Beispiel:
- x-Werte: Cells(3, 7) bis Cells(3, 17)
- y-Werte: Cells(1, 7) bis Cells(1, 17)

VBA-Code einfügen: Öffne den VBA-Editor (Alt + F11) und füge folgendes Modul ein:

Sub CalcNewTrendValue()
   Dim newValue As Double
   newValue = CalcTrend(Range("G1:Q1"), Range("R3"))
   Worksheets("Sheet_Daten").Cells(1, 18) = newValue
End Sub

Public Function CalcTrend(ByRef DataR As Range, newX As Range) As Double
   Dim ws As Worksheet
   Set ws = Worksheets("Sheet_Daten")
   CalcTrend = Application.WorksheetFunction.Trend(DataR, ws.Range(Replace(DataR.Address, "$1", "$3")), newX)(1)
End Function

Aufruf der Funktion: Stelle sicher, dass du die Funktion CalcNewTrendValue im richtigen Kontext aufrufst, um den neuen Trendwert zu berechnen.

Häufige Fehler und Lösungen

Fehler: "Typenkonflikt"
- Lösung: Stelle sicher, dass die Bereiche für die y-Werte und den neuen x-Wert korrekt definiert sind. Prüfe, ob die angegebenen Zellen die richtigen Datentypen enthalten.
Fehler: "Funktion nicht gefunden"
- Lösung: Überprüfe, ob die CalcTrend-Funktion korrekt in deinem Modul definiert ist und dass du den richtigen Arbeitsblattnamen verwendest.

Alternative Methoden

Falls du eine nichtlineare Regression benötigst, bietet sich alternativ die Verwendung von Excel-Diagrammen an, um eine Trendlinie hinzuzufügen. Du kannst auch die LINEST-Funktion nutzen, um die Parameter der linearen Regression zu berechnen:

Dim result As Variant
result = Application.WorksheetFunction.LinEst(Range("y_Werte"), Range("x_Werte"))

Diese Methode gibt dir die Steigung und den y-Achsen-Abschnitt zurück, die du dann manuell verwenden kannst, um den Trendwert zu berechnen.

Praktische Beispiele

Hier ein Beispiel für die Berechnung des Trendwerts mit realen Daten:

Angenommen, du hast folgende Werte:

x-Werte (in Zellen G3 bis Q3): 1, 2, 3, 4, 5
y-Werte (in Zellen G1 bis Q1): 12, 26, 30, 62, 80

Du kannst den Trendwert für x = 6 wie folgt berechnen:

Worksheets("Sheet_Daten").Cells(1, 18) = CalcTrend(Range("G1:Q1"), Range("R3"))

Hierbei wird der Wert in R3 auf 6 gesetzt, um den Trendwert zu erhalten.

Tipps für Profis

Dynamische Bereiche: Nutze Namensbereiche oder dynamische Bereiche in deinen Formeln, um die Flexibilität zu erhöhen.
Debugging: Verwende Debug.Print, um Zwischenergebnisse zu prüfen, falls du auf Probleme stößt.
Fehlerbehandlung: Implementiere Fehlerbehandlungsroutinen in deinem VBA-Code, um unerwartete Probleme elegant zu lösen.

FAQ: Häufige Fragen

1. Wie kann ich eine nichtlineare Regression in Excel durchführen?
Für nichtlineare Regressionen kannst du die Trendlinienoption in Diagrammen nutzen oder eigene Funktionen entwickeln, um die Anpassung vorzunehmen.

2. Kann ich die LINEST-Funktion in VBA verwenden?
Ja, du kannst die LINEST-Funktion in VBA verwenden, um die Parameter der linearen Regression zu berechnen. Diese Werte kannst du dann für weitere Berechnungen verwenden.

3. Wie kann ich den Trendwert für mehrere x-Werte berechnen?
Du kannst eine Schleife in deinem VBA-Code implementieren, um den Trendwert für eine Liste von x-Werten zu berechnen und die Ergebnisse entsprechend zu speichern.

Beliebteste Forumthreads (12 Monate)

Trendsfunktion in VBA (lineare Regression)

Forumthread: Trendsfunktion in VBA (lineare Regression)

2 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Infobox / Tutorial

Trendsfunktion in VBA für lineare Regression

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

2
Beiträge zum Forumthread

Beiträge zu diesem Forumthread