ITQ 엑셀 문제풀이 TIP ::: 200 8 년 7 월 기준 :::

1. ITQ 엑셀 문제풀이 TIP::: 2008년 7월 기준 ::: 한국생산성본부 ITQ센터 이 자료는 한국생산성본부에서 제작한 과목TIP입니다. 본부의 허가 없이 도용을 금합니다.

2. 데이터를 취합, 가공하여 원하는 정보를 추출하는 정보관리능력을 향상시킵니다. ITQ 과목소개 정보가공 및 관리 기초문서서작성 설득과 변화를 위한 문서작성 신뢰성,전문성있는 자료 추출 5과목취득 파워 포인트 Value Creator 한글 인터넷 엑셀 액세스 ITQ의 5 과목 취득자는 정보사회의 진정한 가치창조자 입니다. www.itq.or.kr

3. Contents • ITQ 엑셀 과목소개 • ITQ 엑셀 출제기준 • 엑셀 시험의 구조 • 엑셀 체크포인트 • 기능별 문제풀이 TIP • 별첨 : 자주 틀리는 항목 정리

4. ITQ 엑셀 소개 Excel 이란? • 엑셀(Excel)이란마이크로소프트사에서 개발한 스프레드시트(Spread Sheet)입니다. 스프레드시트는 말 그대로 여러 장의 종이를 펼쳐놓은 작업 공간을 말한다. • 엑셀을 이용하면 무슨 일을 할 수 있을까?엑셀을 이용해서 할 수 있는 일들은 일일이 나열할 수 없을 정도로 많을 뿐만 아니라 그 활용의 폭도 넓습니다. 사람이 계산하기 힘든 수백, 수천개의 데이터도 간단히 단추 하나만 클릭함으로써 정확하게 계산할 수 있기 때문이다.가정에서는 가계부를 비롯해서 차량관리, 신용카드 관리, 주소록 등을 효율적으로 자동화 시킬 수 있으며, 학교에서는 성적관리, 학생관리, 그리고 기업에서는 각종 세무 계산, 견적서, 보고서 등을 쉽게 만들어서 관리할 수 있다. 어려운 표 작성은 물론 차트기능을 이용하여 비교할 데이터도 보기 쉽게 나타낼 수 있다.

5. ITQ 엑셀 소개 (출제기준) ITQ 엑셀 출제기준

6. ITQ 엑셀 활용 예 엑셀 활용 예

7. 엑셀 시험의 구조

8. [제 1 작업] 표 서식 작성 및 값 계산 표를 작성하고, 함수를 이용하여 수식을 작성한다. 수험자 유의사항 / 답안 작성 요령 답안을 작성하기 전에 반드시 알아야 할 내용이다. 꼼꼼히 읽고 시험을 시작한다. 주요 내용 1. (A:)에 ‘수험번호-성명.xls’의 하나의 파일로만 저장. 답과 관련 없는 파일이 저장된 경우 실격처리함. 2. 답안 시트의 이름은 “제1작업” ~ “제4작업”이어야만 함 ITQ 엑셀 소개 (시험구조) 시험 구조

9. [제 2 작업] 목표값 찾기 및 필터 기본 데이터를 이용해 데이터를 필터링하고 목표값 기능을 이용 해 원하는 결과값을 구한다. [제 4 작업] 그래프 차트 기능을 이용해 기본 데이 터를 보기 쉬운 그래프로 만든 다. [제 3 작업] 피벗테이블 / 부분합 피벗테이블을 이용해 중요한 필드를 추출, 보기 쉬운 결과물을 만들고 부분합을 이용해 특정 필드에 대한 합계, 평균 등을 구한다. ITQ 엑셀 소개 (시험구조) 시험 구조

10. ITQ-엑셀 체크 포인트

