Transcript 첨부화일 : 0외부데이터피벗테이블 (1768362 Bytes)

컴퓨터활용능력1급 엑셀
외부데이터 문제
1. 외부데이터 사용

컴활 시험에서 외부데이터 가져
오기(15점)와 피벗 테이블(10점)
기능이 외부 데이터를 가져와 작
업하는 문제이다.
 배점이 높고 부분점수가 없기 때
문에 합격을 위해서는 반드시 알
아야 하는 부분이다.

두 기능 모두 [데이터]-[기타원
본]-[Microsoft Query] 메뉴에서
해결 할 수 있다.
 메뉴를 선택하면 어떤 형식의 파
일을 가져 올 것인지 선택한다. 시
험에서는 Access 파일을 사용하므
로 ‘MS Access Database’ 를 선택
한다.
2. 파일과 필드
 폴더(디렉터리)를 이동하여 가
져 올 파일의 위치를 찾아 파일
을 선택한다.
 Tip) 시험에서는 일반적으로 C
드라이브의 OA 폴더에 위치되
어 있다.
 가져오고자 하는 필드가 입력되
어 있는 테이블을 선택하여 필
드를 선택한다.
 단! 필드를 선택할 때 반드시 순
서대로 배치가 되어야 한다. 순
서가 다르면 감점됨을 잊지 말
자!!!
3. 필드 선택
하나의 필드에서의 AND, OR 선택
 문제에 제시되어 있는 조건을 설정
하는 부분이다.
 여러 개의 조건이 나왔을 경우 각각
의 조건을 입력하면 된다. 단! 하나
의 필드에서는 AND와 OR 조건을
작성할 수 있다. 하지만 서로 다른
필드에서의 조건은 AND 조건만 설
정할 수 있다.
 만일 문제가 서로 다른 필드의 OR
조건이라면 AND 조건으로 입력하
고 마지막에 ‘쿼리 편집’에서 조건
을 수정하는 한다.
다른 필드에서 조건을 입력하면 AND
4. 조건 작성
 조건은 비교연산자(=,<>,>,>=,<,<=)
를 이용하여 조건을 입력하면 된다.
 하지만 조건에 ‘~으로 시작하는’, ‘~으
로 끝나는’, ‘~을 포함하는’ 형식의 조
건이 나왔을 경우 비교연산자를 사용
할 수 없다. 따라서 시작 값, 끝나는
값, 포함하는 값 조건으로 사용할 문
자를 입력하는 것으로 설정할 수 있
다. (이 때, * 기호 절대로 입력하지 말
것)
 또한 외부데이터에서는 ‘*’ 기호 대신
에 ‘%’ 기호를 사용하여 조건을 작성
할 수 있는데, 이 때는 like 조건으로
입력하여야 한다.
조건
LIKE
시작 값
문자%
끝나는 값
%문자
포함하는 값
%문자%
5. 정렬
 정렬은 문제에 제시되어 있는 데
로 순서대로 설정하면 된다.
 ‘Excel로 데이터 되돌리기’를 선택
하여 데이터를 가져오면 된다. 만
약 조건 작성에서 서로 다른 필드
간에 OR 조건이 나왔다면 ‘쿼리
편집’을 선택하고 마침을 누른다.
서로 다른 필드에 OR 조건일 경우
6. 쿼리 편집 (OR 조건)
 ‘쿼리 편집’ 화면에서 필드 간의 조건을 OR로 변경할 수 있다.
 이 때 두 필드의 조건을 값 행에 조건을 입력하면 AND 조건이다. 하지만
두 필드 중 하나의 필드 조건을 또는 행에 입력하면 OR 조건으로 설정된
다.
 아래 데이터의 미리보기를 확인한 후 ‘데이터 반환’ 버튼으로 엑셀로 가
져올 수 있다.
데이터 반환 버튼
다른 행에 조건을 입력
하면 OR 조건 설정
7. 기능 선택
 마지막으로 ‘외부데이터 가져오기’
문제일 경우 ‘표’를 선택하고, ‘피벗
테이블’ 문제라면 ‘피벗 테이블 보고
서’를 선택하여 가져온다.
 ‘외부데이터 가져오기’ 문제의 경우
데이터가 표시될 시작 위치를 클릭하
고, ‘피벗 테이블’ 문제의 경우 보고
서가 시작할 위치를 클릭하면 된다.
8. 외부데이터 가져오기
 만일 외부데이터의 필드나 조건
