Excel har indbyggede funktioner, som du kan bruge til at vise dine kalibreringsdata og beregne en line-of-best-fit. Dette kan være nyttigt, når du skriver en kemi-laboratorierapport eller programmerer en korrektionsfaktor i et stykke udstyr.
I denne artikel vil vi se på, hvordan du bruger Excel til at oprette et diagram, plotte en lineær kalibreringskurve, vise kalibreringskurvens formel og derefter opsætte simple formler med funktionerne SLOPE og INTERCEPT for at bruge kalibreringsligningen i Excel.
Indholdsfortegnelse
Hvad er en kalibreringskurve, og hvordan er Excel nyttig, når du opretter en?
For at udføre en kalibrering sammenligner du aflæsningerne af en enhed (såsom temperaturen, som et termometer viser) med kendte værdier kaldet standarder (såsom fryse- og kogepunkter for vand). Dette lader dig oprette en række datapar, som du derefter skal bruge til at udvikle en kalibreringskurve.
En to-punkts kalibrering af et termometer ved brug af vands fryse- og kogepunkter ville have to datapar: et fra når termometeret placeres i isvand (32°F eller 0°C) og et i kogende vand (212°F) eller 100°C). Når du plotter disse to datapar som punkter og tegner en linje mellem dem (kalibreringskurven), og hvis du antager, at termometrets respons er lineær, kan du vælge et hvilket som helst punkt på linjen, der svarer til den værdi, som termometeret viser, og du kunne finde den tilsvarende “sande” temperatur.
Så linjen udfylder i det væsentlige informationen mellem de to kendte punkter for dig, så du kan være nogenlunde sikker, når du estimerer den faktiske temperatur, når termometeret viser 57,2 grader, men når du aldrig har målt en “standard”, der svarer til den læsning.
Excel har funktioner, der giver dig mulighed for at plotte dataparrene grafisk i et diagram, tilføje en trendlinje (kalibreringskurve) og vise kalibreringskurvens ligning på diagrammet. Dette er nyttigt til en visuel visning, men du kan også beregne formlen for linjen ved hjælp af Excels SLOPE- og INTERCEPT-funktioner. Når du indtaster disse værdier i simple formler, vil du automatisk kunne beregne den “sande” værdi baseret på enhver måling.
Lad os se på et eksempel
Til dette eksempel vil vi udvikle en kalibreringskurve fra en serie på ti datapar, der hver består af en X-værdi og en Y-værdi. X-værdierne vil være vores “standarder”, og de kan repræsentere alt fra koncentrationen af en kemisk opløsning, vi måler ved hjælp af et videnskabeligt instrument, til input-variablen i et program, der styrer en marmoraffyringsmaskine.
Y-værdierne vil være “svarene”, og de vil repræsentere aflæsningen af instrumentet, når man måler hver kemisk opløsning eller den målte afstand af, hvor langt væk fra løfteraketten, marmoren landede ved hjælp af hver inputværdi.
Efter at vi grafisk har afbildet kalibreringskurven, vil vi bruge funktionerne SLOPE og INTERCEPT til at beregne kalibreringslinjens formel og bestemme koncentrationen af en “ukendt” kemisk opløsning baseret på instrumentets aflæsning eller beslutte, hvilket input vi skal give programmet, således at marmor lander en vis afstand fra løfteraketten.
Trin et: Opret dit diagram
Vores enkle eksempelregneark består af to kolonner: X-Value og Y-Value.
Lad os starte med at vælge de data, der skal plottes i diagrammet.
Vælg først kolonnecellerne ‘X-Value’.
Tryk nu på Ctrl-tasten og klik derefter på Y-værdi kolonnecellerne.
Gå til fanen “Indsæt”.
Naviger til menuen “Charts”, og vælg den første mulighed i rullemenuen “Scatter”.
Vælg serien ved at klikke på et af de blå punkter. Når det er valgt, skitserer Excel, punkterne vil blive skitseret.
Højreklik på et af punkterne, og vælg derefter indstillingen “Tilføj trendlinje”.
En lige linje vises på diagrammet.
I højre side af skærmen vises menuen “Format Trendline”. Marker afkrydsningsfelterne ud for “Vis ligning på diagrammet” og “Vis R-kvadratværdi på diagrammet.” R-kvadratværdien er en statistik, der fortæller dig, hvor tæt linjen passer til dataene. Den bedste R-kvadratværdi er 1.000, hvilket betyder, at hvert datapunkt rører linjen. Efterhånden som forskellene mellem datapunkterne og linjen vokser, falder r-kvadratværdien, hvor 0,000 er den lavest mulige værdi.
Ligningen og R-kvadrat-statistikken for trendlinjen vises på diagrammet. Bemærk, at korrelationen af dataene er meget god i vores eksempel, med en R-kvadratværdi på 0,988.
Ligningen er på formen “Y = Mx + B”, hvor M er hældningen og B er y-aksens skæringspunkt for den rette linje.
Nu hvor kalibreringen er færdig, lad os arbejde på at tilpasse diagrammet ved at redigere titlen og tilføje aksetitler.
For at ændre diagramtitlen skal du klikke på den for at vælge teksten.
Indtast nu en ny titel, der beskriver diagrammet.
For at tilføje titler til x-aksen og y-aksen skal du først navigere til Diagramværktøjer > Design.
Naviger nu til Aksetitler > Primær vandret.
For at omdøbe aksetitlen skal du først vælge teksten og derefter indtaste en ny titel.
Gå nu til Aksetitler > Primær lodret.
En aksetitel vises.
Omdøb denne titel ved at markere teksten og indtaste en ny titel.
Dit diagram er nu færdigt.
Trin to: Beregn linjeligningen og R-kvadratstatistik
Lad os nu beregne linjeligningen og R-kvadrat-statistikken ved hjælp af Excels indbyggede SLOPE-, INTERCEPT- og CORREL-funktioner.
Til vores ark (i række 14) har vi tilføjet titler til disse tre funktioner. Vi udfører de faktiske beregninger i cellerne under disse titler.
Først vil vi beregne SLOPE. Vælg celle A15.
Naviger til Formler > Flere funktioner > Statistisk > SLOPE.
I feltet “Kendte_xs” skal du vælge eller skrive i X-Value-kolonnens celler. Rækkefølgen af felterne ‘Kendte_ys’ og ‘Kendte_xs’ har betydning i SLOPE-funktionen.
Klik på “OK”. Den endelige formel i formellinjen skulle se sådan ud:
=HÆLDNING(C3:C12;B3:B12)
Bemærk, at den værdi, der returneres af SLOPE-funktionen i celle A15, svarer til den værdi, der vises på diagrammet.
Vælg derefter celle B15 og naviger derefter til Formler > Flere funktioner > Statistisk > AFSNIT.
Vælg eller skriv i X-Value-kolonnecellerne for feltet “Kendte_xs”. Rækkefølgen af felterne ‘Kendt_ys’ og ‘Kendte_xs’ har også betydning for funktionen AFSKRIVNING.
Klik på “OK”. Den endelige formel i formellinjen skulle se sådan ud:
=AFSKÆRING(C3:C12;B3:B12)
Bemærk, at den værdi, der returneres af funktionen AFSNITNING, svarer til y-skæringspunktet, der vises i diagrammet.
Vælg derefter celle C15 og naviger til Formler > Flere funktioner > Statistisk > CORREL.
Vælg eller indtast det andet af de to celleområder for feltet “Array2”.
Klik på “OK”. Formlen skal se sådan ud i formellinjen:
=CORREL(B3:B12;C3:C12)
Bemærk, at den værdi, der returneres af CORREL-funktionen, ikke stemmer overens med “r-kvadrat”-værdien på diagrammet. CORREL-funktionen returnerer “R”, så vi skal kvadrere det for at beregne “R-kvadrat”.
Klik inde i funktionslinjen og tilføj “^2” til slutningen af formlen for at kvadrere den værdi, der returneres af CORREL-funktionen. Den færdige formel skulle nu se sådan ud:
=CORREL(B3:B12;C3:C12)^2
Tryk på Enter.
Efter at have ændret formlen, svarer “R-kvadrat”-værdien nu til den, der vises i diagrammet.
Trin tre: Opsæt formler til hurtig beregning af værdier
Nu kan vi bruge disse værdier i simple formler til at bestemme koncentrationen af den “ukendte” løsning eller hvilket input vi skal indtaste i koden, så kuglen flyver en vis afstand.
Disse trin opsætter de formler, der kræves for, at du kan indtaste en X-værdi eller en Y-værdi og få den tilsvarende værdi baseret på kalibreringskurven.
Ligningen for den bedste tilpasningslinje er på formen “Y-værdi = SLOPE * X-value + INTERCEPT”, så løsning af “Y-værdien” udføres ved at gange X-værdien og SLOPE og derefter tilføjelse af INTERCEPT.
Som et eksempel sætter vi nul som X-værdi. Den returnerede Y-værdi skal være lig med AFSNITTEN af linjen med bedst tilpasning. Det matcher, så vi ved, at formlen fungerer korrekt.
Løsning af X-værdien baseret på en Y-værdi udføres ved at trække AFSNIT fra Y-værdien og dividere resultatet med HÆNDINGEN:
X-value=(Y-value-INTERCEPT)/SLOPE
Som et eksempel brugte vi INTERCEPT som en Y-værdi. Den returnerede X-værdi skal være lig med nul, men den returnerede værdi er 3.14934E-06. Den returnerede værdi er ikke nul, fordi vi utilsigtet trunkerede INTERCEPT-resultatet, da vi indtastede værdien. Formlen fungerer dog korrekt, fordi resultatet af formlen er 0,00000314934, hvilket i det væsentlige er nul.
Du kan indtaste enhver X-værdi, du ønsker, i den første celle med tyk kant, og Excel vil automatisk beregne den tilsvarende Y-værdi.
Indtastning af en hvilken som helst Y-værdi i den anden celle med tyk kant vil give den tilsvarende X-værdi. Denne formel er, hvad du vil bruge til at beregne koncentrationen af den opløsning, eller hvilket input, der er nødvendigt for at sende kuglen en vis afstand.
I dette tilfælde læser instrumentet “5”, så kalibreringen foreslår en koncentration på 4,94, eller vi ønsker, at marmoren skal rejse fem enheder af afstand, så kalibreringen foreslår, at vi indtaster 4,94 som inputvariabel for programmet, der styrer marmorudskyderen. Vi kan være rimeligt sikre på disse resultater på grund af den høje R-kvadratværdi i dette eksempel.