11. 체크 포인트 (0. 전체구성) 전체 구성 – 주요 평가내용 ▶ 평가기능 개관 엑셀을 이용하여 표 서식, 계산 및 함수, 데이터베이스, 차트작성 능력을 평가 ▶ 중요 포인트 - 시트명과 작업이 순서대로 되어있는지 - 작업 외에 시트가 삽입되어 있지 않은지 - 모든 작업시트의 A열의 너비가 1 로 조절되어 있는지 - 각 시트의 작업이 하나의 파일로 저장되어 있는지

12. 체크 포인트 (1. 제 1 작업) 제 1 작업 – 주요 평가내용 ▶ 평가기능 개관 조건에 따른 서식과 다양한 함수 사용 능력 등을 종합적으로 평가 (배점 240점) ▶ 주요 출제 유형 • 데이터 입력 / 셀 서식 / 열 너비 조정 등 • 메모 / 유효성 검사 기능 • 윗주 / 특수 문자 / 워드아트 / 그림 / 도형 / 카메라 기능 • 다양한 함수의 활용 / 조건부 서식 기능

13. 체크 포인트 (2. 제 2 작업) 제 2 작업 – 주요 평가내용 유형1 유형2 ▶ 평가기능 개관 유형 1 필터 및 서식 : 1 작업의 데이터를 이용하여 고급(/자동)필터 능력과 서식 적용 능력을 평가 유형 2 목표값 찾기 및 필터 : 1 작업의 데이터를 이용하여 목표값을 구하고 고급(/자동)필터 능력을 평가

14. 체크 포인트 (3. 제 3 작업) 제 3 작업 – 주요 평가내용 유형1 유형2 ▶ 평가기능 개관 유형 1 정렬 및 부분합 작업의 데이터를 이용하여 특정 필드별로 분류, 계산 능력을 평가 유형 2 피벗테이블작업의 데이터를 이용하여 특정 항목을 추출하여 요약 분석 능력 평가

15. 체크 포인트 (4. 제 4 작업) 제 4 작업 – 주요 평가내용 ▶ 평가기능 개관 제1작업 데이터를 이용하여 출력형태와 동일한 차트를 작성할 수 있는지 평가

16. 별첨. 기능별 풀이 방법

17. 문제보기-표 서식 유형별 풀이방법 (표 서식) [제 1 작업] 표 서식 작성– 문제풀이 Tip

18. 유형별 풀이방법 (표 서식) 1) 표 서식 설정하기 ① 모든 셀을 선택한 후 [셀 서식]-[맞춤]-가로·세로(가운데)설정한다. ② [셀 서식]-[글꼴]-돋움, 보통, 10pt 설정한다.

19. 유형별 풀이방법 (표 서식) ③ 그리기 도구모음의 워드아트를 삽입하고 글꼴(돋움,32pt, 굵게)과 도형을 설정한다.

20. 유형별 풀이방법 (표 서식) ④ 그리기 도구모음의 [사용자 정의]-[명령]-[도구]-카메라를 드래그하여 표시줄에 등록시킨 후 다음과 같이 임의의 셀에 결재란을 작성하여 범위 설정한 후 카메라 클릭, 명시된 위치에 삽입한다. ※ 카메라기능을 이용하여 삽입한 후에는 반드시 원본을 삭제하여야 한다.

21. 유형별 풀이방법 (표 서식) ⑤ 「B5:J5, B16:C17」영역을 ‘연녹색’으로 채우기 한다. ⑥ 「E6:F15」를 범위 설정하여 [셀 서식]-[표시 형식]-[사용자 정의]에서「 #,##0 “개”」 를 입력한다.

22. 유형별 풀이방법 (표 서식) ⑦ 「B6:B15」셀을 범위 설정하여 [셀 서식]-[맞춤]-가로(균등 분할)설정한다. ⑧ 「B6:B15」셀을 범위 설정하여 ‘이름상자’에 ‘품명’이라고 입력한 후 엔터를 친다.

