실전모의고사 1회

MOS Excel 2007 Expert

01. F 열과 G 열의 수식이 숨겨지도록 서식을 적용하시오. 사용자가 잠긴 셀을 선택만 할 수 있도록 암호 3452를 사용하여 시트를 보호하시오. (실전1-01.xlsx) [Hint: 셀 서식, 시트 보호]

1. F열과 G열 머리글을 드래그하여 선택한다.

2. [홈] 탭-[셀] 그룹-[서식]-[셀 서식] 명령을 클릭한다.

3. [셀 서식] 대화 상자의 [보호] 탭에서 ‘숨김’ 확인란을 선택하고 [확인] 단추를 클릭한다.

4. [홈] 탭-[셀] 그룹-[서식]-[시트 보호] 명령을 클릭한다.

5. [시트 보호] 대화 상자의 [시트 보호 해제 암호]에 암호 3452를 입력한다.

6. [워크시트에서 허용할 내용]에서 ‘잠긴 셀 선택’과 ‘잠기기 않은 셀 선택’의 확인란이 모두 선택되어 있는지 확인한다.

7. [암호 확인] 대화 상자가 나타나면 다시 한 번 3452를 입력하고 [확인] 단추를 클릭한다.

02. [판매현황차트] 워크시트의 차트에 [판매현황] 워크시트의 D4:D8 셀 범위에 있는 값을 사용하는 단가 이름의 데이터 계열을 추가하시오. 그리고 단가 데이터 계열을 보조 축으로 돌리고 표식이 있는 꺾은선형 차트 종류로 변경하시오. (실전1-02.xlsx)

[Hint: 차트 데이터 선택, 데이터 계열 서식, 차트 종류 변경]

1. [판매현황차트] 워크시트에서 차트를 선택한다.

2. [차트 도구]-[디자인] 탭-[데이터] 그룹-[데이터 선택] 명령을 클릭한다.

3. [데이터 원본 선택] 대화 상자에서 [범례 항목(계열)]의 [추가] 단추를 클릭한다.

4. [계열 편집] 대화 상자에서 [계열 이름]에 단가를 입력한다.

5. [계열 값]의 기존 데이터를 삭제한 후 [판매현황] 시트 탭을 클릭하여 이동한다.

6. D4:D8 셀 범위를 드래그하여 추가하고 [확인] 단추를 클릭한다.

7. [데이터 원본 선택] 대화 상자에서 [확인] 단추를 클릭한다.

8. 차트의 단가 데이터 계열을 클릭하여 선택한다.

9. [차트 도구]-[레이아웃] 탭-[현재 선택 영역] 그룹-[선택 영역 서식] 명령을 클릭한다.

10. [데이터 계열 서식] 대화 상자의 [계열 옵션]-[데이터 계열 지정]에서 ‘보조 축’을 선택하고 [닫기] 단추를 클릭한다.

11. 단가 데이터 계열이 선택되어 있는 상태에서 [차트 도구]-[디자인] 탭-[종류] 그룹-[차트 종류 변경] 명령을 클릭한다.

12. [차트 종류 변경] 대화 상자에서 [꺾은선형]의 ‘표식이 있는 꺾은선형’을 선택하고 [확인] 단추를 클릭한다.

13. 단가 데이터 계열만 꺾은선형 차트 종류로 변경된다.

03. [시험] 워크시트에서 동일한 레코드를 [최종] 워크시트의 B2 셀로 필터링하시오. 다른 기본 설정은 모두 그대로 사용하시오.  (실전1-03.xlsx) [Hint: 고급 필터]

1. [최종] 워크시트를 선택한다.

2. [데이터] 탭-[정렬 및 필터] 그룹-[고급] 명령을 클릭한다.

3. [고급 필터] 대화 상자의 [결과]에서 ‘다른 장소에 복사’ 옵션을 선택한다.

4. [목록 범위]에 커서를 이동하고 [시험] 시트 탭을 클릭한다.

5. [시험] 워크시트로 이동되면 B2:I20 셀 범위를 드래그한다.

6. [조건 범위]는 비어 있는 상태로 둔다.

7. [복사 위치]로 커서를 이동한다.

8. [최종] 시트 탭을 클릭하고 B2 셀을 클릭한다.

9. ‘동일한 레코드는 하나만’ 확인란을 선택하고 [확인] 단추를 클릭한다.

10. 중복되지 않은 동일한 레코드만 추출된다.

04. 이름 관리자를 사용하여 B3:B17 셀 범위를 ‘사원번호’라는 이름으로 정의하시오.

(실전1-04.xlsx) [Hint: 이름 관리자-이름 정의]

1. B3:B17 셀 범위를 선택한다.

2. [수식] 탭-[정의된 이름] 그룹-[이름 관리자] 명령을 클릭한다.

3. [이름 관리자] 대화 상자에서 [새로 만들기] 단추를 클릭한다.

4. [새 이름] 대화 상자의 [이름]에 사원번호를 입력하고 [확인] 단추를 클릭한다.

5. [이름 관리자] 대화 상자에서 [닫기] 단추를 클릭한다.

05. B2 셀에 Access 데이터베이스 ‘판매관리.accdb’ 파일의 ‘판매내역’ 테이블을 가져오시오. 파일을 열 때와 30분 간격으로 자동으로 데이터 연결을 새로 고치도록 속성을 설정하고 ‘상반기’ 이름으로 연결 파일을 내보내도록 저장하시오. (실전1-05.xlsx, 판매관리.accdb)

[Hint: Access 외부 데이터 가져오기]

1. [데이터] 탭-[외부 데이터 가져오기] 그룹-[Access] 명령을 클릭한다.

2. [데이터 원본 선택] 대화 상자에서 ‘판매관리.accdb’ 파일을 찾아 선택하고 [열기] 단추를 클릭한다.

3. [테이블 선택] 대화 상자에서 ‘판매내역’ 테이블을 선택하고 [확인] 단추를 클릭한다.

4. [데이터 가져오기] 대화 상자에서 데이터가 들어갈 위치는 ‘기존 워크시트’에서 B2 셀을 클릭하여 지정한다.

5. 연결 옵션을 변경하기 위해 [속성] 단추를 클릭한다.

6. [연결 속성] 대화 상자의 [사용 현황] 탭에서 ‘다음 간격으로 새로 고침’ 확인란을 선택하고 ‘30분’을 설정한다.

7. ‘파일을 열 때 데이터 새로고침’ 확인란을 선택한다.

8. [정의] 탭에서 [연결 파일 내보내기] 단추를 클릭한다.

9. [파일 저장] 대화 상자에서 [파일 이름]에 상반기를 입력하고 [저장] 단추를 클릭한다.

10. [연결 속성] 대화 상자가 나타나면 [확인] 단추를 클릭한다.

11. [데이터 가져오기] 대화 상자가 나타나면 [확인] 단추를 클릭한다.

06. 접수코드의 마지막 1글자를 참조하여 S면 ‘서울’을 아니면 ‘경기’를 반환하는 수식을 지역 열(D3:D22) 셀 범위에 만드십시오. (실전1-06.xlsx)

[Hint: RIGHT 함수, IF 함수]

1. D3 셀을 선택하고 [수식] 탭-[함수 라이브러리] 그룹-[논리] 명령을 클릭하여 ‘IF’함수를 선택한다.

2. IF [함수 인수] 창이 나타나면 [Logical_test]에 RIGHT(B3,1)=“S”을 입력한다.

3. [Value_if_true]에 “서울”을 입력한다.

4. [Value_if_false]에 “경기”를 입력하고 [확인] 단추를 클릭한다.

5. =IF(RIGHT(B3,1)=“S”, “서울”, “경기”) 수식 결과가 나타나면 D3 셀의 채우기 핸들을 더블 클릭하여 수식을 복사한다.

07. 워크시트에서 수식 오류를 모두 선택하여 해당 셀에 굵게, 빨강, 강조2 글꼴색으로 하고 노랑 음영 색 서식을 설정하시오. (실전1-07.xlsx) [Hint: 이동 옵션, 글꼴 서식]

1. [홈] 탭-[편집] 그룹-[찾기 및 선택]에서 [이동 옵션] 명령을 클릭한다.

2. [이동 옵션] 대화 상자에서 ‘수식’ 옵션을 선택한다.

3. ‘오류’ 확인란을 제외한 ‘숫자’, ‘텍스트’, ‘논리값’ 확인란의 선택을 모두 해제한 후 [확인] 단추를 클릭한다.

4. 수식 오류가 있는 셀이 모두 선택된다.

5. [홈] 탭-[글꼴] 그룹에서 [굵게] 아이콘을 클릭한다.

6. [채우기 색] 아이콘의 목록 단추를 클릭하여 [표준 색]에서 ‘노랑’을 선택한다.

7. [글꼴 색] 아이콘의 목록 단추를 클릭하여 [테마 색]에서 ‘빨강, 강조2’를 선택한다.

8. 수식 오류가 있는 셀에만 셀 서식이 적용된다.

08. F3:L8 셀 범위에 변수 두 개의 데이터 표를 만드시오. 행 입력 셀을 C4 셀로 설정하고 열 입력 셀을 C3셀로 설정하시오. (실전1-08.xlsx) [Hint: 데이터 표]

1. 데이터 표를 적용할 F3:L8 셀 범위를 선택한다.

2. [데이터] 탭-[데이터 도구] 그룹-[가상 분석]-[데이터 표] 명령을 클릭한다.

3. [데이터 표] 대화 상자에서 [행 입력 셀]은 환율인 C4 셀을 클릭한다.

4. [열 입력 셀]은 달러인 C3 셀을 클릭한 후 [확인] 단추를 클릭한다.

09. 합계가 120점 이상이면 ‘합격’을 미만이면 ‘불합격’을 계산하는 ‘평가’라는 이름의 사용자 정의 함수를 만드시오. 평가 사용자 정의 함수를 사용하여 평가(H3:H22)를 구하시오.

(실전1-09.xlsm) [Hint: 사용자 정의 함수]

1. [개발 도구] 탭-[코드] 그룹-[Visual Basic] 명령을 클릭한다.

2. [모듈] 폴더에서 ‘Module1’을 더블 클릭한다.

3. 모듈 창에 다음과 같이 코드를 작성한다.

Function 평가(합계)

If 합계 > 120 Then

평가=“합격”

Else

평가=“불합격”

End If

End Function

4. VB 편집기 창의 [닫기] 아이콘을 클릭한다.

5. H3 셀을 선택한다.

6. [수식] 탭-[함수 라이브러리] 그룹-[함수 삽입] 명령을 클릭한다.

7. [함수 마법사] 대화 상자에서 [범주 선택]을 ‘사용자 정의’로 선택한다.

8. [함수 선택] 목록에서 ‘평가’를 선택하고 [확인] 단추를 클릭한다.

9. [함수 인수] 창에서 [합계] 인수를 G3으로 설정하고 [확인] 단추를 클릭한다.

10. H3 셀에 수식 결과가 나타나면 채우기 핸들을 더블 클릭하여 수식을 복사한다.

10. 생산품명 열(C4:C17)의 값을 토너, 잉크, 프린터, 전산용지로 제한하여 목록으로 만드시오. E4:E17 셀 범위의 값이 F2 셀의 값보다 작은 생산수량 데이터에 동그라미 치시오. 다른 기본 설정은 모두 그대로 적용하시오. (실전1-10.xlsx) [Hint: 데이터 유효성 검사]

1. 생산품명 C4:C17 셀 범위를 드래그하여 선택한다.

2. [데이터] 탭-[데이터 도구] 그룹-[데이터 유효성 검사] 명령을 클릭한다.

3. [데이터 유효성] 대화 상자의 [설정] 탭으로 이동한다.

4. [제한 대상]을 ‘목록’으로 선택한다.

5. [원본]에 토너, 잉크, 프린터, 전산용지를 입력하고 [확인] 단추를 클릭한다.

6. 생산수량 E4:E17 셀 범위를 드래그하여 선택한다.

7. [데이터] 탭-[데이터 도구] 그룹-[데이터 유효성 검사] 명령을 클릭한다.

8. [데이터 유효성] 대화 상자의 [설정] 탭에서 [제한 대상]을 ‘정수’로 선택한다.

9. [제한 방법]을 ‘>’으로 선택한다.

10. [최소값]에 커서를 이동하고 F2 셀을 클릭한다.

11. ‘=F2’가 입력되면 <F4> 키를 누른다.

12. 절대 참조 주소 ‘=$F$2’로 변경되면 [확인] 단추를 클릭한다.

13. [데이터] 탭-[데이터 도구] 그룹-[데이터 유효성 검사] 명령의 화살표를 클릭하여 [잘못된 데이터]를 클릭한다.

14. 유효성 규칙에 어긋나는 데이터에만 빨강색의 동그라미가 표시된다.

11. 배재호 필기점수 C12 셀이 직접 및 간접적으로 참조하는 모든 셀을 화살표로 표시하시오.

(실전1-11.xlsx) [Hint: 참조하는 셀 추적]

1. C12 셀을 선택한다.

2. [수식] 탭-[수식 분석] 그룹-[참조하는 셀 추적] 명령을 두 번 클릭한다.

12. 다음 작업을 수행하는 ‘성적인쇄’라는 새 매크로를 기록하시오. 다른 기본 설정은 모두 그대로 적용하시오.

- 용지 방향 가로

- B6:G15 셀 범위만 인쇄 영역 설정

(실전1-12.xlsx) [Hint: 매크로 기록]

1. [개발 도구] 탭-[코드] 그룹-[매크로 기록] 명령이나, 상태 표시줄에서 [매크로 기록] 아이콘을 클릭한다.

2. [매크로 기록] 대화 상자에서 [매크로 이름]에 성적인쇄를 입력하고 [확인] 단추를 클릭한다.

3. [페이지 레이아웃] 탭-[페이지 설정] 그룹-[용지 방향] 명령을 클릭하여 ‘가로’를 선택한다.

4. B6:G15 셀 범위를 드래그하여 선택한다.

5. [페이지 레이아웃] 탭-[페이지 설정] 그룹-[인쇄 영역]-[인쇄 영역 설정] 명령을 클릭한다.

6. [개발 도구] 탭-[코드] 그룹-[기록 중지] 명령이나, 상태 표시줄의 [기록 중지] 아이콘을 클릭한다.

