직접 그려본 파일.xls
엑셀을 활용하여 정규분포곡선 그리기
대부분 6시그마를 수행하는 기업들이 미니탭을 이용해서 데이터 분석을 수행하고 있지만 미니탭을 사용함에 있어 아쉬운 점은 가격이 비싸고, 배우는데 시간이 다소 걸린다는 겁니다. 필자도 많은 중소기업 임직원을 대상으로 6시그마 강의를 해 보았는데 의외로 미니탭 사용에 어려움을 호소하는 분들이 많았습니다. 그래서 이번 호부터 3개월간 엑셀을 이용하여 6시그마에서 사용되는 여러 기법들을 어떻게 활용할 수 있는지 알아보도록 하겠습니다. 이번 호는 그 첫 번째 시간으로 엑셀에서 그래프 그리는 방법을 알아보도록 하겠습니다. 특히 정규분포 곡선 작성법에 대해 알아 보겠습니다.
6시그마 교재를 보면 늘 나오는 정규분포 곡선이 있습니다. 많은 강의를 들으면서 정규분포 곡선의 특징에 대한 얘기를 듣기도 하고, 연필을 이용해 정규분포 곡선을 직접 그려 보기도 합니다. 때론 규격한계를 표시하여 어느 정도 불량이 발생하고 있는지를 비교하기도 합니다. 하지만 이런 정규분포 곡선을 보고서에 직접 삽입하려 하면 마땅히 그리는 방법을 모르기 때문에 미니탭이나 기타 소프트웨어에서 그려지는 그림으로 대신하곤 합니다.
[그림 1] 규격한계가 표시된 정규분포곡선
[그림 1]에 나타낸 그림과 같이 정규분포 곡선과 규격한계가 모두 표시된 그래프를 엑셀을 이용해서 그리는 방법을 알아보도록 하겠습니다.
정규분포 곡선의 특징
정규분포 곡선을 엑셀에서 그리기 위해서는 먼저 정규분포의 특징과 사용할 엑셀의 함수에 대해서 이해를 하고 있어야 합니다. 일반적으로 우리가 알고 있는 정규분포의 특징은 다음과 같은 것들이 있습니다.
- 종 모양
- 평균을 중심으로 좌우 대칭
- 전체 면적은 1
하지만 이것만 가지고는 완벽한 정규분포 곡선을 그릴 수는 없습니다. 바로 정규분포의 확률밀도함수(Probability Density Function)를 알아야만 정규분포 곡선을 그릴 수 있습니다. 평균이
, 표준편차가
인 정규분포의 확률밀도함수는 다음과 같습니다.
[그림 1]에서 보면, X축의 값들이 변함에 따라 Y=f(x)의 값들이 변하는 것을 선으로 연결한 것에 불과합니다. 즉, 위의 정규분포 확률밀도함수에서 평균과 표준편차를 아는 경우에 적절한 X값을 입력하면 함수 값,
가 구해지고 이를 연결하여 정규분포 곡선을 만들게 되는 것입니다.
엑셀에서 정규분포의 확률밀도함수 값을 얻어내는 엑셀함수는 "NORMDIST"라는 것이 있습니다. 이 NORMDIST 함수의 구성은 다음과 같습니다.
이 함수를 이용해서 정규분포 곡선을 그릴 수 있습니다. 하지만 한가지 걸림돌이 있는데, 우리가 그리려고 하는 정규분포는 평균과 표준편차가 항상 동일한 것이 아니라 늘 다르다는 것 입니다. 따라서 NORMDIST 함수에 입력하는 평균과 표준편차가 달라지고 또한 거기에 따라 X값들도 달라져야 합니다. 이러한 문제를 해결하기 위해 한 단계를 추가하게 되는데, 바로 표준화(Standardize) 공식을 이용하는 것 입니다. 정규분포를 따르는 자료를 표준화 하게 되면 표준정규분포(Standard Normal Distribution)가 되는데 이 때 사용되는 식은 다음과 같습니다.
표준화를 한 뒤의 자료는 표준정규분포를 따르는데, 표준정규분포는 평균이 0이고, 표준편차가 1인 정규분포를 의미합니다. 따라서 표준정규분포 곡선의 경우는 X축의 값이 0을 중심으로 나타나게 됩니다. 그런데, 위의 표준화 식을 다음과 같이 바꿔준다면 표준정규분포의 X축 값(Z)이 평균이
이고, 표준편차가
인 정규분포 자료로 바뀌게 됩니다.
즉, 표준정규분포 곡선의 경우 X축의 값이 -4부터 +4까지 움직일 때, 평균과 표준편차를 안다면 거기에 맞는 적절한 X값을 구할 수 있게 됩니다.
이러한 성질을 이용하여 정규분포 곡선을 그려보도록 하겠습니다.
정규분포 곡선 그리기
우선 엑셀의 빈 워크시트에 다음과 같이 A컬럼에 -4부터 +4까지 0.01씩 증가가 되도록 입력합니다.
그런 다음에는 D컬럼과 E컬럼에 적절한 평균과 표준편차를 입력합니다. 이 부분은 나중에 각 공정으로부터 얻은 자료의 평균과 표준편차도 대체되게 됩니다.
그리고 B컬럼에는 위의 표준화 공식을 활용하여 우리가 그리려고 하는 정규분포 곡선의 X축에 해당하는 값들을 구해내야 합니다.
B컬럼에 식을 입력할 때 주의해야 할 점은 평균과 표준편차가 입력되어 있는 D2 셀과 E2셀을 지정한 경우에는 반드시 키보드는 "F4" 키를 눌러 절대 참조가 되게 하는 것입니다. 이렇게 식을 입력한 후에는 드래그 기능을 이용하여 B컬럼 전체에 값을 입력하면 됩니다. 입력이 끝난 다음의 모습은 다음과 같습니다.
이제는 우리가 그리려는 정규분포 곡선의 Y축에 해당하는 값을 얻어내야 하는데, 이것은 앞서 설명한 "NORMDIST" 라는 함수를 이용합니다. 따라서 다음 그림과 같이 입력하면 됩니다.
여기서도 역시 평균과 표준편차가 입력된 셀을 지정하는 경우에는 "F4' 키를 눌러 절대참조로 바꿔줍니다. 입력이 끝난 다음의 모습은 다음과 같습니다.
이제 정규분포 곡선을 그리기 위한 준비가 끝났습니다. 입력된 자료를 이용하여 정규분포 곡선을 그리기 위해서는 먼저 위의 엑셀 워크시트에서 B컬럼과 C컬럼에 입력된 자료만을 블록으로 지정하고, "차트 마법사"를 실행시킵니다. 그러면 다음과 같이 어떤 종류의 그래프를 선택할 것인지를 묻는 화면이 나타나게 됩니다. 이 중 "분산형"을 선택하고, 하위 그래프 중에서는 "데이터 표식 없이 곡선으로 연결된 분산형"(엑셀 버전에 따라 표시되는 이름이 조금 다름)을 선택합니다.
"다음" 버튼을 누르게 되면 정규분포 모습이 나타나게 됩니다.
다시 한 번 "다음" 버튼을 눌러 몇 가지 옵션을 지정해 주면 [그림 1]과 같은 형태의 정규분포가 만들어 지게 됩니다.
그려진 정규분포 곡선은 현재 평균이 100, 그리고 표준편차가 10인 정규분포가 그려졌는데, 각 공정의 평균과 표준편차를 반영한 정규분포 곡선을 그리기 위해서는 D2 셀과 E2 셀에 입력된 평균과 표준편차를 해당 공정의 평균과 표준편차로 바꿔주기만 하면 자동으로 정규분포의 그림이 바뀌게 됩니다.
규격한계의 입력
만든 정규분포 곡선이 어느 중요한 품질특성의 모습이라면 규격한계를 표시하여 어는 정도의 불량(결함)이 발생하고 있는지를 표현하게 되면 더욱 좋은 그림이 될 것입니다. 규격한계 선을 입력하기 위한 방법은 다음과 같습니다.
규격한계 선은 두 개의 점을 선으로 연결하면 되는데, 예를 들어, 규격상한(USL)이 120이라면 두 개의 점 중에서 아래 쪽 점의 좌표는 (120, 0)가 됩니다. 그리고 위 쪽 점의 좌표는 (120, C402 셀 값)으로 정합니다. 여기서 C402 셀 값이라고 쓴 이유는 정규분포는 X축의 값이 평균인 경우에 높이가 가장 높기 때문에 규격한계 선의 높이를 정규분포의 높이와 같게 하기 위해서 앞서 정규분포 곡선을 그리기 위해 입력한 엑셀 워크시트의 C402 셀의 값을 참조해 오게 됩니다. 이와 같은 방법으로 워크시트에 다음과 같이 입력합니다.
위와 같이 입력한 뒤에는 다음과 같은 순서로 규격한계를 정규분포 곡선에 추가할 수 있습니다.
① 규격한계를 입력한 네 개 셀을 블록으로 잡은 뒤에 복사 합니다.
② 워크시트에 그려진 정규분포 곡선의 가운데 부분을 클릭합니다.
③ "편집" 메뉴의 "선택하여 붙여 넣기"를 선택합니다.
④ "첫 열을 항목명으로"으로 선택하고 "확인"을 누르게 되면 규격상한 선이 입력됩니다.
완성된 그림을 보면 정규분포 곡선과 규격상한선이 표시되어 있는 것을 알 수 있습니다. 위의 그림을 이용하여 각 공정의 품질 특성을 비교한다면 멋진 보고서가 될 수 있을 것이라고 생각합니다