Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Subscribe to our mailing list

* indicates required

Bestand crasht Excel!!

Red mijn werkmap!
De beste tool voor Excel bestanden met problemen.

Cursussen

Excel VBA Masterclass (Engels)
Excel VBA voor Financials

Third party tools

Speed up your file

FastExcel
The best tool to optimise your Excel model!

Repair your file

Stellar Phoenix Excel Repair
Best tool to repair corrupt Excel sheets and objects
Home > Nederlandse site > Artikelen > Kleinste Kwadraten
This page in English

curves fitten met de kleinste kwadraten methode

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 de theoretisch bij je metingen zou moeten passen.

Misschien heb je wel metingen gedaan met resultaten zoals deze:

Gegevens fitten met een vergelijking
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
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):

Ribbon with Data Analysis button
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
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:

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
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 zodat het bestand ook werkt in Excel 2003 of zelfs oudere Excel versies. Vanaf Excel 2007 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 =VERSCHUIVING(Sheet1!$A$2,0,0,AANTAL(Sheet1!$A$1:$A$65551),1) Kolom met x-waarden
yDelta =VERSCHUIVING(xValues,0,3) Kolom met kwadratische verschillen
yhat =VERSCHUIVING(xValues,0,2) Kolom met model fit resultaat
yValues =VERSCHUIVING(xValues,0,1) Kolom met y-waarden

Constanten van de vergelijking

De bereiknamen die beginnen met "const" verwijzen naar een tweede tabel in het bestand:

Constanten tabel
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
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
Het eindresultaat

Variantie analyse

Op het werkblad ANOVA, vind je de Variantie analyse, die er zo uitziet:

De ANOVA tabel
De ANOVA tabel

De belangrijkste cel van deze tabel is cel F2. 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 B2 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 B2 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!


Vragen, suggesties en opmerkingen

Heeft u vragen, suggesties of opmerkingen? Gebruik dan dit formulier.

Mocht uw vraag niet direct relevant zijn voor deze pagina, maar een algemene Excel vraag betreffen, dan adviseer ik om deze hier te stellen: www.eileenslounge.com.

Uw naam (verplicht veld):

Uw e-mail adres (Niet verplicht, dit adres wordt niet getoond)

Uw verzoek of commentaar:

Als u VBA code in uw commentaar plaatst, gebruik dan [VB] tags: [VB]Uw code[/VB].