엑셀 각종 수식 계산 방법 모음 세 번째 - 표준편차 구하고(VAR.P, STDEV.P, NORM.DIST) 그래프 그리기

지난 시간에 평균의 함정을 피하기 위해 최빈값과 중간값을 엑셀에서 구해봤습니다.

이번에는 추가로 값의 분포를 한눈에 볼 수 있는 표준편차를 구하는 방법을 알아보겠습니다.

표준편차는 분산의 제곱근입니다.

분산은 값의 분포가 기댓값으로부터 얼마나 떨어져 있는지 나타내줍니다.

평균 → 분산 → 표준편차를 차례대로 구해보겠습니다.

평균에 대한 내용은 엑셀 각종 수식 계산 방법 모음 두 번째(평균, 최빈값, 중간값) (tistory.com) 을 참고해 주시기 바랍니다.

 

목차

1. 엑셀에서 표준편차 구하기
2. 엑셀에서 분산 구하는 함수(VAR.P, VAR.S)
3. 엑셀에서 표준편차 구하는 함수(STDEV.P, STDEV.S)
4. 정규 분포 그래프 그리기(NORM.DIST)

 

 

---

1. 엑셀에서 표준편차 구하기

 

표준편차를 구하기 위해서는 평균을 구하고 평균으로 분산을 계산해야 합니다.

 

엑셀에서 표준편차 구하기 - 평균

 

1. 평균을 구하고자 하는 셀에 수식을 입력합니다.

=AVERAGE(B2:B11)

평균을 구했으면 점수에서 평균을 빼줍니다.

 

 

엑셀에서 표준편차 구하기 - 평균2

 

 

2. C2에 (점수-평균) 수식을 입력합니다.

=B2-$B$12

3. 채우기 핸들을 드래그하여 수식을 채워 넣습니다.

 

엑셀에서 표준편차 구하기 - 제곱

 

4. (점수-평균)의 제곱을 구하는 수식을 D2에 입력합니다.

=POWER(C2,2) 또는 C2^2

5. 채우기 핸들을 드래그하여 수식을 채워 넣습니다.

 

엑셀에서 표준편차 구하기 - 분산

 

6. D열의 평균을 구하는 수식을 D12에 입력합니다.

=AVERAGE(D2:D11)

 

엑셀에서 표준편차 구하기 - 표준편차

 

7. D13열에 분산의 제곱근을 구하는 수식을 입력합니다.

=SQRT(D12)

 

이렇게 어렵게 표준편차 14.36을 구했습니다.

하지만 엑셀에는 분산을 한 번에 구할 수 있는 함수를 제공해 줍니다.

 

 

---

2. 엑셀에서 분산 구하는 함수(VAR.P, VAR.S)

 

분산을 구하기 위한 함수는 두 가지가 있습니다.

모집단 전체의 분산을 계산하는 VAR.P, 표본의 분산을 계산하는 VAR.S입니다.

데이터 전체를 가지고 구하는 경우는 P, 일부만 가지고 계산하는 경우 S입니다.

이러한 접미사는 표준편차에서도 동일합니다.

예시에서는 전체 데이터가 있으니 VAR.P로 분산을 구해보겠습니다.

 

VAR.P(number1,[number2],...)

number1 (필수): 분산을 구하기 위한 첫 번째 숫자, 배열, 셀의 영역이 입력 가능합니다.
number2, ... (선택): number1과 같은 형식으로 총 254개까지 입력 가능합니다.

 

엑셀에서 분산 구하는 함수 - VAR.P

 

전에는 분산을 구하기 위해 많은 단계를 거쳤지만 엑셀에서 제공하는 함수를 이용하면 수식 한 번에 분산을 구할 수 있습니다.

=VAR.P(B2:B11)

결과도 똑같이 205.25인 것이 확인됩니다.

마찬가지로 표준편차도 한번에 구할 수 있습니다.

 

 

---

3. 엑셀에서 표준편차 구하는 함수(STDEV.P, STDEV.S)

표준편차를 구하는 함수도 분산을 구하는 함수처럼 두 가지 종류가 있는데 전체 데이터를 기준으로 하는 경우 STDEV.P를 사용하시면 됩니다.

 

STDEV.P(number1,[number2],...)

number1 (필수): 표준편차를 구하기 위한 첫 번째 숫자, 배열, 셀의 영역이 입력 가능합니다.
number2, ... (선택): number1과 같은 형식으로 총 254개까지 입력 가능합니다.

 

엑셀에서 표준편차 구하는 함수 - STDEV.P

 

역시 셀 B13에 수식만 입력하면 표준편차가 한 번에 계산됩니다.

=STDEV.P(B2:B11)

 

---

4. 정규 분포 그래프 그리기(NORM.DIST)

아무래도 전체 데이터를 한눈에 보려면 정규분포 그래프를 그려보면 좋을 것 같습니다.

지금까지 구한 평균, 표준편차로 정규분포를 그려보겠습니다.

 

정규 분포 그래프 그리기(NORM.DIST) - 구간

 

점수가 5점 단위로 되어 있으니 5씩 증가하는 점수 구간을 만들었습니다.

정규 분포값을 나타낼 수 있는 함수를 사용하여 분포를 구합니다.

 

 

정규 분포 그래프 그리기(NORM.DIST) - 분포

 

1. 정규 분포값을 구하는 수식을 G2에 입력합니다.

    수식을 입력할 때 평균과 표준편차는 고정되어 있는 셀에서 가져오므로 $을 붙여줍니다.

=NORM.DIST(F2,$B$12,$D$15,0)

2. 채우기 핸들을 드래그하여 수식을 채워 넣습니다.

 

정규 분포 그래프 그리기(NORM.DIST) - 그래프

 

3. G1을 클릭하여 선택합니다.(선택 셀과 범위에 따라 x축 이름이 원하는 대로 나오지 않을 수 있습니다.)

4. [삽입] 탭을 클릭합니다.

5. [차트 삽입]을 클릭합니다.

6. 꺾은 선형 그래프를 선택합니다.

7. 확인을 클릭합니다.

 

 

우리가 책에서 보던 그래프가 완성되었습니다.

점수의 분포를 한눈에 볼 수 있습니다.