더하기를 하자 - 열세번째

관심이 끌리는 질문이다.
오튜에 올라온 질문인데 내 맘대로 정리하면 대충 이렇다.

시점별로 값이 있는데 전체 합계의 80%를 점유하는 기간(시작~종료)을 알고 싶다는 것.
물론 기간은 짧을 수록 좋다.
그리고 자료는 종 모양으로 분포하고 최고점을 중심으로 한다는 것.

질문이 유용해 보인다.
그래서 허접대마왕(?)이 나가신다.
으~~ㅁ ㅎㅎㅎ

이야기(?) 전개는 대략 세 가지.
- 피라미드
- 정규분포 응용
- 이동누적점유율

자료가 종 모양을 이루고, 최고점이 중심이라고 해서 처음 떠올린 이야기는 '피라미드'였다.

그리고 종 모양의 대명사, 정규분포를 응용해 보기로 하고 '정규분포 응용'을 구상했다.

그런데 만일 분포가 일정하지 않다면?
모르긴해도 뭔가 많이 불안한 결과가 나올거 같다.

그래서 시점의 이동에 따른 누적점유율을 떠올려봤다.

그런데 '이동누적점유율'은 수식이 너무 복잡.
(나 처럼 재주 없는 넘들이 복잡하게 하는거다.)
이를 이름정의로 대체하면서, 함수 중첩 제한으로 인해 미반영된 부분(?)도 반영.
때문에 '이동누적1', '이동누적2'로 나눠진다.

썰(?)이 길었는데, 결과를 보자.

파이어폭스에서는 수식이 또 잘려서 보이겠으나...
작성된 수식을 나열하면 아래와 같다.
참고로 수식이 좀 길다 싶은 것들은 배열수식이다.

e4 셀 ( 피라미드 : 기준시점)
=MATCH(MAX($B$2:$B$21),$B$2:$B$21,0)

e6 셀 (피라미드 : 시작시점)
=MAX(1,$E$4-(MATCH($E$3,MMULT(TRANSPOSE(ROW($B$2:$B$21)^0),$B$2:$B$21*(ABS(ROW($B$2:$B$21)-1-MATCH(MAX($B$2:$B$21),$B$2:$B$21,0))<TRANSPOSE(ROW(INDIRECT("1:"&ROWS($B$2:$B$21))))))/SUM($B$2:$B$21),1)-1))

e7 셀 (피라미드 : 종료시점)
=MIN(ROWS($B$2:$B$21),$E$4+(MATCH($E$3,MMULT(TRANSPOSE(ROW($B$2:$B$21)^0),$B$2:$B$21*(ABS(ROW($B$2:$B$21)-1-MATCH(MAX($B$2:$B$21),$B$2:$B$21,0))<TRANSPOSE(ROW(INDIRECT("1:"&ROWS($B$2:$B$21))))))/SUM($B$2:$B$21),1)-1))

그동안 소개해온 수식을 조금이라도 연습했다면...
적어도 '기간', '소계', '점유율' 구하기는 누워서 떡먹기일테니 굳이 나열하지 않겠다.

'정규분포응용'에서는 시점을 이산형이 아닌 연속형인 실수로 처리하게 되는데,
경우에 따라 조건을 벗어난 결과가 나온다.
이를 따로 처리할까 했으나, 그닥 실효성이 없어 보여 그냥 놔뒀다.

f4 셀 (정규분포응용 : 기준시점)
=SUMPRODUCT($A$2:$A$21,$B$2:$B$21)/SUM($B$2:$B$21)

f5 셀 (정규분포응용 : 표준편차)
=SQRT(SUMPRODUCT(($A$2:$A$21-$F$4)^2,$B$2:$B$21)/SUM($B$2:$B$21))

f6 셀 (정규분포응용 : 시작시점)
=MAX(1,NORMINV((1-$E$3)/2,$F$4,$F$5))

f7 셀 (정규분포응용 : 종료시점)
=MIN(ROWS($B$2:$B$21),NORMINV($E$3+(1-$E$3)/2,$F$4,$F$5))

앞서 잠깐 얘기했지만 '이동누적'은 보기 불편할 정도다. -_-;;
수식이 너무 길어 '$' 기호도 빼낼 정도로...

그리고 다시 말하지만,
'이동누적'은 자료가 종 모양을 나타내지 않을 때를 고려한 거라는 것.
(앞서 예제에서 수치를 바꿔봤다.)

