Tworzenie wykresów
Tworzenie wykresów jest jedną z najważniejszych umiejętności w pracy z arkuszem kalkulacyjnym.
Począwszy od wersji MS Excel 2007 filozofia tej czynności jest odmienna, niż w wersjach poprzednich.
Poniżej przedstawię sposób tworzenia wykresów w wersji programu MS Excel używanej na ćwiczeniach laboratorium komputerowego.
W celu wstawienia wykresu stosujemy bądź opcję menu Wstaw/Wykres, bądź tez klikamy ikonę kreatora wykresów na pasku narzędzi programu:
Po wykonaniu tej czynności otwiera się pierwsze okno Kreatora wykresów.
W przypadku interesujących nas wykresów danych pomiarowych zawsze wybieramy wykres XY (Punktowy), gdyż jedynie w tym przypadku określamy zarówno współrzędne x, jak i y punktów pomiarowych. Po prawej stronie możemy wybrać Podtyp wykresu. Dla przedstawienia punktów, których wartości wynikają z pomiarów, wybieramy podtyp , krzywe teoretyczne bądź krzywe
dopasowania zwykle przedstawia się przy pomocy podtypu .
W kolejnym kroku w zakładce Zakres danych decydujemy, czy serie pomiarowe znajdują się w kolumnach czy wierszach (najczęściej rozpoznanie przez program jest prawidłowe). Opcję Zakres danych radzę wyczyścić – program sugeruje w nim utworzenie wykresu ze wszystkich serii w całym arkuszu, co nie zawsze jest prawdziwe.
Seria pomiarowa – wielkość mierzona w trakcie całego cyklu pomiarowego (np. poziom wody w zbiorniku). Na cykl pomiarowy składają się kolejne pomiary, mogące składać się z wielu serii pomiarowych (np. poziomu wody, temperatury, ciśnienia itp.). Najczęściej pomiary zajmują kolejne wiersze, natomiast serie są reprezentowane przez kolejne kolumny.
W kolejnym kroku przechodzimy do zakładki Serie tego samego okna i klikamy w przycisk Dodaj. Po prawej stronie pojawią się pola wpisowe poszczególnych parametrów danej serii:
Nazwa: nazwa, pod którą będzie figurować opisywana seria. Może to być tekst, może też być adres komórki zawierającej tekst
Wartości X: zakres komórek, zawierających współrzędne x punktów pomiarowych (odkładanych na osi poziomej wykresu)
Wartości Y: zakres komórek, zawierających współrzędne y punktów pomiarowych (odkładanych na osi pionowej wykresu)
Po wypełnieniu ostatniego pola wpisowego i klknięciu w inne pole wpisowe w podglądzie wykresu (powyżej pól wpisowych) zobaczymy aktualny wygląd wykresu – jest to okazja do sprawdzenia jego poprawności)
UWAGA! Należy zauważyć¸ że w przypadku adresowania danych do serii używa się pełniejszych wersji adresów, niż wtedy, gdy pracujemy w pojedynczym arkuszu. Adresy te, oprócz bezwzględnych adresów komórek, poprzedzone są nazwą arkusza je zawierającego, zakończoną znakiem wykrzyknika, np. Arkusz1!@$A$2:$A$20. Adresów takich używa się zawsze wtedy, gdy dana formuła używa zawartości komórek zawartych w innym arkuszu.
Jeżeli chcemy umieścić w tym samym układzie współrzędnych inne serie pomiarowe (innych wielkości mierzonych, np. magteor), możemy w tym momencie dodać ją klikając ponownie przycisk Dodaj i wypełniając pola wpisowe dla dodawanej serii. Serię można też dodać później, po utworzeniu wykresu.
W kolejnym kroku można ustawić podstawowe parametry wykresu (np, opisy osi), jednak wszystkie parametry można też ustawić po utworzeniu wykresu, tak więc ten krok można pozostawić bez wypełniania.
W ostatnim kroku kreatora wykresów decydujemy, czy wykres ma zostać wstawiony w aktualnym arkuszu (tym, który zawiera dane), czy też w arkuszu odrębnym. Najczęściej wybieramy opcję Jako nowy arkusz, jednak niekiedy, gdy pragniemy na tym samym arkuszu mieć tabelę z danymi i wykres, wybieramy opcję Jako obiekt w:
Po zatwierdzeniu tego kroku otrzymujemy wykres „w stanie surowym”, który wymaga dalszego formatowania:
Informację, jakie są możliwości formatowania danego elementu wykresu, otrzymamy klikając w ten element prawym klawiszem myszy.
Aby np. wyczyścić widoczne poziome linie siatki należy kliknąć w dowolną z nich, a następnie wybrać opcję Wyczyść. Aby oczyścić szary obszar kreślenia, klikamy w niego, wybieramy opcję Formatuj obszar kreślenia i w oknie Obszar wybieramy opcję Brak.
Jeżeli chcemy na osi pionowej wprowadzić pomocnicze znaczniki, klikamy w nią, wybieramy opcję Formatuj osie i zakładce Desenie jako Typ pomocniczego znacznika osi wybieramy Na zewnątrz.
Aby odwrócić oś „do góry nogami” w tym samym oknie, w zakładce Skala zaznaczamy opcję Wartości w kolejności odwrotnej (w takim przypadku dobrze jest też zaznaczyć Przecięcie z osią wartości (X) w wartości maksymalnej). W tej samej zakładce można też ustawić, co ile ma być wpisana jednostka główna, pomocnicza, wartości maksymalne i minimalne osi itp. (domyślnie zaznaczone są wartości dobierane automatycznie do zakresu danych). Efektem tych czynności będzie wykres:
W podobny sposób można sformatować każdy element wykresu. Jeżeli chcemy np. dodać do niego nową serię, a nie uczyniliśmy tego w czasie tworzenia, wystarczy kliknąć gdziekolwiek w obszar wykresu i wybrać opcję Dane źródłowe, a następnie zakładkę Serie – znajdziemy się w znanym nam oknie dodawania kolejnych serii. Jeżeli chcemy zmienić typ wykresu, to po kliknięciu w obszar wykresu wybierzemy opcję Typ wykresu (przypominam, że krzywe teoretyczne i krzywe dopasowania powinny być liniami ciągłymi). Na poniższym wykresie do naszego przykładowego wykresu dodano serię teoretyczną magteor. Krzywą, reprezentującą tą serię pogrubiono, klikając na nią i wybierają opcję Formatuj serie danych, zakładkę Desenie.
Dopasowywanie krzywej teoretycznej do zbioru danych przy pomocy funkcji wbudowanych w program
Obserwowany na wykresach rozrzut punktów pomiarowych związany jest najczęściej z błędami pomiaru przedstawianej wielkości. Często badana zależność opisywana jest wzorem o nieznanych parametrach. Przykładem może być poniższy wykres, gdzie z teorii zagadnienia wynika, iż zależność maghel od logr powinna być liniowa i spełniać równanie prostej:
maghel = A · logr + B gdzie parametrami „do wyznaczenia” są współczynniki A i B.
W celu wstępnego sprawdzenia, czy dwie serie danych są z sobą skorelowane liniowo, można użyć funkcji statystycznej WSP.KORELACJI, wyznaczającej współczynnik korelacji liniowej. Jeżeli otrzymalibyśmy wartość współczynnika korelacji równą zero, oznaczałoby to że badane dwie serie pomiarowe nie są w ogóle skorelowane. W przypadku wartości równej 1 (czyli 100%) oznaczałoby to pełną korelację liniową, czyli położenie punktów na linii prostej. Za istotną uznaje się zwykle korelację większą od 0,89 lub 0,92 (w zależności od tematyki badań). W opisywanym przypadku współczynnik ten wynosi aż 0,964786, co oznacza, że hipoteza o zależności liniowej serii maghel i logr jest poprawna.
W kolejnym kroku analizy możemy poprowadzić przez badany wykres krzywą najlepszego dopasowania, tzw. linię trendu. W tym celu klikamy prawym klawiszem myszy na dowolny punkt wykresu i wybieramy opcję Dodaj linię trendu.
W zakładce Typ możemy wybrać rodzaj krzywej dopasowania. Możemy dopasować:
• Funkcję liniową typu y = A · x + B
• Funkcję logarytmiczną typu y = A · ln x + B
• Funkcję wielomianową typu y = A · xn + B · xn-1 + C · xn-2 + …, gdzie stopień wielomianu n ustawiam w polu Stopień
• Funkcję potęgową typu y = A · xB
• Funkcję wykładniczą typu y = A · eB·x
• Średnią ruchomą, która uśrednia ilość danych określoną przez wpis w polu Okres i używa ich średniej wartości jako punktu.
W naszym przykładzie wybieramy funkcję liniową, następnie klikamy w zakładkę Opcje okna Dodaj linie trendu, w której to zakładce zaznaczamy opcje Wyświetl równanie na wykresie oraz Wyświetl wartości R-kwadrat na wykresie. W efekcie otrzymujemy:
Na otrzymanym wykresie, oprócz prostej dopasowanej do zbioru punktów, widzimy też wypisane równanie tej prostej, jak również wartość R2, będącą współczynnikiem jej dopasowania do zbioru punktów. Podobnie jak przy współczynniku korelacji, wartość R2 równa 0 oznaczałaby brak dopasowania, równa 1 natomiast punkty leżące na prostej. W naszym przykładzie wartość 0,9308 jest zadowalająca (większa od 0,92) i sugeruje, że rzeczywiście cały przedstawiony zbiór danych pomiarowych można opisać przy pomocy zależności liniowej.
Dopasowanie linii prostej do zbiorów punktów nazywamy regresją liniową. Współczynniki dopasowania linii prostej do zbioru punktów można też wyznaczyć bez rysowania wykresu, przy pomocy funkcji statystycznej REGLINP (skrót od Regresji Liniowej Parametry).
Funkcja ta posiada cztery argumenty: REGLINP(zbiór danych y; zbiór danych x; Stała; Statystyka).
Argument Stała jest zmienną typu logicznego. W przypadku, gdy jest ona równa 1 (czyli Prawda) wymusza się na programie poprowadzenie linii dopasowania przez punkt o współrzędnych (0,0), czyli przez początek układu współrzędnych. W przypadku, gdy jest ona równa 0 (czyli Fałsz) zabrania się poprowadzenia linii dopasowania przez ten punkt. Jeżeli nie wiemy, jaką wartość przyjmuje badana wielkość w punkcie x=0, pozostawiamy ten parametr nie wypełniony (jak w naszym przykładzie).
Argument Statystyka również jest zmienną typu logicznego. Jeżeli jest ona równa 1 (czyli Prawda), wyliczone będą wszystkie, a nie tylko podstawowe parametry dopasowania (współczynniki A i B równania prostej).
Przy rozwiązywaniu funkcji REGLINP należy pamiętać, że jest to funkcja typu tablicowego, czyli dająca wiele rozwiązań (patrz rozdział o funkcjach matematycznych) – w tym przypadku rozwiązania tworzą blok komórek o dwu kolumnach i pięciu wierszach:
W opisywanym przykładzie funkcja REGINP będzie miała postać:
=REGLINP(R2:R194;Q2:Q194;;1) i w efekcie otrzymamy:
gdzie A i B są współczynnikami równania prostej y = A · x + B, ∆A i ∆B błędami ich wyznaczenia (czyli A = 8,6 ± 0,2 , B = 2,77 ± 0,09), R2 jest znanym nam już współczynnikiem dopasowania prostej do zbioru punktów, ∆y jest średnim błędem pomiaru wartości y (w tym przykładzie błędem mierzonej wartości maghel), F jest statystyką pomiaru, df jest liczbą stopni swobody, ssreg jest regresyjną sumą kwadratów, natomiast ssresid jest resztkową sumą kwadratów. W zagadnieniach, poruszanych w ramach omawianych ćwiczeń, istotne są jedynie A, B, ∆A, ∆B, R2 i ∆y.
Z funkcją REGLINP związane są również funkcje REGLINW i REGLINX.
Funkcja REGLINW służy do interpolacji danych, tzn. do wyznaczania wartości y w punkcie x przy założeniu liniowej zależności serii y i serii x w sytuacji, gdy wartość x nie występuje w zbiorze posiadanych danych pomiarowych, jednak mieści się w zakresie ich zmienności.
Np. Chcę się dowiedzieć, ile wynosiłaby wartość maghel w opisywanym przykładzie przy wartości logr równej 0,45? Odpowiedź: =REGLINW(R2:R194;Q2:Q194;0,45) [czwarty parametr, Stała, ma to samo znaczenie, co w funkcji REGLINP i jest tu pomijany], da wartość 6,648347.
Funkcja REGLINX służy do ekstrapolacji danych, tzn. do wyznaczania wartości y w punkcie x przy założeniu liniowej zależności serii y i serii x w sytuacji, gdy wartość x nie występuje w zbiorze posiadanych danych pomiarowych, przyczym mieści się poza zakresem ich zmienności.
Np. Chcę się dowiedzieć, ile wynosiłaby wartość maghel w opisywanym przykładzie przy wartości logr równej 0,9? Odpowiedź: =REGLINX(0,9;R2:R194;Q2:Q194), da wartość 10,5258.
Podobną grupę tworzą funkcje REGEXPP i REGEXPW, jednak służą one do badania dopasowania do zbioru danych nie funkcji liniowej, lecz funkcji wykładniczej typu y = B * Ax. Ich składnia jest identyczna, jak wcześniej omówionych funkcji regresji liniowej, toteż nie będę ich szczegółowo opisywał.
Proszę zapoznać się też z przykładem w postaci gotowego skoroszytu:
http://vistula.pk.edu.pl/~sciezor/2002t7.xls
Dopasowywanie krzywej teoretycznej do zbioru danych metodą najmniejszych kwadratów
W zagadnieniach fizycznych i technicznych często zachodzi potrzeba dopasowania do istniejącego zbioru danych krzywej teoretycznej, będącej wynikiem wzoru, którego nie da się sprowadzić do żadnej z dostępnych, a opisanych wcześniej, funkcji dopasowujących w programie MS Excel. W takim przypadku można skorzystać z tzw. metody najmniejszych kwadratów, której implementację w arkuszu kalkulacyjnym przedstawię poniżej na przykładzie wykresu mag (JD) zbioru danych 2002t7.dat.
W celu dopasowania krzywej teoretycznej do zależności mag(JD) należy:
1. Dla każdego pomiaru policzyć nową, teoretyczną wartość mag. W naszym przykładzie wartość ta jest opisana wzorem:
mag = H0 + 5 · log ∆ + 2,5 · n · log R
gdzie wartości ∆ (czyli Delta) i R odczytujemy z tabeli danych dla danego pomiaru (czyli w tym samym wierszu), natomiast H0 i n są parametrami funkcji, które musimy wyznaczyć – muszą one mieć taką wartość, żeby wartości wyliczone z wzoru były jak najbardziej zbliżone do wartości zmierzonych.
W tym celu musimy wstawić dwie kolumny pomocnicze w pobliżu kolumny, przechowującej interesującą nas serię danych pomiarowych – w naszym przykładzie będą to kolumny mag_fit
(fitowaniem nazywa się właśnie dopasowywanie dwóch zbiorów wartości do siebie, stąd nazwa) oraz mag-mag_fit (czyli różnica między wartością zmierzoną (mag) i policzoną z wzoru (mag_fit).
Do pierwszego wiersza (czyli dla pierwszego pomiaru) w kolumnie mag_fit wpisujemy dopasowywany wzór, pamiętając o wcześniejszym przypisaniu wartości do zmiennych H0 i nm (drugą ze zmiennych nazwałem nm a nie n, gdyż powinno unikać się nadawania zmiennym nazw jednoliterowych). Jako wartości początkowe nadaje się wartości przybliżone, które zwykle są znane, lub też wstawiamy liczby całkowicie przypadkowe (np. 3 i 3, jak w poniższym przykładzie):
gdzieś z boku:
2. Powielamy otrzymany wynik mag_fit dla wszystkich pomiarów:
3. W kolumnie mag-mag_fit obliczamy różnicę wartości zmierzonej (mag) i wyliczone z wzoru (mag_fit):
i powielamy ją dla wszystkich pomiarów:
4. Dodajemy otrzymaną serię mag_fit do wykresu mag(JD)
Jak widać, na razie krzywa teoretyczna mag_fit nie jest dopasowana do zbioru punktów. Jest to związane z błędnymi wartościami parametrów H0 i n, które dopiero wymagają wyznaczenia.
5. Przy pomocy funkcji SUMA.KWADRATÓW liczymy sumę kwadratów różnic z serii mag-mag_fit.
6. Uruchamiamy narzędzie optymalizacyjne Solver. Znajduje sie ono w opcji menu Narzędzia/Solver (w przypadku braku tego narzędzia należy je dodać w opcji menu Narzędzia/Dodatki). W pole Komórka celu wstawiamy adres komórki, gdzie znajduje się policzona przed momentem suma kwadratów różnic, wybieramy opcję Równa: min (stąd nazwa „metoda najmniejszych kwadratów”), a w pole Komórki zmieniane wpisujemy zakres adresów komórek przechowujących wartości parametrów, opisujących dopasowwywany wzór (w naszym przypadku H0 i nm):
7. Po naciśnięciu przycisku Rozwiąż otrzymujemy okno Solver – Wyniki. Jeżeli znajdziemy w nim napis „Solver znalazł rozwiązanie”, to na tym kończymy działanie naciskając klawisz OK, jeżeli zobaczymy napis „Solver zbliżył się do rozwiązania”, uruchamiamy Solvera ponownie (wszystkie ustawienia są pamiętane i nie musimy ich ponownie ustawiać), aż do osiągnięcia napisu „Solver znalazł rozwiązanie”.
W tym momencie wartości H0 i n otrzymują wartości optymalne dla dopasowania krzywej teoretycznej do zbioru punktów:
a wykres otrzymuje postać końcową:
Widać, że dopasowanie jest rzeczywiście doskonałe.
Inny przykład dopasowywania można znaleźć na stronie http://vistula.pk.edu.pl/~sciezor/hyak.xls (zbiór danych opisany wzorem analogicznym jak wykorzystany powyżej)
a jeszcze inny na stronie http://vistula.pk.edu.pl/~sciezor/visc.xls (zbiór danych opisany wzorem podanym na arkuszu)
