스프레드시트·1급 전용·15장
데이터 분석 — 부분합·피벗 테이블·가상 분석
부분합과 데이터 통합, 피벗 테이블과 피벗 차트, 목표값 찾기·시나리오·데이터 표 등 가상 분석 도구를 정리합니다.
노란 밑줄n회 기출에 나온 지점(숫자는 등장 문항 수)✎ 는 덧붙인 필기
출제 비중 — 1급 전용 단원. 피벗 테이블52회·부분합28회·시나리오30회·데이터 표32회·목표값 찾기21회 가 고르게 나온다.
1. 부분합
- 부분합28회: 그룹별로 합계·개수·평균·최대·최소·곱·표준편차·분산을 계산한다.
- 반드시 그룹화할 항목을 기준으로 먼저 정렬해야 한다. 정렬하지 않으면 같은 그룹이 여러 번 나뉜다.
- 옵션
- 새로운 값으로 대치를 해제하면 부분합을 중첩할 수 있다(합계 위에 평균 등).
- 그룹 사이에서 페이지 나누기, 데이터 아래에 요약 표시.
- 왼쪽의 윤곽 기호(1·2·3) 로 요약 수준을 바꿀 수 있고, [모두 제거] 로 원래 목록으로 되돌린다.
- 부분합 결과는 SUBTOTAL 함수 로 작성되어, 필터로 숨긴 값은 제외된다.
2. 데이터 통합
- 데이터 통합6회: 여러 워크시트·통합 문서에 흩어진 데이터를 한 표로 요약.
- 방법 두 가지
- 위치 기준: 표의 모양과 순서가 같을 때.
- 항목(레이블) 기준: 순서가 달라도 첫 행·왼쪽 열의 이름으로 맞춘다.
- 원본 데이터에 연결을 체크하면 원본이 바뀔 때 결과가 자동으로 갱신되고 윤곽 기호가 생긴다. (다른 통합 문서를 연결하면 원본 시트를 열어야 갱신된다.)
3. 피벗 테이블
- 피벗 테이블52회: 많은 데이터를 행·열·값·필터 영역으로 배치해 요약·분석하는 대화형 표.
- 구성: 보고서 필터 / 행 레이블 / 열 레이블 / 값.
- 값 필드의 계산 방식은 합계·개수·평균 등으로 바꿀 수 있고, 값 표시 형식으로 총합계 비율·열 합계 비율·순위 등을 낼 수 있다.
- 원본 데이터가 바뀌어도 자동으로 갱신되지 않는다 — [새로 고침] 을 해야 한다.
- 그룹화: 숫자는 시작·끝·단위, 날짜는 연·분기·월·일로 묶을 수 있다.
- 슬라이서와 시간 표시 막대로 필터를 시각적으로 조작한다.
- 피벗 테이블을 삭제해도 피벗 차트는 일반 차트로 남는다(반대로 피벗 차트를 지워도 표는 남는다).
- 피벗 차트: 분산형·거품형·주식형 차트로는 만들 수 없다.
4. 가상 분석(What-if)
| 도구 | 쓰임 |
|---|---|
| 목표값 찾기21회 | 결과값을 먼저 정해 놓고, 그 결과를 만드는 입력값 하나를 거꾸로 찾는다. '수식 셀·찾는 값·값을 바꿀 셀' 3가지를 지정하며, 찾는 값에는 반드시 상수를 넣는다 |
| 시나리오30회 | 변경 셀 값의 여러 조합을 저장해 두고 결과를 비교. 변경 셀은 최대 32개, 요약 보고서·피벗 테이블 보고서로 만들 수 있다. 시나리오 요약은 별도의 새 시트에 만들어지고 자동으로 갱신되지 않는다 |
| 데이터 표32회 | 수식에 한두 개 변수 값을 대입했을 때 결과가 어떻게 달라지는지 표로 본다. 결과는 배열 수식이라 일부만 수정·삭제할 수 없다 |
| 해 찾기 | 제약 조건을 만족하면서 목표 셀을 최대·최소·특정 값으로 만드는 변수 값을 찾는다(추가 기능) |
- 데이터 표의 행 입력 셀 / 열 입력 셀: 값이 행 방향으로 나열되면 행 입력 셀, 열 방향이면 열 입력 셀에 지정한다.
5. 분석 도구와 예측
- 분석 도구 팩(추가 기능): 기술 통계법, 히스토그램, 상관 분석, 회귀 분석 등.
- 예측 시트: 시계열 데이터로 미래 값을 예측한 표와 차트를 만든다.
마무리 체크
- 부분합 전 정렬 필수, 중첩하려면 '새로운 값으로 대치' 해제
- 통합은 위치 기준 / 항목 기준, 연결하면 자동 갱신
- 피벗 테이블은 새로 고침해야 갱신, 피벗 차트에 분산형 불가
- 목표값 찾기는 입력값 1개, 찾는 값에는 상수
- 시나리오 변경 셀 최대 32개, 요약은 자동 갱신 안 됨
- 데이터 표 결과는 배열이라 일부 삭제 불가