Excel-Formular zur Methode der kleinsten Quadrate

[Hirschberger]

#23289

Bei der Justage unserer Maschinen werden Analogsensoren verwendet, welche einen Messwert in Digits ausgeben. Diesen Wert soll dann jeweils in eine physikalische Grösse umgewandelt werden.
Bsp:
Messung 1: 10000 Digits => 0,20 mm
Messung 2: 10050 Digits => 0,21 mm
Messung 3: 10300 Digits => 0,24 mm
…..

Diese Umrechung erfolgt mittels der Methode der kleinsten Quadrate (bezeichnender auch: der kleinsten Fehlerquadrate; englisch: Least Squares Method)

Meine Frage bzw. Bitte:
Hat da jemand eine Vorlage für Excel, zur Ermittlung der entsprechenden Mathematischen Funktion ?

[msb]

#49935

Hallo Hirschberger,

habe dir nicht die gesuchte Vorlage, aber ein gutes Forum, wo du evtl. das Gesuchte findest: http://www.herber.de/forum/index.htm

alternativ: http://www.excel-center.de/foren/list.php?2

Gruß msb

wer die Wahrheit sucht, wird sie finden

geändert von – msb on 07/01/2008 14:02:32

[Barbara]

#49936

Hallo Hirschberger,

ich bin verwirrt.

Wenn Du Zahlen aus einem Dualsystem in ein Dezimalsystem umrechnen willst, dann geht das relativ einfach, z. B. in Excel über die Funktion LINKS oder RECHTS die entsprechende Zahl aus dem Digits-String ausschneiden und mit der passenden Potenz von 2 multiplizieren und am Ende alle Zahlen addieren (etwas genauer steht es bei Wikipedia).

Die Least-Squares-Methode kenne ich ausschließlich aus dem Bereich, wo keine exakte Umrechnung möglich ist, sondern Zahlenwerte approximiert werden. (Einfaches Beispiel für die LS-Methode ist die Ausgleichsgerade, bei der die Summe der quadrierten Abweichungen von der Gerade möglichst klein wird, heißt deshalb auch KQ- oder Kleinste-Quadrate-Methode und wird durch die Gaußschen Normalgleichungen bestimmt.)

Ich krieg nur die Verbindung zwischen einer Umwandlung eines digitalen in einen dezimalen Messwert überhaupt nicht zusammen mit der LS-Methode.

Kannst Du mir mal bitte schlau machen? Danke!

Viele Grüße

Barbara

_____________________________________

Ich fühle, dass Kleinigkeiten die Summe des Lebens ausmachen.
(Charles Dickens, Schriftsteller)

[dreichl]

#49940

Hallo Hirschberger,

Kann es sein, dass das Problem daher kommt, dass es bei obigem Beispiel keinen linearen Zusammenhang zwischen Digitsnzahl und Messwert gibt?

Meine Methode wäre da dann, die „Kennlinie“ für den gesamten Messbereich abzustecken und dann zu approximieren. Linear, quadratisch, etc. Just my 2 cents.

Gruß,
Dieter

Gruß,
Dieter

[Rainaari]

#49942

Servus,

ganz platt geht das mit der Excel Funktion ‚trend‘ für einen Linearen Zusammenhang oder Variation bei einem exponentiellen Zusammenhang.

Ansonsten hilft die von Dreichl vorgeschlagene Methode, zunächst eine Kennlinie aufzunehmen. Eine differenzierte Bearbeitung und fitten der Kurve geht dann z. B. mit Sigma-Plot. Excel ist hier eher ungeeignet*. Bevor du die Kurve allerdings blind fittest, solltest du dir Gedanken über die dahinterstehende Physik / Mathematik und die zu erwartende Kurve (Polynom, exponentiell, logaritmisch, doppelt-logaritmisch, Sättigung, $whatever) machen.
Eine so ermittelte Kurve läßt sich dann wieder in Excel übernehmen und berechnen, ggf. läßt sich in Excel auch mit Interpolationen arbeiten.

