Excel har innebygde funksjoner som du kan bruke til å vise kalibreringsdataene dine og beregne en best-fit-linje. Dette kan være nyttig når du skal skrive en laboratorierapport for kjemi eller programmere en korreksjonsfaktor i et utstyr.
I denne artikkelen skal vi se på hvordan du bruker Excel til å lage et diagram, plotte en lineær kalibreringskurve, vise kalibreringskurvens formel og deretter sette opp enkle formler med SLOPE- og INTERCEPT-funksjonene for å bruke kalibreringsligningen i Excel.
Innholdsfortegnelse
Hva er en kalibreringskurve og hvordan er Excel nyttig når du lager en?
For å utføre en kalibrering, sammenligner du avlesningene til en enhet (som temperaturen som et termometer viser) med kjente verdier som kalles standarder (som fryse- og kokepunktene til vann). Dette lar deg lage en serie datapar som du deretter vil bruke til å utvikle en kalibreringskurve.
En topunktskalibrering av et termometer ved bruk av fryse- og kokepunktene til vann vil ha to datapar: ett fra når termometeret plasseres i isvann (32°F eller 0°C) og ett i kokende vann (212°F) eller 100°C). Når du plotter disse to dataparene som punkter og tegner en linje mellom dem (kalibreringskurven), forutsatt at responsen til termometeret er lineær, kan du velge et hvilket som helst punkt på linjen som tilsvarer verdien termometeret viser, og du kunne finne den tilsvarende «sanne» temperaturen.
Så linjen fyller i hovedsak ut informasjonen mellom de to kjente punktene for deg slik at du kan være rimelig sikker når du estimerer den faktiske temperaturen når termometeret viser 57,2 grader, men når du aldri har målt en «standard» som tilsvarer den lesningen.
Excel har funksjoner som lar deg plotte dataparene grafisk i et diagram, legge til en trendlinje (kalibreringskurve) og vise kalibreringskurvens ligning på diagrammet. Dette er nyttig for en visuell visning, men du kan også beregne formelen til linjen ved å bruke Excels SLOPE- og INTERCEPT-funksjoner. Når du legger inn disse verdiene i enkle formler, vil du automatisk kunne beregne den «sanne» verdien basert på enhver måling.
La oss se på et eksempel
For dette eksemplet vil vi utvikle en kalibreringskurve fra en serie på ti datapar, som hver består av en X-verdi og en Y-verdi. X-verdiene vil være våre «standarder», og de kan representere alt fra konsentrasjonen av en kjemisk løsning vi måler ved hjelp av et vitenskapelig instrument til inngangsvariabelen til et program som styrer en marmorutskytningsmaskin.
Y-verdiene vil være «svarene», og de vil representere avlesningen instrumentet leverte ved måling av hver kjemisk løsning eller den målte avstanden til hvor langt unna utskytningsrampen kulen landet ved å bruke hver inngangsverdi.
Etter at vi grafisk har avbildet kalibreringskurven, vil vi bruke funksjonene SLOPE og INTERCEPT til å beregne kalibreringslinjens formel og bestemme konsentrasjonen av en «ukjent» kjemisk løsning basert på instrumentets avlesning eller bestemme hvilken inngang vi skal gi programmet slik at marmor lander en viss avstand fra utskyteren.
Trinn én: Lag diagrammet ditt
Vårt enkle eksempelregneark består av to kolonner: X-Value og Y-Value.
La oss starte med å velge dataene som skal plottes i diagrammet.
Velg først «X-Value»-kolonnecellene.
Trykk nå Ctrl-tasten og klikk deretter Y-verdi-kolonnecellene.
Gå til fanen «Sett inn».
Naviger til «Charts»-menyen og velg det første alternativet i «Scatter»-rullegardinmenyen.
Velg serien ved å klikke på et av de blå punktene. Når det er valgt, skisserer Excel punktene som vil bli skissert.
Høyreklikk på ett av punktene og velg deretter alternativet «Legg til trendlinje».
En rett linje vil vises på diagrammet.
På høyre side av skjermen vil «Format Trendline»-menyen vises. Merk av i boksene ved siden av «Vis ligning på diagrammet» og «Vis R-kvadratverdi på diagrammet.» R-kvadratverdien er en statistikk som forteller deg hvor tett linjen passer til dataene. Den beste R-kvadratverdien er 1.000, som betyr at hvert datapunkt berører linjen. Etter hvert som forskjellene mellom datapunktene og linjen vokser, synker r-kvadratverdien, med 0,000 som den laveste mulige verdien.
Ligningen og R-kvadrert statistikk for trendlinjen vil vises på diagrammet. Merk at korrelasjonen til dataene er veldig god i vårt eksempel, med en R-kvadratverdi på 0,988.
Ligningen er på formen «Y = Mx + B,» der M er helningen og B er y-aksens avskjæring av den rette linjen.
Nå som kalibreringen er fullført, la oss jobbe med å tilpasse diagrammet ved å redigere tittelen og legge til aksetitler.
For å endre diagramtittelen, klikk på den for å velge teksten.
Skriv nå inn en ny tittel som beskriver diagrammet.
For å legge til titler til x-aksen og y-aksen, naviger først til Kartverktøy > Design.
Naviger nå til Aksetitler > Primær horisontal.
For å gi nytt navn til aksetittelen, velg først teksten og skriv deretter inn en ny tittel.
Gå nå til Aksetitler > Primær vertikal.
En aksetittel vises.
Gi nytt navn til denne tittelen ved å velge teksten og skrive inn en ny tittel.
Diagrammet ditt er nå komplett.
Trinn to: Beregn linjeligningen og R-kvadrert statistikk
La oss nå beregne linjeligningen og R-kvadratstatistikken ved å bruke Excels innebygde SLOPE-, INTERCEPT- og CORREL-funksjoner.
Til arket vårt (på rad 14) har vi lagt til titler for disse tre funksjonene. Vi vil utføre de faktiske beregningene i cellene under disse titlene.
Først skal vi beregne SLOPE. Velg celle A15.
Naviger til Formler > Flere funksjoner > Statistisk > SLOPE.
I feltet «Kjente_xs» velger du eller skriver inn X-verdi-kolonnecellene. Rekkefølgen på feltene «Kjente_ys» og «Kjente_xs» har betydning i SLOPE-funksjonen.
Klikk «OK.» Den endelige formelen i formellinjen skal se slik ut:
=HELLING(C3:C12;B3:B12)
Merk at verdien returnert av SLOPE-funksjonen i celle A15 samsvarer med verdien som vises på diagrammet.
Velg deretter celle B15 og naviger deretter til Formler > Flere funksjoner > Statistisk > INTERCEPT.
Velg eller skriv inn X-Value-kolonnecellene for «Kjente_xs»-feltet. Rekkefølgen på feltene «Kjente_ys» og «Kjente_xs» har også betydning i AVSkjæringsfunksjonen.
Klikk «OK.» Den endelige formelen i formellinjen skal se slik ut:
=SKJØRING(C3:C12;B3:B12)
Merk at verdien returnert av AVSkjæringsfunksjonen samsvarer med y-skjæringspunktet som vises i diagrammet.
Velg deretter celle C15 og naviger til Formler > Flere funksjoner > Statistisk > CORREL.
Velg eller skriv inn det andre av de to celleområdene for «Array2»-feltet.
Klikk «OK.» Formelen skal se slik ut i formellinjen:
=KORREL(B3:B12;C3:C12)
Merk at verdien returnert av CORREL-funksjonen ikke samsvarer med «r-kvadrat»-verdien på diagrammet. CORREL-funksjonen returnerer «R», så vi må kvadrere den for å beregne «R-kvadrert.»
Klikk inne i funksjonslinjen og legg til «^2» på slutten av formelen for å kvadrere verdien returnert av CORREL-funksjonen. Den fullførte formelen skal nå se slik ut:
=KORREL(B3:B12;C3:C12)^2
Trykk enter.
Etter å ha endret formelen, samsvarer nå «R-kvadrat»-verdien med den som vises i diagrammet.
Trinn tre: Sett opp formler for rask beregning av verdier
Nå kan vi bruke disse verdiene i enkle formler for å bestemme konsentrasjonen av den «ukjente» løsningen eller hvilken inngang vi skal legge inn i koden slik at klinkekulen flyr en viss avstand.
Disse trinnene vil sette opp formlene som kreves for at du skal kunne legge inn en X-verdi eller en Y-verdi og få den tilsvarende verdien basert på kalibreringskurven.
Ligningen for linjen med best-tilpasning er i formen «Y-verdi = SLOPE * X-verdi + INTERCEPT,» så å løse for «Y-verdien» gjøres ved å multiplisere X-verdien og SLOPE og deretter legger til INTERCEPT.
Som et eksempel setter vi null som X-verdi. Y-verdien som returneres skal være lik AVSNITTET til linjen med best tilpasning. Det samsvarer, så vi vet at formelen fungerer som den skal.
Å løse for X-verdien basert på en Y-verdi gjøres ved å trekke AVSNITTET fra Y-verdien og dele resultatet med SLOPE:
X-value=(Y-value-INTERCEPT)/SLOPE
Som et eksempel brukte vi INTERCEPT som en Y-verdi. X-verdien som returneres skal være lik null, men verdien som returneres er 3.14934E-06. Verdien som returneres er ikke null fordi vi utilsiktet trunkerte INTERCEPT-resultatet da vi skrev inn verdien. Formelen fungerer riktignok, fordi resultatet av formelen er 0,00000314934, som i hovedsak er null.
Du kan legge inn hvilken som helst X-verdi du ønsker i den første cellen med tykke kantlinjer, og Excel vil automatisk beregne den tilsvarende Y-verdien.
Hvis du legger inn en hvilken som helst Y-verdi i den andre cellen med tykke kantlinjer, får du den tilsvarende X-verdien. Denne formelen er hva du vil bruke for å beregne konsentrasjonen av den løsningen eller hvilken input som trengs for å lansere klinkekulen en viss avstand.
I dette tilfellet viser instrumentet «5», slik at kalibreringen antyder en konsentrasjon på 4,94, eller vi vil at klinken skal reise fem avstandsenheter, så kalibreringen foreslår at vi legger inn 4,94 som inngangsvariabel for programmet som kontrollerer marmorutskytningen. Vi kan være rimelig sikre på disse resultatene på grunn av den høye R-kvadratverdien i dette eksemplet.