을 잘못 설정하여 가져왔을 경
우 표를 선택하고 오른쪽 마우
스를 클릭하여 [표]-[쿼리 편집]
을 클릭하면 ‘쿼리 편집’ 화면이
나타나 수정할 수 있다.
주의) 가져온 데이터의 표를 ‘범위로 변환’
을 실행하여 정상 범위로 변환하시오.
 문제에 위 내용의 표 데이터를
‘범위로 변환’하라는 지시가 있
다면 해당 메뉴를 클릭하여 범
위 데이터로 변환하면 된다.
표 데이터를 범위
데이터로 변환
9. 피벗 테이블
 피벗 테이블에서는 보고서 필드, 행 레이블, 열 레이블, 값 4개의 영역에
데이터를 지정하므로 작성할 수 있다.
보고서
행 레이블
값
 값 영역에 여러 개일
경우 ‘Σ값’이 생긴다.
‘Σ값’의 위치에 따라
화면 결과가 달라진다.
(왼쪽그림은 Σ값의 위
치가 열 레이블에 있
는 경우이다.)
 Σ값의 위치에 따라 값 필드의 방향
이 행이나 열로 표시할 수 있다. (왼
쪽그림은 Σ값이 행 레이블에 있는
경우이다.)
 값 범위에서 필드를 클릭하면 ‘값
필드 설정’을 하여 함수나 표시 형
식을 변경할 수 있다.
클릭 → [값 필드 설정]
함수를 변경하여 요약 값을 변경
값 표시 형식을 변경할 수 있다.
10. 추가 기능
 [피벗테이블 도구]-[디자인]-[보고서 레이아웃]
 시험에서 언급되어 나올 수도 있지만, 그림을
보고 설정해야 할 수 있으니 그림을 보고 차이
점을 꼭 기억하자.
1. 압축 형식
구분과 비고가 같은 열에 있음
2. 개요 형식
구분과 비고가 다른열에 있고,
행의 높이가 다름
3. 테이블 형식
구분과 비고가 다른 열에 있고,
행의 높이가 같음
 행 레이블에서 오른쪽 마우스 버튼을 클릭하였을 때
할 수 있는 작업이 있다.
① “구분” 부분합 : 행 레이블의 요약 값을 표시 및 해제
② 그룹/그룹 해제 : 그룹을 지정하고 해제할 수 있다.
Tip. 숫자/날짜 데이터인 경우 행 레이블 한 셀만 클릭하여 그룹하면
그룹자동화로 간격을 지정하여 그룹을 만들 수 있다. 하지만 그
룹하고자 하는 데이터(문자/숫자/날짜 모두 가능)를 범위 지정하
여 그룹을 클릭하면 만들 수 있다. 범위를 지정하여 그룹을 만들
면 왼쪽에 필드2 가 생성이 된다. 만일 그림에서 필드2가 있다면
범위지정하여 그룹을 지정한 결과이다. (그림 참조)
1
2
3
③ 이동 : 행 레이블의 데이터의 순서를 바꿀 수 있다.
 [피벗 테이블 도구] – [옵션] – [수식]
 기존의 필드를 사용해 새로운 계산 필드를 만들 수 있다.
①
이름 : 새로운 필드의 이름
②
수식 : 계산 수식 (필드이름으로 작성이 되고, 함수 사용 가능)
③
필드 : 사용할 수 있는 필드를 표시(필드삽입을 클릭하면 선택
된 필드가 수식에 추가)
예)
전산구조, SW공학, 데이터통신의 평균을 계산하여 ‘평균’이라
는 필드이름으로 만듬
 사칙연산을 사용한 단순한 계산도 가능하고, 함수를 이용해 함수 결과
를 표시할 수도 있다.
 수식을 작성한 후 ‘추가’ 버튼을 클릭하여 계산 필드를 삽입 할 수 있다.
[피벗 테이블 도구]-[옵션]-왼쪽의 [옵션]
피벗 테이블에 적용할 수 있는 추가 옵션이다.
[레이아웃 및 서식] 탭

레이블이 있는 셀 병합 및 가운데 맞춤 : 레이블이 한 셀이
아니라 여러 셀로 표시되는 경우 자동으로 병합을 하고 가
운데 정렬 해주는 옵션임
(과거버전에서는 ‘레이블병합’ 이라는 옵션이었음)

빈 셀 표시 : 피벗 테이블은 데이터 부분을 수정할 수 없다.
빈 셀에 데이터를 입력하려면 옵션에 빈 셀에 표시할 데이
터를 입력하여야 한다.
[요약 및 필터] 탭
 행 총합계 표시 : 행 기준으로 총합계를 표시
 열 총합계 표시 : 열 기준으로 총합계를 표시
 시험에서 총합계 표시 여부에 따라 옵션 체크