[개미의 걸음 2과목 스프레드시트 일반] 분석작업① 피벗 테이블

외부데이터 가져오기

데이터베이스 파일, 텍스트 파일 등을 워크 시트로 가져오거나 데이터베이스 파일을 쿼리 형태로 변경해 워크시트에서 사용하는 기능

• SQL, dBASE, Access 등의 데이터베이스 파일과 텍스트 파일, EXCEL 파일, 웹, XML 등의 외부 데이터를 엑셀에 가져올 수 있음
• Microsoft Query, VBA, 웹 쿼리, 쿼리 마법사 등을 이용해 외부 데이터를 가져오거나 쿼리를 작성 가능
• [데이터 → 외부데이터 가져오기]그룹에서 [Access], [웹], [기타 원본] 등 가져올 파일 형식 선택
• 외부 데이터 가져오기를 사용해 가져온 데이터는 원본 데이터가 변경될 경우, 가져온 데이터에도 반영하도록 설정 가능
• 일반적으로 엑셀 실기에서는 Microsoft Query에서 데이터를 가져옴

# Microsoft Query

외부 데이터베이스에서 가져올 데이터의 추출 조건을 쿼리로 만들어 가져오거나 만든 쿼리를 다음에 사용할 수 있도록 저장

• 데이터베이스 파일, OLAP 큐브 파일을 가져오거나 쿼리 파일로 만들 수 있음
• 새 쿼리는 하나의 통합 문서에 대해서만 만들 수 있음

# Web Query

웹 페이지에서 테이블이나 텍스트와 같은 데이터를 검색하여 워크시트에서 사용할 수 있도록 가져오거나 웹 쿼리를 작성할 때 사용

• 웹 페이지에서 텍스트, 서식이 설정된 텍스트 영역, 테이블의 텍스트 등은 가져올 수 있지만 그림, 스크립트의 내용은 가져올 수 없음
• 웹 페이지의 데이터를 검색하여 가져오려면 인터넷이 연결되어 있고, 웹 브라우저가 설치되어 있어야 함

 

 

피벗테이블

많은 양의 데이터를 한눈에 쉽게 파악할 수 있도록 요약*분석하는 도구

• 엑셀 목록, 데이터베이스, 외부 데이터, 다른 피벗 테이블 등의 데이터를 사용 가능
• 각 필드에 다양한 조건 지정 가능 / 그룹별 데이터 집계 가능
• 원본 데이터 변경시 [피벗 테이블 도구 → 옵션 → 데이터 → 새로고침]을 이용해 피벗테이블 데이터 변경가능
• 피벗 테이블 작성시 피벗 테이블의 작성 위치를 지정하지 않을 경우, 새 워크시트에 작성됨
• 작성한 피벗 테이블의 필드 단추를 다른 열이나 행으로 드래그하여 변경 가능
• 필드 단추의 위치를 변경하면 데이터 표시 형식이 변경됨
• 원본 데이터를 기초로 피벗 테이블에 새로운 필드를 추가 가능
• 행 레이블에 지정한 필드는 하나의 열에 모두 표시되고, 열 레이블에 지정한 필드는 하나의 행에 모두 표시됨
• 행 레이블이나 열 레이블에 표시된 데이터의 일부는 수정할 수 있으나 값 영역에 표시된 데이터는 수정할 수 없음
• 값 영역의 두 개 이상의 필드를 지정하면 열 레이블 또는 행 레이블 값에 값(∑)필드가 생성됨
  값 필드가 열 레이블과 행 레이블 중 어디에 삽입하느냐에 따라 값 영역에 추가된 필드의 표시 방향이 결정됨
• [삽입 → 표 → 피벗테이블]

피벗 테이블의 그룹화

특정 필드를 일정한 단위로 묶어 표현할 때 사용

• 문자, 숫자, 날짜, 시간 등 모든 필드에서 사용 가능
• 숫자나 날짜 필드일 경우, '그룹화' 대화상자에서 시작, 끝, 단위를 지정
• 문자 필드에 그룹 만들기를 실행하면 그룹 이름 자동 부여 [그룹1, 그룹2, ...]
• 그룹으로 지정하려면 그룹으로 묶고자 하는 데이터를 기준으로 먼저 정렬해야 됨
• 그룹으로 지정할 필드의 바로가기 메뉴에서 [그룹]을 선택하면 그룹화
  그룹으로 설정된 필드의 바로가기 메뉴에서 [그룹해제]를 선택하면 그룹 해제

 

피벗 차트 보고서

피벗 테이블의 데이터를 이용해 작성한 차트

• 피벗 테이블 항목이나 필드에 변화를 주면 피벗 차트도 변경됨
  피벗 차트에서 변화를 주면 피벗 테이블도 변경
