HOME > 사용자 제공 자료 > 통계로 세상보기

'무지개타고'님의 통계강좌

- 최초 작성일 : 2008-10-14
- 최종 수정일 : 2008-10-14

- 강좌 읽음수 : 3,346회
- 자료 작성자 : 무지개타고 (조석현님)

- 자료 편집자 :

강좌 제목 : 통계로 세상보기 - (83) 더하기를 하자 - 세번째

 

'무지개 타고'님의 통계 강좌를 시작한 지도 1년이 넘었군요. 우리 주변의 친숙한 주제를 통계와 접목하여 좋은 강의를 하고 계시는 무지개 타고(조석현)님께 다시 한번 감사드립니다. '통계로 세상보기' 블로그에 들르셔서 감사의 인사를 남기는 센스를 발휘하셔도 좋겠지요? ^^

통계로 세상보기 블로그


예제 파일 내려받기


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

73

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

72

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

74

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),))

물론 배열수식이다(별도 말이 없으면 오늘은 모두 배열수식이다). 그런데 문제가 있다. 제약조건을 만족하는 합을 구성하는 조합이 유일하지 않다는 것.

75

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

76

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))
 

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

77

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을 참고하면 된다. 그리고 불필요해(?) 보이는 조합 나열은 제외하고 다시 정리하면...

78

음... 모양새가 안 이쁘군. 내가 언제 모양새에 신경 썼다고 이제 와서... 수식 형태는 유사하지만, 여기저기 수식이 여럿 나오니 헷갈린다. 이 때 엑셀의 자동필터 기능을 활용하면 좀더 손 쉽다.

79

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

80

81

82

83


Previous

Next

Copyright ⓒ Since 2008 아이엑셀러 닷컴. All rights reserved.

이 페이지의 저작권은 컨텐츠 제공자에게 있습니다.

본 강좌의 내용은 필자 개인의 견해이며, 아이엑셀러 닷컴의 공식 견해와 일치하지 않을 수도 있습니다.