엑셀(EXCEL)을 사용해서 정규화 도수분포표 - 히스토그램 그래프 그리기(엑셀에서 미니탭처럼 그리기)

사회나 자연현상으로부터 수집하는 데이터의 분포를 파악하여 산포도로 정리하고는 합니다.

많은 수의 데이터를 파악하는데 도움이 되는 도수분포표(히스토그램 - Histogram)은 아래와 같은 모양으로 그려집니다.

주로 가로층은 계급(Level 혹은 숫자의 범위)와 세로축은 그 수량으로 파악합니다.

 

도수분포표

 

이런 히스토그램은 거의 주로 정규분포를 띌 때가 많습니다.

그래서 정규분포 곡선을 그려서 산포와 비교합니다.

대표적인 예로는 우리 공정산포를 보는 미니탭(Minitap)에서 찾을 수 있습니다.

미니탭에서는 공정산포를 표시할 때 히스토그램 그래프에서 해당 산포가 가지고 있는 정규분포를 함께 표현합니다.

오늘은 엑셀로 어떻게 방법을 사용하는지 알아보겠습니다.

 

 

< 선행해야할 것 >

 

오늘 사용법을 알려드리기는 할 것이지만, FREQUENCY와 NORM.DIST 함수를 사용할 예정입니다.

함수 자체에 포스팅을 한 적도 있으니 링크 참고하세요.

EXCEL 데이터를 히스토그램을 만드는 FREQUENCY 함수와 통계 차트 삽입기능

정규분포의 확률을 알려주는 NORM.DIST 함수는 무엇이고 사용하는 예시

 

 

< 차트 그리기 >

 

① 사용할 데이터를 정리합니다.

 

사용할 데이터를 정렬합니다.

AVERAGE 함수를 사용해서 평균을 STDEV.S 함수를 통해서 표본집단의 표준편차를 구해둡니다.

오늘 사용할 데이터는 야구 2024년 정규시즌 40 경기 이상 출전선수의 평균 안타수로 하겠습니다.

오늘의 데이터

 

 

② 도수분포표의 계급을 정합니다.

 

계급을 결정하는 방법은 여러가지가 있으나 표준적인 방법이나 대세적으로 많이 쓰는 방법은 없습니다.

꼭 이거다 하는 방법이 없음으로 이번에는 임의로 10개 간격으로 15칸으로 설정해 보겠습니다.

 

계급 정하기

 

 

③ Frequency 함수를 사용합니다.

 

Frequency 함수는 배열 함수로 사용해야 합니다.

오피스 365 이상에서는 그냥 배열함수로 사용이 되지만, 그 이전 버전에서는

Ctrl + Shift + Enter를 눌러줘야 합니다.

레벨을 bins array로 사용해야하며 DATA array는 원본 데이터를 사용합니다.

 

Frequency 함수 사용하기

 

 

④ NORM.dist 함수를 사용합니다.

 

NORM.dist는 배열함수가 아닙니다. 평범하게 사용하면 됩니다.

NORM.dist(x, 평균, 표준편차, 옵션) : 범위의 시작값을 x로 하고 평균과 표준편차는 구해두었습니다.

옵션은 False : 확률밀도함수로 입력합니다.

모든 범위(계급)에 대해서 NORM.dist를 구해서 정렬하겠습니다.

 

함수 사용하기

 

 

⑤ 그래프를 그립니다.

 

수량은 막대그래프로 정규곡선은 꺽은선 그래프로 그립니다.

그리고는 어느정도 꾸미면 아래처럼 그려집니다.

 

그래프 그리기

 

---

 

아래 그래프는 동일한 데이터를 넣고 통계 프로그램인 Minitap에 넣고 계산한 결과입니다.

주변에 여러가지 계산값을 만들어주기는 하지만 그래프 모양 자체는 유사하게 그려지네요.

아마 디자인 감각을 살린다면 더 나은 그래프도 그릴 수 있을 껍니다.