curves fitten met de kleinste kwadraten methode
Inhoud
Inleiding
Als je (net als ik, voordat ik Excel specialist werd) ingenieur bent,
dan heb je waarschijnlijk het nodige aan experimenteerwerk gedaan. Vaak
heb je dan een methode nodig om je meetresultaten te fitten met een kromme.
En als je een echte ingenieur bent, dan heb je ook al een goed idee wat
de soort vergelijking is die theoretisch bij je metingen zou moeten passen.
Misschien heb je wel metingen gedaan met resultaten zoals deze:
Gegevens fitten met een vergelijking.
Een zeer bekende methode om gegevens te fitten met een vergelijking is
de
methode der kleinste kwadraten (Least Squares). Ik ga hier niet de theorie
uitleggen achter deze methode, lees daarover meer op de genoemde Wikipedia
pagina.
Simpele lineaire vergelijkingen
fitten
Excel bevat een aantal hulpmiddelen om berekeningen te doen met de kleinste
kwadraten methode, maar deze beperken zich allemaal tot eenvoudigere (lineaire)
functies in de vorm van:
y=a.x+b, y-a.exp(b.x), y=a.x^b etcetera. Met enkele trucjes kan je daarnaast
ook polynomen fitten met Excel.
Regressie tools in de invoegtoepassing Analysis Toolpak
Activeer de Analysis Toolpak in je lijst met invoegtoepassingen (Bestand
tab of Office knop, Excel Opties, tabje Invoegtoepassingen, klik Start):
Excel's lijst met invoegtoepassingen met het Analysis toolpak geselecteerd
Hiermee wordt de groep "Analysis" aan je lint toegevoegd op de tab Gegevens
met de knop Gegevens analyse (Dit is ook waar je straks de Oplosser knop
kan vinden die ik verderop noem):
Lint met de groep Analysis (mijn Excel is een Engelse installatie met het
Nederlandse taalpakket. Hierdoor worden sommige functies waaronder de invoegtoepassingen
niet altijd volledig vertaald)
Klik op die knop om te zien welke regressie tools beschikbaar zijn.
Werkblad functies
Er is een aantal functies beschikbaar om regressie analyses mee te doen.
Om deze snel te vinden druk je shift+F3 om de functie wizard te openen.
In het zoekvak voer je in: "Regressie". Excel zal een aantal relevante functies
tonen:
Functie wizard met Regressie functies
Helaas is deze lijst in een Nederlandse Excel niet compleet met deze
zoekterm. Andere zoektermen zijn "Kleinste kwadraten" en "Fit". Kies er
eentje uit en klik eens op Help-informatie over deze functie om er meer
over te leren.
Complexere functies fitten
Wat nu als je een complexere functie hebt, zoals y=exp(a.x).sin(x) +
b ? Hoe fit je deze curve met behulp van Excel?
Ik heb hiervoor een methode bedacht die de volgende stappen omvat:
- Maak een tabel met x en y waarden
- Voeg een kolom toe achter deze tabel met daarin de formule van jouw
functie zoals je die in Excel moet invoegen, die verwijst naar je x-waarden
en naar een paar cellen (met bereiknamen) die de constanten van de vergelijking
bevatten die je wilt gaan schatten
- Voeg een kolom toe die de
som der kwadraten berekent
- Gebruik Excel's Oplosser om de constanten te zoeken die de kleinste
som der kwadraten vindt.
Uitleg bij het voorbeeldbestand
Ik heb een voorbeeldbestand gemaakt dat je direct kunt gebruiken. Hieronder
vind je de link naar het bestand en een uitleg hoe dit bestand is samengesteld.
Download
Download dit bestand:
Non linear least
squares
Hoe het bestand werkt
Gegevens
De gegevens en de calculatie staan beide op het werkblad Sheet1. het
belangrijkste gebied is de tabel die start vanaf cel A1:
Data tabel in het voorbeeldbestand
Ik kolom A staan de x-waarden van de meting en in kolom B de gemeten
waarden. De derde kolom bevat de vergelijking die gefit moet worden. Het
voorbeeldbestand heeft deze vergelijking in kolom C:
=EXP(Const_a*xValues)*SIN(xValues)+Const_b
De vierde kolom berekent de kwadratische verschillen, formule:
=(B2-C2)^2
Zoals je waarschijnlijk al ziet heb ik gebruik gemaakt van enkele bereiknamen.
Ik leg deze hieronder even uit.
Bereiknamen
Om het werken met het bestand eenvoudiger te maken heb ik bereiknamen
toegepast. In plaats van te werken met de gestructureerde tabelverwijzingen
heb ik in dit bestand gebruik gemaakt van dynamische bereiknamen. Tegenwoordig
kunnen we de bereiknamen gewoon rechtstreeks laten verwijzen naar de cellen
in de tabel omdat Excel er middels de tabelfunctionaliteit voor zorgt dat
de bereiknamen worden aangepast als de tabel groeit of krimpt.
Bereik naam
Verwijst naar
Omschrijving
Const_a
=Sheet1!$G$2
Model constante
Const_b
=Sheet1!$G$3
Model constante
Const_c
=Sheet1!$G$4
Model constante
Const_d
=Sheet1!$G$5
Model constante
Const_e
=Sheet1!$G$6
Model constante
Const_f
=Sheet1!$G$7
Model constante
Const_g
=Sheet1!$G$8
Model constante
Const_h
=Sheet1!$G$9
Model constante
Constants
=Sheet1!$G$2:$G$9
constanten van de vergelijking
xValues
=Table2[x]
Kolom met x-waarden
yDelta
=Table2[(y - yhat)²]
Kolom met kwadratische verschillen
yhat
=Table2[yhat]
Kolom met model fit resultaat
yValues
=Table2[y]
Kolom met y-waarden
Constanten van de vergelijking
De bereiknamen die beginnen met "const" verwijzen naar een tweede tabel
in het bestand:
Constanten tabel
In deze tabel kan je je eerste schattingen ingeven voor de constanten.
Hier zal de Oplosser bovendien de resultaten in plaatsen. Zoals je kunt
zien is onder deze tabel de resterende som der kwadraten weergegeven. Formule:
=SOM(yDelta)
Het is deze cel die we met behulp van de oplosser invoegtoepassing gaan
minimaliseren (kleinste kwadraten).
De oplosser gebruiken
Allereerst dien je de invoegtoepassing Oplosser te installeren, gebruik
makend van het dialoogvenster invoegtoepassingen dat ik eerder al heb weergegeven.
Zet een vinkje naast "Oplosser-invoegtoepassing", waarmee de knop "Oplosser"
wordt toegevoegd aan de groep Analyse op de Gegevens tab in het lint.
Nadat je je ervan hebt vergewist dat je model formule in kolom C juist
is ingevoerd en de berekeningen goed werken klik je op die knop. Het onderstaande
venster opent (helaas heb ik daar de Nederlandse versie niet van):
Het dialoogvenster van de Oplosser
Zorg ervoor dat het "Set Objective" vakje verwijst naar de cel met de
som van de kwadratische verschillen. Selecteer "Min" bij "To".
Het vak "By Changing Variable cells" mag ALLEEN verwijzen naar cellen
die ook echt in je model in gebruik zijn, anders klopt het aantal vrijheidsgraden
niet dat wordt gebruikt bij de calculatie van de ANOVA (variantie analyse).
Zorg er tevens voor dat de cellen van de niet gebruikte constanten leeg
zijn door ze aan te klikken en op de Del toets te drukken.
Merk op dat je afhankelijk van je model soort mogelijk de
instellingen van Oplosser moet aanpassen. Het kan hier nodig zijn wat met
die instellingen te experimenteren voor het beste resultaat. Je kunt instellingen
opslaan en openen met de betreffende knoppen van de oplosser.
Wees dus verstandig en kritisch en bekijk goed of de gevonden oplossing
daadwerkelijk een juiste fit van je gegevens is want de Oplosser komt niet
altijd met het beste resultaat. Wat het resultaat is hangt soms sterk af
van de gekozen instellingen van de oplosser.
Als je tevreden bent met de huidige instellingen van de oplosser klik
je op "Solve" (Oplossen?). Als vervolgens na enige tijd het dialoogvenster
Oplosser resultaten opent, dan krijg je enkele opties om verder te gaan.
Merk op dat je hier ook enkele keuzemogelijkheden krijgt voor het genereren
van een rapport.
Het eindresultaat in het voorbeeldbestand is:
Het eindresultaat
Variantie analyse
Op het werkblad ANOVA, vind je de Variantie analyse, die er zo uitziet:
De ANOVA tabel
De belangrijkste cel van deze tabel is cel K34. Zolang de waarde van deze
cel kleiner is dan 0,05, dan is er een waarschijnlijkheid van 95% dat je
model je data correct fit. Dus "less is more" voor deze cel, de waarde ervan
moet onder de 0,05 blijven. Met hulp van voorwaardelijke opmaak wordt deze
cel rood bij waarden boven de 0,05.
Controleer wel even of de waarde in cel G34 exact 1 minder is dan het
aantal constanten in je model. Dus gebruik je alleen de constanten const_a
en const_b, dan moet er in cel G34 1 staan (model degrees of freedom).
Conclusie
Zoals je ziet is het fitten van complexe functies niet heel moeilijk
om te doen als je van de genoemde trucjes gebruik maakt. Een combinatie
van enkele relatief eenvoudige formules en de Oplosser invoegtoepassing
schieten je hier te hulp.
Tot slot nog een advies van de ene ingenieur aan de andere: Wees alsjeblieft
kritisch. Geloof niet alles wat Excel je vertelt. Analyseer het resultaat
nauwgezet want de oplosser geeft wel eens onjuiste resultaten en dus niet
de juiste oplossing van je probleem!
