Excel nabízí užitečné nástroje pro zpracování a vizualizaci kalibračních dat, včetně výpočtu optimální prokládající přímky. Tato funkcionalita je neocenitelná při sestavování laboratorních zpráv nebo při programování korekčních faktorů do měřicích zařízení.
V tomto návodu se zaměříme na to, jak v Excelu vytvořit graf, zobrazit lineární kalibrační křivku, extrahovat její rovnici a následně využít funkce SLOPE a INTERCEPT pro praktické využití kalibrační rovnice přímo v Excelu.
Co je kalibrační křivka a jak Excel usnadňuje její tvorbu?
Kalibrace spočívá v porovnávání měření zařízení, například teploty na teploměru, se známými standardními hodnotami. Typickými standardy mohou být body tuhnutí a varu vody. Z těchto porovnání se vytváří série datových párů, které tvoří základ pro kalibrační křivku.
Například dvoubodová kalibrace teploměru s využitím bodu tuhnutí (0 °C) a varu vody (100 °C) by poskytla dva datové páry. Když tyto body vyneseme do grafu a spojíme je přímkou, získáme kalibrační křivku. Za předpokladu lineární odezvy teploměru můžeme z této přímky odečítat odpovídající „skutečné“ teploty pro jakoukoli hodnotu zobrazenou teploměrem.
Kalibrační křivka tak interpoluje informace mezi dvěma známými body, což nám umožňuje s jistotou odhadovat skutečné hodnoty i při měřeních, pro která nemáme přímý standard. Například, pokud teploměr ukazuje 57,2 stupně, kalibrační křivka nám pomůže určit odpovídající skutečnou teplotu.
Excel umožňuje grafické zobrazení datových párů, přidání trendové čáry (kalibrační křivky) a zobrazení její rovnice přímo v grafu. Pro praktické použití je však možné vypočítat parametry této přímky pomocí funkcí SLOPE a INTERCEPT. Takto získané hodnoty můžeme pak jednoduše použít v dalších vzorcích pro automatický výpočet korigovaných hodnot měření.
Praktický příklad tvorby kalibrační křivky
V následujícím příkladu vytvoříme kalibrační křivku z deseti datových párů, kde každá dvojice obsahuje hodnotu X a hodnotu Y. Hodnoty X budou našimi standardy, a mohou reprezentovat cokoliv, například koncentrace chemického roztoku měřené vědeckým přístrojem nebo vstupní parametr programu řídícího zařízení. Hodnoty Y budou odpovídat odezvám přístroje, ať už je to naměřená hodnota chemického roztoku nebo vzdálenost, kterou urazí vystřelený projektil.
Po grafickém znázornění kalibrační křivky použijeme funkce SLOPE a INTERCEPT k určení její rovnice. Na základě toho můžeme například určit koncentraci „neznámého“ roztoku z naměřených dat nebo nastavit optimální vstupní parametr pro dosažení požadovaného výsledku.
Krok 1: Vytvoření grafu
Náš jednoduchý příklad tabulky se skládá ze dvou sloupců: X-hodnota a Y-hodnota.
Nejprve vybereme data pro vykreslení do grafu. Začněte označením buněk ve sloupci „X-Value“. Poté, držte klávesu Ctrl a vyberte buňky ve sloupci „Y-Value“.
Přejděte na kartu „Vložit“ a v sekci „Grafy“ vyberte možnost „Rozptyl“ (první ikona).
Zobrazí se graf s datovými body. Klikněte na jeden z modrých bodů, čímž označíte celou sérii dat. Excel body ohraničí.
Klikněte pravým tlačítkem myši na jeden z bodů a vyberte možnost „Přidat trendovou linii“.
Přes datové body se vykreslí lineární trendová čára.
Na pravé straně obrazovky se objeví nabídka „Formát trendové linie“. Zaškrtněte políčka u „Zobrazit rovnici v grafu“ a „Zobrazit v grafu druhou mocninu“. Hodnota R² (R-squared) vyjadřuje, jak dobře trendová čára odpovídá datům. Ideální hodnota R² je 1, což znamená, že všechny datové body leží přesně na přímce. Čím více se body odchylují od čáry, tím nižší je hodnota R².
V grafu se objeví rovnice trendové čáry a hodnota R². Všimněte si, že v tomto příkladu je korelace dat velmi vysoká s hodnotou R² rovnou 0,988.
Rovnice má tvar „Y = Mx + B“, kde M je směrnice (sklon) přímky a B je průsečík s osou y.
Nyní, když je kalibrace dokončena, upravíme si graf změnou nadpisu a přidáním popisků os. Pro úpravu názvu grafu na něj klikněte a vyberte text. Napište nový název, který přesně popisuje obsah grafu.
Pro přidání popisků os přejděte na Nástroje grafu > Návrh.
Klikněte na rozbalovací nabídku „Přidat prvek grafu“.
Nyní vyberte „Názvy os“ > „Primární horizontální“.
Zobrazí se popisek osy.
Pro změnu textu klikněte na popisek osy a zadejte nový název.
Nyní vyberte „Názvy os“ > „Primární vertikální“.
Zobrazí se popisek pro vertikální osu.
Stejným způsobem přejmenujte i tento popisek.
Váš graf je nyní kompletní.
Krok 2: Výpočet parametrů přímky a R²
Nyní vypočítáme rovnici přímky a statistiku R² pomocí vestavěných funkcí SLOPE, INTERCEPT a CORREL v Excelu.
Do našeho listu jsme přidali popisky pro tyto tři funkce (v řádku 14). Výpočty provedeme v buňkách pod těmito popisky.
Nejprve vypočítáme SLOPE. Vyberte buňku A15.
Přejděte na Vzorce > Další funkce > Statistické > SLOPE.
V zobrazeném okně „Argumenty funkce“ vyberte nebo zadejte buňky sloupce Y-Value do pole „Known_ys“.
Do pole „Known_xs“ vyberte buňky sloupce X-Value. U funkce SLOPE je důležité pořadí polí ‚Known_ys‘ a ‚Known_xs‘.
Klikněte na „OK“. Výsledný vzorec ve vzorcovém řádku by měl vypadat takto: =SLOPE(C3:C12;B3:B12)
Všimněte si, že hodnota vrácená funkcí SLOPE v buňce A15 odpovídá sklonu přímky zobrazené v grafu.
Dále vyberte buňku B15 a přejděte na Vzorce > Další funkce > Statistika > INTERCEPT.
V zobrazeném okně „Argumenty funkce“ vyberte nebo zadejte buňky sloupce Y-Value do pole „Known_ys“.
Vyberte buňky sloupce X-Value pro pole „Known_xs“. Stejně jako u SLOPE, i zde záleží na pořadí polí ‚Known_ys‘ a ‚Known_xs‘.
Klikněte na „OK“. Výsledný vzorec by měl vypadat takto: =INTERCEPT(C3:C12;B3:B12)
Všimněte si, že hodnota vrácená funkcí INTERCEPT odpovídá průsečíku s osou y v grafu.
Dále vyberte buňku C15 a přejděte na Vzorce > Další funkce > Statistika > KOREL.
V zobrazeném okně „Argumenty funkce“ vyberte libovolný z rozsahů buněk pro pole „Array1“. U funkce CORREL na pořadí nezáleží.
Vyberte druhý z rozsahů buněk pro pole „Array2“.
Klikněte na „OK“. Výsledný vzorec by měl vypadat takto: =CORREL(B3:B12,C3:C12)
Všimněte si, že hodnota vrácená funkcí CORREL neodpovídá přímo hodnotě R² v grafu. Funkce CORREL vrací hodnotu R, kterou musíme umocnit na druhou, abychom získali R².
Klikněte do řádku vzorců a na konec vzorce přidejte „^2“. Hotový vzorec by měl vypadat takto: =CORREL(B3:B12,C3:C12)^2
Stiskněte klávesu Enter.
Po úpravě vzorce se hodnota R² shoduje s hodnotou zobrazenou v grafu.
Krok 3: Vytvoření vzorců pro výpočet hodnot
Nyní můžeme pomocí těchto hodnot v jednoduchých vzorcích určit koncentraci „neznámého“ roztoku nebo vypočítat optimální vstupní parametry pro naše zařízení. Tyto kroky nám umožní zadat buď hodnotu X, nebo hodnotu Y, a automaticky získat odpovídající protihodnotu z naší kalibrační křivky.
Rovnice přímky má tvar „Y = SLOPE * X + INTERCEPT“. Pro výpočet „Y“ tedy vynásobíme hodnotu „X“ se směrnicí (SLOPE) a přičteme průsečík (INTERCEPT).
Pro ilustraci jsme použili nulu jako hodnotu X. Vrácená hodnota Y by se měla rovnat průsečíku. Vidíme, že tomu tak je, což potvrzuje správnost vzorce.
Pro výpočet „X“ z hodnoty „Y“ od „Y“ odečteme průsečík a výsledek vydělíme směrnicí: X = (Y – INTERCEPT) / SLOPE
Jako příklad jsme použili průsečík jako hodnotu Y. Vrácená hodnota X by měla být rovna nule. Výsledkem je 3,14934E-06. Toto odchýlení je způsobeno zaokrouhlením hodnoty INTERCEPT. I přes to vzorec funguje správně, protože výsledek je velmi blízko nule.
Do první buňky se silným okrajem můžete zadat libovolnou hodnotu X a Excel automaticky vypočítá odpovídající hodnotu Y.
Stejně tak zadáním libovolné hodnoty Y do druhé buňky se silným okrajem získáte odpovídající hodnotu X. Tento vzorec použijete pro výpočet koncentrace roztoku z naměřených dat nebo pro určení správného vstupního parametru pro dosažení požadovaného výsledku.
Pokud tedy náš přístroj měří hodnotu „5“, kalibrace nám napovídá, že koncentrace látky je 4,94. Stejně tak, pokud chceme, aby kulička urazila vzdálenost 5 jednotek, kalibrace nám doporučí zadat hodnotu 4,94 jako vstupní parametr. Díky vysoké hodnotě R² si můžeme být těmito výsledky poměrně jisti.