[그림 1] 스크롤 바를 이용한 Xbar-R관리도

엑셀에서의 자료 입력

엑셀의 편리함은 사용자에게 익숙한 인터페이스를 제공한다는 겁니다. 누구나가 익숙한 화면이기에 조금 어려운 내용들도 쉽게 받아들일 수 있다는 것인데요, 엑셀의 한 시트를 자료입력 만을 위한 공간으로 활용토록 합니다. [그림 2]에서 보는 것과 같이 최근의 자료가 위에 오도록 자료를 입력합니다.

[그림 2] 자료입력 시트

입력된 자료의 효율적인 활용을 위해서 "Sheet1"의 데이터가 들어 있는 부분 전체를 블록으로 지정한 다음 이름을 다음 [그림 3]과 같이 부여합니다.

[그림 3] 입력 자료에 대한 이름 정의

위의 이름을 정의할 때 주의할 점은 첫째 행부터 블록으로 지정한다는 것이다. 향후 삽입기능을 통해 자료가 추가되더라도 추가된 부분이 정의한 "DATA"에 속해지도록 하기 위함입니다.

위와 같이 자료를 입력한 후에 "Sheet2"의 관리도에 실제로 표현될 자료를 가져오는 것이 필요한데, A2셀에 1을 입력한 뒤에 A3셀에는 "=A2+1"과 같이 식을 사용합니다. 식을 사용하는 이유는 A2셀의 값을 달리 입력해 주면 자동으로 1이 더해져서 A3셀이 바뀌도록 하기 위함입니다. A3셀에 입력이 끝나면 A21셀까지 드래그를 해서 20까지 입력되도록 합니다.

컬럼 A에 입력되는 1 ~ 20의 숫자는 관리도에 표시될 부분군(Subgroup) 번호를 의미합니다. 식을 사용해 입력했으므로 A2셀의 값을 바꿔주면 나머지 A3 ~ A21까지의 셀의 자동으로 바뀌게 됩니다.
다음은 X1 ~ X5까지 컬럼 A에 있는 부분군 번호에 해당하는 데이터를 Sheet1으로부터 참조해 와야 하는데, 이 때 "VLOOKUP"이라는 함수를 사용합니다. 예를 들어 B2셀에 부분군 번호가 1인 자료를 참조해 오기 위해서는 "=VLOOKUP(A1, DATA, 2, FALSE)"이라는 식을 B2셀에 입력합니다. 여기서 DATA는 앞서 Sheet1에서 지정한 자료 범위를 의미합니다. 그리고 숫자 2는 지정한 DATA 범위에서 우리가 찾으려는 X1 자료가 두번째 컬럼에 있기 때문에 지정한 값이다. 자세한 내용은 "F1"키를 이용해 도움말을 참조토록 합니다. 위의 식처럼 B2셀에서 F2셀까지 식을 입력하고 블록을 지정한 후에 부분군 번호가 20인 곳까지 드래그를 해서 B2셀에서 F21셀까지 채우면 [그림 4]와 같이 됩니다.

[그림 4] 관리도에 표시할 부분군 자료의 지정

Sheet2에 자료를 입력한 다음에는 우리가 그리려고 하는 관리도가 Xbar-R관리도이기 때문에 각 부분군의 평균(Mean)과 범위(Range)를 계산해서 입력해야 합니다. [그림 4]에 G1셀과 H1셀에 있는 것처럼 각각 "XBAR"와 "R"이라고 입력하고, 평균과 범위를 구하기 위해서는 다음과 같은 함수를 이용합니다. 두 번째 행부터 각 행에 있는 부분군의 평균과 범위를 구해서 입력해야 하는데, G2셀의 경우는 "=AVERAGE(B2:F2)"라고 입력하면 되고, H2셀의 경우는 "=MAX(B2:F2)-MIN(B2:F2)"라는 함수를 이용하여 각각 평균과 범위를 구합니다. 구한 뒤의 모습은 [그림 5]와 같습니다.

[그림 5] 평균과 범위의 계산

스크롤 바의 삽입

[그림 5]의 상태에서 A2셀의 값을 1이상의 자연수를 입력하게 되면(사실은 부분군 번호를 지정하는 것임) Sheet2에 있는 모든 자료가 변경될 것입니다. 즉 관리도에 표시할 자료가 달라지는 것인데 이를 스크롤 바를 이용하여 쉽게 바꾸고자 합니다. "보기 > 도구 모음 > 컨트롤 도구상자" 메뉴를 선택하면 다음과 같은 도구 모음이 생깁니다.

