Live-Forum - Die aktuellen Beiträge
Datum
Titel
29.03.2024 13:14:12
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
488to492
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
488to492
488to492
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Inhaltsverzeichnis

Nullstellensuche einer Funktion in Matrix-Form

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.

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

Betreff
Datum
Anwender
Anzeige
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
Anzeige
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
Anzeige
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
Anzeige
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
Anzeige
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
Anzeige
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
Anzeige
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
Anzeige
AW: und ich wollt doch kein VBA :-))
Otmar
Danke dir Andreas,
mit dem link sollte ich es auf die Reihe kriegen.

302 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige