부분합과 피벗 테이블
쉽게 말하면
부분합은 표 안에서 그룹마다 소계를 끼워 넣어 줘요. '부서별 급여 합계'를 구하려면 먼저 부서로 정렬해 같은 부서끼리 모아 둔 뒤 부분합을 실행해야 해요. 피벗 테이블은 원래 표는 그대로 두고, 다른 곳에 '행은 부서, 열은 직급, 값은 급여 합계' 같은 요약표를 마우스로 끌어다 만들어요. 원본이 바뀌면 피벗 테이블은 '새로 고침'을 눌러야 반영돼요.
시험용 정의
부분합(데이터 → 부분합)은 그룹화할 항목으로 먼저 정렬한 뒤 실행하며, 대화 상자에서 그룹화할 항목, 사용할 함수(합계·개수·평균·최대·최소·곱·숫자 개수·표준 편차·분산), 부분합 계산 항목을 고른다. 새로운 값으로 대치를 끄면 기존 부분합을 지우지 않고 중첩되며, '그룹 사이에서 페이지 나누기', '데이터 아래에 요약 표시'를 고를 수 있다. 결과 왼쪽의 윤곽 기호(1 2 3)로 총합계만·소계까지·전체를 펼쳐 보고, 모두 제거로 원래 표로 되돌린다. 피벗 테이블(삽입 → 피벗 테이블)은 많은 데이터를 필터·행·열·값 영역에 필드를 끌어 놓아 요약하는 표로, 새 워크시트나 기존 워크시트에 만들 수 있다. 값 영역의 계산 방식은 값 필드 설정에서 합계·개수·평균 등으로 바꾸고, 날짜·숫자는 그룹화할 수 있다. 원본 데이터가 바뀌어도 자동으로 반영되지 않으므로 새로 고침(Alt+F5)을 한다. 피벗 차트는 피벗 테이블과 연결된 차트로, 피벗 테이블을 지우면 일반 차트로 바뀐다.
비유로 이해하기
부분합은 반별로 줄 세운 출석부 사이사이에 '1반 소계' 줄을 끼워 넣는 것, 피벗 테이블은 출석부는 그대로 두고 칠판에 반·성별 요약표를 새로 그리는 것이다.
자세히
먼저 알아 둘 말
| 말 | 뜻 |
|---|---|
| 부분합 | 그룹마다 소계를 표 안에 넣는 기능 (SUBTOTAL 함수 사용) |
| 윤곽 기호 | 부분합 결과 왼쪽의 1·2·3 단추와 +·- 단추. 접고 펼치기 |
| 중첩 부분합 | 부분합 위에 다른 함수의 부분합을 하나 더 넣는 것 |
| 피벗 테이블 | 원본에서 필드를 골라 다른 곳에 만드는 요약표 |
| 필드 | 원본 표의 열(머리글) |
| 새로 고침 | 원본이 바뀐 것을 피벗 테이블에 반영 (Alt+F5) |
부분합 순서
- 그룹화할 열(부서)로 정렬
- 데이터 → 부분합
- 그룹화할 항목: 부서 / 사용할 함수: 합계 / 부분합 계산 항목: 급여
- 두 번째 부분합(평균)을 더하려면 다시 실행하고 새로운 값으로 대치를 끈다
- 없애려면 부분합 → 모두 제거
피벗 테이블 영역
| 영역 | 하는 일 | 예 |
|---|---|---|
| 필터 | 전체 표를 한 조건으로 거르기 | 연도 |
| 행 | 왼쪽 세로 항목 | 부서 |
| 열 | 위쪽 가로 항목 | 직급 |
| 값 | 계산할 숫자 (합계·개수·평균) | 급여 합계 |
피벗 테이블 특징
- 원본 데이터는 바뀌지 않는다
- 원본이 바뀌면 새로 고침해야 반영 (자동 아님)
- 값 영역의 기본 함수는 숫자면 합계, 글자면 개수
- 행·열 총합계 표시 여부, 빈 셀에 표시할 값, 레이아웃(압축·개요·테이블 형식)을 바꿀 수 있다
- 날짜 필드는 연·분기·월로 그룹화할 수 있다
- 피벗 테이블 안 값을 더블클릭하면 그 값을 이루는 원본 행이 새 시트에 나온다
- 피벗 차트를 지워도 피벗 테이블은 남는다. 피벗 테이블을 지우면 피벗 차트는 일반 차트가 된다
1급에서 더 나오는 것
| 항목 | 알아 둘 것 |
|---|---|
| 피벗 차트 | 피벗 차트를 만들면 피벗 테이블도 함께 생기고, 필터는 양쪽에 함께 적용된다. 피벗 차트를 지워도 피벗 테이블은 남고, 피벗 테이블을 지우면 피벗 차트는 일반 차트가 된다. 분산형·주식형·거품형은 쓸 수 없다 |
| 피벗 테이블 | 원본을 고치면 [새로 고침]을 해야 바뀐다. 값 영역 숫자는 직접 고칠 수 없다. 텍스트 필드는 기본으로 행 영역, 값 영역에 넣으면 개수. 값 필드가 둘 이상이면 Σ 값 항목이 생긴다. 조건부 서식·레이블 필터·값 필터를 쓸 수 있고, 오류 값·빈 셀 표시를 옵션에서 정한다. 새 시트에 만들면 필터는 A1, 행 레이블은 A3부터 |
| 그룹화 | 날짜는 일·월·분기·연, 숫자는 시작·끝·단위로, 직접 고른 항목도 묶는다 |
| 부분합과 윤곽 | 먼저 정렬하고 실행. 다른 함수로 중첩하려면 '새로운 값으로 대치'를 끈다. 부분합을 없애면 윤곽·페이지 나누기도 없어진다. 윤곽은 최대 8수준, [윤곽 지우기]는 기호만 없애고 데이터는 남긴다 |
시험에 이렇게 나와요
- 부분합 전 → 그룹화할 항목으로 정렬, 되돌리기 → 모두 제거
- 부분합을 하나 더 추가 → '새로운 값으로 대치' 해제
- 부분합 결과 → 기본은 그룹 아래에 요약 표시
- 피벗 테이블을 지워도 피벗 차트는 남고 일반 차트로 바뀐다
- 값 영역 항목을 두 번 누르면 → 그 데이터의 세부 내용이 새 시트에
- (1급) 피벗 차트와 피벗 테이블 관계, 새로 고침, 피벗에 조건부 서식 가능, 중첩 부분합은 '새로운 값으로 대치' 해제, 윤곽 지우기
확인 문제
Q1. 부서별 급여 합계를 부분합으로 구하기 전에 반드시 해야 할 일은?
- 자동 필터 적용
- 부서로 정렬
- 피벗 테이블 만들기
- 급여로 정렬
정답 보기
2번 부서로 정렬
부분합은 위에서부터 값이 바뀌는 지점마다 소계를 넣는다. 그룹화할 항목(부서)으로 먼저 정렬하지 않으면 같은 부서가 여러 번 나뉘어 소계가 여러 개 생긴다.
Q2. 이미 있는 부분합(합계)에 평균 부분합을 함께 표시하려면?
- 새로운 값으로 대치를 끈다
- 모두 제거를 누른다
- 새로운 값으로 대치를 켠다
- 데이터 아래에 요약 표시를 끈다
정답 보기
1번 새로운 값으로 대치를 끈다
대치를 끄면 기존 부분합이 남고 중첩된다.
Q3. 피벗 테이블에 대한 설명으로 틀린 것은?
- 값 필드의 계산 방식을 평균으로 바꿀 수 있다
- 필터·행·열·값 영역이 있다
- 새 워크시트에 만들 수 있다
- 원본 데이터를 바꾸면 자동으로 즉시 반영된다
정답 보기
4번 원본 데이터를 바꾸면 자동으로 즉시 반영된다
'원본 데이터를 바꾸면 자동으로 즉시 반영된다'가 틀리다. 원본을 바꾼 뒤 새로 고침을 해야 피벗 테이블에 반영된다.
Q4. 피벗 테이블을 삭제하면 연결된 피벗 차트는?
- 일반 차트로 바뀐다
- 함께 삭제된다
- 피벗 테이블이 다시 만들어진다
- 오류가 표시된다
정답 보기
1번 일반 차트로 바뀐다
데이터가 고정된 일반 차트가 된다.
Q5. 부분합을 실행하기 전에 먼저 해야 하는 일은?
- 셀 병합
- 조건부 서식 지정
- 그룹으로 묶을 항목을 기준으로 정렬
- 피벗 테이블 만들기
정답 보기
3번 그룹으로 묶을 항목을 기준으로 정렬
같은 항목끼리 모여 있어야 그룹별로 합계를 낸다.
Q6. 피벗 테이블 값 영역의 숫자를 두 번 누르면?
- 차트가 만들어진다
- 그 값을 만든 세부 데이터가 새 시트에 나온다
- 값이 지워진다
- 부분합이 실행된다
정답 보기
2번 그 값을 만든 세부 데이터가 새 시트에 나온다
어떤 행들이 합쳐진 값인지 새 시트로 보여 준다.
헷갈리기 쉬운 O·X
부분합은 정렬하지 않아도 같은 항목끼리 알아서 묶어 준다.
정답 보기
X 틀리다. 부분합은 붙어 있는 같은 항목끼리 묶기 때문에 기준 열로 먼저 정렬해야 한다.
피벗 테이블을 만들어도 원본 데이터는 바뀌지 않는다.
정답 보기
O 맞다. 피벗 테이블은 원본을 요약해 따로 보여 줄 뿐 원본 데이터는 그대로다.
부분합의 윤곽 기호 1을 누르면 모든 데이터가 펼쳐진다.
정답 보기
X 1은 총합계만. 숫자가 클수록 자세히.