13. 새 워크시트에서 다음과 같이 피벗 테이블을 만드시오. 월을 보고서필터로, 성명을 열 레이블로, 제품을 행 레이블로, 수량을 값으로 설정하시오. 수량 필드 값을 평균으로 값 필드의 이름을 ‘평균 수량’ 으로 변경하고, 소수점 이하 한 자리의 숫자 서식을 적용하시오.

(실전1-13.xlsx) [Hint: 피벗 테이블]

1. 데이터베이스 목록에 셀 포인터를 이동한다.

2. [삽입] 탭-[표] 그룹-[피벗 테이블] 명령을 클릭한다.

3. [피벗 테이블 만들기] 대화 상자에서 분석할 데이터를 ‘표 또는 범위 선택’의 [표/범위]에 데이터베이스 범위가 자동으로 참조된다.

4. 피벗 테이블 보고서를 넣을 위치를 ‘새 워크시트’로 두고 [확인] 단추를 클릭한다.

5. [피벗 테이블 필드 목록] 작업창의 [보고서에 추가할 필드 선택]에서 ‘월’ 필드를 [보고서 필터] 위치로 드래그한다.

6. ‘성명’ 필드를 [열 레이블]로 드래그한다.

7. ‘제품’ 필드를 [행 레이블]로 드래그한다.

8. ‘수량’ 필드를 [값]으로 드래그한다.

9. [피벗 테이블 필드 목록] 작업창의 [값] 영역에서 ‘합계 : 수량’ 필드의 목록 단추를 클릭하여 [값 필드 설정] 메뉴를 선택한다.

10. [값 필드 설정] 대화 상자의 [사용할 함수] 탭에서 ‘평균’을 선택한다.

11. [사용자 지정 이름]을 평균 수량으로 수정한다.

12. [표시 형식] 단추를 클릭한다.

13. [셀 서식] 대화 상자에서 [범주]를 ‘숫자’로 선택한다.

14. [소수 자릿수]를 ‘1’로 설정하고 [확인] 단추를 클릭한다.

15. [값 필드 설정] 대화 상자에서 [확인] 단추를 클릭한다.

16. [피벗 테이블 필드 목록] 작업창에서 [닫기] 아이콘을 클릭한다.

14. 2010년의 값과 ‘예상성장률’이라고 정의된 이름을 곱한 값을 2010년 값과 더하는 수식을 F7 셀에 구하시오. 수식을 F13 셀까지 복사하시오. (실전1-14.xlsx) [Hint: 이름, 수식]

1. F7 셀을 선택하고 =D7*예상성장률+D7 수식을 입력한 후 <Enter> 키를 누른다.

2. F7 셀의 채우기 핸들을 F13 셀까지 드래그하여 수식을 복사한다.

15. 2011년 계획 매출합계가 3천만원이 되려면 예상 성장률이 얼마가 돼야 하는지 구하시오.

(실전1-15.xlsx) [Hint: 목표값 찾기]

1. 수식 셀 F14 셀을 선택한다.

2. [데이터] 탭-[데이터 도구] 그룹-[가상 분석]-[목표값 찾기] 명령을 클릭한다.

3. [목표값 찾기] 대화 상자의 [수식 셀]에 F14 셀 주소가 표시된다.

4. [찾는 값]에 30000000을 입력한다.

5. [값을 바꿀 셀]에 커서를 이동하고 F4 셀을 클릭하고 [확인] 단추를 클릭한다.

6. 목표값 찾기 결과가 나타나면 [확인] 단추를 클릭한다.

16. ‘2010년’이라는 텍스트의 확인란(양식 컨트롤)을 F19 셀에 추가하시오. 확인란이 기본적으로 선택되고, 3차원 음영이 적용되고, 다른 웹 텍스트나 레이블 대체 텍스트는 없고 H17 셀에 연결되도록 옵션을 설정하시오. (실전1-16.xlsx) [Hint: 양식 컨트롤]

1. [개발 도구] 탭-[컨트롤] 그룹-[삽입]-[옵션 단추] 양식 컨트롤을 클릭한다.

2. F19 셀 위에서 드래그하여 그린다.

3. 옵션 단추의 텍스트를 2010년으로 수정한다.

4. 옵션 단추가 선택되어 있는 상태에서 [개발 도구] 탭-[컨트롤] 그룹-[속성] 명령을 클릭한다.

5. [컨트롤 서식] 대화 상자의 [컨트롤] 탭에서 [값]을 ‘선택한 상태’로 선택한다.

6. [셀 연결]에 커서를 이동하고 H17 셀을 클릭한다.

7. ‘3차원음영’ 확인란을 선택한다.

8. [웹] 탭에서 [대체 텍스트]의 ‘2010년’을 선택하고 <Delete> 키를 눌러 삭제한다.

9. [확인] 단추를 클릭한다.

17. ‘평가’라는 이름의 범위를 참조하여 평가가 합격인 셀의 개수를 세는 수식을 K2 셀에 구하시오.

(실전1-17.xlsx) [Hint: COUNTIF 함수]

1. K2 셀을 선택한다.

2. [수식] 탭-[함수 라이브러리] 그룹-[함수 추가]-[통계]에서 ‘COUNTIF’ 함수를 클릭한다.

3. COUNTIF [함수 인수] 창에서 [Range] 인수에 평가를 입력한다.

4. [Criteria] 인수에 “합격”을 입력하고 [확인] 단추를 클릭한다.

5. =COUNTIF(평가, “합격”) 수식이 완성된다.

18. 다음 매개변수를 사용하여 ‘회원.CSV’ 파일을 A1 셀로 가져오시오. 다른 기본 설정은 모두 그대로 적용하시오.

- 첫 번째 열은 가져오지 마시오.

- 네 번째 열은 텍스트로 가져오시오.

- 쿼리를  1차회원 이름으로 저장하시오.

(실전1-18.xlsx, 회원.CSV) [Hint: 텍스트 외부 데이터 가져오기]

1. [데이터] 탭-[외부 데이터 가져오기] 그룹-[텍스트] 명령을 클릭한다.

2. [텍스트 파일 가져오기] 대화 상자에서 ‘회원.csv’ 파일을 찾아 선택하고 [가져오기] 단추를 클릭한다.

3. [텍스트 마법사] 3단계 중 1단계가 나타나면 ‘구분 기호로 분리됨’을 선택하고 [다음] 단추를 클릭한다.

4. [텍스트 마법사] 2단계의 구분 기호에서 ‘쉼표’ 확인란을 선택하고 [다음] 단추를 클릭한다.

5. [텍스트 마법사] 3단계의 [데이터 미리 보기]에서 첫 번째 ‘회원번호’ 열을 클릭하고 ‘열 가져오지 않음(건너뜀)’ 옵션을 선택한다.

6. [데이터 미리 보기]에서 네 번째 ‘우편번호’ 열을 클릭하고 ‘텍스트’ 옵션을 선택한 후 [마침] 단추를 클릭한다.

7. [데이터 가져오기] 대화 상자에서 ‘기존 워크시트’ 옵션이 선택된 상태에서 A1 셀을 클릭하여 지정한다.

8. [속성] 단추를 클릭한다.

9. [외부 데이터 범위 속성] 대화 상자에서 [이름]을 ‘1차회원’으로 입력한다.

10. [쿼리 정의]의 ‘쿼리 정의 저장’ 확인란이 선택되어 있는 상태에서 [확인] 단추를 클릭한다.

11. 다시 [데이터 가져오기] 대화 상자가 나타나면 [확인] 단추를 클릭한다.

19. ‘성적인쇄’ 매크로의 새 바로 가기 키를 Ctrl+Q로 변경하시오.

(실전1-19.xlsm) [Hint: 매크로 관리]

1. [보안 경고] 메시지 표시줄에서 [옵션] 단추를 클릭한다.

2. [Microsoft office 보안 경고] 창에서 ‘이 콘텐츠 사용’ 확인란을 선택하고 [확인] 단추를 클릭한다.

3. [개발 도구] 탭-[코드] 그룹-[매크로] 명령을 클릭한다.

4. [매크로] 대화 상자에서 ‘성적인쇄’ 매크로를 선택한 후 [옵션] 단추를 클릭한다.

5. [매크로 옵션] 대화 상자에서 [바로 가기 키]에 Ctrl+Q를 입력하고 [확인] 단추를 클릭한다.

6. [매크로] 대화 상자에서 [닫기] 아이콘을 클릭한다.

20. [통합] 워크시트의 B2 셀에 [강북]과 [강남] 워크시트의 생산날짜를 제외하고 생산현황 데이터에 대한 최대값을 통합하여 첫 행 및 왼쪽 열 레이블과 함께 표시하시오.

(실전1-20.xlsx) [Hint: 데이터 통합]

1. [통합] 워크시트에서 B2 셀을 선택한다.

2. [데이터] 탭-[데이터 도구] 그룹-[통합] 명령을 클릭한다.

3. [통합] 대화 상자에서 [함수]를 ‘최대값’으로 선택한다.

4. [참조]로 커서를 이동한 후 [강북] 시트 탭을 클릭한다.

5. C2:F9 셀 범위를 드래그하여 선택한 후 [추가] 단추를 클릭한다.

6. [강남] 시트 탭을 클릭하고 [추가] 단추를 클릭한다.

7. C2:F11 셀 범위를 드래그하여 선택한 후 [추가] 단추를 클릭한다.

8. [모든 참조 영역]에 참조 주소가 모두 추가된다.

9. [사용할 레이블]에서 ‘첫 행’과 ‘왼쪽 열’의 확인란을 모두 선택하고 [확인] 단추를 클릭한다.

21. I5 셀에서 시작해서 부서명이 산업영업부, 전략영업부, 또는 해외영업부인 레코드를 필터링하는 조건으로 만들어 I10 셀에 필터링된 값만 복사하시오.

(실전1-21.xlsx) [Hint: 고급 필터]

1. I5 셀부터 시작하여 다음과 같이 조건을 작성한다.

부서명

산업영업부

전략영업부

해외영업부

2. 데이터베이스의 임의의 셀을 선택한다.

3. [데이터] 탭-[정렬 및 필터] 그룹-[고급] 명령을 클릭한다.

4. [고급 필터] 대화 상자의 [결과]를 ‘다른 장소에 복사’로 선택한다.

5. [목록 범위]에 데이터베이스 범위가 자동으로 들어온다.

6. [조건 범위]로 커서를 이동하고 I5:I8 셀 범위를 드래그하여 선택한다.

7. [복사 위치]로 커서를 이동하고 I10 셀을 클릭한 후 [확인] 단추를 클릭한다.

8. 15개의 레코드가 필터링된다.

22. 파일이 열릴 때만 새로 고침이 되고 백그라운드에서 새로 고침하지 않도록 표를 수정하시오.

(실전1-22.xlsx) [Hint: 연결 속성]

1. [보안 경고] 메시지 표시줄에서 [옵션] 단추를 클릭한다.

2. [Microsoft office 보안 경고] 창에서 ‘이 콘텐츠 사용’ 확인란을 선택하고 [확인] 단추를 클릭한다.

3. [데이터] 탭-[연결] 그룹-[모두 새로 고침]-[연결 속성]이나, [표 도구]-[디자인] 탭-[외부 표 데이터] 그룹-[새로 고침]-[연결 속성]을 클릭한다.

4. [연결 속성] 대화 상자의 [사용 현황] 탭에서 ‘다른 작업하면서 새로 고침’ 확인란의 선택을 해제한다.

5. ‘파일을 열 때 데이터 새로고침’ 확인란을 선택하고 [확인] 단추를 클릭한다.

23. 두 개의 시나리오를 만드시오. ‘올림’은 할인율이 30%로 증가했을 경우이고, ‘내림’은 10% 감소했을 경우로 설정하시오. 구입액의 합계 결과인 F11 셀에 대한 시나리오 요약보고서를 만드시오.

(실전1-23.xlsx) [Hint: 시나리오]

1. [데이터] 탭-[데이터 도구] 그룹-[가상 분석]-[시나리오 관리자] 명령을 클릭한다.

2. [시나리오 관리자] 대화 상자에서 [추가] 단추를 클릭한다.

3. [시나리오 추가] 대화 상자의 [시나리오 이름]에 올림을 입력한다.

4. [변경 셀]에 커서를 이동하고 F4 셀을 클릭하고 [확인] 단추를 클릭한다.

5. [시나리오 값] 대화 상자의 할인율에 0.3을 입력하고 [추가] 단추를 클릭한다.

6. [시나리오 추가] 대화 상자의 [시나리오 이름]에 내림을 입력한다.

7. [변경 셀]이 F4 셀임을 확인하고 [확인] 단추를 클릭한다.

8. [시나리오 값] 대화 상자의 할인율에 0.1을 입력하고 [확인] 단추를 클릭한다.

9. [시나리오 관리자] 대화 상자의 [요약] 단추를 클릭한다.

7. [시나리오 요약] 대화 상자에서 ‘시나리오 요약’ 옵션을 선택한다.

8. [결과 셀]에 커서를 이동한 후 F11 셀을 클릭한 후 [확인] 단추를 클릭합니다.

9. [시나리오 요약] 워크시트가 삽입되고 요약 보고서가 나타난다.

24. 판매 현황 차트의 단가 계열에 하강선을 추가하시오.

(실전1-24.xlsx) [Hint: 하강선]

1. 차트에서 단가 계열인 꺾은선을 클릭하여 선택한다.

2. [차트 도구]-[레이아웃] 탭-[분석] 그룹-[선]에서 ‘하강선’을 클릭한다.

25. 생활문화교실 표를 참조하여 C10:C14 셀 범위에 과목별 수강료의 합을 계산하시오.

(실전1-25.xlsx) [Hint: SUMIF 함수]

1. C10 셀을 선택한다.

2. [수식] 탭-[함수 라이브러리] 그룹-[수학/삼각] 명령을 클릭하여 ‘SUMIF’함수를 클릭한다.

3. [함수 인수] 창이 나타나면 [Range]는 $C$5:$L$5, [Criteria]는 B10, [Sum_range]는 $C$6:$L$6을 입력하고 [확인] 단추를 클릭한다.

※ 수식 =SUMIF($C$5:$L$5,B10,$C$6:$L$6)을 직접 작성해도 된다.

4. C10 셀의 채우기 핸들을 더블 클릭하여 수식을 복사한다.

