Како направити криву линеарне калибрације у Екцелу

Excel nudi ugrađene funkcije koje vam omogućavaju da vizualizujete podatke kalibracije i izračunate liniju koja najbolje odgovara. Ovo je izuzetno korisno prilikom sastavljanja izveštaja iz hemijske laboratorije ili programiranja korekcionog faktora u opremi.

U ovom tekstu, detaljno ćemo istražiti kako koristiti Excel za kreiranje grafikona, iscrtavanje linearne kalibracione krive, prikaz formule kalibracione krive i primenu jednostavnih formula uz pomoć funkcija SLOPE i INTERCEPT. Na taj način ćemo moći da iskoristimo jednačinu kalibracije direktno u Excelu.

Šta je kalibraciona kriva i kako Excel pomaže pri njenom stvaranju?

Kalibracija se sprovodi poređenjem očitavanja uređaja (kao što je temperatura prikazana na termometru) sa poznatim vrednostima, poznatim kao standardi (na primer, tačke mržnjenja i ključanja vode). Ovaj proces omogućava formiranje niza parova podataka koji se zatim koriste za razvoj kalibracione krive.

Kalibracija termometra u dve tačke, koristeći tačke mržnjenja i ključanja vode, rezultiraće sa dva para podataka: jedan kada se termometar stavi u ledenu vodu (0°C) i drugi u kipuću vodu (100°C). Kada se ova dva para podataka predstave kao tačke i povuče se linija između njih (kalibraciona kriva), uz pretpostavku linearne reakcije termometra, moguće je odabrati bilo koju tačku na liniji koja odgovara očitanoj vrednosti termometra. Tako se može proceniti odgovarajuća „prava“ temperatura.

Drugim rečima, linija interpolira informacije između dve poznate tačke, omogućavajući pouzdanu procenu stvarne temperature kada termometar očitava, na primer, 14°C, čak i ako taj specifičan „standard“ nikada nije direktno izmeren.

Excel nudi funkcionalnosti za grafički prikaz parova podataka, dodavanje linije trenda (kalibracione krive) i prikazivanje jednačine kalibracione krive na grafikonu. Ovo je korisno za vizualizaciju, ali takođe omogućava i proračun formule linije koristeći Excelove funkcije SLOPE i INTERCEPT. Unošenjem ovih vrednosti u jednostavne formule, možete automatski izračunati „pravu“ vrednost na osnovu bilo kog merenja.

Primer praktične primene

U ovom primeru, razvićemo kalibracionu krivu koristeći seriju od deset parova podataka, gde svaki par čine X-vrednost i Y-vrednost. X-vrednosti predstavljaju naše „standarde“ i mogu biti bilo šta, od koncentracije hemijskog rastvora merene naučnim instrumentom do ulazne varijable programa koji upravlja mašinom za lansiranje klikera.

Y-vrednosti predstavljaju „odgovore“, odnosno očitavanja koja instrument daje prilikom merenja svakog hemijskog rastvora, ili izmerenu udaljenost koju je kliker prešao od lansera, koristeći svaku ulaznu vrednost.

Nakon grafičkog predstavljanja kalibracione krive, koristićemo funkcije SLOPE i INTERCEPT kako bismo izračunali formulu kalibracione linije. To će nam omogućiti da odredimo koncentraciju „nepoznatog“ hemijskog rastvora na osnovu očitavanja instrumenta, ili da utvrdimo koji ulaz treba da damo programu kako bi kliker sleteo na željenu udaljenost od lansera.

Prvi korak: Kreiranje grafikona

Naš primer tabele se sastoji od dve kolone: ‘X-Value’ i ‘Y-Value’.

Započinjemo selektovanjem podataka za iscrtavanje na grafikonu.

Prvo, izaberite ćelije u koloni ‘X-Value’.

Sada, pritisnite taster Ctrl i, držeći ga pritisnutim, kliknite na ćelije u koloni ‘Y-Value’.

Idite na karticu „Umetanje“.