gruß,

Rainaari

*Einen ersten Eindruck vermittelt die X/Y Grafik, dann ggf. _vorsichtig_ die in Excel enthaltenen Trendfunktionen nutzen.

[Q-Planer]

#49949

Hallo zusammen,

die Fragestellung von Hirschberger verstehe ich so:

Er sucht die Parameter der Geraden, die optimal den Zusammenhang der drei Wertepaare wiedergibt. (Eine Gleichung höher Ordnung macht bei drei Punkten nicht viel Sinn.)

Eine (lesbare) Darstellung der Bestimmungsgleichungen für a und b hier im Forum ist kaum möglich; sie lassen sich aber unter dem Stichwort „Lineare Regression“ in entsprechenden Formelsammlungen und auch wohl im Netz finden. – Sie lässt sich nach Einfügen der entsprechenden Spalten für Nebenrechnungen problemlos und schnell in Excel umsetzen.

…..……..X-i……….. Y-i …..(X-i)^2 ……..X-i * Y-i
——————————————
..…………0,20….10.000……..0,040……..…2.000,0
……..…..0,21….10.050……..0,044……..…2.110,5
………….0,24….10.300……..0,058……..…2.472,0
——————————————-
Summe.0,65.…30.350……..0,142……..…6.582,5

Das Endergebnis für das angegeben Beispiel lautet dann:

Y = a + b*X = 8.450,0 + 7.692,3*X

Gruß

WH

[Barbara]

#49950

Hallo zusammen,

ah jetzt ja.

Wenn es um die Anpassung einer noch unbekannten Funktion an Wertepaare geht, dann würd ich als erstes nach Informationen zu dieser Funktion suchen und *nicht* nach einem Excel-Sheet. Je nach Mess-Situation können da ganz unterschiedliche Funktionen in Frage kommen.

@Q-Planer: Man kann schon mit drei Punkten auch eine Kurve anpassen, die Frage ist nur, ob das sinnvoll ist.

Grundsätzlich wäre ein Modell für diese Funktion von der Form Digits ~ Messwert / x~y (d. h. die Digits bestimmen den Messwert, nicht umgekehrt). Eine Ausgleichsgerade ist eine relativ einfache Funktion. Ob die passt, lässt sich allerdings nur mit dem entsprechenden technischen Hintergrund beantworten. (Wahrscheinlich ist irgendein Polynom sinnvoller.)

Irgendwie scheint es bei Euch ja im Moment eine Umrechnung zu geben oder sind die oben angegebenen Werte Phantasie-Werte?

Wenn es diese Umrechnung gibt und sie irgendwo sichtbar ist, könntest Du sie relativ einfach in Excel umsetzen. Also gibt es diese Umrechnung entweder nicht oder sie ist unsichtbar.

Ich glaub ich brauche da noch ein paar mehr Infos, um die ursprüngliche Frage beantworten zu können ;-)

Viele Grüße

Barbara

_____________________________________

Ich fühle, dass Kleinigkeiten die Summe des Lebens ausmachen.
(Charles Dickens, Schriftsteller)

[Barbara]

#49951

Nachtrag:

Ich würd solche Berechnungen übrigens grundsätzlich nicht zu Fuß machen, weil diese Quadratsummen sehr fehleranfällig sind. (Was haben wir damals im Studium geflucht, bevor wir auf den PC umgestiegen sind…)

Eine Ausgleichsgerade in R zu bestimmen, ist viiiel einfacher und geht auch deutlich schneller:

###### Beginn R-Syntax ######
# Ab hier kann die Syntax direkt in R kopiert werden:

# Messwerte eingeben, x Digits, y Ergebnis
x <- c(10000,10050,10300)
y <- c(0.20,0.21,0.24)

