Funktion finden

05.08.2016, 14:26

totti

Auf diesen Beitrag antworten »

Funktion finden

Hallo zusammen,

ich habe eine Reihe an Messwerten, welche ich gerne mit einer Funktion beschrieben möchte.

Ich habe diese in Excel eingegeben und einmal Plotten lassen. Über die Funktion der Trendlinie habe ich eine Funktion gefunden(eine Potenzfunktion $\begin{eqnarray*} a*x^{-b} \end{eqnarray*}$ )
Dabei ist $\begin{eqnarray*} a\in [3|600] \end{eqnarray*}$ und $\begin{eqnarray*} b\in (-\infty |0) \end{eqnarray*}$

Naja jetzt benötige ich allerdings noch eine etwas andere annäherung, und zwar die die Funktion möglichst genau beschreibt. Es handelt sich hier um Akkus mit einer Zellenspannung von 1,75V

Nachher möchte ich für 6 andere Zellspannungen auch eine Optimale Funktion finden, welche mir dann sagen kann, zu welcher Zeit habe ungefähr den Wert.
Wie gesagt möglichst genau.

Ich kenne eine Methode bei einer Geraden, und zwar mit den kleinsten fehlerquadraten.
Wo ich den fehler bilde nachher die Ableitung Partiell bestimme nach den beiden parameter(Steigung m und y-Achsenabschnitt b)
und dann das Minimum suche indem ich die Ableitung Null setzte....

Anbei die Messwerte:
x y
3 3021
5 2654
10 1982
15 1566
20 1282
30 950
45 689
60 541
120 300
180 211
300 140
480 89,3
600 73,6

Ich hoffe ihr versteht mein Problem und habt eine Idee...

Danke im Voraus

05.08.2016, 14:40

Steffen Bühler

Auf diesen Beitrag antworten »

RE: Funktion finden
Also Du hast mit Hilfe von Excel eine ausgleichende Potenzfunktion für einen Akkutyp gefunden. Nun willst Du dasselbe für fünf weitere Akkus tun. Was hindert Dich denn daran, dies auf die gleiche Art und Weise zu bestimmen?

Natürlich kannst Du das auch berechnen, dabei helfen wir Dir gerne. Aber wenn Du's schon einmal in Excel gemacht hast, kannst Du's doch auch sechsmal machen.

Viele Grüße
Steffen

05.08.2016, 14:52

HAL 9000

Auf diesen Beitrag antworten »

Das Modell $\begin{align*} y = a\cdot x^{-b} \end{align*}$ wird durch Logarithmierung in ein lineares Modell $\begin{align*} \ln(y) = \ln(a) - b\ln(x) \end{align*}$ überführt:

D.h., mit $\begin{align*} (\tilde{x},\tilde{y}):=(\ln(x),\ln(y)) \end{align*}$ sowie den Parametertransformationen $\begin{align*} \tilde{a}=\ln(a) \end{align*}$ und $\begin{align*} \tilde{b}=-b \end{align*}$ liegt $\begin{align*} \tilde{y} = \tilde{a} + \tilde{b}\cdot \tilde{x} \end{align*}$ vor, d.h., normale lineare Regression angewandt auf die Datenpaare $\begin{align*} (\tilde{x}_k,\tilde{y}_k)=(\ln(x_k),\ln(y_k)) \end{align*}$ liefert dann die Koeffizienten $\begin{align*} \tilde{a},\tilde{b} \end{align*}$ , die man dann über $\begin{align*} a=e^{\tilde{a}} \end{align*}$ und $\begin{align*} b=-\tilde{b} \end{align*}$ zu den gewünschten Koeffizienten rückrechnen kann.

P.S. (für Insider): Richtig ist, dass die MKQ-Probleme $\begin{align*} \sum_k \left(y_k - a\cdot x_k^{-b}\right)^2 \to \min! \end{align*}$ sowie $\begin{align*} \sum_k \left(\ln(y_k) - \ln(a)+b\ln(x_k)\right)^2 \to \min! \end{align*}$ nicht äquivalent sind. Dennoch ist die zweite Variante in vielen Fällen akzeptabel - mitunter ist der Effekt, dass die Abweichungen bei kleinen Werten dort stärker bestraft werden als bei großen (im Vergleich zur ersten Variante) ja sogar erwünscht. Augenzwinkern

Augenzwinkern

05.08.2016, 14:56

totti

Auf diesen Beitrag antworten »

Danke für die schnelle Antwort.

Die Funktion die Ecxel mir vorgeschlagen hat, ist mir leider nicht genau genug.

Daher wollte ich da noch weiter optimeren. Und ob es überhaupt eine Möglichkeit gibt diss zu tun ?

