Statystyki opisowe w Excelu - funkcje, Analiza danych i tabela do pracy

Statystyki opisowe w Excelu policzysz na dwa sposoby. Pierwszy to funkcje wpisywane w komórki: ŚREDNIA, MEDIANA, ODCH.STANDARD.PRÓBKI, KWARTYL.PRZEDZ.OTW, SKOŚNOŚĆ i kilka innych. Drugi to narzędzie Statystyka opisowa z dodatku Analiza danych (Analysis ToolPak), które jednym kliknięciem tworzy gotową tabelę. Funkcje są przejrzyste i przeliczają się same po zmianie danych, a dodatek jest szybszy, ale ma kilka pułapek. Poniżej znajdziesz oba sposoby krok po kroku, przykład na liczbach i wzór tabeli do pracy dyplomowej.

Ten tekst dotyczy techniki liczenia w Excelu. Które miary w ogóle podać, czym jest opis próby i jak go zredagować, omawiamy w tekście statystyka opisowa w pracy dyplomowej. Zanim zaczniesz, upewnij się, że arkusz ma układ „jedna osoba = jeden wiersz, jedna zmienna = jedna kolumna” i że liczby nie są zapisane jako tekst. Jak to zrobić, pokazujemy w poradniku jak przygotować dane w Excelu do analizy.

Statystyki opisowe w Excelu: funkcje czy Analiza danych

Oba sposoby dają te same liczby, różnią się wygodą i kontrolą. Funkcja to formuła: widzisz, z jakiego zakresu liczy, a po poprawieniu błędu w danych wynik zmienia się sam. Narzędzie z dodatku Analiza danych wkleja do arkusza zwykłe liczby, a nie formuły. Jeśli po wygenerowaniu tabeli poprawisz jedną odpowiedź w bazie, wynik się nie zaktualizuje i trzeba uruchomić narzędzie ponownie. To najczęstsze źródło rozbieżności między tabelą w pracy a danymi.

W praktyce dobrze sprawdza się połączenie: dodatek do szybkiego przeglądu wszystkich zmiennych, a funkcje do tabeli, która trafi do pracy.

Funkcje Excela do statystyk opisowych (polskie nazwy)

W polskiej wersji Excela funkcje mają polskie nazwy, a argumenty rozdziela się średnikiem, nie przecinkiem. Jeśli kopiujesz formułę z angielskiego poradnika, musisz zmienić i nazwę, i separator. Poniższe nazwy pochodzą z polskiej dokumentacji Microsoft.

Starsza funkcja ODCH.STANDARDOWE działa nadal i daje ten sam wynik co ODCH.STANDARD.PRÓBKI, ale Microsoft zaleca nowsze nazwy. W pracy dyplomowej prawie zawsze potrzebujesz wersji „z próby”, bo badana grupa jest próbą, a nie całą populacją.

Statystyki opisowe w Excelu krok po kroku (funkcje)

1. Wpisz dane jednej zmiennej w kolumnę, na przykład wyniki skali w komórkach A2:A21, z nazwą zmiennej w A1.
2. Obok, w kolumnie C, wpisz nazwy miar: n, M, Mdn, SD, min, max, Q1, Q3, skośność, kurtoza.
3. W kolumnie D wpisz formuły, za każdym razem z tym samym zakresem, np. =ŚREDNIA(A2:A21), =ODCH.STANDARD.PRÓBKI(A2:A21).
4. Zakres zablokuj znakami dolara ($A$2:$A$21, klawisz F4), jeśli chcesz kopiować formuły.
5. Ustaw liczbę miejsc po przecinku przyciskami na karcie Narzędzia główne, zamiast zaokrąglać ręcznie.
6. Sprawdź, czy ILE.LICZB zgadza się z liczbą respondentów. Jeśli wychodzi mniej, część komórek zawiera tekst albo braki.

Ostatni krok bywa najważniejszy. Funkcje Excela pomijają komórki z tekstem, więc liczba „3,5” wpisana jako tekst po prostu wypadnie z obliczeń, a Ty dostaniesz średnią z mniejszej grupy bez żadnego ostrzeżenia.

Jak włączyć Analizę danych w Excelu

Polecenie Analiza danych pojawia się na karcie Dane dopiero po włączeniu dodatku Analysis ToolPak. Dodatek jest częścią Excela, nie trzeba niczego pobierać.

1. Wybierz Plik > Opcje > Dodatki.
2. Na dole okna, w polu Zarządzaj, wybierz Dodatki programu Excel i kliknij Przejdź.
3. Zaznacz Analysis ToolPak i kliknij OK.
4. Na karcie Dane, po prawej stronie, pojawi się przycisk Analiza danych.

Na komputerze Mac dodatek włączasz w menu Narzędzia > Dodatki programu Excel. Jeśli przycisku nadal nie ma, zamknij i ponownie uruchom Excela.

Narzędzie Statystyka opisowa w dodatku Analiza danych

