일전에 오피스튜터에 올라온 질문인데, 상당히 유용한 질문이라 재활용하련다. 그런데 그때 답변을 검토해보니 문제가 아주아주 많다(-_-). 이번에도 오류가 있을지 모르니 주의 바란다.

 

위와 같이 제품별 생산일정이 예정되어 있고, 각각의 제품 생산에 소비되는 부품 구성 및 부품별 조달일정이 아래와 같다고 하자.

   

그랬을 때, 각각의 부품별로 생산일정에 차질을 초래하지 않는 최대 운용일을 찾으려 한다. 즉 생산일정에 따라 필요부품이 제때 공급되지 못 한다면 생산이 중단되므로 이를 방지하기 위해 그 시점을 미리 찾아내려는 것이다.

 

이런 식의 문제설정은 다양한 분야에서 적용 가능할 것 같은데, 일정별로 누적조달량(원)과 누적생산량(원)의 차이를 구해 검토하면 된다.

 

B29 셀:
=SUM($E8,SUMIF($B$17:$H$17,"<="&B$40,$B18:$H18))-SUM(MMULT($B8:$D8,$B$2:B$4))

배열수식이다. 그럼 위와 같은 방식을 가지고 부품별 안정적 최대 운용일을 구하면...

 

G8 셀:
=INDEX($B$1:$I$1,MIN(IF((MMULT(MMULT($B8:$D8,$B$2:$I$4),(TRANSPOSE($B$1:$I$1)<=($B$1:$I$1))*1))>($E8+MMULT($B18:$H18,(TRANSPOSE($B$17:$H$17)<=($B$1:$I$1))*1)),TRANSPOSE(ROW(INDIRECT("1:"&COLUMNS($B$1:$I$1))))-1,COLUMNS($B$1:$I$1))))

역시 배열수식이다. 참고로 애용하는 파이어폭스에서는 저 위에 긴 수식들이 제대로 나타나지 않는다. 구글 크롬에서도 제대로 볼 수 없다. 익스플로러에서나 볼 수 있다. 수식이 길어 불편한데, 누적표를 이용하면 좀더 간략하게 줄일 수 있다.

 

J29 셀:
=INDEX($B$28:$I$28,MIN(IF($B29:$I29<0,COLUMN($B$28:$I$28)-COLUMN($B$28),COLUMNS($B$28:$I$28))))

배열수식이다. 이번엔 부품이 아니라, 각 제품별로 주안점을 이동하면...

 

J2 셀:
=INDEX($B$1:$I$1,MIN(IF((MMULT($B2:$I2,(TRANSPOSE($B$1:$I$1)<=($B$1:$I$1))*1)*OFFSET($A$8:$A$15,0,MATCH($A2,$B$7:$D$7,0)))>($E$8:$E$15+MMULT($B$18:$H$25,(TRANSPOSE($B$17:$H$17)<=($B$1:$I$1))*1)),TRANSPOSE(ROW(INDIRECT("1:"&COLUMNS($B$1:$I$1))))-1,COLUMNS($B$1:$I$1))))

J5 셀:
=INDEX($B$1:$I$1,MIN(IF(MMULT($B$8:$D$15,MMULT($B$2:$I$4,(TRANSPOSE($B$1:$I$1)<=($B$1:$I$1))*1))>($E$8:$E$15+MMULT($B$18:$H$25,(TRANSPOSE($B$17:$H$17)<=($B$1:$I$1))*1)),TRANSPOSE(ROW(INDIRECT("1:"&COLUMNS($B$1:$I$1))))-1,COLUMNS($B$1:$I$1))))

둘 다 배열수식이다.

여기서 제품별 운용일(J2)은 해당 제품만 생산한다고 가정할 때, 안정적으로 생산 가능한 최대 운용일이다. 그리고 전체 운용일(J5)은 전 제품을 일정대로 생산 시 안정적으로 생산 가능한 최대 운용일이다.