실전모의고사 1회

MOS Excel 2010 Expert

01. 차트를 ‘레이아웃’ 11과 ‘차트 스타일 32’로 변경하고 ‘주황, 강조 6, 60% 더 밝게’ 도형 채우기를 설정하시오. 그리고 이 차트를 ‘수량비교차트’라는 이름의 차트 서식 파일로 저장하시오.

(실전1-01.xlsx, 수량비교차트(완성).crtx)

[Hint: 차트 레이아웃, 차트 스타일, 차트 서식, 차트 서식 파일 저장]

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

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

3. [차트 도구]-[디자인] 탭-[차트 스타일] 그룹에서 [자세히] 명령을 클릭하여 ‘스타일 32’를 선택한다.

4. [차트 도구]-[서식] 탭-[도형 스타일] 그룹-[도형 채우기] 명령을 클릭해 [테마 색]에서 ‘주황, 강조 6, 60% 더 밝게’를 선택한다.

5. [차트 도구]-[디자인] 탭-[종류] 그룹-[서식 파일로 저장] 명령을 클릭한다.

6. [차트 서식 파일 저장] 대화 상자에서 [파일 이름]에 ‘수량비교차트’를 입력하고 [저장] 단추를 클릭한다.

02. [강북], [강남] 및 [강서] 범위의 데이터 합계를 새 워크시트의 A3 셀에서 시작하여 통합하시오. 첫 행과 왼쪽 열의 레이블을 표시하고 새 워크시트의 이름을 ‘지역합계’로 지정하시오.

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

1. 시트 탭에서 [워크시트 삽입] 아이콘을 클릭한다.

2. 새 워크시트의 A3 셀을 선택한다.

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

4. [통합] 대화 상자에서 [함수]를 ‘합계’로 선택한다.

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

6. 레이블과 데이터 범위인 A3:M13 셀 범위를 드래그하여 선택한 후 [추가] 단추를 클릭한다.

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

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

9. [모든 참조 영역]에 참조 주소가 모두 추가되면 [사용할 레이블]에서 ‘첫 행’과 ‘왼쪽 열’의 확인란을 모두 선택한다.

10. ‘원본 데이터에 연결’의 확인란은 선택이 해제되어 있는 상태에서 [확인] 단추를 클릭한다.

11. 통합 결과가 나타나면 시트 탭을 더블 클릭한다.

12. ‘지역합계’를 입력하고 <Enter> 키를 누른다.

03. [G3] 셀에 AVERAGEIFS 함수를 사용하여 차량유지의 카드 평균 사용 금액을 찾으시오.

(실전1-03.xlsx) [Hint: AVERAGEIFS 함수]

1. G3 셀을 선택한다.

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

3. [함수 인수] 창이 나타나면 [Average_range]는 E2:E143, [Criteria_range1]은 C2:C143, [Criteria1]은 “차량유지”, [Criteria_range2]는 D2:D143, [Criteria2]는 “카드”를 입력하고 [확인] 단추를 클릭한다.

※ 수식 = AVERAGEIFS(E2:E143,C2:C143, “차량유지”, D2:D143, “카드”)를 직접 작성해도 된다.

04. ‘주문목록.xsd’ 스키마 맵을 사용하여 XML 요소를 현재 통합 문서에 매핑하고, ‘주문목록.xml’ 데이터 파일을 가져오시오. 그리고 현재 워크시트를 ‘하반기 주문’이라는 XML 데이터 파일로 문서 폴더에 내보내시오.

(실전1-04.xlsx) [Hint: XML 스키마 맵 추가, 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] 그룹-[내보내기] 명령이나, 표 안에서 마우스 오른쪽 단추를 클릭하여 [XML]-[내보내기] 메뉴를 클릭한다.

16. [XML 내보내기] 대화 상자에서 저장 위치를 [문서] 폴더로 선택하고 [파일 이름]에 ‘하반기 주문’을 입력하고 [내보내기] 단추를 클릭한다.

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

05. 새 워크시트에 ‘공사명’을 행 레이블로, ‘자재등급’은 열 레이블로, ‘출고량’의 합계 값을 표시하는 피벗 테이블을 만드시오.

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

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

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

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

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

5. [피벗 테이블 필드 목록] 작업창의 [보고서에 추가할 필드 선택]에서 ‘공사명’ 필드를 [행 레이블]로 드래그한다.

6. ‘자재등급’ 필드를 [열 레이블]로 드래그한다.

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

8. 피벗 테이블 이외의 임의 셀을 클릭한다.

06. [F3:H20] 셀 범위만 선택할 수 있고 다른 모든 셀은 선택할 수 없도록 시트를 보호하시오.

(실전1-06.xlsx) [Hint: 셀 잠금 해제, 시트 보호]

1. F3:H20 셀 범위를 선택한다.

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

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

4. [시트 보호] 대화 상자의 [워크시트에서 허용할 내용]에서 ‘잠긴 셀 선택’ 확인란의 선택을 해제한다.

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

6. F3:H20 셀만 선택 및 편집이 가능하고 이외의 셀은 셀 선택조차 안 된다.

07. 반복 계산되고 최대 반복 횟수가 30이 되도록 설정하시오.

(실전1-07.xlsx) [Hint: 수식 계산 옵션]

1. [파일] 탭-[옵션]을 클릭한다.

2. [Excel 옵션] 대화 상자에서 [수식] 범주를 클릭한다.

3. [계산 옵션] 구역에서 ‘반복 계산 사용’ 확인란을 선택한다.

4. [최대 반복 횟수]를 ‘30’으로 수정하고 [확인] 단추를 클릭한다.

08. 변경 내용이 100일 동안 저장되도록 현재 통합 문서를 공유하시오.

(실전1-08.xlsx) [Hint: 통합 문서 공유]

1. [검토] 탭-[변경 내용] 그룹-[통합 문서 공유] 명령을 클릭한다.

2. [통합 문서 공유] 대화 상자의 [편집] 탭에서 ‘여러 사용자가 동시에 변경할 수 있으며 통합 문서 병합도 가능’ 확인란을 선택한다.

3. [고급] 탭에서 [변경 내용 저장 기간]을 ‘100’일로 수정하고 [확인] 단추를 클릭한다.

4. 이 작업으로 통합 문서가 저장될 수 있다는 메시지 창이 나타나면 [확인] 단추를 클릭한다.

09. [D3:D22] 셀 범위에 접수 코드의 마지막 1글자를 참조하여 S면 ‘서울’을 아니면 ‘경기’를 반환하는 수식을 구하시오.

