Nullstellensuche einer Funktion in Matrix-Form

Forumthread: Nullstellensuche einer Funktion in Matrix-Form

22.09.2004 09:29:07

Otmar

Hallo liebe Excelfreunde,
Vielleicht kann mir jemand bei der Lösung des folgenden Problems weiterhelfen:
In Spalte C1 bis C20 stehen 20 x-Werte einer Funktion y = f(x), z.B. -1; -0,9; -0,8; ...; 0,8; 0,9; 1.
In Spalte D1 bis D20 stehen die dazugehörenden y-Werte.
Im Diagramm dargestellt stellt man fest, daß es dabei irgendwo eine Nullstelle gibt. Tun wir einmal so, als wäre für x = 0,854625789 y = 0 und somit die gesuchte Nullstelle.
(Nebenbei bemerkt: Die y-Werte müssen nicht zwingend das Vorzeichen wechseln, damit man auf eine Nullstelle schließen kann, denn für y = x^2 ist, außer an der Nullstelle x = 0, y stets positiv.)
Frage:
Gibt es eine Möglichkeit - außerhalb von VBA - nur aufgrund von x/y - Wertepaaren die Nullstelle zu interpolieren ?
Wäre schön, wenn mir jemand helfen könnte.

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: Nullstellensuche einer Funktion in Matrix-Form

22.09.2004 09:36:55

Harald

Hallo Otmar,
Beispiel für eine Gerade:
C D
1 -2,5
2 -1,5
3 -0,5
4 0,5
5 1,5
6 2,5
7 3,5
8 4,5
9 5,5
10 6,5
Die Steigung der Geraden berechnest Du mit: =STEIGUNG(D1:D10;C1:C10)
Den Achsenabschnitt mit : =ACHSENABSCHNITT(D1:D10;C1:C10)
Die Gerade hat dann die Formel y=Steigung*x + Achsenabschnitt.
Daraus errechnet sich x zu x= (y-Achsenabschnitt)/Steigung und damit der Schnittpunkt für y=0.
Wenn Deine Kurve keine Gerade ist, dann musst Du die entsprechende Funktion anderweitig annähern (STEIGUNG und ACHSENABSCHNITT funktionieren nur für Gerade).
Gruß Harald

Noch einfacher

Harald

Hallo Otmar,
für Gerade geht's noch einfacher:
=Schätzer(0;Y-Werte;X-Werte) liefert den X-Wert, bei dem Y Null wird.
Dazu habe ich die Funktion Schätzer() einfach missbraucht, indem ich X- und Y-Werte vertauscht habe.
Gruß Harald

AW: Noch einfacher - danke aber

Otmar

Danke dir Harald für deine Antworten,
für eine Gerade ist das einfach, da reichen zwei bekannte Punkte bestehend aus x/y Wertepaaren um den Rest errechnen zu können.
Leider liegt aber häufiger der Fall von anderen Funktionen, seien es höhere Potenzen, oder trigonometrische Funktionen oder sonst was vor.
Es müßte irgendwie ne Excelfuntion geben, die sich aufgrund vorliegender x-y-Werte eine angenäherte Funktion zusammenbastelt und dann die gesuchte Nullstelle interpoliert.
Trotzdem Danke schön

AW: Noch einfacher - danke aber

Harald

Hallo Otmar,
gibt es nicht (so viel ich weiß). Die Schwierigkeit liegt doch darin, dass es unendlich viele mögliche Funktionen gibt, die Deine Wertepaare annähern können. Woher soll Excel wissen, weölche Funktion geeignet ist?
In einigen Fällen kannst Du Dir und Excel Dir aber dennoch helfen. Einige Funktionen lassen sich relativ gut linearisieren. Dann berechnest Du die Nullstelle für die linearisierte Funktionund rechnest dan zurück auf die nicht-lineare Funktion.
Beispiel:
y=ax^2+b substituieren x^2 = z, dann lautet die Gleichung y=az+b. Dafür suchst Du die Nullstelle 0=a*z0+b. Dann rechnest Du zurück: x=Wurzel(z), also x0 = Wurzel(z0).
Ebenso kann man auch die Funktionspaare ln() &lt&gt exp(), x &lt&gt 1/x, sin() &lt&gt asin() etc. im Rahmen der jeweiligen Gültigkeitsbereiche annähern.
Übrigens kannst Du auf diese Weise auch näherungsweise die Parameter der Funktionen ermitteln.
Gruß Harald

