Jak obliczyć Z-Score w Excelu

Jak wiecie, istnieje wiele obliczeń matematycznych, które można wykonać w programie Microsoft Excel(Microsoft Excel) . W tym samouczku przeprowadzimy Cię przez kroki związane z obliczaniem wskaźnika Z(Z-Score) w programie Excel .

Jeśli po raz pierwszy używasz programu Excel(Excel) , zalecamy poświęcenie pięciu minut na zapoznanie się z samouczkiem programu Microsoft Excel dla początkujących(Microsoft Excel beginner’s tutorial) . Dowiesz się, jak działają podstawowe funkcje, skróty nawigacyjne, tworzenie skoroszytów, formatowanie danych i wszystko, co trzeba wiedzieć o korzystaniu z arkusza kalkulacyjnego jako początkujący.

Kiedy skończysz, przejdź do następnej sekcji, aby dowiedzieć się, jak obliczyć wynik Z w programie Excel . Ale najpierw wyjaśnijmy krótko Z-Score , jego zastosowania, dlaczego może być konieczne jego obliczenie i jak to zrobić.

Co to jest wskaźnik Z?

Z-Score (znany również jako „Standard Score”) to metryka, która podkreśla związek między wartościami w rozkładzie. Dokładniej opisuje pozycję wartości w zbiorze danych w odniesieniu do ich średniej i odchylenia standardowego. Z-Score pozwala na dokładny pomiar i porównanie wartości w zbiorze danych.

Wzór matematyczny do obliczania Z-Score to (x-µ) / σ ; gdzie x = Cell Value , µ = Mean i σ = Standard Deviation .

Firmy czasami wykorzystują Z-Score do prognozowania i szacowania zbliżającego się bankructwa. Dodatkowo jest to świetna miara do ustalenia sytuacji finansowej instytucji. Badacze(Researchers) wykorzystują również Z-Score do porównywania obserwacji uzyskanych z różnych próbek lub populacji.

Jak obliczyć Z-Score w Excelu

Ponieważ wskaźnik Z(Z-Score) jest funkcją średniej i odchylenia standardowego, musisz najpierw obliczyć średnią i odchylenie standardowe zestawu danych. Chociaż możesz wyodrębnić średnią i odchylenie standardowe w dowolnej komórce, w naszym arkuszu roboczym utworzyliśmy dedykowane kolumny dla „średniej” i „odchylenia standardowego”. Utworzyliśmy również kolumnę „ Z-Score ”.

Nasz przykładowy dokument zawiera oceny wydajności 10 pracowników w firmie papierniczej. Teraz obliczmy Z-Score oceny pracowników.

Oblicz średnią średnią(Calculate Mean Average)