23. 문제보기-값 계산 유형별 풀이방법 (값 계산) 1) 수식 작성 ① 출고단가(단위:원) = ROUND(G6*(1+$C$4),-2). ② 입고량 순위 = RANK(E6,$E$6:$E$15) ③ 제조일 = DATE(MID(D6,5,2)+2000,MID(D6,7,2),MID(D6,9,2)) ④ 딸기제품수 = COUNTIF(품명,"*딸기*") & " 개" ⑤ 입고단가 총액 = SUM(G6:G15) ⑥ 출고량이 가장 큰 품명 = INDEX(품명,MATCH(MAX(F6:F15),F6:F15,0))

24. 유형별 풀이방법 (값 계산) 2) 조건부 서식 ①「G6:H15 」를 범위 설정하여 [서식]-[조건부 서식]에서 다음과 같이 설정한다. ② ‘서식’ 버튼을 클릭하여 글자색상을 파랑으로 선택한다.

25. 제1작업 – 표, 함수 ▣ 제1작업 문제의 개관/출제범위

26. 제1작업 – 표, 함수 ▣ 제1작업 출제함수 - 날짜/시간

27. 제1작업 – 표, 함수 ▣ 제1작업 출제함수 – 수학/삼각

28. 제1작업 – 표, 함수 ▣ 제1작업 출제함수 - 통계

29. 제1작업 – 표, 함수 ▣ 제1작업 출제함수 – 찾기/참조

30. 제1작업 – 표, 함수 ▣ 제1작업 출제함수 - 데이터베이스

31. 제1작업 – 표, 함수 ▣ 제1작업 출제함수 – 논리값, 정보, 텍스트

32. 문제보기-필터 및 서식 유형별 풀이방법 (필터 및 서식) [제 2 작업] 필터 및 서식– 문제풀이 Tip

33. 유형별 풀이방법 (필터 및 서식) 1) 고급 필터 ① “제1작업” 시트의「B5:G15」영역을 복사하여 “제2작업” 시트의「B2」셀을 선택하여 모두 붙여넣기한 후 「B14:C15」에 다음과 같이 조건을 입력한다. ② [데이터]-[필터]-[고급 필터]를 다음과 같이 설정한다. ③ 다음과 같이 분류와 제품코드 필드를 범위 설정하여 ‘삭제 - 셀을 왼쪽으로 밀기’ 한다(또는 해당하는 필드명을 나열하고 필드명을 블록씌운 후 필터 결과를 표시한다.)

34. 유형별 풀이방법 (필터 및 서식) 2) 자동 서식 ① [서식]-[자동 서식]-[기본1형] 옵션 항목의 글꼴을 제외하고 확인 한다.

35. 문제보기-정렬과 부분합 유형별 풀이방법 (정렬과 부분합) [제 3 작업] 정렬과 부분합– 문제풀이 Tip

36. 유형별 풀이방법 (정렬과 부분합) 1) 선택하여 붙여넣기(값과 서식만 붙여넣기 할 경우) ① ‘제1작업’ 시트의「B5:G15」영역을 복사하여 “제3작업” 시트「B2」셀에 [편집]- [선택하여 붙여넣기]- [값] 확인한다. ② [편집]- [선택하여 붙여넣기] – [서식] 확인한다.

37. 유형별 풀이방법 (정렬과 부분합) 2) 정렬 ① 출력형태를 확인하여 그룹화 할 부분을 기준으로 정렬한다. ②「B2:G12」셀을 범위 설정하여 [데이터]- [정렬]- 첫째기준을 품명(내림차순)으로 정렬한다. ※ 정렬이 사용자 정의일 경우는 도구→옵션→사용자 지정 목록을 이용하여 목록을 만든 후 정렬한다.

