12차시
데이터 관리와 분석 도구
Part 2. 스프레드시트 일반 · 출제 빈도 ★★★
이 차시를 공부하는 이유
1급 2과목에서 피벗 테이블은 거의 매회 나오고, 시나리오·목표값 찾기·데이터 표·통합 중 하나가 꼭 출제됩니다. 네 가지 '가상 분석' 도구를 목적으로 구분하는 것이 핵심입니다.
2급에서 먼저 보면 좋은 내용
정렬·필터·부분합·피벗 기본은 2급과 같고, 1급은 고급 필터 수식 조건과 가상 분석 도구 비교가 핵심입니다.
학습 목표
정렬 순서 규칙과 자동 필터·고급 필터의 조건 지정 방법을 설명할 수 있다.
외부 데이터 가져오기와 부분합 작성 규칙을 이해한다.
피벗 테이블·피벗 차트의 구성과 동작 규칙을 설명할 수 있다.
시나리오, 목표값 찾기, 데이터 표, 데이터 통합을 목적에 맞게 선택할 수 있다.
핵심 개념
1. 정렬
- 정렬 기준은 최대 64개까지 지정할 수 있고, 기본은 위에서 아래(열 기준) 정렬입니다. [옵션]에서 왼쪽에서 오른쪽(행 기준) 정렬과 대/소문자 구분을 선택할 수 있습니다.
- 오름차순: 숫자 → 문자 → 논리값(FALSE→TRUE) → 오류값 → 빈 셀. 내림차순은 빈 셀을 뺀 순서가 반대이며 빈 셀은 항상 마지막입니다.
- 대/소문자를 구분하면 오름차순에서 소문자가 먼저 옵니다.
- 정렬 기준으로 셀 값뿐 아니라 셀 색, 글꼴 색, 조건부 서식 아이콘을 쓸 수 있고, 사용자 지정 목록 순서로도 정렬합니다.
- 숨겨진 행·열은 정렬에 포함되지 않습니다. 표 스타일이 적용된 범위를 왼쪽→오른쪽으로 정렬하려면 먼저 범위로 변환해야 합니다.
시험 함정 포인트
'내림차순 정렬 시 빈 셀이 맨 위에 온다'는 오답입니다. 빈 셀은 오름차순이든 내림차순이든 맨 아래입니다.
'내림차순 정렬 시 빈 셀이 맨 위에 온다'는 오답입니다. 빈 셀은 오름차순이든 내림차순이든 맨 아래입니다.
2. 자동 필터와 고급 필터
자동 필터
간단한 조건으로 추출합니다. 여러 열의 조건은 AND로 결합됩니다. 데이터 형식에 따라 숫자·텍스트·날짜 필터가 표시되고, '상위 10'은 숫자 필드에서만 사용 가능합니다. 결과는 원래 위치에만 표시됩니다.
고급 필터
조건 범위를 따로 만들어 복잡한 조건으로 추출합니다. 결과를 다른 위치에 복사할 수 있고, 동일한 레코드는 하나만 표시하는 옵션이 있습니다.
| 고급 필터 조건 | 작성 방법 |
|---|---|
| AND 조건 | 조건을 같은 행에 입력 |
| OR 조건 | 조건을 다른 행에 입력 |
| 값이 정확히 같은 항목 | ="=영업"처럼 입력 (만능 문자 *, ? 사용 가능) |
| 수식(함수) 조건 | 조건 범위의 필드명을 원본과 다르게 하거나 비움, 셀에는 TRUE/FALSE가 표시됨. 예: 필드명 '평균초과', 조건 =C2>AVERAGE($C$2:$C$20) |
3. 외부 데이터와 부분합
부분합 작성 규칙
① 첫 행에 열 이름 ② 그룹화할 필드로 반드시 먼저 정렬 ③ [데이터]→[개요]→[부분합]. 같은 필드에 여러 함수를 중첩하려면 두 번째부터 '새로운 값으로 대치'를 해제합니다.
부분합 옵션
사용 함수: 합계, 개수, 평균, 최대, 최소, 곱, 숫자 개수, 표준 편차, 분산 등 / 그룹 사이 페이지 나누기 / '데이터 아래에 요약 표시' 해제 시 그룹 위에 표시 / [모두 제거] 시 개요·페이지 나누기도 제거
- 외부 데이터: Access·SQL 데이터베이스, 텍스트(txt, prn, csv), 웹(htm), XML, JSON, 다른 엑셀 파일, 쿼리(dqy), OLAP 큐브(oqy) 등을 가져옵니다. [데이터]→[데이터 가져오기 및 변환]
- 가져온 데이터는 [모두 새로 고침]으로 원본 변경 사항을 반영합니다. '연결 속성'에서 파일을 열 때 새로 고침, 일정 간격 새로 고침을 지정할 수 있습니다.
- Microsoft Query는 조건을 지정해 필요한 데이터만 가져오고, 웹 쿼리는 웹 페이지의 표를 가져옵니다.
4. 피벗 테이블과 피벗 차트
많은 데이터를 원하는 기준으로 요약·분석하는 대화형 표입니다. 엑셀 목록, 외부 데이터, 다른 피벗 테이블 등을 원본으로 쓸 수 있습니다.
| 항목 | 내용 |
|---|---|
| 구성 영역 | 필터, 행, 열, 값 (값 영역에 필드가 2개 이상이면 'Σ 값' 필드 자동 생성) |
| 원본 변경 | 원본 데이터가 바뀌어도 자동 반영되지 않으므로 [새로 고침]을 실행 |
| 값 요약 | 합계·개수·평균·최대·최소 등으로 변경, '값 표시 형식'으로 총합계 비율 등 표시 |
| 그룹화 | 숫자·날짜는 시작·끝·단위 지정, 문자는 셀을 선택해 그룹 지정 |
| 레이아웃 | 압축 형식, 개요 형식, 테이블 형식 / 총합계 표시 여부 / 빈 셀 표시 값 |
| 기본 위치 | 새 워크시트(기존 워크시트도 선택 가능) |
- 피벗 차트와 피벗 테이블은 서로 연동됩니다. 피벗 테이블을 삭제하면 피벗 차트는 일반 차트로 바뀌고, 피벗 차트를 삭제해도 피벗 테이블은 그대로입니다.
- 피벗 차트의 축 필드 = 일반 차트의 항목, 범례 필드 = 계열입니다. 분산형, 거품형, 주식형은 피벗 차트로 만들 수 없습니다.
시험 함정 포인트
'원본 데이터를 수정하면 피벗 테이블이 자동으로 갱신된다'는 오답입니다. 반드시 [새로 고침]이 필요합니다.
'원본 데이터를 수정하면 피벗 테이블이 자동으로 갱신된다'는 오답입니다. 반드시 [새로 고침]이 필요합니다.
5. 가상 분석: 시나리오·목표값 찾기·데이터 표·통합
| 도구 | 목적 | 핵심 규칙 |
|---|---|---|
| 시나리오 | 여러 변수 값의 조합(낙관·비관 등)에 따른 결과 비교 | 변경 셀 최대 32개, 요약 보고서는 현재 시트 왼쪽에 새 시트로 생성, 결과 셀 지정 필수, 원본을 바꿔도 이미 만든 보고서는 갱신 안 됨, 다른 시트·통합 문서의 시나리오 병합 가능 |
| 목표값 찾기 | 원하는 결과를 얻기 위한 입력값 하나를 역산 | 수식 셀(반드시 수식), 찾는 값(직접 입력), 값을 바꿀 셀(하나만) |
| 데이터 표 | 입력값 변화에 따른 결과를 표 형태로 한 번에 계산 | 결과는 수식으로 작성, 행 입력 셀/열 입력 셀 지정, 결과는 {=TABLE()} 배열이라 일부만 수정 불가, 원본 변경 시 자동 갱신 |
| 통합 | 여러 시트·통합 문서의 비슷한 데이터를 하나로 요약 | 함수(합계·평균 등) 선택, 첫 행·왼쪽 열 레이블 사용, '원본 데이터에 연결'은 통합 결과가 다른 시트에 있을 때만 가능 |
시험 함정 포인트
'목표값 찾기는 여러 셀의 값을 동시에 바꿀 수 있다'는 오답입니다. 여러 변수를 바꿔 비교하려면 시나리오(또는 해 찾기)를 사용합니다.
'목표값 찾기는 여러 셀의 값을 동시에 바꿀 수 있다'는 오답입니다. 여러 변수를 바꿔 비교하려면 시나리오(또는 해 찾기)를 사용합니다.
확인 문제
1 / 5
문제 1
엑셀의 정렬 기능에 대한 설명으로 옳지 않은 것은?
💡 정답 ③ 해설: 빈 셀은 오름차순·내림차순과 관계없이 항상 맨 마지막에 정렬됩니다.
문제 2
부분합을 작성하기 전에 반드시 수행해야 하는 작업은?
💡 정답 ① 해설: 부분합은 같은 값끼리 모여 있어야 그룹별로 계산되므로, 그룹화할 필드를 기준으로 먼저 정렬해야 합니다.
문제 3
피벗 테이블과 피벗 차트에 대한 설명으로 옳은 것은?
💡 정답 ③ 해설: 원본 변경은 [새로 고침]으로 반영하고, 피벗 차트를 지워도 피벗 테이블은 남습니다. 분산형·거품형·주식형은 피벗 차트로 만들 수 없습니다.
문제 4
대출 원금을 바꾸어 가며 월 상환액이 정확히 500,000원이 되는 원금을 찾으려 할 때 가장 적합한 도구는?
💡 정답 ② 해설: 결과값(월 상환액 500,000원)을 알고 있고 그 결과를 만드는 입력값 하나(원금)를 찾는 것은 목표값 찾기의 용도입니다.
문제 5
고급 필터에서 '부서가 영업이거나 총무인 레코드'를 추출하기 위한 조건 범위 작성 방법으로 옳은 것은?
💡 정답 ② 해설: OR 조건은 서로 다른 행에 입력합니다. ④처럼 같은 필드명을 두 번 쓰고 같은 행에 입력하면 '영업이면서 총무'라는 AND 조건이 되어 결과가 없습니다.
12차시 문제 더 풀기
확인문제와 실전 모의고사에서 이 차시 범위 문제만 모아 해설과 함께 볼 수 있습니다.