g6 셀 (이동누적1 : 시작시점)
=MATCH(MIN(IF((MMULT((MMULT((ROW(B2:B21)<=TRANSPOSE(ROW(B2:B21)))*1,(ROW(B2:B21)<=TRANSPOSE(ROW(B2:B21)))*B2:B21)/SUM(B2:B21)>E3)*1,ROW(B2:B21)^0)>0)*(MMULT((MMULT((ROW(B2:B21)<=TRANSPOSE(ROW(B2:B21)))*1,(ROW(B2:B21)<=TRANSPOSE(ROW(B2:B21)))*B2:B21)/SUM(B2:B21)<=E3)*1,ROW(B2:B21)^0)-(ROW(INDIRECT("1:"&ROWS(B2:B21)))-1)>0),(MMULT((MMULT((ROW(B2:B21)<=TRANSPOSE(ROW(B2:B21)))*1,(ROW(B2:B21)<=TRANSPOSE(ROW(B2:B21)))*B2:B21)/SUM(B2:B21)>E3)*1,ROW(B2:B21)^0)>0)*(MMULT((MMULT((ROW(B2:B21)<=TRANSPOSE(ROW(B2:B21)))*1,(ROW(B2:B21)<=TRANSPOSE(ROW(B2:B21)))*B2:B21)/SUM(B2:B21)<=E3)*1,ROW(B2:B21)^0)-(ROW(INDIRECT("1:"&ROWS(B2:B21)))-1)))),(MMULT((MMULT((ROW(B2:B21)<=TRANSPOSE(ROW(B2:B21)))*1,(ROW(B2:B21)<=TRANSPOSE(ROW(B2:B21)))*B2:B21)/SUM(B2:B21)>E3)*1,ROW(B2:B21)^0)>0)*(MMULT((MMULT((ROW(B2:B21)<=TRANSPOSE(ROW(B2:B21)))*1,(ROW(B2:B21)<=TRANSPOSE(ROW(B2:B21)))*B2:B21)/SUM(B2:B21)<=E3)*1,ROW(B2:B21)^0)-(ROW(INDIRECT("1:"&ROWS(B2:B21)))-1)),0)

g7 셀 (이동누적1 : 종료시점)
=G6+MIN(IF((MMULT((MMULT((ROW(B2:B21)<=TRANSPOSE(ROW(B2:B21)))*1,(ROW(B2:B21)<=TRANSPOSE(ROW(B2:B21)))*B2:B21)/SUM(B2:B21)>E3)*1,ROW(B2:B21)^0)>0)*(MMULT((MMULT((ROW(B2:B21)<=TRANSPOSE(ROW(B2:B21)))*1,(ROW(B2:B21)<=TRANSPOSE(ROW(B2:B21)))*B2:B21)/SUM(B2:B21)<=E3)*1,ROW(B2:B21)^0)-(ROW(INDIRECT("1:"&ROWS(B2:B21)))-1)>0),(MMULT((MMULT((ROW(B2:B21)<=TRANSPOSE(ROW(B2:B21)))*1,(ROW(B2:B21)<=TRANSPOSE(ROW(B2:B21)))*B2:B21)/SUM(B2:B21)>E3)*1,ROW(B2:B21)^0)>0)*(MMULT((MMULT((ROW(B2:B21)<=TRANSPOSE(ROW(B2:B21)))*1,(ROW(B2:B21)<=TRANSPOSE(ROW(B2:B21)))*B2:B21)/SUM(B2:B21)<=E3)*1,ROW(B2:B21)^0)-(ROW(INDIRECT("1:"&ROWS(B2:B21)))-1))))-1

에고...
수식을 짧게 만드는게 실력인데 진짜 허접해 보인다. -_-;;
하삼각 및 상삼각 행렬을 처리하는 함수가 지원됐으면 좋으련만...

수식이 너무 길고 중첩 제한에 걸린 부분이 있어 이름정의를 사용하게 됐고,
미반영된 부분도 집어넣었다.

h6 셀 (이동누적2 : 시작시점)
=MIN(IF((ms_Interval=Min_Interval)*(ms_Matrix<=Limit)*(ms_Matrix=MAX((ms_Interval=Min_Interval)*(ms_Matrix<=Limit)*ms_Matrix)),order_Row))

h7 셀 (이동누적2 : 종료시점)
=H6+Min_Interval-1

그리고 이름정의된 내용은...

rng             =Sheet1!$B$2:$B$21
Limit           =Sheet1!$E$3
rng_Row     =ROW(rng)
rng_Cnt      =ROWS(rng)
order_Row  =ROW(INDIRECT("1:"&rng_Cnt))
tri_Matrix    =(rng_Row<=TRANSPOSE(rng_Row))
ms_Matrix   =MMULT(tri_Matrix*1,tri_Matrix*rng)/SUM(rng)
ms_OK       =MMULT((ms_Matrix>Limit)*1,rng_Row^0)
ms_Interval =MMULT((ms_Matrix<=Limit)*1,rng_Row^0)-(order_Row-1)
Min_Interval =MIN(IF((ms_OK>0)*(ms_Interval>0),(ms_OK>0)*(ms_Interval)))

그런데 위 처리 방식엔 한계가 있다.
자료가 주기성을 갖는 경우가 반영되어 있지 않다.
아마도 치명적인 한계가 아닐까 한다...