# Ausgleichsgerade bestimmen und in lm.1 speichern
lm.1 <- lm(y~x1)

# Modell-Werte
summary(lm.1)

# Koeffizienten a und b für die Ausgleichsgerade
# y = a + b*x
a <- lm.1$coef[1]
b <- lm.1$coef[2]

# Bevor das Modell für die Umrechnung verwendet wird, sollte es natürlich
# validiert werden.
# Bei nur drei Messwerten ist das schwer möglich, deshalb sollten hier
# weitere Messwerte gesammelt werden.

# Die Grafik zu den Messwerten:
plot(x1,y, xlab=“Digits“, ylab=“Ergebnis“)

# Ausgleichsgerade in die Grafik einzeichnen
library(car)
reg.line(lm.1)

###### Ende R-Syntax ######

Und ganz egal, ob ich nur für drei Werte oder für einige tausend Werte die Ausgleichsgerade bestimmen will, geht das Ratze-Fatze-Katze (in jedem Statistik-Programm).

Ich sollte mir nur vorher sicher sein, dass eine *Gerade* auch ein passendes Modell ist.

Viele Grüße

Barbara

_____________________________________

Ich fühle, dass Kleinigkeiten die Summe des Lebens ausmachen.
(Charles Dickens, Schriftsteller)

[Cara]

#49983

Hallo Leute,

hab da noch nen guten Link zu Excel
http://www.office-loesung.de/index3.php

Der hat mir schon öfters das Leben gerettet ;-)

Gruß
Cara

[Q-Planer]

#49984

Hallo zusammen,

hat Barbara Recht:

Auch (nur) drei Punkte können durch eine nicht-lineare Kurve beschrieben werden.
Auf die von Hirschberger genannten Wertepaare lässt sich z. B. eine quadratische Kurve „zwingen“ (Y = a+b*X+c*X^2 = 12.500 + 29.167 * X – 83.333 * X^2).

Dies ist aber nur dann sinnvoll, wenn man den Zusammenhang genau kennt.
Beispiel: Über die Zeitabhängigkeit der Radioaktivität ist bekannt, dass sie einen exponentiellen Verlauf hat. Durch entsprechende Kurvenanpassung kann die ich deshalb aus wenigen Punkten u. a. die Halbwertszeit des strahlenden Materials ermitteln.

Im vorliegen Fall ist ein solcher Zusammenhang nicht bekannt. – Im Übrigen ist die Abweichung von einer vollkommen Geraden äußerst gering: Die Zahlenpaare 0,2015 mm/10.000, 0,2080 mm/10.050 und 0,2405 mm/10.300 lägen exakt auf der von mir angegebenen Geraden. – Die Unterschiede sind also <= 0,002 mm. Da die Werte nur auf 0,01 mm angegeben sind, kann m. E. (zumindest im betrachten Bereich) von einem linearen Verlauf ausgegangen werden. – Auch der Wert des Korrelationskoeffizienten von 0,996 belegt dies.

Dies gilt allerdings nur, solange der Messbereich das Intervalls 0,20 ,/. 0,24 mm nicht (deutlich) überschreitet.

Weitere Wertepaare wären trotzdem nützlich, um den Zusammenhang zu untermauern (oder zu widerlegen).

Zu x~ y
——–

Welcher der Werte jeweils als X aufzufassen ist, ist eine Frage des Blickpunktes. Ich verstehe Hirschberger so, dass es bei dem ganzen um die Skalierung eines Messsystems geht. Die Messgröße (z. B.0,20 mm) wird in das System eingegeben und dieses „antwortet“ mit „10.000“. Somit ist die Länge (mm) die unabhängige Variable.

Gruß

WH

[Hirschberger]

#50013

Hallo !
Sorry für die Passivität von meiner Seite -Ich war jetzt ein paar Tage auf Geschäftsreise, da hatte ich meinen Kopf woanders.

