Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1352to1356
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

Syntax für VBA gesucht

Syntax für VBA gesucht
18.03.2014 14:29:02
Frank
Hallo an Alle,
besteht die Möglichkeit die Formel dynamisch zu gestalten, sprich den Bereich in der geschweiften Klammer?
=INDEX(LINEST(R2C2:R50C2,R2C1:R50C1^{1,2,3,4,5,6,7,8,9,10,11}),1,RC[1])
Vielen Dank und viele Grüße
Frank

24
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Syntax für VBA gesucht
18.03.2014 17:49:16
Dieter
Hallo Frank,
ich interpretiere deine Frage so, dass du die Formel per VBA schreiben willst und dabei sollen variabel viele Potenzen für die X-Werte auftreten können.
Das könntest du mit dem folgenden VBA-Programm machen:
Sub Matrixformel_schreiben()
Dim anzahl As Long
Dim i As Long
Dim ws As Worksheet
Dim zelle As Range
Dim potenzen As String
Set ws = ThisWorkbook.Worksheets(1)
Set zelle = ws.Range("C2")
anzahl = 12
For i = 1 To anzahl
potenzen = potenzen & i & ","
Next i
potenzen = "{" & Left$(potenzen, Len(potenzen) - 1) & "}"
zelle.FormulaArray = "=INDEX(LINEST($B$2:$B$50,$A$2:$A$50^" & potenzen & "),1,D2)"
End Sub
Viele Grüße
Dieter

Anzeige
AW: Syntax für VBA gesucht
18.03.2014 18:29:29
Frank
Hallo Dieter,
super vielen Dank für deine Mühe. Meine Anfrage hast du richtig interpretiert und nochmals vielen Dank an dieser Stelle. Allerdings bekomme ich es gerade nicht hin alle 12 Koeffizienten ausgeben zu lassen, sondern immer nur den ersten. Ich habe nun in deinem Code etwas geändert und zwar Range. Darüber hinaus müsste sich der Wert auch ändern, da das die Index-Funktion ist und ich jeden Koeffizienten der Ansatzfunktion benötige. Allerdings sind meine VBA-Kenntnisse auf diesem Gebiet doch recht gering. Ich habe dir eine Datei beigefügt. Ziel ist es für die vorliegenden Daten eine mögliche genaue Ersatzfunktion bzw. im ersten Schritt die Koeffizienten (grüner Bereich) zu erhalten. Könntest du mir bitte noch einen Tipp geben?
Wäre es auch denkbar, dass ich die Koeffizienten nicht an Excel übergebe, sondern in VBA als Vektor vorliegen habe?
https://www.herber.de/bbs/user/89730.zip
Vielen Dank für deine Hilfe.
Frank

Anzeige
AW: Syntax für VBA gesucht
18.03.2014 18:00:43
fcs
Hallo Frank,
aus der Excel-Hilfe zu RGP hab ich folgendes gefunden.
Neben der Verwendung von RKB zum Berechnen von Statistiken für andere Regressionstypen können Sie RGP zum Berechnen eines Bereichs von Regressionstypen verwenden, indem Sie Funktionen der x- und y-Variablen als x- und y-Reihen für RGP eingeben. Beispielsweise wird die folgende Formel:
=RGP(Y_Werte, X_Werte^SPALTE($A:$C))
verwendet, wenn Sie über eine Spalte von y-Werten und eine Spalte von x-Werte verfügen, um die kubische (Polynom der Ordnung 3) Annäherung in folgender Form zu berechnen:
y = m1*x + m2*x^2 + m3*x^3 + b
Sie können diese Formel anpassen, um andere Regressionstypen zu berechnen. In einigen Fällen ist dafür die Anpassung der Ausgabewerte und anderer Statistiken erforderlich.