05.08.2016, 15:11

Steffen Bühler

Auf diesen Beitrag antworten »

Zitat:

Original von totti
Die Funktion die Ecxel mir vorgeschlagen hat, ist mir leider nicht genau genug.

Excel schlägt bei mir mit diesen Daten $\begin{align*} y=9965,7 \cdot x^{-0,74} \end{align*}$ vor. Mit einem Regressionskoeffizienten von über 98 Prozent. Die Kurve sieht auch recht passend aus. Willst Du wirklich noch exakter sein?

Zitat:

Original von totti
ob es überhaupt eine Möglichkeit gibt diss zu tun ?

Da würde ich genau wie HAL über die log/log-Darstellung gehen und die lineare Regression, die Du ja kennst, nehmen. Aber, wie gesagt, viel genauer wird das nicht werden.

05.08.2016, 15:16

totti

Auf diesen Beitrag antworten »

Ja genau,

die Daten schlägt mir Excel vor.
Ich mache das mit einem Kollegen zusammen, er ist der festen Überzeugung, dass es zu jeder Messreihe auch eine Funktion genau geben MUSS!!!

Das was Stefan geschrieben hat muss ich mir erstmal genau anschauen ob ich es doch alles richtig verstehe.

Ist das im Endeffekt auf die schnelle betrachtet, eine sogenannte Substitution ?

Ich Stelle mir eine Gerade vor, und Substituiere dabei die Variablen? Also jetzt auf den ersten Blick. ICh denke aber, dass ich weiß was er sagen möchte...

EDIT:
Vorallem im Bereich des "Bauches" nenne ich es mal, ist mir die Abweichung doch zu stark. Ich weiß das zum Beispiel die Methode für geraden, also der QMW (Quadratsiche Mittelwert) den "Ausreißern" mehr Beachtung schenkt im Gegensatz zum Arithmetischen Mittel ?!

Anzeige

05.08.2016, 15:25

Steffen Bühler

Auf diesen Beitrag antworten »

Zitat:

Original von totti
er ist der festen Überzeugung, dass es zu jeder Messreihe auch eine Funktion genau geben MUSS!

Kann ja gut sein. Dann lasst doch Excel mal auf die fünf anderen Messreihen los und schaut, was da für Funktionen rauskommen.

Zitat:

Original von totti
Ist das im Endeffekt auf die schnelle betrachtet, eine sogenannte Substitution ?

Ich würde es eher Transformation nennen. Das kannst Du auch schnell in Excel sehen: wenn Du beide Achsen logarithmisch skalierst, werden die Daten fast exakt auf einer gedachten Geraden liegen. Und diese Ausgleichsgerade kann eben einfach berechnet werden, wie sonst auch.

Du begibst Dich mit dieser Transformation also in die logarithmische Welt, wo vieles einfacher ist (z.B. Addition statt Multiplikation), rechnest ein paar einfache Sachen aus, und kehrst dann wieder in die lineare Welt zurück. Das hat man früher übrigens mit den Logarithmentafeln und Rechenschiebern genauso gemacht.

PS:

Zitat:

Original von totti
Vorallem im Bereich des "Bauches" nenne ich es mal, ist mir die Abweichung doch zu stark.

Ja, ich sehe es. Der Grund scheint das erste Wertepaar zu sein, das wohl nicht so richtig zum Rest "passt". Wenn Du das für die Regression weglässt (sozusagen als "Ausreißer"), sind die Abweichungen deutlich kleiner.

05.08.2016, 17:02

totti

Auf diesen Beitrag antworten »

Ich habe mich glaube ich falsch ausgedrückt was die Beschreibung Messreihe angeht.

Diese Punkte, die ich vorhin geschrieben habe, sollen alle exakt über eine Funktion beschrieben werden können.

Ich rede also erst einmal nur von einer Messreihe einer Zelle.

Er sagt, es gibt bestimmt eine Funktion, die genau jeden Punkt dieser Messreihe abdeckt.

Ja stimmt, ich habe so einen Rechenschieber auch mal ausprobiert. Hat mir mein Vater mal gezeigt. Unglaublich wie komfortabel die Welt mit einem einfachen TR geworden ist.

05.08.2016, 17:11

HAL 9000

Auf diesen Beitrag antworten »

Zitat:

Original von Steffen Bühler
wenn Du beide Achsen logarithmisch skalierst, werden die Daten fast exakt auf einer gedachten Geraden liegen.

Ich hab mir die obigen Daten des Ausgangsposts mal diesbezüglich angeschaut: Man sieht tatsächlich eine leichte, aber deutliche konkave Biegung der Kurve. Ich denke mal, wenn man zu obigem