Danke wie Ihr euch mit der Thematik beschäftigt habt, trotz meiner schlechten Beschreibung – für mich war das ganz klar…

Dass das nicht immer ein Gerade gibt, sondern auch mal was ganz anderes, ist klar.
Ich suchte eigentlich ein Excel-Sheet,der mir das Leben einfach macht, bevor ICH entscheiden muss, ob das linear ist oder eine Kurve n-ten Grades, deren Berechnung das System an der Maschine ausbremst.

Ein Paradebeispiel für das Problem ist die klassische Zugprüfung: Ich ziehe mit einer definieren Kraft, und schaue wie gross die Dehnung des Prüflings ist: Zuerst ist es ist es eine Gerade, dann eine Quadratfunktion. Wenn ich die entsprechende Umrechnung (x-Digits => y Weg) „von Hand“ festlege, dann sind die Diskussionen mit den Kunden etwas länger.

Nun, ich werde den Ansatz von Barbara mal durchtesten, bisher war mir der Ansatz der kleinsten Quadrate bekannt.

[Barbara]

#50015

Hallo Hirschberger,

die Entscheidung, welche Art Deine Kurve hat, ist keine Excel- oder Statistik-Frage, sondern eine Frage, die ein Kenner der Mess-Situation deutlich besser beantworten kann. Dieses Hintergrundwissen brauchst Du notwendigerweise, bevor Du ein Modell für den Einfluss von X auf Y erstellst (ansonsten bastelst Du Dich dumm und dusselig an einem funktionierenden Modell).

Du kannst natürlich auch erstmal ein Modell probieren, nur stellst Du dann u. U. später fest, dass das so überhaupt nicht funktioniert (Stichwort Modell-Prüfung, s. u. oder auch den Thread zur Regression.)

Das was ich aufgeschrieben habe, *ist* der LS-Ansatz, nur eben mit R statt mit Excel berechnet. Und natürlich kann ich auch statt der Geradengleichung ein Polynom als Modell annehmen, also in R zumindest, z. B.:
Y ~ X + X² + X³
bzw.
Y = b0 + b1*X + b2*X² + b3*X³
(das „a“ aus der Modellgleichung weiter oben entspricht dann b0, das „b“ b1). Das ist immer noch ein lineares Modell, weil es linear in den Parameter bi ist. Die Einflussgrößen dürfen ruhig nicht-linear auf Y wirken.

Und so geht das in R:

######### Beginn R-Syntax #########

# Digits / X
x1 <- c(10000,10040,10100,10160,10220,10250,10270,10280,10300)

# polynomiale Einflüsse von x auf y, hier: quadratisch und kubisch
x2 <- x1^2
x3 <- x1^3

y <- c(0.20,0.20,0.21,0.20,0.21,0.22,0.23,0.22,0.24)

lm.2 <- lm(y~x1+x2+x3)

summary(lm.2)

plot(x1,y, pch=20, cex=1.4, axes=FALSE, xlab=“Digits“, ylab=“Ergebnis“)
axis(1)
axis(2, las=2)
box()

# Einzeichnen der angepassten Funktion (deutlich nicht-linear)

neue.x <- seq(min(x1),max(x1),5) # neue x-Werte

# Berechnung der Modell-Schätzung / Prognose für neue x-Werte / Digits
prognose <- function(x) lm.2$coef[1]+lm.2$coef[2]*x+lm.2$coef[3]*x^2+lm.2$coef[4]*x^3
angepasst <- prognose(neue.x)

# Einzeichnen der Linie (kubische Funktion)
lines(neue.x,angepasst, col=“blue“)

# prognostizierte Funktionswerte für die Modell-Diagnose
fitted <- lm.2$fitted.values

# Und bei aller Freude über das gefundene Modell muss *immer*
# geprüft werden, ob das Modell auch funktioniert.
# Dafür gibt es neben der Anpassungsgüte R² (steht in der
# Summary als R-squared und ist hier 89 %) die
# Modell-Diagnose-Grafiken

