Łatwa obsługa, wszechstronność i intuicyjność arkuszy kalkulacyjnych sprawiają, że są one powszechnie stosowane do gromadzenia, porządkowania i przetwarzania danych liczbowych oraz tekstowych. Sprawnemu wprowadzaniu danych sprzyjają liczne mechanizmy automatyzujące wypełnianie komórek arkuszy oraz łatwy import danych zapisanych w plikach o różnorodnych formatach. W laboratoriach badawczych i wzorcujących arkusze mogą one być używane zarówno do wykonywania prostych obliczeń, sporządzania zestawień i raportów, jak i do zaawansowanych analiz, zgłębiania danych, obliczeń symulacyjnych, testowania hipotez badawczych i formułowania prognoz.
Najpowszechniej stosowanym obecnie arkuszem kalkulacyjnym jest Excel, wyposażony w liczne i bardzo użyteczne funkcje statystyczne. Praktyka pokazuje jednak, że w większości laboratoriów wykorzystuje się tylko znikomą część możliwości, jakie daje to rozbudowane narzędzie. Jednym z powodów takiej sytuacji jest zapewne niedopracowany opis funkcji, szczególnie w polskojęzycznej wersji pakietu, gdzie występuje wiele błędów i niepoprawnie stosowanych terminów statystycznych, komentarze do przykładów są zdawkowe a nawet mylące, często brakuje jakichkolwiek objaśnień.
Warto więc zwrócić uwagę na możliwości Excela, a także na pułapki grożące użytkownikom. W kolejnych krokach zostaną poruszone następujące kwestie:
a) obliczanie wybranych parametrów statystycznych,
b) tworzenie wykresów szczególnie użytecznych w analizach laboratoryjnych,
c) posługiwanie się funkcjami opisującymi modele matematyczno-statystyczne,
d) testowanie hipotez statystycznych.
W niniejszym tekście odniesiemy się do obliczania najczęściej stosowanej miary rozproszenia wyników pomiaru, jakim jest odchylenie standardowe.
Zapewne niewielu użytkowników pakietu Excel wie, że część algorytmów obliczeniowych zaimplementowanych w wersjach MS Office 2010 i MS Office 2013 dość istotnie różni się od swych odpowiedników stosowanych w poprzednich edycjach. Wprowadzono znaczne zmiany mające na celu poprawę dokładności obliczeń i ułatwienie analizy danych. W trosce o zachowanie kompatybilności pakietów MS Excel 2010 oraz MS Excel 2013 z poprzednimi wersjami tego programu, funkcje statystyczne, które uzyskały udoskonalone odpowiedniki, zostały przeniesione do specjalnie utworzonej grupy funkcji o nazwie Zgodność. Producent pakietu zachęca do korzystania z nowego, poszerzonego zestawu funkcji umiejscowionych w grupie o nazwieStatystyczne.
 |
