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

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

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

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열을 참고하면 더욱 간편해질 수 있겠다. 이건 숙제~~~