#
# Modell-Diagnose
#

resi <- lm.2$resid

# Wahrscheinlichkeitsnetz, Modell-Reste / Residuen sollten nur Rauschen enthalten,
# d. h. normalverteilt sein
qqnorm(resi, pch=20, xlab=“Soll / Normalverteilung“, ylab=“Ist / Modell-Reste / Residuen“,
main=“Wahrscheinlichkeitsnetz“)
qqline(resi, col=“blue“)

# Histogramm -> Normalverteilung?
hist(resi, col=“whitesmoke“, xlab=“Modell-Reste / Residuen“, ylab=“Häufigkeit“,
main=“Histogramm“)

# Modell-Reste vs. aus dem Modell geschätzte Werte
# -> sollte keine weiteren Strukturen enthalten!
plot(fitted,resi, pch=20, xlab=“aus dem Modell geschätzt“, ylab=“Modell-Reste / Residuen“,
main=“Modell-Reste vs. Modell-Schätzung“)

# Modell-Reste über die Zeit / nach Beobachtungsnummer
# -> sollte keine zeitliche Struktur zeigen!
plot(resi, pch=20, xlab=“Nr im Datensatz“, ylab=“Modell-Reste / Residuen“,
main=“Modell-Reste über die Zeit“)

######### Ende R-Syntax #########

Die Modell-Diagnose-Grafiken sehen in dem obigen Beispiel ganz akzeptabel aus. Natürlich ist die Anpassung eines Polynoms 3-ten Grades bei den vorgegebenen Daten nicht optimal und damit die Anpassungsgüte R² nur 90 %. Auf jeden Fall scheint in diesem Fall eine kubische Funktion halbwegs zu taugen.

Wie immer gilt: Erst GMV, dann Statistik! So, und jetzt ist es genug Statistik für heute.

Viele Grüße

Barbara

_____________________________________

Ich fühle, dass Kleinigkeiten die Summe des Lebens ausmachen.
(Charles Dickens, Schriftsteller)

[Rainaari]

#50028

Mal so rein aus Neugier:

Hirschberger, konntest du mit dem Input was anfangen oder haben wir alle am Problem vorbei (ge|be)raten? ;)

gruß, Rainaari

[Hirschberger]

#50058

Zur Frage von Rainaari:
Ja und nein:
Ich kriegte nicht die Antwort die ich wollte, nämlich dass diese Berechnung jemand in der Schublage hat.
Die Beiträge zur Berechnung sind mir sehr vertraut, weil ich an dem Knochen regelmäßig nage…

[Stefan741]

#61467

Hallo Barbara,

leider hat dein erster R-Code nicht funktioniert (reg.line).
Hier die korrigierte Version:
###### Beginn R-Syntax ######
# Ab hier kann die Syntax direkt in R kopiert werden:

# Messwerte eingeben, x Digits, y Ergebnis
x <- c(10000,10050,10300)
y <- c(0.20,0.21,0.24)

# Ausgleichsgerade bestimmen und in lm.1 speichern
lm.1 <- lm(y~x)

# Modell-Werte
summary(lm.1)

# Koeffizienten a und b für die Ausgleichsgerade
# y = a + b*x
a <- lm.1$coef[1]
b <- lm.1$coef[2]

# Bevor das Modell für die Umrechnung verwendet wird, sollte es natürlich
# validiert werden.
# Bei nur drei Messwerten ist das schwer möglich, deshalb sollten hier
# weitere Messwerte gesammelt werden.

# Die Grafik zu den Messwerten:
plot(x,y, xlab=“Digits“, ylab=“Ergebnis“)

# Ausgleichsgerade in die Grafik einzeichnen
#library(car)
regLine(lm.1)

###### Ende R-Syntax ######

Gruß
Stefan

[Autor]

Beiträge