이번 강의는 얼마 전 다른 사이트에 올라온 질문을 재구성한 것이다.

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

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

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

=IF(AND($B4K$1),1,0)

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

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

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

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

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

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

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

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

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

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

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

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

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

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