1. Kliknij Dane > Analiza danych i wybierz Statystyka opisowa.
2. W polu zakresu wejściowego zaznacz kolumnę lub kilka kolumn z danymi, razem z nagłówkami.
3. Zaznacz opcję etykiet w pierwszym wierszu, żeby w wyniku pojawiły się nazwy zmiennych.
4. Wskaż miejsce wyniku: nowy arkusz albo komórkę w obecnym arkuszu.
5. Zaznacz opcję statystyk podsumowania oraz poziom ufności dla średniej (domyślnie 95%).
6. Kliknij OK.

Wynik to kolumna kilkunastu miar: średnia, błąd standardowy, mediana, dominanta, odchylenie standardowe, wariancja próbki, kurtoza, skośność, zakres, minimum, maksimum, suma, liczebność i poziom ufności. W polskiej wersji część etykiet bywa przetłumaczona niezręcznie, na przykład dominanta bywa podpisana jako „Tryb”, a rozstęp jako „Zakres”. Dwie rzeczy wymagają uwagi:

• Wiersz z poziomem ufności nie jest przedziałem ufności, tylko jego połową. Aby dostać przedział, odejmij tę wartość od średniej i dodaj do niej.
• Błąd standardowy to błąd standardowy średniej (SD podzielone przez pierwiastek z n), a nie odchylenie standardowe. Do opisu próby podajesz SD, a błąd standardowy przydaje się dopiero przy wnioskowaniu o średniej.

Przykład: statystyki opisowe w Excelu na danych z ankiety

Przykładowe dane: wyniki skali stresu (zakres 0-40) u 20 studentów, wpisane w komórki A2:A21: 14, 18, 22, 17, 25, 19, 21, 16, 23, 19, 28, 15, 20, 19, 24, 12, 31, 18, 22, 26. Poniżej formuły i wyniki, które da Excel.

W ostatnim wierszu D5 to komórka z odchyleniem standardowym z próby, a D2 z liczebnością. Przedział ufności 95% dla średniej wynosi więc od 20,45 - 2,24 = 18,21 do 20,45 + 2,24 = 22,69. Narzędzie Statystyka opisowa pokazałoby dla tych danych te same wartości średniej, mediany, SD, skośności i kurtozy, błąd standardowy 1,07 i rozstęp 19.

Jak to czytać? Średnia i mediana są blisko siebie, skośność jest niewielka (0,39), a kurtoza bliska zera, więc rozkład jest zbliżony do symetrycznego i średnia z odchyleniem standardowym dobrze go opisuje. Gdyby skośność przekraczała wyraźnie 1, lepiej byłoby podać medianę z kwartylami. Progi i interpretację obu miar omawiamy w tekście skośność i kurtoza.

Kwartyle w Excelu: PRZEDZ.OTW czy PRZEDZ.ZAMK

Excel ma dwie funkcje kwartyli i w przykładzie dają różne wyniki: 17,25 i 23,75 albo 17,75 i 23,25. Żadna nie jest błędna. Programy statystyczne stosują różne definicje kwantyli z próby; Hyndman i Fan (1996) opisali ich dziewięć. KWARTYL.PRZEDZ.OTW liczy pozycję kwartyla jako (n + 1) · p i daje te same wartości co percentyle w procedurze Częstości w SPSS. KWARTYL.PRZEDZ.ZAMK liczy pozycję jako 1 + (n - 1) · p, tak jak domyślna funkcja w R i starsza funkcja KWARTYL.

Jeśli promotor liczy w SPSS, wybierz KWARTYL.PRZEDZ.OTW, wtedy wyniki będą zgodne. Najważniejsze, aby w całej pracy używać jednej metody. Co oznaczają kwartyle i rozstęp międzykwartylowy, wyjaśniamy w osobnym wpisie na blogu.

Statystyki opisowe w Excelu w podziale na grupy

Do porównania grup, na przykład kobiet i mężczyzn, potrzebujesz statystyk osobno dla każdej grupy. Liczebność i średnią policzysz funkcjami warunkowymi: =LICZ.JEŻELI(B2:B21;"K") zwraca liczbę kobiet, a =ŚREDNIA.JEŻELI(B2:B21;"K";A2:A21) ich średni wynik. Dla odchylenia standardowego i mediany nie ma prostych odpowiedników z warunkiem, więc najprościej posortować dane według grupy i policzyć funkcje na dwóch osobnych zakresach. Ten sam sposób działa w dodatku Analiza danych: posortuj dane i wskaż osobno zakres każdej grupy.

Liczebności i odsetki dla zmiennych kategorialnych

Średnia i odchylenie standardowe mają sens tylko dla zmiennych ilościowych. Płeć, kierunek studiów czy miejsce zamieszkania opisujesz liczebnościami i odsetkami. W Excelu robisz to funkcją LICZ.JEŻELI: jeśli w kolumnie B są kody płci, formuła =LICZ.JEŻELI($B$2:$B$21;"K") zwraca liczbę kobiet, a =D2/ILE.NIEPUSTYCH($B$2:$B$21) ich udział, który formatujesz jako procent. Funkcja ILE.NIEPUSTYCH liczy komórki niepuste, także tekstowe, więc nadaje się do kodów słownych, a ILE.LICZB nie.