38. 요약이 위에 있을 경우 해제함 유형별 풀이방법 (정렬과 부분합) 3) 부분합 ① 표 안에 셀을 선택한 후 [데이터]-[부분합]에서 ‘그룹화항목-분류, 함수-합계, 계산항목-입고,출고, 데이터 아래에 요약표시 해제’를 설정한다. ② 다시 한번 부분합에서 다음과 같이 ‘함수-최대값, 계산 항목-입고단가’를 설정한다. ※ 주의사항 : 두번째 부분합을 설정할 경우 “새로운 값으로 대치”에 체크를 반드시 해제하여야 한다.

39. 유형별 풀이방법 (정렬과 부분합) 4) 윤곽지우기 ① [데이터]-[그룹 및 윤곽 설정] – 윤곽지우기 한다.

40. 문제보기-그래프 유형별 풀이방법 (그래프) [제 4 작업] 그래프– 문제풀이 Tip

41. 차트 영역 차트 제목 보조 Y(값) 축 제목 데이터 리이블(값표시) 범례 최대값 그림 영역 도형 Y(값) 축 보조 Y(값) 축 최소값 X(항목) 축 유형별 풀이방법 (그래프) ▣ 그래프 출력형태 및 명칭

42. 유형별 풀이방법 (그래프) 1) 차트 만들기 ① 제1작업 시트의 품명, 입고, 출고, 입고단가를 범위 설정하여 [삽입]-[차트]를 선택한다. ② 차트 마법사 – 1단계- 이중 축 혼합형을 선택하고 다음 한다.

43. 유형별 풀이방법 (그래프) ③ 차트 마법사 – 2단계 – 데이터 범위와 계열위치(열)를 확인하고 다음 한다. ④ 차트 마법사 – 3단계 – 차트 제목(2006년 7월 제품 입출고 현황)과 보조 Y값 축(단가:원)을 입력하고 다음 한다. ⑤ 차트 마법사 – 4단계 - 차트 위치를 ‘새로운 시트로’ 선택하고 마침 한다.

44. 유형별 풀이방법 (그래프) 2) 시트 이름 바꾸기 ‘Chart1’을 ‘제3작업’뒤로 이동하고 [서식]-[시트]-[이름 바꾸기] (또는 시트명에서 더블클릭)하여 시트 명을 ‘제4작업’으로 변경한다.

45. 유형별 풀이방법 (그래프) 3) 영역 서식 적용하기 ① 차트 영역을 선택하여 글꼴(돋움, 보통, 12pt)을 설정한다. ② 그림 영역을 선택하여 채우기 효과(그라데이션-기본 설정색(양피지), 음영스타일-가운데에서)를 설정한다.

46. 유형별 풀이방법 (그래프) 4) 제목 서식 설정하기 ① 차트 제목 영역을 선택하여 글꼴(돋움, 보통, 12pt)과 테두리, 그림자를 설정한다.

47. 유형별 풀이방법 (그래프) 5) 범례 설정하기 ① ‘범례’ 영역을 선택한 후 드래그로 이동하여 출력형태와 같이 조절점을 이용하여 크기를 조절한다. ② ‘보조 값 축 제목’ 영역을 선택한 후 이동하여 [축 제목 서식] – 방향(0˚)을 설정한다.

48. 유형별 풀이방법 (그래프) 6) 데이터 계열 서식 설정하기 ① ’입고’ 계열을 선택하여 [데이터 계열 서식]-[채우기 효과]-[무늬]-’넓은 하향 대각선’을 선택 확인한다. ② ‘냉동딸기’의 ‘입고단가(단위:원)’를 선택하여 [데이터 요소 서식]-’값 표시’ 선택하여 확인한다.

49. 유형별 풀이방법 (그래프) 7) 보조 축 최대값 변경하기 ① ‘보조 값 축’의 [축 서식] –‘최대값’을 10000을 변경 확인한다. ☞ 주의사항 : 모든 시트의 ‘A’열의 너비가 ‘1’로 지정되어 있는지, 시트의 순서가 정확한지 확인한다.

50. ※별첨 자주 틀리는 항목 정리