일전에 다른 곳에 올라온 질문인데 상당히 유용하다고 생각되어 정리해 본다. 그 때 답변을 검토해 보니 문제가 많았다.(-_-) 이번에도 오류가 있을 지 모르니 주의하시기 바란다.
위와 같이 제품별 생산일정이 예정되어 있고 각각의 제품 생산에 소비되는 부품 구성 및 부품별 조달 일정이 아래와 같다고 하자.
각 부품별로 생산 일정에 차질을 초래하지 않는 최대 운용일을 찾으려 한다. 즉 생산 일정에 따라 필요 부품이 제때 공급되지 못하면 생산이 중단되므로 이를 방지하기 위해 그 시점을 미리 찾아내려는 것이다.
이런 종류의 문제는 다양한 분야에서 적용 가능할 것 같다. 일정별로 누적조달량(원)과 누적생산량(원)의 차이를 구해 검토하면 된다.
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, J5 셀에 입력된 수식은 아래와 같으며, 둘 다 배열수식이다.
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)은 전 제품을 일정대로 생산할 때 안정적으로 생산 가능한 최대 운용일이다.
