통계로 세상보기

아래의 자료를 이용해 합을 구하려는데, 제약조건이 있다.
상한선을 초과하지 말아야 한다.

즉 다섯 숫자를 이용해 합을 구할 때, 상한선을 초과하지 않는 근사값을 구하는 경우다.
이때 지난번에 구성한 조합을 이용하면 근사값도 구하고 해당 조합도 찾을 수 있다.

방법은 간단하다. 해당 조합에 맞춰 더하기를 하면 된다.
그리고 상한선을 초과하지 않는 결과를 불러오는거다.

l3 셀
=INDEX(OFFSET($B$1:$F$1,MATCH(MAX(IF($K$1>=MMULT($B$2:$F$33,$K$3:$K$7),MMULT($B$2:$F$33,$K$3:$K$7))),IF($K$1>=MMULT($B$2:$F$33,$K$3:$K$7),MMULT($B$2:$F$33,$K$3:$K$7)),0),0),ROW($A1))

l8 셀
=MAX(IF($K$1>=MMULT($B$2:$F$33,$K$3:$K$7),MMULT($B$2:$F$33,$K$3:$K$7),))

물론 배열수식이다.
(별도 말이 없으면 오늘은 모두 배열수식이다.)

그런데 문제가 있다.
제약조건을 만족하는 합을 구성하는 조합이 유일하지 않다는 것.

l9 셀
=SUM(N(IF($K$1>=MMULT($B$2:$F$33,$K$3:$K$7),MMULT($B$2:$F$33,$K$3:$K$7))=MAX(IF($K$1>=MMULT($B$2:$F$33,$K$3:$K$7),MMULT($B$2:$F$33,$K$3:$K$7)))))

이 경우는 3건의 조합이 제약조건을 만족하는 근사값을 구성한다고 나왔다.
조합이 하나만 존재했다면 편리하겠지만, 현실은 기대를 여지 없이 저버렸다?
그러나 이를 모두 보여주기는 뭐하고해서, 해당 조합번호만 보여주는 쪽으로 생각하면...

l10 셀
=LARGE(N(IF($K$1>=MMULT($B$2:$F$33,$K$3:$K$7),MMULT($B$2:$F$33,$K$3:$K$7))=MAX(IF($K$1>=MMULT($B$2:$F$33,$K$3:$K$7),MMULT($B$2:$F$33,$K$3:$K$7))))*($A$2:$A$33),ROW($A1))

그래도 근사하는 조합만을 봐야한다면...

=INDEX(OFFSET($B$1:$F$1,LARGE(N(IF($K$1>=MMULT($B$2:$F$33,$K$3:$K$7),MMULT($B$2:$F$33,$K$3:$K$7))=MAX(IF($K$1>=MMULT($B$2:$F$33,$K$3:$K$7),MMULT($B$2:$F$33,$K$3:$K$7))))*($A$2:$A$33),COLUMN(A$1)),0),ROW($A1))

여기서 한발 더 나아가자.
동일한 합을 보여주는 조합이 유일하지 않을 수도 있다는 것을 알았으니
조합에 사용되는 구성 갯수를 달리할 때 마다 근사값을 구하는 경우로...
말이 버벅거리는데 결과를 보면 대충 안다.

m3 셀
=INDEX(OFFSET($B$1:$F$1,MATCH(MAX(IF(MMULT($B$2:$F$33,{1;1;1;1;1})=M$2,IF($K$1>=MMULT($B$2:$F$33,$K$3:$K$7),MMULT($B$2:$F$33,$K$3:$K$7)))),IF(MMULT($B$2:$F$33,{1;1;1;1;1})=M$2,IF($K$1>=MMULT($B$2:$F$33,$K$3:$K$7),MMULT($B$2:$F$33,$K$3:$K$7))),0),0),ROW($A1))

m8 셀
=MAX(IF(MMULT($B$2:$F$33,{1;1;1;1;1})=M$2,IF($K$1>=MMULT($B$2:$F$33,$K$3:$K$7),MMULT($B$2:$F$33,$K$3:$K$7),)))

m9 셀
=SUM(N(IF(MMULT($B$2:$F$33,{1;1;1;1;1})=M$2,IF($K$1>=MMULT($B$2:$F$33,$K$3:$K$7),MMULT($B$2:$F$33,$K$3:$K$7)))=MAX(IF(MMULT($B$2:$F$33,{1;1;1;1;1})=M$2,IF($K$1>=MMULT($B$2:$F$33,$K$3:$K$7),MMULT($B$2:$F$33,$K$3:$K$7))))))

m10 셀
=LARGE(N(IF(MMULT($B$2:$F$33,{1;1;1;1;1})=M$2,IF($K$1>=MMULT($B$2:$F$33,$K$3:$K$7),MMULT($B$2:$F$33,$K$3:$K$7)))=MAX(IF(MMULT($B$2:$F$33,{1;1;1;1;1})=M$2,IF($K$1>=MMULT($B$2:$F$33,$K$3:$K$7),MMULT($B$2:$F$33,$K$3:$K$7)))))*($A$2:$A$33),ROW($A1))

한개만을 이용할 때는 근사값이 550 이고, 해당 조합번호는 16 이다.
그리고 세개를 이용할 때는 근사값이 1100 이고, 해당 조합번호는 10, 7 이다.
그리고 다섯개를 이용할 때는 만족하는 근사값이 존재하지 않았다.
(함수 중첩 제한에 걸려서 수식을 완성하지 못해 경우에 따라 에러가 표시되나
눈에 띄지 않게 조건부서식을 이용해 글꼴색을 적절히 조정해주었다.)

이 결과 조합 구성에 사용되는 구성 갯수가 많아야 된다면 '조합4'에 나열된 조합번호 17 을
참고하면 되고, 그 반대로 적어야 된다면 '조합3'에 나열된 조합번호 10, 7 을 참고하면 된다.

그리고 불필요해(?) 보이는 조합 나열은 제외하고 다시 정리하면...

음...
모양새가 안 이쁘군. -_-;; 
내가 언제 모양새에 신경 썼다고 이제 와서...

수식 형태는 유사하지만, 여기저기 수식이 여럿 나오니 헷갈린다.
이때 엑셀의 자동필터 기능을 활용하면 좀더 손 쉽다.

g2 셀
=IF($K$1>=MMULT($B2:$F2,$K$3:$K$7),MMULT($B2:$F2,$K$3:$K$7),"")

h2 셀
=SUM(B2:F2)

여기부터는 배열수식이 아니다.
그리고 g2:h33 범위에 아래와 같이 조건부서식을 적용하면 보기 수월해진다.

이제와 돌아보니, 앞서 그 길고 긴 수식들이 g, h 열을 참고하면 더욱 간편해질 수 있겠다!!!
이건 숙제~~~