$\begin{align*} \ln(y) = \tilde{a} + \tilde{b}\cdot \ln(x) \end{align*}$

noch ein quadratisches Glied dazunimmt, d.h.,

$\begin{align*} \ln(y) = \tilde{a} + \tilde{b}\cdot \ln(x) + \tilde{c}\cdot (\ln(x))^2 \end{align*}$ ,

(immer noch ein lineares Modell), dann sieht das schon sehr gut aus.

05.08.2016, 17:24

Steffen Bühler

Auf diesen Beitrag antworten »

Zitat:

Original von totti
Er sagt, es gibt bestimmt eine Funktion, die genau jeden Punkt dieser Messreihe abdeckt.

Ja, die gibt es auch. So wie zwei Punkte exakt von einer linearen Funktion abgedeckt werden können, drei Punkte von einer Parabel zweiter Ordnung, so können Eure 13 Punkte mit einem Polynom 12. Ordnung, also

$\begin{align*} y=a_{12}x^{12} + a_{11}x^{11} + a_{10}x^{10} + a_{ 9}x^{ 9} + a_{ 8}x^{ 8} + a_{ 7}x^{ 7} + a_{ 6}x^{ 6} + a_{ 5}x^{ 5} + a_{ 4}x^{ 4} + a_{ 3}x^{ 3} + a_2x^2 + a_1x^1 + a_0x^0 \end{align*}$

abgedeckt werden. Aber glaub mir: das wollt Ihr nicht. Augenzwinkern

Augenzwinkern

Denn das ist zwar mathematisch wunderbar, hat aber mit der Realität nichts zu tun. Die zugrundeliegende physikalische Funktion ist ganz bestimmt nicht so ein Trumm von Funktion, da liegen einfachere Gesetzmäßigkeiten zugrunde. Dass Eure Messwerte nicht genau da liegen, wo Ihr sie erwartet, ist das Leiden aller Physiker und Ingenieure: Messfehler. Euer erstes Wertepaar (3|3021) kann genausogut auch (2,9|3023,45) sein.

Genau deswegen macht man ja eine statistische Auswertung, um der Wahrheit, die die Natur vor uns verbirgt, so nah wie möglich zu kommen.

05.08.2016, 17:35

totti

Auf diesen Beitrag antworten »

Das hört sich doch ganz gut an.

Ja das mit den Messungenauigkeiten habe ich auch schon gesagt, dass es wenig Sinn macht eine Funktion 50. Grades zu beschreiben.

alles klar, also wie gehe ich jetzt am besten vor?

In Excel die Werte erst einmal auf logarithmische Achsen skalieren ist eine Sache. Das ganze allerdings direkt mathematisch zu gestalten eine anderen.

05.08.2016, 18:01

HAL 9000

Auf diesen Beitrag antworten »

Multiple lineare Regression kann Excel in der Standardform nicht (oder doch?), da benötigt man wohl ein passenden Add-In (die meisten davon sind anscheinend kostenpflichtig). Ich mach sowas dann meist mit R, im vorliegenden Fall etwa für das lineare und quadratische Modell (s.o.) etwa mit folgendem Skript:

code:

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:

x <- c(3,5,10,15,20,30,45,60,120,180,300,480,600)
y <- c(3021,2654,1982,1566,1282,950,689,541,300,211,140,89.3,73.6)
xl <- log(x)
yl <- log(y)
xq <- xl*xl

mod_1 <- lm(yl~xl)
mod_2 <- lm(yl~xl+xq)
summary(mod_1)
summary(mod_2)

Ergebnisausdruck (nur der letzten Zeilen) ist dann

code:

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:

> summary(mod_1)

Call:
lm(formula = yl ~ xl)

Residuals:
     Min       1Q   Median       3Q      Max 
-0.38027 -0.13164  0.04098  0.14635  0.16741 

Coefficients:
            Estimate Std. Error t value Pr(>|t|)    
(Intercept)  9.20690    0.12426   74.09 3.37e-16 ***
xl          -0.74028    0.02956  -25.04 4.74e-11 ***
---
Signif. codes:  0 ‘***’ 0.001 ‘**’ 0.01 ‘*’ 0.05 ‘.’ 0.1 ‘ ’ 1

Residual standard error: 0.1756 on 11 degrees of freedom
Multiple R-squared:  0.9828,    Adjusted R-squared:  0.9812 
F-statistic: 627.1 on 1 and 11 DF,  p-value: 4.736e-11

> summary(mod_2)

Call:
lm(formula = yl ~ xl + xq)

Residuals:
      Min        1Q    Median        3Q       Max 
-0.083361 -0.044580  0.009537  0.046009  0.070311 

