[Excel] 엑셀로 Z-score 표준화시키는 방법

문제 상황

아래의 A열과 B열의 값들을 각각 z-score로 표준화시켜야 한다.

 

 

해결 방법

1. Z-score?

z-점수(z-score)는 데이터가 평균에서 얼마만큼 떨어져 있나를 표준편차를 기준으로 보여주는 통계 값이다.

 

z = (X – μ) / σ

 

여기에서

X = 데이터 값, μ = 평균, σ = 표준편차이다.

 

2. Z-score 계산을 위한 평균, 표준편차 구하기

엑셀에서 z-score를 구하기 위해서는 평균과 표준편차, 두 개의 값을 먼저 계산해야 한다.

 

1) 엑셀로 평균 구하기

평균은 Average 함수로 구한다.

 

2) 엑셀로 표준편차 구하기

 

표준편차는 stdev 함수로 구한다.

 

이렇게 A와 B의 평균과 표준편차를 모두 구했다.

 

3. 앞서 구한 평균, 표준편차로 Z-score 계산하기

 z = (X – μ) / σ

공식을 이용하여 z-score을 구해보자

 

이와 같이 구할 수 있다.

(해당 값을 보여주는 셀 - 평균 셀) / 표준편차 셀

 

중요한 것은, 평균 셀과 표준편차 셀은 F4를 눌러 고정시키고

해당 값을 보여주는 셀은 F4를 누르지 않아서 한 칸씩 내려갈 수 있게 해야 한다는 점이다.

 

계산후 이렇게 아래로 끌어당기거나,

셀의 우측 하단 모서리를 더블클릭해 자동계산되도록 한다.

 

B에 대해서도 똑같이 진행해 주었다. 

B 열에서 해당 값을 나타내는 셀에서 평균 셀을 뺀 후, 표준편차 셀로 나눠준다.

여기에서도 F4는 사진과 같이 걸어주자.

4. z-score 활용하기

z-score 값은 그 수치가 평균에서 표준편차를 단위로 얼마나 떨어져 있나를 알려준다.

음수 값은 평균보다 좌측에, 양수 값은 평균보다 우측에 놓인 값임을 의미한다.

 

예를 들어 A열의 첫째 값인 39591의 경우 평균인 60127.17에서 -0.42 표준편차만큼 떨어져 있다.

평균으로부터 더 멀리 떨어질수록 절댓값은 더 커진다.