통계로 세상보기

얼마전 오피스튜터 엑셀나눔터에 올라온 질문을 재구성해봤다.

매시간대별 위치하는 자료의 수를 찾아내 집계 양식에 표시하는게 요점이다.
그런데 (개인적으로) 시간은 처리하기 어려운 자료 형식 중 하나인거 같다.
그리고 이 질문 또한 내겐 그리 만만치 않은 질문이었다.

이 처리를 위해서는 크게 네부분으로 나눠 생각해야 된다.
10시 대를 예로들면,
10:00 ~ 11:00 사이에 포함된 건 ③
10:00 ~ 11:00 사이에 조금 걸친 건 ④
10:00 ~ 11:00 사이에 모두 걸친 건 ①
10:00 ~ 11:00 사이에 전혀 포함되지 않는 건 ②

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

시작이 상한값(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 함수가 없어도 결과는 같다.

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

여기서 '하한값'은 매시각이고 '상한값'은 '하한값'에 한시간을 더한 시각이다.

위에 수식이 '그런가 보다' 하고 조금이라도 수긍되 넘어간다면...
'전체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 함수는 좀 낯선 함수인데, 전치 함수로 기준일을 비틀어서 시간대에 더한거
외에는 앞 수식과 거의 동일하다.

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

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

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

시간.xls