Jak obliczyć wynik Z za pomocą programu Microsoft Excel

Wynik Z jest wartością statystyczną, która określa, ile odchyleń standardowych ma konkretna wartość od średniej z całego zestawu danych. Możesz użyć formuł AVERAGE i STDEV.S lub STDEV.P, aby obliczyć średnie i standardowe odchylenie danych, a następnie użyć tych wyników do określenia wyniku Z dla każdej wartości.

Co to jest Z-Score i co robią funkcje AVERAGE, STDEV.S oraz STDEV.P?

Wynik Z jest prostym sposobem porównywania wartości z dwóch różnych zestawów danych. Definiuje się ją jako liczbę odchyleń standardowych od średniej, z której wynika punkt danych. Ogólna formuła wygląda następująco:

= (DataPoint-AVERAGE (DataSet)) / STDEV (DataSet)

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

Na pierwszy rzut oka ocena na poziomie 95% jest bardziej imponująca, ale co, jeśli nauczyciel drugiej klasy podjął trudniejszy egzamin? Można obliczyć wynik Z każdego wyniku ucznia na podstawie średnich wyników w każdej klasie i standardowego odchylenia wyników w każdej klasie. Porównywanie wyników Z dwóch uczniów może wykazać, że uczeń z wynikiem 87% wypadł lepiej w porównaniu do reszty swojej klasy, niż uczeń z wynikiem 98% w porównaniu z resztą klasy.

Pierwsza wymagana wartość statystyczna to "średnia", a funkcja "ŚREDNIA" Excela 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ą 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ę "STDEV", która oblicza odchylenie standardowe, traktując dane jako "próbkę" populacji. Excel 2010 podzielił to na dwie funkcje, które obliczają odchylenie standardowe:

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

Wybór zależy od zestawu danych. Różnica będzie zwykle niewielka, ale wynik funkcji "STDEV.P" zawsze będzie mniejszy niż wynik funkcji "STDEV.S" dla tego samego zestawu danych. Jest to bardziej konserwatywne podejście zakładające, że dane są bardziej zmienne.

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", "STDEV.S" i "STDEV.P". Kolumna "Wartości" zawiera dziesięć losowych liczb zcentralizowanych około 500, a kolumna "Z-Score" to miejsce, w którym obliczymy wynik Z za pomocą wyników przechowywanych w komórkach "pomocnika".

Najpierw obliczymy średnią wartości za pomocą funkcji "ŚREDNIA". Wybierz komórkę, w której będzie przechowywany wynik funkcji "ŚREDNIA".

Wpisz następującą formułę i naciśnij wchodzić -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ę "Statystyczna", a następnie kliknij "ŚREDNIA".

W oknie Argumenty funkcji zaznacz wszystkie komórki w kolumnie "Wartości" jako dane wejściowe dla pola "Numer 1". Nie musisz się martwić o pole "Numer2".

Teraz naciśnij "OK".

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

Aby obliczyć odchylenie standardowe za pomocą funkcji "STDEV.S", wpisz tę formułę i naciśnij Enter (lub uzyskaj dostęp do niej za pośrednictwem menu "Formuły").

= STDEV.S (E3: E12)

Aby uzyskać dostęp do funkcji za pośrednictwem menu "Formuły", wybierz menu "Więcej funkcji", wybierz opcję "Statystyka", przewiń w dół, a następnie kliknij polecenie "STDEV.S".

W oknie Argumenty funkcji zaznacz wszystkie komórki w kolumnie "Wartości" jako dane wejściowe dla pola "Numer 1". Nie musisz się też martwić o pole "Numer2".

Teraz naciśnij "OK".

Następnie obliczymy odchylenie standardowe za pomocą funkcji "STDEV.P". Wybierz komórkę, w której wynik będzie przechowywany.

Aby obliczyć odchylenie standardowe za pomocą funkcji "STDEV.P", wpisz tę formułę i naciśnij Enter (lub uzyskaj dostęp do niej za pośrednictwem menu "Formuły").

= STDEV.P (E3: E12)

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

W oknie Argumenty funkcji zaznacz wszystkie komórki w kolumnie "Wartości" jako dane wejściowe dla pola "Numer 1". Ponownie nie musisz się martwić o pole "Numer2".

Teraz naciśnij "OK".

Po obliczeniu średniej i odchylenia standardowego naszych danych mamy wszystko, czego potrzebujemy, aby obliczyć wynik Z. Możemy użyć prostej formuły odwołującej się do komórek zawierających wyniki funkcji "ŚREDNIA" i "STDEV.S" lub "STDEV.P".

Wybierz pierwszą komórkę w kolumnie "Z-Score". W tym przykładzie użyjemy wyniku działania funkcji "STDEV.S", ale można również użyć wyniku z "STDEV.P".

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

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

Możesz też wykonać następujące kroki, aby wprowadzić formułę, zamiast wpisywać:

1. Kliknij komórkę F3 i wpisz = (
2. Wybierz komórkę E3. (Możesz nacisnąć strzałka w lewo raz lub użyj myszy)
3. Wpisz znak minus -
4. Wybierz komórkę G3, a następnie naciśnij F4 dodać znaki "$", aby utworzyć "bezwzględne" odwołanie do komórki (przejdzie ona przez "G3"> "$sol$3 ">" G$3 ">"$G3 ">" G3 ", jeśli kontynuujesz naciskanie F4)
5. Rodzaj ) /
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

Wynik Z został obliczony dla pierwszej wartości. Jest to 0,155945 odchyleń standardowych poniżej średniej. Aby sprawdzić wyniki, można pomnożyć standardowe odchylenie 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 wartości Z pozostałych wartości. Podświetl całą kolumnę "Z-Score" rozpoczynającą się od komórki zawierającej formułę.

Naciśnij Ctrl + D, który kopiuje formułę w górnej komórce do wszystkich innych wybranych komórek.

Teraz formuła została "wypełniona" dla wszystkich komórek, a każda z nich zawsze będzie odwoływać się do prawidłowych komórek "ŚREDNIA" i "STDEV.S" lub "STDEV.P" z powodu znaków "$". Jeśli pojawią się błędy, wróć i upewnij się, że znaki "$" są zawarte we wprowadzonej formule.

Obliczanie Z-Score bez użycia komórek pomocniczych

Komórki pomocnicze przechowują wyniki, takie jak te przechowujące wyniki funkcji "ŚREDNIA", "STDEV.S" i "STDEV.P". Mogą być przydatne, ale nie zawsze są konieczne. Możesz je pominąć podczas obliczania Z-Score, używając zamiast tego uogólnionych formuł.

Oto jedna z funkcji "STDEV.S":

= (Wartość - AVERAGE (wartości)) / STDEV.S (wartości)

I jeden używający funkcji "STEV.P":

= (Wartość - AVERAGE (wartości)) / STDEV.P (wartości)

Wprowadzając zakresy komórek dla "Wartości" w funkcjach, pamiętaj, aby dodać bezwzględne odniesienia ("$" używając F4), aby po "wypełnieniu" nie obliczać średniej lub standardowego odchylenia innego zakresu komórek w każdej formule.

Jeśli masz duży zestaw danych, może być bardziej efektywne korzystanie z komórek pomocniczych, ponieważ nie oblicza on wyniku funkcji "ŚREDNIA" i "STDEV.S" lub "STDEV.P" za każdym razem, oszczędzając zasoby procesora i przyspieszenie czasu potrzebnego do obliczenia wyników.

Ponadto "$ G $ 3" ​​zajmuje mniej bajtów do zapisania i mniej pamięci RAM do załadowania niż "AVERAGE ($ 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ń co do ilości pamięci RAM).