[개미의 걸음 2과목 스프레드시트 일반] 기본작업① 외부데이터 가져오기

들어가기 전..

스프레드시트는 실기에 나오는 개념 위주로 공부하면 충분히 합격 가능할 것 같습니다.

스프레드시트 실기 문제 예제는 대한상공회의소 홈페이지에 들어가시면 예제 문제 1개가 올라와 있습니다.

아래 링크로 들어가셔서 다운 받으시면 될 것 같습니다.

http://license.korcham.net/kor/customer/file/materialview.jsp?num=139861&flag=0103&&field=title&field_value=%C4%C4%C7%BB%C5%CD&pg=1

 

 

외부데이터 가져오기

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

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

# Microsoft Query

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

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

# Web Query

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

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

 

 

예제 문제

출처 : 대한상공회의소 홈페이지

 

 

 

1. 외부데이터 가져오기 기능을 사용해 <직원복지관리.accdb>의 데이터 가져오기

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

 

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

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

 

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

• 문제에서 주어진 대로 조건 설정!
• 서로 다른 열끼리 or 조건을 지정할 경우에도 일단, 문제에 주어진 대로 조건 설정
  [뒤에서 서로 다른 열끼리 or조건 설정]  
• AND(이고)조건인지 OR(이거나)조건인지 확인하기!
• not (A OR B) = (not A) AND (not B) 
• 쿼리 마법사는 불완전하므로 다른 열 클릭 후 꼭 다시 확인해보기!!

=	같다
<>	같지 않다.(제외, 아니다)
>	크다(초과)
>=	크거나 같다.(이상, 이후)
<	작다.(미만)
<=	작거나 같다.(이하, 이전)
시작 값
제외할 시작 값
끝나는 값
제외할 끝나는 값
포함하는 값
포함하지 않는 값
like	일반적으로 와일드카드(_, % 등)와 함께 사용  % : 몇 개의 문자와 와도 상관 없음  _ : 단 하나의 문자만 올 수 있음 ※ 특정 자리에 값을 갖는 데이터를 찾는데 유용     ex> '2번째 글자가 a인 것'
not like	like 조건을 제외한 데이터들을 찾음
Null	데이터가 없는 것들만 가져옴 ['0'은 데이터가 있는 것을 의미]
Not Null	Null 값을 제외한 것들만 가져옴

※ 성명이 '박'으로 시작

    → 시작값 : 박

※ 2000년 3월 자료

    → >= 2000-03-01
        그리고
        <= 2000-03-31

 

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

 

5. 쿼리 마법사 - 마침

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

  

 

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

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

 

7. 가져온 데이터의 표를 '범위로 변환'을 실행해 정상 범위로 변환

• 마우스 오른쪽을 클릭하여 [표 → 범위로 변환] 클릭
• 또는 [디자인 → 도구 → 범위로 변환] 클릭

 

 

 

※ 잘못했을 경우 : 해당 셀들을 블록 설정 후, [홈-편집-지우기]에서 모두 지우기 선택