더하기를 하자 32 - 겹치는 일시 엑셀로 계산하기

저번에 공백 일시를 구하는 것을 살펴봤는데

이번에는 그 반대인 겹치는 일시를 엑셀로 구해봤다.

 

 

간트 차트를 통해 어느 일시에 겹치는지 쉽게 파악되는데

이를 엑셀 수식으로 계산하면...

둘 다 배열수식이다.

 

q4 (시작)

=IF(OR(R3="",
       SUM(IF((MMULT(
                 (TRANSPOSE(($C$3:$C$12-MIN($C$3:$C$12))*1440)<=(ROW(INDIRECT("1:"&(MAX($D$3:$D$12)-MIN($C$3:$C$12))*1440))-1))
                  *((ROW(INDIRECT("1:"&(MAX($D$3:$D$12)-MIN($C$3:$C$12))*1440))-1)<TRANSPOSE(($D$3:$D$12-MIN($C$3:$C$12))*1440)),
                  ROW($C$3:$C$12)^0)>1)
                 *(N(R3)<((ROW(INDIRECT("1:"&(MAX($D$3:$D$12)-MIN($C$3:$C$12))*1440))-1)/1440+MIN($C$3:$C$12))),
                 (ROW(INDIRECT("1:"&(MAX($D$3:$D$12)-MIN($C$3:$C$12))*1440))-1)))=0),"",
    MIN(IF((MMULT(
             (TRANSPOSE(($C$3:$C$12-MIN($C$3:$C$12))*1440)<=(ROW(INDIRECT("1:"&(MAX($D$3:$D$12)-MIN($C$3:$C$12))*1440))-1))
              *((ROW(INDIRECT("1:"&(MAX($D$3:$D$12)-MIN($C$3:$C$12))*1440))-1)<TRANSPOSE(($D$3:$D$12-MIN($C$3:$C$12))*1440)),
              ROW($C$3:$C$12)^0)>1)
             *(N(R3)<((ROW(INDIRECT("1:"&(MAX($D$3:$D$12)-MIN($C$3:$C$12))*1440))-1)/1440+MIN($C$3:$C$12))),
             (ROW(INDIRECT("1:"&(MAX($D$3:$D$12)-MIN($C$3:$C$12))*1440))-1)))/1440+MIN($C$3:$C$12))

 

r4 (종료)

=IF(Q4="","",
    MIN(IF((MMULT(
              (TRANSPOSE(($C$3:$C$12-MIN($C$3:$C$12))*1440)<=(ROW(INDIRECT("1:"&(MAX($D$3:$D$12)-MIN($C$3:$C$12))*1440))-1))
               *((ROW(INDIRECT("1:"&(MAX($D$3:$D$12)-MIN($C$3:$C$12))*1440))-1)<TRANSPOSE(($D$3:$D$12-MIN($C$3:$C$12))*1440)),
               ROW($C$3:$C$12)^0)<=1)
              *(Q4<((ROW(INDIRECT("1:"&(MAX($D$3:$D$12)-MIN($C$3:$C$12))*1440))-1)/1440+MIN($C$3:$C$12))),
              (ROW(INDIRECT("1:"&(MAX($D$3:$D$12)-MIN($C$3:$C$12))*1440))-1)))/1440+MIN($C$3:$C$12))