모델별 가격/수량 데이타에서 수량을 고려한 중위값, 1분위값, 3분위값 획득 하기

가격 분석을 하다보면, 제품별 실거래가격과 그 수량 데이터를 기준으로 Box-Plot을 그려서 제품이 판매되는 가격대를 시계열이든 지역별로 파악할 필요가 있습니다. 미니탭 등 전문 통계 소프트웨어는 Box Plot이나 중위수 계산시 스량 데이터를 '발생빈도'개념으로 계산에 포함시킬 수 있으나, 엑셀 2013까지는 두 데이터를 자동으로 통합하는 기능이 없어서 기본 기능으로는 파악이 불가능했습니다.

 

그러던 중, 아래 사이트에서 ' Find medians from a frequency distribution table?'라는 제목의 Q&A글을 발견하여 적용해보니, 미니탭과 동일한 결과가 도출되어, 향후 돌이켜볼 때 사용할 겸 블로그에 남겨보고자 합니다.

 

아이디어 출처 : Find medians from a frequency distribution table?

 

[ 빌려온 아이디어 정리 ]

데이터가 아래와 같이 C2:H5로 구성되어 있습니다. 값은 C2:H2이고, C3:H5는 그룹별 값의 발생 횟수를 기록하고 있습니다.

		C	D	E	F	G	H	I
2	values:	6	5	4	3	2	1	Median
3	group1:	1	3	0	0	5	1	2
4	group2:	3	4	7	0	0	0	4.5
5	group3:	0	0	1	3	4	2	2

 

이 경우, 발생 횟수를 고려한 중위수 산출 함수는 다음과 같습니다. 함수를 감싸는 { } 는 이 함수가 Array 기능을 사용한다는 것을 의미합니다. 셀에 입력 후 'Enter'가 아니라 'Ctrl+Shift+Enter'로 입력해야 활성화 됩니다.

 

{=MEDIAN( LOOKUP( ROW( INDIRECT( "1:"&SUM(C3:H3) ) )-1,

                    SUBTOTAL( 9, OFFSET(B3,0,0,1,COLUMN(C3:H3)-COLUMN(C3)+1) ),

                    C$2:H$2) )}

 

Offset() 부분이 {B3, B3:C3, B3:D3: … }의 집합을 생성합니다.

그리고, Subtotal() 부분이 각 항의 합계를 구해, 집합 { 0,1,4,4,4,9 }를 만듭니다. 위 그룹1의 발생 빈도가 10회이므로, 좌측부터 발생하는 빈도수를 누적한다고 보면 됩니다.

 

ROW( INDIRECT () ) 함수는 0부터 빈도 합계보다 1 작은 수까지의 집합을 만듭니다. 위 그룹1 기준을 {0;1;2;3;4;5;6;7;8;9}를 만듭니다. 이제 함수는 다음의 모양이 됩니다.

 

=MEDIAN(LOOKUP({0;1;2;3;4;5;6;7;8;9},{0,1,4,4,4,9},C$2:H$2))

 

Lookup() 함수는 첫째 집합의 값을 둘째 집합의 값에서 찾으며, 같거나, 최대한 근접한 값을 찾게 됩니다. 이는 {0;1;1;1;4;4;4;4;4;9}이 되고, 이를 C$2:H$2에서 값을 가져와 {B2,C2,C2,C2,F2,F2,F2,F2,F2,G2}의 형식이 되고 결과적으로는 {6,5,5,5,2,2,2,2,2,1}의 집합을 만들어, Median() 함수를 실행하게 됩니다.

 

(엑셀 붙여넣기 용)

=MEDIAN(LOOKUP(ROW(INDIRECT("1:"&SUM(C4:H4)))-1,SUBTOTAL(9,OFFSET(B4,0,0,1,COLUMN(C4:H4)-COLUMN(C4)+1)),C$3:H$3))

 

[ 모델별 가격 통계에 활용 ]

데이터 구조가 바뀌었습니다. 모델 별 데이터가 열을 형성하고 있고, 거래선별 가격이 행을 이루며, 그 아래에는 거래선별 수량을 나열합니다. 이 때, 중위 가격은 얼마인지를 구하는 것이 문제입니다.

	L	M	N	O	P
2	가격		모델1	모델2	모델3
3		거래선1	1	2	3
4		거래선2	2	3	4
5		거래선3	3	4	5
6		거래선4	4	5	6
7		거래선5	5	6	7
8		거래선6	6	7	8
9	수량		모델1	모델2	모델3
10		거래선1	1	1	2
11		거래선2	5	4	4
12		거래선3	6	0	3
13		거래선4	4	7	1
14		거래선5	3	4	0
15		거래선6	1	3	2
16		수량 합계	20	19	12
17		Median	1.00	5.00	4.50

 

계산식을 간단히 하기 위해, 수량 합계를 별도로 계산합니다.

=SUM(N10:N15)

 

그리고, 앞 예제에서 사용한 함수를 아래와 같이 살짝 변경하면, 거래선별/가격별 수량을 고려한 중위 가격 값이 뚝딱 나옵니다.

{=MEDIAN(LOOKUP(ROW(INDIRECT("1:"&N16))-1,SUBTOTAL(9,OFFSET(N9,0,0,ROW(N10:N15)-ROW(N10)+1,1)),N$3:N$8))}

역시 셀 입력 후 'Ctrl+Shift+Enter'로 입력하여, Array 함수임을 엑셀에 알려줍니다.

수식 변경 사항은 Offset() 부분에 행,열이 바뀌어 있는 것 빼고는 동일합니다.

 

마찬가지로, 1분위값, 3분위값도 다음 함수를 통해 뚝딱 계산할 수 있습니다.

 

< 1분위 수  >

{=Quartile.inc(LOOKUP(ROW(INDIRECT("1:"&N16))-1,SUBTOTAL(9,OFFSET(N9,0,0,ROW(N10:N15)-ROW(N10)+1,1)),N$3:N$8),1)}

 

< 3분위 수  >

{=Quartile.inc(LOOKUP(ROW(INDIRECT("1:"&N16))-1,SUBTOTAL(9,OFFSET(N9,0,0,ROW(N10:N15)-ROW(N10)+1,1)),N$3:N$8),3)}

Min, Max는 수량과 무관하므로, 쉽게 구할 수 있습니다.

축하합니다, 이제 Box Plot을 그리기 위한 모든 값을 다 얻었습니다.