데이터에 음수가 있을 경우 엑셀로 boxplot 그리기

[김브링이]

데이터에 음수가 포함되어있을 경우에 일반적인 방법으로 boxplot을 그리면 제대로 나오지 않습니다.

그래서 이곳 동영상을 참고하여 방법을 정리했습니다. 

- https://www.youtube.com/watch?v=BoNQpIsjkYc

전체적인 내용은 one sample t-test에 대한 것입니다. boxplot을 그려본 후 분석 방법에 대해서도 좀 정리하면 좋을 것 같네요.

- http://www.real-statistics.com/students-t-distribution/one-sample-t-test/

----------------------------------------------------------------------------------------------

먼저 엑셀에 데이터를 위와 같이 입력합니다. 데이터는 소아 비만 치료를 위해 처방이 내려진 12명의 환자의 2년 후의 몸무게 변화입니다. +는 몸무게가 늘었다는 의미이고 -는 몸무게가 줄었다는 의미입니다.

데이터 오른쪽에 보시면 여러 값들이 있습니다. 먼저 데이터에 대한 기본 정보는 아래와 같습니다.

count	=COUNT(B4:B15)	데이터의 개수
mean	=AVERAGE(B4:B15)	데이터의 평균
std dev	=STDEV(B4:B15)	데이터의 표준편차	자료의 산포도를 나타내는 수치로, 분산의 음이 아닌 제곱근으로 정의
std err	=E5 / SQRT(E3) =std dev / square(mean)	데이터의 표준오차	12개의 데이터는 표본 데이터입니다. 표본의 평균은

※ 표준편차 : 표본의 측정값이 평균에서 어느 정도 떨어져 있는지를 나타내는 지표

※ 표준오차 : 표준오차는 모평균의 추정치인 표본평균이 가지는 표준 편차

----------------------------------------------------------------------------------------------

위에서 입력한 데이터 아래 부분에 최소값, 제1사분위수, 중앙값, 제3사분위수, 최대값을 입력합니다.

엑셀식은 아래와 같습니다.

	기존값	기존값 + 20	왼쪽 열 아래, 위 차이
최소값	=MIN(B4:B15)	=B19 + 20	=C19
제1사분위수	=PERCENTILE(B4:B15, 25%)	=B20 + 20	=C20-C19
중앙값	=MEDIAN(B4:B15)	=B21 + 20	=C21-C20
제3사분위수	=PERCENTILE(B4:B15, 75%)	=B22 + 20	=C22-C21
최대값	=MAX(B4:B15)	=B23 + 20	=C23-C22
			10(더미 변수)

다음으로 이런식으로 마우스로 선택을 해줍니다.

다음으로 엑셀 옵션의 삽입 - 세로 막대형 - 2차원 세로 막대형 - 누적 세로 막대형을 선택 해줍니다.

그래프가 아래와 같이 나오면 X, Y축 위치를 바꿔줘야 합니다.

생성한 그래프 가장자리에서 마우스 오른쪽 클릭하고 데이터 선택 옵션을 선택합니다.

여기서 행/열 전환을 눌러서 계열이 범례 항목에 오도록 하고 확인을 누릅니다.

그래프가 이런 모양이 되면 정상입니다

여기서 제일 아래에 있는 파란색 박스를 클릭하고 마우스 오른쪽 클릭을 한 다음 데이터 계열 서식 추가를 누릅니다.

나오는 창의 데이터 계열 지정 옵션에서 보조 축을 선택하고 닫기를 누릅니다.

그러면 이렇게 파란색 박스가 커진 것을 볼 수 있습니다. 계열2, 계열3, 계열4, 계열5 박스도 마찬가지로 보조축으로 만듭니다.

파란색 박스로 가려서 마우스로 선택이 힘들면 키보드의 윗 방향 이동키를 눌러서 셀 간에 이동 가능합니다.

계열2를 선택한 모습입니다.

계열1 ~ 5의 박스를 모두 보조 축으로 하면 최종적으로는 이런 모양이 나옵니다.  

생성된 그래프에서 왼쪽 축을 박스 모양이 나오도록 선택하고 축 서식을 클릭합니다.

축 옵션의 최소값 옵션을 아까 엑셀 계산식에서 더해준 20에 - 기호를 붙여서 -20으로 설정합니다.

최대값은 (오른쪽 축 최대값 - 20)을 해서 30으로 설정합니다.

그러면 이런 화면으로 변하게 되는데 오른쪽 축을 왼쪽 축을 선택한 방식으로 선택하고 축 레이블을 없음으로 설정합니다.

그래프에서 계열3 박스를 선택하고 엑셀 메인 메뉴의 의 레이아웃 - 기타 오차 막대 옵션으로 들어갑니다.

표시 옵션에서 방향을 양의 값으로 설정하고 오차량 옵션의 사용자 지정 옵션에서는 값 지정 버튼을 클릭합니다.

나오는 창에서 양의 오류 값을 엑셀에서 계산한 최대값으로 설정하고 닫기를 누릅니다.

그래프에서 계열2 박스를 선택하고 엑셀 메인 메뉴의 의 레이아웃 - 기타 오차 막대 옵션으로 들어갑니다.

표시 옵션에서 방향을 음의 값으로 설정하고 오차량 옵션의 사용자 지정 옵션에서는 값 지정 버튼을 클릭합니다. 

나오는 창에서 음의 오류 값을 엑셀에서 계산한 제1사분위수로 설정하고 닫기를 누릅니다.

완료하면 아래와 같은 모양이 되는데 여기서 계열 3, 계열 4 박스를 제외한 나머지 박스는 채우기 없음을 통해

투명하게 하고 중간에 보이는 1이 적힌 박스는 삭제합니다.

데이터에 대한 boxplot이 만들어졌습니다.

----------------------------------------------------------------------------------------------

R에서는 아래와 같은 명령어로 그릴 수 있습니다.

data <- c(23, 15, -5, 7, 1, -10, 12, -8, 20, 8, -2, -5)

boxplot(data)

----------------------------------------------------------------------------------------------

summary 명령어를 사용해서 엑셀에서 계산했던 값들을 R에서도 볼 수 있습니다.

[천감기]

와. 김브랑이님..엑셀을 이리 애용하시다니...
이 방법도 좋은데, 이게 좀 복잡하면,
http://cafe.naver.com/easy2know/6133

제가 만들어던 탬플릿을 사용하는 것은 어떨까요?
음수 양수 가리지 않고 그냥 만들기는 하지만....

그래도 원자료가 있다면 web-R로 만들면 더 좋겠죠.

web-R이 생기기 전에 만든 템프릿이라서..

[김브링이]

다른 통계 개념도 그렇고 엑셀로 한 번씩 해보면 이해가 더 잘 되는 것 같네요 ㅎㅎ
생각보다 구현할 수 있는 통계 기능도 많은 것 같아서 한 번씩은 해보면 좋을 것 같습니다

알려주신 템플릿은 분산형 그래프로 만들어진 것 같네요. 정말 여러가지 방법이 있는 것 같습니다.

[천감기]

boxplot만 엑셀에서 만들어 자동으로 만들어 준다면,
훨씬 좋을 텐데, 2016 버젼에서도 안되지요? 아마도

[김브링이]

검색해보니 아직 지원이 안 되는 것 같네요. 통계 관련 기능들이 많은데 왜 boxplot만 빠져있는지 좀 이상하군요