AW: Noch einfacher - danke aber

22.09.2004 11:00:28

ANdreas

Hallo Otmar,
mein Vorschlag: du kannst Dir einfach ein Näherungsverfahren (z.b. Newton, oder einfach Annäherung mit Mittelwerten) als VBA-Function schreiben.
Die Function bekommt mehrere Parameter übergeben.
1. Zelle in der die Funktion als Formel steht
2. Zelle in der der X-Wert steht, auf den sich die Formel bezieht
3. Intervalluntergrenze fürs Suchen
4. Intervallobergrenze fürs Suchen
5. Genauigkeitsfaktor (z.B. 0,0001) für Annäherung an Nullstelle.
Hoffe das hilft weiter,
Andreas

AW: Zwar nicht das was ich suche

Otmar

Hallo Andreas, hallo Harald,
danke für eure Vorschläge. VBA wollte ich vermeiden. Nach Möglichkeit auch das Zugreifen auf die Funktion. Lediglich die Wertepaare sollten herangezogen werden.
Vielleicht gehts ja nicht anders. Eine Möglichkeit wäre noch irgendwie über Zielwertsuche (wenn man mit dem Trick über die Namensvergabe arbeitet, läßt sich wohl auch hier VBA umgehen).
Mal sehen, vielleicht find ich ja noch was.
Danke euch und einen schönen Tag

AW: Zwar nicht das was ich suche

22.09.2004 12:15:31

ANdreas

Hallo Otmar,
VBA ist doch nichts böses ;-)
den Y-Wert der Wertepaare berechnest Du doch über eine Funktion, oder nicht?
Hier mal nen Vorschlag über eine VBA-Funktion das ganze anzunähern:
In Zelle B1 steht die Formel (Bezug ohne $), z.B.:
=A1*A1-5*A1+1
In Zelle B2 berechnen wir näherungsweise bis auf z.B. 0,000001 genau eine Nullstelle im Intervall von 0 bis 1 (Dieses Intervall kannst Du natürlich auch aus deinen Wertepaaren mit einer Formel an die Funktion übergeben)
=Y0Naeherung(B1;A1;0;1;0,000001)
In einem Standardmodul sollte dazu dieses Makro stehen - es "sucht" mit Hilfe der Intervallhalbierung eine Annäherung an die Nullstelle:

Private Function YWert(rngFunktion As Range, rngXWert As Range, dblXWert#) As Double
On Error GoTo ErrorHandler
YWert = Application.Evaluate( _
Replace(rngFunktion.Formula, rngXWert.Address(0, 0), Replace(dblXWert, ",", ".")))
On Error GoTo 0
Exit Function
ErrorHandler:
MsgBox Err.Description
Exit Function
End Function
Function Y0Naeherung(rngFunktion As Range, rngXWert As Range, _
dblIntvU#, dblIntvO#, dblGenauigkeit#) As Variant
Dim dblMittelwert#, i&
Dim dblYIntvU#, dblYIntvO#, dblYMittelwert#
i = 0 'Zähler für Durchläufe
Do
i = i + 1
dblMittelwert = 0.5 * (dblIntvU + dblIntvO)
dblYIntvU = YWert(rngFunktion, rngXWert, dblIntvU)
dblYIntvO = YWert(rngFunktion, rngXWert, dblIntvO)
dblYMittelwert = YWert(rngFunktion, rngXWert, dblMittelwert)
If dblYIntvU * dblYMittelwert <= 0 Then 'Vorzeichenwechsel
dblIntvO = dblMittelwert
Else
If dblYIntvO * dblYMittelwert < 0 Then 'Vorzeichenwechsel
dblIntvU = dblMittelwert
Else
If Abs(dblYIntvU) <= Abs(dblYIntvO) Then
dblIntvO = dblMittelwert
Else
dblIntvU = dblMittelwert
End If
End If
End If
Loop Until i > 1000 Or Abs(dblYMittelwert) <= dblGenauigkeit Or _
Abs(dblYIntvU) <= dblGenauigkeit Or Abs(dblYIntvO) <= dblGenauigkeit
If i > 1000 Then
Y0Naeherung = "Keine Annäherung an Nullstelle gefunden"
Else
If Abs(dblYMittelwert) <= dblGenauigkeit Then
Y0Naeherung = dblMittelwert
Else
If Abs(dblYIntvU) <= dblGenauigkeit Then
Y0Naeherung = dblYIntvU
Else
Y0Naeherung = dblYIntvO
End If
End If
End If
End Function

Hoffe das hilft weiter,
Andreas

AW: und ich wollt doch kein VBA :-))

