이번 강좌는 얼마 전 오피스튜터 엑셀나눔터에 올라온 질문을 재구성한 것이다.

 

매시간대별 위치하는 자료의 수를 찾아내 집계 양식에 표시하는 것이 요점이다. 그런데 (개인적으로) 시간은 처리하기 어려운 자료 형식 중 하나인 것 같다. 그리고 이 질문 또한 내겐 그리 만만치 않은 질문이었다. 이 처리를 위해서는 크게 네부분으로 나눠 생각해야 된다. 10시 대를 예로 들면,

10:00 ~ 11:00 사이에 포함된 건 ③
10:00 ~ 11:00 사이에 조금 걸친 건 ④
10:00 ~ 11:00 사이에 모두 걸친 건 ①
10:00 ~ 11:00 사이에 전혀 포함되지 않는 건 ②

여러분이 오튜 나눔터를 관심있게 지켜봐 왔다면 다른 분들에 자료를 통해 적절한 수식을 대부분 찾을 수 있다(예전에 오튜에서 본 방식인데 어느 분의 것이었는지는 기억나지 않는다. -_-).

시작이 상한값(11:00) 보다 작으면서, 종료가 하한값(10:00) 보다 큰 경우
→ (시작<상한값) and (종료>하한값)

그런데 이 처리방식이 미쳐 떠오르지 않아 답변엔 삽질(?)이 많이 첨부되어 있다. 그리고 수식에 오류도 있다. 아무튼 엑스큐즈 하고, 이를 이용해 수식으로 처리하면...

=IF(AND($B4<K$3,$C4>K$1),1,0)

위 수식은, "조건을 만족한다면 1, 만족하지 않는다면 0"을 표시해준다. 뭐 별거 아니다. 그리고 이 수식은 엑셀의 성질(?)을 적절히 활용하면 얼마든지 다르게 변형할 수 있다.

=IF(($B4<K$3)*($C4>K$1),1,0)
=N(AND($B4<K$3,$C4>K$1))
=N(($B4<K$3)*($C4>K$1))
=1*(($B4<K$3)*($C4>K$1))
=(($B4<K$3)*($C4>K$1))

이처럼 If, And 함수의 역할을 다르게 나타내는 것이 헷갈린다면 경험상 기능키 <F9>을 이용해 임시 계산 결과를 확인해보면 좀더 쉽게 다가갈 수 있다. 그런데 이런 경우는 어떻게 될까? 즉, 시작과 종료가 (23:15, 10:20)인 "달밤에 체조"한 경우...

애석하게도 위에 수식으로는 정상적인 결과를 얻을 수 없다. 이는 위 수식이 시간이 갖는 특성을 반영해 해당 집계 양식에 나타내지 못하기 때문이다. 즉 시간이란 주기를 가지며 끝 없이 연이어져 있는데, 이를 반영하려면 종료 시간이 10:20이 아니라 34:20으로 입력해야 되고, 집계 양식도 수정해야 된다(시간대를 길게 쭉~~~ 늘려야 되는...).

그러나 이는 누가 봐도 수긍하기 어려운 집계 양식이니, 천상 수식을 조정하는 것 말고는 다른 도리가 없겠다.

=N(($B4<D$3+((D$1<$C4)*($B4>$C4)))*(($C4+($B4>$C4))>D$1))

음... 괄호와 부등호만 보이고 뭔 소린지 모르겠다. -_-; 그런데 설마 N 함수 몰라 모르겠다는 얘긴 말자. 위에서 이미 봤듯이 이 경우는 N 함수가 없어도 결과는 같다. N 함수에 대해서는 도움말이나 다음 강좌를 참고하시기 바란다.

http://www.iExceller.com/MyXls/Lectures/X0226.zip

=변환((시작<상한값+((하한값<종료)*(시작>종료)))*((종료+(시작>종료))>하한값))

여기서 '하한값'은 매시각이고 '상한값'은 '하한값'에 한 시간을 더한 시각이다. 위의 수식이 '그런가 보다' 하고 조금이라도 이해가 되어 넘어간다면, '전체1'도 별도 처리과정 없이, 위 수식과 비슷하게 구할 수 있다. 물론 배열수식이다.

=SUM(N(($B4:$B8<D$3+((D$1<$C4:$C8)*($B4:$B8>$C4:$C8)))*(($C4:$C8+($B4:$B8>$C4:$C8))>D$1)))

앞에 (위, 위) 수식이 '그런가 보다'하고 넘어갈 수 있었다면, 위의 수식도 '그런가 보다'하고 당연히 넘어갈 수 있다. 단지 Sum 함수를 추가하고 셀 대신 범위 그리고 배열수식을 이용했다는 것이 다를 뿐이다.

=합(변환((시작_범위<=상한값+((하한값<종료_범위)*(시작_범위>종료_범위)))*((종료_범위+(시작_범위>종료_범위))>하한값)))

이를 통해 각각의 자료별 시간대 표시 과정을 거치지 않고 바로 전체를 구할 수 있다. 그런데 자료가 아래와 같고...

날짜별 시간대별로 보고자 한다면...

=SUM(N((($B$2:$B$6<$E4+F$3)*($C$2:$C$6>$E4+F$1))))

주어진 자료에 '일' 정보가 함께 기재되어 있기에, 앞에 수식 보다 훨씬(?) 쉽다. 왜냐하면 이미 처음에 제시된 (시작<상한값) and (종료>하한값) 의 틀을 그대로 유지하고 있기 때문이다.

=합(변환(((시작_범위<기준일+상한값)*(종료_범위>기준일+하한값))))

여기서 '기준일'은 매날짜다. 그리고 '전체1'도 별도 과정없이 직접 구할 수 있다.

=SUM(N((($B$2:$B$6<TRANSPOSE($E$4:$E$6)+F$3)*($C$2:$C$6>TRANSPOSE($E$4:$E$6)+F$1))))

Transpose 함수는 좀 낯선 함수인데, 전치 함수로 기준일을 비틀어서 시간대에 더한거 외에는 앞 수식과 거의 동일하다.

=합(변환(((시작_범위<전치(기준일_범위)+상한값)*(종료_범위>전치(기준일_범위)+하한값))))

엑셀은 기본적으로 '계산'이 주목적이다. 그리고 계산을 위해서는 수식이 필요하다. 그리고 수식은 가급적 체계적이고 단순함에서 출발하는게 정신건강(?)에 유익하다. 그러나... 그러기 위해서는 머리를 좀 굴려야 된다(그게 부족하다면 내 꼴 나는거다 -_-;)

이렇게 예제를 만들고 보니, 오튜에 올린 답변이 너무나도 허접했다는걸 뼈저리게 느끼고 있다. 혹 그 질문자가 이 글을 본다면 적절히 조정해 주시기 바란다. 그리고 그 질문 고맙게 생각한다. 그 덕분에 내 부족함을 돌아볼 수 있었고 또 많이 배웠다.