Excel heeft ingebouwde functies die u kunt gebruiken om uw kalibratiegegevens weer te geven en een best passende lijn te berekenen. Dit kan handig zijn wanneer u een chemisch laboratoriumrapport schrijft of een correctiefactor in een apparaat programmeert.
In dit artikel bekijken we hoe u Excel kunt gebruiken om een grafiek te maken, een lineaire kalibratiecurve uit te zetten, de formule van de kalibratiecurve weer te geven en vervolgens eenvoudige formules in te stellen met de functies SLOPE en INTERCEPT om de kalibratievergelijking in Excel te gebruiken.
Wat is een kalibratiecurve en hoe is Excel nuttig bij het maken ervan?
Om een kalibratie uit te voeren, vergelijkt u de meetwaarden van een apparaat (zoals de temperatuur die een thermometer weergeeft) met bekende waarden die standaarden worden genoemd (zoals het vriespunt en het kookpunt van water). Hiermee kunt u een reeks gegevensparen maken die u vervolgens kunt gebruiken om een kalibratiecurve te ontwikkelen.
Een tweepuntskalibratie van een thermometer met behulp van de vries- en kookpunten van water zou twee gegevensparen hebben: één vanaf het moment dat de thermometer in ijswater (32 ° F of 0 ° C) wordt geplaatst en één in kokend water (212 ° F) of 100°C). Wanneer u die twee gegevensparen als punten plot en er een lijn tussen trekt (de kalibratiecurve), en aangenomen dat de respons van de thermometer lineair is, kunt u elk punt op de lijn kiezen dat overeenkomt met de waarde die de thermometer weergeeft, en u kon de corresponderende “echte” temperatuur vinden.
De lijn vult dus in wezen de informatie tussen de twee bekende punten voor u in, zodat u redelijk zeker kunt zijn bij het schatten van de werkelijke temperatuur wanneer de thermometer 57,2 graden aangeeft, maar wanneer u nog nooit een “standaard” hebt gemeten die overeenkomt met dat lezen.
Excel heeft functies waarmee u de gegevensparen grafisch in een grafiek kunt plotten, een trendlijn (kalibratiecurve) kunt toevoegen en de vergelijking van de kalibratiecurve in de grafiek kunt weergeven. Dit is handig voor een visuele weergave, maar u kunt ook de formule van de lijn berekenen met de functies SLOPE en INTERCEPT van Excel. Wanneer u deze waarden in eenvoudige formules invoert, kunt u automatisch de “echte” waarde berekenen op basis van elke meting.
Laten we een voorbeeld bekijken
Voor dit voorbeeld zullen we een kalibratiecurve ontwikkelen uit een reeks van tien dataparen, elk bestaande uit een X-waarde en een Y-waarde. De X-waarden zullen onze ‘standaarden’ zijn en ze kunnen alles vertegenwoordigen, van de concentratie van een chemische oplossing die we meten met een wetenschappelijk instrument tot de invoervariabele van een programma dat een marmeren lanceermachine bestuurt.
De Y-waarden zijn de “reacties”, en ze vertegenwoordigen de meetwaarde die het instrument heeft verstrekt bij het meten van elke chemische oplossing of de gemeten afstand van hoe ver weg van de draagraket de knikker landde met behulp van elke invoerwaarde.
Nadat we de kalibratiecurve grafisch hebben weergegeven, zullen we de functies SLOPE en INTERCEPT gebruiken om de formule van de kalibratielijn te berekenen en de concentratie van een “onbekende” chemische oplossing te bepalen op basis van de meetwaarde van het instrument of om te beslissen welke invoer we het programma moeten geven zodat de marmer landt op een bepaalde afstand van de draagraket.
Stap één: maak uw grafiek
Onze eenvoudige voorbeeldspreadsheet bestaat uit twee kolommen: X-waarde en Y-waarde.
Laten we beginnen met het selecteren van de gegevens die u in de grafiek wilt plotten.
Selecteer eerst de ‘X-Value’-kolomcellen.
Druk nu op de Ctrl-toets en klik vervolgens op de Y-waarde-kolomcellen.
Ga naar het tabblad “Invoegen”.
Navigeer naar het menu “Grafieken” en selecteer de eerste optie in de vervolgkeuzelijst “Scatter”.
Selecteer de serie door op een van de blauwe punten te klikken. Eenmaal geselecteerd, schetst Excel de punten die zullen worden geschetst.
Klik met de rechtermuisknop op een van de punten en selecteer vervolgens de optie “Trendlijn toevoegen”.
Er verschijnt een rechte lijn op de kaart.
Aan de rechterkant van het scherm verschijnt het menu “Trendlijn opmaken”. Vink de vakjes aan naast ‘Vergelijking weergeven in grafiek’ en ‘R-kwadraatwaarde weergeven in grafiek’. De R-kwadraatwaarde is een statistiek die aangeeft hoe goed de lijn bij de gegevens past. De beste R-kwadraatwaarde is 1.000, wat betekent dat elk datapunt de lijn raakt. Naarmate de verschillen tussen de gegevenspunten en de lijn groter worden, daalt de r-kwadraatwaarde, waarbij 0,000 de laagst mogelijke waarde is.
De vergelijking en R-kwadraat-statistiek van de trendlijn verschijnen op de grafiek. Merk op dat de correlatie van de gegevens in ons voorbeeld erg goed is, met een R-kwadraatwaarde van 0,988.
De vergelijking heeft de vorm “Y = Mx + B”, waarbij M de helling is en B het snijpunt van de rechte lijn op de y-as.
Nu de kalibratie is voltooid, gaan we werken aan het aanpassen van de grafiek door de titel te bewerken en astitels toe te voegen.
Om de grafiektitel te wijzigen, klikt u erop om de tekst te selecteren.
Typ nu een nieuwe titel die de grafiek beschrijft.
Om titels aan de x-as en y-as toe te voegen, navigeert u eerst naar Hulpmiddelen voor grafieken > Ontwerpen.
Navigeer nu naar Astitels > Primair horizontaal.
Als u de naam van de as wilt wijzigen, selecteert u eerst de tekst en typt u vervolgens een nieuwe titel.
Ga nu naar Astitels> Primair verticaal.
Er verschijnt een astitel.
Hernoem deze titel door de tekst te selecteren en een nieuwe titel in te typen.
Je grafiek is nu compleet.
Stap twee: Bereken de lijnvergelijking en R-kwadraatstatistiek
Laten we nu de lijnvergelijking en R-kwadraat-statistiek berekenen met behulp van Excel’s ingebouwde SLOPE-, INTERCEPT- en CORREL-functies.
Aan ons blad (in rij 14) hebben we titels toegevoegd voor die drie functies. We voeren de daadwerkelijke berekeningen uit in de cellen onder die titels.
Eerst berekenen we de HELLING. Selecteer cel A15.
Navigeer naar Formules > Meer functies > Statistisch > HELLING.
Selecteer of typ in het veld “Known_xs” de X-Value-kolomcellen. De volgorde van de velden ‘Known_ys’ en ‘Known_xs’ is van belang in de SLOPE-functie.
Klik OK.” De uiteindelijke formule in de formulebalk zou er als volgt uit moeten zien:
=HELLING(C3:C12,B3:B12)
Merk op dat de waarde die wordt geretourneerd door de functie SLOPE in cel A15 overeenkomt met de waarde die in de grafiek wordt weergegeven.
Selecteer vervolgens cel B15 en navigeer vervolgens naar Formules > Meer functies > Statistisch > INTERCEPT.
Selecteer of typ in de X-Value-kolomcellen voor het veld “Known_xs”. De volgorde van de velden ‘Known_ys’ en ‘Known_xs’ is ook van belang in de INTERCEPT-functie.
Klik OK.” De uiteindelijke formule in de formulebalk zou er als volgt uit moeten zien:
=INTERCEPT(C3:C12,B3:B12)
Merk op dat de waarde die door de INTERCEPT-functie wordt geretourneerd, overeenkomt met het y-snijpunt dat in de grafiek wordt weergegeven.
Selecteer vervolgens cel C15 en navigeer naar Formules > Meer functies > Statistisch > CORREL.
Selecteer of typ de andere van de twee celbereiken voor het veld “Array2”.
Klik OK.” De formule zou er als volgt uit moeten zien in de formulebalk:
=CORREL(B3:B12,C3:C12)
Merk op dat de waarde die wordt geretourneerd door de functie CORREL niet overeenkomt met de “r-kwadraat” waarde in de grafiek. De functie CORREL retourneert “R”, dus we moeten het kwadraat om “R-kwadraat” te berekenen.
Klik in de functiebalk en voeg “^2” toe aan het einde van de formule om de waarde die wordt geretourneerd door de functie CORREL te kwadrateren. De voltooide formule zou er nu als volgt uit moeten zien:
=CORREL(B3:B12,C3:C12)^2
Druk op Enter.
Na het wijzigen van de formule, komt de waarde “R-kwadraat” nu overeen met de waarde die in de grafiek wordt weergegeven.
Stap drie: formules instellen om snel waarden te berekenen
Nu kunnen we deze waarden in eenvoudige formules gebruiken om de concentratie van die “onbekende” oplossing te bepalen of welke invoer we in de code moeten invoeren zodat de knikker een bepaalde afstand vliegt.
Met deze stappen worden de formules opgesteld die nodig zijn om een X-waarde of Y-waarde in te voeren en de overeenkomstige waarde te verkrijgen op basis van de kalibratiecurve.
De vergelijking van de best passende lijn heeft de vorm “Y-waarde = HELLING * X-waarde + INTERCEPT”, dus het oplossen van de “Y-waarde” wordt gedaan door de X-waarde en HELLING te vermenigvuldigen en vervolgens het toevoegen van de INTERCEPT.
Als voorbeeld plaatsen we nul als de X-waarde. De geretourneerde Y-waarde moet gelijk zijn aan het INTERCEPT van de best passende lijn. Het komt overeen, dus we weten dat de formule correct werkt.
Het oplossen van de X-waarde op basis van een Y-waarde wordt gedaan door het INTERCEPT af te trekken van de Y-waarde en het resultaat te delen door de HELLING:
X-value=(Y-value-INTERCEPT)/SLOPE
Als voorbeeld hebben we de INTERCEPT als Y-waarde gebruikt. De geretourneerde X-waarde moet gelijk zijn aan nul, maar de geretourneerde waarde is 3.14934E-06. De geretourneerde waarde is niet nul omdat we per ongeluk het INTERCEPT-resultaat hebben afgekapt bij het typen van de waarde. De formule werkt echter correct, omdat het resultaat van de formule 0.00000314934 is, wat in wezen nul is.
U kunt elke gewenste X-waarde invoeren in de eerste cel met dikke randen en Excel berekent automatisch de bijbehorende Y-waarde.
Als u een Y-waarde invoert in de tweede cel met dikke randen, krijgt u de bijbehorende X-waarde. Deze formule is wat je zou gebruiken om de concentratie van die oplossing te berekenen of welke input nodig is om de knikker over een bepaalde afstand te lanceren.
In dit geval geeft het instrument “5” aan, dus de kalibratie zou een concentratie van 4,94 suggereren of we willen dat de knikker vijf afstandseenheden aflegt, dus de kalibratie suggereert dat we 4,94 invoeren als de invoervariabele voor het programma dat de knikkerwerper bestuurt. We kunnen redelijk zeker zijn van deze resultaten vanwege de hoge R-kwadraatwaarde in dit voorbeeld.
