- 최초 작성일: 2002-09-24
- 최종 수정일: 2026-09-29
- 조회수: 26 회
- 작성자: 권현욱 (엑셀러)
- 강의 제목: 피벗 테이블 그룹화로 점수 구간별 인원수 구하기
들어가기 전에
오래 전 엑셀 파일로 진행하던 강의를 웹 글로 복원한 것이라, "버튼을 눌러보세요"처럼 엑셀에서 직접 조작하라는 표현이 남아 있을 수 있습니다. 결과는 본문의 그림이나 표로 확인하실 수 있으며, 현재와 맞지 않는 내용은 보완하되 전반적인 스타일은 당시의 갬성(?)을 유지했습니다.
You don't know what you've got until you've lost it.
어떤 것을 잃어버릴 때까지 무엇을 잃었는지 알 수 없습니다. 정작 잃고 나서야 비로소 잃은 것의 가치를 깨닫게 된다는 뜻이겠지요.
추석 연휴는 잘들 보내셨습니까? 개인적으로 이번 추석에는 가족이란, 특히 아버지란 어떤 존재인가에 대해 많이 생각해 보았습니다. 마침 이런 심경을 잘 대변해 주는 글이 있어 자유 게시판에 '아버지란 무엇인가?'라는 제목으로 올려두었으니 관심 있는 분은 읽어보시기 바랍니다. 자식을 낳아서 키워 보아야 부모의 마음을 이해한다는 말을 이제 조금은 알 듯합니다. 이제 철이 들어가나 봅니다. *^^*
- 최신 글을 불러오는 중...
피벗 테이블 그룹화로 점수 구간별 인원수 구하기
핵심 요약
피벗 테이블의 그룹화 기능을 사용하면 점수·나이 등 연속된 숫자 데이터를 원하는 구간(10점 단위 등)으로 묶어 구간별 인원수를 손쉽게 집계할 수 있습니다.
- 피벗 테이블에서 값 필드를 마우스 오른쪽 버튼으로 클릭해 그룹화 대화상자를 엽니다.
- 시작값·끝값·단위를 지정하면 구간별로 자동으로 묶입니다.
- 집계 함수를 '개수'로 바꾸면 구간별 인원수(도수분포)를 바로 확인할 수 있습니다.
질문: COUNTIF로 도수분포표를 만들 수 없을까요?
사랑니 때문에 염증이 생겨 연휴에 술 한잔도 못하고 새벽에 깨어나 책을 보다 보니 103페이지까지 보게 되었습니다. IF문에서는 '다중 IF'문에서 7번까지 중복 사용할 수 있다고 나오는데, COUNTIF문에서는 '다중 COUNTIF'문 같은 것은 없을까요? 앞으로 배울 다른 방법들 말고 COUNTIF 문으로 할 수 없나요?
예) 성적표에서 70~79: 몇 명, 80~89: 몇 명, 90~100: 몇 명
여기에 이런 질문을 해도 되는지 모르겠지만 일단 해보겠습니다. 엑셀을 이용해서 숙제를 하고 있는데(통계요... 숙제를 해달라는 질문은 아닙니다) 모르는 것이 있어서 질문드립니다. 여러 문제 중에 도수분포표를 만드는 것이 있는데, 도수를 하나씩 세는 것이 너무 힘들고 헷갈려서 그러는데 혹시 엑셀에서 이런 것을 구할 수 없을까요? 엑셀을 배워본 적이 없어서 잘 모르겠습니다. 예를 들어 "나이에 대한 도수분포표"를 구한다면 간격을 1~10, 11~20, 21~30 이렇게 하고 자료를 보고 몇 명씩 있는지 알아볼 때, 지금까지는 완전히 노가다로 자료를 보면서 하나씩 세어봤는데 너무 힘들고 헷갈려서 맞았다는 보장도 없습니다.
즉 셀을 전부 블록 지정한 후에 나이가 1~10에 해당되는 사람은 몇 명, 11~20에 해당하는 사람은 몇 명 이렇게 나오도록 할 수는 없을까요? COUNTIF로 해보니 "남, 여" 같은 구분이나 블록 지정한 곳에서 숫자 10이 몇 개 있는지 같은 건 되는데, 위처럼 1~10, 11~20 구간은 안 되더라고요.
이런 경우 여러 가지 방법으로 해결이 가능합니다. COUNTIF 함수나 배열 수식을 사용하는 방법은 '그룹목록 만들기' 게시물을 참고하시고, 이번 시간에는 피벗 테이블을 사용하여 해결하는 방법에 대해 알아보겠습니다.
피벗 테이블을 이용하면 편리한 점이 한두 가지가 아니지만, 특히 데이터를 그룹별로 구분하여 목록을 만들 수 있다는 사실을 아는 분은 생각보다 많지 않은 듯합니다. 즉 [표 1]과 같은 형태를 이용하여 [표 2]와 같은 그룹 목록을 만들 수 있습니다.
[표 1]
| 번호 | 성명 | 국어 | 영어 | 수학 | 총점 | 평균 | 평가 |
|---|---|---|---|---|---|---|---|
=ROW()-1 | 이무열 | 50 | 53 | 61 | =SUM(D71:F71) | =G71/3 | =IF(H71>=90,"수",IF(H71>=80,"우",IF(H71>=70,"미",IF(H71>=60,"양","가")))) |
=ROW()-1 | 강감찬 | 96 | 63 | 94 | =SUM(D72:F72) | =G72/3 | =IF(H72>=90,"수",IF(H72>=80,"우",IF(H72>=70,"미",IF(H72>=60,"양","가")))) |
=ROW()-1 | 권율 | 65 | 82 | 85 | =SUM(D73:F73) | =G73/3 | =IF(H73>=90,"수",IF(H73>=80,"우",IF(H73>=70,"미",IF(H73>=60,"양","가")))) |
=ROW()-1 | 을지문덕 | 53 | 85 | 95 | =SUM(D74:F74) | =G74/3 | =IF(H74>=90,"수",IF(H74>=80,"우",IF(H74>=70,"미",IF(H74>=60,"양","가")))) |
=ROW()-1 | 박경근 | 87 | 72 | 88 | =SUM(D75:F75) | =G75/3 | =IF(H75>=90,"수",IF(H75>=80,"우",IF(H75>=70,"미",IF(H75>=60,"양","가")))) |
=ROW()-1 | 박유진 | 93 | 89 | 90 | =SUM(D76:F76) | =G76/3 | =IF(H76>=90,"수",IF(H76>=80,"우",IF(H76>=70,"미",IF(H76>=60,"양","가")))) |
=ROW()-1 | 권예지 | 96 | 98 | 100 | =SUM(D77:F77) | =G77/3 | =IF(H77>=90,"수",IF(H77>=80,"우",IF(H77>=70,"미",IF(H77>=60,"양","가")))) |
=ROW()-1 | 주정민 | 99 | 90 | 85 | =SUM(D78:F78) | =G78/3 | =IF(H78>=90,"수",IF(H78>=80,"우",IF(H78>=70,"미",IF(H78>=60,"양","가")))) |
=ROW()-1 | 윤영미 | 63 | 72 | 63 | =SUM(D79:F79) | =G79/3 | =IF(H79>=90,"수",IF(H79>=80,"우",IF(H79>=70,"미",IF(H79>=60,"양","가")))) |
=ROW()-1 | 김정구 | 50 | 74 | 67 | =SUM(D80:F80) | =G80/3 | =IF(H80>=90,"수",IF(H80>=80,"우",IF(H80>=70,"미",IF(H80>=60,"양","가")))) |
=ROW()-1 | 이순신 | 63 | 91 | 93 | =SUM(D81:F81) | =G81/3 | =IF(H81>=90,"수",IF(H81>=80,"우",IF(H81>=70,"미",IF(H81>=60,"양","가")))) |
=ROW()-1 | 한기찬 | 88 | 59 | 87 | =SUM(D82:F82) | =G82/3 | =IF(H82>=90,"수",IF(H82>=80,"우",IF(H82>=70,"미",IF(H82>=60,"양","가")))) |
=ROW()-1 | 조재희 | 64 | 55 | 95 | =SUM(D83:F83) | =G83/3 | =IF(H83>=90,"수",IF(H83>=80,"우",IF(H83>=70,"미",IF(H83>=60,"양","가")))) |
=ROW()-1 | 남상구 | 66 | 94 | 97 | =SUM(D84:F84) | =G84/3 | =IF(H84>=90,"수",IF(H84>=80,"우",IF(H84>=70,"미",IF(H84>=60,"양","가")))) |
[표 2]
(1) 데이터베이스 내의 임의의 셀 선택 후 피벗 테이블 만들기
데이터베이스 내의 임의의 셀을 선택하고 피벗 테이블을 만듭니다. 이때 필드의 배치는 아래와 같이 합니다.
(2) 피벗 테이블 생성 결과 확인
그러면 아래와 같은 형태의 피벗 테이블이 만들어집니다.
(3) '평균' 필드 그룹화하기
평균 부분에서 마우스 오른쪽 버튼을 클릭한 다음 '그룹 및 하위 수준 표시 - 그룹' 메뉴를 선택합니다.
(4) 그룹화 대화상자 설정
그룹화 대화상자에 아래와 같이 지정해 줍니다.
(5) 필드 설정 고급 옵션 열기
피벗 테이블을 마우스 오른쪽 버튼으로 클릭하고 '필드 설정' 메뉴를 선택합니다. 대화상자가 나타나면 '고급' 버튼을 클릭합니다.
(6) 내림차순 정렬 지정
'피벗 테이블 필드 고급 옵션' 대화상자에서 '내림차순' 항목을 선택하고 '확인'을 누릅니다.
(7) 집계 함수를 '개수'로 변경
피벗 테이블 좌측 상단의 '합계: 평균' 부분을 마우스 오른쪽 버튼으로 클릭한 다음 '필드 설정' 메뉴를 선택합니다. '사용할 함수'를 '개수'로 지정하고 '확인'을 클릭하면 10점 단위로 그룹핑된 피벗 테이블이 만들어집니다.
(8) '번호' 필드의 부분합 없애기
'번호' 필드를 더블 클릭한 다음 '부분합' 항목을 '없음'으로 지정해 줍니다.
(9) '평가' 필드도 동일하게 처리
'평가' 필드도 마찬가지 방법으로 '부분합' 항목을 '없음'으로 지정하면 피벗 테이블의 형태가 이렇게 바뀝니다.
이제 이 피벗 테이블의 서식을 상황에 맞게 적당히 수정하면 됩니다. 분량이 좀 많아졌습니다만, 흐름만 잘 기억하시면 되겠지요?
정리 — 피벗 테이블 그룹화로 구간별 인원수 구하기
| 단계 | 내용 |
|---|---|
| 그룹화 시작 | 그룹화할 값 필드에서 마우스 오른쪽 클릭 → '그룹 및 하위 수준 표시 - 그룹' |
| 구간 지정 | 그룹화 대화상자에서 시작값·끝값·단위(예: 10) 입력 |
| 정렬 방향 | '필드 설정 - 고급' 옵션에서 오름차순/내림차순 지정 가능 |
| 집계 함수 변경 | 값 필드의 '사용할 함수'를 '개수'로 바꾸면 구간별 인원수 산출 |
| 부분합 제거 | 불필요한 필드는 '부분합 없음'으로 지정해 표를 깔끔하게 정리 |
자주 묻는 질문 (FAQ)
Q1. COUNTIF 함수만으로도 구간별 인원수를 구할 수 있나요?
네, COUNTIFS나 배열 수식을 사용하면 가능합니다. 다만 구간이 여러 개이거나 자주 바뀌는 경우에는 피벗 테이블의 그룹화 기능이 훨씬 간편합니다.
Q2. 그룹화 단위를 10이 아닌 다른 값으로 지정할 수 있나요?
네, 그룹화 대화상자의 '단위' 값을 원하는 숫자로 바꾸면 5점, 20점 등 어떤 간격으로도 구간을 나눌 수 있습니다.
Q3. 그룹화 후 원본 데이터가 추가되면 피벗 테이블도 자동으로 갱신되나요?
자동으로는 갱신되지 않습니다. 원본 데이터를 수정한 뒤에는 피벗 테이블을 마우스 오른쪽 버튼으로 클릭해 '새로 고침'을 실행해야 최신 데이터가 반영됩니다.
마치며
피벗 테이블의 그룹화 기능은 점수뿐 아니라 나이, 매출액 등 구간별로 나눠서 살펴봐야 하는 모든 숫자 데이터에 똑같이 적용할 수 있습니다. COUNTIF나 배열 수식으로 일일이 구간을 세고 계셨다면, 오늘 소개한 방법으로 훨씬 빠르게 정리해 보세요!