Aby obliczyć średnią średnią zbioru danych, wpisz =AVERAGE( , wybierz pierwszą wartość(first value) w zbiorze danych, naciśnij klawisz kolumny(column key) , wybierz ostatnią wartość(last value) w zakresie zbioru danych, naciśnij klawisz nawiasu zamykającego(closing parenthesis) i naciśnij Enter . Formuła powinna wyglądać jak ten poniżej:

=AVERAGE(B2:B11)

Powinieneś zobaczyć średnią lub średnią wartość zestawu danych w komórce, w której wpisałeś formułę.

Oblicz odchylenie standardowe(Calculate Standard Deviation)

Program Excel ułatwia również obliczenie odchylenia standardowego zestawu danych(calculate the standard deviation of your dataset) za pomocą kilku kliknięć. 

Wybierz komórkę w kolumnie „Odchylenie standardowe”, wpisz =STDEV.P( , a następnie wybierz pierwszą wartość(first value) z zakresu, naciśnij klawisz kolumny(column key) , wybierz ostatnią wartość, wprowadź nawias zamykający(closing parenthesis) i naciśnij Enter . w przypadku wątpliwości otrzymana formuła powinna być podobna do poniższej:

=STDEV.P(B2:B11)

Oblicz Z-Score w Excelu: Metoda 1(Calculate Z-Score in Excel: Method 1)

Program Excel(Excel) ma funkcję STANDARDIZE , która zapewnia wynik Z(Z-Score) zestawu danych w dystrybucji. Wybierz pierwszą komórkę w kolumnie Z-Score i wykonaj poniższe czynności.

  1. Przejdź do zakładki Formuły i wybierz (Formulas)Więcej formuł(More Formulas) .

  1. Najedź myszą na opcję Statystyka i wybierz (Statistical)STANDARYZUJ(STANDARDIZE) .

Spowoduje to uruchomienie nowego okna argumentów funkcji(Function Arguments) , w którym będzie można obliczyć wynik Z(Z-Score) rozkładu.

  1. Wprowadź(Enter) odwołanie do komórki pierwszej wartości w polu „X”.

  1. Wprowadź(Enter) odwołanie do komórki średniej arytmetycznej w polu "Średnia" i naciśnij F4 na klawiaturze, aby zablokować odwołanie do komórki.

  1. Na koniec wprowadź odwołanie do komórki odchylenia standardowego w polu „Odchylenie_standardowe” i naciśnij klawisz F4 , aby zablokować odwołanie do komórki. Narzędzie wyświetli podgląd wartości Z(Z-value) . Naciśnij OK , aby kontynuować.

Aby uzyskać wynik Z(Z-Score) dla innych wartości, umieść kursor w prawym dolnym rogu komórki i przeciągnij plus (+) icon w dół kolumny.

Excel skopiuje formułę w dół kolumny i automatycznie wygeneruje Z-Score dla innych wartości w odpowiednich wierszach.

Oblicz Z-Score w Excelu: Metoda 2(Calculate Z-Score In Excel: Method 2)

Jak wspomniano wcześniej, wynik Z punktu danych można uzyskać, odejmując średnią zestawu danych od punktu danych i dzieląc wynik przez odchylenie standardowe. Używając (x-µ) / σ, możesz obliczyć z-score w Excelu(Excel) , wprowadzając te wartości ręcznie. 

  1. Wybierz pierwszą komórkę w kolumnie Z-Score , wpisz znak równości ( = ), a następnie otwarty nawias(open parenthesis) i wybierz pierwszą wartość w kolumnie zawierającej zestawy danych, których Z-Score chcesz obliczyć. Następnie(Afterward) wpisz łącznik(hyphen) , wybierz średnią arytmetyczną, naciśnij klawisz F4 , aby uzyskać średnią bezwzględną/stałą, a następnie naciśnij ikonę zamykania nawiasów . (close parenthesis)Na koniec naciśnij klawisz ukośnika(forward-slash) ( / ), wybierz odchylenie standardowe i naciśnij klawisz F4 , aby zablokować odwołanie do komórki.

Ostateczna formuła powinna wyglądać podobnie do tego: =(B2-$C$2)/$D$2 . Naciśnij klawisz Enter , aby wykonać formułę.

Zwróć uwagę, że formuła obliczy wynik Z(Z-Score) tylko dla pierwszej wartości w wybranej komórce.

  1. Najedź(Hover) myszą na prawy dolny róg pierwszej komórki Z-Score i przeciągnij plus (+) icon w dół kolumny.

Interpretacja wskaźnika Z

Twój zbiór danych najprawdopodobniej będzie zawierał mieszankę ujemnych i dodatnich wyników Z-Score . Dodatni wynik Z-Score wskazuje, że wartość/wynik jest wyższy niż średnia średnia zestawu danych. Ujemny Z-Score oczywiście mówi coś przeciwnego: wartość leży poniżej średniej średniej. Jeśli Z-Score punktu danych wynosi zero (0), dzieje się tak dlatego, że jego wartość jest równa średniej arytmetycznej.

Im większy punkt danych, tym wyższy jego Z-Score . Przejrzyj swój arkusz, a zdasz sobie sprawę, że małe wartości mają niższe wyniki Z-Score(Z-Score) . Podobnie(Likewise) wartości mniejsze niż średnia arytmetyczna będą miały ujemny wynik Z-Score(Z-Score) .

Na przykład w naszym przykładowym arkuszu zobaczysz, że „Michael” miał najwyższą ocenę (78) i najwyższy wynik Z(Z-Score) (1,679659). Z drugiej strony „Dwight” i „Kevin” mieli najniższe oceny (34) i najniższy Z-Score ( -1.59047 ).

Zostań(Become) ekspertem Excel : repozytorium samouczków

Wiesz już, jak obliczyć Z-Score zbioru danych. Upuść komentarz poniżej, jeśli masz pytania lub inne przydatne wskazówki dotyczące obliczania wskaźnika Z(Z-Score) w programie Excel(Excel)

Zalecamy przeczytanie samouczków związanych z programem Excel na temat obliczania wariancji(calculating variance) , usuwania zduplikowanych wierszy(removing duplicate rows) w zestawie danych oraz używania funkcji podsumowujących w programie Excel(use summary functions in Excel) do podsumowywania danych. 



About the author

Jestem inżynierem komputerowym z ponad 10-letnim doświadczeniem w branży oprogramowania, w szczególności w Microsoft Office. Napisałem artykuły i samouczki na różne tematy związane z pakietem Office, w tym wskazówki, jak efektywniej korzystać z jego funkcji, sztuczki do opanowania typowych zadań biurowych i nie tylko. Moje umiejętności jako pisarza sprawiają, że jestem również doskonałym źródłem informacji dla innych osób, które chcą dowiedzieć się więcej o pakiecie Office lub po prostu potrzebują szybkiej porady.



Related posts