더하기를 하자 31 - 엑셀로 공백 시간 찾아내기

작성된 일정표에서 일정 사이사이에 공백인 시간이 있는지

공백 시간이 있다면 언제부터 언제까지 인지가 궁금한 경우에

이를 찾기 위한 엑셀 수식이다.

 

 

간트 차트가 시각적으로 나타내기 유용하니

엑셀로 간트 차트 만드는 방법은 알아서 검색해보기 바라고,

위에 그림에 나오듯 중간중간 일정이 빈 시간이 존재한다.

언제가 일정이 빈, 공백인지 시각적으로는 쉽게 눈에 띄는데

이걸 엑셀 수식으로 찾아내려면 이리저리 궁리를 많이 해야 한다.

 

가장 손쉬운 방법은 일정 목록에 "묶음"을 나누는 열을 추가하는 것이다.

참고로 여기서 "묶음"은

현재 셀이 이전 셀의 시작과 종료 시간에 위치하면 같은 묶음으로 분류했다.

 

a3

=IF(SUMPRODUCT((C$2:C2<=C3)*(C3<=D$2:D2)),MAX(A$2:A2),N(A2)+1)

 

그리고 이번엔 별도 셀이나 열을 사용하지 않기 위해 굳이(?) 복잡한 수식으로,

언제나 그렇듯 MMult 함수를 이용해 "묶음"값을 만들어

a열과 비교해 본다.

이하 수식은 모두 배열수식이므로 입력 시 Ctrl, Shift, Enter 키를 동시에 눌러야 한다.

 

f3 (f3:f12 범위 동시 입력)

=MMULT(1*(ROW($C$3:$C$12)>=TRANSPOSE(ROW($C$3:$C$12))),
            1*(MMULT((TRANSPOSE($C$2:$C$11)<=$C$3:$C$12)
                          *($C$3:$C$12<=TRANSPOSE($D$2:$D$11))
                          *(ROW($C$3:$C$12)>=TRANSPOSE(ROW($C$3:$C$12))),
                           ROW($C$3:$C$12)^0)=0))

 

 

예제에서는 일시를 함께 사용한 경우인데,

날짜만으로도 가능하고

시간만으로도 가능하다.

 

가장 간편한 수식은 "묶음"을 이용한 수식이다.

 

L4

=IF(MAX($A$3:$A$12)>$I4,
     MAX(IF($A$3:$A$12
              =($I4+0),
              $D$3:$D$12)),
     "")

 

M4

=IF(MAX($A$3:$A$12)>$I4,
     MIN(IF($A$3:$A$12
              =($I4+1),
              $C$3:$C$12)),
     "")

 

위 수식에서 MMult 함수로 이용한 수식을 바꿔 대입하면 아래가 된다.

 

j4

=IF(MAX(MMULT(1*(ROW($C$3:$C$12)>=TRANSPOSE(ROW($C$3:$C$12))),
                     1*(MMULT((TRANSPOSE($C$2:$C$11)<=$C$3:$C$12)
                                  *($C$3:$C$12<=TRANSPOSE($D$2:$D$11))
                                  *(ROW($C$3:$C$12)>=TRANSPOSE(ROW($C$3:$C$12))),
                                   ROW($C$3:$C$12)^0)=0)))>$I4,
     MAX(IF(MMULT(1*(ROW($C$3:$C$12)>=TRANSPOSE(ROW($C$3:$C$12))),
                        1*(MMULT((TRANSPOSE($C$2:$C$11)<=$C$3:$C$12)
                                     *($C$3:$C$12<=TRANSPOSE($D$2:$D$11))
                                     *(ROW($C$3:$C$12)>=TRANSPOSE(ROW($C$3:$C$12))),
                                      ROW($C$3:$C$12)^0)=0))
               =($I4+0),
               $D$3:$D$12)),
      "")

 

k3

=IF(MAX(MMULT(1*(ROW($C$3:$C$12)>=TRANSPOSE(ROW($C$3:$C$12))),
                     1*(MMULT((TRANSPOSE($C$2:$C$11)<=$C$3:$C$12)
                                  *($C$3:$C$12<=TRANSPOSE($D$2:$D$11))
                                  *(ROW($C$3:$C$12)>=TRANSPOSE(ROW($C$3:$C$12))),
                                    ROW($C$3:$C$12)^0)=0)))>$I4,
     MIN(IF(MMULT(1*(ROW($C$3:$C$12)>=TRANSPOSE(ROW($C$3:$C$12))),
                        1*(MMULT((TRANSPOSE($C$2:$C$11)<=$C$3:$C$12)
                                     *($C$3:$C$12<=TRANSPOSE($D$2:$D$11))
                                     *(ROW($C$3:$C$12)>=TRANSPOSE(ROW($C$3:$C$12))),
                                      ROW($C$3:$C$12)^0)=0))
               =($I4+1),
               $C$3:$C$12)),
     "")

 

 

그리고 쓸 일이 있기를 바라고 구상한 좀 더 많이 복잡한 수식이다.

복잡한 만큼 수식 하나로 시작과 종료를 모두 구할 수 있다.

 

n4

=IF((2*ROWS($N$3:$N3)-MOD(COLUMNS($N$3:N3),2))
      <SUM(1*((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)>0)
 <>(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))-0))
          *((ROW(INDIRECT("1:"&(MAX($D$3:$D$12)-MIN($C$3:$C$12))*1440))-0)<TRANSPOSE(($D$3:$D$12-MIN($C$3:$C$12))*1440)),
          ROW($C$3:$C$12)^0)>0))),
MIN($C$3:$C$12)+1/LARGE(
((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)>0)
 <>(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))-0))
          *((ROW(INDIRECT("1:"&(MAX($D$3:$D$12)-MIN($C$3:$C$12))*1440))-0)<TRANSPOSE(($D$3:$D$12-MIN($C$3:$C$12))*1440)),
          ROW($C$3:$C$12)^0)>0))
 /(ROW(INDIRECT("1:"&(MAX($D$3:$D$12)-MIN($C$3:$C$12))*1440))),
 2*ROWS($N$3:$N3)-MOD(COLUMNS($N$3:N3),2))/1440,
"")