Coefficients:
             Estimate Std. Error t value Pr(>|t|)    
(Intercept)  8.467132   0.088933  95.208 4.00e-16 ***
xl          -0.270126   0.050948  -5.302 0.000347 ***
xq          -0.061033   0.006488  -9.407 2.78e-06 ***
---
Signif. codes:  0 ‘***’ 0.001 ‘**’ 0.01 ‘*’ 0.05 ‘.’ 0.1 ‘ ’ 1

Residual standard error: 0.05868 on 10 degrees of freedom
Multiple R-squared:  0.9982,    Adjusted R-squared:  0.9979 
F-statistic:  2852 on 2 and 10 DF,  p-value: 1.643e-14

Übersetzt sind das die beiden Anpassungskurven

$\begin{align*} \ln(y) = 9.2069-0.7403\ln(x) \end{align*}$ , das ist die von Steffen oben.

$\begin{align*} \ln(y) = 8.46713-0.27013\ln(x)-0.06103(\ln(x))^2 \end{align*}$ , das ist die zum zuletzt von mir vorgeschlagenen Modell.

05.08.2016, 18:02

Steffen Bühler

Auf diesen Beitrag antworten »

Das ist im Prinzip nichts anderes. Statt Deiner originalen Wertepaare (3|3021) etc. nimmst Du jetzt deren Logarithmen, also (ln3|ln3021)=(1,0986|8,0133) etc.

So bekommst Du 13 neue Wertepaare. Und mit denen machst Du jetzt entweder eine lineare Regression (Regressionstyp Linear bei Excel), oder, wie HAL vorschlägt, eine quadratische. Letzteres ist Regressionstyp Polynom, Ordnung 2. (Ist im neuesten Excel eigentlich immer noch dieser peinliche Übersetzungsfehler enthalten, das englische Wort "order" (für "Ordnung") hier mit dem deutschen "Reihenfolge" auszudrücken?)

Dann hast Du entweder eine Funktion $\begin{align*} y=bx+a \end{align*}$ oder $\begin{align*} y=cx^2+bx+a \end{align*}$ .

Und nun gehen wir wieder in die lineare Welt. Im linearen Fall bekommst Du also z.B., wie beschrieben, $\begin{align*} y=e^a \cdot x^b \end{align*}$ .

05.08.2016, 19:33

totti

Auf diesen Beitrag antworten »

WOW,

eine Sache nach der anderen.

1. Wie heißt das Programm?
Und ich muss gestehe, dass ich nur die ersten 10 Zeilen Code verstehe.

So ich habe jetzt in Excel mal jeweils den Ln von den x Werten und die Ergebnisse vom ersten Ergebnis.
Es ergibt in Excel eine gerade.

Jetzt würde ich gerne diese Werte bzw die Ergebnisse mit meiner ursprünglichen Funktion vergleichen

05.08.2016, 19:40

HAL 9000

Auf diesen Beitrag antworten »

Zitat:

Original von totti
1. Wie heißt das Programm?

Es heißt schlicht R, und ist salopp gesagt die Freeware-Variante des kommerziellen Statistikpakets S/SPlus, zu weiten Teilen codekompatibel.

Zitat:

Original von totti
Und ich muss gestehe, dass ich nur die ersten 10 Zeilen Code verstehe.

Das ist doch prima, da es ja ingesamt nur 10 Zeilen Code sind. Big Laugh

Big Laugh

08.08.2016, 09:29

Steffen Bühler

Auf diesen Beitrag antworten »

Zitat:

Original von totti
Es ergibt in Excel eine gerade.

Jetzt würde ich gerne diese Werte bzw die Ergebnisse mit meiner ursprünglichen Funktion vergleichen

Dann tu das doch! Was für eine Formel ergibt sich in Excel für die Ausgleichsgerade?

08.08.2016, 15:17

Huggy

Auf diesen Beitrag antworten »

Zitat:

Original von HAL 9000
Multiple lineare Regression kann Excel in der Standardform nicht (oder doch?)

Excel beherrscht das, wenn man die Regression mit den Excelfunktionen RGP bzw. RKP durchführt. Man muss dann nur für den x-Datenbereich einen Bereich aus mehreren Spalten (Zeilen) angeben. Jede dieser Spalten (Zeilen) wird dann als eigene Variable interpretiert.

Allerdings ist es mir nicht gelungen, das über den Diagrammassistenten zu realisieren mit Ausnahme des Polynomansatzes, der keine zusätzlichen Spalten benötigt. Da ich Excel nur selten benutze, mag es sein, dass das bei mir einfach Unkenntnis ist.

1

Verwandte Themen

Die Beliebtesten »

Die Größten »

Die Neuesten »