Program Excel ma wbudowane funkcje, których można używać do wyświetlania danych kalibracji i obliczania linii najlepszego dopasowania. Może to być pomocne, gdy piszesz raport laboratorium chemicznego lub programujesz współczynnik korygujący w urządzeniu.
W tym artykule przyjrzymy się, jak używać programu Excel do tworzenia wykresów, wykreślania liniowej krzywej kalibracji, wyświetlania wzoru krzywej kalibracji, a następnie konfigurowania prostych formuł z funkcjami NACHYLENIE i ODCINEK, aby użyć równania kalibracji w programie Excel.
Spis treści:
Co to jest krzywa kalibracji i jak przydaje się Excel podczas jej tworzenia?
Aby przeprowadzić kalibrację, porównujesz odczyty urządzenia (np. Temperaturę wyświetlaną przez termometr) ze znanymi wartościami zwanymi wzorcami (takimi jak punkty zamarzania i wrzenia wody). Pozwala to na utworzenie serii par danych, których następnie użyjesz do opracowania krzywej kalibracji.
Dwupunktowa kalibracja termometru przy użyciu punktów zamarzania i wrzenia wody miałaby dwie pary danych: jedną z umieszczenia termometru w lodowatej wodzie (32 ° F lub 0 ° C), a drugą we wrzącej wodzie (212 ° F) lub 100 ° C). Kiedy narysujesz te dwie pary danych jako punkty i narysujesz linię między nimi (krzywa kalibracji), a następnie zakładając, że odpowiedź termometru jest liniowa, możesz wybrać dowolny punkt na linii, który odpowiada wartości wyświetlanej przez termometr, a ty może znaleźć odpowiednią „prawdziwą” temperaturę.
Tak więc linia zasadniczo wypełnia informacje między dwoma znanymi punktami, dzięki czemu możesz być dość pewny podczas szacowania rzeczywistej temperatury, gdy termometr wskazuje 57,2 stopni, ale kiedy nigdy nie zmierzyłeś „normy”, która odpowiada to czytanie.
Excel ma funkcje, które pozwalają na graficzne wykreślenie par danych na wykresie, dodanie linii trendu (krzywej kalibracji) i wyświetlenie równania krzywej kalibracji na wykresie. Jest to przydatne w przypadku wyświetlania wizualnego, ale można również obliczyć wzór linii za pomocą funkcji programu Excel NACHYLENIE i ODCINEK. Gdy wprowadzisz te wartości do prostych wzorów, będziesz mógł automatycznie obliczyć „prawdziwą” wartość na podstawie dowolnego pomiaru.
Spójrzmy na przykład
W tym przykładzie opracujemy krzywą kalibracji z serii dziesięciu par danych, z których każda składa się z wartości X i wartości Y. Wartości X będą naszymi „standardami” i mogą reprezentować wszystko, od stężenia roztworu chemicznego, który mierzymy za pomocą instrumentu naukowego, po zmienną wejściową programu sterującego maszyną do wypuszczania marmuru.
Wartości Y będą „odpowiedziami” i będą reprezentować odczyt dostarczony przez przyrząd podczas pomiaru każdego roztworu chemicznego lub zmierzoną odległość, na jaką odległość od wyrzutni wylądowała kulka przy użyciu każdej wartości wejściowej.
Po graficznym przedstawieniu krzywej kalibracji, użyjemy funkcji NACHYLENIE i ODCIĄG, aby obliczyć wzór linii kalibracji i określić stężenie „nieznanego” roztworu chemicznego na podstawie odczytu przyrządu lub zdecydować, jakie dane wprowadzić program, aby marmur ląduje w pewnej odległości od wyrzutni.
Krok pierwszy: utwórz swój wykres
Nasz prosty przykładowy arkusz kalkulacyjny składa się z dwóch kolumn: X-Value i Y-Value.
Zacznijmy od wybrania danych do wykreślenia na wykresie.
Najpierw wybierz komórki kolumny „Wartość X”.
Teraz naciśnij klawisz Ctrl, a następnie kliknij komórki kolumny Wartość Y.
Przejdź do zakładki „Wstaw”.
Przejdź do menu „Wykresy” i wybierz pierwszą opcję z menu „Rozproszenie”.
scatter ”width =” 314 ″ height = ”250 ″ onload =” pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this); ” onerror = ”this.onerror = null; pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this);”>
Pojawi się wykres zawierający punkty danych z dwóch kolumn.
Wybierz serię, klikając jeden z niebieskich punktów. Po wybraniu programu Excel obrysuje punkty, które zostaną obrysowane.
Kliknij jeden z punktów prawym przyciskiem myszy, a następnie wybierz opcję „Dodaj linię trendu”.
Na wykresie pojawi się prosta linia.
Po prawej stronie ekranu pojawi się menu „Format Trendline”. Zaznacz pola obok „Wyświetl równanie na wykresie” i „Wyświetl wartość R-kwadrat na wykresie”. Wartość R-kwadrat to statystyka, która mówi ci, jak dokładnie linia pasuje do danych. Najlepsza wartość R-kwadrat to 1.000, co oznacza, że każdy punkt danych dotyka linii. Wraz ze wzrostem różnic między punktami danych i linią wartość r-kwadrat spada, przy czym 0,000 jest najniższą możliwą wartością.
Na wykresie pojawi się równanie i statystyka R-kwadrat linii trendu. Zauważ, że korelacja danych w naszym przykładzie jest bardzo dobra, z wartością R-kwadrat wynoszącą 0,988.
Równanie ma postać „Y = Mx + B”, gdzie M to nachylenie, a B to punkt przecięcia z osią Y prostej.
Teraz, gdy kalibracja jest zakończona, popracujmy nad dostosowaniem wykresu, edytując tytuł i dodając tytuły osi.
Aby zmienić tytuł wykresu, kliknij go, aby zaznaczyć tekst.
Teraz wpisz nowy tytuł, który opisuje wykres.
Aby dodać tytuły do osi X i Y, najpierw przejdź do Narzędzia wykresów> Projekt.
design ”width =” 650 ″ height = ”225 ″ onload =” pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this); ” onerror = ”this.onerror = null; pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this);”>
Kliknij menu „Dodaj element wykresu”.
Teraz przejdź do Tytuły osi> Główny poziomo.
główna pozioma ”width =” 650 ″ height = ”500 ″ onload =” pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this); ” onerror = ”this.onerror = null; pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this);”>
Pojawi się tytuł osi.
Aby zmienić nazwę tytułu osi, najpierw zaznacz tekst, a następnie wpisz nowy tytuł.
Teraz przejdź do Tytułów osi> Główna pionowa.
Pojawi się tytuł osi.
Zmień nazwę tego tytułu, zaznaczając tekst i wpisując nowy tytuł.
Twój wykres jest teraz kompletny.
Krok drugi: Oblicz równanie linii i statystykę R-kwadrat
Teraz obliczmy równanie linii i statystykę R-kwadrat za pomocą wbudowanych funkcji programu Excel: NACHYLENIE, PRZECIĄGNIĘCIE i KOREL.
Do naszego arkusza (w wierszu 14) dodaliśmy tytuły tych trzech funkcji. Przeprowadzimy rzeczywiste obliczenia w komórkach pod tymi tytułami.
Najpierw obliczymy NACHYLENIE. Wybierz komórkę A15.
Przejdź do Formuły> Więcej funkcji> Statystyka> NACHYLENIE.
Więcej funkcji> Statystyka> NACHYLENIE ”szerokość =” 650 ″ wysokość = ”435 ″ onload =” pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this); ” onerror = ”this.onerror = null; pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this);”>
Wyskakuje okno argumentów funkcji. W polu „Known_ys” wybierz lub wpisz komórki kolumny Y-Value.
W polu „Znane_xs” wybierz lub wpisz komórki w kolumnie Wartość X. Kolejność pól „Known_ys” i „Known_xs” ma znaczenie w funkcji SLOPE.
Kliknij OK.” Ostateczna formuła na pasku formuły powinna wyglądać następująco:
= NACHYLENIE (C3: C12; B3: B12)
Zwróć uwagę, że wartość zwrócona przez funkcję NACHYLENIE w komórce A15 jest zgodna z wartością wyświetlaną na wykresie.
Następnie wybierz komórkę B15, a następnie przejdź do Formuły> Więcej funkcji> Statystyka> ODCIĄG.
Więcej funkcji> Statystyka> INTERCEPT ”width =” 650 ″ height = ”435 ″ onload =” pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this); ” onerror = ”this.onerror = null; pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this);”>
Wyskakuje okno argumentów funkcji. Wybierz lub wpisz komórki kolumny Wartość Y w polu „Znane_ys”.
Wybierz lub wpisz komórki kolumny X-Value w polu „Known_xs”. Kolejność pól „Known_ys” i „Known_xs” ma również znaczenie w funkcji ODCIĘTA.
Kliknij OK.” Ostateczna formuła na pasku formuły powinna wyglądać następująco:
= ODCIĘTA (C3: C12; B3: B12)
Zwróć uwagę, że wartość zwrócona przez funkcję INTERCEPT jest zgodna z punktem przecięcia z osią y wyświetlanym na wykresie.
Następnie wybierz komórkę C15 i przejdź do Formuły> Więcej funkcji> Statystyka> CORREL.
Więcej funkcji> Statystyka> CORREL ”width =” 650 ″ height = ”435 ″ onload =” pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this); ” onerror = ”this.onerror = null; pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon (this);”>
Wyskakuje okno argumentów funkcji. Wybierz lub wpisz jeden z dwóch zakresów komórek w polu „Tablica1”. W przeciwieństwie do SLOPE i INTERCEPT, kolejność nie wpływa na wynik funkcji CORREL.
Wybierz lub wpisz drugi z dwóch zakresów komórek w polu „Tablica2”.
Kliknij OK.” Wzór na pasku formuły powinien wyglądać następująco:
= CORREL (B3: B12; C3: C12)
Zwróć uwagę, że wartość zwrócona przez funkcję CORREL nie odpowiada wartości „r-kwadrat” na wykresie. Funkcja CORREL zwraca „R”, więc musimy ją podnieść do kwadratu, aby obliczyć „R-kwadrat”.
Kliknij wewnątrz paska funkcji i dodaj „^ 2” na końcu formuły, aby podnieść do kwadratu wartość zwróconą przez funkcję CORREL. Wypełniona formuła powinna teraz wyglądać następująco:
= CORREL (B3: B12; C3: C12) ^ 2
Naciśnij enter.
Po zmianie formuły wartość „R-kwadrat” odpowiada teraz wartości wyświetlanej na wykresie.
Krok trzeci: Skonfiguruj formuły do szybkiego obliczania wartości
Teraz możemy użyć tych wartości w prostych formułach, aby określić stężenie tego „nieznanego” roztworu lub jakie dane wprowadzić do kodu, aby kulka przeleciała na określoną odległość.
Te kroki pozwolą na skonfigurowanie formuł potrzebnych do wprowadzenia wartości X lub wartości Y i uzyskania odpowiedniej wartości na podstawie krzywej kalibracji.
Równanie linii najlepszego dopasowania ma postać „Wartość Y = NACHYLENIE * Wartość X + ODCINEK”, więc rozwiązanie „Wartość Y” odbywa się poprzez pomnożenie wartości X i NACHYLENIA, a następnie dodanie INTERCEPT.
Na przykład wstawiamy zero jako wartość X. Zwrócona wartość Y powinna być równa ODCIĘCIE linii najlepszego dopasowania. Pasuje, więc wiemy, że formuła działa poprawnie.
Aby znaleźć wartość X na podstawie wartości Y, należy odjąć ODCIĘCIE od wartości Y i podzielić wynik przez NACHYLENIE:
X-value=(Y-value-INTERCEPT)/SLOPE
Jako przykład użyliśmy ODCIĘCIE jako wartości Y. Zwrócona wartość X powinna być równa zero, ale zwrócona wartość to 3,14934E-06. Zwracana wartość nie jest zerem, ponieważ nieumyślnie obcięliśmy wynik ODCIĘCIE podczas wpisywania wartości. Formuła działa jednak poprawnie, ponieważ wynik formuły to 0,00000314934, czyli w zasadzie zero.
Możesz wprowadzić dowolną wartość X, którą chcesz, do pierwszej komórki z grubą ramką, a program Excel automatycznie obliczy odpowiednią wartość Y.
Wprowadzenie dowolnej wartości Y do drugiej komórki z grubą ramką da odpowiednią wartość X. Tej formuły należy użyć do obliczenia stężenia tego roztworu lub danych wejściowych potrzebnych do wystrzelenia kulki na określoną odległość.
W tym przypadku przyrząd odczytuje „5”, więc kalibracja sugeruje stężenie 4,94 lub chcemy, aby kulka pokonała pięć jednostek odległości, więc kalibracja sugeruje wprowadzenie 4,94 jako zmiennej wejściowej dla programu sterującego wyrzutnią marmuru. Możemy być dość pewni tych wyników ze względu na wysoką wartość R-kwadrat w tym przykładzie.