Jak obliczyć wskaźnik Z za pomocą programu Microsoft Excel

Z-Score to wartość statystyczna, która mówi, ile odchyleń standardowych ma dana wartość od średniej całego zestawu danych. Możesz użyć formuł ŚREDNIA i ODCH.STANDARDOWE lub ODCH.STANDARDOWE.P, aby obliczyć średnią i odchylenie standardowe danych, a następnie wykorzystać te wyniki do określenia wyniku Z każdej wartości.

Co to jest wskaźnik Z i do czego służą funkcje ŚREDNIA, ODCH.STANDARDOWE.S i ODCH.STANDARDOWE.P?

Z-Score to prosty sposób porównywania wartości z dwóch różnych zestawów danych. Definiuje się ją jako liczbę odchyleń standardowych od średniej, w której znajduje się punkt danych. Ogólna formuła wygląda tak:

 =(ŚREDNIA Punktu Danych(ZestawDanych))/ODCH.STANDARDOWE(ZestawDanych)

Oto przykład, który pomoże wyjaśnić. Załóżmy, że chcesz porównać wyniki testów dwóch uczniów algebry nauczanych przez różnych nauczycieli. Wiesz, że pierwszy uczeń dostał 95% na egzaminie końcowym w jednej klasie, a uczeń w drugiej klasie 87%.

Na pierwszy rzut oka bardziej imponująca jest ocena 95%, ale co by było, gdyby nauczycielka drugiej klasy dała trudniejszy egzamin? Możesz obliczyć Z-Score każdego ucznia na podstawie średnich wyników w każdej klasie i odchylenia standardowego wyników w każdej klasie. Porównanie wyników Z dwóch uczniów może ujawnić, że uczeń z wynikiem 87% radził sobie lepiej w porównaniu z resztą swojej klasy niż uczeń z wynikiem 98% w porównaniu z resztą swojej klasy.

Pierwszą potrzebną wartością statystyczną jest „średnia”, a funkcja „ŚREDNIA” programu Excel oblicza tę wartość. Po prostu sumuje wszystkie wartości w zakresie komórek i dzieli tę sumę przez liczbę komórek zawierających wartości liczbowe (ignoruje puste komórki).

Inną potrzebną nam wartością statystyczną jest „odchylenie standardowe”, a Excel ma dwie różne funkcje do obliczania odchylenia standardowego w nieco inny sposób.

Poprzednie wersje programu Excel miały tylko funkcję „ODCH.STANDARDOWE”, która oblicza odchylenie standardowe, traktując dane jako „próbkę” populacji. Excel 2010 podzielił to na dwie funkcje, które obliczają odchylenie standardowe:

• ODCH.STANDARDOWE.S: Ta funkcja jest identyczna z poprzednią funkcją „ODCH.STANDARDOWE”. Oblicza odchylenie standardowe, traktując dane jako „próbkę” populacji. Próbka populacji może być czymś w rodzaju konkretnych komarów zebranych w ramach projektu badawczego lub samochodów, które zostały odłożone na bok i użyte do testów bezpieczeństwa zderzeniowych.
• ODCH.STANDARDOWE.P: Ta funkcja oblicza odchylenie standardowe, traktując dane jako całą populację. Cała populacja byłaby czymś w rodzaju wszystkich komarów na Ziemi lub każdego samochodu w serii produkcyjnej określonego modelu.

Wybór zależy od zestawu danych. Różnica będzie zwykle niewielka, ale wynik funkcji „ODCH.STANDARDOWE.P” będzie zawsze mniejszy niż wynik funkcji „ODCH.STANDARDOWE.S” dla tego samego zestawu danych. Bardziej konserwatywnym podejściem jest założenie, że dane są bardziej zróżnicowane.

Spójrzmy na przykład

W naszym przykładzie mamy dwie kolumny („Wartości” i „Wynik Z”) oraz trzy komórki „pomocnicze” do przechowywania wyników funkcji „ŚREDNIA”, „ODCH.STANDARDOWE.S” i „ODCH.STANDARDOWE.P”. Kolumna „Wartości” zawiera dziesięć losowych liczb o środku wokół 500, a kolumna „Z-Score” to miejsce, w którym obliczymy Z-Score na podstawie wyników zapisanych w komórkach pomocniczych.

Najpierw obliczymy średnią z wartości za pomocą funkcji „ŚREDNIA”. Wybierz komórkę, w której będziesz przechowywać wynik funkcji „ŚREDNIA”.

Wpisz następującą formułę i naciśnij enter -lub- użyj menu „Formuły”.

 =ŚREDNIA(E2:E13)

Aby uzyskać dostęp do funkcji za pomocą menu „Formuły”, wybierz menu „Więcej funkcji”, wybierz opcję „Statystyczne”, a następnie kliknij „ŚREDNIA”.

W oknie Argumenty funkcji wybierz wszystkie komórki w kolumnie „Wartości” jako dane wejściowe dla pola „Numer1”. Nie musisz się martwić o pole „Numer2”.

Teraz naciśnij „OK”.

Następnie musimy obliczyć odchylenie standardowe wartości za pomocą funkcji „ODCH.STANDARDOWE.S” lub „ODCH.STANDARDOWE.P”. W tym przykładzie pokażemy, jak obliczyć obie wartości, zaczynając od „STDEV.S”. Wybierz komórkę, w której zostanie zapisany wynik.

Aby obliczyć odchylenie standardowe za pomocą funkcji „ODCH.STANDARDOWE.S”, wpisz tę formułę i naciśnij klawisz Enter (lub przejdź do menu „Wzory”).

 =ODCH.STANDARDOWE.S(E3:E12)

Aby uzyskać dostęp do funkcji za pomocą menu "Formuły", wybierz menu rozwijane "Więcej funkcji", wybierz opcję "Statystyczne", przewiń nieco w dół, a następnie kliknij polecenie "STDEV.S".

W oknie Argumenty funkcji wybierz wszystkie komórki w kolumnie „Wartości” jako dane wejściowe dla pola „Numer1”. Nie musisz się też martwić o pole „Numer2” tutaj.

Teraz naciśnij „OK”.

Następnie obliczymy odchylenie standardowe za pomocą funkcji „ODCH.STANDARDOWE.P”. Wybierz komórkę, w której zostanie zapisany wynik.

Aby obliczyć odchylenie standardowe za pomocą funkcji „ODCH.STANDARDOWE.P”, wpisz tę formułę i naciśnij klawisz Enter (lub przejdź do menu „Wzory”).

=ODCH.STANDARDOWE(E3:E12)

Aby uzyskać dostęp do funkcji za pomocą menu "Formuły", wybierz menu rozwijane "Więcej funkcji", wybierz opcję "Statystyczne", przewiń nieco w dół, a następnie kliknij formułę "STDEV.P".

W oknie Argumenty funkcji wybierz wszystkie komórki w kolumnie „Wartości” jako dane wejściowe dla pola „Numer1”. Ponownie, nie musisz się martwić o pole „Numer2”.

Teraz naciśnij „OK”.

Teraz, gdy obliczyliśmy średnią i odchylenie standardowe naszych danych, mamy wszystko, czego potrzebujemy do obliczenia wskaźnika Z. Możemy użyć prostej formuły, która odwołuje się do komórek zawierających wyniki funkcji „ŚREDNIA” i „ODCH.STANDARDOWE.S” lub „ODCH.STANDARDOWE.P”.

Wybierz pierwszą komórkę w kolumnie „Z-Score”. W tym przykładzie użyjemy wyniku funkcji „ODCH.STANDARDOWE.S”, ale możesz również użyć wyniku z funkcji „ODCH.STANDARDOWE.P.”

Wpisz następującą formułę i naciśnij Enter:

 =(E3-$G$3)/$H3$

Alternatywnie możesz użyć następujących kroków, aby wprowadzić formułę zamiast wpisywać:

1. Kliknij komórkę F3 i wpisz =(
2. Wybierz komórkę E3. (Możesz nacisnąć raz klawisz strzałki w lewo lub użyć myszy)
3. Wpisz znak minus -
4. Wybierz komórkę G3, a następnie naciśnij klawisz F4 , aby dodać znaki „$”, aby utworzyć odwołanie „bezwzględne” do komórki (będzie przechodzić przez „G3” > „ $ G $ 3” > „G $ 3” > „ $ G3” > „G3”, jeśli nadal będziesz naciskać F4 )
5. Typ )/
6. Wybierz komórkę H3 (lub I3, jeśli używasz „STDEV.P”) i naciśnij F4 , aby dodać dwa znaki „$”.
7. naciśnij enter

Z-Score został obliczony dla pierwszej wartości. Jest to 0,15945 odchylenia standardowego poniżej średniej. Aby sprawdzić wyniki, możesz pomnożyć odchylenie standardowe przez ten wynik (6,271629 * -0,15945) i sprawdzić, czy wynik jest równy różnicy między wartością a średnią (499-500). Oba wyniki są równe, więc wartość ma sens.

Obliczmy Z-Score pozostałych wartości. Zaznacz całą kolumnę „Z-Score”, zaczynając od komórki zawierającej formułę.

Naciśnij klawisze Ctrl+D, co spowoduje skopiowanie formuły z górnej komórki w dół przez wszystkie inne zaznaczone komórki.

Teraz formuła została „wypełniona” do wszystkich komórek, a każda z nich zawsze będzie odwoływać się do poprawnych komórek „ŚREDNIA” i „ODCH.STANDARDOWE.S” lub „ODCH.STANDARDOWE.P” ze względu na znaki „$”. Jeśli pojawią się błędy, wróć i upewnij się, że znaki „$” są zawarte we wprowadzonej formule.

Obliczanie wskaźnika Z bez używania komórek pomocniczych

Komórki pomocnicze przechowują wynik, podobnie jak te, które przechowują wyniki funkcji „ŚREDNIA”, „ODCH.STANDARD.POP.” i „ODCH.STANDARDOWE.P”. Mogą być przydatne, ale nie zawsze są konieczne. Podczas obliczania wskaźnika Z można je całkowicie pominąć, używając zamiast tego następujących ogólnych formuł.

Oto przykład wykorzystujący funkcję „ODCH.STANDARDOWE.S”:

 =(Wartość-ŚREDNIA(wartości))/ODCH.STANDARDOWE.S(wartości)

I jeden wykorzystujący funkcję „STEV.P”:

 =(Wartość-ŚREDNIA(wartości))/ODCH.STANDARDOWE.P(wartości)

Wprowadzając zakresy komórek dla „Wartości” w funkcjach, pamiętaj, aby dodać odniesienia bezwzględne („$” za pomocą F4), aby podczas „wypełniania” nie obliczać średniej lub odchylenia standardowego innego zakresu komórek w każdej formule.

Jeśli masz duży zestaw danych, bardziej wydajne może być użycie komórek pomocniczych, ponieważ nie obliczają one za każdym razem wyników funkcji „ŚREDNIA”, „ODCH.STANDARDOWE.S” lub „ODCH.STANDARDOWE.P”, oszczędzając zasoby procesora i przyspieszenie czasu obliczania wyników.

Ponadto „$ G $ 3” zajmuje mniej bajtów do przechowywania i mniej pamięci RAM do załadowania niż „ŚREDNIA ($ E $ 3: $ E $ 12).”. Jest to ważne, ponieważ standardowa 32-bitowa wersja programu Excel jest ograniczona do 2 GB pamięci RAM (wersja 64-bitowa nie ma żadnych ograniczeń dotyczących ilości pamięci RAM, którą można wykorzystać).