Excel oferă instrumente integrate care vă permit să vizualizați date de calibrare și să determinați o linie de potrivire optimă. Această capacitate este extrem de utilă în diverse contexte, cum ar fi generarea rapoartelor de laborator de chimie sau configurarea unui factor de corecție pentru un anumit echipament.
În acest ghid, vom explora pașii pentru a utiliza Excel în crearea unui grafic, trasarea unei curbe de calibrare liniare și afișarea formulei acestei curbe. Vom analiza, de asemenea, cum să folosim funcțiile PANTĂ și INTERCEPT pentru a implementa ecuația de calibrare în Excel.
Ce reprezintă o curbă de calibrare și cum ne ajută Excel în crearea ei?
Procesul de calibrare presupune compararea datelor citite de un instrument (cum ar fi temperatura indicată de un termometru) cu valorile de referință cunoscute, denumite standarde (de exemplu, punctele de îngheț și fierbere ale apei). Acest proces ne permite să obținem seturi de date care vor fi ulterior utilizate la elaborarea curbei de calibrare.
De exemplu, calibrarea unui termometru în două puncte, folosind punctele de îngheț și fierbere ale apei, generează două perechi de date: prima, atunci când termometrul este introdus în apă cu gheață (0°C sau 32°F) și a doua, în apă clocotită (100°C sau 212°F). Dacă trasăm aceste perechi de date ca puncte și le conectăm cu o linie (curba de calibrare), putem presupune un răspuns liniar al termometrului. Astfel, putem alege orice punct de pe această linie corespunzător citirii termometrului și determina temperatura „adevărată” aferentă.
Practic, linia completează informațiile lipsă dintre cele două puncte de referință, oferind o estimare rezonabilă a temperaturii reale chiar și atunci când termometrul indică valori care nu corespund standardelor măsurate anterior, cum ar fi 57,2 grade.
Excel oferă funcții pentru reprezentarea grafică a perechilor de date, adăugarea unei linii de tendință (curba de calibrare) și afișarea ecuației acestei curbe pe grafic. Această reprezentare vizuală este utilă, dar putem calcula și formula liniei folosind funcțiile SLOPE și INTERCEPT ale Excel. Prin introducerea acestor valori în formule simple, putem determina automat valoarea corectă pe baza oricărei măsurători.
Un exemplu concret
Pentru a ilustra acest proces, vom crea o curbă de calibrare dintr-un set de zece perechi de date, fiecare având o valoare X și una Y. Valorile X vor reprezenta standardele noastre, care ar putea fi concentrația unei soluții chimice măsurate cu un instrument, sau variabila de intrare a unui program ce controlează o mașină de lansat bile.
Valorile Y vor reprezenta răspunsurile sau citirile obținute la măsurarea fiecărei soluții chimice, sau distanța la care bilele au aterizat la diferite valori de intrare.
După ce trasăm curba de calibrare, vom utiliza funcțiile SLOPE și INTERCEPT pentru a calcula formula liniei de calibrare și a determina concentrația unei soluții necunoscute sau pentru a decide ce valoare ar trebui să introducem în program pentru a lansa bila la o anumită distanță.
Pasul 1: Crearea graficului
Tabelul nostru simplu va fi format din două coloane: „Valoare X” și „Valoare Y”.
Începem prin a selecta datele pentru grafic.
Selectați mai întâi celulele din coloana „Valoare X”.
Apoi, apăsați tasta Ctrl și selectați celulele din coloana „Valoare Y”.
Mergeți la fila „Inserare”.
Alegeți meniul „Grafice” și selectați prima opțiune din meniul derulant „Scatter”.
Va apărea un grafic cu punctele de date din cele două coloane.
Selectați seria de date făcând clic pe unul dintre punctele albastre. Excel va contura punctele selectate.
Faceți clic dreapta pe unul dintre puncte și selectați opțiunea „Adăugați linie de tendință”.
O linie dreaptă va apărea pe grafic.
În partea dreaptă a ecranului va apărea meniul „Format Trendline”. Bifați casetele „Afișați ecuația pe diagramă” și „Afișați valoarea R-pătrat pe diagramă”. Valoarea R-pătrat indică cât de bine se potrivește linia cu datele. O valoare R-pătrat de 1.000 înseamnă că fiecare punct de date se află pe linie. Pe măsură ce diferențele dintre puncte și linie cresc, valoarea R-pătrat scade, cu 0.000 fiind cea mai mică valoare posibilă.
Ecuația și valoarea R-pătrat a liniei de tendință vor fi afișate pe grafic. Observați că în exemplul nostru, corelația datelor este foarte bună, având o valoare R-pătrat de 0.988.
Ecuația are forma „Y = Mx + B”, unde M reprezintă panta liniei, iar B reprezintă intersecția liniei cu axa Y.
Acum, că am terminat calibrarea, vom personaliza graficul adăugând un titlu și etichete pentru axe.
Pentru a schimba titlul graficului, faceți clic pe el pentru a selecta textul.
Introduceți un titlu nou, descriptiv pentru grafic.
Pentru a adăuga titluri axelor, accesați Instrumente grafice > Proiectare.
Faceți clic pe meniul derulant „Adăugați element de diagramă”.
Navigați la Titluri axe > Orizontală principală.
Un titlu de axă va apărea.
Pentru a redenumi titlul axei, selectați textul și introduceți noul titlu.
Acum, selectați Titluri axe > Verticală principală.
Un nou titlu de axă va apărea.
Redenumiți acest titlu selectând textul și introducând un nou titlu.
Graficul este acum complet.
Pasul 2: Calculul ecuației liniei și a valorii R-pătrat
Vom calcula acum ecuația liniei și statistica R-pătrat utilizând funcțiile încorporate SLOPE, INTERCEPT și CORREL din Excel.
În foaia noastră de calcul (la rândul 14), am adăugat titluri pentru aceste trei funcții. Vom realiza calculele efective în celulele de sub titlurile respective.
Începem prin a calcula Panta. Selectați celula A15.
Accesați Formule > Mai multe funcții > Statistică > PANTĂ.
Va apărea fereastra Argumente funcție. În câmpul „Known_ys”, selectați sau introduceți celulele din coloana „Valoare Y”.
În câmpul „Known_xs”, selectați sau introduceți celulele din coloana „Valoare X”. Ordinea câmpurilor „Known_ys” și „Known_xs” este importantă în funcția SLOPE.
Faceți clic pe „OK”. Formula finală în bara de formule ar trebui să arate astfel:
=PANTĂ(C3:C12;B3:B12)
Valoarea returnată de funcția SLOPE în celula A15 corespunde cu valoarea afișată pe grafic.
În continuare, selectați celula B15, apoi accesați Formule > Mai multe funcții > Statistică > INTERCEPTARE.
Va apărea fereastra Argumente funcție. Selectați sau introduceți celulele din coloana „Valoare Y” pentru câmpul „Known_ys”.
Selectați sau introduceți celulele din coloana „Valoare X” pentru câmpul „Known_xs”. Ordinea câmpurilor „Known_ys” și „Known_xs” este importantă și în funcția INTERCEPT.
Faceți clic pe „OK”. Formula finală în bara de formule ar trebui să arate astfel:
=INTERCEPTARE(C3:C12;B3:B12)
Valoarea returnată de funcția INTERCEPT corespunde cu intersecția cu axa Y afișată în grafic.
În continuare, selectați celula C15 și accesați Formule > Mai multe funcții > Statistică > CORREL.
Va apărea fereastra Argumente funcție. Selectați sau tastați oricare dintre cele două intervale de celule pentru câmpul „Array1”. Spre deosebire de SLOPE și INTERCEPT, ordinea nu afectează rezultatul funcției CORREL.
Selectați sau tastați celălalt interval de celule pentru câmpul „Array2”.
Faceți clic pe „OK”. Formula ar trebui să arate astfel în bara de formule:
=CORREL(B3:B12;C3:C12)
Valoarea returnată de funcția CORREL nu corespunde valorii „r-pătrat” din grafic. Funcția CORREL returnează „R”, așa că trebuie să-l ridicăm la pătrat pentru a calcula „R-pătrat”.
Faceți clic în interiorul barei de formule și adăugați „^2” la finalul formulei pentru a ridica la pătrat valoarea returnată de funcția CORREL. Formula finalizată ar trebui să arate acum astfel:
=CORREL(B3:B12;C3:C12)^2
Apăsați Enter.
După modificarea formulei, valoarea „R-pătrat” corespunde acum cu cea afișată în grafic.
Pasul 3: Configurați formule pentru calculul rapid al valorilor
Acum putem utiliza aceste valori în formule simple pentru a determina concentrația acelei soluții „necunoscute” sau ce valoare trebuie introdusă în cod pentru a face ca bila să zboare la o anumită distanță.
Acești pași vor configura formulele necesare pentru a introduce o valoare X sau Y și pentru a obține valoarea corespunzătoare pe baza curbei de calibrare.
Ecuația liniei de potrivire optimă are forma „Valoare-Y = PANTĂ * Valoare-X + INTERCEPT”, deci determinarea „valorii-Y” se face prin înmulțirea valorii-X cu panta și adăugarea intercepției.
De exemplu, dacă introducem zero ca valoare-X, valoarea-Y returnată ar trebui să fie egală cu INTERCEPTAREA liniei de potrivire optimă. Deoarece rezultatul este corect, formula funcționează.
Determinarea valorii-X pe baza unei valori-Y se face prin scăderea INTERCEPTEI din valoarea-Y și împărțirea rezultatului la PANTĂ:
X-value=(Y-value-INTERCEPT)/SLOPE
De exemplu, am utilizat INTERCEPTA ca valoare-Y. Valoarea-X returnată ar trebui să fie zero, dar rezultatul este 3.14934E-06. Această valoare nu este exact zero deoarece am trunchiat rezultatul INTERCEPTEI. Formula funcționează corect, rezultatul fiind 0.00000314934, care este, în esență, zero.
Puteți introduce orice valoare-X în prima celulă cu margini groase, iar Excel va calcula automat valoarea-Y corespunzătoare.
Introducerea unei valori-Y în a doua celulă cu margini groase va returna valoarea-X corespunzătoare. Această formulă este ceea ce ați utiliza pentru a calcula concentrația unei soluții sau ce intrare este necesară pentru a lansa bila la o anumită distanță.
În acest caz, dacă instrumentul indică „5”, calibrarea sugerează o concentrație de 4.94, sau dacă vrem ca bila să parcurgă cinci unități de distanță, calibrarea sugerează introducerea valorii 4.94 ca variabilă de intrare pentru programul care controlează lansatorul de bile. Putem fi siguri de aceste rezultate datorită valorii ridicate a R-pătratului din acest exemplu.