Przy pytaniach z kilkoma odpowiedziami szybszy bywa wstawiony z karty Wstawianie raport tabeli przestawnej: przeciągasz zmienną do wierszy i do wartości, a w ustawieniach pola wartości wybierasz pokazywanie jako procent sumy kolumny. Pamiętaj tylko, żeby odsetki liczyć od liczby osób, które odpowiedziały na pytanie, i podawać tę liczbę w tabeli. Gdy w zmiennej są braki, 40% z 18 osób to coś innego niż 40% z 20. Jak taki rozkład najlepiej pokazać na rysunku, opisujemy w tekście wykres słupkowy czy kołowy.

Jak zapisać statystyki opisowe w pracy (APA 7)

Do pracy nie wklejasz zrzutu ekranu z Excela ani tabeli z dodatku. Przepisujesz potrzebne miary do tabeli w formacie pracy: z polskim przecinkiem dziesiętnym, jednolitą liczbą miejsc po przecinku i opisem skali w notce. Zwykle wystarczą n, M, SD, mediana, minimum i maksimum, a przy skośnych rozkładach także kwartyle. Dla przykładu tabela wyglądałaby tak:

Tabela 1
Statystyki opisowe wyniku skali stresu

Notka. Wynik skali w zakresie 0-40, wyższy wynik oznacza wyższy stres. Dane przykładowe.

W tekście wystarczy jedno lub dwa zdania, na przykład: „Średni wynik skali stresu wyniósł M = 20,45 (SD = 4,80; 95% CI [18,21; 22,69]). Rozkład był zbliżony do symetrycznego (skośność 0,39)”. Wzory zapisu wyników testów znajdziesz w poradniku jak zaraportować wynik testu w APA 7.

Typowe błędy przy statystykach opisowych w Excelu

• Użycie ODCH.STAND.POPUL zamiast ODCH.STANDARD.PRÓBKI. Różnica jest mała, ale wyniki nie zgodzą się z SPSS.
• Liczby zapisane jako tekst, które funkcje po cichu pomijają. Pomaga kontrola ILE.LICZB.
• Kody braków danych, na przykład 99, wpisane jako zwykłe liczby. Excel policzy je jak prawdziwe odpowiedzi i zawyży średnią.
• Podanie „poziomu ufności” z dodatku jako przedziału ufności.
• Tabela z dodatku niezaktualizowana po poprawkach w danych.
• Mieszanie metod kwartyli w jednej pracy.
• Kropki dziesiętne i angielskie etykiety przeniesione do tabeli w pracy.
• Średnia jako jedyna miara dla wyraźnie skośnej zmiennej, np. dochodu czy czasu.

Kiedy Excel przestaje wystarczać

Statystyki opisowe to mocna strona Excela. Kłopoty zaczynają się przy testach: Excel nie ma testu normalności Shapiro-Wilka, testów nieparametrycznych ani wygodnej ANOVA z post hoc. Jeśli planujesz tylko porównanie dwóch grup, zobacz, jak zrobić test t w Excelu, a przy związku dwóch zmiennych, jak policzyć korelację w Excelu. Jeśli wolisz dostać gotowy opis próby i dobrane analizy w raporcie z tabelami APA, możesz wgrać swój plik Excela po wybraniu analiz w kreatorze wyceny.

Najczęstsze pytania

Jak włączyć Analizę danych w Excelu?

Wybierz Plik, Opcje, Dodatki, w polu Zarządzaj ustaw Dodatki programu Excel i kliknij Przejdź. Zaznacz Analysis ToolPak i zatwierdź. Przycisk Analiza danych pojawi się na karcie Dane.

Którą funkcję odchylenia standardowego wybrać w Excelu?

Do danych z próby, czyli w praktycznie każdej pracy dyplomowej, użyj ODCH.STANDARD.PRÓBKI. Dzieli ona przez n - 1, tak jak SPSS i inne programy statystyczne. ODCH.STAND.POPUL stosuje się tylko wtedy, gdy masz dane o całej populacji.

Dlaczego kwartyle w Excelu różnią się od SPSS?

Excel ma dwie metody liczenia kwartyli. KWARTYL.PRZEDZ.OTW daje te same wartości co percentyle w procedurze Częstości w SPSS, a KWARTYL.PRZEDZ.ZAMK i starsza funkcja KWARTYL liczą inaczej. Wybierz jedną metodę i stosuj ją w całej pracy.

Co oznacza poziom ufności w statystyce opisowej Excela?

To połowa szerokości przedziału ufności dla średniej, a nie sam przedział. Przedział otrzymasz, odejmując tę wartość od średniej i dodając do niej. Ten sam wynik daje funkcja UFNOŚĆ.T.

Czy można policzyć statystyki opisowe dla grup w Excelu?

Tak. Średnią i liczebność w grupie policzysz funkcjami ŚREDNIA.JEŻELI i LICZ.JEŻELI. Dla odchylenia standardowego i mediany najprościej posortować dane według grupy i policzyć funkcje osobno dla każdego zakresu.