Konstanty Wiatkin, Podstawy matematyki i statystyki dla geografów, Instytut Geografii Akademii Bydgoskiej, www.geo.ab.edu.pl 3.4 Obliczenie charakterystyk liczbowych próbki i budowanie histogramu rozkładu empirycznego Ponieważ dane otrzymane w wyniku obserwacji z reguły są losowe, to trzeba wyznaczyć charakterystyki liczbowe tego rozkładu i zbudować histogram (rozkład empiryczny). Można oczywiście skorzystać ze wzorów z podrozdziału 2.3 obliczając parametry rozkładu ręcznie lub za pomocą kalkulatora. Jednak wygodniej jest skorzystać z usług arkusza kalkulacyjnego. Niech dane liczbowe badanego rozkładu empirycznego (n=30) zostały wprowadzone do komórek A1:A30 (rys.3.13). Mogą to być, na przykład, wyniki pomiarów średnic losowo wybranych drzew Do obliczeń statystycznych parametrów rozkładu były zastosowane odpowiednie funkcje. Większość z nich nie wymaga komentarzy i sposób korzystania z nich jest oczywisty (patrz niżej na fragmencie arkusza). Więc omówimy tylko zastosowanie niektórych funkcji. Rys.3.13. Przykłady zastosowania funkcji statystycznych. 150 Konstanty Wiatkin, Podstawy matematyki i statystyki dla geografów, Instytut Geografii Akademii Bydgoskiej, www.geo.ab.edu.pl Szczegóły zastosowania niektórych funkcji a) Gdy chcemy obliczyć kwartyle dla próbki, to korzystamy z funkcji KWARTYL, gdzie drugim argumentem jest liczba, określającą, który kwartyl trzeba wyznaczyć. Gdy podajemy 0, to wynikiem będzie minimalna wartość ze zbioru. Podając 1 otrzymujemy wartość pierwszego kwartyla, gdy podajemy 2, to funkcja zwraca wartość drugiego kwartyla, który jest również medianą. Przy wartości 3 funkcja zwraca 3 kwartyl, a 4 zwraca maksymalną wartość ze zbioru. b) Argumentami funkcji UFNOŚĆ są trzy parametry: poziom istotności ą, który często przyjmują jako 0.05 (poziom ufności odpowiednio 0.95, a współczynnik uą w tym przypadku jest równy 1.96) , odchylenie standardowe empiryczne oraz liczebność próbki . Jednak funkcja ta zwraca połowę długości przedziału ufności. W całości długość przedziału ufności w naszym przykładzie wynosi 6.2, a zatem przedział: [21, 27.2]. Zwrócić uwagę, że funkcja wykonuje obliczenia wg wzoru 2.40 (dla rozkładu normalnego), a nie ze wzoru 2.41(dla rozkładu t-Studenta), co byłoby prawidłowej. c) Funkcja CZSTOŚĆ zwraca liczebności poszczególnych klas o podanych granicach. Funkcja ta ma dwa parametry: zakres danych i zakres granic przedziałów (klas). Pozwala to na zbudowanie histogramu rozkładu empirycznego. Budowanie histogramu rozkładu empirycznego Najpierw określamy najmniejszą i największą wartości z zakresu A1:A30. Korzystając z funkcji MIN i MAX odpowiednio otrzymujemy: 8.9cm i 41.2cm. Stąd mamy rozstęp: L= 41.2-8.9 =32.3cm. Ponieważ danych jest niewiele, to przyjmujemy ilość klas 5. Wtedy długość klasy wynosi: "L=32.3/5=6.46. Dalej wyznaczamy granicę poszczególnych klas dodając długości "L do minimalnej wartości: 15.36, 21.82, 28.28, 34.74 (ilość granic jest o 1 mniej niż ilość klas). Granice te zostały wpisane do zakresu C26:C29 (patrz rys. 3.15). Teraz możemy wyznaczyć liczebności klas, tj. obliczyć, ile wartości występuje w każdym przedziale. Wcześniej robiono to ręcznie metodą kreskową, teraz pomoże nam funkcja CZSTOŚĆ. Ponieważ dane znajdują się w komórkach A1:A30, a wartości granic w obszarze C26:C29, to funkcja ta odpowiednio będzie miała postać: 151 Konstanty Wiatkin, Podstawy matematyki i statystyki dla geografów, Instytut Geografii Akademii Bydgoskiej, www.geo.ab.edu.pl CZSTOŚĆ(A1:A30;C26:C29). Po kliknięciu na przycisku Wklej funkcję i wyborze funkcji CZSTOŚĆ pojawi się panel, w oknach którego wpisujemy zakresy danych i granic przedziałów. Zobaczymy w panelu wynik: ={5\7\9\5\4} (patrz rys.3.14). Właśnie są to liczebności poszczególnych klas. Rys. 3.14. Panel zawierający wprowadzone zakresy i wyniki funkcji CZSTOŚĆ. Teraz możemy zbudować histogram wykres kolumnowy. Wprowadzmy do arkusza dane w takiej postaci: Tabela 3.7 <=15.4 15.5-21.8 21.9-28.3 28.4-34.7 >34.7 5 7 9 5 4 Tworzymy wykres jak pokazano wyżej w podrozdziale 3.3 wybierając typ kolumnowy i zaznaczając jako zakres całą tablicę. Rozkład średnic drzew 10 9 8 7 6 5 4 3 2 1 0 <=15.4 15.5-21.8 21.9-28.3 28.4-34.7 >34.7 Granice poszczególnych klas Rys 3.15. Histogram rozkładu empirycznego. 152 Konstanty Wiatkin, Podstawy matematyki i statystyki dla geografów, Instytut Geografii Akademii Bydgoskiej, www.geo.ab.edu.pl 3.5 Przykłady zastosowania arkusza kalkulacyjnego w ćwiczeniach z hydrografii A. Budowanie wykresu codziennych stanów wody na podstawie danych z roczników hydrograficznych. Zadanie polega na zbudowaniu wykresu na podstawie danych pobranych z "Rocznika hydrograficznego dorzecza Wisły, rok 1981" . Są to dane codziennych stanów wody na rzece Skawa w roku 1981 w profilu Jordanów. Do arkusza kalkulacyjnego wprowadzamy dane jak pokazano niżej: Tabela 3.8 Miesiące w układzie roku hydrograficznego Dni XI XII I II III IV V VI VII VIII IX X 1 192 184 198 186 185 187 187 198 189 184 188 184 2 189 188 196 187 185 186 190 192 188 183 188 183 3 188 190 194 188 185 185 186 188 186 184 186 183 4 188 190 230 193 186 185 185 186 185 183 185 183 5 186 188 210 210 186 185 185 184 185 182 184 183 6 186 189 208 197 185 185 186 185 185 182 184 182 7 192 188 201 192 184 184 186 186 184 181 183 182 8 196 187 194 212 210 184 184 186 184 181 182 182 9 192 186 194 250 250 184 183 191 183 182 182 182 10 200 186 196 226 245 183 183 185 183 182 182 182 11 196 186 196 218 230 184 192 205 182 192 196 182 12 184 192 196 202 258 183 181 192 182 185 189 184 13 191 224 197 198 239 183 181 192 182 184 186 183 14 186 210 196 194 224 183 184 188 182 182 188 184 15 181 223 195 192 213 183 182 201 182 181 241 185 16 196 230 194 190 206 183 181 200 185 181 257 187 17 224 211 192 190 210 183 181 206 183 191 218 187 18 211 201 191 192 206 183 182 185 199 189 204 185 19 202 197 191 188 201 183 181 218 196 184 197 184 20 199 195 190 187 198 183 184 216 194 182 193 185 21 196 194 188 187 196 183 184 202 200 182 191 184 22 198 192 186 187 194 182 182 196 196 188 188 183 23 196 191 186 185 193 182 181 192 189 188 186 183 24 193 190 185 185 192 182 181 191 185 201 186 183 25 192 190 186 184 192 182 181 229 183 203 185 188 153 Konstanty Wiatkin, Podstawy matematyki i statystyki dla geografów, Instytut Geografii Akademii Bydgoskiej, www.geo.ab.edu.pl 26 191 192 186 184 192 182 180 220 205 202 185 186 27 190 197 184 183 206 182 180 210 192 205 184 192 28 190 194 184 183 192 181 181 202 187 206 184 190 29 189 192 186 190 182 212 196 190 203 183 188 30 188 193 188 190 182 206 192 188 194 183 186 31 194 188 188 218 186 191 185 NW 181 184 184 183 184 181 180 184 182 181 182 182 SW 193 196 194 195 204 183 185 197 188 188 192 185 WW 224 230 230 250 258 187 212 229 205 206 257 192 Po wprowadzeniu danych korzystając z odpowiednich funkcji wyznaczamy wartości minimalne i maksymalne oraz obliczamy wartości średnie stanów wód. Umieszczamy te dane w trzech wierszach poniżej danych, gdzie: NW - niska woda, poziom najmniejszy w danym okresie SW - średnia woda, średnia arytmetyczna poziomów w danym okresie WW - wysoka woda, poziom największy w danym okresie Następnie obliczamy: Minimalny poziom wody w roku - 180 cm, Średni roczny poziom wody - 192 cm, Maksymalny poziom wody w roku - 258 cm W odróżnieniu od poprzednich przykładów, tu dane potrzebne do sporządzenia wykresu zgrupowane są w 12 kolumnach. Jeżeli zaznaczymy całą tablicę jako zakres danych, to EXCEL utworzy nam 12 serii wykresów, jednak naszym zadaniem jest zbudowanie wykresu ciągłego, tzn. wartości powinny być wykreślone kolejno "kolumna po kolumnie". Do budowania takiego wykresu można zastosować różne sposoby, np. skorzystać z opcji "Dodaj dane", ale z tym mogą być kłopoty, ponieważ EXCEL po wprowadzeniu danych z pierwszej kolumny kolejne dane wprowadzane z drugiej kolumny będzie traktował jako drugą serię. Aby tego uniknąć można zalecać inne rozwiązanie. Po wyborze wykresu (liniowy) zaznaczamy zakres zawierający dane w pierwszej kolumnie (dla nas to kolumna XI). Mamy już wykres dla pierwszego miesiąca. Dalej przechodzimy do kartki "Serie" i w oknie "Wartości" 154 Konstanty Wiatkin, Podstawy matematyki i statystyki dla geografów, Instytut Geografii Akademii Bydgoskiej, www.geo.ab.edu.pl dopisujemy na końcu wiersza średnik i zaznaczamy myszką dane z drugiej, sąsiedniej kolumny - XII. Powtarzamy to samo dla pozostałych kolumn, ale po wprowadzeniu wszystkich danych średnika na końcu nie stawiamy, inaczej EXCEL będzie oczekiwał wprowadzenia następnej porcji danych. Dalej klikamy na "Zakończ". Aby wykres miał wygląd jak na rys. 3.17 wykonujemy następujące czynności: - likwidujemy legendę, wpisujemy tytuły i ustalamy żądane rozmiary czcionek, - zmieniamy podziałkę na osi "Dni". W tym celu klikamy prawym przyciskiem myszy na osi, dalej w menu podręcznym wybieramy "Formatuj osie& " i na kartce "Skala" ustalamy "Liczba kategorii pomiędzy etykietami znaczników osi" -30, a "Liczba kategorii pomiędzy znacznikami osi" - 10. - zmieniamy podziałkę na osi "H,cm" tak, aby wykres wyglądał bardziej wyraznie. W tym celu po kliknięciu myszą na tej osi na kartce "Skala" w oknie "Minimum" wpisujemy 180, a w oknie "Maksimum" - 260. - poziom średni roczny w postaci linii przerywanej oraz napis SW nanosimy na wykres ręcznie, korzystając z możliwości opcji "Linia" przy aktywnym pasku "Rysowanie". H,cm 260 250 240 230 220 210 200 190 180 1 31 61 91 121 151 181 211 241 271 301 331 361 Dni Rys. 3.16. Wykres wahań stanów wody na rzece Skawa w profilu Jordanów w roku 1981 155 Konstanty Wiatkin, Podstawy matematyki i statystyki dla geografów, Instytut Geografii Akademii Bydgoskiej, www.geo.ab.edu.pl Wykres ten jest zupełnie prawidłowym, ale hydrolodzy wolą mieć podziałkę osi Dni tak, aby widocznie była wartość stanu wody dla każdego dnia miesiąca. Więc musimy udoskonalić nasz wykres. W tym celu zastosujemy następującą metodę: 1. Rozciągamy wykres w arkuszu tak, aby była widoczna szczegółowa podziałka (co dzień - jedna kreska!). Parametr Liczba kategorii pomiędzy znacznikami osi powinna być ustawiona jako 1. Wykres przy tym staje się dość długi , wiec do wydruku będie potrzebnych kilka kartek. 2. Odliczamy kreski wg liczb dni w miesiącu i nanosimy pionowe linie oddzielające poszczególne miesiące korzystając z graficznych możliwości arkusza (opcja Linia ). Liczenie kresek wygodnie przeprowadzić zaznaczając wykres (klikamy na wykresie), a potem wciskając klawisz strzałka w prawo obserwujemy odliczenie kresek w oknie adresów arkusza. 3. Wstawiamy pole tekstowe poniżej osi Dni i wpisujemy w nim w odpowiednich miejscach: XI,XII,I,II, ... , X. Ukrywamy ramkę pola tekstowego korzystając z opcji rysowania Brak linii . Oto fragment takiego wykresu: Hydrogram stanów w ody na rzece Skaw a w 1981 r. H,cm 260 250 240 230 220 210 200 190 180 Dni XI XII ... miesiace Rys.3.17. Fragment wykresu stanów wody na rzece Skawa. 156 Konstanty Wiatkin, Podstawy matematyki i statystyki dla geografów, Instytut Geografii Akademii Bydgoskiej, www.geo.ab.edu.pl B. Budowanie wykresu częstotliwości stanów wody i wykresu trwania stanów wody wraz ze stanami wyższymi. Danymi zródłowymi są dane z tabeli 3.7. Zadanie polega na budowaniu rozkładu wysokości wody w ciągu roku. Trzeba zbudować histogram (częstotliwość stanów wody) i wykres stanów wody na podstawie skumulowanych wartości dni - wykres czasu trwania stanów wody wraz ze stanami wyższymi. W tym celu zgodnie z zaleceniami w rozdziale II (tabela na stronie 105) określamy ilość klas. Można przyjąć mniejszą ilość klas - 8 z tradycyjnie przyjętym podziałem co 10 cm: 180-189, 190-199, & , 249- 260. Korzystając z funkcji CZSTOŚĆ otrzymujemy częstotliwości w poszczególnych klasach (granicami podziału są liczby: 189,199,& ,249). Dane wprowadzmy do tabeli: Tabela 3.9 Przedziały Stan średni w Częstotliwość Czas trwania stanów przedziale w dniach wraz z wyższymi w (cm) dniach 180-189 185 211 365 190-199 195 96 155 200-209 205 26 59 210-219 215 16 33 220-229 225 7 17 230-239 235 4 10 240-249 245 2 6 250-259 255 4 4 Na podstawie danych budujemy wykres typu "Słupkowy" i doprowadzamy do postaci: 157 Konstanty Wiatkin, Podstawy matematyki i statystyki dla geografów, Instytut Geografii Akademii Bydgoskiej, www.geo.ab.edu.pl C z ę s to tliw o ś ć trw a n ia s ta n ó w w o d y H ,c m 2 5 0 -2 5 9 2 4 0 -2 4 9 2 3 0 -2 3 9 2 2 0 -2 2 9 2 1 0 -2 1 9 2 0 0 -2 0 9 1 9 0 -1 9 9 1 8 0 -1 8 9 D n i 0 5 0 1 0 0 1 5 0 2 0 0 2 5 0 Rys.3.18. Wykres ilustrujący częstotliwość trwania stanów wody. Wykres czasu trwania stanów wody budujemy następująco. Argumentem tu występują dane z kolumny Czas trwania stanów wraz z wyższymi w dniach , przy czym wartości powinny być ułożone w kolejności rosnącej. Odpowiednimi im wartościami zależnymi są dolne granicy przedziałów z kolumny "Przedziały": 250, 240, & , 180. Więc budujemy wykres typu "Punktowy" łącząc punkty krzywą wygładzoną na podstawie danych umieszczonych w tabeli: Tabela 3.10 Czas trwania (dni) 4 6 10 17 33 59 155 365 Stan wody wraz z wyższymi (cm) 250 240 230 220 210 200 190 180 2 5 0 2 4 0 2 3 0 2 2 0 215 2 1 0 2 0 0 1 9 0 1 8 0 25 0 5 0 1 0 0 1 5 0 2 0 0 2 5 0 3 0 0 3 5 0 4 0 0 Ilo ś ć d n i Rys.3.19. Wykres czasu trwania stanów wody wraz ze stanami wyższymi. Na podstawie tego wykresu łatwo wyznaczyć np., że stan wody 215 cm i wyżej trwał około 25 dni w roku. 158 Stan wody (cm) Konstanty Wiatkin, Podstawy matematyki i statystyki dla geografów, Instytut Geografii Akademii Bydgoskiej, www.geo.ab.edu.pl C. Obliczanie wielkości odpływu wody. Wpisujemy do tabeli dane z dokonanych własnych pomiarów przepływu wody lub odczytanych z publikacji Wyniki pomiarów hydrometrycznych tak, jak pokazano niżej: Tabela 3.11 Odpływ Q[m^3/s] 15 25 26 35 35 38 50 Stan wody H[cm] 422 430 452 450 455 466 475 Na podstawie tych danych tworzymy wykres punktowy (patrz rozdział 3.2 i rys.3.9) dla zależności H=H(Q). Następnie budujemy krzywą konsumpcyjną. W tym celu przy uaktywnionym wykresie wchodzimy do opcji Dodaj linię trendu... i na kartce Typ wybieramy typ linii (regresji), która z punktu widzenia badacza w najlepszym stopniu odpowiada charakterowi wykresu. Na kartce Opcje zaznaczamy 2 opcję: Wyświetl równanie na wykresie oraz Wyświetl wartości R-kwadrat na wykresie . Jest to współczynnik determinacji. Dla jednej serii danych można wyświetlić jednocześnie kilka typów linii regresji i porównać wartości R2 dla każdej z nich. Lepszą będzie ta linia, dla której wartość R2 jest największa. W naszym przypadku spośród zależności liniowej, kwadratowej i logarytmicznej wybrana została zależność kwadratowa (wielomian stopniu 2), mająca największą wartość: R2= 0.8686. Krzywa konsumpcyjna odpływów H=H(Q) H[cm] 480 H = -0,0112Q2 + 2,2735Q + 389,89 470 R2 = 0,8686 460 450 440 430 420 410 0 10 20 30 40 50 60 Q[m^3/s] Rys. 3.20. Zależność stanu wody od przepływów Mając wykres linii konsumpcyjnej H=H(Q) możemy odczytać prognozowaną wielkość przepływu Q przy wybranym stanie wody H. 159