Excel har inbyggda funktioner som du kan använda för att visa dina kalibreringsdata och beräkna en rad som passar bäst. Detta kan vara till hjälp när du skriver en kemilabbrapport eller programmerar en korrigeringsfaktor i en utrustning.
I den här artikeln kommer vi att titta på hur du använder Excel för att skapa ett diagram, rita en linjär kalibreringskurva, visa kalibreringskurvans formel och sedan ställa in enkla formler med funktionerna SLOPE och INTERCEPT för att använda kalibreringsekvationen i Excel.
Innehållsförteckning
Vad är en kalibreringskurva och hur är Excel användbar när du skapar en?
För att utföra en kalibrering jämför du avläsningarna för en enhet (som temperaturen som en termometer visar) med kända värden som kallas standarder (som frys- och kokpunkter för vatten). Detta låter dig skapa en serie datapar som du sedan ska använda för att utveckla en kalibreringskurva.
En tvåpunktskalibrering av en termometer med vattens frys- och kokpunkt skulle ha två datapar: ett från när termometern placeras i isvatten (32°F eller 0°C) och ett i kokande vatten (212°F) eller 100°C). När du plottar de två dataparen som punkter och ritar en linje mellan dem (kalibreringskurvan), förutsatt att termometerns respons är linjär, kan du välja vilken punkt som helst på linjen som motsvarar värdet som termometern visar, och du kunde hitta motsvarande ”sanna” temperatur.
Så linjen fyller i huvudsak i informationen mellan de två kända punkterna för dig så att du kan vara någorlunda säker när du uppskattar den faktiska temperaturen när termometern visar 57,2 grader, men när du aldrig har mätt en ”standard” som motsvarar den läsningen.
Excel har funktioner som låter dig plotta dataparen grafiskt i ett diagram, lägga till en trendlinje (kalibreringskurva) och visa kalibreringskurvans ekvation på diagrammet. Detta är användbart för en visuell visning, men du kan också beräkna linjens formel med Excels SLOPE- och INTERCEPT-funktioner. När du anger dessa värden i enkla formler kommer du att automatiskt kunna beräkna det ”sanna” värdet baserat på alla mått.
Låt oss titta på ett exempel
För det här exemplet kommer vi att utveckla en kalibreringskurva från en serie på tio datapar, som vart och ett består av ett X-värde och ett Y-värde. X-värdena kommer att vara våra ”standarder”, och de kan representera allt från koncentrationen av en kemisk lösning som vi mäter med hjälp av ett vetenskapligt instrument till indatavariabeln för ett program som styr en marmorutskjutningsmaskin.
Y-värdena kommer att vara ”svaren” och de skulle representera avläsningen som instrumentet tillhandahåller när man mäter varje kemisk lösning eller det uppmätta avståndet för hur långt bort från utskjutningsrampen kulan landade med hjälp av varje ingångsvärde.
Efter att vi grafiskt har avbildat kalibreringskurvan kommer vi att använda funktionerna SLOPE och INTERCEPT för att beräkna kalibreringslinjens formel och bestämma koncentrationen av en ”okänd” kemisk lösning baserat på instrumentets avläsning eller bestämma vilken input vi ska ge programmet så att marmor landar ett visst avstånd från utskjutaren.
Steg ett: Skapa ditt diagram
Vårt enkla exempelkalkylblad består av två kolumner: X-Value och Y-Value.
Låt oss börja med att välja data som ska plottas i diagrammet.
Välj först kolumncellerna ”X-Value”.
Tryck nu på Ctrl-tangenten och klicka sedan på Y-Value-kolumncellerna.
Gå till fliken ”Infoga”.
Navigera till menyn ”Charts” och välj det första alternativet i rullgardinsmenyn ”Scatter”.
Välj serie genom att klicka på en av de blå punkterna. När vald, Excel skisserar punkterna kommer att beskrivas.
Högerklicka på en av punkterna och välj sedan alternativet ”Lägg till trendlinje”.
En rak linje kommer att visas på diagrammet.
På höger sida av skärmen visas menyn ”Format trendlinje”. Markera rutorna bredvid ”Visa ekvation på diagrammet” och ”Visa R-kvadratvärde på diagrammet.” R-kvadratvärdet är en statistik som talar om hur nära linjen passar data. Det bästa R-kvadratvärdet är 1 000, vilket innebär att varje datapunkt vidrör linjen. När skillnaderna mellan datapunkterna och linjen växer, sjunker r-kvadratvärdet, med 0,000 som det lägsta möjliga värdet.
Ekvationen och R-kvadratstatistiken för trendlinjen kommer att visas på diagrammet. Observera att korrelationen av data är mycket bra i vårt exempel, med ett R-kvadratvärde på 0,988.
Ekvationen har formen ”Y = Mx + B”, där M är lutningen och B är y-axelns skärning av den räta linjen.
Nu när kalibreringen är klar, låt oss arbeta med att anpassa diagrammet genom att redigera titeln och lägga till axeltitlar.
För att ändra diagramtiteln, klicka på den för att markera texten.
Skriv nu in en ny titel som beskriver diagrammet.
För att lägga till titlar till x-axeln och y-axeln, navigera först till Diagramverktyg > Design.
Navigera nu till Axeltitlar > Primär horisontell.
För att byta namn på axeltiteln, välj först texten och skriv sedan in en ny titel.
Gå nu till Axis Titlar > Primär vertikal.
En axeltitel visas.
Byt namn på den här titeln genom att markera texten och skriva in en ny titel.
Ditt diagram är nu färdigt.
Steg två: Beräkna linjeekvationen och R-kvadratstatistik
Låt oss nu beräkna linjeekvationen och R-kvadratstatistiken med hjälp av Excels inbyggda SLOPE-, INTERCEPT- och CORREL-funktioner.
Till vårt ark (på rad 14) har vi lagt till titlar för dessa tre funktioner. Vi kommer att utföra de faktiska beräkningarna i cellerna under dessa titlar.
Först kommer vi att beräkna SLOPE. Välj cell A15.
Navigera till Formler > Fler funktioner > Statistik > SLOPE.
I fältet ”Kända_xs”, välj eller skriv i X-Value-kolumncellerna. Ordningen på fälten ’Known_ys’ och ’Known_xs’ har betydelse i SLOPE-funktionen.
Klicka på ”OK”. Den slutliga formeln i formelfältet ska se ut så här:
=LUTNING(C3:C12;B3:B12)
Observera att värdet som returneras av SLOPE-funktionen i cell A15 matchar värdet som visas i diagrammet.
Välj sedan cell B15 och navigera sedan till Formler > Fler funktioner > Statistisk > INTERCEPT.
Välj eller skriv i X-Value-kolumncellerna för fältet ”Kända_xs”. Ordningen på fälten ’Known_ys’ och ’Known_xs’ har också betydelse i INTERCEPT-funktionen.
Klicka på ”OK”. Den slutliga formeln i formelfältet ska se ut så här:
=INTERCEPT(C3:C12;B3:B12)
Observera att värdet som returneras av funktionen INTERCEPT matchar y-avsnittet som visas i diagrammet.
Välj sedan cell C15 och navigera till Formler > Fler funktioner > Statistisk > CORREL.
Välj eller skriv in det andra av de två cellområdena för fältet ”Array2”.
Klicka på ”OK”. Formeln ska se ut så här i formelfältet:
=KORREL(B3:B12;C3:C12)
Observera att värdet som returneras av CORREL-funktionen inte matchar värdet ”r-kvadrat” i diagrammet. CORREL-funktionen returnerar ”R”, så vi måste kvadrera det för att beräkna ”R-kvadrat”.
Klicka inuti funktionsfältet och lägg till ”^2” i slutet av formeln för att kvadrera värdet som returneras av CORREL-funktionen. Den färdiga formeln ska nu se ut så här:
=KORREL(B3:B12;C3:C12)^2
Tryck enter.
Efter att ha ändrat formeln matchar nu ”R-kvadrat”-värdet det som visas i diagrammet.
Steg tre: Ställ in formler för att snabbt beräkna värden
Nu kan vi använda dessa värden i enkla formler för att bestämma koncentrationen av den ”okända” lösningen eller vilken ingång vi ska ange i koden så att kulan flyger en viss sträcka.
Dessa steg kommer att ställa in de formler som krävs för att du ska kunna ange ett X-värde eller ett Y-värde och få motsvarande värde baserat på kalibreringskurvan.
Ekvationen för raden av bästa passform har formen ”Y-värde = SLOPE * X-värde + INTERCEPT”, så att lösa för ”Y-värdet” görs genom att multiplicera X-värdet och SLOPE och sedan lägga till INTERCEPT.
Som ett exempel lägger vi in noll som X-värde. Y-värdet som returneras bör vara lika med INTERCEPTEN för linjen med bästa passform. Det matchar, så vi vet att formeln fungerar korrekt.
Att lösa X-värdet baserat på ett Y-värde görs genom att subtrahera INTERCEPT från Y-värdet och dividera resultatet med SLOPE:
X-value=(Y-value-INTERCEPT)/SLOPE
Som ett exempel använde vi INTERCEPT som ett Y-värde. X-värdet som returneras bör vara lika med noll, men värdet som returneras är 3.14934E-06. Det returnerade värdet är inte noll eftersom vi oavsiktligt trunkerade INTERCEPT-resultatet när vi skrev värdet. Formeln fungerar dock korrekt eftersom resultatet av formeln är 0,00000314934, vilket i princip är noll.
Du kan ange vilket X-värde du vill i den första tjocka cellen och Excel kommer att beräkna motsvarande Y-värde automatiskt.
Om du anger valfritt Y-värde i den andra tjockkantade cellen får du motsvarande X-värde. Denna formel är vad du skulle använda för att beräkna koncentrationen av den lösningen eller vilken input som behövs för att lansera kulan ett visst avstånd.
I det här fallet läser instrumentet ”5” så kalibreringen skulle föreslå en koncentration på 4,94 eller så vill vi att kulan ska färdas fem enheter av avstånd, så kalibreringen föreslår att vi anger 4,94 som indatavariabel för programmet som styr marmorsändaren. Vi kan vara rimligt säkra på dessa resultat på grund av det höga R-kvadratvärdet i det här exemplet.