26. ‘인사정보.xsd’ 스키마의 dataroot 맵을 현재 워크시트를 추가하시오 사원번호, 성명, 연락처, 우편번호, 주소 XML 요소를 각 필드에 추가하고 ‘인사정보.xml’ 데이터 파일을 가져오시오.

(실전1-26.xlsx, 인사정보.xsd, 인사정보.xml) [Hint: XML 데이터]

1. [개발 도구] 탭-[XML] 그룹-[원본] 명령을 클릭한다.

2. [XML 원본] 작업창 하단의 [XML 맵] 단추를 클릭한다.

3. [XML 맵] 대화 상자에서 [추가] 단추를 클릭한다.

4. ‘인사정보.xsd’ 파일을 찾아 선택하고 [열기] 단추를 클릭한다.

5. [여러 루트] 대화 상자에서 ‘dataroot’를 선택하고 [확인] 단추를 클릭한다.

6. [XML 맵] 대화 상자에서 맵이 나타나면 [확인] 단추를 클릭한다.

7. [XML 원본] 작업창에서 ‘사원번호’ 필드를 B4 셀로 드래그한다.

8. ‘성명’ 필드를 C4 셀로 드래그한다.

9. ‘연락처’ 필드를 D4 셀로 드래그한다.

10. ‘우편번호’ 필드를 E4 셀로 드래그한다.

11. ‘주소’ 필드를 F4 셀로 드래그한다.

12. [개발 도구] 탭-[XML] 그룹-[가져오기] 명령이나, 표 안에서 마우스 오른쪽 단추를 클릭하여 [XML]-[가져오기] 메뉴를 클릭한다.

13. [XML 가져오기] 대화 상자에서 ‘인사정보.xml’ 데이터 파일을 찾아 선택하고 [가져오기] 단추를 클릭한다.

14. XML 데이터 중 매핑된 필드의 데이터가 표시됨을 확인한다.

15. [XML 원본] 작업창에서 [닫기] 아이콘을 클릭한다.

27. ‘2009년실적.xlsx’의 [영업실적] 워크시트에 있는 C5:D8 셀 값을 사용하여 현재 통합 문서의 [계획] 워크시트의 C2 셀에 평균을 계산하시오.

(실전1-27.xlsx, 2009년실적.xlsx) [Hint: AVERAGE 함수]

1. 실전1-27.xlsx 통합 문서의 [계획] 워크시트의 C2 셀을 선택한다.

