- 최초 작성일: 2025-01-27
- 최종 수정일: 2026-09-23
- 조회수: 29 회
- 작성자: 권현욱 (엑셀러)
- 강의 제목: 시간대별 출근 인원수와 히스토그램
들어가기 전에: 엑셀로 이어진 러브 스토리
오래 전 엑셀 파일로 진행하던 강의를 웹 글로 복원한 것이라, "버튼을 눌러보세요"처럼 엑셀에서 직접 조작하라는 표현이 남아 있을 수 있습니다. 결과는 본문의 그림이나 표로 확인하실 수 있으며, 현재와 맞지 않는 내용은 보완하되 전반적인 스타일은 당시의 갬성(?)을 유지했습니다.
얼마 전에 이런 영상을 보았습니다 (유 퀴즈 온 더 블럭, tvN, 2019/8/14). 유재석/조세호 씨가 진행하는 프로그램이며 러닝 타임은 2분 24초 입니다.
그 동안 기사나 드라마 등에서 엑셀이 언급되는 것은 많이 보았습니다만, 이렇게 대놓고(?) 엑셀 이야기를 하고, 엑셀을 매개로 두 남녀가 훈훈하게 이어지는 내용은 처음인 것 같습니다. '엑.잘.男'으로 소개된 이 남성은 단축키만으로도 이런 성과(?)를 이루었는데, 엑셀의 수많은 기능들과 VBA까지 섭렵하면 과연 어떤 일이 일어날까요? ^^
- 최신 글을 불러오는 중...
시간대별 출근 인원수 구하고 히스토그램 만들기
핵심 요약: SUMPRODUCT + 분석 도구 팩으로 히스토그램 만들기
TIME 함수로 10분 단위 시간 구간을 만들고 SUMPRODUCT로 각 구간의 인원수를 센 다음, 분석 도구 팩의 히스토그램 기능으로 도수분포표와 차트를 한 번에 생성합니다.
- 1단계: D열에 TIME(0,10,0)으로 10분 단위 구간 생성
- 2단계: E열에 SUMPRODUCT로 구간별 인원수 계산
- 3단계: [파일]-[옵션]-[추가 기능]에서 분석 도구 팩 활성화
- 4단계: [데이터 분석]-[히스토그램]으로 도수분포표+차트 생성
완성 예
직원들의 출근시간표가 있는데 일정한 구간대별로 해당 인원수를 구할 수 없는가, 하는 질문이 있었습니다.
이것만 하면 재미가 적으니 아래와 같이 시간대별로 빈도수를 구하고 "히스토그램"을 만드는 방법까지 함께 알아보도록 하죠. 하나를 물어보면 열을 가르쳐주는 이 친절함!(또 건방을~~ ^^;).
시간대별 빈도수 구하기
D4 셀에는 시작되는 시간(여기서는 오전 8시)을 입력합니다. D5 셀에는 10분 단위로 구간값을 증가시키기 위해 다음의 수식을 입력하고 아래로 복사합니다. Time 함수를 이용하여 시간의 증감을 표현하였군요.
D5: =D4+TIME(0,10,0)E5 셀에는 8시 정각부터 8시 10분 이전에 출근한 사람 수를 카운팅 하기 위한 수식을 입력합니다. Sumproduct 함수를 이용하여 두 가지 조건을 동시에 충족하는지 여부를 체크합니다.
E5: =SUMPRODUCT(($B$4:$B$28>=D4)*($B$4:$B$28<(D4+TIME(0,10,0))))즉, (B4:B28 영역 중에서 오전 8시 "이상") And (B4:B28 영역 중에서 오전 8시 10분 "미만")인 데이터를 카운팅합니다.
시간대별 빈도수를 구하기 위한 수식은 이것 말고도 다양하게 표현할 수 있습니다. 예를 들어 Time 함수를 이용하지 않고 다음과 같이 해도 됩니다.
=SUMPRODUCT(($B$4:$B$28>=D4)*($B$4:$B$28<(D4+1/24/60*10)))엑셀에는 날짜와 시간이라는 개념이 없고 1900년 1월 1일부터 일련번호로 관리합니다. 1900년 1월 1일은 1, 1900년 1월 2일은 2, 1900년 1월 3일은 3, 하는 식이죠. 시간은 0부터 1사이의 소수로 관리합니다. 아무 셀에나 0.00001이라고 입력하고 '1:30:55 PM'이라는 시간 서식을 지정해 보세요. 12:00:01 AM이라고 표시됩니다.
위 수식의 뒷 부분(D4+1/24/60*10)은 바로 이 성질을 이용한 겁니다. 1(하루)을 24로 나누면 '한 시간'이 되고, 여기에 또 60을 나누면 '분'이 됩니다. 분에 10을 곱하니까 '10분'이 되는 원리입니다. 만약 5분 단위로 하려면 이렇게 하면 되겠죠?
=SUMPRODUCT(($B$4:$B$28>=D4)*($B$4:$B$28<(D4+1/24/60*5)))배열 수식(Array Formula)을 이용할 수도 있겠군요. E5 셀에 아래 수식을 입력하고 <Ctrl>+<Shift>+<Enter> 키를 함께 눌러 보세요.
{=SUM(($B$4:$B$28>=D4)*($B$4:$B$28<(D4+TIME(0,10,0))))}수식 좌우의 중괄호 { }는 손으로 입력하는 것이 아니라 <Ctrl>+<Shift>+<Enter> 키를 함께 누르면 자동으로 생기는 것임에 유의!
도수분포표와 히스토그램 만들기
(1) '파일 - 옵션' 메뉴를 선택합니다.
(2) 'Excel 옵션' 대화상자에서 '추가 기능 - 이동' 버튼을 클릭합니다.
(3) '추가 기능' 대화상자가 나타나면 '분석 도구' 항목에 체크 표시를 하고 '확인' 버튼을 누릅니다.
(4) '데이터' 탭에 가보면 '분석' 카테고리가 추가되어 있을 겁니다. '데이터 분석' 아이콘을 클릭하세요.
(5) '통계 데이터 분석' 대화상자에서 '히스토그램'을 선택하고 '확인' 버튼을 클릭합니다.
(6) '히스토그램' 대화상자의 각 항목을 지정합니다. 도수분포표를 만들 때 히스토그램을 함께 생성하려면 '차트 출력' 항목에 체크 표시를 해 주면 됩니다.
(7) '확인' 버튼을 클릭하면 도수분포표와 히스토그램이 함께 만들어집니다.
히스토그램의 서식을 변경하는 것은 일반 차트의 그것과 같으므로 따로 설명하지는 않겠습니다. '완성 예'를 참고로 차트 서식을 설정해 줍니다.
참고: 이미지에 대하여
이 글은 원래 네이버 포스트에 게재되었던 글로, 네이버 포스트 서비스 종료로 네이버 블로그로 옮기는 과정에서 원본 이미지가 소실되었습니다. 서두의 '유 퀴즈 온 더 블럭' 방송 화면은 제공받은 이미지를 그대로 사용했고, 나머지 데이터·대화상자 화면은 본문 설명을 바탕으로 재구성한 예시이며 실제 엑셀 화면과 세부 디자인은 다를 수 있습니다.
정리 — 시간대별 히스토그램 만들기
| 단계 | 동작 |
|---|---|
| 1. 시간 구간 생성 | D5 =D4+TIME(0,10,0) |
| 2. 구간별 인원수 | E5 =SUMPRODUCT((조건1)*(조건2)) |
| 3. 분석 도구 팩 활성화 | 파일-옵션-추가 기능-이동-분석 도구 체크 |
| 4. 히스토그램 생성 | 데이터 분석-히스토그램-차트 출력 체크 |
자주 묻는 질문 (FAQ)
Q1. 시간대별 인원수는 어떤 수식으로 구하나요?
SUMPRODUCT 함수로 '이상' 조건과 '미만' 조건을 동시에 충족하는 셀의 개수를 셉니다. 예: =SUMPRODUCT(($B$4:$B$28>=D4)*($B$4:$B$28<(D4+TIME(0,10,0)))).
Q2. 분석 도구 팩은 어떻게 추가하나요?
[파일] - [옵션] - [추가 기능] 범주에서 [이동] 버튼을 클릭한 뒤, [추가 기능] 대화상자에서 [분석 도구]에 체크하고 확인하면 [데이터] 탭에 [분석] 카테고리와 [데이터 분석] 아이콘이 추가됩니다.
Q3. 히스토그램과 도수분포표를 한 번에 만들 수 있나요?
네. [데이터 분석] - [히스토그램] 대화상자에서 입력 범위와 계급 구간을 지정하고 [차트 출력]에 체크하면, 도수분포표와 히스토그램 차트가 함께 생성됩니다.
마치며
VBA에 대한 기초 지식을 공부하실 분은 아이엑셀러 닷컴 사이트 상단 메뉴에서 [Excel 강의] - [Excel 입문]을 먼저 보시면 이해하기 쉽습니다.