• 피벗 차트를 작성하면 자동으로 피벗 테이블도 작성됨
  피벗 테이블을 만들지 않으면 피벗 차트 작성 불가!
• 피벗 테이블 및 피벗 차트를 만든 후 피벗 테이블을 삭제하면 피벗 차트는 일반 차트로 변경됨
• 피벗 차트 보고서는 피벗 테이블 보고서를 작성할 때 같이 작성하거나,
  이미 작성된 피벗 테이블 보고서를 이용해 작성 가능
• 표준 차트의 항목, 계열, 데이터가 피벗 차트 보고서에서는 축필드(항목), 범례 필드(계열), 값 필드에 해당
• 분산형, 거품형, 주식형 차트는 피벗 차트 보고서 작성 불가

    보고서 레이아웃

압축 형식	개요형식	테이블 형식
행레이블, 열레이블로 나타남	필드명으로 나타남	필드명으로 나타남
	그룹명과 데이터가 다른 줄	그룹명과 데이터가 같은 줄

 

 

들어가기 전...

빠른 실행도구모음에 자동서식 넣기

• [파일 → 옵션 → 빠른 실행 도구 모음 → 모든명령]선택 후 자동서식 추가

 

 

예제 문제

 

 

1. <직원복지관리.accdb>의 <의료비데이터>테이블의 데이터 가져오기

 

• [데이터 → 기타원본 → Microsoft Query → MS Access Database] 선택
• '쿼리를 만들거나 편집할 때 쿼리 마법사 사용' 체크 되어있는지 반드시 확인!
  [일반적으로는 체크되어 있음]
• 문제에 나와있는 파일을 가져오는데 파일 위치는 시험지 맨 앞장에 나와있음!
  [C:\OA\파일명]

 

2. 쿼리 마법사 - 열선택 : <의료비데이터> 테이블의 데이터 중 필요한 것만 순서대로 가져오기

• 문제에서 주어진 열의 순서대로 더블클릭하면 오른쪽으로 이동

 

3. 쿼리 마법사 - 데이터 필터 : 가져올 열들을 조건 설정하여 데이터 가져오기

• 문제에서 주어진 조건이 없으므로 그냥 넘어감

 

4. 쿼리 마법사 - 정렬 순서 : 정렬 순서 설정하기

• 문제에서 주어진 조건이 없으므로 그냥 넘어감

5. 쿼리 마법사 - 마침

• 일반적으로는 'Microsoft Excel로 데이터 되돌리기' 선택
• 열끼리 'or조건'인 경우 'Microsoft Query에서 데이터 보기 또는 쿼리 편집'선택
  ex> 만약 문제에 '미지급금액이 0보다 크거나 지급금액이 0이하인 행'으로 조건이 주어진 경우 
  

  

 

6. 데이터가 들어갈 위치 정하기

• 문제에서 [B4]셀부터 데이터를 표시하라고 했으므로 B4셀 선택
• 처음 외부데이터를 가져올 때부터 B4셀을 선택한 후 작업하면 실수를 줄일 수 있음

 

7. 피벗 테이블 보고서 만들기

• 문제에서 주어진 그림을 참조하여 행*열 레이블에 들어갈 데이터 설정

 

8. 보고서 레이아웃 설정

• [디자인 → 레이아웃 → 보고서 레이아웃]에서 [테이블 형식으로 표시] 선택

 

9. 그룹지정

• 진료일 데이터 중 하나의 셀을 선택하여 마우스 오른쪽 클릭하여 그룹지정
• 그룹화에서 '연' 단위 선택 후 확인
• 부,모 선택 후 그룹지정
• 딸,아들 선택 후 그룹지정

+ 문자 그룹지정 VS 날짜/숫자 그룹지정

문자	날짜/숫자
블록 설정 후 그룹화	블록 설정 없이 그룹화 자료 첫번째 셀에 마우스를 놔두고 그룹 설정

 

10. 피벗 테이블 스타일 지정

• [피벗테이블도구 → 디자인 → 피벗테이블 스타일]에서 ''피벗 스타일 보통1' 선택
• 보고서 서식을 설정하는 문제의 경우, 앞서 지정한 빠른 실행도구모음의 자동서식을 사용하여 지정

 

11. 표시 형식

• 데이터 영역 범위 지정 후 마우스 오른쪽 클릭하여 셀 서식[단축키 : Ctrl + 1]선택
• 표시형식 탭에서 회계 범주에서 기호없음 선택

※ 데이터 영역의 수치를 '쉼표 스타일'로 표시하시오
    → [홈 → 표시형식 → 쉼표스타일] 선택