[엑셀 문답] 원하는 시간대의 셀 개수를 구하려 합니다

제목만를 봐서는 어렵지 않은 문답일 것 같습니다. 엑셀을 이용하여 원하는 시간대의 셀의 수를 구하려 합니다. 엑셀 IF 함수, SUM 함수, OR 함수, HOUR 함수가 사용됩니다. 살펴 봅니다.

**님의 질문입니다

제목 : 날짜와 시간사이의 셀수 구하기
내용 :
안녕하세요..
엑셀에서 셀에 2007-02-01 13:01:30 이렇게 나와 있는 셀이 여러개 있다고 치고(시간은 다름)
그 날짜의 13시에서 14시 사이의 갯수를 구하려고 합니다.
어떻게 하면 구할 수 있을까요?

마늘빵의 답변입니다

.....
먼저 간단한 방법을 설명드립니다.

[그림1] 엑셀 기본 함수로 문제 해결합니다

A1:A7 범위에 날짜와 시간이 입력되어 있다면 B1 셀에 셀 포인터를 놓고 다음의 수식을 입력합니다.

=IF(OR(HOUR(A1)=13 , HOUR(A1)=14) , 1 , 0)

위의 수식은 엑셀 HOUR 함수를 이용하여 A1 셀의 날짜와 시간중 시간(HOUR)만 추출합니다. 이를 엑셀 IF 함수를 사용하여 조건 비교합니다. 조건이 둘이기에 OR 함수로 두 조건을 묶습니다. OR 함수는 여러 조건중 하나의 조건만을 만족한다면 참이 되는 함수입니다. 물음에서 요구하는 13시 와 14시는 1 을 반환 하고 나머지 시간은 0 을 반환 합니다.

이렇게 B1에 입력된 수식을 채우기 핸들을 이용하여 B7 셀까지 수식 복사합니다.
이후 B8 셀에 포인터를 놓고 B1:B7 의 합을 구합니다. 당연히 조건을 만족하는 셀만 1이 반환 되었기에 카운트하는 효과와 같습니다.

=SUM(B1:B7)

또 한가지 방법이 있다면 엑셀 배열 수식을 이용하는 방법입니다. 이 엑셀 배열 수식을 이용하면 위의 문제는 하나의 수식으로 결과를 얻을 수 있습니다. 하지만 초보자는 이해하시기가 어려울 수도 있습니다. 참고로 엑셀 배열 수식은 컴퓨터활용능력 1급에 출제됩니다.

먼저 빈 셀 , A8 셀 정도에 셀 포인터를 놓고 다음의 수식을 입력합니다.

=SUM( IF( HOUR(A1:A7)=13 , 1 , IF( HOUR(A1:A7)=14 , 1 , 0 ) ) )

위의 수식 입력 후 그냥 ENTER 키를 치시면 안 되고 CTRL+SHIFT+ENTER 키를 누르면 엑셀 배열 수식으로 입력되면서 수식의 앞뒤에 중괄호( { } ) 가 삽입 됩니다.

수식은 위의 간단한 방법으로는 두번으로 나뉘어 작업을 할 것을 한번에 모았다 생각하시면 되겠습니다.
먼저 A1:A7의 셀에서 HOUR 함수를 이용하여 시간을 각각 시간을 추출합니다. 이 결과를 IF 함수로 13 과 같은지 A1:A7 의 모든 셀 들과 비교합니다.

만족한는 셀이 있다면 1 을 반환하고 그렇지 않다면 14 와 같은지 두번째 비교를 하지요. 두번째에서도 만족하지 않으면 0을 반환합니다.(이는 13시에서 14시 사이의 시간이 아니라는 것입니다) 이 반환된 값들은 SUM 함수를 이용하여 모두 더해지는 것입니다.

[그림2] 원하는 시간대의 셀 수 - 엑셀 배열 수식

기본적으로 엑셀 HOUR 함수의 인수로 범위를 사용한다면 #VALUE! 오류가 발생합니다. 위의 수식은 CTRL+SHIFT+ENTER 로 엑셀 배열 수식을 완성하셔야 바른 결과를 얻을 수 있습니다.
( 엑셀의 배열 수식은 셀이 사용될 인수로 범위를 사용하여 그 범위의 각 셀들이 이 수식을 차례로 한번씩 완성하고 결과를 배열 처럼 저장해 두고 마지막 최종 수식에서 이 저장된 값을 사용하는 것입니다. 쉽지만 어렵게 느껴지는 어렵지만 쉬운 아리송한 엑셀의 수식 기능입니다 )

[그림3] 엑셀 배열 수식의 일시적 저장후 계산

이상입니다. 초심자이면 조금 귀찮을 수 있지만 첫번째 방법을 추천드립니다.
.....

엑셀 배열 수식을 처음 접하는 분들은 생소하고 잘 이해가지 않을 것입니다. 한번에 이해가 어렵지요. 컴퓨터활용능력 1급 취득이 목적이라면 반드시 아셔야하는 엑셀 수식 입력 기능이고 엑셀의 배열 수식을 이해 하시면 보다 많은 문제가 손쉽게 해결될 수도 있습니다. 이 엑셀 배열 수식은 다음번 게시물에서 보다 자세히 설명드리겠습니다.

간단한 물음을 하나 살펴보았습니다. 엑셀을 어려운 것도 아니고 어렵게 사용해야 하는 것도 아닙니다. 편하게 쉽게 사용하면서 자신에게 도움이 되면 됩니다. 엑셀을 쉬운곳에 하나씩 활용해 봅시다.

[엑셀함수] 논리함수 IF, AND, OR, NOT 을 정리합니다
[엑셀함수] 날짜와시간함수 HOUR 함수 강좌입니다
[엑셀함수] 수학과삼각함수 SUM 함수 강좌입니다

마늘빵.