Dla użytkowników polskich, którzy posługiwali się wcześniejszymi wersjami programu, nazwy niektórych funkcji nowych wersji Excela mogą być dezorientujące i stać się źródłem pomyłek. Nierzadko bowiem brakuje konsekwencji i porządku, czego przykładem są nazwy funkcji służących do wyznaczania odchylenia standardowego.
Wartość odchylenia standardowego jest wyznaczana na podstawie wszystkich danych w zbiorze. Służy do wyrażenia stopnia rozproszenia danych liczbowych (np. wyników pomiaru) względem średniej arytmetycznej. Jak wiadomo,wyróżnia się dwa rodzaje odchylenia standardowego: odchylenie standardowe próbki i odchylenie standardowe populacji, które są powiązane ścisłą zależnością matematyczną. Dla zbioru zawierającego n wartosci odchylenie standardowe próbki jest równe iloczynowi odchylenia standardowego populacji przez pierwiastek kwadratowy z ilorazu n/(n-1).
Obliczając odchylenie standardowe korzystając z Excela, należy zachować uwagę, aby spośród kilku funkcji o bardzo podobnych nazwach wybrać odpowiednią do danego celu. W grupie funkcji o nazwie Statystyczne znajdziemy cztery funkcje dotyczące odchylenia standardowego: ODCH.STAND.POPUL, ODCH.STANDARD.POPUL.A, ODCH.STANDARD.PRÓBKI, ODCH.STANDARDOWE.A. Zaś w grupie Zgodność znajdują się dwie funkcje przeniesione z wcześniejszych wersji Excela: ODCH.STANDARD.POPUL, której udoskonalonym odpowiednikiem jest ODCH.STAND.POPUL oraz ODCH.STANDARDOWE, które zastąpiła dokładniejsza funkcja ODCH.STANDARD.PRÓBKI.
Czym należy kierować się przy wyborze funkcji wyznaczającej odchylenie standardowe?
Po pierwsze, tworząc nowe arkusze, nie stosujmy już funkcji z grupy Zgodność. Po drugie, wybór funkcji zależy od zawartości komórek, na których będą dokonywane obliczenia. Najczęściej w laboratorium komórki te zawierają liczby lub są puste. W takiej sytuacji do obliczenia odchylenia standardowego populacji służy funkcja ODCH.STAND.POPUL a do obliczenia odchylenia standardowego próbki ODCH.STANDARD.PRÓBKI. Jeśli w którychś komórkach znajdzie się jednak tekst, komórki te będą traktowane tak samo jak puste, a więc ignorowane.
W dość rzadkich przypadkach do komórek wprowadza się funkcje logiczne PRAWDA lub FAŁSZ. Zastosowanie do takiego obszaru funkcji zawierających na końcu literę A, tzn. ODCH.STANDARD.POPUL.A lub ODCH.STANDARDOWE.A sprawi, że PRAWDA będzie traktowana jak 1, zaś FAŁSZ a także dowolny tekst jako 0.
Przykład 1
Jeśli komórki od A1 do A7 zawierają kolejno: 1; 2; 3; 4; 5; a; b, to:
=ODCH.STAND.POPUL(A1:A7) zwraca 1,414
=ODCH.STANDARD.PRÓBKI(A1:A7) zwraca 1,581
=ODCH.STANDARD.POPUL.A(A1:A7) zwraca 1,807
=ODCH.STANDARDOWE.A(A1:A7) zwraca 1,952.
Pierwsze dwie funkcje wykonują obliczenia tylko na podstawie komórek A1:A5, a kolejne dwie na całym zbiorze, przy czym do komórek A6 i A7 jest podstawiane 0.
Literatura:
[1] J. C. Nash: Teaching statistics with Excel 2007 and other spreadsheets. Computational Statistics & Data Analysis, vol.52, 2008, 4602–4606.
[2] A. Talha Yalta: The accuracy of statistical distributions in Microsoft® Excel 2007. Computational Statistics & Data Analysis, vol. 52, 2008, 4579–4586.
[3] Yu-Sung Su: It’s easy to produce chartjunk using Microsoft®Excel 2007 but hard to make good graphs. Computational Statistics & Data Analysis, vol. 52, 2008, 4594–4601.
[4] Żebrowska-Łucyk S.: Analiza wyników badań laboratoryjnych z wykorzystaniem arkuszy kalkulacyjnych XII Sympozjum Klubu POLLAB „Doskonalenie systemu zarządzania w laboratorium”. Ustroń, 10-12.2006, Mat konf. 39-51.
[5] Żebrowska-Łucyk S.: Przedziały ufności dla krzywych kalibracyjnych a niepewność pomiarów. Mat. Symp. Metrologia w systemach zarządzania – 6. Dymaczewo k/Poznania, 15-17.09.2010. Wyd. Polskie Forum ISO 9000, Warszawa 2010, 121-129.
[6] PN-ISO 5725:2002 Dokładność (poprawność i precyzja) metod pomiarowych i wyników pomiarów. Części 1-6.
[7] ISO 13528:2005 Statistical methods for use in proficiency testing by interlaboratory comparisons.
Autor artykułu:
prof. nzw. dr hab. inż. Sabina Żebrowska - Łucyk - Politechnika Warszawska, Instytut Metrologii i Systemów Pomiarowych. Wieloletni wykładowca metrologii i metod statystycznych