(실전1-09.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 셀의 채우기 핸들을 더블 클릭하여 수식을 복사한다.

10. 소수점 이하 2자리까지 표시하고 상위 20%인 상위/하위 규칙을 적용하는 매크로를 현재 통합 문서에 만드시오. 매크로 이름은 ‘상위평균’으로 지정하고 평균 열의 값에 이 매크로를 적용하시오. 다른 기본 성적은 모두 그대로 두시오.

(실전1-10.xlsx) [Hint: 매크로 기록, 표시 형식, 조건부 서식, 매크로 실행]

1. 셀 포인터를 이동하지 않고 [개발 도구] 탭-[코드] 그룹-[매크로 기록] 명령이나, 상태 표시줄에서 [매크로 기록] 아이콘을 클릭한다.

2. [매크로 기록] 대화 상자에서 [매크로 이름]에 ‘상위평균’을 입력한다.

3. [매크로 저장 위치]가 ‘현재 통합 문서’인지 확인하고 [확인] 단추를 클릭한다.

4. [홈] 탭-[표시 형식] 그룹에서 [자릿수 늘림] 아이콘을 두 번 클릭한다.

5. [홈] 탭-[스타일] 그룹-[조건부 서식]-[상위/하위 규칙]-[상위 10%]를 클릭한다.

6. [상위 10%] 대화 상자에서 20을 입력하고 [확인] 단추를 클릭한다.

7. [개발 도구] 탭-[코드] 그룹-[기록 중지] 명령을 클릭한다.

8. G3 셀을 선택하고 <Ctrl+Shift+↓> 키를 눌러 마지막 데이터 행까지 선택한다.

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

10. [매크로] 대화 상자에서 ‘상위평균’ 매크로를 선택하고 [실행] 단추를 클릭한다.

11. 할인율을 20%로 변경하는 ‘20퍼센트할인’ 이름의 시나리오를 만들고 표시하시오.

(실전1-11.xlsx) [Hint: 시나리오 추가, 시나리오 표시]

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

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

3. [시나리오 추가] 대화 상자의 [시나리오 이름]에 ‘20퍼센트할인’을 입력한다.

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

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

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

7. 워크시트에 시뮬레이션 결과가 나타나면 [닫기] 단추를 클릭한다.

12. 값이 ‘336655’인 숫자 서식인 ‘구매번호’라는 이름의 사용자 지정 문서 속성을 설정하시오.

(실전1-12.xlsx) [Hint: 통합 문서 속성]

1. [파일] 탭-[정보]를 클릭한다.

2. [속성]-[고급 속성]을 클릭한다.

3. [속성] 대화 상자의 [사용자 지정] 탭으로 이동한다.

4. [이름]에 ‘구매번호’를, [형식]은 ‘숫자’, [값]에 336655를 입력하고 [추가] 단추를 클릭한다.

5. [속성] 목록에 추가되면 [확인] 단추를 클릭한다.

13. [I3:I12] 셀 범위에 1월~6월 실적을 꺾은선 스파크라인으로 만드시오. 그리고 스파크라인에 ‘스파크라인 스타일 색상형 #2’ 스파크라인 스타일을 적용하고, 높은 점과 낮은 점을 표시하시오.

(실전1-13.xlsx) [Hint: 통합 문서 속성]

1. I3:I12 셀 범위를 선택한다.

2. [삽입] 탭-[스파크라인] 그룹-[꺾은선형]을 클릭한다.

3. [스파크라인 만들기] 대화 상자의 [데이터 범위]에 커서를 이동한다.

4. C3:H12 셀 범위를 드래그하고 [확인] 단추를 클릭한다.

5. [스파크라인 도구]-[디자인] 탭-[스타일] 그룹에서 ‘스파크라인 스타일 색상형 #2’를 클릭한다.

6. [스파크라인 도구]-[디자인] 탭-[표시] 그룹에서 ‘높은 점’과 ‘낮은 점’ 확인란을 선택한다.

14. 워크시트에서 일치하지 않는 수식이 참조되는 모든 셀을 추적하시오.

(실전1-14.xlsx) [Hint: 통합 문서 속성]

1. [수식] 탭-[수식 분석] 그룹-[오류 검사] 명령을 클릭한다.

2. E14 셀이 일치하지 않는 수식이 있는 셀로 선택되면 [닫기] 아이콘을 클릭한다.

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

15. [F3] 셀 왼쪽에 스핀 단추를 만들고 [F3] 셀의 값이 1 ~ 31 사이의 수로 1씩 증가하도록 변경하시오. 다른 기본 설정은 모두 그대로 적용하시오.

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

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

2. F3 셀 왼쪽에서 드래그하여 그린다.

3. 스핀 단추 위에서 마우스 오른쪽 단추를 눌러 [컨트롤 서식] 명령을 클릭한다.

4. [컨트롤 서식] 대화 상자의 [컨트롤] 탭에서 [최소값]은 ‘1’로 설정한다.

5. [최대값]은 ‘31’로 설정한다.

6. [증분 변경]은 ‘1’로 설정한다.

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

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

16. [H3] 셀에 단가가 50000을 초과하고 B로 시작하는 제품수를 계산하는 함수를 작성하시오.

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

1. H3 셀을 선택한다.

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

3. COUNTIFS [함수 인수] 창에서 [Criteria_range1] 인수에 C2:C101을 입력한다.

4. [Criteria1] 인수에 “>50000”을 입력한다.

5. [Criteria_range2] 인수에 D2:D101을 입력한다.

6. [Criteria2] 인수에 “B*”을 입력하고 [확인] 단추를 클릭한다.

7. =COUNTIFS(C2:C101,“>50000”,D2:D101,“B*”) 수식이 완성된다.

17. 56789라는 암호로 통합 문서를 암호화하고 최종본으로 표시하시오.

(실전1-17.xlsx) [Hint: 통합 문서 보호, 최종본 표시]

1. [파일] 탭-[정보] 명령을 클릭한다.

2. [통합 문서 보호]-[암호 설정]을 클릭한다.

3. [문서 암호화] 대화 상자의 [암호]에 56789를 입력하고 [확인] 단추를 클릭한다.

4. 56789 암호를 재입력하고 [확인] 단추를 클릭한다.

5. [파일] 탭-[정보]에서 [통합 문서 보호]-[최종본으로 표시] 메뉴를 클릭한다.

6. 나타나는 메시지 창에서 [확인] 단추를 클릭한다.

7. 최종본으로 표시됨을 알리는 메시지 창에서 [확인] 단추를 클릭한다.

18. 성장률 차트에 차수 2를 사용하고 앞으로 2구간을 예측하는 다항식 추세선을 추가하시오.

(실전1-18.xlsx) [Hint: 추세선]

1. 성장률 차트의 데이터 계열 위에서 마우스 오른쪽 단추를 클릭하여 [추세선 추가] 메뉴를 클릭한다.

2. [추세선 서식] 대화 상자의 [추세선 옵션] 탭에서 [추세/회귀 유형]은 ‘다항식’으로 선택한다.

3. [차수]는 ‘2’로 설정한다.

4. [예측]-[앞으로]를 ‘2’로 설정한다.

5. [닫기] 단추를 클릭한다.

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

(실전1-19.xlsx) [Hint: 데이터 표]

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

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

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

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

20. 해외영업부에 대한 상기실적을 영업사원별로 표시하는 피벗 차트를 새 워크시트에 만드시오.

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

1. 실적표 데이터베이스 임의의 셀을 선택한다.

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

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

4. [피벗 테이블 필드 목록] 작업창의 [보고서에 추가할 필드 선택]에서 ‘부서명’ 필드를 [범례 필드(계열)] 위치로 드래그한다.

5. ‘사원명’ 필드를 [축 필드(항목)] 위치로 드래그한다.

6. ‘상기실적’ 필드를 [값] 위치로 드래그하여 피벗 차트 레이아웃을 구성한다.

7. 피벗 차트에서 [부서명] 범례 필드를 클릭하고 ‘(모두 선택)’ 확인란의 선택을 해제한다.

8. ‘해외영업부’ 확인란만 선택하고 [확인] 단추를 클릭한다.

9. 임의의 빈 셀을 클릭한다.

21. 현재 공유 문서에서 모든 사용자가 수행한 변경 내용을 새 워크시트에 모두 표시하시오. 다른 기본 설정은 모두 그대로 적용하시오.

(실전1-21.xlsx) [Hint: 변경 내용 표시]

1. [검토] 탭-[변경 내용] 그룹-[변경 내용 추적]-[변경 내용 표시] 명령을 클릭한다.

2. [변경 내용 표시] 대화 상자에서 ‘언제’ 확인란의 선택을 해제합니다.

3. ‘새 시트에 변경 내용 작성’ 확인란을 선택한 후 [확인] 단추를 클릭한다.

4. [변경 내용] 이름의 새 시트에 변경 내용 정보가 표시된다.

22. 수식 계산 도구를 사용하여 [F7] 셀의 수식 오류를 정정하시오.

(실전1-22.xlsx) [Hint: 수식 계산]

1. F7 셀을 선택한다.

2. [수식] 탭-[수식 분석] 그룹-[수식 계산] 명령을 클릭한다.

3. [수식 계산] 대화 상자에서 C7 셀 주소에 밑줄이 그어진 상태에서 [계산] 단추를 클릭한다.

4. C7 셀의 참조 값이 ‘76’으로 표시된다.

5. $B$3 셀에 밑줄이 그어진 상태에서 [계산] 단추를 클릭한다.

6. $B$3 셀의 참조 값 ‘필기’라는 문자열이 표시되면 [계산] 단추를 클릭한다.

7. C7□$B$3 수식이 숫자*문자가 되어 #VALUE!라는 값 오류가 표시된다.

8. D7 셀 주소에 밑줄이 그어진 상태에서 [계산] 단추를 클릭한다.

9. D7 셀의 참조 값이 ‘97’로 표시된다.

10. $C$3 셀에 밑줄이 그어진 상태에서 [계산] 단추를 클릭한다.

11. $C$3 셀의 참조 값 ‘실기’라는 문자열이 표시되면 [계산] 단추를 클릭한다.

12. D7□$C$3 수식이 숫자*문자가 되어 #VALUE!라는 값 오류가 표시된다.

13. #VALUE 셀에 밑줄이 그어진 상태에서 [계산] 단추를 클릭한다.

14. E7 셀 주소에 밑줄이 그어진 상태에서 [계산] 단추를 클릭한다.

15. E7 셀의 참조 값이 ‘77’로 표시된다.

16. $D$3 셀에 밑줄이 그어진 상태에서 [계산] 단추를 클릭한다.

17. $D$3 셀의 참조 값 ‘면접’이라는 문자열이 표시되면 [계산] 단추를 클릭한다.

18. E7□$D$3 수식이 숫자*문자가 되어 #VALUE!라는 값 오류가 표시된다.

19. [계산] 단추를 클릭해 최종 값이 #VALUE! 오류로 표시되면 [닫기] 단추를 클릭한다.

20. 수식 입력줄에서 ‘$B$3’ 셀 주소를 드래그한 후 B4 셀을 클릭한다.

21. <F4> 키를 눌러 절대 참조 $B$4로 수정한다.

22. ‘$C$3’ 셀 주소를 드래그한 후 C4 셀을 클릭한다.

23. <F4> 키를 눌러 절대 참조 $C$4로 수정한다.

24. ‘$D$3’ 셀 주소를 드래그한 후 D4 셀을 클릭한다.

25. <F4> 키를 눌러 절대 참조 $D$4로 수정한다.

26. <Enter> 키를 누른다.

23. [재고분석] 워크시트에서 피벗 테이블에 공사명 및 자재등급을 표시하도록 슬라이스를 삽입하시오. 그리고 도배공사 및 조명공사인 공사명을 표시하시오.

(실전1-23.xlsx) [Hint: 슬라이서]

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

2. [피벗 테이블 도구]-[옵션] 탭-[정렬 및 필터] 그룹-[슬라이서 삽입] 명령을 클릭한다.

3. [슬라이서 삽입] 창에서 ‘공사명’과 ‘자재등급’의 확인란을 선택하고 [확인] 단추를 클릭한다.

4. [공사명] 슬라이서 창에서 ‘도배공사’를 클릭한다.

5. <Ctrl> 키를 누른 채 ‘조명공사’를 클릭해 동시에 선택한다.

24. 영업 계획 차트에 2013년 계획 열의 값이 막대 차트에 포함되도록 데이터 원본을 수정하시오.

(실전1-24.xlsx) [Hint: 차트 데이터 범위]

1. 차트를 선택한다.

2. [차트 도구]-[디자인] 탭-[데이터] 그룹-[데이터 선택] 명령이나, 차트 영역 위에서 마우스 오른쪽 단추를 눌러 [데이터 선택] 메뉴를 클릭한다.

3. [데이터 원본 선택] 대화 상자에서 [차트 데이터 범위]의 기존 참조 주소가 선택되어 있는 상태에서 <Ctrl> 키를 누른 채 F6:F13 셀 범위를 드래그한다.

4. 기존 범위에 새 데이터 범위가 추가되면 [확인] 단추를 클릭한다.

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. 수식 오류가 감지되면 파랑으로 표시되도록 Excel 옵션을 구성하시오.

(실전1-26.xlsx) [Hint: 오류 표시 색]

1. [파일] 탭-[옵션]을 클릭한다.

2. [Excel 옵션] 대화 상자의 [수식] 범주에서 [오류 검사] 항목의 [오류 표시 색] 아이콘을 클릭해서 ‘파랑’을 선택한다.

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

27. [B4:G14] 셀 범위에는 모든 테두리를 [C5:G14] 셀 범위에 회계 표시 형식을 설정하는 매크로를 기록하시오. 현재 통합 문서에 매크로를 기록하고 새 매크로 이름은 ‘표서식’으로 지정하시오.

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

1. B4:G14 셀 범위를 드래그해 선택한다.

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

3. [매크로 기록] 대화 상자에서 [매크로 이름]에 ‘표서식’을 입력하고 [확인] 단추를 클릭한다.

4. [홈] 탭-[글꼴] 그룹-[테두리] 아이콘의 목록 단추를 클릭하여 ‘모든 테두리’를 클릭한다.

5. C5:G14 셀 범위를 드래그해 선택한다.

6. [홈] 탭-[표시 형식] 그룹에서 [회계 표시 형식] 아이콘을 클릭한다.

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

28. ‘표 서식’이라는 이름의 단추(양식 컨트롤)을 [G2] 셀에 삽입하고 이 단추에 ‘표서식’ 매크로를 할당하시오. 그리고 매크로를 실행하시오.

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

1. [보안 경고] 메시지 표시줄에서 [콘텐츠 사용] 단추를 클릭한다.

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

3. G2 셀 위에서 드래그하여 그린다.

4. [매크로 지정] 대화 상자에서 ‘표서식’ 매크로를 선택하고 [확인] 단추를 클릭한다.

5. 단추에 표 서식을 입력하고 다른 임의의 셀을 클릭한다.

6. [표 서식] 단추를 클릭해 매크로를 실행한다.

29. [F10] 셀에 HLOOKUP 함수를 사용하여 ‘김재균’의 과목을 찾으시오.

(실전1-29.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)를 직접 작성해도 된다.

실전모의고사 2회

01. [D5] 셀이 참조하는 모든 셀을 추적하시오.

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

1. D5 셀을 클릭한다.

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

02. 대분류를 보고서 필터로, 날짜를 행 레이블로 분기별, 월별로 표시하고, 열 레이블은 사이즈로, 판매가의 최대값을 회계 표시 형식으로 표시하는 피벗 테이블을 새 워크시트에서 만드시오.

(실전2-02.xlsx) [Hint: 피벗 테이블, 그룹 묶기, 함수 변경]

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

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

3. [피벗 테이블 만들기] 대화 상자에서 피벗 테이블 보고서를 넣을 위치를 ‘새 워크시트’로 선택하고 [확인] 단추를 클릭한다.

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

5. ‘날짜’ 필드를 [행 레이블] 위치로 드래그한다.

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

7. ‘판매가’ 필드를 [값]으로 드래그한다.

8. 피벗 테이블 레이아웃의 날짜인 행 레이블의 값이 있는 임의의 셀을 선택한다.

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

10. [그룹화] 대화 상자에서 [단위]는 ‘분기’를 클릭해 ‘월’과 ‘분기’를 모두 선택하고 [확인] 단추를 클릭한다.

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

12. [값 필드 설정] 대화 상자의 [값 요약 기준] 탭에서 ‘최대값’을 선택한다.

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

14. [셀 서식] 대화 상자에서 ‘회계’ 범주를 선택하고 [확인] 단추를 클릭한다.

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

16. 피벗 테이블 이외의 임의의 셀을 클릭한다.

03. 강수량 차트에 12월 행의 값이 꺾은선 차트에 포함되도록 데이터 원본을 변경하시오. 그리고 ‘평균’ 데이터 계열을 묶은 세로 막대형 차트 종류로 변경하시오.

(실전2-03.xlsx) [Hint: 차트 데이터 범위, 계열 차트 종류 변경]

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

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

3. [데이터 원본 선택] 대화 상자에서 [차트 데이터 범위]의 셀 주소가 선택되어 있는 상태에서 A3:F15 셀 범위를 드래그한다.

4. 차트 데이터 범위가 수정되면 [확인] 단추를 클릭한다.

5. ‘평균’ 데이터 계열 위에서 마우스 오른쪽 단추를 클릭하고 [계열 차트 종류 변경] 메뉴를 클릭한다.

6. [차트 종류 변경] 대화 상자에서 ‘묶은 세로 막대형’ 차트 종류를 선택하고 [확인] 단추를 클릭한다.

04. 강수량 차트를 스타일 25로 변경하고 ‘황갈색, 개병 2, 10% 더 어둡게’ 도형 채우기 색 및 ‘황갈색, 배경 2, 50% 더 어둡게’ 도형 윤곽선을 설정하시오. 그리고 이 차트를 ‘강수량차트’라는 이름의 차트 서식 파일로 저장하시오.

(실전2-04.xlsx) [Hint: 빠른 차트 스타일, 도형 채우기, 도형 윤곽선, 차트 서식 파일 저장]

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

2. [차트 도구]-[디자인] 탭-[차트 스타일] 그룹에서 [자세히] 명령을 클릭하여 ‘스타일 25’를 선택한다.

3. [차트 도구]-[서식] 탭-[도형 스타일] 그룹-[도형 채우기] 명령을 클릭해 [테마 색]에서 ‘황갈색, 배경 2, 10% 더 어둡게’를 선택한다.

4. [차트 도구]-[서식] 탭-[도형 스타일] 그룹-[도형 윤곽선] 명령을 클릭해 [테마 색]에서 ‘황갈색, 배경 2, 50% 더 어둡게’를 선택한다.

5. [차트 도구]-[디자인] 탭-[종류] 그룹-[서식 파일로 저장] 명령을 클릭한다.

6. [차트 서식 파일 저장] 대화 상자에서 [파일 이름]에 강수량차트를 입력하고 [저장] 단추를 클릭한다.

05. 분류코드 [B2] 셀에 품번에서 4번째에서 5번째까지 문자를 추출하는 수식을 작성하시오. 수식을 [B16] 셀까지 복사하시오.

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

1. B2 셀을 선택한다.

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

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

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

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

06. 셀 내용은 글꼴 크기는 12포인트, 굵게, 가운데 맞춤으로 적용하고 행 높이는 정확히 21로 설정하는 매크로를 만드시오. 매크로의 이름은 ‘레이블’로 설정하여 현재 통합 문서에 저장하시오.

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

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

2. [매크로 기록] 대화 상자에서 [매크로 이름]에 ‘레이블’을 입력하고 [매크로 저장 유치]를 ‘현재 통합 문서’로 두고 [확인] 단추를 클릭한다.

3. [홈] 탭-[글꼴] 그룹-[글꼴 크기] 명령의 목록 단추를 클릭하여 ‘12’로 설정한다.

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

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

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

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

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

07. [G2:H3] 셀 범위에 ‘레이블 서식’이라는 이름의 단추(양식 컨트롤)을 만들어 ‘레이블’ 매크로를 할당하고, 3행의 레이블 셀에 ‘레이블’ 매크로를 실행하시오.

(실전2-07.xlsx) [Hint: 수식 계산 옵션]

1. [보안 경고] 메시지 표시줄에서 [콘텐츠 사용] 단추를 클릭한다.

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

3. G2:H3 셀 범위 위에서 드래그하여 그린다.

4. [매크로 지정] 대화 상자에서 ‘레이블’ 매크로를 선택하고 [확인] 단추를 클릭한다.

5. 단추에 ‘레이블 서식’을 입력하고 다른 임의의 셀을 클릭한다.

6. A3:F3 셀 범위를 드래그해 선택한다.

7. ‘레이블 서식’ 단추를 클릭해 매크로를 실행한다.

08. 상반기 및 하반기 범위의 매출 값을 평균 내어 [매출통합] 워크시트의 [B2] 셀에서 시작하여 통합하시오. 첫 행 및 왼쪽 열의 레이블을 표시하시오.

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

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

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

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

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

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

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

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

09. 2013년 영업 계획 평균이 5,000을 달성하려면 조정율이 얼마여야 하는지를 구하시오.

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

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

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

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

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

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

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

7. 영업 계획 평균 값이 50000이 되려면 조정율이 29%로 조정돼야 한다.

10. [H3] 셀에 AVERAGEIFS 함수를 사용하여 식비를 카드로 결재한 평균 금액의 수를 0 값을 제외하여 찾으시오.

(실전2-10.xlsx) [Hint: AVERAGEIFS 함수]

1. H3 셀을 선택한다.

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

3. [함수 인수] 창이 나타나면 [Average_range]는 F3:F144, [Criteria_range1]은 D3:D144, [Criteria1]은 “식비”, [Criteria_range2]는 E3:E144, [Criteria2]는 “카드”를 입력하고 [확인] 단추를 클릭한다.

※ 수식 =AVERAGEIFS(F3:F144,D3:D144,”식비”,E3:E144,”카드”)를 직접 작성해도 된다.

11. ‘작성일’이라는 이름의 값이 ‘2012-12-01’인 날짜 형식을 사용하는 사용자 지정 속성을 만드시오.

(실전2-11.xlsx) [Hint: 통합 문서 속성]

1. [파일] 탭-[정보]를 클릭한다.

2. [속성]-[고급 속성]을 클릭한다.

3. [속성] 대화 상자의 [사용자 지정] 탭으로 이동한다.

4. [이름]에 ‘작성일’을, [형식]은 ‘날짜’, [값]에 2012-12-01을 입력하고 [추가] 단추를 클릭한다.

5. [속성] 목록에 추가되면 [확인] 단추를 클릭한다.

12. 수식 계산 도구를 사용하여 [D6] 셀의 수식 오류를 정정하시오.

(실전2-12.xlsx) [Hint: 수식 계산]

1. D6 셀을 선택한다.

2. [수식] 탭-[수식 분석] 그룹-[수식 계산] 명령을 클릭한다.

3. [수식 계산] 대화 상자에서 $C6 셀 주소에 밑줄이 그어진 상태에서 [계산] 단추를 클릭한다.

4. $C6 셀의 참조 값이 ‘87000’으로 표시된다.

5. D$4 셀에 밑줄이 그어진 상태에서 [계산] 단추를 클릭한다.

6. D$4 셀의 참조 값 ‘일본’이라는 문자열이 표시되면 [계산] 단추를 클릭한다.

7. $C6*D$4 수식이 숫자(87000)*문자(일본)가 되어 #VALUE!라는 값 오류가 표시된다.

8. [닫기] 단추를 클릭한다.

9. 수식 입력줄에서 ‘D$4’ 셀 주소를 드래그한 후 D5 셀을 클릭한다.

10. <F4> 키를 두 번 눌러 혼합 참조 D$5으로 수정한다.

11. <Enter> 키를 누른다.

13. 기존 XML 스키마 맵을 사용하여 XML 요소를 현재 통합 문서에 매핑하고 ‘인사정보.xml’ 데이터 파일을 가져오시오. 그리고 현재 워크시트를 ‘주소록’이라는 XML 데이터 파일로 문서 폴더에 내보내시오. (실전2-13.xlsx, 인사정보.xml) [Hint: XML 요소 매핑, XML 가져오기, XML 내보내기]

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

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

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

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

5. ‘주소’ 필드를 E4 셀로 드래그한다.

6. ‘연락처’ 필드를 F4 셀로 드래그한다.

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

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

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

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

11. [XML 내보내기] 대화 상자에서 저장 위치를 [문서] 폴더로 선택하고 [파일 이름]에 ‘주소록’을 입력하고 [내보내기] 단추를 클릭한다.

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

14. 변경 셀인 컴퓨터 [E4] 셀 및 영어 [F4] 셀은 35%, 상식 [G4] 셀은 30%인 ‘가중치’라는 시나리오를 만드시오. ‘강민우’ 조정점수에 대한 시나이로 요약 보고서를 만들고 새 시트의 이름을 ‘성적시나리오’로 설정하시오.

(실전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. [결과 셀]에 커서를 이동한 후 G8 셀을 클릭한 후 [확인] 단추를 클릭한다.

11. [시나리오 요약] 워크시트가 삽입되고 요약 보고서가 나타나면 [시나리오 요약] 시트 탭을 더블 클릭한다.

12. ‘성적시나리오’를 입력하고 <Enter> 키를 누른다.

15. 반복 계산되고 최대 반복 횟수가 50이 되도록 설정하시오.

(실전2-15.xlsx) [Hint: 수식 계산 옵션]

1. [파일] 탭-[옵션]을 클릭한다.

2. [Excel 옵션] 대화 상자에서 [수식] 범주를 클릭한다.

3. [계산 옵션] 구역에서 ‘반복 계산 사용’ 확인란을 선택한다.

4. [최대 반복 횟수]를 ‘50’으로 수정하고 [확인] 단추를 클릭한다.

16. [H3] 셀에 카드에 해당하는 거래 유형의 수를 계산하는 함수를 작성하시오.

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

1. H3 셀을 선택한다.

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

3. [Range]는 E3:E144, [Criteria]는 “카드”를 입력하고 [확인] 단추를 클릭한다.

※ 수식 =COUNTIF(E3:E144,“카드”)를 직접 작성해도 된다.

17. 변경 내용이 60일 동안 저장되도록 현재 통합 문서를 공유하시오.

(실전2-17.xlsx) [Hint: 통합 문서 공유]

1. [검토] 탭-[변경 내용] 그룹-[통합 문서 공유] 명령을 클릭한다.

2. [통합 문서 공유] 대화 상자의 [편집] 탭에서 ‘여러 사용자가 동시에 변경할 수 있으며 통합 문서 병합도 가능’ 확인란을 선택한다.

3. [고급] 탭에서 [변경 내용 저장 기간]을 ‘60’일로 수정하고 [확인] 단추를 클릭한다.

4. 이 작업으로 통합 문서가 저장될 수 있다는 메시지 창이 나타나면 [확인] 단추를 클릭한다.

18. [판매분석] 워크시트에서 피벗 테이블에 날짜 및 대분류를 표시하도록 슬라이드를 삽입하시오. 그리고 1월~3월의 가디건, 니트, 스커트, 티셔츠 값을 표시하시오.

(실전2-18.xlsx) [Hint: 슬라이서]

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

2. [피벗 테이블 도구]-[옵션] 탭-[정렬 및 필터] 그룹-[슬라이서 삽입] 명령을 클릭한다.

3. [슬라이서 삽입] 창에서 ‘날짜’ 및 ‘대분류’의 확인란을 선택하고 [확인] 단추를 클릭한다.

4. [날짜] 슬라이서 창에서 ‘1월’을 클릭한다.

5. <Shift> 키를 누른 채 ‘3월’을 클릭하면 1월부터 3월까지 날짜가 선택된다.

6. [대분류] 슬라이서 창에서 ‘가디건’을 클릭한다.

7. <Ctrl> 키를 누른 채 ‘니트’, ‘스커트’, ‘티셔츠’를 차례로 클릭해 동시에 선택한다.

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

19. 평균 이용자 수 차트에 앞으로 1구간을 예측하는 선형 추세선을 추가하시오.

(실전2-19.xlsx) [Hint: 추세선]

1. 평균 이용자 수 차트의 데이터 계열 위에서 마우스 오른쪽 단추를 클릭하여 [추세선 추가] 메뉴를 클릭한다.

2. [추세선 서식] 대화 상자의 [추세선 옵션] 탭에서 [추세/회귀 유형]은 ‘선형’으로 선택한다.

3. [예측]-[앞으로]를 ‘1’로 설정한다.

4. [닫기] 단추를 클릭한다.

20. 수식 오류가 감지되면 빨강으로 표시되도록 Excel 옵션을 구성하시오.

(실전2-20.xlsx) [Hint: 오류 표시 색]

1. [파일] 탭-[옵션]을 클릭한다.

2. [Excel 옵션] 대화 상자의 [수식] 범주에서 [오류 검사] 항목의 [오류 표시 색] 아이콘을 클릭해서 ‘빨강’을 선택한다.

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

21. [F3:G22] 셀 범위만 선택 및 데이터를 입력할 수 있고 다른 모든 셀은 선택할 수 없도록 시트를 보호하시오. 암호는 12345로 설정하시오.

(실전2-21.xlsx) [Hint: 셀 잠금 해제, 시트 보호]

1. F3:G22 셀 범위를 선택한다.

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

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

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

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

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

7. 다시 한 번 암호 12345를 입력하고 [확인] 단추를 클릭한다.

8. F3:F22 셀 범위만 선택 및 입력이 가능하고 이외의 셀은 셀 선택조차 안 된다.

22. [C2:C16] 셀 범위에 VLOOKUP 함수를 사용하여 분류코드의 분류를 찾으시오.

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

1. C2 셀을 선택한다.

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

3. [함수 인수] 창에서 [Lookup_value]는 B2, [Table_array]는 $B$20:$C$22, [Col_index_num]은 2, [Range_lookup]은 FALSE를 입력하고 [확인] 단추를 클릭한다.

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

※ 수식 =VLOOKUP(B2,$B$20:$C$22,2,FALSE)를 직접 작성해도 된다.

23. 새 워크시트에 대분류의 판매가를 표시하는 3차원 원형 피벗 차트를 만드시오. 피벗 차트에 차트 ‘레이아웃 3’을, 차트 스타일은 ‘스타일 28’을 적용하고, 월을 보고서 필터에 배치하여 1월이 표시되도록 차트를 필터링하시오.

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

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

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

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

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

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

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

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

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

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

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

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

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

13. 피벗 테이블과 피벗 차트 이외의 임의의 셀을 클릭한다.

24. [B3] 셀에 ‘완료’ 텍스트를 표시하는 확인란 컨트롤을 추가하시오. 그리고 기한의 [D3] 셀의 콤보 상자를 변경하여 입력 범위는 기한 범위([H3:H12])로 설정하고 [E3] 셀에 연결하고 목록 표시 줄수를 10으로 변경하시오.

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

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

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

3. 확인란 텍스트를 ‘완료’로 수정한다.

4. D3 셀의 콤보 상자 양식 컨트롤 위에서 마우스 오른쪽 단추를 눌러 [컨트롤 서식] 메뉴를 클릭한다.

5. [컨트롤 서식] 대화 상자의 [컨트롤] 탭에서 [입력 범위]에 커서를 이동하고 H3:H12 셀 범위를 드래그한다.

6. [셀 연결]에 E3 셀을 클릭한다.

7. [목록 표시 줄 수]를 ‘10’으로 설정하고 [확인] 단추를 클릭한다.

25. 현재 공유 문서에서 모든 사용자가 수행한 변경 내용을 새 워크시트에 모두 표시하시오. 다른 기본 설정은 모두 그대로 적용하시오.

(실전2-25.xlsx) [Hint: 변경 내용 표시]

1. [검토] 탭-[변경 내용] 그룹-[변경 내용 추적]-[변경 내용 표시] 명령을 클릭한다.

2. [변경 내용 표시] 대화 상자에서 ‘언제’ 확인란의 선택을 해제합니다.

3. ‘새 시트에 변경 내용 작성’ 확인란을 선택한 후 [확인] 단추를 클릭한다.

4. [변경 내용] 이름의 새 시트에 변경 내용 정보가 표시된다.

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

(실전2-26.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”)를 직접 작성해도 된다.

27. R1234라는 암호호 통합 문서를 암호화하고 최종본으로 표시하시오.

(실전2-27.xlsx) [Hint: 통합 문서 보호, 최종본 표시]

1. [파일] 탭-[정보]를 클릭한다.

2. [통합 문서 보호]-[암호 설정]을 클릭한다.

3. [문서 암호화] 대화 상자의 [암호]에 ‘R1234’를 입력하고 [확인] 단추를 클릭한다.

4. ‘R1234’ 암호를 재입력하고 [확인] 단추를 클릭한다.

5. [파일] 탭-[정보]에서 [통합 문서 보호]-[최종본으로 표시] 메뉴를 클릭한다.

6. 나타나는 메시지 창에서 [확인] 단추를 클릭한다.

7. 최종본으로 표시됨을 알리는 메시지 창에서 [확인] 단추를 클릭한다.

28. [F7:F14] 셀 범위에 2011년, 2012년 및 2013년 계획 실적을 열 스파크라인으로 만드시오. 그리고 스파크라인에 ‘스파크라인 스타일 강조 4, (어둡게 또는 밝게 없음)’ 스파크라인 스타일을 적용하고, 높은 점을 표시하고 높은 점의 색상을 빨강으로 표시하시오.

(실전2-28.xlsx) [Hint: 스파크라인]

1. F7:F14 셀 범위를 선택한다.

2. [삽입] 탭-[스파크라인] 그룹-[열]을 클릭한다.

3. [스파크라인 만들기] 대화 상자의 [데이터 범위]에 커서를 이동한다.

4. C7:E14 셀 범위를 드래그하고 [확인] 단추를 클릭한다.

5. [스파크라인 도구]-[디자인] 탭-[스타일] 그룹에서 ‘스파크라인 스타일 강조 4, (어둡게 또는 밝게 없음)’을 클릭한다.

6. [스파크라인 도구]-[디자인] 탭-[표시] 그룹에서 ‘높은 점’ 확인란을 선택한다.

7. [스파크라인 도구]-[디자인] 탭-[스타일] 그룹-[표식 색]-[높은 점]-[표준 색]에서 ‘빨강’을 클릭한다.

29. [B9:E16] 셀 범위에 두 개의 변수에 따른 데이터 표를 만드시오. 행 입력 셀은 차량가격([D3]), 열 입력 셀은 월상환횟수([D5])로 설정하시오.

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

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

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

3. [데이터 표] 대화 상자에서 [행 입력 셀]은 D3 셀을 선택해 입력한다.

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

실전모의고사 3회

01. [매출] 워크시트의 데이터를 사용하여 제품별 사원별로 수량의 값을 표시하는 피벗 차트를 만드시오. 이 피벗 차트를 새 워크시트에 배치하고 새 워크시트의 이름을 ‘매출분석’으로 변경하시오.

(실전3-01.xlsx) [Hint: 피벗 차트]

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

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

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

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

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

6. ‘사원명’ 필드를 [범례 필드(계열)]로 드래그한다.

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

8. 피벗 테이블 및 피벗 차트 이외의 임의의 셀을 클릭한다.

9. 새 워크시트의 시트 탭을 더블 클릭한다.

10. ‘매출분석’을 입력하고 <Enter> 키를 누른다.

02. [I2] 셀에 SUMIFS 함수를 사용하여 S로 시작하는 제품 중에서 싱싱곡물 공급업체의 수량 수를 계산하시오. (실전3-02.xlsx) [Hint: SUMIFS 함수]

1. I2 셀을 선택한다.

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

3. [함수 인수] 창에서 [Sum_range]는 G2:G26, [Criteria_range1]은 A2:A26, [Criteria1]은 “S*”, [Criteria_range2]는 C2:C26, [Criteria2]는 “싱싱곡물”을 입력하고 [확인] 단추를 클릭한다.

※ 수식 =SUMIFS(G2:G26,A2:A26,“S*”,C2:C26,“싱싱곡물”)을 직접 작성해도 된다.

03. 기존 XML 스키마 맵을 사용하여 XML 요소를 현재 통합문서에 매핑하고 현재 워크시트를 ‘입출고’라는 XML 데이터 파일로 문서 폴더에 내보내시오.

(실전3-03.xlsx) [Hint: XML 요소 매핑, XML 가져오기, XML 내보내기]

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

2. [XML 원본] 작업창에서 ‘상품군’ 필드를 B4 셀로 드래그한다

3. ‘상품명’ 필드를 C4 셀로 드래그한다

4. ‘입고단가’ 필드를 D4 셀로 드래그한다.

5. ‘출고단가’ 필드를 E4 셀로 드래그한다

6. ‘입고량’ 필드를 F4 셀로 드래그한다.

7. ‘출고량’ 필드를 G4 셀로 드래그한다.

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

9. [XML 가져오기] 대화 상자에서 ‘재고관리.xml’ 데이터 파일을 찾아 선택한 다음 [가져오기] 단추를 클릭한다.

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

11. [XML 내보내기] 대화 상자에서 저장 위치를 ‘문서’로 지정한다.

12. [파일 이름]에 ‘입출고’를 입력하고 [내보내기] 단추를 클릭한다.

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

04. 현재 통합 문서를 98765라는 암호로 암호화하고 최종본으로 표시하시오.

(실전3-04.xlsx) [Hint: 통합 문서 보호, 최종본 표시]

1. [파일] 탭-[정보]을 클릭한다.

2. [통합 문서 보호]-[암호 설정]을 클릭한다.

3. [문서 암호화] 대화 상자의 [암호]에 98765를 입력하고 [확인] 단추를 클릭한다.

4. 98765 암호를 재입력하고 [확인] 단추를 클릭한다.

5. [파일] 탭-[정보]에서 [통합 문서 보호]-[최종본으로 표시] 메뉴를 클릭한다.

6. 나타나는 메시지 창에서 [확인] 단추를 클릭한다.

7. 최종본으로 표시됨을 알리는 메시지 창에서 [확인] 단추를 클릭한다.

05. [A지점], [B지점] 및 [C지점]의 판매수량과 재고량 데이터에 대한 최대 함수를 적용하여 새 워크시트의 [B2] 셀부터 시작하여 통합하시오. 새 워크시트의 이름을 ‘판매통합’으로 변경하시오.

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

1. 시트 탭에서 [워크시트 삽입] 아이콘을 클릭한다.

2. 새 워크시트의 B2 셀을 선택한다.

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

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

5. [참조]에 커서를 이동한 후 [판매] 시트 탭을 클릭한다.

6. A지점인 B3:D13 셀 범위를 드래그하여 선택한 후 [추가] 단추를 클릭한다.

7. B지점인 F3:H13 셀 범위를 드래그하여 선택한 후 [추가] 단추를 클릭한다.

8. C지점인 J3:L13 셀 범위를 드래그하여 선택한 후 [추가] 단추를 클릭한다.

9. [모든 참조 영역]에 참조 주소가 모두 추가되면 [사용할 레이블]에서 ‘첫 행’과 ‘왼쪽 열’의 확인란을 모두 선택한다.

10. ‘원본 데이터에 연결’의 확인란은 선택이 해제되어 있는 상태에서 [확인] 단추를 클릭한다.

11. 통합 결과가 나타나면 새 워크시트의 시트 탭을 더블 클릭한다.

12. ‘판매통합’을 입력하고 <Enter> 키를 누른다.

06. [E5] 셀과 [F5] 셀에 HLOOKUP 함수를 사용하여 작업자코드에 대한 기술등급과 월급여를 반환하는 수식을 만드시오. [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. 통합 문서의 수식 계산이 수동으로 되고 최대 반복 횟수가 30으로 반복 계산되도록 설정하시오.

(실전3-07.xlsx) [Hint: 수식 계산 옵션]

1. [파일] 탭-[옵션]을 클릭한다.

2. [Excel 옵션] 대화 상자에서 [수식] 범주를 클릭한다.

3. [계산 옵션]-[통합 문서 계산] 구역에서 ‘수동’ 옵션을 선택한다.

4. ‘반복 계산 사용’ 확인란을 선택한다.

5. [최대 반복 횟수]를 ‘30’으로 수정하고 [확인] 단추를 클릭한다.

08. 선택된 셀에 열 너비를 15로 지정하고 3방향 화살표(컬러) 아이콘 집합 규칙을 적용하는 새 매크로를 기록하시오. 이 매크로의 이름은 ‘강조’로, ‘누적이용횟수 강조’라는 설명과 Ctrl +T 바로 가기 키를 지정하시오. 그리고 누적 이용횟수 열의 값에 이 매크로를 적용하시오.

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

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

2. [매크로 기록] 대화 상자에서 [매크로 이름]에 강조를 입력한다.

3. 바로 가기 키에 t를 입력한다.

4. [설명]에 ‘누적이용횟수 강조’를 입력하고 [확인] 단추를 클릭한다.

5. [홈] 탭-[셀] 그룹-[서식]-[열 너비] 명령을 클릭한다.

6. [열 너비] 대화 상자의 [열 너비]에 15를 입력하고 [확인] 단추를 클릭한다.

7. [홈] 탭-[스타일] 그룹-[조건부 서식]-[아이콘 집합]에서 ‘3방향 화살표(컬러)’를 클릭한다.

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

9. H4 셀을 클릭하고 <Ctrl+Shift+↓> 키를 눌러 데이터 범위를 선택한다.

10. 매크로 실행 바로 가기 키인 <Ctrl+t> 키를 누른다.

09. [C10] 셀이 참조되는 모든 셀을 추적하시오.

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

1. C10 셀을 선택한다.

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

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

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

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

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

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

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

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

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

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

11. [계약서] 워크시트에서 문서 종류 선택 그룹 상자 안의 견적서 옵션 단추 오른쪽 빈 영역에 ‘계약서’ 텍스트 내용의 옵션 단추(양식 컨트롤)를 삽입하시오. 그리고 옵션 단추를 [G2] 셀에 연결하시오. (참고: 다른 기본 설정은 모두 그대로 적용하시오.)

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

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

2. 문서 종류 선택 그룹 상자 내의 오른쪽 빈 공간에 드래그하여 그린다.

3. 옵션 단추가 삽입되면 계약서를 입력한다.

4. 옵션 단추 위에서 마우스 오른쪽 단추를 클릭해 [컨트롤 서식] 메뉴를 클릭한다.

5. [컨트롤 서식] 대화 상자의 [컨트롤] 탭에서 [셀 연결]에 커서를 이동한 후 G2 셀을 클릭하고 [확인] 단추를 클릭한다.

12. [계약서] 워크시트에서 [C6] 셀에 IF 함수를 사용하여 G2 셀에 값이 1이면 ‘견적서’를 아니면 ‘계약서’를 반환하는 함수를 계산하시오.

(실전3-12.xlsx) [Hint: IF 함수]

1. C6 셀을 선택한다.

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

3. IF [함수 인수] 창에서 [Logical_test]를 G2=1, [Value_if_true]는 “견적서”, [Value_if_false]는 “계약서”를 입력하고 [확인] 단추를 클릭한다.

※ 수식 =IF(G2=1,“견적서”,“계약서”)를 직접 작성해도 된다.

13. [계약서] 워크시트에서 [H12] 셀의 스핀 단추가 [H12] 셀의 값이 1 ~ 50 사이의 수로 1씩 증가하도록 변경하시오. (참고: 다른 기본 설정은 모두 그대로 적용하시오.)

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

1. H12 셀의 스핀 단추 위에서 마우스 오른쪽 단추를 클릭해 [컨트롤 서식] 메뉴를 클릭한다.

2. [컨트롤 서식] 대화 상자의 [컨트롤] 탭에서 [최소값]은 ‘1’로 설정한다.

3. [최대값]은 ‘50’으로 설정한다.

4. [증분 변경]은 ‘1’로 설정한다.

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

14. 변경 내용이 40일 동안 저장되도록 현재 통합 문서를 공유하시오.

(실전3-14.xlsx, 급여현황.xlsx) [Hint: 통합 문서 공유]

1. [검토] 탭-[변경 내용] 그룹-[통합 문서 공유] 명령을 클릭한다.

2. [통합 문서 공유] 대화 상자의 [편집] 탭에서 ‘여러 사용자가 동시에 변경할 수 있으며 통합 문서 병합도 가능’ 확인란을 선택한다.

3. [고급] 탭에서 [변경 내용 저장 기간]을 ‘40’일로 수정하고 [확인] 단추를 클릭한다.

4. 이 작업으로 통합 문서가 저장될 수 있다는 메시지 창이 나타나면 [확인] 단추를 클릭한다.

15. [항공료] 워크시트의 데이터를 사용하여 새 워크시트에 출발도시별 구분별 일반석성인의 인원수를 값으로 표시하는 피벗 테이블을 만드시오. 그리고 새 워크시트의 이름을 ‘인원수분석’으로 변경하시오. (실전3-15.xlsx) [Hint: 피벗 테이블]

1. [항공료] 시트 탭을 클릭한다.

2. 데이터베이스 내에 셀 포인터를 이동한다.

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

4. [피벗 테이블 만들기] 대화 상자에서 피벗 테이블 보고서를 넣을 위치를 ‘새 워크시트’로 선택하고 [확인] 단추를 클릭한다.

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

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

7. ‘일반석성인’ 필드를 [값]으로 드래그한다.

8. [피벗 테이블 필드 목록] 작업창의 [값] 영역에서 ‘합계 : 일반석성인’ 필드의 목록 단추를 클릭하여 [값 필드 설정] 메뉴를 클릭한다.

9. [값 필드 설정] 대화 상자의 [값 요약 기준] 탭에서 ‘개수’를 선택하고 [확인] 단추를 클릭한다.

10. 피벗 테이블 이외의 임의의 셀을 클릭한다.

11. 새 워크시트의 시트 탭을 더블 클릭한다.

12. ‘인원수분석’을 입력하고 <Enter> 키를 누른다.

16. 차트에 하반기 열의 값이 세로 막대 차트에 포함되도록 계열을 추가하시오.

(실전3-16.xlsx, 급여현황.xlsx) [Hint: 데이터 계열 추가]

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

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

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

4. [계열 편집] 대화 상자에서 [계열 이름]에 커서를 이동하고 D2 셀을 클릭한다.

5. [계열 값]의 내용을 선택하고 <Delete> 키를 눌러 모두 삭제한다.

6. D3:D12 셀 범위를 드래그하고 [확인] 단추를 클릭한다.

7. 차트에 계열이 추가되면 [확인] 단추를 클릭한다.

17. 값이 ‘아니오’인 예 또는 아니오 서식의 ‘공개’라는 이름의 사용자 지정 문서 속성을 설정하시오.

(실전3-17.xlsx) [Hint: 통합 문서 속성]

1. [파일] 탭-[정보]를 클릭한다.

2. [속성]-[고급 속성]을 클릭한다.

3. [속성] 대화 상자의 [사용자 지정] 탭으로 이동한다. [이름]에 공개를 입력한다.

4. [형식]은 ‘예 또는 아니오’를 선택한다.

5. [값]에 ‘아니오’ 옵션을 선택하고 [추가] 단추를 클릭한다.

6. [속성] 목록에 추가되면 [확인] 단추를 클릭한다.

18. 수식 계산 도구를 사용하여 [F4] 셀의 수식 오류를 정정하시오.

(실전3-18.xlsx) [Hint: 수식 계산]

1. F4 셀을 선택한다.

2. [수식] 탭-[수식 분석] 그룹-[수식 계산] 명령을 클릭한다.

3. [수식 계산] 대화 상자에서 E4 셀 주소에 밑줄이 그어진 상태에서 [계산] 단추를 클릭한다.

4. E4 셀의 참조 값이 ‘62870000’으로 표시된다.

5. $E$16 셀에 밑줄이 그어진 상태에서 [계산] 단추를 클릭한다.

6. $E$16 셀의 참조 값 ‘0’으로 표시되면 [계산] 단추를 클릭한다.

7. 값을 0으로 나눴을 때 나타나는 오류인 #DIV/0! 오류가 표시된다.

8. [닫기] 단추를 클릭한다.

9. 수식 입력줄에서 ‘$E$16’ 셀 주소를 드래그한 후 E14 셀을 클릭한다.

10. <F4> 키를 눌러 절대 참조 $E$14로 수정한다.

11. <Enter> 키를 누른다.

19. [D4] 셀에 VLOOKUP 함수를 만들어 ‘곽희나’ 사원의 실적에 해당하는 보너스를 찾으시오.

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

1. D4 셀을 선택한다.

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

3. [함수 인수] 창에서 [Lookup_value]는 C4, [Table_array]는 B9:C13, [Col_index_num]은 2, [Range_lookup]은 TRUE를 입력하고 [확인] 단추를 클릭한다.

※ 수식 =VLOOKUP(C4,B9:C13,2,TRUE)를 직접 작성해도 된다.

20. ‘특별상여율’을 10%로 변경할 수 있는 ‘인상’이라는 이름의 시나리오를 만들고 표시하시오.

(실전3-20.xlsx) [Hint: 시나리오 추가, 시나리오 표시]

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

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

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

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

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

6. [시나리오 관리자] 대화 상자에서 ‘인상’ 시나리오가 선택되어 있는 상태에서 [표시] 단추를 클릭한다.

7. 시나리오 결과가 표시되면 [닫기] 단추를 클릭한다.

21. [I5] 셀에 COUNTIFS 함수를 사용하여 체중이 40kg을 초과하는 남학생이 몇 명인지 계산하시오.

(실전3-21.xlsx) [Hint: COUNTIFS 함수]

1. I6 셀을 선택한다.

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

3. [함수 인수] 창에서 [Criteria_range1]은 G5:G49, [Criteria1]은 “>40”, [Criteria_range2]는 C5:C49, [Criteria2]는 “남”을 입력하고 [확인] 단추를 클릭한다.

※ 수식 =COUNTIFS(G5:G49,“>40”, C5:C49,“남”)을 직접 작성해도 된다.

22. [매출분석] 워크시트에서 피벗 테이블에 제품 및 사원명을 표시하도록 슬라이스를 삽입하시오. 그리고 ‘박찬식’ 사원의 사계절 핫 소스 및 필로 믹스의 값을 표시하시오.

(실전3-22.xlsx) [Hint: 슬라이서]

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

2. [피벗 테이블 도구]-[옵션] 탭-[정렬 및 필터] 그룹-[슬라이서 삽입] 명령을 클릭한다.

3. [슬라이서 삽입] 창에서 ‘사원명’ 및 ‘제품’의 확인란을 선택하고 [확인] 단추를 클릭한다.

4. [사원명] 슬라이서 창에서 ‘박찬식’을 클릭한다.

5. [제품] 슬라이서 창에서 ‘사계절 핫 소스’를 클릭한다.

6. <Ctrl> 키를 누른 채 ‘필로 믹스’를 클릭해 동시에 선택한다.

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

23. 매출 차트의 하반기 계열에 차수 3을 사용하는 이동 평균 추세선을 추가하시오.

(실전3-23.xlsx) [Hint: 추세선]

1. 차트의 하반기 데이터 계열 위에서 마우스 오른쪽 단추를 클릭하여 [추세선 추가] 메뉴를 클릭한다.

2. [추세선 서식] 대화 상자의 [추세선 옵션] 탭에서 [추세/회귀 유형]은 ‘이동 평균’을 선택한다.

3. [구간]은 ‘3’으로 설정한다.

4. [닫기] 단추를 클릭한다.

24. [D15] 셀의 변경 내용을 적용 취소하고 다른 모든 변경 내용은 적용하시오.

(실전3-24.xlsx) [Hint: 변경 내용 적용/취소]

1. [검토] 탭-[변경 내용] 그룹-[변경 내용 추적]-[변경 내용 적용/취소] 명령을 클릭한다.

2. [적용하거나 취소할 변경 내용 선택] 대화 상자에서 [확인] 단추를 클릭한다.

3. [변경 내용 적용/취소] 대화 상자에서 D15 셀의 변경 정보가 나타나면 [적용 안 함] 단추를 클릭한다.

4. D15 셀의 변경 내용이 적용 취소되면 [모두 적용] 단추를 클릭한다.

25. [계약서] 워크시트를 인쇄하는 ‘문서인쇄’ 이름의 새 매크로를 현재 통합 문서에 기록하시오. 그리고 ‘견적서 및 계약서’라는 파일 이름으로 문서에 매크로 사용 통합 문서로 저장하시오.

(실전3-25.xlsx) [Hint: 매크로 기록, 매크로 사용 통합 문서 저장]

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

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

3. [파일] 탭-[인쇄]에서 [인쇄] 단추를 클릭한다.

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

5. [파일] 탭-[저장/보내기] 메뉴를 클릭하고 [파일 형식 변경]에서 ‘매크로 사용 통합 문서(*.xlsm)’를 더블 클릭한다.

6. [다른 이름으로 저장] 대화 상자의 [파일 이름]에 ‘견적서 및 계약서’를 입력하고 [저장] 단추를 클릭한다.

26. [계약서] 워크시트에서 [G2:H3] 셀 범위에 ‘견적서 및 계약서 인쇄’라는 이름의 단추(양식 컨트롤)을 만들고 이 단추에 ‘문서인쇄’ 매크로를 할당하시오.

(실전3-26.xlsm) [Hint: 매크로 실행 단추, 양식 컨트롤]

1. 보안 경고 알림 표시줄이 나타나면 [콘텐츠 사용] 단추를 클릭한다.

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

3. G2:H3 셀 범위 위에서 드래그하여 그린다.

4. [매크로 지정] 대화 상자에서 ‘문서인쇄’ 매크로를 선택하고 [확인] 단추를 클릭한다.

5. 단추에 견적서 및 계약서 인쇄를 입력하고 다른 임의의 셀을 클릭한다.

27. 상여비율 [G5:G13] 셀 범위만 선택할 수 있고 다른 모든 셀은 선택할 수 없도록 암호를 설정하지 않고 시트를 보호하시오. (실전3-27.xlsx) [Hint: 셀 잠금 해제, 시트 보호]

1. G5:G13 셀 범위를 선택한다.

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

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

4. [시트 보호] 대화 상자의 [워크시트에서 허용할 내용]에서 ‘잠긴 셀 선택’ 확인란의 선택을 해제한다.

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

6. G5:G13 셀만 선택 및 편집이 가능하고 이외의 셀은 셀 선택조차 안 된다.

28. 매출 차트를 ‘스타일 4’로 변경하고 범례를 위쪽에 배치하고 그림 영역에 ‘황록색, 강조색 3, 80% 더 밝게’ 도형 채우기를 지정하시오. 이 차트를 ‘분석차트’라는 이름의 차트 서식 파일로 저장하시오. (실전3-28.xlsx) [Hint: 빠른 차트 스타일, 차트 레이아웃, 도형 채우기, 차트 서식 파일 저장]

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

2. [차트 도구]-[디자인] 탭-[차트 스타일] 그룹에서 [자세히] 명령을 클릭하여 ‘스타일 4’를 선택한다.

3. [차트 도구]-[레이아웃] 탭-[레이블] 그룹-[범례] 명령을 클릭해 ‘위쪽에 범례 표시’를 선택한다.

4. 차트의 그림 영역을 클릭해 선택한다.

5. [차트 도구]-[서식] 탭-[도형 스타일] 그룹-[도형 채우기] 명령을 클릭해 [테마 색]에서 ‘황록색, 강조 3, 80% 더 밝게’를 선택한다.

6. [차트 도구]-[디자인] 탭-[종류] 그룹-[서식 파일로 저장] 명령을 클릭한다.

7. [차트 서식 파일 저장] 대화 상자에서 [파일 이름]에 ‘분석차트’를 입력하고 [저장] 단추를 클릭한다.

29. 수식 오류가 감지되면 노랑으로 표시되도록 Excel 옵션을 설정하시오. 그리고 일치하지 않는 수식이 참조되는 모든 셀을 추적하시오.

(실전3-29.xlsx) [Hint: 오류 표시 색, 오류 검사, 참조되는 셀 추적]

1. [파일] 탭-[옵션]을 클릭한다.

2. [Excel 옵션] 대화 상자의 [수식] 범주에서 [오류 검사] 항목의 [오류 표시 색] 아이콘을 클릭해서 ‘노랑’을 선택한다.

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

4. [수식] 탭-[수식 분석] 그룹-[오류 검사] 명령을 클릭한다.

5. G14 셀이 일치하지 않는 수식이 있는 셀로 선택되면 [닫기] 아이콘을 클릭한다.

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