Mit der Funktion INDIREKT kann man hier $C dynamisch machen, indem der gewünschte Grad des Polynoms in einer extra Zelle eingegeben wird.
Hier die Umsetzung als VBA-Formel, die dann in entsprechende Zellen eingefügt werden muss.
Der Grad des Polynoms steht in meinem Beispiel als Zahl in Zelle G5 (Z5S7).
Gruß
Franz
Sub Makro5()
'Formel in E5: _
=INDEX(RGP(Z2S2:Z50S2;Z2S1:Z50S1^SPALTE(INDIREKT("S1:S"&Z5S7;FALSCH)));1;ZS(1))
'Formel in E5: _
=INDEX(RGP($B$2:$B$50;$A$2:$A$50^SPALTE(INDIREKT("S1:S"&$G$5;FALSCH)));1;F5)
ActiveSheet.Range("E5").FormulaR1C1 = _
"=IFERROR(INDEX(LINEST(R2C2:R50C2,R2C1:R50C1^COLUMN(INDIRECT(""S1:S""&R5C7,FALSE)))" _
& ",1,RC[1]),"""")"
End Sub
'oder mit Vorgabe des Grads des Polynoms as Variable im Code
Sub Makro6()
Dim iPolyGrad As Integer
iPolyGrad = Application.InputBox( _
Prompt:="Bitte Grad des Polynoms eingeben (Werte von 1 bis 10)", _
Title:="RGP-Formel", Default:=1, Type:=1)
'Formel in E5: =INDEX(RGP(Z2S2:Z50S2;Z2S1:Z50S1^SPALTE(INDIREKT("S1:S3";FALSCH)));1;ZS(1))
'Formel in E5: =INDEX(RGP($B$2:$B$50;$A$2:$A$50^SPALTE(INDIREKT("S1:S3";FALSCH)));1;F5)
ActiveSheet.Range("E5").FormulaR1C1 = _
"=IFERROR(INDEX(LINEST(R2C2:R50C2,R2C1:R50C1^COLUMN(INDIRECT(""S1:S" & iPolyGrad _
& """,FALSE))),1,RC[1]),"""")"
End Sub

Anzeige
AW: Syntax für VBA gesucht
18.03.2014 18:40:55
Frank
Hallo Franz,
da war ich wohl etwas schneller. Ich habe gerade die Antwort an Dieter verfasst. Vielen Dank auch an dich ich denke das hilft mir weiter. Ich fahre jetzt erst einmal nach Hause und setze mich dann in Ruhe an deinem eben vorgestellten Code an. Vielen Dank nochmal. Ich bin eigentlich gar kein Freund von dieser Darstellung Z2S2 etc.. ich arbeite gern mit Cells. Am liebsten hätte ich, wie bereits bei Dieter beschrieben diese RGP Funktion mit Index herum in VBA vorliegen, so dass ich die Koeffizienten direkt in VBA bestimmen kann. Ich schau mal ob ich mit Hilfe eurer Vorschläge was zusammenbasteln kann.
Im Prinzip eine Funktion der ich meine x und y Werte übergebe und mein n.
Und innerhalb dieser Funktion soll dann mit Hilfe der RGP Funktion die Koeffizienten bestimmt werden und als Arrayformel gebe ich dann die mit den berechneten Koeffizienten f(x) Ergebnisse auf.
Ich glaub das stellt mich vor eine schwierige Aufgabe :o).
Vielen Dank und viele Grüße
Frank

Anzeige
ich verzweifele...
18.03.2014 20:55:26
Frank
Hallo Dieter und Franz,
ich habe nun ein wenig versucht das umzusetzen was Ihr mir geschrieben habt, leider mit sehr geringem Erfolg, anbei mein Code der leidet nicht das gewünschte Ergebnis bringt... :-(. Die Syntax stimmt nicht. Muss ich zur Ermittlung der Koeffizienten bei der Verwendung von LINEST nochmal eine Schleife für jeden x und y-Wert einbauen oder wo liegt das Problem? Ich hoffe sehr das Ihr mir helfen könnt - ich bin am verzweifeln.
Function RGP2(x, y, Polynomgrad)
Dim xVek, yVek, Koeffizienten()  'xVek = x-Werte, yVek = y-Werte, Koeffizienten = Ergbnisvektor
Dim i As Integer
Dim Anzahl As Long
Dim Potenzen As String
ReDim Koeffizienten(1 To Polynomgrad + 1, 1 To 1)
xVek = x
yVek = y
Anzahl = Polynomgrad
Polynomgrad = Polynomgrad + 1
For i = 1 To Anzahl
Potenzen = Potenzen & i & ","
Next i
Potenzen = "{" & Left$(Potenzen, Len(Potenzen) - 1) & "}"
i = 1
For i = 1 To Polynomgrad
Koeffizienten(i, 1) = Application.Index(WorksheetFunction.LinEst(xVek, yVek ^ Potenzen), 1,  _
Polynomgrad)
Polynomgrad = Polynomgrad - i
Next
RGP2 = Koeffizienten
End Function
Vielen Dank und viele Grüße
Frank

Anzeige
AW: ich verzweifele...
18.03.2014 22:03:28
Dieter
Hallo Franz,
ich habe mir das noch einmal angeschaut. Ich habe versucht rein intern zu rechnen, komme aber leider nicht ohne den Hilfsbereich C2:C50 aus.
Das Programm sieht dann so aus:
Sub Koeffizienten_berechnen()
Dim anzahl As Long
Dim ergebnis As Variant
Dim i As Long
Dim ws As Worksheet
Dim xWerte As Range
Dim yWerte As Range
Dim zeile As Long
Set ws = ThisWorkbook.Worksheets(1)
anzahl = 12
ReDim ergebnis(1 To anzahl)
ws.Columns("E:F").ClearContents
Set yWerte = ws.Range("B2:B50")
Set xWerte = ws.Range("C2:C50")
For i = 1 To anzahl
For zeile = 2 To 50
ws.Cells(zeile, "C") = ws.Cells(zeile, "A") ^ i
Next zeile
ergebnis = WorksheetFunction.LinEst(yWerte, xWerte)
ws.Cells(i, "E") = ergebnis(1)
ws.Cells(i, "F") = ergebnis(2)
Next i
End Sub

Vielleicht kommst du damit weiter.
Viele Grüße
Dieter

Anzeige
AW: ich verzweifele...
19.03.2014 12:04:55
Frank
Hallo Dieter und Franz,
vielen Dank an dieser Stelle für eure Antworten und vielen Dank für den geposteten Code von dir Dieter. Leider sind die Ergebnisse nicht die richtigen. Ich nehme an, dass die Aussage von Franz stimmt, denn wenn ich mir die Ergebnisse ausgeben lasse, dann bekomme ich eine Linie ausgegeben. Zum besseren Verständnis habe ich die Datei angehangen. Ich habe in Spalte I die Koeffizienten mittels RGP berechnen lassen.. MIt Hilfe dieser Koeffizienten berechne ich dann in Spalte D die neue "geglättete" Funktion. Selbst wenn es ohne eine Hilfspalte nicht funktionieren sollte, dann ist das auch in Ordnung. Mir wäre wichtig, dass ich in VBA die Koeffizienten in einem Vektor ausgegeben bekomme. Ich habe gestern Abend noch meinen Code gepostet (Function). DIe aber bisher noch keine Ergebnisse liefert. Ich wäre euch sehr dankbar, wenn Ihr mir weitere Tipps zur Umsetzung dieses Vorhabens geben könntet. Ich schaue mir das jetzt weiter an - irgendwie muss das doch umsetzbar sein (grübel).
https://www.herber.de/bbs/user/89742.zip
Besten Dank und Guten Appetit :o).
Frank

Anzeige
AW: ich verzweifele...
19.03.2014 15:34:02
Dieter
Hallo Frank,
also das sieht schon merkwürdig aus. Wenn ich in Spalte I die Werte einzeln neu berechne (in die Zelle klicken und Eingabe-Taste drücken), dann erhalte ich die gleichen Ergebnisse, wie du sie in Spalte G hast (anstelle von 9,84824 den Wert -7498,79218 usw.).
Wie hast du denn die Werte in Spalte I bekommen? Ich habe das mit Excel 2003 und Excel 2007 gemacht, mit gleichen Ergebnissen.
Per VBA kann ich die gleichen Koeffizienten berechnen - die wie gesagt, von deinen abweichen -, allerdings brauche ich dazu einen freien Zellbereich in dem Tabellenblatt als Hilfsbereich.
Das Programm sieht dann folgendermaßen aus:
Sub Koeffizienten_berechnen_3()
Dim anzahl As Long
Dim ergebnis() As Double
Dim i As Long
Dim ws As Worksheet
Dim rng As Range
Dim potenzen As String
Dim startZelle As Range
Set ws = ThisWorkbook.Worksheets(1)
anzahl = 12
ReDim ergebnis(1 To anzahl)
Set startZelle = ws.Range("N1")
' alte Formel löschen
If startZelle.HasArray Then
startZelle.CurrentArray.ClearContents
End If
Set rng = startZelle.Resize(1, anzahl)
For i = 1 To anzahl
potenzen = potenzen & i & ","
Next i
potenzen = "{" & Left$(potenzen, Len(potenzen) - 1) & "}"
rng.FormulaArray = "=LINEST($B$2:$B$515,$A$2:$A$515^" & potenzen & ")"
For i = 1 To anzahl
ergebnis(i) = startZelle.Offset(0, i - 1).Value
Next i
End Sub

Die Koeffizienten stehen in dem Feld "ergebnis".
Ich hoffe, du kommst damit weiter.
Viele Grüße
Dieter

Anzeige
AW: ich verzweifele...
19.03.2014 16:42:08
Frank
Hallo Dieter,
besten Dank für deine grandiose Unterstützung das hilft mir auf jeden Fall weiter. Ist es so wie Franz gesagt hat, dass man in VBA nur lineare Funktionen bestimmen kann, weil du nun wieder auf den Hilfsbereich ausgewichen bist und die, wie ich fand schöne Variante aus dem 2.ten Code mit xWerte und YWerte, wieder verworfen hast?
Ich verwende Excel 2010 und wenn ich mir Spalte G und I ansehe, dann stehen dort identische Ergebnisse. Ich muss nicht reinklicken. Wie bzw. wo berechnet sich denn bei dir der Wert -7498,79218? Ist bei dir in Spalte I keine Formel hinterlegt? Um die Koeffizienten in Spalte I zu bestimmen verwende ich die Formel
=INDEX(RGP($B$2:$B$515;$A$2:$A$515^{1.2.3.4.5.6.7.8.9.10.11});1;J2)

Um die einzelnen Ergebnisse der Funktion RGP zu entlocken verwende ich noch INDEX drumherum, allerdings sehe ich das in deinem Code nicht und dennoch funktioniert es? Seltsam, wie machst du das?
Per VBA kann ich die gleichen Koeffizienten berechnen - die wie gesagt, von deinen abweichen
Ich gehe davon aus, dass das daran liegt, weil du bei anzahl = 12 stehen hast. Oder was meintest du mit diesem Satz?
Einziges Problem was noch besteht ist, dass die letzte Koeffizient nicht ausgegeben wird. Bei einer Funktion mit m=11 ergeben sich 12 Ergebnisse. Ich schau mal ob ich das hin bekomme. Zur Zeit erzeuge ich noch einen Index außerhalb des gültigen Bereichs, wenn ich den 12.ten Wert abfragen möchte.
Ist das jetzt 100 prozentig sicher, dass die LINEST Funktion nur in Verbindung mit Excel verwendet werden kann um Koeffizienten höheren Grades zu bestimmen wie Franz gestern schrieb?
Tausend Dank nochmal an dieser Stelle an dich Dieter.
Ich hoffe das wir nun auch noch die letzten offenen Fragen klären können.
Vielen Dank und viele Grüße
Frank

Anzeige
AW: ich verzweifele...
19.03.2014 21:59:07
Dieter
Hallo Frank,
ich denke, wir kommen der Sache langsam näher.
Die unterschiedlichen Ergebnisse sind auf die unterschiedlichen Excel-Versionen zurückzuführen.
Ich habe unter 2003 und 2007 identische Ergebnisse und ebenso unter 2010 und 2013.
Die 2010-Ergebnisse weichen aber völlig von den 2007-Ergebnissen ab.
Da ich das Ganze nur formal von der VBA-Programmierung her sehe, kann ich zu diesen Unterschieden nichts sagen. Da ich unter 2010 die gleichen Ergebnisse bekomme, wie du, können wir die Sache auf sich beruhen lassen.
Die Lösung mit xWerte und yWerte habe ich tatsächlich wieder verworfen, da sie nicht die gewünschten Ergebnisse gebracht hat. Ich versteh zuwenig von der Funktion RGB, als dass ich sagen könnte, ob auf diesem Weg noch was zu holen wäre. Daher habe ich den pragmatischen Weg gewählt und die Funktion als Matrixfunktion in einen Zellbereich geschrieben.
Nun zu dem fehlenden Element: Ich wusste gar nicht, dass du bei "anzahl = 11" 12 Werte erwartest. Mein Programm muss nur etwas erweitert werden, dann liefert es den letzten Wert auch noch.
Das Programm sieht dann so aus:
Sub Koeffizienten_berechnen_3()
Dim anzahl As Long
Dim ergebnis() As Double
Dim i As Long
Dim ws As Worksheet
Dim rng As Range
Dim potenzen As String
Dim startZelle As Range
Set ws = ThisWorkbook.Worksheets(1)
anzahl = 11
ReDim ergebnis(1 To anzahl + 1)
Set startZelle = ws.Range("N1")
' alte Formel löschen
If startZelle.HasArray Then
startZelle.CurrentArray.ClearContents
End If
Set rng = startZelle.Resize(1, anzahl + 1)
For i = 1 To anzahl
potenzen = potenzen & i & ","
Next i
potenzen = "{" & Left$(potenzen, Len(potenzen) - 1) & "}"
rng.FormulaArray = "=LINEST($B$2:$B$515,$A$2:$A$515^" & potenzen & ")"
For i = 1 To anzahl + 1
ergebnis(i) = startZelle.Offset(0, i - 1).Value
Next i
End Sub
Du hast gefragt, wie die Sache funktioniert. Per Programm wird so gearbeitet, wie wenn du eine Matrixformel, von der du 12 Rückgabewerte erwartest, manuell in ein Tabellenblatt schreibst.
Dazu kannst du so vorgehen, wie du es mit der INDEX-Funktion gemacht hast.
Du kannst aber auch 12 nebeneinander liegende Zellen markieren, dann die die Formel
=RGP($B$2:$B$515;$A$2:$A$515^{1.2.3.4.5.6.7.8.9.10.11})
in die Bearbeitungszeile schreiben und anschließend die Eingabe mit der Tastenkombination Strg+Umschalt+Eingabe abschließen. Die Funktion wird dann automatisch in gescheifte Klammern eingeschlossen und sie zeigt ihre 12 Werte in den 12 Zellen an. Das Programm macht es so, wie im zweiten Verfahren beschrieben.
Ich hoffe, dass du damit weiter kommst.
Viele Grüße
Dieter

Vielen Dank Dieter...
20.03.2014 09:12:19
Frank
...für deine ausführliche Untersuchung in den verschiedenen Excelversionen und deiner Mühe einen Quellcode zu formulieren - er funktioniert tadellos und liefert die gewünschten Ergebnisse. Wahrscheinlich war die RGP-Funktion in Excel 2003 bzw. 2007 noch falsch... Ich habe hier nur 2010 installiert und kann dazu leider keine Aussage treffen. Wichtig ist, dass die Ergebnisse in 2010 stimmen und damit bin ich zufrieden :o).
Ich wusste gar nicht, dass du bei "anzahl = 11" 12 Werte erwartest.
Um das nochmal zu durchleuchten...
Es ist so bei einer quadratischen Funktion, sprich m=2, mit dem Ansatz f(x) = a*x^2+b*x+c
ergeben sich m+1 Koeffizienten und demnach bspw. bei Polynomgrad 11, 12.
Dieter, nochmals vielen Dank für deine großartige Unterstützung - du hast mir sehr geholfen.
Ich wünsche dir einen schönen Tag.
Viele Grüße,
Frank

AW: Es geht auch ohne Hilfsbereich
20.03.2014 12:35:10
Dieter
Hallo Frank,
vielen Dank für deine freundliche Rückmeldung mit den zusätzlichen Erläuterungen.
Der Hilfsbereich hat mir keine Ruhe gelassen und siehe, es geht auch ohne.
Das Programm sieht dann folgendermaßen aus (die Anzahl der Zeilen ist wie bei Franz auch variabel):
Sub Koeffizienten_berechnen_4()
Dim anzahl As Long
Dim ergebnis() As Variant
Dim i As Long
Dim letzteZeile As Long
Dim xWerte() As Variant
Dim yWerte() As Variant
Dim ws As Worksheet
Dim zeile As Long
anzahl = 11
Set ws = ThisWorkbook.Worksheets(1)
letzteZeile = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
ReDim xWerte(1 To letzteZeile - 1, 1 To anzahl)
For zeile = 2 To letzteZeile
For i = 1 To anzahl
xWerte(zeile - 1, i) = ws.Cells(zeile, "A") ^ i
Next i
Next zeile
yWerte = ws.Range(ws.Cells(2, "B"), _
ws.Cells(letzteZeile, "B")).Value
ergebnis = Application.WorksheetFunction.LinEst(yWerte, xWerte)
' Ergebnis in Spalte O ausgeben
ws.Columns("O").ClearContents
For zeile = 1 To anzahl + 1
ws.Cells(zeile, "O") = ergebnis(zeile)
Next zeile
End Sub
Viele Grüße
Dieter

Also nun bin ich baff...
20.03.2014 14:34:34
Frank
...
Hallo Dieter,
vielen, vielen Dank ich bin sprachlos. Schön wie man sich in ein Problem festbeißen kann. Also ist es nun doch entgegen allen Erwartungen so, dass die Funktion innerhalb von VBA verwendet werden kann, ganz ohne einen Hilfsbereich in Excel? Echt super... Ich freu mich, dass sich andere einfach aus purem Wissensdurst mit der "Probleme" anderer befassen und hier dann solch elegente Lösungen aufzeigen.
So einen Ansatz hattest du doch auch schon in dem Code Koeffizienten_berechnen_2 angesetzt. Ich muss mir beide Codes erst einmal ausdrucken und nebeneinander legen, um zu sehen was du verändert hast.
TOLL, TOLL, TOLL - auf diese Lösung wäre ich nicht von allein gekommen :o).
Danke Dieter und viele Grüße,
Frank

Nachtrag zur Hilfe von Dir
20.03.2014 14:51:50
Dir
Hallo Dieter,
das Problem an dieser ganzen Thematik ist auch bzw. ist es das eigentliche Hauptproblem zu wissen, wie die Funktion RGP die Daten übergeben bekommen möchte, um sie anschließend intern verarbeiten zu können. Ich meine es ist ja elegant zu wenn man Excel x und y-Werte übergibt und die Funktion, in diesem Falle, RGP rechnet intern wie ein Blackbox um dann Ergebnisse auszugeben. Als Programmierer, der nun das ganze glanzvoll über VBA lösen möchte, steht nun genau vor diesem Problem. Wie verarbeitet die Funktion die eigenartig geschweifeten Klammerausdrücke und bzw. den darin enthaltenen Daten. Werden die potenzierten Werte zusammengerechnet...wie ich deinem Quellcode entnehme, wahrscheinlich nicht. Und möchte die Funktion die Daten als Spalte oder Zeile übergeben bekommen.
Das ist genauso, wie wenn ich eine UDF schreibe und niemand so wirklich weiß, was mit dieser Funktion über ein paar Tricks für Möglichkeiten in ihr stecken, da nur ich weiß was und vorallem wie die Daten intern verarbeitet werden.
Wie siehst du das? Ich würde mich über deine Meinung freuen.
Viele Grüße
Frank

AW: Es geht auch ohne Hilfsbereich
20.03.2014 21:26:04
Frank
Guten Abend Dieter,
ich habe nun versucht Dein Wissen in eine Function umzusetzen... Da gibt es einiges was verbessert werden könnte in meinem Code. Zum Beispiel das Finden des letzt beschriebenen Wertes im Array... Ich löse das gerade über eine Schleife und suche so lange bis die nächste Zelle leer ist. Nun ja - Anfänger. Darüber hinaus versuche ich mich gerade an dem Befehl Redim Preserve... Da bekomme ich aber immer eine Sytaxfehlermeldung bzw. bricht der Code dort ab (ReDim Preserve yVek(1 To m, 1 To 1)). In der Not weise ich die Vektoren also nochmals deinen deklarierten Vektoren xWerte und yWerte zu.
Das ganze schließe ich mit Strg+Alt+Enter ab. Als Resultat bekomme ich im "Ergebnisarray" in Excel immer nur das erste Polynom ausgegeben. Könntest du mir eventuell sagen was ich falsch mache?
Function RGP3(x, y, Polynomgrad)
Dim anzahl As Long
Dim Koeffizienten() As Variant
Dim i As Long, m As Long
Dim letzteZeile As Long
Dim xWerte() As Variant
Dim yWerte() As Variant
Dim ws As Worksheet
Dim zeile As Long
Dim xVek, yVek
xVek = x
yVek = y
i = 1
Do Until IsEmpty(xVek(i, 1)) = True
If i = AnzX Then
Exit Do
Else
i = i + 1
End If
Loop
If IsEmpty(xVek(i, 1)) = True Then
m = i - 1
Else
m = i
End If
ReDim xWerte(1 To m, 1 To Polynomgrad)
ReDim yWerte(1 To m, 1 To 1)
'ReDim Preserve yVek(1 To m, 1 To 1)
i = 1
For zeile = 1 To m
For i = 1 To Polynomgrad
xWerte(zeile, i) = xVek(zeile, 1) ^ i
Next i
yWerte(zeile, 1) = yVek(zeile, 1)
Next zeile
Koeffizienten = Application.WorksheetFunction.LinEst(yWerte, xWerte)
RGP3 = Koeffizienten
End Function
Viele Grüße und einen schönen Abend wünscht
Frank

...ich glaube es liegt daran
20.03.2014 21:32:29
Frank
...dass das Ergebnis einen Zeilenvektor liefert und ich in Excel einen Spaltenvektor mit Strg+Alt+Enter abschließe...
also flux im Code geändert auf
Koeffizienten = WorksheetFunction.Transpose(Koeffizieten)
RGP3 = Koeffizienten
...mag aber auch nicht funktionieren... Es ist sicher nur eine Kleinigkeit...

...wäre es auch denkbar, dass...
20.03.2014 21:50:10
Frank
man in Abhängingkeit der Usereingaben - Strg+Alt+Enter überprüft ob es sich um einen Zeilen-/Spaltenvektor in Excel handelt und dementsprechend die Koeffizienten übergibt?
Viele Grüße und dann gleich gute Nacht sagt
Frank

AW: ...wäre es auch denkbar, dass...
21.03.2014 10:53:53
Dieter
Hallo Frank,
ich denke, dass sich die Sache so langsam ganz gut klärt.
Ich habe am Anfang vermutet, dass der Aufruf
{=RGP($B$2:$B$515;$A$2:$A$515^{1.2.3.4.5.6.7.8.9.10.11})}
sich so verhält, dass die Funktion RGP 11mal mit wechselnden Spaltenvektoren aufgerufen wird. Deshalb habe ich auch nur 11 Ergebnisse erwartet und nicht 12.
Meine Vermutung kam aus der Analogie zu dem Aufruf
{=SIN(A2^{1.2.3})}
(3 nebeneinanderliegende Zellen markieren, dann
=SIN(A2^{1.2.3})
in die Bearbeitungszeile eingeben und Eingabe mit Strg+Umschalt+Eingabe abschließen)
Bei diesem Aufruf werden die 3 Sinuswerte SIN(A2), SIN(A2^2) und SIN(A2^3) berechnet.)
Der oben genannte Aufruf wird aber ganz anders, nämlich folgendermaßen behandelt:
Schreib in die Zellen C2 bis M2 die Formeln
=A2^1 bis =A2^11
und ersetze dann den o.g. Aufruf (12 nebeneinanderliegende Zellen) durch
{=RGP($B$2:$B$515;$C$2:$M$515)}
Du erhälst das gleiche Ergebnis.
Nun zu deiner Function-Prozedur. Zusammen mit einem Testprogramm könnte das so aussehen:
Function RGP3(x As Variant, y As Variant, Polynomgrad As Long) As Variant
Dim dimXY As Long
Dim s As Long
Dim z As Long
dimXY = UBound(x)
ReDim Preserve x(1 To dimXY, 1 To Polynomgrad)
For z = 1 To dimXY
For s = 2 To Polynomgrad
x(z, s) = x(z, 1) ^ s
Next s
Next z
RGP3 = Application.WorksheetFunction.LinEst(y, x)
End Function
Sub Test_RGP3()
Dim grad As Long
Dim i As Long
Dim koeffizienten() As Variant
Dim letzteZeile As Long
Dim ws As Worksheet
Dim x() As Variant
Dim y() As Variant
grad = 11
Set ws = ThisWorkbook.Worksheets(1)
ws.Columns("D").ClearContents
letzteZeile = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
If letzteZeile 

An die Funktion werden zwei Arrays übergeben, die bei der vorliegenden Datenmenge die Dimensionen
x(1 To 514, 1 To 1) und y(1 To 514, 1 To 1) haben.
Die Dimensionierung mit den beiden Dimensionen ergibt sich automatisch aus der Zuordnung zu den Werten der Zellbereiche B2:B515 bzw. A2:A515.
Die Funktion bläst jetzt nur noch die Spalten von x auf.
Ich hoffe, du kommst damit weiter.
Viele Grüße
Dieter

AW: RGP-Berechnung Formellösung
19.03.2014 22:42:56
fcs
Hallo Frank,
hier deine Datei mit einer reinen Formel-Lösung für die Ausgleichspolynome bis 10-ter Ordnung.
https://www.herber.de/bbs/user/89755.xlsx
Die Berechng der Koeffizienten des Polynoms erfolgt dabei auf einem separaten Blatt.
Einzige Eingabe ist die Ordnung des Polynoms in I2.
Die Zahl der Zeilen mit Daten in in Spalte A + B wird automatisch per Formel berechnet.
Gruß
Franz

AW: RGP-Berechnung Formellösung
20.03.2014 09:18:20
Frank
Guten Morgen Franz,
vielen Dank auch an dieser Stelle für deine "reine" Formellösung. Ich habe mir die Datei eben angesehen. In welcher Version hast du die Datei erstellt? Denn die Ergebnisse der Koeffizienten bzw. Ausgleichspolynome sind anders als die berechneten Polynome in Spalte G. Ergeben sich bei dir die richtigen Ergebnisse?
https://www.herber.de/bbs/user/89758.jpg
Vielen Dank und viele Grüße
Frank

AW: RGP-Berechnung Formellösung
20.03.2014 11:56:42
fcs
Hallo Frank,
zur Berechnungsmethode der Koeefizienten in Spalte G kann ich nichts sagen.
In den Spalten "Kraft ideal" und "RGB" (berechnet mit meinen Ergebnissen) stimmen die Werte bei gleicher Ordnung jedenfalls bis zur 3. Nachkommastelle überein und der Verlauf der Kurven im Diagramm ist identisch.
Gruß
Franz

AW: RGP-Berechnung Formellösung
20.03.2014 13:57:50
Frank
Hallo Franz,
also das ist nun wirklich eigenartig. Hast du dir das Bild angesehen? Also bei mir ergeben sich ganz andere Werte als die Ergebnisse in Spalte C und D. Bei dir nicht?
Viele Grüße
Frank

AW: ich verzweifele...
18.03.2014 22:22:15
Dieter
Hallo Frank,
esgeht auch ohne den Hilfsbereich C2:C50.
Das sieht dann so aus (mir fehlte vorher bei xWerte die 2. Dimension):
Sub Koeffizienten_berechnen_2()
Dim anzahl As Long
Dim ergebnis As Variant
Dim i As Long
Dim k As Long
Dim rng As Range
Dim ws As Worksheet
Dim xWerte As Variant
Dim yWerte As Range
Dim zeile As Long
Set ws = ThisWorkbook.Worksheets(1)
anzahl = 12
ReDim ergebnis(1 To anzahl)
ws.Columns("E:F").ClearContents
Set yWerte = ws.Range("B2:B50")
xWerte = ws.Range("A2:A50").Value
For i = 1 To anzahl
For k = 1 To 49
xWerte(k, 1) = ws.Cells(k + 1, "A") ^ i
Next k
ergebnis = WorksheetFunction.LinEst(yWerte, xWerte)
ws.Cells(i, "E") = ergebnis(1)
ws.Cells(i, "F") = ergebnis(2)
Next i
End Sub
Jetzt ist es für heute genug.
Viele Grüße
Dieter

AW: ich verzweifele...
19.03.2014 01:36:50
fcs
Hallo Frank,
ich hab noch ein wenig mit der Worksheetfunction LinEst unter VBA experimentier.
Sieht ganz so aus, als ob die Funktion unter VBA nur die Koeffizienten einer linearen Ausgleichsfunktion der Form y = m * x + b
berechnen kann.
Mit dem zusätzlichen Parameter "^{1.2.3}" zur Berechnung der Koeffizienten von Polynomen höheren Grades kommt die Funktion unter VBA nicht klar. Diese spezielle Anwendung der Funktion ist nur als Tabellenformel möglich.
Gruß
Franz

Links zu Excel-Dialogen

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige