Z-Score to wartość statystyczna, która mówi, ile odchyleń standardowych przypada na określoną wartość od średniej z całego zbioru danych. Możesz użyć formuł ŚREDNIA i ODCH.STANDARDOWE lub ODCH.STANDARDOWE.Po obliczeniu średniej i odchylenia standardowego danych, a następnie użyć tych wyników do określenia Z-Score dla każdej wartości.
Spis treści:
Co to jest Z-Score i do czego służą funkcje ŚREDNIA, ODCH.STANDARDOWE i ODCH.STANDARDOWE?
Z-Score to prosty sposób porównywania wartości z dwóch różnych zestawów danych. Jest definiowany jako liczba odchyleń standardowych od średniej, w jakiej znajduje się punkt danych. Ogólna formuła wygląda następująco:
=(DataPoint-AVERAGE(DataSet))/STDEV(DataSet)
Oto przykład, który pomoże wyjaśnić. Powiedzmy, że chcesz porównać wyniki testów dwóch studentów algebry prowadzonych przez różnych nauczycieli. Wiesz, że pierwszy uczeń uzyskał 95% z egzaminu końcowego w jednej klasie, a uczeń z drugiej – 87%.
Na pierwszy rzut oka ocena 95% jest bardziej imponująca, ale co by było, gdyby nauczyciel drugiej klasy zdał 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 obu uczniów może wykazać, ż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ą 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 program 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 obliczające odchylenie standardowe:
ODCH. STANDARDOWE: Ta funkcja jest identyczna jak poprzednia funkcja 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 odstawione i wykorzystane do testów bezpieczeństwa zderzeniowego.
ODCH.STANDARDOWE.P: Ta funkcja oblicza odchylenie standardowe, traktując dane jako całą populację. Cała populacja przypominałaby wszystkie komary na Ziemi lub każdy samochód w serii produkcyjnej określonego modelu.
Wybór zależy od Twojego zbioru danych. Różnica będzie zwykle niewielka, ale wynik funkcji „ODCH.STANDARDOWE.P” zawsze będzie mniejszy niż wynik funkcji „ODCH.STANDARDOWE” 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 („Values” i „Z-Score”) i trzy komórki „pomocnicze” do przechowywania wyników funkcji „ŚREDNIA”, „ODCH.STANDARDOWE” i „ODCH.STANDARDOWE”. Kolumna „Wartości” zawiera dziesięć liczb losowych wyśrodkowanych wokół 500, a kolumna „Z-Score” to miejsce, w którym obliczymy Z-Score, używając wyników przechowywanych w komórkach „pomocniczych”.
Najpierw obliczymy średnią wartości za pomocą funkcji „ŚREDNIA”. Wybierz komórkę, w której zapiszesz wynik funkcji „ŚREDNIA”.
Wpisz poniższą formułę i naciśnij enter -lub- użyj menu „Formuły”.
=AVERAGE(E2:E13)
Aby uzyskać dostęp do funkcji z menu „Formuły”, wybierz menu rozwijane „Więcej funkcji”, wybierz opcję „Statystyki”, a następnie kliknij opcję „ŚREDNIA”.
W oknie Argumenty funkcji wybierz wszystkie komórki w kolumnie „Wartości” jako dane wejściowe dla pola „Liczba1”. Nie musisz martwić się o pole „Numer2”.
Teraz naciśnij „OK”.
Następnie musimy obliczyć odchylenie standardowe wartości za pomocą funkcji „ODCH.STANDARDOWE” lub „ODCH.STANDARDOWE.P”. W tym przykładzie pokażemy, jak obliczyć obie wartości, zaczynając od „ODCH.STANDARDOWE”. Wybierz komórkę, w której zostanie zapisany wynik.
Aby obliczyć odchylenie standardowe za pomocą funkcji „ODCH.STANDARDOWE”, wpisz tę formułę i naciśnij klawisz Enter (lub uzyskaj do niej dostęp przez menu „Formuły”).
=STDEV.S(E3:E12)
Aby uzyskać dostęp do funkcji poprzez menu „Formuły”, wybierz menu rozwijane „Więcej funkcji”, wybierz opcję „Statystyka”, przewiń nieco w dół, a następnie kliknij polecenie „ODCH.STANDARDOWE”.
W oknie Argumenty funkcji wybierz wszystkie komórki w kolumnie „Wartości” jako dane wejściowe dla pola „Liczba1”. Nie musisz się też martwić o pole „Numer2”.
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”, wpisz tę formułę i naciśnij klawisz Enter (lub uzyskaj do niej dostęp przez menu „Formuły”).
= ODCH.STANDARDOWE.P (E3: E12)
Aby uzyskać dostęp do funkcji poprzez menu „Formuły”, wybierz menu rozwijane „Więcej funkcji”, wybierz opcję „Statystyka”, przewiń nieco w dół, a następnie kliknij wzór „ODCH.STANDARDOWE”.
W oknie Argumenty funkcji wybierz wszystkie komórki w kolumnie „Wartości” jako dane wejściowe dla pola „Liczba1”. Ponownie nie musisz martwić się o pole „Numer2”.
Teraz naciśnij „OK”.
Teraz, gdy obliczyliśmy średnią i odchylenie standardowe naszych danych, mamy wszystko, czego potrzebujemy, aby obliczyć Z-Score. Możemy użyć prostej formuły, która odwołuje się do komórek zawierających wyniki funkcji „ŚREDNIA” i „ODCH.STANDARDOWE” lub „ODCH.STANDARDOWE.P”.
Wybierz pierwszą komórkę w kolumnie „Z-Score”. W tym przykładzie użyjemy wyniku funkcji „ODCH.STANDARDOWE”, 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)/$H$3
Alternatywnie możesz wykonać następujące czynności, aby wprowadzić formułę zamiast wpisywać:
Kliknij komórkę F3 i wpisz = (
Wybierz komórkę E3. (Możesz jeden raz nacisnąć lewy klawisz strzałki lub użyć myszy)
Wpisz znak minus –
Wybierz komórkę G3, a następnie naciśnij klawisz F4, aby dodać znaki „$”, aby utworzyć „bezwzględne” odniesienie do komórki (będzie to cykliczne oznaczanie „G3”> „$ G $ 3 ″>„ G $ 3 ″> „$ G3 ″>„ G3 ” ”Jeśli będziesz dalej naciskać F4)
Rodzaj )/
Wybierz komórkę H3 (lub I3, jeśli używasz „STDEV.P”) i naciśnij klawisz F4, aby dodać dwa znaki „$”.
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 wyniki Z pozostałych wartości. Podświetl całą kolumnę „Z-Score”, zaczynając od komórki zawierającej formułę.
Naciśnij Ctrl + D, co spowoduje skopiowanie formuły z górnej komórki w dół przez wszystkie inne wybrane komórki.
Teraz formuła została „wypełniona” we wszystkich komórkach i każda z nich będzie zawsze odnosić się do właściwych komórek „ŚREDNIA” i „ODCH.STANDARDOWE” lub „ODCH.STANDARDOWE.P” ze względu na znaki „$”. Jeśli wystąpią błędy, wróć i upewnij się, że we wpisanej formule znajdują się znaki „$”.
Obliczanie Z-Score bez użycia komórek „pomocniczych”
Komórki pomocnicze przechowują wynik, podobnie jak komórki przechowujące wyniki funkcji „ŚREDNIA”, „ODCH.STANDARDOWE” i „ODCH.STANDARDOWE.P”. Mogą być przydatne, ale nie zawsze są konieczne. Możesz je całkowicie pominąć podczas obliczania Z-Score, używając zamiast tego następujących uogólnionych formuł.
Oto jeden z funkcją „ODCH.STANDARDOWE”:
=(Value-AVERAGE(Values))/STDEV.S(Values)
I jeden z funkcją „STEV.P”:
=(Value-AVERAGE(Values))/STDEV.P(Values)
Wprowadzając zakresy komórek dla „Wartości” w funkcjach, pamiętaj o dodaniu odwołań bezwzględnych („$” za pomocą klawisza F4), aby podczas „wypełniania” nie obliczać średniej ani 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 wyniku funkcji „ŚREDNIA” i „ODCH.STANDARDOWE” lub „ODCH.STANDARDOWE” za każdym razem, 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 (3 $ E $: 12 $ E)”. 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ć).