U meniju „Grafikoni“, izaberite prvu opciju u padajućem meniju „Raspršeni“.

Pojaviće se grafikon sa tačkama podataka iz dve kolone.

Kliknite na jednu od plavih tačaka da biste selektovali seriju. Kada je serija selektovana, Excel će označiti tačke koje će biti iscrtane.

Desnim klikom miša na jednu od tačaka, izaberite opciju „Dodaj liniju trenda“.

Na grafikonu će se pojaviti prava linija.

Sa desne strane ekrana pojaviće se meni „Format Trendline“. Označite polja pored „Prikaži jednačinu na grafikonu“ i „Prikaži R-kvadrat vrednost na grafikonu“. R-kvadrat vrednost je statistički podatak koji pokazuje koliko dobro linija odgovara podacima. Najbolja vrednost R-kvadrata je 1.000, što znači da svaka tačka podataka leži tačno na liniji. Sa povećanjem razlika između tačaka podataka i prave linije, vrednost R-kvadrata opada, a najniža moguća vrednost je 0.000.

Jednačina i R-kvadrat statistika linije trenda će se pojaviti na grafikonu. Uočite da je u našem primeru korelacija podataka vrlo dobra, sa R-kvadrat vrednošću od 0.988.

Jednačina je u formi „Y = Mx + B“, gde je M nagib, a B presek y-ose prave linije.

Sada, kada je kalibracija završena, fokusiraćemo se na prilagođavanje grafikona uređivanjem naslova i dodavanjem naslova osa.

Da biste promenili naslov grafikona, kliknite na njega i selektujte tekst.

Unesite novi naslov koji opisuje grafikon.

Da biste dodali naslove x-osi i y-osi, prvo idite na „Alatke za grafikone“ > „Dizajn“.

Kliknite na padajući meni „Dodaj element grafikona“.

Zatim idite na „Naslovi osa“ > „Primarno horizontalno“.

Pojaviće se naslov ose.

Da biste preimenovali naslov ose, selektujte tekst i unesite novi naslov.

Sada idite na „Naslovi osa“ > „Primarna vertikala“.

Pojaviće se naslov ose.

Preimenujte ovaj naslov tako što ćete selektovati tekst i ukucati novi naslov.

Vaš grafikon je sada završen.

Drugi korak: Izračunavanje jednačine prave i statistike R-kvadrata

Sada ćemo izračunati jednačinu linije i R-kvadrat statistiku koristeći ugrađene funkcije SLOPE, INTERCEPT i CORREL u Excelu.

Na naš list (u redu 14) smo dodali naslove za te tri funkcije. Stvarne proračune ćemo izvršiti u ćelijama ispod tih naslova.

Prvo ćemo izračunati NAGIB. Izaberite ćeliju A15.

Idite na „Formule“ > „Više funkcija“ > „Statistički“ > „SLOPE“.

Pojavljuje se prozor sa argumentima funkcije. U polju „Known_ys“ izaberite ili ukucajte ćelije kolone „Y-Value“.

U polju „Known_xs“ izaberite ili ukucajte ćelije kolone „X-Value“. Redosled polja ‘Known_ys’ i ‘Known_xs’ je važan u funkciji SLOPE.

Kliknite na „OK“. Konačna formula u traci formule treba da izgleda ovako:

=SLOPE(C3:C12,B3:B12)

Uočite da se vrednost koju vraća funkcija SLOPE u ćeliji A15 poklapa sa vrednošću prikazanom na grafikonu.

Zatim izaberite ćeliju B15, pa idite na „Formule“ > „Više funkcija“ > „Statistički“ > „INTERCEPT“.

Pojavljuje se prozor sa argumentima funkcije. Izaberite ili unesite ćelije iz kolone „Y-Value“ u polje „Known_ys“.

Izaberite ili unesite ćelije kolone „X-Value“ u polje „Known_xs“. Redosled polja ‘Known_ys’ i ‘Known_xs’ je takođe važan u funkciji INTERCEPT.

Kliknite na „OK“. Konačna formula u traci formule treba da izgleda ovako:

=INTERCEPT(C3:C12,B3:B12)

Uočite da se vrednost koju vraća funkcija INTERCEPT poklapa sa presekom y-ose prikazanom na grafikonu.

Zatim izaberite ćeliju C15, pa idite na „Formule“ > „Više funkcija“ > „Statistički“ > „CORREL“.

Pojavljuje se prozor sa argumentima funkcije. Izaberite ili unesite bilo koji od dva raspona ćelija u polje „Array1“. Za razliku od SLOPE i INTERCEPT, redosled ne utiče na rezultat funkcije CORREL.

Izaberite ili unesite drugi od dva raspona ćelija u polje „Array2“.

Kliknite na „OK“. Formula bi u traci formule trebalo da izgleda ovako:

=CORREL(B3:B12,C3:C12)

Uočite da vrednost koju vraća funkcija CORREL ne odgovara vrednosti „R-kvadrat“ na grafikonu. Funkcija CORREL vraća „R“, tako da moramo da ga kvadriramo da bismo izračunali „R-kvadrat“.

Kliknite unutar trake sa funkcijama i dodajte „^2“ na kraj formule da biste kvadrirali vrednost koju vraća funkcija CORREL. Završena formula bi sada trebalo da izgleda ovako:

=CORREL(B3:B12,C3:C12)^2

Pritisnite Enter.

Nakon izmene formule, vrednost „R-kvadrat“ sada odgovara onoj prikazanoj na grafikonu.

Treći korak: Podešavanje formula za brzo izračunavanje vrednosti

Sada možemo koristiti ove vrednosti u jednostavnim formulama da odredimo koncentraciju tog „nepoznatog“ rastvora ili koji unos treba da unesemo u kod da bi kliker preleteo određenu udaljenost.

Ovi koraci će postaviti formule neophodne da biste mogli da unesete X-vrednost ili Y-vrednost i dobijete odgovarajuću vrednost na osnovu kalibracione krive.

Jednačina linije najboljeg uklapanja je u obliku „Y-vrednost = NAGIB * X-vrednost + PRESEK“, tako da se rešavanje za „Y-vrednost“ vrši množenjem X-vrednosti i NAGIBA, a zatim dodavanjem INTERCEPT.

Kao primer, uneli smo nulu kao X-vrednost. Vraćena Y-vrednost treba da bude jednaka INTERCEPT-u linije najboljeg uklapanja. Poklapa se, tako da znamo da formula radi ispravno.

Rešavanje za X-vrednost na osnovu Y-vrednosti se vrši oduzimanjem INTERCEPT od Y-vrednosti i deljenjem rezultata nagibom:

X-value=(Y-value-INTERCEPT)/SLOPE

Kao primer, koristili smo INTERCEPT kao Y-vrednost. Vraćena vrednost X treba da bude jednaka nuli, ali vraćena vrednost je 3.14934E-06. Vraćena vrednost nije nula jer smo nehotice skratili rezultat INTERCEPT prilikom kucanja vrednosti. Formula ipak radi ispravno, jer je rezultat formule 0.00000314934, što je u suštini nula.

Možete da unesete bilo koju X-vrednost koju želite u prvu ćeliju sa debelim ivicama, i Excel će automatski izračunati odgovarajuću Y-vrednost.

Unošenje bilo koje Y-vrednosti u drugu ćeliju sa debelim ivicama će dati odgovarajuću X-vrednost. Ovu formulu biste koristili da izračunate koncentraciju datog rastvora, ili koji unos je potreban da se kliker lansira na željenu udaljenost.

U ovom slučaju, instrument očitava „5“, tako da bi kalibracija ukazala na koncentraciju od 4.94. Ili, ako želimo da kliker pređe pet jedinica udaljenosti, kalibracija sugeriše da unesemo 4.94 kao ulaznu varijablu za program koji kontroliše lanser klikera. Možemo biti prilično sigurni u ove rezultate, zahvaljujući visokoj R-kvadrat vrednosti u ovom primeru.