그 중에서 스크롤 막대라는 것을 클릭하여 Sheet2의 빈 곳에 삽입합니다. 삽입을 한 후에 스크롤 막대 위에 마우스 포인터를 위치한 후에 오른쪽 마우스 버튼을 누르면 나타나는 메뉴 중에서 "속성"이라는 메뉴가 있습니다. 속성 메뉴 중에서 "Linked Cell"의 공란에 "A2"라고 입력합니다. 이는 스크롤 바를 이용해서 A2셀의 값을 조정하기 위한 것입니다. 그리고 "Min"과 "Max" 부분에 각각 1과 500을 입력합니다. 관리도에 표시할 부분군 번호가 최소 1번부터 500번 내로 한정하기 위해서 입니다.

[그림 6] 속성 창의 설정

입력이 끝난 다음에는 속성 창을 닫고, 컨트롤 도구상자의 "디자인 모드 끝내기" 버튼을 누르면 스크롤 바가 작동 됩니다. 스크롤 바를 움직이면 Sheet2의 모든 데이터들이 자동으로 바뀌게 됩니다.

이젠 Xbar-R관리도를 그리기 위해 관리한계를 계산해주면 됩니다.

관리한계의 계산

< Xbar관리도의 관리한계 >

< R관리도의 관리한계 >

위 식을 이용해서 관리한계를 [그림 7]과 같이 Sheet2에 계산하여 입력합니다. 계산 식에 나오는 A2, D4, D3는 관리도 계수표로써 본 글의 끝에 첨부되어 있는 엑셀 파일, CoefTbl.XLS 파일을 보면 부분군의 크기 별로 그 값이 나와 있습니다. 그 표를 보면 a2 = 0.577, D4 = 2.115 그리고 D3 = 0 이 됩니다. 또한 Xbar 관리도의 중심선(CL)은 Sheet2에 계산된 각 부분군 평균들의 평균이고, R관리도의 중심선은 각 부분군 범위의 평균이므로 각각 G22 셀과 H22 셀에 AVERAGE 함수를 이용해서 평균을 구하면 됩니다. 위의 내용이 다 입력된 화면이 [그림 7]입니다.

[그림 7] 관리한계가 입력된 화면

차트 마법사를 이용한 관리도 작성

평균과 범위 그리고 관리한계 입력이 끝나고 차트 마법사를 이용하여 차트를 작성하면 모든 것이 끝나게 되는 것입니다. 우선 Xbar 관리도 작성법부터 알아 보도록 하겠습니다.

Xbar관리도를 그리기 위해서는 컬럼G의 Xbar 값이 들어 있는 G1:G21 셀을 블록으로 지정하고, Xbar 관리도의 관리한계가 입력되어 있는 I1:K21 범위를 CTRL 버튼을 이용하여 블록으로 지정합니다. 비연속적인 블록을 지정하는 경우에는 CTRL 키를 이용합니다. 블록을 지정한 다음에는 툴 바에 있는 차트마법사를 실행합니다. 차트 마법사의 1단계에서 나오는 차트의 종류에서 다음과 같은 "꺽은선형"을 선택합니다.

[그림 8] 차트 마법사 실행 화면

차트 마법사의 2단계에서는 "계열" 탭을 선택하여 다음 그림과 같이 X축에 들어갈 데이터를 지정해 줍니다. 관리도의 X축에는 부분군 번호가 들어가도록 해야 하며, 그러기 위해서는 X축 레이블에 A2:A21 셀을 지정해 줍니다.

[그림 9] 2단계 부분군 번호의 지정

[그림 10] 3단계 차트 제목 입력

[그림 11] 3단계 Y축 주눈금선 표시 해제

모든 옵션 지정이 끝나면 차트를 Sheet2에 삽입시킵니다.

차트를 만든 후에는 다음과 같이 세 개의 관리한계 선에 있는 표식을 데이터 계열 서식을 이용하여 없애면 다음과 같은 깔끔한 관리도가 만들어 집니다.

[그림 12] 표식을 없애기 위한 데이터 계열 서식

[그림 13] 완성된 Xbar관리도

관리도가 완성된 후에는 앞서 만든 스크롤 바를 이용하여 A2 셀의 값을 조정하면 관리도에 표시되는 자료가 연동되어 변하게 됩니다.

따라서 Sheet1에 데이터를 입력하고 Sheet2에서는 원하는 부분군의 관리도를 스크롤 바를 이용하여 볼 수 있게 되었습니다. 추가로 위의 관리도에서 관리한계를 고정시키는 작업이 필요한데, 컨트롤 도구 상자의 옵션 단추와 관리한계를 구하는 셀에서 IF문을 적절히 사용하면 고정된 관리한계와 실시간 주어진 데이터를 이용한 관리한계 중에서 선택할 수 있는 좀 더 실용적인 관리도를 만들 수 있습니다.

다음 호에서는 이러한 관리도와 자료를 이용하여 공정능력분석을 하는 방법에 대해 알아 보도록 하겠습니다.

• XbarR.XLS: Xbar-R 관리도 파일
• CoefTbl.XLS: 관리도 계수표가 나타나 있는 파일