2. =AVERAGE(를 입력한다.

3. [보기] 탭-[창] 그룹-[창 전환] 명령을 클릭하여 열려 있는 ‘2009년실적.xlsx’ 파일을 선택한다.

4. 2009년실적.xlsx 통합 문서로 전환된다.

5. C5:D8 셀 범위를 드래그하면 ‘=AVERAGE([2009년실적.xlsx]영업실적!$C$5:$D$8’ 수식이 완성된다.

6. 다시 실전 1-27.xlsx 통합 문서를 전환하고 입력한 후 <Enter> 키를 누른다.

7. 평균 값이 구해진다.

28. 양식컨트롤의 연결 셀인 H17 셀의 값의 위치에 있는 년도를 C6:D6 셀 범위에서 찾아 H20 셀에 가져오는 수식을 계산하시오.

(실전1-28.xlsx) [Hint: INDEX 함수]

1. H20 셀을 선택하고 [수식] 탭-[함수 라이브러리] 그룹-[찾기/참조 영역]에서 ‘INDEX’ 함수를 클릭한다.

2. [인수 선택] 대화 상자에서 array가 선택되어 있는 상태에서 [확인] 단추를 클릭한다.

3. [함수 인수] 창에서 [Array]는 C6:D6, [Row_num]은 H17, [Column_num]은 생략하고 [확인] 단추를 클릭한다.

※ 수식 =INDEX(C6:D6,H17)을 직접 작성해도 된다.

29. [분석] 워크시트의 A1 셀에 [판매] 워크시트의 E2:G33 데이터를 사용한 피벗 차트를 만드시오. 날짜는 축 필드, 판매 수는 값 필드로 지정하시오.

(실전1-29.xlsx) [Hint: 피벗 차트]

1. [판매] 워크시트의 데이터베이스 임의의 셀을 선택한다.

2. [삽입] 탭-[표] 그룹-[피벗 테이블]-[피벗 차트] 명령을 클릭한다.

3. [피벗 테이블 및 피벗 차트 만들기] 대화 상자에서 피벗 테이블 보고서를 넣을 위치로 ‘기존 워크시트’ 옵션을 선택한다.

4. [위치]로 커서를 이동한다.

5. [분석] 시트 탭을 클릭하고 A1 셀을 선택한다.

6. ‘분석!$A$1’이 나타나면 [확인] 단추를 클릭한다.

7. [피벗 테이블 필드 목록] 작업창의 [보고서에 추가할 필드 선택]에서 ‘날짜’ 필드를 [축 필드(항목)] 위치로 드래그한다.

8. ‘판매수’ 필드를 [값]으로 드래그하여 피벗 차트 레이아웃을 구성한다.

9. [피벗 테이블 필드 목록] 작업창의 [닫기] 아이콘을 클릭한다.

30. 행이 7일씩 세트 그룹으로 묶이고 2010-7-31일에서 끝나도록 피벗 테이블을 수정하시오.

(실전1-30.xlsx) [Hint: 그룹 필드]

1. [분석] 워크시트에서 피벗 테이블의 헹 레이블의 값이 있는 임의의 셀을 선택한다.

2. [피벗 테이블 도구]-[옵션] 탭-[그룹] 그룹-[그룹 필드] 명령이나, 선택한 셀 위에서 마우스 오른쪽 단추를 눌러 [그룹] 메뉴를 클릭한다.

3. [그룹화] 대화 상자에서 [시작]은 그대로 둔다.

4. [끝]은 2010-07-31로 수정한다.

5. [단위]는 목록에서‘월’을 클릭해 선택을 해제하고

6. ‘일’을 클릭해 선택한다.

7. [날짜 수]는 ‘7’로 설정하고 [확인] 단추를 클릭한다.

8. 행 필드가 7일씩 그룹으로 묶인다.

31. 현재 매크로 사용 통합 문서에서 ‘영업계획.xlsm’ 통합 문서로 ‘성적인쇄’ 매크로를 복사하시오.

(실전1-31.xlsm, 영업계획.xlsm) [Hint: 매크로 복사]

1. [보안 경고] 메시지 표시줄에서 [옵션] 단추를 클릭한다.

2. [Microsoft office 보안 옵션] 대화 상자에서 ‘이 콘텐츠 사용’ 옵션을 선택하고 [확인] 단추를 클릭한다.

3. [개발 도구] 탭-[코드] 그룹-[매크로] 명령을 클릭한다.

4. [매크로] 대화 상자에서 ‘성적인쇄’ 매크로를 선택한 후 [편집] 단추를 클릭한다.

5. ‘Sub 성적인쇄 ~ End Sub’범위를 선택한 후 <Ctrl+C> 키를 누르거나, 마우스 오른쪽 단추를 눌러 [복사] 메뉴를 선택하거나, 도구 모음에서 [복사] 아이콘을 클릭한다.

6. ‘영업계획.xlsm’ 통합문서의 [모듈] 폴더에서 ‘Module1’을 더블클릭하여 연다.

7. <Ctrl+V> 키를 누르거나, 선택 영역 위에서 마우스 오른쪽 단추를 눌러 [붙여넣기] 메뉴를 클릭하거나, 도구모음의 [붙여넣기] 아이콘을 클릭하여 붙여 넣는다.

8. VB 편집기 창의 [닫기] 아이콘을 클릭한다.

32. [집계] 워크시트에서 금액에서 금액의 0.1 할인율을 빼는 판매가 필드를 피벗 테이블에 삽입하시오.

(실전1-32.xlsx) [Hint: 계산 필드]

1. [집계] 워크시트에서 피벗 테이블 레이아웃의 임의의 셀을 클릭한다.

2. [피벗 테이블 도구]-[옵션] 탭-[도구] 그룹-[수식]-[계산 필드] 명령을 클릭한다.

3. [계산 필드 삽입] 대화 상자에서 [이름]에 판매가를 입력한다.

4. [수식]에 기존 내용을 삭제한다.

5. [필드] 목록에서 ‘금액’을 선택한 후 [필드 삽입] 단추를 클릭한다.

6. [수식]에 ‘=금액’이 입력되면 빼기 - 연산자를 입력한다.

7. [필드] 목록에서‘금액’을 선택한 후 [필드 삽입] 단추를 클릭한다.

8. [수식]에 ‘=금액-금액’이 입력되면 *0.1을 입력한다.

9. [추가] 단추를 클릭한다.

10. [확인] 단추를 클릭한다.

11. 새 판매가 필드가 값 영역에 추가된다.

33. B4 셀에 적용되어 있는 서식을 모두 찾아서 B17 셀에 적용되어 있는 서식으로 교체하시오.

(실전1-33.xlsx) [Hint: 바꾸기]

1. [홈] 탭-[편집] 그룹-[찾기 및 선택]에서 [바꾸기] 명령을 클릭한다.

2. [찾기 및 바꾸기] 대화 상자의 [바꾸기] 탭에서 [옵션] 단추를 클릭한다.

3. [찾을 내용]의 [서식] 목록 단추를 클릭하여 [셀에서 서식 선택]을 클릭한다.

4. 마우스 모양이 변경되면 찾을 서식이 있는 B4 셀을 클릭한다.

5. [바꿀 내용]의 [서식] 목록 단추를 클릭하여 [셀에서 서식 선택]을 클릭한다.

6. 마우스 모양이 변경되면 바꿀 서식이 있는 B17 셀을 클릭한다.

7. [찾기 및 바꾸기] 대화 상자에서 [모두 바꾸기] 단추를 클릭한다.

8. 6개 항목이 바뀌었다는 메시지 창이 나타나면 [확인] 단추를 클릭한다.

9. [찾기 및 바꾸기] 대화 상자에서 [닫기] 단추를 클릭한다.

34. 생활문화표를 참조하여 성명에 대한 과목을 반환하는 수식을 F10 셀에 구하시오.

(실전1-34.xlsx) [Hint: HLOOKUP 함수]

1. F10 셀을 선택한다.

2. [수식] 탭-[함수 라이브러리] 그룹-[찾기/참조 영역] 명령을 클릭하여 ‘HLOOKUP’ 함수를 클릭한다.

3. [함수 인수] 창이 나타나면 [Lookup_value]는 F9, [Table_array]는 C4:L6, [Row_index_num]은 2, [Range_lookup]은 FALSE를 입력하고 [확인] 단추를 클릭한다.

※ 수식 =HLOOKUP(F9,C4:L6,2,FALSE)를 직접 작성해도 된다.

35. C3 셀에 있는 날짜에서 월만 추출하여 E1 셀에 반환하는 식을 구하시오.

(실전1-35.xlsx) [Hint: MONTH 함수]

1. E1 셀을 선택한다.

2. =MONTH(C3)을 입력하고 <Enter> 키를 누른다.

실전모의고사 2회

01. E2:E16 셀 범위에서 빈 셀을 모두 찾아서 선택하고 선택한 셀에 X를 입력하시오.

(실전2-01.xlsx) [Hint: 이동 옵션]

1. E2:E16 셀 범위를 드래그하여 선택한다.

2. [홈] 탭-[편집] 그룹-[찾기 및 선택]-[이동 옵션] 명령을 클릭한다.

3. [이동 옵션] 대화 상자에서 ‘빈 셀’을 선택하고 [확인] 단추를 클릭한다.

4. 빈 셀이 모두 선택된다.

5. X를 입력하고 <Ctrl+Enter> 키를 눌러 동시에 입력한다.

02. D18 셀의 시간, 30분, 45초 숫자 값으로 시간을 만드는 수식을 A19 셀에 만드시오.

(실전2-02.xlsx) [Hint: TIME 함수]

1. A19 셀을 선택한다.

2. [수식] 탭-[함수 라이브러리] 그룹-[날짜 및 시간] 명령을 클릭하여 ‘TIME’을 클릭한다.

3. [함수 인수] 창에서 [Hour]는 D18, [Minute]은 30, [Second]는 45를 설정하고 [확인] 단추를 클릭한다.

※ 수식 =TIME(D18,30,45)를 직접 작성해도 된다.

03. 매수수수료 열 및 수익률 열의 모든 값에 대한 조사식을 조사식 창에 추가하시오.

(실전2-03.xlsx) [Hint: 조사식 추가]

1. 매수수수료 값이 있는 F4:F13 셀 범위를 선택한다.

2. <Ctrl> 키를 누른 채 수익률 값이 있는 K4:K13 셀 범위를 드래그하여 동시에 선택한다.

3. [수식] 탭-[수식 분석] 그룹-[조사식 창] 명령을 클릭한다.

4. [조사식 창]에서 [조사식 추가] 단추를 클릭한다.

5. [조사식 추가] 대화 상자에서 [추가] 단추를 클릭한다.

6. [조사식 창]에 모든 조사식이 추가되면 조사식 창의 [닫기] 아이콘을 클릭한다.

04. 2011년 영업 계획 평균이 45,000을 달성하기 위하여 필요한 조정율을 구하시오.

(실전2-04.xlsx) [Hint: 목표값 찾기]

1. 수식이 있는 C6 셀을 선택한다.

2. [데이터] 탭-[데이터 도구] 그룹-[가상 분석]-[목표값 찾기] 명령을 클릭한다.

3. [목표값 찾기] 대화 상자의 [수식 셀]에 C6 셀 주소가 표시된다.

4. [찾는 값]에 45000을 입력한다.

5. [값을 바꿀 셀]에 커서를 이동하고 C5 셀을 클릭하고 [확인] 단추를 클릭한다.

6. 목표값 찾기 결과가 나타나면 [확인] 단추를 클릭한다.

7. 영엽 계획 평균 값이 45000이 되려면 조정율이 16%로 조정돼야 한다.

05. 남자 인구 2차원 누적 막대 차트에 계열선이 표시되도록 추가하시오. 다른 기본 설정은 모두 그대로 적용하시오.

(실전2-05.xlsx) [Hint: 분석선]

1. 차트 영역을 선택한다.

2. [차트 도구]-[레이아웃] 탭-[분석] 그룹-[선]에서 ‘계열선’을 클릭한다.

06. 셀 서식을 다음과 같이 변경하는 ‘레이블’이라는 새 매크로를 기록하시오. 바로 가기 키는 Ctrl+E로 설정하시오. 3행 레이블에 매크로 적용하시오.

- 글꼴크기 : 12포인트, 굵게

- 가운데 맞춤

- 행 높이: 21.00 (28픽셀)

(실전2-06.xlsx) [Hint: 매크로 기록, 매크로 실행]

1. A2 셀이 선택되어 있는 상태에서 [개발 도구] 탭-[코드] 그룹-[매크로 기록] 명령이나, 상태 표시줄에서 [매크로 기록] 아이콘을 클릭한다.

2. [매크로 기록] 대화 상자에서 [매크로 이름]에 레이블을 입력한다.

3. [바로 가기 키]에 CTRL+E를 입력하고 [확인] 단추를 클릭한다.

4. [홈] 탭-[글꼴] 그룹-[글꼴 크기] 명령의 목록 단추를 클릭하여 ‘12’로 설정하고 [굵게] 아이콘을 클릭한다.

5. [홈] 탭-[맞춤] 그룹-[가운데 맞춤] 아이콘을 클릭한다.

6. [홈] 탭-[셀] 그룹-[서식]-[행 높이] 명령을 클릭한다.

7. [행 높이] 대화 상자에 [행 높이]를 21.00으로 입력하고 [확인] 단추를 클릭한다.

8. [개발 도구] 탭-[코드] 그룹-[기록 중지] 명령이나, 상태 표시줄에서 [기록 중지] 아이콘을 클릭한다.

9. A3:F3 셀 범위를 선택하고 <Ctrl+E> 키를 누른다.

07. A2 셀에 있는 품번 텍스트에서 4번째부터 6번째까지 문자를 추출하는 수식을 B2 셀에 구하시오. 수식을 B16 셀까지 복사하시오.

(실전2-07.xlsx) [Hint: MID 함수]

1. B2 셀을 선택한다.

2. [수식] 탭-[함수 라이브러리] 그룹-[텍스트] 명령을 클릭하여 'MID’를 클릭한다.

3. [Text]는 A2, [Start_num]는 4, [Num_chars]는 3을 입력하고 [확인] 단추를 클릭한다.

4. B2 셀의 채우기 핸들을 더블 클릭하여 수식을 복사한다.

※ 수식 =MID(A2,4,3)을 직접 작성해도 된다.

08. [매출통합] 워크시트의 데이터 영역(C3:G14)에 [상반기] 및 [하반기] 워크시트에서 통합한 평균 매출액을 표시하시오.

(실전2-08.xlsx) [Hint: 데이터 통합]

1. [매출통합] 워크시트의 C3 셀을 선택한다.

2. [데이터] 탭-[데이터 도구] 그룹-[통합] 명령을 클릭한다.

3. [통합] 대화 상자에서 [함수]를 ‘평균’으로 선택한다.

4. [참조]에 커서를 이동한 후 [상반기] 시트 탭을 클릭한다.

5. C3:G14 셀 범위를 드래그하여 선택한 후 [추가] 단추를 클릭한다.

6. [하반기] 시트 탭을 클릭하고 C3:G14 셀 범위가 그대로 들어오면 [추가] 단추를 클릭한다.

7. [모든 참조 영역]에 참조 주소가 모두 추가되면 [확인] 단추를 클릭한다.

09. [상품목록] 워크시트에 J3 셀부터 시작해서 색상이 KH 또는 NV 또는 BR이고 재고량이 50개 이상 100 미만인 조건 목록을 만드시오. 필터링 결과를 [필터] 워크시트 B2 셀에 복사하시오.

(실전2-09.xlsx) [Hint: 고급 필터]

1. [상품목록] 워크시트에서 J3 셀을 선택하고 다음과 같이 조건을 작성한다.

색상	재고량	재고량
KH	>=50	<100
NV	>=50	<100
BR	>=50	<100

2. [필터] 워크시트로 이동한 후 [데이터] 탭-[정렬 및 필터] 그룹-[고급] 명령을 클릭한다.

3. [고급 필터] 대화 상자의 [결과]를 ‘다른 장소에 복사’로 선택한다.

4. [목록 범위]로 커서를 이동하고 [상품목록] 시트 탭을 클릭한다.

5. 데이터베이스의 임의의 셀을 선택하고 <Ctrl+A> 키를 눌러 데이터베이스 범위 전체를 선택한다.

6. [조건 범위]로 커서를 이동하고 [상품목록] 시트 탭을 클릭한다.

7. J3:L6 셀 범위를 드래그하여 선택한다.

8. [복사 위치]로 커서를 이동하고 [필터] 워크시트의 B2 셀을 클릭한 후 [확인] 단추를 클릭한다.

9. [필터] 워크시트에 16개의 레코드가 필터링된다.

10. Access 데이터베이스 ‘상품.accdb’를 사용하여 [재고] 워크시트의 B2 셀에 피벗 테이블을 만드시오. 적립포인트를 열 레이블로, 색상을 행 레이블로, 재고량은 값으로 설정하시오. 값 필드의 설정을 최대값으로 변경하고 값 필드의 이름을 ‘최대 재고량’으로 지정하시오.

(실전2-10.xlsx, 상품.accdb) [Hint: 피벗 테이블]

1. [삽입] 탭-[표] 그룹-[피벗 테이블] 명령을 클릭한다.

2. [피벗 테이블 만들기] 대화 상자에서 분석할 데이터를 ‘외부 데이터 원본 사용’ 옵션을 선택한다.

3. [연결 선택] 단추를 클릭한다.

4. [기존 연결] 대화 상자에서 [더 찾아보기] 단추를 클릭한다.

5. [데이터 원본 선택] 대화 상자에서 ‘상품.accdb’ Access 데이터베이스 파일을 찾아 선택하고 [열기] 단추를 클릭한다.

6. [피벗 테이블 만들기] 대화 상자에서 피벗 테이블 보고서를 넣을 위치를 ‘기존 워크시트’로 선택한다.

7. [재고] 워크시트의 B2 셀을 클릭하고 [확인] 단추를 클릭한다.

8. [피벗 테이블 필드 목록] 작업창의 [보고서에 추가할 필드 선택]에서 ‘적립포인트’ 필드를 [열 레이블] 위치로 드래그한다.

9. ‘색상’ 필드를 [행 레이블]로 드래그한다.

10. ‘재고량’ 필드를 [값]으로 드래그한다.

11. [피벗 테이블 필드 목록] 작업창의 [값] 영역에서 ‘합계 : 재고량’ 필드의 목록 단추를 클릭하여 [값 필드 설정] 메뉴를 클릭한다.

12. [값 필드 설정] 대화 상자에서 [사용할 함수] 탭에서 ‘최대값’을 선택한 후 [사용자 지정 이름]을 최대 재고량으로 수정한다.

13. [확인] 단추를 클릭한다.

14. [피벗 테이블 필드 목록] 작업창의 [닫기] 아이콘을 클릭한다.

11. 피벗 테이블에서 총합계 행과 업데이트 시 열 자동 맞춤 서식을 제거하시오.

(실전2-11.xlsx) [Hint: 피벗 테이블 옵션]

1. 피벗 테이블 레이아웃의 임의의 셀을 선택한다.

2. [피벗 테이블 도구]-[옵션] 탭-[피벗 테이블] 그룹-[옵션] 명령을 클릭한다.

3. [피벗 테이블 옵션] 대화 상자의 [레이아웃 및 서식] 탭에서 ‘업데이트 시 열 자동 맞춤’ 확인란의 선택을 해제한다.

4. [요약 및 필터] 탭에서 ‘행 총합계 표시’의 확인란을 선택 해제하고 [확인] 단추를 클릭한다.

12. 수수료율 0.25%의 매수금액을 계산하는 매수수수료라는 새 사용자 정의 함수를 만드시오.

(실전2-12.xlsm) [Hint: 사용자 정의 함수]

1. 보안 경고 메시지 표시줄이 나타나면 [옵션] 단추를 클릭한다.

2. [Microsoft Office 보안 옵션] 대화 상자에서 ‘이 콘텐츠 사용’ 옵션을 선택하고 [확인] 단추를 클릭한다.

3. [개발 도구] 탭-[코드] 그룹-[Visual Basic] 명령을 클릭한다.

4. [모듈] 폴더에서 ‘Module1’을 더블 클릭하여 연다.

5. 다음의 코드를 작성한다.

Function 매수수수료(매수금액)

매수수수료 = 매수금액 * 0.0025

End Function

6. VB 편집기 창의 [닫기] 아이콘을 클릭한다.

13. ‘실전2-13.xlsm’ 매크로 사용 통합 문서에서 ‘주식2.xlsx’ 통합 문서로 사용자 정의 함수 ‘보너스’를 복사하시오.

(실전2-13.xlsm, 주식2.xlsx) [Hint: 사용자 정의 함수 모듈 복사]

1. 보안 경고 메시지 표시줄이 나타나면 [옵션] 단추를 클릭한다.

2. [Microsoft Office 보안 옵션] 대화 상자에서 ‘이 콘텐츠 사용’옵션을 선택하고 [확인] 단추를 클릭한다.

3. [개발 도구] 탭-[코드] 그룹-[Visual Basic] 명령을 클릭한다.

4. [모듈] 폴더에서 'Module1'을 더블 클릭하여 연다.

5. ‘Function ~ End Function’ 범위를 선택한다.

6. <Ctrl+C>를 누르거나, 마우스 오른쪽 단추를 눌러 [복사] 메뉴를 선택하거나, 도구 모음에서 [복사] 아이콘을 클릭하여 복사한다.

7. ‘주식2.xlsx’ 통합 문서의 [모듈] 폴더에서‘Module1’을 더블 클릭하여 연다.

8. <Ctrl+V>를 누르거나, 선택 영역 위에서 마우스 오른쪽 단추를 눌러 [붙여넣기] 메뉴를 클릭하거나, 도구 모음의 [붙여넣기] 아이콘을 클릭하여 붙여 넣는다.

9. VB 편집기 창의 [닫기] 아이콘을 클릭한다.

14. 변경 셀인 E4 셀 및 영어 F4 셀은 35%, 상식 G4 셀은 30%인 ‘가중치’라는 시나리오를 만드시오. G22 결과 셀에 대한 시나리오 요약 보고서를 만드시오.

(실전2-14.xlsx) [Hint: 시나리오]

1. [데이터] 탭-[데이터 도구] 그룹-[가상 분석]-[시나리오 관리자] 명령을 클릭한다.

2. [시나리오 관리자] 대화 상자에서 [추가] 단추를 클릭한다.

3. [시나리오 추가] 대화 상자의 [시나리오 이름]에 가중치를 입력한다.

4. [변경 셀]에 커서를 이동하고 E4:G4 셀 범위를 드래그하고 [확인] 단추를 클릭한다.

5. [시나리오 값] 대화 상자의 E4 셀에 0.35를 입력한다.

6. F4 셀에 0.35를 입력한다.

7. G4 셀에 0.3을 입력하고 [확인] 단추를 클릭한다.

8. [시나리오 관리자] 대화 상자의 [요약] 단추를 클릭한다.

9. [시나리오 요약] 대화 상자에서 ‘시나리오 요약’ 옵션을 선택한다.

10. [결과 셀]에 커서를 이동한 후 G22 셀을 클릭한 후 [확인] 단추를 클릭한다.

11. [시나리오 요약] 워크시트가 삽입되고 요약 보고서가 나타난다.

15. F22 셀이 직접 및 간접 참조되는 모든 셀을 화살표로 표시하시오.

(실전2-15.xlsx) [Hint: 참조되는 셀 추적]

1. F22 셀을 선택하고 [수식] 탭-[수식 분석] 그룹-[참조되는 셀 추적] 명령을 두 번 클릭한다.

16. ‘컴퓨터’와 ‘조정점수’ 이름의 범위를 참조하고 컴퓨터 점수가 70보다 크거나 같고, 조정점수가 80보다 큰 셀의 개수를 세는 수식을 I4 셀에 구하시오.

(실전2-16.xlsx) [Hint: COUNTIFS 함수]

1. I4 셀을 선택한다.

2. [수식] 탭-[함수 라이브러리] 그룹-[함수 추가]-[통계] 명령을 클릭하여 ‘COUNTIFS’ 함수를 선택한다.

3. [Criteria_range1]은 컴퓨터, [Criteria1]은 “>=70”, [Criteria_range2]는 조정점수, [Criteria2]는 “>80”을 입력하고 [확인] 단추를 클릭한다.

※ 수식=COUNTIFS(컴퓨터, “>=70”, 조정점수, “>80”)을 직접 작성해도 된다.

17. 현재 통합 문서의 값을 모두 새로 고침하고 ‘상반기(완성).odc’에 대한 링크를 모두 끊으시오.

(실전2-17.xlsx) [Hint: 모두 새로 고침, 연결 끊기]

1. 보안 경고 메시지 표시줄이 나타나면 [옵션] 단추를 클릭한다.

2. [Microsoft Office 보안 옵션] 대화 상자에서 ‘이 콘텐츠 사용’ 옵션을 선택하고 [확인] 단추를 클릭한다.

3. 표 내부의 임의의 셀을 클릭한다.

4. [표 도구]-[디자인] 탭-[외부 표 데이터] 그룹-[새로 고침]-[모두 새로 고침] 명령을 클릭한다.

5. [표 도구]-[디자인] 탭-[외부 표 데이터] 그룹-[링크 끊기] 명령을 클릭한다.

6. 제거 확인 메시지 창이 나타나면 [확인] 단추를 클릭한다.

18. K4:K6 셀 범위를 참조하는 ‘지역코드’라는 이름의 정의된 범위를 만드시오.

(실전2-18.xlsx) [Hint: 이름 정의]

1. K4:K6 셀 범위를 선택한다.

2. [이름 상자]를 클릭하고 지역코드를 입력한 후 <Enter> 키를 누른다.

19. 지역코드 이름 목록에서 하나의 값만 입력받도록 D3:D22 셀 범위를 수정하시오.

(실전2-19.xlsx) [Hint: 데이터 유효성 검사]

1. D3:D22 셀 범위를 드래그하여 선택한다.

2. [데이터] 탭-[데이터 도구] 그룹-[데이터 유효성 검사] 명령을 클릭한다.

3. [데이터 유효성] 대화 상자의 [설정] 탭으로 이동한다.

4. [제한 대상]은 ‘목록’, [원본]에 =지역코드 를 입력하고 [확인] 단추를 클릭한다.

20. 사용자가 매입수량 열만 선택하고 데이터를 입력할 수 있도록 시트를 보호하시오. 암호는 GG32를 사용하시오. (실전2-20.xlsx) [Hint: 셀 잠금 해제, 시트 보호]

1. E5:E13 셀 범위를 선택한다.

2. [홈] 탭-[셀] 그룹-[서식]-[셀 잠금] 명령을 클릭하여 셀 잠금을 해제한다.

3. [홈] 탭-[셀] 그룹-[서식]-[시트 보호] 명령을 클릭한다.

4. [시트 보호] 대화 상자의 [시트 보호 해제 암호]에 암호 GG32를 입력한다.

5. [워크시트에서 허용할 내용]에서 ‘잠긴 셀 선택’ 확인란의 선택을 해제한다.

6. ‘잠기기 않은 셀 선택’만 선택된 상태에서 [확인] 단추를 클릭한다.

7. [암호 확인] 대화 상자가 나타나면 다시 한 번 GG32를 입력하고 [확인] 단추를 클릭한다.

8. 매입수량 셀의 선택 및 편집이 가능하고 이외의 셀은 셀 선택조차 안 된다.

21. [차트분석] 워크시트의 년도별 이용자 수 분석 차트에 [이용자수] 워크시트의 C8:F8 셀 범위에 있는 값을 사용하는 2차 데이터 계열을 추가하시오. 다른 기본 설정은 모두 그대로 적용하시오.

(실전2-21.xlsx) [Hint: 데이터 계열 추가]

1. [차트분석] 워크시트에서 차트 영역을 선택한다.

2. [차트 도구]-[디자인] 탭-[데이터] 그룹-[데이터 선택] 명령을 클릭한다.

3. [데이터 원본 선택] 대화 상자에서 [범례 항목(계열)]의 [추가] 단추를 클릭한다.

4. [계열 편집] 대화 상자에서 [계열 값]의 기존 데이터를 삭제한다.

5. [이용자수] 시트 탭을 클릭하여 이동한다.

6. C8:F8 셀 범위를 드래그하여 추가하고 [확인] 단추를 클릭한다.

7. [데이터 원본 선택] 대화 상자에서 [확인] 단추를 클릭한다.

22. 대분류가 원피스이고 원가가 20000을 넘는 모든 상품에 대한 판매가의 평균을 구하는 수식을 I3 셀에 만드시오. (실전2-22.xlsx) [Hint: AVERAGEIFS 함수]

1. I3 셀을 선택한다.

2. [수식] 탭-[함수 라이브러리] 그룹-[함수 추가]-[통계] 명령을 클릭하여 ‘AVERAGEIFS’ 함수를 클릭한다.

3. [함수 인수] 창이 나타나면 [Average_range]는 G2:G283, [Criteria_range1]은 C2:C283, [Criteria1]은 “원피스”, [Criteria_range2]는 F2:F283, [Criteria2]는 “>20000”을 입력하고 [확인] 단추를 클릭한다.

※ 수식 =AVERAGEIFS(G2:G283,C2:C283,“원피스”,F2:F283,“>20000”)을 직접 작성해도 된다.

23. 새 워크시트에 3차원 원형 피벗 차트를 다음과 같이 만드시오. 월 및 사이즈는 보고서 필터로, 대분류는 축 필드로 판매가는 값으로 설정하시오. 차트에 차트 레이아웃4를, 차트 스타일은 스타일 28을 적용하고, 월이 1월이 표시되도록 차트를 필터링하시오.

(실전2-23.xlsx) [Hint: 피벗 차트, 차트 종류 변경, 차트 레이아웃, 차트 스타일, 필터]

1. [판매] 워크시트에서 데이터베이스의 임의의 셀을 선택한다.

2. [삽입] 탭-[표] 그룹-[피벗 테이블]-[피벗 차트] 명령을 클릭한다.

3. [피벗 테이블 및 피벗 차트 만들기] 대화 상자에서 분석할 데이터를 ‘표 또는 범위 선택’ 옵션을 선택한다.

4. 피벗 테이블 보고서를 넣을 위치를 ‘새 워크시트’로 두고 [확인] 단추를 클릭한다.

5. [피벗 테이블 필드 목록] 작업창의 [보고서에 추가할 필드 선택]에서 ‘월’ 필드를 [보고서 필터] 위치로 드래그한다.

6. ‘사이즈’ 필드도 [보고서 필터] 위치로 드래그한다.

7. ‘대분류’ 필드를 [축 필드(항목)]으로 드래그한다.

8. ‘판매가’ 필드를 [값]으로 드래그하여 피벗 차트 레이아웃을 구성한다.

9. 피벗 차트를 선택하고 [피벗 차트 도구]-[디자인] 탭-[종류] 그룹에서 [차트 종류 변경] 명령을 클릭한다.

10. [차트 종류 변경] 대화 상자에서 ‘원형’의 ‘3차원 원형’을 선택하고 [확인] 단추를 클릭한다.

11. [피벗 차트 도구]-[디자인] 탭-[차트 레이아웃] 그룹-[자세히] 명령을 클릭하여 ‘레이아웃 4’를 선택한다.

12. [피벗 차트 도구]-[디자인] 탭-[차트 스타일] 그룹-[자세히] 명령을 클릭하여 ‘스타일 28’을 선택한다.

13. [피벗 차트 필터 창]의 [보고서 필터]에서 ‘월’ 필터 목록 단추를 클릭하여 ‘1월’을 선택하고 [확인] 단추를 클릭한다.

14. [피벗 테이블 필드 목록] 작업창의 [닫기] 아이콘을 클릭한다.

24. ‘완료여부’ 텍스트를 표시하는 레이블을 B2 셀에 추가하고, ‘완료’ 텍스트를 표시하는 확인란 컨트롤을 B3 셀에 추가하시오. 셀이 변경될 때 레이블이 이동하거나 크기가 바뀌지 않도록 메모 레이블 서식을 변경하시오.

(실전2-24.xlsx) [Hint: 양식 컨트롤, 컨트롤 서식]

1. [개발 도구] 탭-[컨트롤] 그룹-[삽입]에서 [레이블] 양식 컨트롤( )을 클릭한다.

2. B2 셀 위에서 드래그하여 그린다.

3. 레이블의 텍스트를 완료여부로 수정한다.

4. [개발 도구] 탭-[컨트롤] 그룹-[삽입]에서 [확인란] 양식 컨트롤( )을 클릭한다.

5. B3 셀 위에서 드래그하여 그린다.

6. 확인란 텍스트를 완료로 수정한다.

7.‘ 메모’레이블위에서마우스오른쪽단추를눌러[컨트롤 서식] 메뉴를클릭한다.

8. [컨트롤 서식] 대화 상자의 [속성] 탭에서‘변하지 않음’을 선택하고 [확인] 단추를 클릭한다.

25. 재고량(I2:I16) 셀 범위에 있는 데이터 중에서 100과 200 사이에 없는 데이터에 동그라미를 표시하시오. 다른 기본 설정은 모두 그대로 적용하시오.

(실전2-25.xlsx) [Hint: 데이터 유효성 검사, 잘못된 데이터]

1. I2:I16 셀 범위를 드래그하여 선택한다.

2. [데이터] 탭-[데이터 도구] 그룹-[데이터 유효성 검사] 명령을 클릭한다.

3. [데이터 유효성] 대화 상자의 [설정] 탭으로 이동한다.

4. [제한 대상]은 ‘정수’, [제한 방법]은 ‘해당 범위’, [최소값]은 100, [최대값]은 200을 입력하고 [확인] 단추를 클릭한다.

5. [데이터] 탭-[데이터 도구] 그룹-[데이터 유효성 검사]-[잘못된 데이터]를 클릭한다.

6. 유효성 규칙에 어긋나는 데이터에만 빨강색의 동그라미가 표시된다.

26. 지역코드에 대한 지역을 반환하는 수식을 E3 셀에 만드시오. E3 셀의 수식을 E22 셀까지 복사하시오.

(실전2-26.xlsx) [Hint: VLOOKUP 함수]

1. E3 셀을 선택한다.

2. [수식] 탭-[함수 라이브러리] 그룹-[찾기/참조 영역] 명령을 클릭하여 ‘VLOOKUP’ 함수를 클릭한다.

3. [함수 인수] 창이 나타나면 [Lookup_value]는 D3, [Table_array]는 $K$4:$L$6, [Col_index_num]은 2, [Range_lookup]은 FALSE를 입력하고 [확인] 단추를 클릭한다.

4. E3 셀의 채우기 핸들을 더블 클릭하여 수식을 복사한다.

※ 수식 =VLOOKUP(B3,$L$4:$L$6,2,FALSE)를 직접 작성해도 된다.

27. 스키마 맵을 ‘주소록’이라는 이름을 지정하고, 가져오기 및 내보내기에 대한 스키마 데이터 유효성을 모두 검사하도록 설정하시오. (실전2-27.xlsx) [Hint: XML 맵 속성]

1. XML 데이터 목록에 셀 포인터를 이동한다.

2. [개발 도구] 탭-[XML 그룹]에서 [맵 속성] 명령이나, 마우스 오른쪽 단추를 클릭하여 [XML]-[XML 맵 속성] 메뉴를 클릭한다.

3. [XML 맵 속성] 대화 상자에서 [이름]에 주소록을 입력한다.

4. ‘가져오기 및 내보내기에 대한 스키마 데이터 유효성 검사’의 확인란을 선택한 후 [확인] 단추를 클릭한다.

28. B4 셀에 적용된 서식과 같은 서식을 찾아서 K2 셀에 적용된 서식으로 모두 교체하시오.

(실전2-28.xlsx) [Hint: 바꾸기]

1. [홈] 탭-[편집] 그룹-[찾기 및 선택]에서 [바꾸기] 명령을 클릭한다.

2. [찾기 및 바꾸기] 대화 상자의 [바꾸기] 탭에서 [옵션] 단추를 클릭한다.

3. [찾을 내용]의 [서식] 목록 단추를 클릭하여 ‘셀에서 서식 선택’을 클릭한다.

4. 마우스 모양이 변경되면 찾을 서식이 있는 B4 셀을 클릭한다.

5. [찾기 및 바꾸기] 대화 상자에서 [바꿀 내용]의 [서식] 목록 단추를 클릭하여 ‘셀에서 서식 선택’을 클릭한다.

6. 마우스 모양이 변경되면 바꿀 서식이 있는 K2 셀을 클릭한다.

7. [찾기 및 바꾸기] 대화 상자에서 [모두 바꾸기] 단추를 클릭한다.

8. 11개의 항목이 바뀌었다.

9. [찾기 및 바꾸기] 대화 상자에서 [닫기] 단추를 클릭한다.

29. 대분류 D1:D283 범위에서 동일한 레코드를 J1 셀로 필터링하시오.

(실전2-29.xlsx) [Hint: 고급 필터]

1. [데이터] 탭-[정렬 및 필터] 그룹에서 [고급] 명령을 클릭한다.

2. [고급 필터] 대화 상자의 [결과]를 ‘다른 장소에 복사’ 옵션을 선택한다.

3. [목록 범위]의 기존 셀 주소를 삭제한다.

4. D1 셀을 선택하고 <Ctrl+Shift+↓> 키를 누른다.

5. 대분류 범위가 모두 선택된다.

6. [조건 범위]는 비어 있는 상태로 둔다.

7. [복사 위치]로 커서를 이동하고 J1 셀을 클릭한다.

8. ‘동일한 레코드는 하나만’ 확인란을 선택하고 [확인] 단추를 클릭한다.

30. 사이즈의 모든 보고서 필터 페이지를 별도의 워크시트에 표시하시오.

(실전2-30.xlsx) [Hint: 피벗 테이블 옵션-보고서 필터 페이지 표시]

1. 피벗 테이블 레이아웃의 임의의 셀을 클릭한다.

2. [피벗 테이블 도구]-[옵션] 탭-[피벗 테이블] 그룹-[옵션]-[보고서 필터 페이지 표시]를 선택한다.

3. [보고서 필터 페이지 표시] 대화 상자에서 ‘사이즈’ 보고서 필드를 선택하고 [확인] 단추를 클릭한다.

4. [44] 및 [55] 보고서 필터 페이지 워크시트가 생성된다.

31. E4:F11 셀 범위에 변수 한 개의 데이터 표를 만드시오. 열 입력 셀을 C6 셀로 설정하시오.

(실전2-31.xlsx) [Hint: 데이터 표]

1. 데이터 표를 적용할 E4:F11 셀 범위를 선택한다.

2. [데이터] 탭-[데이터 도구] 그룹-[가상 분석]-[데이터 표] 명령을 클릭한다.

3. [데이터 표] 대화 상자에서 [행 입력 셀]은 비워둔다.

4. [열 입력 셀]은 C6 셀을 선택하여 입력한 후 [확인] 단추를 클릭한다.

32. 누적이용횟수가 20 이상이면 A를 반환하고 10이상이면 B를 반환하고, 아니면 C 회원등급이 표시되도록 I4 셀에 수식을 계산하시오. I4셀의 수식을 I33셀까지 복사하시오.

(실전2-32.xlsx) [Hint: 중첩 IF]

1. I4 셀을 선택한다.

2. [수식] 탭-[함수 라이브러리] 그룹-[논리] 명령을 클릭하여 ‘IF’ 함수를 클릭한다.

3. [함수 인수] 창이 나타나면 [Logical_test] 인수에 H4>=20을, [Value_if_true]는 “A”를 입력한다.

4. [Value_if_false]에 커서를 이동하고 이름 상자의 ‘IF’를 클릭한다.

5. 두 번째 중첩 IF [함수 인수] 창이 나타나면 [Logical_test] 인수에 H4>=10을, [Value_if_true]는 “B”를, [Value_if_false]에 “C”를 입력하고 [확인] 단추를 클릭한다.

6. I4 셀의 채우기 핸들을 더블 클릭하여 수식을 복사한다.

※ 수식 =IF(H4>=20,“A”,IF(H4>=10,“B”,“C”))를 직접 작성해도 된다.

33. 열이 500개씩 그룹으로 묶고 0에서 시작해서 3500항목에서 끝나도록 피벗 테이블을 수정하시오.

(실전2-33.xlsx) [Hint: 그룹 필드]

1. 보안 경고 메시지 표시줄이 나타나면 [옵션] 단추를 클릭한다.

2. [Microsoft Office 보안 옵션] 대화 상자에서 ‘이 콘텐츠 사용’ 옵션을 선택하고 [확인] 단추를 클릭한다.

3. 피벗 테이블의 열 레이블의 값이 있는 임의의 셀을 선택한다.

4. [피벗 테이블 도구]-[옵션] 탭-[그룹] 그룹-[그룹 필드] 명령이나, 선택한 셀 위에서 마우스 오른쪽 단추를 눌러 [그룹] 메뉴를 클릭한다.

5. [그룹화] 대화 상자에서 [시작]은 0, [끝]은 3500, [단위]는 500으로 설정하고 [확인] 단추를 클릭한다.

34. ‘직원목록.dqy’ 쿼리를 사용하여 ‘직위’ 매개 변수 값을 적용하도록 수정하시오. 쿼리를 저장하고 직위가 대리에 대한 데이터를 B2셀로 가져오시오.

(실전2-34.xlsx, 직원목록.dqy) [Hint: 외부 데이터 쿼리로 가져오기]

1. [데이터] 탭-[외부 데이터 가져오기] 그룹-[기타 원본]-[Microsoft Query] 명령을 클릭한다.

2. [데이터 원본 선택] 대화 상자의 [쿼리] 탭으로 이동한 후 ‘쿼리를 만들거나 편집할 때 쿼리 마법사 사용’ 확인란의 선택을 해제하고 [찾아보기] 단추를 클릭한다.

3. [데이터 원본 찾아보기] 대화 상자에서 ‘직원목록.dqy’ 파일을 찾아 선택하고 [열기] 단추를 클릭한다.

4. [데이터 원본 선택] 대화 상자의 [쿼리] 탭에 ‘직원목록’ 쿼리 파일이 나타나면 [열기] 단추를 클릭한다.

5. [Microsoft Query] 프로그램에서 [보기]-[조건] 메뉴나 도구 모음의 [조건 표시/숨기기] 아이콘을 클릭한다.

6. 조건 창의 [조건 필드]에 ‘직위’를 선택하고 [값]에 [대리]를 입력하고 <Enter> 키를 누른다.

7. 직위가 대리인 레코드가 표시되면 [닫기] 아이콘을 클릭한다.

8. [데이터 가져오기] 대화 상자에서 ‘기존 워크시트’를 선택하고 B2 셀을 클릭하여 지정하고 [확인] 단추를 클릭한다.

35. 기본 매크로 보안 수준을 모든 매크로 포함(위험성 있는 코드가 실행될 수 있으므로 권장하지 않음)으로 변경하시오.

(파일 없음) [Hint: 매크로 보안]

1. [개발 도구] 탭-[코드] 그룹-[매크로 보안] 명령을 클릭한다.

2. [보안 센터] 대화 상자의 [매크로 설정] 항목에서 신뢰할 수 없는 위치에 있는 문서에서 ‘모든 매크로 포함(위험성 있는 코드가 실행될 수 있으므로 권장하지 않음)’ 옵션을 설정하고 [확인] 단추를 클릭한다.

실전모의고사 3회

01. [매출] 워크시트의 B2:G66 영역을 사용하여 [분석] 워크시트의 A1 셀에 피벗 차트를 만드시오. 사원명 필드를 축 필드로, 날짜를 범례 필드(계열)로, 수량을 값으로, 제품을 보고서 필터로 지정하고 제품을 필로 믹스로 필터하시오. (실전3-01.xlsx) [Hint: 피벗 차트]

1. [매출] 워크시트의 데이터베이스 임의의 셀을 선택한다.

2. [삽입] 탭-[표] 그룹-[피벗 테이블]-[피벗 차트] 명령을 클릭한다.

3. [피벗 테이블 및 피벗 차트 만들기] 대화 상자에서 분석할 데이터를 ‘표 또는 범위 선택’ 옵션인지 확인한다.

4. 피벗 테이블 보고서를 넣을 위치를 ‘기존 워크시트’로 선택하고, [위치]에 커서를 이동한 후 [분석] 시트 탭을 클릭한다.

5. [분석] 워크시트로 이동되면 A1 셀을 클릭한다.

6. ‘분석!$A$1’ 참조 위치가 나타나면[확인] 단추를 클릭한다.

7. [피벗 테이블 필드 목록] 작업창의 [보고서에 추가할 필드 선택]에서 ‘사원명’ 필드를 [축 필드(항목)] 위치로 드래그한다.

8. ‘날짜’ 필드를 [범례 필드(계열)]로 드래그한다.

9. ‘수량’ 필드를 [값]으로 드래그한다.

10. ‘제품’ 필드를 [보고서 필터]로 드래그하여 피벗 차트 레이아웃을 구성한다.

11. [피벗 차트 필터] 창에서 [보고서 필터]의 필터 단추를 클릭하여 ‘필로 믹스’를 선택하고 [확인] 단추를 클릭한다.

12. [피벗 테이블 필드 목록] 작업창의 [닫기] 아이콘을 클릭한다.

02. I3셀부터 시작해서 날짜가 1월, 사원명이 박찬식 또는 김지훈, 수량이 50개 이상인 목록을 필터링하는 조건을 만드시오. 필터링된 값을 I7셀에 복사하시오.

(실전3-02.xlsx) [Hint: 고급 필터]

1. I3 셀을 선택하고 다음과 같이 조건을 작성한다.

날짜	사원명	수량
1월	박찬식	>=50
1월	김지훈	>=50

2. 데이터베이스의 임의의 셀을 선택한다.

3. [데이터] 탭-[정렬 및 필터] 그룹-[고급] 명령을 클릭한다.

4. [고급 필터] 대화 상자의 [결과]를 ‘다른 장소에 복사’로 선택한다.

5. [목록 범위]에 데이터베이스 범위가 표시된다.

6. [조건 범위]로 커서를 이동하고 I3:K5 셀 범위를 드래그하여 선택한다.

7. [복사 위치]로 커서를 이동하고 I7 셀을 클릭한 후 [확인] 단추를 클릭한다.

8. [필터] 워크시트에 3개의 레코드가 필터링된다.

03. ‘주문목록.xsd’ 스키마의 맵을 현재 워크시트에 추가하시오. 거래처명, 주문날짜, 배송날짜, 금액 XML 요소를 각 필드에 추가하시오. ‘주문목록.xml’ 데이터 파일을 가져오시오.

(실전3-03.xlsx, 주문목록.xsd, 주문목록.xml) [Hint: XML 스키마, XML 가져오기]

1. [개발 도구] 탭-[XML] 그룹-[원본] 명령을 클릭한다.

2. [XML 원본] 작업창 하단의 [XML 맵] 단추를 클릭한다.

3. [XML 맵] 대화 상자에서 [추가] 단추를 클릭한다.

4. ‘주문목록.xsd’ 파일을 찾아 선택하고 [열기] 단추를 클릭한다.

5. [여러 루트] 대화 상자에서 ‘dataroot’를 선택하고 [확인] 단추를 클릭한다.

6. [XML 맵] 대화 상자에서 맵이 나타나면 [확인] 단추를 클릭한다.

7. [XML 원본] 작업창에서 ‘주문코드’ 필드를 B2 셀로 드래그한다

8. ‘거래처명’ 필드를 C2 셀로 드래그한다

9. ‘주문날짜’ 필드를 D2 셀로 드래그한다.

10. ‘배송날짜’ 필드를 E2 셀로 드래그한다

11. ‘금액’ 필드를 F2 셀로 드래그한다.

12. [개발 도구] 탭-[XML 그룹] 그룹-[가져오기] 명령이나, 마우스 오른쪽 단추를 클릭하여 [XML]-[가져오기] 메뉴를 클릭한다.

13. [XML 가져오기] 대화 상자에서 ‘주문목록.xml’ 데이터 파일을 찾아 선택한 다음 [가져오기] 단추를 클릭한다.

14. XML 데이터 중 매핑된 필드의 데이터가 표시됨을 확인한다.

15. [XML 원본] 작업창에서 [닫기] 아이콘을 클릭한다.

04. XML맵의 이름을 ‘주문목록’으로 설정하고 데이터를 새로 고치거나 가져올 때 기존 XML표에 새 데이터를 추가하도록 지정하시오.

(실전3-04.xlsx) [Hint: XML 옵션]

1. XML 데이터 목록에 셀 포인터를 이동한다.

2. [개발 도구] 탭-[XML 그룹]에서 [맵 속성] 명령이나, 마우스 오른쪽 단추를 클릭하여 [XML]-[XML 맵 속성] 메뉴를 클릭한다.

3. [XML 맵 속성] 대화 상자에서 [이름]에 주문목록을 입력한다.

4. ‘기존 XML 표에 새 데이터 추가’ 옵션을 선택하고 [확인] 단추를 클릭한다.

05. [통합] 워크시트의 C3:D12 셀 범위에 [A지점], [B지점] 및 [C지점] 워크시트의 판매수량과 재고량 데이터에 대한 최소 함수를 적용하여 링크를 만드시오.

(실전3-05.xlsx) [Hint: 데이터 통합]

1. [통합] 워크시트의 C3 셀을 선택한다.

2. [데이터] 탭-[데이터 도구] 그룹-[통합] 명령을 클릭한다.

3. [통합] 대화 상자에서 [함수]를 ‘최소값’으로 선택한다.

4. [참조]에 커서를 이동한 후 [A지점] 시트 탭을 클릭한다.

5. C3:D12 셀 범위를 드래그하여 선택한 후 [추가] 단추를 클릭한다.

6. [B지점] 시트 탭을 클릭하고 C3:D12 셀 범위가 그대로 들어오면 [추가] 단추를 클릭한다.

7. [C지점] 시트 탭을 클릭하고 C3:D12 셀 범위가 그대로 들어오면 [추가] 단추를 클릭한다.

8. [모든 참조 영역]에 참조 주소가 모두 추가되면 '원본 데이터에 연결‘ 확인란을 선택하고 [확인] 단추를 클릭한다.

06. 코드표를 참조하여 작업자코드에 대한 기술등급과 월급여를 반환하는 수식을 E5 셀과 F5 셀에 만드시오. E5셀과 F5셀의 수식을 19행까지 복사하시오.

(실전3-06.xlsx) [Hint: HLOOKUP 함수]

1. E5 셀을 선택한다.

2. [수식] 탭-[함수 라이브러리] 그룹-[찾기/참조 영역] 명령을 클릭하여 ‘HLOOKUP’ 함수를 클릭한다.

3. [함수 인수] 창이 나타나면 [Lookup_value]는 D5, [Table_array]는 $J$4:$L$6, [Row_index_num]은 2, [Range_lookup]은 FALSE를 입력하고 [확인] 단추를 클릭한다.

※ 수식 =HLOOKUP(D5,$J$4:$L$6,2,FALSE)를 직접 작성해도 된다.

4. F5 셀을 선택한다.

5. [수식] 탭-[함수 라이브러리] 그룹-[찾기/참조 영역] 명령을 클릭하여 ‘HLOOKUP’ 함수를 클릭한다.

6. [함수 인수] 창이 나타나면 [Lookup_value]는 D5, [Table_array]는 $J$4:$L$6, [Row_index_num]은 3, [Range_lookup]은 FALSE를 입력하고 [확인] 단추를 클릭한다.

※ 수식 =HLOOKUP(D5,$J$4:$L$6,3,FALSE)를 직접 작성해도 된다.

7. E5:F5 셀 범위를 선택하고 채우기 핸들을 더블 클릭하여 수식을 복사한다.

07. 월급여에서 월급여와 세율이라고 정의된 이름을 곱한 값을 빼는 수식을 G5 셀에 만드시오. 수식을 G19 셀까지 복사하시오.

(실전3-07.xlsx) [Hint: 수식]

1. G5 셀을 선택하고 =F5-F5*세율 수식을 입력한 후 <Enter> 키를 누른다.

2. G5 셀의 채우기 핸들을 G19 셀까지 드래그하여 수식을 복사한다.

08. 선택된 셀에 다음의 셀 서식을 지정하는 ‘확인’이라는 새 매크로를 기록하시오. 이 매크로에 ‘확인이 필요한 데이터에 적용’이라는 설명과 Ctrl+T 바로가기 키를 지정하시오. 그리고 C 열에만 확인 매크로를 적용하시오. 다른 기본 설정은 모두 그대로 적용하시오.

- 글꼴색 : 황록색, 강조3   - 기울임꼴, 가운데 맞춤

(실전3-08.xlsx) [Hint: 매크로 기록]

1. [개발 도구] 탭-[코드] 그룹-[매크로 기록] 명령이나, 상태 표시줄에서 [매크로 기록] 아이콘을 클릭한다.

2. [매크로 기록] 대화 상자에서 [매크로 이름]에 확인을 입력한다.

3. 바로 가기 키에 CTRL+T를 입력한다.

4. [설명]에 확인이 필요한 데이터에 적용을 입력하고 [확인] 단추를 클릭한다.

5. [홈] 탭-[글꼴] 그룹에서 [글꼴 색]을 [테마 색]에서 ‘황록색 강조3’을 선택한다.

6. [홈] 탭-[글꼴] 그룹에서 [기울임꼴] 아이콘을 클릭한다.

7. [홈] 탭-[맞춤] 그룹에서 [가운데 맞춤] 아이콘을 클릭한다.

8. [개발 도구] 탭-[코드] 그룹-[기록 중지] 명령이나, 상태 표시줄의 [기록 중지] 아이콘을 클릭한다.

9. C열 머리글을 선택하고 <Ctrl+T>를 누른다.

09. 순이익 100000을 달성하기 위해 조정해야 할 원가 비율을 표시하시오.

(실전3-09.xlsx) [Hint: 목표값 찾기]

1. 수식이 있는 C10 셀을 선택한다.

2. [데이터] 탭-[데이터 도구] 그룹-[가상 분석]-[목표값 찾기] 명령을 클릭한다.

3. [목표값 찾기] 대화 상자의 [수식 셀]에 C10 셀 주소가 표시된다.

4. [찾는 값]에 100000을 입력한다.

5. [값을 바꿀 셀]에 커서를 이동하고 C2 셀을 클릭하고 [확인] 단추를 클릭한다.

6. 목표값 찾기 결과가 나타나면 [확인] 단추를 클릭한다.

7. 순이익이 100000이 되려면 원가비율이 8%로 조정돼야 한다.

10. C2 셀의 직접 및 간접적으로 참조하는 모든 셀을 화살표로 표시하시오.

(실전3-10.xlsx) [Hint: 참조하는 셀 추적]

1. C2 셀을 선택한다.

2. [수식] 탭-[수식 분석] 그룹-[참조하는 셀 추적] 명령을 두 번 클릭한다.

11. 2011년, 5월, 24일 숫자 값을 날짜로 계산하는 수식을 작성일 C12 셀에 만드시오.

(실전3-11.xlsx) [Hint: DATE 함수]

1. 작성일 C12 셀을 선택하고 [수식] 탭-[함수 라이브러리] 그룹-[날짜 및 시간] 명령에서 ‘DATE’를 클릭한다.

2. [함수 인수] 창에서 [Year]는 2011, [Month]는 5, [Day]는 24를 설정하고 [확인] 단추를 클릭한다.

※ 수식 =DATE(2011,5,24)을 직접 작성해도 된다.

12. [분석] 워크시트에 판매금액의 3%로 계산하는 ‘카드수수료’라는 필드를 피벗 테이블에 삽입하시오.

(실전3-12.xlsx) [Hint: 계산 필드]

1. [분석] 워크시트에서 피벗 테이블 레이아웃의 임의의 셀을 클릭한다.

2. [피벗 테이블 도구]-[옵션] 탭-[도구] 그룹-[수식]-[계산 필드] 명령을 클릭한다.

3. [계산 필드 삽입] 대화 상자에서 [이름]에 카드수수료를 입력한다.

4. [수식]에 기존 내용을 삭제하고 [필드] 목록에서 ‘판매금액’을 선택한 후 [필드 삽입] 단추를 클릭한다.

5. [수식]에 ‘=판매금액’이 입력되면 *3%를 입력한다.

6. [수식]에 ‘=판맥금액*3%’가 완성되면 [추가] 단추를 클릭한다.

7. [계산 필드 삽입] 대화 상자에서 [확인] 단추를 클릭한다.

13. B11 셀부터 F18 셀 범위에 변수 2개의 데이터 표를 만드시오. 행 입력 셀을 C6으로, 열 입력 셀을 C5로 설정하시오.

(실전3-13.xlsx) [Hint: 데이터 표]

1. 데이터 표를 적용할 B11:F18 셀 범위를 선택한다.

2. [데이터] 탭-[데이터 도구] 그룹-[가상 분석]-[데이터 표] 명령을 클릭한다.

3. [데이터 표] 대화 상자에서 [행 입력 셀]은 상환기간인 C6 셀을 클릭한다.

4. [열 입력 셀]은 연이율인 C5 셀을 클릭하고 [확인] 단추를 클릭한다.

14. ‘실전3-14.xlsx’의 C12 셀에 ‘급여현황.xlsx’의 목록 워크시트에 있는 C5:C19 셀의 개수를 계산하시오.

(실전3-14.xlsx, 급여현황.xlsx) [Hint: COUNTA 함수]

1. 실전3-14.xlsx 파일의 C12 셀을 선택한다.

2. =COUNTA(를 입력한다.

3. [보기] 탭-[창] 그룹-[창 전환] 명령을 클릭하여 열려있는 ‘급여현황.xlsx’ 파일을 클릭한다.

4. C5:C19 셀 범위를 드래그하고 )를 입력하다.

5. =COUNTA([급여현황.xlsx]급여!$C$5:$C$19) 수식이 완성되면 <Enter> 키를 누른다.

15. A1 셀에 ‘상품.accdb’ 데이터베이스의 상품목록을 가져오시오. 15분 간격으로 연결을 업데이트하도록 속성을 변경하시오. 내보내기를 ‘신상품’으로 저장하시오. 다른 기본 설정은 모두 그대로 적용하시오.

(실전3-15.xlsx, 상품.accdb) [Hint: Access 외부 데이터 가져오기]

1. [데이터] 탭-[외부 데이터 가져오기] 그룹-[Access] 명령을 클릭한다.

2. [데이터 원본 선택] 대화 상자에서 ‘상품.accdb’ 파일을 찾아 선택하고 [열기] 단추를 클릭한다.

3. [데이터 가져오기] 대화 상자에서 데이터를 표가 들어갈 위치는 ‘기존 워크시트’에서 A1 셀을 클릭하여 지정한다.

4. 연결 옵션을 변경하기 위해 [속성] 단추를 클릭한다.

5. [연결 속성] 대화 상자의 [사용 현황] 탭에서 ‘다음 간격으로 새로 고침’ 확인란을 선택하고 ‘15분’을 설정한다.

6. [정의] 탭에서 [연결 파일 내보내기] 단추를 클릭한다.

7. [파일 저장] 대화 상자에서 [파일 이름]에 신상품을 입력하고 [저장] 단추를 클릭한다.

8. [연결 속성] 대화 상자가 나타나면 [확인] 단추를 클릭한다.

9. [데이터 가져오기] 대화 상자가 나타나면 [확인] 단추를 클릭한다.

16. B15:E15 셀 범위에 있는 데이터를 사용하여 2차 Y 보조축을 차트에 추가하시오. 새 Y 보조축에 12월 강수량으로 표시하시오. 차트에 최고/최저값 연결선을 추가하시오.

(실전3-16.xlsx) [Hint: 데이터 계열 추가, 분석 선]

1. 차트를 선택하고 [차트 도구]-[디자인] 탭-[데이터] 그룹-[데이터 선택] 명령을 클릭한다.

2. [데이터 원본 선택] 대화 상자에서 [범례 항목(계열)]의 [추가] 단추를 클릭한다.

3. [계열 편집] 대화 상자에서 [계열 이름]에 12월 강수량을 입력한다.

4. [계열 값]의 기존 데이터를 삭제한 후 B15:E15 셀 범위를 드래그하여 추가하고 [확인] 단추를 클릭한다.

5. [데이터 원본 선택] 대화 상자에서 [확인] 단추를 클릭한다.

6. 새 꺾은선 차트를 선택하고 [차트 도구]-[레이아웃] 탭-[분석] 그룹-[선]에서 ‘최고/최저값 연결선’을 클릭한다.

17. 레이블 매크로를 삭제하시오. (실전3-17.xlsm) [Hint: 매크로 삭제]

1. [보안 경고] 메시지 표시줄에서 [옵션] 단추를 클릭한다.

2. [Microsoft office 보안 옵션] 대화 상자에서 ‘이 콘텐츠 사용’ 옵션을 선택하고 [확인] 단추를 클릭한다.

3. [개발 도구] 탭-[코드] 그룹-[매크로] 명령을 클릭한다.

4. [매크로] 대화 상자에서 ‘레이블’ 매크로를 선택한 후 [삭제] 단추를 클릭한다.

5. 매크로 삭제 확인 메시지 창이 나타나면 [예] 단추를 클릭한다.

18. 수량이 100개 이상이면 금액의 20% 할인을 계산하는 ‘할인금액’이라는 사용자 정의 함수를 만드시오. 그리고 G2:G65 셀 범위에 할인금액을 구하시오.

(실전3-18.xlsm) [Hint: 사용자 정의 함수]

1. [보안 경고] 메시지 표시줄에서 [옵션] 단추를 클릭한다.

2. [Microsoft office 보안 옵션] 대화 상자에서 ‘이 콘텐츠 사용’ 옵션을 선택하고 [확인] 단추를 클릭한다.

3. [개발 도구] 탭-[코드] 그룹-[Visual Basic] 명령을 클릭한다.

4. [모듈] 폴더에서 ‘Module1’을 더블 클릭한다.

5. 모듈 창에 다음과 같이 코드를 작성한다.

Function 할인금액(수량, 금액)

If 수량 >= 100 Then

할인금액 = 금액*0.2

End If

End Function

6. VB 편집기 창의 [닫기] 아이콘을 클릭한다.

7. G2 셀을 선택한다.

8. [수식] 탭-[함수 라이브러리] 그룹-[함수 삽입] 명령을 클릭한다.

9. [함수 마법사] 대화 상자에서 [범주 선택]을 ‘사용자 정의’로 선택한다.

10. [함수 선택] 목록에서 ‘할인금액’을 선택하고 [확인] 단추를 클릭한다.

11. [함수 인수] 창에서 [수량] 인수를 E2 셀로 설정하고, [금액]은 F2 셀로 설정한 후 [확인] 단추를 클릭한다.

12. G2 셀에 수식 결과가 나타나면 채우기 핸들을 더블 클릭하여 수식을 복사한다.

19. G10 셀에 단가와 수량을 곱하는 수식을 작성하되 오류가 있으면 공백으로 표시되도록 수식을 계산하시오. G10 셀의 수식을 G16셀까지 복사하시오.

(실전3-19.xlsx) [Hint: IFERROR 함수]

1. G10 셀을 선택한다.

2. [수식] 탭-[함수 라이브러리] 그룹-[논리] 명령을 클릭하여 ‘IFERROR’ 함수를 클릭한다.

3. [함수 인수] 창이 나타나면 [Value]를 E10*F10을, [Value_if_error]를 “”을 입력하고 [확인] 단추를 클릭한다.

4. G10 셀의 채우기 핸들을 G16 셀까지 드래그하여 수식을 복사한다.

※ 수식 =IFERROR(E10*F10,“”)을 직접 작성해도 된다.

20. 파일이 열릴 때 새로 고침 되고 백그라운드도 새로 고침 되도록 재고 표를 수정하시오. 다른 기본 설정은 모두 그대로 적용하시오.

(실전3-20.xlsx) [Hint: 연결 속성]

1. [보안 경고] 메시지 표시줄에서 [옵션] 단추를 클릭한다.

2. [Microsoft office 보안 옵션] 대화 상자에서 ‘이 콘텐츠 사용’ 옵션을 선택하고 [확인] 단추를 클릭한다.

3. [데이터] 탭-[연결] 그룹-[속성] 명령을 클릭한다.

4. [연결 속성] 대화 상자의 [사용 현황] 탭에서 ‘다른 작업하면서 새로 고침’ 확인란을 선택한다.

5. ‘파일을 열 때 데이터 새로 고침’ 확인란을 선택하고 [확인] 단추를 클릭한다.

21. G4:G21 셀 범위에 있는 데이터 중에서 0.5 와 1 사이에 없는 데이터에 동그라미를 표시하시오. 다른 기본 설정은 모두 그대로 적용하시오.

(실전3-21.xlsx) [Hint: 데이터 유효성 검사]

1. G4:G12 셀 범위를 드래그하여 선택한다.

2. [데이터] 탭-[데이터 도구] 그룹-[데이터 유효성 검사] 명령을 클릭한다.

3. [데이터 유효성] 대화 상자의 [설정] 탭으로 이동한다.

4. [제한 대상]은 ‘소수점’, [제한 방법]은 ‘해당 범위’, [최소값]은 0.5, [최대값]은 1을 입력하고 [확인] 단추를 클릭한다.

5. [데이터] 탭-[데이터 도구] 그룹-[데이터 유효성 검사] 명령의 화살표를 클릭하여 [잘못된 데이터]를 클릭한다.

6. 유효성 규칙에 어긋나는 데이터에만 빨강색의 동그라미가 표시된다.

22. [지역목록] 워크시트의 ‘지역’이라는 이름이 지정된 범위의 4개의 항목을 모두 표시하는 콤보 상자(양식컨트롤)를 I1 셀에 추가하시오. 그리고 콤보상자를 K1 셀에 연결하시오.

(실전3-22.xlsx) [Hint: 양식 컨트롤]

1. [개발 도구] 탭-[컨트롤] 그룹-[삽입]에서 [콤보 상자] 양식 컨트롤을 클릭한다.

2. I1 셀 위에서 드래그하여 그린다.

3. 콤보 상자가 선택되어 있는 상태에서 [개발 도구] 탭-[컨트롤] 그룹-[속성] 명령을 클릭한다.

4. [컨트롤 서식] 대화 상자의 [컨트롤] 탭에서 [입력 범위]에 지역을 입력한다.

5. [셀 연결]에 커서를 이동한 후 K1 셀을 클릭하고 [확인] 단추를 클릭한다.

23. 수량의 합계를 행 비율 값 표시 형식으로 표시하시오.

(실전3-23.xlsx) [Hint: 값 필드 설정]

1. [피벗 테이블 필드 목록] 창에서 [값]의 ‘합계 : 수량’ 목록 단추를 클릭해 [값 필드 설정] 메뉴를 클릭한다.

2. [값 필드 설정] 대화 상자의 [값 표시 형식] 탭에서 [값 표시 형식]을 ‘행 방향의 비율’로 선택하고 [확인] 단추를 클릭한다.

3. [피벗 테이블 필드 목록] 창의 [닫기] 아이콘을 클릭한다.

24. B3:E3 셀 범위에서 I2 셀의 지역이 있는 위치를 K1 셀에 구하시오.

(실전3-24.xlsx) [Hint: MATCH 함수]

1. K1 셀을 선택한다.

2. [수식] 탭-[함수 라이브러리] 그룹-[찾기/참조 영역] 명령을 클릭하여 ‘MATCH’ 함수를 클릭한다.

3. [함수 인수] 창에서 [Lookup_value]는 I1, [Lookup_array]는 B3:E3, [Match_type]은 정확한 값 0으로 설정하고 [확인] 단추를 클릭한다.

※ 수식 =MATCH(I1,B3:E3,0)을 직접 작성해도 된다.

25. ‘실전3-25.xlsm’ 매크로 사용 통합 문서에서 ‘내역.xlsm’ 통합 문서로 ‘확인’ 매크로를 복사하시오.

(실전3-25.xlsm, 내역.xlsm) [Hint: 매크로 복사]

1. 실전3-25.xlsm과 내역.xlsm 두 문서를 모두 열고 [보안 경고] 메시지 표시줄에서 [옵션] 단추를 클릭한다.

2. [Microsoft office 보안 옵션] 대화 상자에서 ‘이 콘텐츠 사용’옵션을 선택하고 [확인] 단추를 클릭한다.

3. 실전3-25.xlsm 매크로 사용 통합 문서에서 [개발 도구] 탭-[코드] 그룹-[매크로] 명령을 클릭한다.

4. [매크로] 대화 상자에서 ‘확인’ 매크로를 선택한 후 [편집] 단추를 클릭한다.

5. ‘Sub 확인() ~ End Sub’ 범위를 선택한 후 <Ctrl+C>를 누르거나, 마우스 오른쪽 단추를 눌러 [복사] 메뉴를 선택하거나, 도구 모음에서 [복사] 아이콘을 클릭하여 복사한다.

6. ‘내역.xlsm’ 통합 문서의[모듈] 폴더에서 ‘Module1’을 더블 클릭하여 연다.

7. <Ctrl+V>를 누르거나, 선택 영역 위에서 마우스 오른쪽 단추를 눌러 [붙여넣기] 메뉴를 클릭하거나, 도구 모음의 [붙여넣기] 아이콘을 클릭하여 붙여 넣는다.

8. VB 편집기 창의 [닫기] 아이콘을 클릭한다.

26. B5 셀에 적용된 서식과 동일한 서식을 모두 찾아서 C5 셀에 적용되어 있는 서식으로 바꾸시오.

(실전3-26.xlsx) [Hint: 바꾸기]

1. [홈] 탭-[편집] 그룹-[찾기 및 선택]에서 [바꾸기] 명령을 클릭한다.

2. [찾기 및 바꾸기] 대화 상자의 [바꾸기] 탭에서 [옵션] 단추를 클릭한다.

3. [찾을 내용]의 [서식] 목록 단추를 클릭하여 ‘셀에서 서식 선택’을 클릭한다.

4. 마우스 모양이 변경되면 찾을 서식이 있는 B5 셀을 클릭한다.

5. [찾기 및 바꾸기] 대화 상자에서 [바꿀 내용]의 [서식] 목록 단추를 클릭하여 ‘셀에서 서식 선택’을 클릭한다.

6. 마우스 모양이 변경되면 바꿀 서식이 있는 C5 셀을 클릭한다.

7. [찾기 및 바꾸기] 대화 상자에서 [모두 바꾸기] 단추를 클릭한다.

8. 11개의 항목이 바뀌었다는 메시지 창이 나타나면 [확인] 단추를 클릭한다.

9. [찾기 및 바꾸기] 대화 상자에서 [닫기] 단추를 클릭한다.

27. 사용자가 양식 컨트롤만 편집할 수 있도록 [할일목록] 워크시트를 보호하시오. 암호는 123을 사용하시오.

(실전3-27.xlsx) [Hint: 시트 보호]

1. [홈] 탭-[셀] 그룹-[서식]-[시트 보호] 명령을 클릭한다.

2. [시트 보호] 대화 상자의 [시트 보호 해제 암호]에 암호 123을 입력한다.

3. [워크시트에서 허용할 내용]에서 ‘잠기지 않은 셀 선택’ 확인란의 선택을 해제한다.

4. ‘개체 편집’ 확인란이 선택된 상태에서 [확인] 단추를 클릭한다.

5. [암호 확인] 대화 상자가 나타나면 다시 한 번 123을 입력하고 [확인] 단추를 클릭한다.

6. 양식 컨트롤을 비롯한 개체 선택 및 편집만 되며 그 밖의 셀은 셀 선택조차 안 된다.

28. 쉼표 구분 기호로 분리된 ‘승진.txt’ 파일에서 다음의 매개변수를 사용하여 A1 셀로 가져오시오. 네 번째 열은 가져오지 말고 연속되는 쉼표 구분 기호는 하나로 처리하시오. 쿼리를 ‘승진최종’으로 저장하고 파일을 열 때 데이터를 새로 고치도록 설정하시오.

(실전3-28.xlsx, 승진.txt) [Hint: 외부 데이터 가져오기]

1. [데이터] 탭-[외부 데이터 가져오기] 그룹-[텍스트] 명령을 클릭한다.

2. [텍스트 파일 가져오기] 대화 상자에서 ‘승진.txt’ 파일을 찾아 선택하고 [가져오기] 단추를 클릭한다.

3. [텍스트 마법사] 3단계 중 1단계가 나타나면 ‘구분 기호로 분리됨’을 선택하고 [다음] 단추를 클릭한다.

4. [텍스트 마법사] 2단계에서 구분 기호를 ‘쉼표’ 확인란을 선택한다.

5. ‘연속된 구분 기호를 하나로 처리’ 확인란을 선택하고 [다음] 단추를 클릭한다.

6. [텍스트 마법사] 3단계에서 [데이터 미리 보기]에서 네 번째 ‘성별’ 열을 클릭하고 ‘열 가져오지 않음(건너뜀)’ 옵션을 선택한다.

7. [마침] 단추를 클릭한다.

8. [데이터 가져오기] 대화 상자에서 ‘기존 워크시트’ 옵션이 선택된 상태에서 A1 셀을 클릭하여 지정한다.

9. [속성] 단추를 클릭한다.

10. [외부 데이터 범위 속성] 대화 상자에서 [이름]을 승진최종으로 입력한다.

11. [쿼리 정의]의 ‘쿼리 정의 저장’ 확인란이 선택되어 있는지 확인한다.

12. ‘파일을 열 때 데이터 새로 고침’ 확인란을 선택하고 [확인] 단추를 클릭한다.

13. 다시 [데이터 가져오기] 대화 상자가 나타나면 [확인] 단추를 클릭한다.

29. ‘확인’ 매크로에서 기울임꼴을 굵은 서식으로 대체하시오. 그리고 G 열에 확인 매크로를 실행하시오.

(실전3-29.xlsm) [Hint: 매크로 편집]

1. [보안 경고] 메시지 표시줄에서 [옵션] 단추를 클릭한다.

2. [Microsoft office 보안 옵션] 대화 상자에서 ‘이 콘텐츠 사용’ 옵션을 선택하고 [확인] 단추를 클릭한다.

3. [개발 도구] 탭-[코드] 그룹에서 [매크로] 명령을 클릭한다.

4. [매크로] 대화 상자에서 ‘확인’ 매크로를 선택하고 [편집] 단추를 클릭한다.

5. VB 편집기 창에서 [모듈] 폴더의 ‘Module1’을 더블 클릭하여 코드 창을 연다.

6. ‘확인’ 매크로에서 다음과 같이 코드를 수정한다.

Selection.Font.Italic = True → Selection.Font.Bold = True

7. VB 편집기 창의 [닫기] 아이콘을 클릭한다.

8. G열 머리글을 클릭해 선택한다.

9. [개발 도구] 탭-[코드] 그룹에서 [매크로] 명령을 클릭한다.

10. [매크로] 대화 상자에서 ‘확인’ 매크로를 선택하고 [실행] 단추를 클릭한다.

30. 제품명 이름 목록에서 하나의 값만 받도록 C10:C16 셀 범위를 수정하시오.

(실전3-30.xlsx) [Hint: 데이터 유효성 검사]

1. C10:C16 셀 범위를 드래그하여 선택한다.

2. [데이터] 탭-[데이터 도구] 그룹-[데이터 유효성 검사] 명령을 클릭한다.

3. [데이터 유효성] 대화 상자의 [설정] 탭으로 이동한다.

4. [제한 대상]은 ‘목록’, [원본]에 =제품명 을 입력하고 [확인] 단추를 클릭한다.

31. 다음의 매개변수를 사용하여 Access 데이터베이스 ‘예약.accdb’ 파일의 예약리스트 테이블을 A1 셀로 가져오시오.

- 회원ID, 여권번호, 출발날짜, 출발공항, 도착공항, 편명 열을 가져오시오.

- 편명이 KE 711 또는 KE 715 인 레코드를 가져오시오.

- 1차적으로 편명을 오름차순으로 정렬하고, 2차적으로 출발날짜를 내림차순으로 정렬하시오.

- 30분 간격으로 자동으로 데이터 연결을 새로 고침 되도록 속성과 ‘12월 둘째 주’ 연결 이름을 설정하시오.

(실전3-31.xlsx, 예약.accdb) [Hint: ACCESS 외부 데이터 가져오기]

1. [데이터] 탭-[외부 데이터 가져오기] 그룹-[기타 원본]-[Microsoft Query] 명령을 클릭한다.

2. [데이터 원본 선택] 대화 상자의 [데이터베이스] 탭에서 ‘MS Access Database’를 선택하고 ‘쿼리를 만들거나 편집할 때 쿼리 마법사 사용’이 체크되어 있는지 확인한 후 [확인] 단추를 클릭한다.

3. [데이터베이스를 선택] 대화 상자에서 [드라이브], [디렉터리]를 차례로 이동하여 [데이터베이스 이름] 목록에서 ‘예약.accdb’ 데이터베이스 파일을 선택하고 [확인] 단추를 클릭한다.

4. [쿼리 마법사-열 선택] 대화 상자의 [사용할 수 있는 테이블과 열]에서 ‘예약리스트’ 테이블 이름의 [+]를 클릭한다.

5. 필드 목록에서 ‘회원ID’, ‘여권번호’, ‘출발날짜’, ‘출발공항’, ‘도착공항’, ‘편명’ 필드를 차례로 선택하고 [>] 단추를 클릭한 후 [다음] 단추를 클릭한다.

6. [쿼리 마법사-데이터 필터] 대화 상자의 [필터할 열]에서 ‘편명’ 필드 이름을 선택한 후 [포함할 행에 대한 조건]에서 ‘=’, ‘KE 711’을 설정한다.

7. ‘또는’ 옵션을 선택하고 두 번째 조건에 ‘=’, ‘KE 715’를 설정한 후 [다음] 단추를 클릭한다.

8. [쿼리 마법사-정렬 순서] 대화상자에서 [첫째 기준]은 ‘편명’, ‘오름차순’을 선택한다.

9. [다음 기준]은 ‘출발날짜’, ‘내림차순’으로 선택하고 [다음] 단추를 클릭한다.

10. [쿼리 마법사-마침] 대화 상자에서 ‘Microsoft Office Excel로 데이터 되돌리기’를 선택하고 [마침] 단추를 클릭한다.

11. [데이터 가져오기] 대화 상자에서 ‘기존 워크시트’를 선택하고 A1 셀을 클릭하여 지정한다.

12. [속성] 단추를 클릭한다.

13. [연결 속성] 대화 상자의 [사용 현황] 탭에서 ‘다음 간격으로 새로 고침’ 확인란을 선택하고 ‘30’분을 설정한다.

14. [연결 이름]에 12월 둘째 주를 입력하고 [확인] 단추를 클릭한다.

15. [데이터 가져오기] 대화 상자에서 [확인] 단추를 클릭한다.

32. 알림 표시 없이 모든 매크로를 제외하도록 매크로 보안 옵션을 변경하시오.

(파일 없음) [Hint: 매크로 보안]

1. [개발 도구] 탭-[코드] 그룹에서 [매크로 보안] 명령을 클릭한다.

2. [보안 센터] 대화 상자의 [매크로 설정] 항목에서 신뢰할 수 없는 위치에 있는 문서의 매크로에서 ‘모든 매크로 제외(알림 표시 없음)’ 옵션을 설정하고 [확인] 단추를 클릭한다.

33. 생년월일의 월과 오늘날짜 J1 셀의 월이 같으면 ‘생일’을 반환하고 아니면 공백으로 반환하도록 J4 셀에 수식을 계산하시오. J4 셀의 수식을 J33 셀까지 복사하시오.

(실전3-33.xlsx) [Hint: IF 함수, MONTH 함수]

1. J4 셀을 선택한다.

2. [수식] 탭-[함수 라이브러리] 그룹-[논리] 명령을 클릭하여 ‘IF’ 함수를 클릭한다.

3. IF [함수 인수] 창에서 [Logical_test]를 MONTH(E4)=MONTH($J$1), [Value_if_true]는 “생일”, [Value_if_false]는 “”을 입력하고 [확인] 단추를 클릭한다.

4. J4 셀의 채우기 핸들을 더블 클릭하여 수식을 복사한다.

※ 수식 =IF(MONTH(E4)=MONTH($J$1),“생일”,“”)을 직접 작성해도 된다.

※ 오늘날짜는 =TODAY() 함수로 매일 업데이트되므로, 생일 반환 결과는 그림과 다를 수 있다.

34. [예약] 워크시트의 표의 모든 값을 새로 고침하고 링크를 끊으시오.

(실전3-34.xlsx) [Hint: 새로 고침, 링크 끊기]

1. 보안 경고 메시지 표시줄이 나타나면 [옵션] 단추를 클릭한다.

2. [Microsoft Office 보안 옵션] 대화 상자에서 ‘이 콘텐츠 사용’ 옵션을 선택하고 [확인] 단추를 클릭한다.

3. 표 내부의 임의의 셀을 클릭한다.

4. [표 도구]-[디자인] 탭-[외부 표 데이터] 그룹-[새로 고침]-[모두 새로 고침] 명령을 클릭한다.

5. [표 도구]-[디자인] 탭-[외부 표 데이터] 그룹-[링크 끊기] 명령을 클릭한다.

6. 제거 확인 메시지 창이 나타나면 [확인] 단추를 클릭한다.

※ 문제32에서 매크로를 사용하지 못하도록 기본 보안 설정을 변경했다면 다시 매크로 포함 옵션으로 변경해야 한다.

35. 주민등록번호에서 8번째 자리의 한 문자인 성별 코드를 추출해 1과 3이면 ‘남자’, 2와 4면 ‘여자’로 표시되도록 E5 셀에 구하시오 E5 셀의 수식을 E31 셀까지 복사하시오.

(실전3-35.xlsx) [Hint: CHOOSE 함수, MID 함수]

1. E5 셀을 선택하고 [수식] 탭-[함수 라이브러리] 그룹-[찾기/참조 영역] 명령을 클릭하여 ‘CHOOSE’ 함수를 클릭한다.

2. [함수 인수] 창이 나타나면 [Index_num]은 MID(F5,8,1), [Value1]은 “남자”, [Value2]는 “여자”, [Value3]은 “남자”를, [Value4]는 “여자”를 입력하고 [확인] 단추를 클릭한다.

3. E5 셀의 채우기 핸들을 더블 클릭하여 수식을 복사한다.

※ 수식 =CHOOSE(MID(F5,8,1),“남자”,“여자”,“남자”,“여자”)를 직접 작성해도 된다.