Otmar

Hallo Andreas,
du willst mir unbedingt dein VBA verkaufen :-)).
Trotzdem danke für die zur Verfügungstellung des Codes (ich glaub in Fachkreisen sagt man zu dem von dir dargestellten Verfahren auch Regula Falsi). Damit kann ich die Nullstelle einwandfrei berechnen, da - wie du bereits richtig vermutet hast - die Funktion y = f(x) bekannt ist.
A B E R :
Mit der Funktion kann ich mir beliebig viele Wertepaare x-y erstellen. Daraus nimmt man zwei benachbarte y-Werte, berechnet die Differenz und teilt diese durch den x-Abstand = dx. Heraus kommt die erste Ableitung der Funktion, diesmal aber wirklich nur als Wertepaare (und nicht mehr als bekannte Funktion). Genauso kann man auch die zweite Ableitung der Funktion als Wertepaare ermitteln und mittels Diagramm darstellen.
Summa sumarum liefert mir Excel per Diagramm die Funktion, die 1. Ableitung, die 2. Ableitung, das Integral und die Bogenlänge in den angegebenen Grenzen für jede beliebige Funktion und das ohne Zahlenkolonnen (geht alles über Namen definieren) und ohne Makros.
Also fast die komplette Kurvendiskussion für alle denkbaren stetigen und differenzierbaren Funktionen.
Nullstellensuche funktioniert noch mit dem von dir beschriebenen Code.
Jetzt hätte ich aber noch gerne die Extremwerte (= Nullstellen der ersten Ableitung) und die Wendepunkte (= Nullstellen der zweiten Ableitung). Hierfür gibt es ja, wie gesagt, gratis keine Funktionen mehr.
Ums kurz zu wiederholen. Vorgestellt hatte ich mir: Gebe Excel ein paar Werte, Excel bastelt sich die Parameter einer Funktion (die der wirklichen recht nahe kommt) und liefert mir die Nullstellen für f(x), f'(x) und f''(x).
Ich fürchte zwar sowas gibt es nicht, wollte aber nichts unversucht lassen.
Danke dir Andreas, bis dann

AW: und ich wollt doch kein VBA :-))

22.09.2004 13:38:23

ANdreas

Hallo Otmar,
die Komplexität die Du brauchst, ist m.M. nicht einfach so erhältlich. Kenn mich da aber nicht aus.
Nicht ohne Grund beschäftigen sich damit eigens umfangreiche Matheprogramme - die gutes Geld kosten.
Natürlich kann man grds. fast alles was berechenbar ist auch mit Excel machen - der Aufwand jedoch ist enorm.
Schon allein aus Wertepaaren eine Näherungsfunktion zu bilden, ist nicht trivial, hängt natürlich von den Termen der Funktion ab (Sinus? Polynom? welcher Grade? exponentiell? logarithmisch? usw...)
siehe dazu auch:
http://home.t-online.de/home/arndt.bruenner/mathe/scripts/regr.htm
Dann die Ableitungsfunktionen zu bilden ist wieder ne andere Sache und zusätzlich die besagte Nullstellensuche, die dabei ja eher das kleinste Problem ist...
Gruß
Andreas

AW: und ich wollt doch kein VBA :-))

Otmar

Danke dir Andreas,
mit dem link sollte ich es auf die Reihe kriegen.

Beliebteste Forumthreads (12 Monate)

Nullstellensuche einer Funktion in Matrix-Form

Forumthread: Nullstellensuche einer Funktion in Matrix-Form

10
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Beliebteste Forumthreads (12 Monate)

Nullstellensuche einer Funktion in Matrix-Form

Forumthread: Nullstellensuche einer Funktion in Matrix-Form

10 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Beliebteste Forumthreads (12 Monate)

10
Beiträge zum Forumthread

Beiträge zu diesem Forumthread