2010 년 2 회컴퓨터활용능력 1 급실기 E 형 프로그램명 EXCEL 제한시간 45분 수험번호 : 성명 : 1 급 E 형 < 유의사항 > 인적사항누락및잘못작성으로인한불이익은수험자책임으로합니다. 화면에암호입력상자가나타나면아래의암호를입력해야합니다. 암호 : 652*25 작성된답안의파일명은지정된경로및파일명을변경하지마시고저장해야합니다. 이를준수하지않으면실격처리됩니다. < 답안파일명예 > 2003 버전 : C:\OA\ 수험번호 8자리.xls 2007 버전 : C:\OA\ 수험번호 8자리.xlsm ( 확장자에유의하시오.) 외부데이터위치 : C:\OA\ 파일명 별도지시사항이없는경우, 다음과같이처리하면실격처리됩니다. 제시된시트순서를임의로변경한경우 제시된시트이름을임의로변경한경우 제시된시트를임의로추가또는삭제한경우 답안은반드시문제에서지시또는요구한셀에입력하여야하며, 수험자가임의로셀의위치를변경하여입력한경우에는채점대상에서제외됩니다. 아울러지시하지않은셀의이동, 수정, 삭제, 변경등으로인해셀의위치및내용이변경된경우에도관련문제모두채점대상에서제외됩니다. 도형및차트의개체가중첩되어있거나, 동일한계산결과시트가복수로존재할경우에는해당개체나시트는채점대상에서제외됩니다. 별도지시사항이없는경우, 주어진각시트의설정값또는기본설정값 (Default) 으로처리하십시오. 저장시간은별도로주어지지아니하므로제한된시간내에저장을완료해야합니다. 본문제에사용된용어는 Microsoft Office 2003 기준으로작성되었습니다. 대한상공회의소 126_ 컴퓨터활용능력 1 급실기
2010 년 7 월 18 일시행 문제 1 기본작업 (25 점 ) 주어진시트에서다음의과정을수행하고저장하시오. 1. 기본작업-1 시트에서 외부데이터가져오기 기능을사용하여 < 수강현황.mdb> 의데이터를다음조건에따라 [B2] 셀부터표시하시오. (15점 ) < 접수현황 > 테이블의데이터를 이름, 분류, 강사명, 강사구분, 강의요일, 정원, 수강료 열의순서로가져오시오. `강사명` 이 우정민 이거나 한명구 이고, 분류 가 학생 인행만을대상으로하시오. 이름, 강사명, 강사구분 을기준으로오름차순으로정렬하시오. 2. 기본작업-2 시트에서다음과같이 고급필터 를수행하시오. (5점 ) ` 강사명` 이 구기자 이거나, 강사명 이 사오정 또는 오동추 이고 분류 가 어린이 인데이터를표시하시오. 조건은 [B28:J33] 영역내에알맞게표시 결과는 [B34] 셀부터표시 3. 기본작업-2 시트에서다음과같이 조건부서식 을설정하시오. (5점 ) [B3:J26] 영역에서 강사요일 이 2개이상이거나 접수일 과 변경일 사이의일수가 10 이상인행전체에대해서글꼴색 파랑, 굵게 가적용되는조건부서식을작성하시오 ( `강사요일 이 2개이상인경우콤마로구분하여입력 ). 단, 조건은수식으로작성하시오 (OR, LEN, DAYS360 이용 ). 문제 2 계산작업 (30 점 ) 계산작업 시트에서다음의과정을수행하고저장하시오. 1. [ 표 1] 의연이율, 기간 ( 년 ), 월납입액을이용하여현재가치를 [C6:H6] 영역에계산하여표시하시오. (6 점 ) 현재가치가 20,000,000 이상이면 한도초과, 그렇지않으면현재가치를반올림하여천의자리까지표시 IF, PV, ROUND 함수사용 2. [ 표 2] 를이용하여강사구분별강사인원을 [ 표 3] 의 [C27:C30] 영역에계산하여표시하시오. (6 점 ) SUM 함수를사용한배열수식 3. [ 표2] 를이용하여경력이 5 이상인강사들의강사구분별수강료평균을 [ 표3] 의 [D27:D30] 영역에계산하여표시하시오. (6점 ) AVERAGE, IF 함수를사용한배열수식 4. [ 표 2] 를이용하여경력이가장많은강사명을 [ 표 3] 의 [F27] 셀에계산하여표시하시오. (6 점 ) MATCH, INDEX, MAX 함수사용 2010 년 2 회 1 급 ( 엑셀 ) E 형 _127
2010 년 2 회컴퓨터활용능력 1 급실기 ( 엑셀 ) E 형 5. 접수율을계산하는사용자정의함수 fn 접수율 을작성하여계산을수행하시오. (6 점 ) fn 접수율 은정원과수강인원을인수로받아값을되돌려줌 수강인원 / 정원 을계산한값이 1보다크면 정원초과, 1이면 접수완료, 그렇지않으면 수강인원 / 정원 의값을표시하시오 (SELECT CASE 이용 ). fn접수율 함수를이용하여 [J10:J23] 영역에접수율을계산하시오 ( 백분율스타일적용 ). Public Function fn접수율 ( 정원, 수강인원 ) End Function 문제 3 분석작업 (20 점 ) 주어진시트에서다음의과정을수행하고작업하시오. 1. 분석작업 -1 시트에서다음과같이피벗테이블을작성하시오. (10 점 ) 피벗테이블의외부데이터를이용하여 < 수강현황.mdb> 의 < 접수현황 > 테이블에서수강료가 100,000 이상인데이터의 이름, 분류, 강사구분, 강의요일, 정원, 수강료 열만이용하시오. 행에 강의요일, 열에 분류, 데이터에 이름 의개수, 정원 의합계를표시하고위치는 < 그림 > 을참조하여작성하시오. 정원 의행의비율을나타내는 비율 계산필드를추가하시오. < 그림 > 과같이 강의요일 을 월, 수, 금 과 화, 목 으로그룹을지정한후보고서서식 6 을지정하시오. 작업이완성된그림이며부분점수없음 128_ 컴퓨터활용능력 1 급실기
2010 년 7 월 18 일시행 2. 분석작업 -2 시트에대하여다음과같은기능을수행하는매크로를작성하고실행하시오. ( 각 5 점 ) 1 데이터표기능을이용하여 [D9:G14] 영역에할인율과과목수에따른수강료를계산하는매크로를생성하고, 매크로이름은 수강료계산 으로정의하시오. 수강료는 기본수강료 과목수 (1-할인율 ) 로계산 2 수강료계산 매크로도형은 모서리가둥근직사각형 (` `) 으로만들어지정하며, 도형의텍스트를 수강료계산하기 로입력하여 [F2:G3] 영역에위치시키시오. 도형의채우기색을 노랑 으로지정하고텍스트맞춤을 가로가운데, 세로가운데 로지정하시오. 매크로는도형과연결되어야하며, 셀포인터의위치에관계없이매크로가실행되어야정답으로인정됨 문제 4 기타작업 (25 점 ) 주어진시트에서다음의과정을수행하고저장하시오. 1. 기타작업-1 시트에서다음의지시사항및그림에따라차트를수정하시오. ( 각 2점 ) 차트는반드시문제에서제공한차트를사용하여야하며, 신규로차트작성시 0점처리됨 1 수강인원 계열을추가한후해당계열의데이터레이블을글꼴색 빨강, 글꼴스타일 굵게 로지정하시오. 2 차트의첫째조각의각을 15, 도넛구멍크기를 20으로지정하시오. 3 차트제목을 < 그림 > 과같이지정한후글꼴크기 14, 그림자를지정하시오. 4 범례위치를 < 그림 > 과같이지정하시오. 5 차트영역서식은그림자를설정하여둥글게표시하시오. 2010 년 2 회 1 급 ( 엑셀 ) E 형 _129
2010 년 2 회컴퓨터활용능력 1 급실기 ( 엑셀 ) E 형 2. 기타작업-2 시트에서다음과같은작업을수행하고저장하시오. ( 각 5점 ) 1 < 등록 > 버튼을클릭하면 < 학원생등록 > 폼이나타나고, 폼이초기화되면 `초등학생`, `중학생`, `고등학생` 이분류 (cmb분류) 콤보상자에목록으로추가되도록프로시저를작성하시오 (With, Additem 이용 ). 2 분류 (cmb분류) 콤보상자에서 초등학생 을선택하면 [J6:M8] 영역을, 중학생 을선택하면 [J10:M15] 영역을, 고등학생 을선택하면 [J17:M22] 영역을강좌 (list강좌) 목록상자에표시되록프로시저를작성하시오 (Select, Change 이용 ). 3 < 학원생등록 > 폼의 < 입력 (cmd입력)> 버튼을클릭하면폼에입력된데이터를시트의표안에추가되도록프로시저를작성하시오 (List, Listindex 이용 ). 130_ 컴퓨터활용능력 1 급실기
2010 년 2 회컴퓨터활용능력 1 급실기 ( 엑셀 ) 정답및 E 형 문제 1 기본작업 정답 01. 외부데이터가져오기 _ 참고 : Section 01 외부데이터 28 쪽 결과 열선택 조건지정 1 조건지정 2 정렬 2010 년 2 회 1 급 ( 엑셀 ) E 형 _131
2010 년 2 회컴퓨터활용능력 1 급실기 ( 엑셀 ) E 형정답및 02. 고급필터 _ 참고 : Section 06 고급필터 66 쪽 결과 고급필터 대화상자 03. 조건부서식 _ 참고 : Section 04 조건부서식 52 쪽 결과 132_ 컴퓨터활용능력 1 급실기
2010 년 7 월 18 일시행 조건지정 문제2 1 2 3 4 5 계산작업 정답 1. 현재가치 (C6) : =IF( PV(C3/12,C4 * 12,C5)>=20000000, `한도초과`,ROUND( PV(C3/12,C4 * 12,C5 ),-3) ) 2. 강사구분별강사인원 (C27) : {=SUM( ($E$10:$E$23=B27) * 1 ) } 3. 강사구분별수강료 (D27) : {=AVERAGE( IF( ($E$10:$E$23=B27) * ($C$10:$C$23>=5),$H$10:$H$23 ) )} 4. 경력이가장많은강사명 (F27) : =INDEX( B10:J23,MATCH ( MAX(C10:C23),C10:C23,0 ),1 ) 5. 접수율 (J10) : =fn접수율 (G10,I10) 2010 년 2 회 1 급 ( 엑셀 ) E 형 _133
2010 년 2 회컴퓨터활용능력 1 급실기 ( 엑셀 ) E 형정답및 Public Function fn접수율 ( 정원, 수강인원 ) Select Case 수강인원 / 정원 Case Is > 1 fn 접수율 = 정원초과 Case Is = 1 fn 접수율 = 접수완료 Case Else fn 접수율 = 수강인원 / 정원 End Select End Function 문제3 분석작업 정답 01. 피벗테이블 _ 참고 : Section 12 피벗테이블 132 쪽 조건지정 134_ 컴퓨터활용능력 1 급실기
2010 년 7 월 18 일시행 레이아웃 ` 보고서서식 6` 을지정하면열에위치한 ` 분류 ` 가행으로이동합니다. 비율 필드추가하기 피벗테이블 도구모음의 [ 피벗테이블 ] [ 수식 ] [ 계산필드 ] 를선택하여다음과같이지정한후 < 추가 > 를클릭한다음 < 닫기 > 를클릭한다. 피벗테이블에추가된 비율 필드의바로가기메뉴에서 [ 필드설정 ] 을선택한다. 피벗테이블필드 대화상자에서 < 옵션 > 단추를클릭한후데이터표시형식을 행방향의비율 로지정하고 < 확인 > 을클릭한다. 2010 년 2 회 1 급 ( 엑셀 ) E 형 _135
2010 년 2 회컴퓨터활용능력 1 급실기 ( 엑셀 ) E 형정답및 02. 매크로작성 _ 참고 : Section 17 매크로 177 쪽 수강료계산 매크로실행 수강료 (C5, C8) : =C2*C3*(1-C4) 표 대화상자 136_ 컴퓨터활용능력 1 급실기
2010 년 7 월 18 일시행 문제4 기타작업 정답 01. 차트수정 _ 참고 : Section 18 차트 192 쪽 첫째조각의각도및도넛구멍크기지정 02. 프로시저작성 _ 참고 : Section 18 프로시저 214 쪽 1 폼활성화및초기화프로시저 폼활성화프로시저 Private Sub 등록 _Click( ) 학원생등록.Show End Sub 폼초기화프로시저 Private Sub UserForm_Initialize( ) With cmb분류.additem 초등학생.AddItem 중학생.AddItem 고등학생 End With End Sub 2010 년 2 회 1 급 ( 엑셀 ) E 형 _137
2010 년 2 회컴퓨터활용능력 1 급실기 ( 엑셀 ) E 형정답및 2 분류 (cmb 분류 ) 콤보상자에기능구현하기 Private Sub cmb분류 _Change( ) Select Case cmb분류 Case 초등학생 list강좌.rowsource = J6:M8 Case 중학생 list강좌.rowsource = J10:M15 Case Else list강좌.rowsource = J17:M22 End Select End Sub 3 < 입력 > 버튼에기능구현하기 Private Sub cmd입력 _Click( ) 참조행 = list강좌.listindex 입력행 = [a4].row + [a4].currentregion.rows.count Cells( 입력행, 2) = txt이름 Cells( 입력행, 3) = cmb분류 Cells( 입력행, 4) = list강좌.list( 참조행, 0) Cells( 입력행, 5) = list강좌.list( 참조행, 1) Cells( 입력행, 6) = list 강좌.List( 참조행, 2) End Sub ListIndex 와 List 속성에대한자세한설명은교재 232 쪽에되어있으니참고하세요. 138_ 컴퓨터활용능력 1 급실기