04_엑셀_PDF(11년1회~09년4)3

Transcription

1 EXAMINATION E 형 2011 년 1 회컴퓨터활용능력 1 급실기 프로그램명 EXCEL 제한시간 45분 수험번호 : 성명 : 1 급 E 형 < 유의사항 > 인적사항누락및잘못작성으로인한불이익은수험자책임으로합니다. 화면에암호입력상자가나타나면아래의암호를입력해야합니다. 암호 : 5219$5 작성된답안의파일명은지정된경로및파일명을변경하지마시고저장해야합니다. 이를준수하지않으면실격처리됩니다. < 답안파일명예 > C:\OA\ 수험번호 8자리.xlsm 외부데이터위치 : C:\OA\ 파일명 별도의지시사항이없는경우, 다음과같이처리하면실격처리됩니다. 제시된시트순서를임의로변경한경우 제시된시트이름을임의로변경한경우 제시된시트를임의로추가또는삭제한경우 답안은반드시문제에서지시또는요구한셀에입력하여야하며, 수험자가임의로셀의위치를변경하여입력한경우에는채점대상에서제외됩니다. 아울러지시하지않은셀의이동, 수정, 삭제, 변경등으로인해셀의위치및내용이변경된경우에도관련문제모두채점대상에서제외됩니다. 도형및차트의개체가중첩되어있거나, 동일한계산결과시트가복수로존재할경우에는해당개체나시트는채점대상에서제외됩니다. 별도의지시사항이없는경우, 주어진각시트의설정값또는기본설정값 (Default) 으로처리하십시오. 저장시간은별도로주어지지아니하므로제한된시간내에저장을완료해야합니다. 본문제에사용된용어는 Microsoft Office 2007 기준으로작성되었습니다. 대한상공회의소 2011 년 1 회 1 급 ( 엑셀 ) E 형 _261

2 2011 년 1 회컴퓨터활용능력 1 급실기 ( 엑셀 ) E 형 문제 1 기본작업 (25 점 ) 주어진시트에서다음의과정을수행하고저장하시오. 1. 기본작업-1 시트에서 외부데이터가져오기 기능을사용하여 < 판매.accdb> 의데이터를다음조건에따라 [B2] 셀부터표시하시오. (15점 ) < 녹차판매 > 테이블의데이터를 상품명, 제조방법, 용량, 제조년도, 가격, 판매량 열의순서로가져오시오. 가격 이 20000원이상이고, 판매량이 40 미만인행만을대상으로하시오. 제조방법 을기준으로오름차순, 가격 을기준으로내림차순으로정렬하시오. 가져온데이터의표를 범위로변환 을실행하여정상범위로변환하시오. 2. 기본작업-2 시트에서다음과같이 고급필터 를수행하시오. (5점 ) 제조방법 이 `가루차` 가아니고 가격 이 이상, 이하인필드의 상품명, 제조방법, 제조년도, 친환경인증, 가격 을표시하시오. 조건은 [A23:I26] 영역내에알맞게표시 결과는 [A27] 셀부터표시 3. 기본작업-2 시트에서다음과같이 조건부서식 을설정하시오. (5점 ) [A3:I21] 영역에서 상품코드 의첫글자가 `H` 이고, 제조년도 가 2009년인행전체에대해서글꼴색 파랑, 글꼴스타일 굵게 가적용되는조건부서식을작성하시오. 단, 규칙유형은 수식을사용하여서식을지정할셀결정 을이용하시오 (AND, LEFT 함수이용 ). 문제 2 계산작업 (30 점 ) 계산작업 시트에서다음의과정을수행하고저장하시오. 1. [ 표1] 에서상품코드를이용하여구분을 [C3:C21] 영역에계산하여표시하시오. (6점 ) 구분은상품코드의여섯번째글자가 1이면 특우, 2이면 우전, 3이면 세작, 4이면 중작, 5이면 대작 으로표시 CHOOSE, MID 함수이용 2. [ 표1] 의가격과판매량을이용하여판매가격을 [L3:L21] 영역에계산하여표시하시오. (6점 ) 판매가격은 가격 (1-할인율 ) 판매량 으로계산하시오. 할인율은 [ 표4] 를참조하여계산하시오. VLOOKUP, MATCH 함수이용 3. 비고를계산하는사용자정의함수 fn 비고 를작성하여계산을수행하시오. (6 점 ) fn 비고 는친환경인증, 제조년도, 판매량을인수로받아값을되돌려줌 친환경인증이 무농약 이고제조년도가 2010년인제품중판매량이 40 이상이면 추천상품 을, 그외는공백을표시하시오. fn비고 함수를이용하여 [M3:M21] 영역에비고를계산하시오. 262_ 컴퓨터활용능력 1 급실기

3 2011 년 5 월 1 일시행 Public Function fn비고 ( 친환경인증, 제조년도, 판매량 ) End Function 4. [ 표 1] 을이용하여다원명별로최대판매량을 [ 표 2] 의 [B25:B27] 영역에계산하여표시하시오. (6 점 ) 2010 년도에제조된제품중최대판매량을표시하고, 판매량이없을경우 0 으로표시 IF, ISERROR, LARGE 함수를사용한배열수식 5. [ 표 1] 을이용하여제조방법별가격의평균을 [ 표 3] 의 [E25:E27] 영역에계산하여표시하시오. (6 점 ) 소수점이하는표시하지마시오. AVERAGE, IF, TRUNC 함수를사용한배열수식 문제 3 분석작업 (20 점 ) 주어진시트에서다음의과정을수행하고작업하시오. 1. `` 분석작업 -1 시트에서다음과같이피벗테이블을작성하시오. (10 점 ) 피벗테이블의외부데이터를이용하여 < 판매.accdb> 의 < 녹차판매 > 테이블에서 제조년도, 친환경인증, 판매량, 판매가격 열만이용하시오. 피벗테이블보고서의레이아웃과위치는 < 그림 > 과같이설정하고, 보고서레이아웃을개요형식으로지정하시오. 판매가격 / 판매량 으로계산하는 판매단가 계산필드를추가하고, 보고서서식 6을설정하시오. 작업이완성된그림이며부분점수없음 2011 년 1 회 1 급 ( 엑셀 ) E 형 _263

4 2011 년 1 회컴퓨터활용능력 1 급실기 ( 엑셀 ) E 형 2. 분석작업-2 시트에대하여다음과같은기능을수행하는매크로를작성하고실행하시오. (10점 ) 1 판매량을내림차순으로정렬한후자동필터기능을이용하여판매량이하위 30% 에해당하는상품만을표시하는매크로를생성하고, 매크로이름은 하위30 으로정의하시오. 2 하위 30 매크로도형은양식컨트롤의 단추 로만들어지정하며, 단추의텍스트를 하위30% 실행 으로입력하여 [K1:K2] 영역에위치시키시오. 셀포인터의위치에관계없이매크로가실행되어야정답으로인정됨 문제 4 기타작업 (25 점 ) 주어진시트에서다음의과정을수행하고저장하시오. 1. 기타작업-1 시트에서다음의지시사항및그림에따라차트를수정하시오. (10점 ) 차트는반드시문제에서제공한차트를사용하여야하며, 신규로차트작성시 0점처리됨 1차트종류를 표식이있는꺾은선형 으로변경하시오. 2 보조세로축을표시한후세로축과보조세로축의최대값과주단위를 < 그림 > 과같이지정하시오. 3 차트제목과축제목을 < 그림 > 과같이지정하시오. 4 판매량이가장많은 2010년중국데이터요소에값을표시하시오. 5 차트영역의테두리스타일은 ``둥근모서리`, 그림자는 오프셋대각선오른쪽아래 로표시하고, 그림영역에 파랑박엽지 질감을표시하시오. 264_ 컴퓨터활용능력 1 급실기

5 2011 년 5 월 1 일시행 2. 기타작업-2 시트에서다음과같은작업을수행하고저장하시오. ( 각 5점 ) 1 < 상품판매 > 버튼을클릭하면 < 판매상품등록 > 폼이나타나고, 폼이초기화되면제조방법을나타내는콤보상자 (cmb제조방법) 에는 [I5:I7] 영역의데이터가표시되고, 구분을나타내는콤보상자 (cmb구분) 에는 [J5:J9] 영역의데이터가표시되도록프로시저를작성하시오 2 < 판매상품등록 > 폼의 < 등록 (cmd등록)> 버튼을클릭하면폼에입력된데이터가시트의표안에추가되도록프로시저를작성하시오. `` 친환경인증에는친환경인증의체크상자 (chk인증) 를선택하면 친환경상품, 그렇지않으면공백을표시하시오. 3 < 판매상품등록 > 폼의 < 종료 (cmd종료)> 버튼을클릭하면폼이종료되도록구현하시오 년 1 회 1 급 ( 엑셀 ) E 형 _265

6 2011 년 1 회컴퓨터활용능력 1 급실기 ( 엑셀 ) 정답및 E 형 문제 1 기본작업 정답 01. 외부데이터가져오기 _ 참고 : Section 01 외부데이터 28 쪽 결과 열선택 조건지정 1 조건지정 2 정렬 266_ 컴퓨터활용능력 1 급실기

7 2011 년 5 월 1 일시행 02. 고급필터 _ 참고 : Section 05 고급필터 63 쪽 결과 고급필터 대화상자 03. 조건부서식 _ 참고 : Section 03 조건부서식 47 쪽 결과 2011 년 1 회 1 급 ( 엑셀 ) E 형 _267

8 2011 년 1 회컴퓨터활용능력 1 급실기 ( 엑셀 ) E 형정답및 조건지정 문제 2 계산작업 정답 _ 컴퓨터활용능력 1 급실기

9 2011 년 5 월 1 일시행 1. 구분 (C3) : =CHOOSE( MID(A3,6,1),` 특우, 우전, 세작, 중작, 대작 ) 2. 판매가격 (L3) : =H3 * (1-VLOOKUP( G3,$H$25:$K$27, MATCH`( F3,$I$24:$K$24,0 )+1,FALSE ) `) * K3 3. 비고 (M3) : =fn비고 (G3,F3,K3) Public Function fn비고 ( 친환경인증, 제조년도, 판매량 ) If 친환경인증 = 무농약 And 제조년도 = 2010 And 판매량 >= 40 Then fn비고 = 추천상품 Else fn비고 = End If End Function 4. 최대판매량 (B25) : {=IF( ISERROR( LARGE( IF( ($J$3:$J$21=A25) * ($F$3:$F$21=2010), $K$3:$K$21),1 ) ), 0, LARGE( IF( ($J$3:$J$21= A25) * ($F$3:$F$21=2010), $K$3:$K$21 ), 1 ) )} 5. 평균가격 (E25) : {=TRUNC( AVERAGE( IF($D$3:$D$21=D25,$H$3:$H$21) ), 0 )} 문제 3 분석작업 정답 01. 피벗테이블 _ 참고 : Section 11 피벗테이블 133 쪽 피벗테이블필드목록 창 2011 년 1 회 1 급 ( 엑셀 ) E 형 _269

10 2011 년 1 회컴퓨터활용능력 1 급실기 ( 엑셀 ) E 형정답및 계산필드삽입 대화상자 [ 피벗테이블도구 ] 디자인 레이아웃 보고서레이아웃 테이블형식으로표시 / 개요형식으로표시를선택해야합니다. 02. 매크로작성 _ 참고 : Section 16 매크로작성 182 쪽 `` 하위 30` 매크로실행 상위 10 자동필터 대화상자 270_ 컴퓨터활용능력 1 급실기

11 2011 년 5 월 1 일시행 문제 4 기타작업 정답 01. 차트수정 _ 참고 : Section 17 차트 198 쪽 보조축지정 2010 년계열의바로가기메뉴에서 [ 데이터계열서식 ] 을선택한후 계열옵션 탭에서 보조축 을선택한다. 02. 프로시저작성 _ 참고 : Section 18 프로시저 227 쪽 1 폼활성화및초기화프로시저 폼활성화프로시저 Private Sub cmd상품판매 _Click( ) 판매상품등록.Show End Sub 폼초기화프로시저 Private Sub UserForm_Initialize( ) cmb제조방법.rowsource = i5:i7 cmb구분.rowsource = j5:j9 End Sub 2 < 등록 > 버튼에기능구현하기 Private Sub cmd등록 _Click( ) 입력행 = [b4].row + [b4].currentregion.rows.count Cells( 입력행, 2) = txt상품명 Cells( 입력행, 3) = txt다원명 Cells( 입력행, 4) = cmb제조방법 Cells( 입력행, 5) = cmb구분 Cells( 입력행, 6) = txt판매량 If chk인증 = True Then Cells( 입력행, 7) = 친환경상품 Else Cells( 입력행, 7) = End If End Sub 3 < 종료 > 버튼에기능구현하기 Private Sub cmd종료 _Click( ) Unload Me End Sub 2011 년 1 회 1 급 ( 엑셀 ) E 형 _271

12 EXAMINATION E 형 2010 년 3 회컴퓨터활용능력 1 급실기 프로그램명 EXCEL 제한시간 45분 수험번호 : 성명 : 1 급 E 형 < 유의사항 > 인적사항누락및잘못작성으로인한불이익은수험자책임으로합니다. 화면에암호입력상자가나타나면아래의암호를입력해야합니다. 암호 : 25823& 작성된답안의파일명은지정된경로및파일명을변경하지마시고저장해야합니다. 이를준수하지않으면실격처리됩니다. < 답안파일명예 > C:\OA\ 수험번호 8자리.xlsm 외부데이터위치 : C:\OA\ 파일명 별도의지시사항이없는경우, 다음과같이처리하면실격처리됩니다. 제시된시트순서를임의로변경한경우 제시된시트이름을임의로변경한경우 제시된시트를임의로추가또는삭제한경우 답안은반드시문제에서지시또는요구한셀에입력하여야하며, 수험자가임의로셀의위치를변경하여입력한경우에는채점대상에서제외됩니다. 아울러지시하지않은셀의이동, 수정, 삭제, 변경등으로인해셀의위치및내용이변경된경우에도관련문제모두채점대상에서제외됩니다. 도형및차트의개체가중첩되어있거나, 동일한계산결과시트가복수로존재할경우에는해당개체나시트는채점대상에서제외됩니다. 별도의지시사항이없는경우, 주어진각시트의설정값또는기본설정값 (Default) 으로처리하십시오. 저장시간은별도로주어지지아니하므로제한된시간내에저장을완료해야합니다. 본문제에사용된용어는 Microsoft Office 2007 기준으로작성되었습니다. 대한상공회의소 272_ 컴퓨터활용능력 1 급실기

13 2010 년 11 월 28 일시행 문제 1 기본작업 (25 점 ) 주어진시트에서다음의과정을수행하고저장하시오. 1. 기본작업-1 시트에서 외부데이터가져오기 기능을사용하여 < 상공.accdb> 의데이터를다음조건에따라 [B2] 셀부터표시하시오. (15점 ) < 도서판매 > 테이블의데이터를 도서명, 구매자, 전화번호, 나이, 판매일 열의순서로가져오시오. 판매일 이 2010년 10 월에해당하고, 나이 가 30 을초과하는행만을대상으로하시오. 판매일 을기준으로오름차순, 나이 를기준으로내림차순으로정렬하시오. 가져온데이터의표를 범위로변환 을실행하여정상범위로변환하시오. 2. 기본작업-2 시트에서다음과같이 고급필터 를수행하시오. (5점 ) 부서명 이 기획부 이거나 총무부 인자료중 점수 가 85 이상인자료의 이름, 부서명, 직위, 점수, 평가 를표시하시오. 조건은 [A25:L27] 영역내에알맞게표시 결과는 [A30] 셀부터표시 3. 기본작업-2 시트에서다음과같이 조건부서식 을설정하시오. (5점 ) [A2:L23] 영역에서 사원번호 의마지막글자가 `6` 이고 업무수행 이 60 이상인행전체에대해서글꼴색 파랑, 글꼴스타일 굵게 가적용되는조건부서식을작성하시오. 단, 규칙유형은 수식을사용하여서식을지정할셀결정 을이용하시오 (AND, RIGHT 이용 ). 문제 2 계산작업 (30 점 ) 계산작업 시트에서다음의과정을수행하고저장하시오. 1. [ 표 1] 의제품번호와크기를이용하여각제품별크기에따른단가를 [E3:E27] 영역에계산하여표시하시오. (6 점 ) [ 표 4] 의단가표이용 VLOOKUP, MATCH 함수이용 2. 배달금액을계산하는사용자정의함수 fn 배달금액 을작성하여계산을수행하시오. (6 점 ) fn 배달금액 은판매수량, 단가, 배달방법을인수로받아값을되돌려줌 배달방법 이 택배 이면 3000, 퀵 이면 12000, 직접수령 이면 0을표시하시오. 단, 배달방법 이 택배 인것중 판매수량 단가 를계산한값이 이상이면 0을표시하시오 (IF 이용 ). fn배달금액 함수를이용하여 [H3:H27] 영역에배달금액을계산하시오. Public Function fn배달금액 ( 판매수량, 단가, 배달방법 ) End Function 3. [ 표 1] 의고객번호, 판매수량, 단가를이용하여비고를 [I3:I27] 영역에계산하여표시하시오. (6 점 ) 고객번호의첫번째글자가 G 나 S 이면 우수, 그렇지않으면 일반 을표시한다음뒤에 판매수량 단가 를계산한값이 이상이면 DC10%, 그렇지않으면 DC5% 를표시하시오 ( 예 : 우수DC10%) 년 3 회 1 급 ( 엑셀 ) E 형 _273

14 2010 년 3 회컴퓨터활용능력 1 급실기 ( 엑셀 ) E 형 IF, OR, LEFT 함수와 & 연산자이용 4. [ 표 1] 을이용하여판매일의월별각제품의판매수량합계를 [ 표 2] 의 [B31:E35] 영역에계산하여표시하시오. (6 점 ) SUM, IF, MONTH 함수를사용한배열수식 5. [ 표1] 의배달방법을이용하여전체배달건수에대한각배달방법의비율을 [ 표3] 의 [B38:B40] 영역에계산하여표시하시오. (6점 ) COUNT, COUNTA, IF 함수를사용한배열수식 문제 3 분석작업 (20 점 ) 주어진시트에서다음의과정을수행하고작업하시오. 1. 분석작업-1 시트에서다음과같이피벗테이블을작성하시오. (10점 ) 피벗테이블의외부데이터를이용하여 < 상공.accdb> 의 < 도서판매 > 테이블에서 도서명, 책가격, 수량, 성별 열만이용하시오. 레이아웃과위치는 < 그림 > 과같이설정하고, 보고서레이아웃은개요형식으로지정하시오. 도서명 의순서를 < 그림 > 과같이표시되도록변경하시오. 보고서서식 표 1 을설정하고, 책가격 은셀서식대화상자를이용하여회계서식을지정하시오. 작업이완성된그림이며부분점수없음 2. 분석작업 -2 시트에대하여다음과같은기능을수행하는매크로를작성하고실행하시오. (10 점 ) 1 부분합기능을이용하여지점명별매출금액의합계를계산하는매크로를생성하고, 매크로이름은 부분합 으로정의하시오. 지점명 을기준으로오름차순으로정렬하시오. 2 부분합 매크로도형은양식컨트롤의 단추 로만들어지정하며, 단추의텍스트를 부분합실행 으로입력하여 [I2:I3] 영역에위치시키시오. 셀포인터의위치에관계없이매크로가실행되어야정답으로인정됨 274_ 컴퓨터활용능력 1 급실기

15 2010 년 11 월 28 일시행 문제 4 기타작업 (25 점 ) 주어진시트에서다음의과정을수행하고저장하시오. 1. 기타작업-1 시트에서다음의지시사항및그림에따라차트를수정하시오. (10점 ) 차트는반드시문제에서제공한차트를사용하여야하며, 신규로차트작성시 0점처리됨 1 매출금액 계열의차트종류를 < 그림 > 과같이변경한후선을완만하게표시하고, 데이터레이블값을표시하시오. 2 차트제목과세로 ( 값 ) 축제목을 < 그림 > 과같이지정하시오. 3세로축의표시단위를 천 으로지정하되, 레이블은표시하지마시오. 4 그림영역을 연한녹색 으로지정하시오. 5 차트영역의테두리스타일은 ``둥근모서리`, 그림자는 오프셋대각선오른쪽아래 로설정하시오. 2. 기타작업-2 시트에서다음과같은작업을수행하고저장하시오. ( 각5점 ) 1 < 접수하기 > 버튼을클릭하면 < 접수현황 > 폼이나타나고, 폼이초기화되면수강과목을나타내는콤보상자 (cmb수강과목) 에는 [I2:I7] 영역의데이터가표시되고, 담당자를나타내는목록상자 (list담당자) 에는 [I10:M16] 영역의데이터가표시되도록프로시저를작성하시오. 2 < 접수현황 > 폼의 < 등록 (cmd등록)> 버튼을클릭하면폼에입력된데이터를시트의표안에추가되도록프로시저를작성하시오 (List, Listindex 이용 ). 3 < 접수현황 > 폼의 < 종료 (cmd종료)> 버튼을클릭하면폼이종료되도록구현하시오 년 3 회 1 급 ( 엑셀 ) E 형 _275

16 2010 년 3 회컴퓨터활용능력 1 급실기 ( 엑셀 ) 정답및 E 형 문제 1 기본작업 정답 01. 외부데이터가져오기 _ 참고 : Section01 외부데이터 28 쪽 결과 열선택 조건지정 1 조건지정 2 정렬 276_ 컴퓨터활용능력 1 급실기

17 2010 년 11 월 28 일시행 02. 고급필터 _ 참고 : Section 05 고급필터 63 쪽 결과 고급필터 대화상자 03. 조건부서식 _ 참고 : Section 03 조건부서식 47 쪽 결과 조건지정 2010 년 3 회 1 급 ( 엑셀 ) E 형 _277

18 2010 년 3 회컴퓨터활용능력 1 급실기 ( 엑셀 ) E 형정답및 문제 2 계산작업 정답 _ 컴퓨터활용능력 1 급실기

19 2010 년 11 월 28 일시행 1. 단가 (E3) : =VLOOKUP( B3, $G$31:$J$35, MATCH(C3,$H$30:$J$30,0)+1, FALSE ) 2. 배달금액 (H3) : =fn 배달금액 (D3,E3,G3) Public Function fn배달금액 ( 판매수량, 단가, 배달방법 ) If 배달방법 = 택배 Then If 판매수량 * 단가 >= Then fn 배달금액 = 0 Else fn 배달금액 = 3000 End If ElseIf 배달방법 = 퀵 Then fn 배달금액 = Else fn 배달금액 = 0 End If End Function 3. 비고 (I3) : =IF( OR( LEFT(A3,1)= G,LEFT(A3,1)= S ), 우수, 일반 ) & IF(D3 * E3>=30000, DC10%, DC5% ) 4. 월별판매수량합계 (B31) : {=SUM( IF( ($B$3:$B$27=$A31) * ( MONTH($F$3:$F$27)=B$30 ),$D$3:$D$27 ) )} 5. 배달방법별비율 (B38) : {=COUNT( IF($G$3:$G$27=A38,1) )/COUNTA($G$3:$G$27)} 문제 3 분석작업 정답 01. 피벗테이블 _ 참고 : Section 11 피벗테이블 133 쪽 피벗테이블필드목록 창 2010 년 3 회 1 급 ( 엑셀 ) E 형 _279

20 2010 년 3 회컴퓨터활용능력 1 급실기 ( 엑셀 ) E 형정답및 도서명 필드정렬하기 도서명이입력된임의의셀을클릭한후 [ 피벗테이블도구 ] 옵션 정렬 ( 텍스트내림차순 ) 을클릭한다. ` 딸깍 ` 딸깍 회계서식 지정하기 280_ 컴퓨터활용능력 1 급실기

21 2010 년 11 월 28 일시행 02. 매크로작성 _ 참고 : Section 16 매크로작성 182 쪽 부분합 매크로실행 부분합 대화상자 2010 년 3 회 1 급 ( 엑셀 ) E 형 _281

22 2010 년 3 회컴퓨터활용능력 1 급실기 ( 엑셀 ) E 형정답및 문제 4 기타작업 정답 02. 프로시저작성 _ 참고 : Section 18 프로시저 227 쪽 1 폼활성화및초기화프로시저 폼활성화프로시저 Private Sub cmd접수하기 _Click( ) 접수현황.Show End Sub 폼초기화프로시저 Private Sub UserForm_Initialize( ) cmb 수강과목.RowSource = i2:i7 list담당자.rowsource = i10:m16 End Sub 2 < 등록 > 버튼에기능구현하기 Private Sub cmd등록 _Click( ) 참조행 = list담당자.listindex 입력행 = [b4].row + [b4].currentregion.rows.count Cells( 입력행, 2) = txt 학생이름 Cells( 입력행, 3) = txt나이 Cells( 입력행, 4) = cmb수강과목 Cells( 입력행, 5) = list담당자.list( 참조행, 1) Cells( 입력행, 6) = list담당자.list( 참조행, 2) Cells( 입력행, 7) = list담당자.list( 참조행, 4) End Sub ListIndex와 List 속성에대한내용은교재 1편 246쪽에자세하게설명되어있으니참고하세요. 3 < 종료 > 버튼에기능구현하기 Private Sub cmd종료 _Click( ) Unload Me End Sub 282_ 컴퓨터활용능력 1 급실기

23 EXAMINATION E 형 2010 년 2 회컴퓨터활용능력 1 급실기 프로그램명 EXCEL 제한시간 45분 수험번호 : 성명 : 1 급 E 형 < 유의사항 > 인적사항누락및잘못작성으로인한불이익은수험자책임으로합니다. 화면에암호입력상자가나타나면아래의암호를입력해야합니다. 암호 : 652*25 작성된답안의파일명은지정된경로및파일명을변경하지마시고저장해야합니다. 이를준수하지않으면실격처리됩니다. < 답안파일명예 > C:\OA\ 수험번호 8자리.xlsm 외부데이터위치 : C:\OA\ 파일명 별도의지시사항이없는경우, 다음과같이처리하면실격처리됩니다. 제시된시트순서를임의로변경한경우 제시된시트이름을임의로변경한경우 제시된시트를임의로추가또는삭제한경우 답안은반드시문제에서지시또는요구한셀에입력하여야하며, 수험자가임의로셀의위치를변경하여입력한경우에는채점대상에서제외됩니다. 아울러지시하지않은셀의이동, 수정, 삭제, 변경등으로인해셀의위치및내용이변경된경우에도관련문제모두채점대상에서제외됩니다. 도형및차트의개체가중첩되어있거나, 동일한계산결과시트가복수로존재할경우에는해당개체나시트는채점대상에서제외됩니다. 별도의지시사항이없는경우, 주어진각시트의설정값또는기본설정값 (Default) 으로처리하십시오. 저장시간은별도로주어지지아니하므로제한된시간내에저장을완료해야합니다. 본문제에사용된용어는 Microsoft Office 2007 기준으로작성되었습니다. 대한상공회의소 2010 년 2 회 1 급 ( 엑셀 ) E 형 _283

24 2010 년 2 회컴퓨터활용능력 1 급실기 ( 엑셀 ) E 형 문제 1 기본작업 (25 점 ) 주어진시트에서다음의과정을수행하고저장하시오. 1. 기본작업-1 시트에서 외부데이터가져오기 기능을사용하여 < 수강현황.accdb> 의데이터를다음조건에따라 [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] 를이용하여경력이가장많은강사명을 [ 표 4] 의 [F27] 셀에계산하여표시하시오. (6 점 ) MATCH, INDEX, MAX 함수사용 284_ 컴퓨터활용능력 1 급실기

25 2010 년 7 월 18 일시행 5. 접수율을계산하는사용자정의함수 fn 접수율 을작성하여계산을수행하시오. (6 점 ) fn 접수율 은정원과수강인원을인수로받아값을되돌려줌 수강인원 / 정원 을계산한값이 1보다크면 정원초과, 1이면 접수완료, 그렇지않으면 수강인원 / 정원 의값을표시하시오 (SELECT CASE 이용 ). fn접수율 함수를이용하여 [J10:J23] 영역에접수율을계산하시오 ( 백분율스타일적용 ). Public Function fn접수율 ( 정원, 수강인원 ) End Function 문제 3 분석작업 (20 점 ) 주어진시트에서다음의과정을수행하고작업하시오. 1. 분석작업-1 시트에서다음과같이피벗테이블을작성하시오. (10점 ) 피벗테이블의외부데이터를이용하여 < 수강현황.accdb> 의 < 접수현황 > 테이블에서수강료가 100,000 이상인데이터의 이름, 분류, 강사구분, 강의요일, 정원, 수강료 열만이용하시오. 행레이블에 강의요일, 열레이블에 분류, 값에 이름 의개수, 정원 의합계를표시하고, 위치는 < 그림 > 을참조하여작성하시오. 보고서레이아웃은개요형식으로지정하고 정원 의행의비율을나타내는 비율 계산필드를추가하시오. < 그림 > 과같이 강의요일 을 월, 수, 금 과 화, 목 으로그룹을지정한후보고서서식 6을지정하시오. 작업이완성된그림이며부분점수없음 2010 년 2 회 1 급 ( 엑셀 ) E 형 _285

26 2010 년 2 회컴퓨터활용능력 1 급실기 ( 엑셀 ) E 형 2. 분석작업-2 시트에대하여다음과같은기능을수행하는매크로를작성하고실행하시오. ( 각 5점 ) 1 데이터표기능을이용하여 [D9:G14] 영역에 할인율 과 과목수 에따른 수강료 를계산하는매크로를생성하고, 매크로이름은 수강료계산 으로정의하시오. 수강료는 기본수강료 _ 과목수 _(1-할인율) 로계산 2` 수강료계산 매크로도형은 모서리가둥근직사각형 ( ) 으로만들어지정하며, 도형의텍스트를 수강료계산하기 로입력하여 [F2:G3] 영역에위치시키시오. 도형의채우기색을 노랑, 글꼴색을 검정 으로지정하시오. 셀포인터의위치에관계없이매크로가실행되어야정답으로인정됨 문제 4 기타작업 (25 점 ) 주어진시트에서다음의과정을수행하고저장하시오. 1. 기타작업-1 시트에서다음의지시사항및그림에따라차트를수정하시오. ( 각 2점 ) 차트는반드시문제에서제공한차트를사용하여야하며, 신규로차트작성시 0점처리됨 1 수강인원 계열을추가한후해당계열의데이터레이블을글꼴색 빨강, 글꼴스타일 굵게 로지정하시오. 2 차트의첫째조각의각을 15, 도넛구멍크기를 20으로지정하시오. 3 차트제목을 < 그림 > 과같이지정한후글꼴크기 14, 채우기색 흰색, 배경 1, 그림자 오프셋대각선오른쪽아래 를지정하시오. 4 범례위치를 < 그림 > 과같이지정하시오. 5 차트영역의테두리스타일은 ``둥근모서리`, 그림자는 오프셋대각선오른쪽아래 로설정하시오. 286_ 컴퓨터활용능력 1 급실기

27 2010 년 7 월 18 일시행 2. 기타작업-2 시트에서다음과같은작업을수행하고저장하시오. ( 각 5점 ) 1 < 등록 > 버튼을클릭하면 < 학원생등록 > 폼이나타나고, 폼이초기화되면 초등학생, 중학생, 고등학생 이분류 (cmb분류) 콤보상자에목록으로추가되도록프로시저를작성하시오 (With, Additem 이용 ). 2 분류 (cmb분류) 콤보상자에서 초등학생 을선택하면 [J6:M8] 영역을, 중학생 을선택하면 [J10:M15] 영역을, 고등학생 을선택하면 [J17:M22] 영역을강좌 (list강좌) 목록상자에표시되록프로시저를작성하시오 (Select, Change 이용 ). 3 < 학원생등록 > 폼의 < 입력 (cmd입력)> 버튼을클릭하면폼에입력된데이터를시트의표안에추가되도록프로시저를작성하시오 (List, Listindex 이용 ) 년 2 회 1 급 ( 엑셀 ) E 형 _287

28 2010 년 2 회컴퓨터활용능력 1 급실기 ( 엑셀 ) 정답및 E 형 문제 1 기본작업 정답 01. 외부데이터가져오기 _ 참고 : Section 01 외부데이터 28 쪽 결과 열선택 조건지정 1 조건지정 2 정렬 288_ 컴퓨터활용능력 1 급실기

29 2010 년 7 월 18 일시행 02. 고급필터 _ 참고 : Section 05 고급필터 63 쪽 결과 고급필터 대화상자 03. 조건부서식 _ 참고 : Section 03 조건부서식 47 쪽 결과 2010 년 2 회 1 급 ( 엑셀 ) E 형 _289

30 2010 년 2 회컴퓨터활용능력 1 급실기 ( 엑셀 ) E 형정답및 조건지정 문제 2 계산작업 정답 _ 컴퓨터활용능력 1 급실기

31 2010 년 7 월 18 일시행 1. 현재가치 (C6) : =IF( PV(C3/12,C4 * 12,C5)>= , 한도초과,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) Public Function fn접수율 ( 정원, 수강인원 ) Select Case 수강인원 / 정원 Case Is > 1 fn 접수율 = 정원초과 Case Is = 1 fn 접수율 = 접수완료 Case Else fn 접수율 = 수강인원 / 정원 End Select End Function 문제 3 분석작업 정답 01. 피벗테이블 _ 참고 : Section 11 피벗테이블 133 쪽 조건지정 2010 년 2 회 1 급 ( 엑셀 ) E 형 _291

32 2010 년 2 회컴퓨터활용능력 1 급실기 ( 엑셀 ) E 형정답및 피벗테이블필드목록 창 비율 계산필드삽입 1[ 피벗테이블도구 ] 옵션 도구 수식 계산필드를선택하여다음과같이지정하고 < 추가 > 를클릭한후 < 확인 > 을클릭한다. 2 피벗테이블에추가된 비율 필드의바로가기메뉴에서 [ 값필드설정 ] 을선택한다. 3 값필드설정 대화상자의 값표시형식 탭에서값표시형식을 행방향의비율 로지정한후 < 확인 > 을클릭한다. 292_ 컴퓨터활용능력 1 급실기

33 2010 년 7 월 18 일시행 02. 매크로작성 _ 참고 : Section 16 매크로작성 182 쪽 수강료계산 매크로실행 수강료 (C5, C8) : =C2*C3*(1-C4) 데이터표 대화상자 2010 년 2 회 1 급 ( 엑셀 ) E 형 _293

34 2010 년 2 회컴퓨터활용능력 1 급실기 ( 엑셀 ) E 형정답및 문제 4 기타작업 정답 01. 차트수정 _ 참고 : Section 17 차트 198 쪽 첫째조각의각도및도넛구멍의크기지정 특정계열의바로가기메뉴에서 [ 데이터계열서식 ] 을선택한후 계열옵션 탭에서그림과같이지정한다. 02. 프로시저작성 _ 참고 : Section 18 프로시저 227 쪽 1 폼활성화및초기화프로시저 폼활성화프로시저 Private Sub 등록 _Click( ) 학원생등록.Show End Sub 폼초기화프로시저 Private Sub UserForm_Initialize( ) With cmb분류.additem 초등학생.AddItem 중학생.AddItem 고등학생 End With End Sub 294_ 컴퓨터활용능력 1 급실기

35 2010 년 7 월 18 일시행 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 속성에대한내용은교재 1편 246쪽에자세하게설명되어있으니참고하세요 년 2 회 1 급 ( 엑셀 ) E 형 _295

36 EXAMINATION E 형 2010 년 1 회컴퓨터활용능력 1 급실기 프로그램명 EXCEL 제한시간 45분 수험번호 : 성명 : 1 급 E 형 < 유의사항 > 인적사항누락및잘못작성으로인한불이익은수험자책임으로합니다. 화면에암호입력상자가나타나면아래의암호를입력해야합니다. 암호 : 795$37 작성된답안의파일명은지정된경로및파일명을변경하지마시고저장해야합니다. 이를준수하지않으면실격처리됩니다. < 답안파일명예 > C:\OA\ 수험번호 8자리.xlsm 외부데이터위치 : C:\OA\ 파일명 별도의지시사항이없는경우, 다음과같이처리하면실격처리됩니다. 제시된시트순서를임의로변경한경우 제시된시트이름을임의로변경한경우 제시된시트를임의로추가또는삭제한경우 답안은반드시문제에서지시또는요구한셀에입력하여야하며, 수험자가임의로셀의위치를변경하여입력한경우에는채점대상에서제외됩니다. 아울러지시하지않은셀의이동, 수정, 삭제, 변경등으로인해셀의위치및내용이변경된경우에도관련문제모두채점대상에서제외됩니다. 도형및차트의개체가중첩되어있거나, 동일한계산결과시트가복수로존재할경우에는해당개체나시트는채점대상에서제외됩니다. 별도의지시사항이없는경우, 주어진각시트의설정값또는기본설정값 (Default) 으로처리하십시오. 저장시간은별도로주어지지아니하므로제한된시간내에저장을완료해야합니다. 본문제에사용된용어는 Microsoft Office 2007 기준으로작성되었습니다. 대한상공회의소 296_ 컴퓨터활용능력 1 급실기

37 2010 년 5 월 2 일시행 문제 1 기본작업 (25 점 ) 주어진시트에서다음의과정을수행하고저장하시오. 1. 기본작업-1 시트에서 외부데이터가져오기 기능을사용하여 < 사내거래.accdb> 의데이터를다음조건에따라 [B2] 셀부터표시하시오. (15점 ) < 사내거래현황 > 테이블의데이터를 진행자, 소속, 시기, 종기, 일수 열의순서로가져오시오. 소속 이 ABC 이고 일수 가 10 이상인행만을대상으로하시오. 진행자 를기준으로오름차순으로정렬하고, 2차적으로 일수 를기준으로내림차순으로정렬하시오. 가져온데이터의표를 범위로변환 을실행하여정상범위로변환하시오. 2. 기본작업-2 시트에서다음과같이 고급필터 를수행하시오. (5점 ) 부서 가 홍보 부나 영업 부이고, 생년월일의출생년도가 1970~1980년이고직위가 행원 인데이터의 부서, 성명, 할당량, 실적량 을표시하시오 (AND, YEAR 함수이용 ). 조건은 [B23:G28] 영역내에알맞게표시 결과는 [B29] 셀부터표시 3. 기본작업-2 시트에서다음과같이 조건부서식 을설정하시오. (5점 ) [B4:G21] 영역에서 생년월일 의요일이 토요일 이거나 일요일 인행전체에대해글꼴색이 파랑 으로적용되는조건부서식을작성하시오. 단, 규칙유형은 수식을사용하여서식을지정할셀결정 을이용하시오 (WEEKDAY 함수의 2번형식이용 ). 문제 2 계산작업 (30 점 ) 계산작업 시트에서다음의과정을수행하고저장하시오. 1. [ 표1] 의 홈페이지주소 에서.kr 상공.kr 로변경하고, 첫글자는대문자로변경하여메일주소 [E4:E22] 를구하시오. (6점 ) SUBSTITUTE, UPPER, PROPER, CONCATENATE 중알맞은함수를선택하여사용 2. [ 표 1] 의대출금액, 대출기간, 이율을이용하여월상환액 [J4:J22] 을양수로계산하여표시하시오. (6 점 ) 이율은대출기간과 [ 표 2] 를이용하여계산 PMT, HLOOKUP 함수사용 3. 비고를계산하는사용자정의함수 fn 비고 를작성하여계산을수행하시오. (6 점 ) fn 비고 는 구매건수 와 구매금액 을인수로받아값을되돌려줌 구매건수 가 60 이상이거나 구매금액 이 15,000,000 이상이면 `VIP고객`, 구매건수 가 50 이상이거나 구매금액 이 10,000,000 이상이면 우수고객, 그렇지않으면공백을표시하시오. fn비고 함수를이용하여 [K4:K22] 영역에비고를표시하시오. Public Function fn비고 ( 구매건수, 구매금액 ) End Function 2010 년 1 회 1 급 ( 엑셀 ) E 형 _297

38 2010 년 1 회컴퓨터활용능력 1 급실기 ( 엑셀 ) E 형 4. [ 표 1] 에서구매금액과구매건수가가장많은사람의성명을 [ 표 3] 의 [B30:C30] 영역에계산하여표시하시오. (6 점 ) MATCH, INDEX, MAX 함수사용 5. [ 표 1] 을이용하여 [ 표 4] 에성별및직업별로구매금액의평균 [I26:J29] 을구하시오. (6 점 ) IF, AVERAGE 함수를사용한배열수식 문제 3 분석작업 (20 점 ) 주어진시트에서다음의과정을수행하고작업하시오. 1. 분석작업-1 시트에서다음과같이피벗테이블을작성하시오. (10점 ) 피벗테이블의외부데이터를이용하여 < 사내거래.accdb> 의 < 사내거래현황 > 테이블에서 소속, 시기, 일수, 1본부, 2본부 의열만이용하시오. 레이아웃과위치는 < 그림 > 과같이설정하고, 보고서레이아웃은개요형식으로지정하시오. 일수의행의비율을나타내는일수비율계산필드를추가하시오. 시기의연도별로그룹을지정한후보고서서식 6을지정하시오. 값영역의숫자는 < 그림 > 과같이표시되도록표시형식을지정하시오. 작업이완성된그림이며부분점수없음 2. 분석작업-2 시트에대하여다음과같은기능을수행하는매크로를작성하고실행하시오. (10점 ) 1 자동필터기능을이용하여 MD가 10 이상인데이터를추출하는매크로를생성하고, 매크로이름은 MD추출 로정의하시오. 2 MD추출 매크로도형은양식컨트롤의 단추 로만들어지정하며, 도형의텍스트를 10이상추출 로입력하여 [I1:J2] 영역에위치시키시오. 셀포인터의위치에관계없이매크로가실행되어야정답으로인정됨 298_ 컴퓨터활용능력 1 급실기

39 2010 년 5 월 2 일시행 문제 4 기타작업 (25 점 ) 주어진시트에서다음의과정을수행하고저장하시오. 1. 기타작업-1 시트에서다음의지시사항및그림에따라차트를수정하시오. (10점 ) 차트는반드시문제에서제공한차트를사용하여야하며, 신규로차트작성시 0점처리됨 1 숙제 데이터를추가한후계열이름을 과제 로지정하고, 해당계열에값을표시하시오. 2 < 그림 > 을참조하여가로 ( 항목 ) 축제목을지정하시오. 3 차트제목을지정한후글꼴은 궁서, 크기는 16으로지정하시오. 4 < 그림 > 을참조하여범례위치를지정한후글꼴을 굴림, 크기를 11로지정하시오. 5 차트영역의테두리스타일은 ``둥근모서리`, 그림자는 오프셋대각선오른쪽아래 로설정하시오. 2. 기타작업-2 시트에서다음과같은작업을수행하고저장하시오. ( 각 5점 ) 1 < 입사자입력 > 버튼을클릭하면 < 신규입사자입력 > 폼이나타나고, 폼이초기화되면지원부서를나타내는콤보상자 (cmb지원부서) 에는 [H6:H10] 영역의데이터가표시되고, 지원지역을나타내는콤보상자 (cmb지원지역 ) 에는 [I6:I11] 영역의데이터가표시되도록프로시저를작성하시오. 각콤보상자에는표시하는영역의첫번째셀의값이표시되도록지정하시오. 2 < 신규입사자입력 > 폼의 < 입력 (cmd입력)> 버튼을클릭하면폼에입력된데이터를시트의표안에추가되도록프로시저를작성하시오. 성명을입력하지않았으면 성명을입력하시오., 성별을입력하지않았으면 성별을입력하시오. 라는메시지를출력하고, 성명과성별을모두입력했을때만폼의데이터를워크시트에입력하시오 년 1 회 1 급 ( 엑셀 ) E 형 _299

40 2010 년 1 회컴퓨터활용능력 1 급실기 ( 엑셀 ) E 형 3 < 신규입사자입력 > 폼의 < 종료 (cmd 종료 )> 버튼을클릭하면폼이종료되도록구현하시오. 300_ 컴퓨터활용능력 1 급실기

41 2010 년 1 회컴퓨터활용능력 1 급실기 ( 엑셀 ) 정답및 E 형 문제 1 기본작업 정답 01. 외부데이터가져오기 _ 참고 : Section 01 외부데이터 28 쪽 결과 열선택 조건지정 1 조건지정 2 정렬 2010 년 1 회 1 급 ( 엑셀 ) E 형 _301

42 2010 년 1 회컴퓨터활용능력 1 급실기 ( 엑셀 ) E 형정답및 02. 고급필터 _ 참고 : Section 05 고급필터 63 쪽 결과 필드명과다른이름을수험자가임의로입력합니다. 고급필터 대화상자 [C24], [C25] : =AND(YEAR(E4)>=1970,YEAR(E4)<=1980) 03. 조건부서식 _ 참고 : Section 03 조건부서식 47 쪽 결과 조건지정 WEEKDAY( 날짜, 옵션 ) 함수에서옵션을 2 로지정하면요일번호를 1( 월요일 ) 에서 7( 일요일 ) 까지의숫자로표시합니다. 302_ 컴퓨터활용능력 1 급실기

43 2010 년 5 월 2 일시행 문제 2 계산작업 정답 메일주소 (E4) : =SUBSTITUTE( 상공.kr ) 2. 월상환액 (J4) : =PMT( HLOOKUP( F4,$B$25:$F$26,2 )/12,F4 * 12,-I4 ) 결과값이양수로나오도록대출금액 (I4) 을음수로입력합니다. 3. 비고 (K4) : =fn비고 (G4,H4) Public Function fn비고 ( 구매건수, 구매금액 ) If 구매건수 >= 60 Or 구매금액 >= Then fn비고 = VIP고객 ElseIf 구매건수 >= 50 Or 구매금액 >= Then fn비고 = 우수고객 Else fn비고 = End If End Function 4. 가장많은구매건수의성명 (B30) : =INDEX( $A$4:$K$22, MATCH( MAX(G4:G22),G4:G22,0 ),1 ) 5. 성별및직업별구매금액평균 (I26) : {=AVERAGE( IF( ($C$4:$C$22=I$25) * ($B$4:$B$22=$H26),$H$4:$H$22 ) )} 2010 년 1 회 1 급 ( 엑셀 ) E 형 _303

44 2010 년 1 회컴퓨터활용능력 1 급실기 ( 엑셀 ) E 형정답및 문제 3 분석작업 정답 01. 피벗테이블 _ 참고 : Section 11 피벗테이블 133 쪽 피벗테이블필드목록 창 일수비율 계산필드삽입 1 [ 피벗테이블도구 ] 옵션 도구 수식 계산필드를선택하여그림과같이지정하고 < 추가 > 를클릭한후 < 확인 > 을클릭한다. 2 피벗테이블에추가된 일수비율 필드의바로가기메뉴에서 [ 값필드설정 ] 을선택한다. 3 값필드설정 대화상자의 값표시형식 탭에서값표시형식을 행방향의비율 로지정한후 < 확인 > 을클릭한다. 304_ 컴퓨터활용능력 1 급실기

45 2010 년 5 월 2 일시행 그룹지정 02. 매크로작성 _ 참고 : Section 16 매크로작성 182 쪽 MD 추출 매크로실행 2010 년 1 회 1 급 ( 엑셀 ) E 형 _305

46 2010 년 1 회컴퓨터활용능력 1 급실기 ( 엑셀 ) E 형정답및 사용자지정자동필터 대화상자 문제 4 기타작업 정답 01. 차트작성 _ 참고 : Section 17 차트 198 쪽 데이터추가및계열이름변경 1 차트영역의바로가기메뉴에서 [ 데이터선택 ] 을선택한다. 2 데이터원본선택 대화상자의 범례항목 ( 계열 ) 에서 < 추가 > 를클릭한다. 3 계열편집 대화상자에서그림과같이지정한후 < 확인 > 을클릭한다. 02. 프로시저작성 _ 참고 : Section 18 프로시저 227 쪽 1 폼활성화및초기화프로시저 폼활성화프로시저 Private Sub cmd입사자입력 _Click( ) 신규입사자입력.Show End Sub 306_ 컴퓨터활용능력 1 급실기

47 2010 년 5 월 2 일시행 폼초기화프로시저 Private Sub UserForm_Initialize( ) cmb 지원부서.RowSource = H6:H10 cmb 지원부서 = cmb 지원부서.List(0, 0) cmb 지원지역.RowSource = I6:I11 cmb 지원지역 = cmb 지원지역.List(0, 0) End Sub 2 < 입력 > 버튼에기능구현하기 Private Sub cmd입력 _Click( ) If txt성명 = Then MsgBox 성명을입력하시오. ElseIf txt성별 = Then MsgBox 성별을입력하시오. Else 입력행 = [C5].Row + [C5].CurrentRegion.Rows.Count Cells( 입력행, 3) = txt성명 Cells( 입력행, 4) = txt성별 Cells( 입력행, 5) = cmb지원부서 Cells( 입력행, 6) = cmb 지원지역 End If End Sub 3 < 종료 > 버튼에기능구현하기 Private Sub cmd종료 _Click( ) Unload Me End Sub 2010 년 1 회 1 급 ( 엑셀 ) E 형 _307

48 EXAMINATION D 형 2009 년 4 회컴퓨터활용능력 1 급실기 프로그램명 EXCEL 제한시간 45분 수험번호 : 성명 : 1 급 D 형 < 유의사항 > 인적사항누락및잘못작성으로인한불이익은수험자책임으로합니다. 화면에암호입력상자가나타나면아래의암호를입력해야합니다. 암호 : 86#594 작성된답안의파일명은지정된경로및파일명을변경하지마시고저장해야합니다. 이를준수하지않으면실격처리됩니다. < 답안파일명예 > C:\OA\ 수험번호 8자리.xlsm 외부데이터위치 : C:\OA\ 파일명 별도의지시사항이없는경우, 다음과같이처리하면실격처리됩니다. 제시된시트순서를임의로변경한경우 제시된시트이름을임의로변경한경우 제시된시트를임의로추가또는삭제한경우 답안은반드시문제에서지시또는요구한셀에입력하여야하며, 수험자가임의로셀의위치를변경하여입력한경우에는채점대상에서제외됩니다. 아울러지시하지않은셀의이동, 수정, 삭제, 변경등으로인해셀의위치및내용이변경된경우에도관련문제모두채점대상에서제외됩니다. 도형및차트의개체가중첩되어있거나, 동일한계산결과시트가복수로존재할경우에는해당개체나시트는채점대상에서제외됩니다. 별도의지시사항이없는경우, 주어진각시트의설정값또는기본설정값 (Default) 으로처리하십시오. 저장시간은별도로주어지지아니하므로제한된시간내에저장을완료해야합니다. 본문제에사용된용어는 Microsoft Office 2007 기준으로작성되었습니다. 대한상공회의소 308_ 컴퓨터활용능력 1 급실기

49 2009 년 11 월 29 일시행 문제 1 기본작업 (25 점 ) 주어진시트에서다음의과정을수행하고저장하시오. 1. 기본작업-1 시트에서 외부데이터가져오기 기능을사용하여 < 급여대장.accdb> 의데이터를다음조건에따라 [A1] 셀부터표시하시오.(15점 ) < 급여지불현황 > 테이블의데이터를 사원번호, 입사일, 기본급, 직위, 소속 열의순서로가져오시오. 직위 가 부장 이거나 소속 이 K-P 인행만을대상으로하시오. 입사일 을기준으로오름차순으로정렬하고, 2차적으로 기본급 을기준으로오름차순으로정렬하시오. 가져온데이터의표를 범위로변환 을실행하여정상범위로변환하시오. 2. 기본작업-2 시트에서다음과같이 고급필터 를수행하시오.(5 점 ) 고객번호 가 PR 로시작하거나지역이 도봉 인데이터의 이름, 성별, 지역, 최종주문일 을표시하시오. 조건은 [A22:I24] 영역내에알맞게표시 결과는 [A26] 셀부터표시 3. 기본작업-2 시트에서다음과같이 조건부서식 을설정하시오.(5 점 ) [A3:I20] 영역에서행번호가짝수인행전체에대해서글꼴스타일은 기울임꼴, 글꼴색은 빨강 으로적용되는조건부서식을작성하시오. 단, 규칙유형은 수식을사용하여서식을지정할셀결정 을이용하시오 (MOD, ROW 함수이용 ). 문제 2 계산작업 (30 점 ) 계산작업 시트에서다음의과정을수행하고저장하시오. 1. [ 표 1] 을이용해서남자와여자의순위에해당하는기록을 [ 표 2] 의 [H3:I5] 영역에계산하여표시하시오.(6 점 ) 코드의첫번째자리가 M 이면 남자, W 면 여자 임 IF, SMALL, LEFT 함수를적용한배열수식사용 2. [ 표 1] 을이용하여기록내완주한선수들의인원수를 [ 표 3] 의 [H9:K9] 영역에계산하여표시하시오.(6 점 ) SUM, IF 함수를적용한배열수식사용 3. [ 표 1] 에서선수코드의 2~3 번째문자를이용하여각선수의출신지역을 [D3:D23] 영역에계산하여표시하시오.(6 점 ) [G13:H20] 영역 ( 표 4) 을참조하여계산 VLOOKUP, MID 함수이용 4. 성별을계산하는사용자정의함수 fn성별 을작성하여계산을수행하시오.(6 점 ) fn성별 은선수코드를인수로받아값을되돌려줌 성별은선수코드의첫번째글자가 `M` 이면 `남`, Ẁ` 이면 `여` 를의미함 fn성별 함수를이용하여 [E3:E23] 영역에성별을표시 2009 년 4 회 1 급 ( 엑셀 ) D 형 _309

50 2009 년 4 회컴퓨터활용능력 1 급실기 ( 엑셀 ) D 형 Public Function fn 성별 ( 선수코드 ) End Function 5. [ 표5] 의주문량, 재고량, 반품량을이용하여 [G27:G36] 영역에비고를계산하여표시하시오.(6 점 ) 반품량이공백이고주문량이 8000 이상, 재고량이 1000 이하이면 베스트셀러 를표시하고, 그렇지않으면공백을표시하시오. IF, OR, AND, ISBLANK, ISERROR 중알맞은함수를선택하여사용 문제 3 분석작업 (20 점 ) 주어진시트에서다음의과정을수행하고작업하시오. 1. 분석작업-1 시트에서다음과같이피벗테이블을작성하시오.(10점 ) 피벗테이블의외부데이터를이용하여 < 급여대장.accdb> 의 < 급여지불현황 > 테이블에서 직위, 입사일, 배우자, 부양가족 의열만이용하시오. 레이아웃과위치는 < 그림 > 과같이설정하고, 보고서레이아웃은테이블형식으로지정하시오. < 그림 > 을참조하여그룹을지정하고, 직위별부분합이표시되지않도록지정하시오. 값영역의숫자는 < 그림 > 과같이표시되도록표시형식을지정하시오. 작업이완성된그림이며부분점수없음 310_ 컴퓨터활용능력 1 급실기

51 2009 년 11 월 29 일시행 2. 분석작업-2 시트에대하여다음과같은기능을수행하는매크로를작성하고실행하시오.(10점 ) 1 데이터통합기능을이용하여각지역전자상거래사시험성적에대해직업별필기시험, 실기시험의평균을 [G6:H8] 영역에작성하는매크로를생성하고, 매크로이름은 평균계산 으로정의하시오. 2 평균계산 매크로도형은 직사각형 으로만들어지정하며, 직사각형 의텍스트를 직업별평균구하기 로입력하여 [F1:H2] 영역에위치시키시오. 셀포인터의위치에관계없이매크로가실행되어야정답으로인정됨 문제 4 기타작업 (25 점 ) 주어진시트에서다음의과정을수행하고저장하시오. 1. 기타작업-1 시트에서다음의지시사항및그림에따라차트를수정하시오.(10점 ) 차트는반드시문제에서제공한차트를사용하여야하며, 신규로차트작성시 0점처리됨 1 < 그림 > 을참조하여 중간 과 기말 계열을각각 묶은세로막대형 과 표식이있는꺾은선형 두가지의차트로표시하시오. 2 < 그림 > 를참조하여차트제목과축제목을지정하시오. 3 차트제목에테두리를표시하시오. 4 범례위치를아래쪽으로지정하시오. 5 차트영역의테두리스타일은 ``둥근모서리`, 그림자는 오프셋대각선오른쪽아래 로설정하시오. 2. 기타작업-2 시트에서다음과같은작업을수행하고저장하시오.( 각 5점 ) 1 < 보유현황 > 버튼을클릭하면 < 제품보유현황폼 > 이나타나고, 폼이초기화되면제품명을나타내는콤보상자 (cmb제품명) 에는 [A4:A11] 영역의데이터가표시되도록프로시저를작성하시오. 2 < 제품보유현황폼 > 에서제품명 (cmb제품명) 을선택한후 < 조회 (cmd조회)> 버튼을클릭하면워크시트에입력된해당제품의데이터를폼에표시하는프로시저를작성하시오 (Listindex 사용 ) 년 4 회 1 급 ( 엑셀 ) D 형 _311

52 2009 년 4 회컴퓨터활용능력 1 급실기 ( 엑셀 ) D 형 3 < 제품보유현황폼 > 의 < 닫기 (cmd 닫기 )> 버튼을클릭하면폼이종료되도록구현하시오. 312_ 컴퓨터활용능력 1 급실기

53 2009 년 4 회컴퓨터활용능력 1 급실기 ( 엑셀 ) 정답및 D 형 문제 1 기본작업 정답 01. 외부데이터가져오기 _ 참고 : Section 01 외부데이터 28 쪽 결과 열선택 조건지정 1 조건지정 2 정렬 2009 년 4 회 1 급 ( 엑셀 ) D 형 _313

54 2009 년 4 회컴퓨터활용능력 1 급실기 ( 엑셀 ) D 형정답및 쿼리마침 쿼리편집 K-P 를다른행으로이동시킨후 닫기 ( ) 단추를클릭한다. 쿼리마법사에서지정하는필드간의조건은 AND 로만연결됩니다. 이문제와같이 직위가 ~ 이거나소속이 ~ 인 OR 조건을지정하려면쿼리마법사에서 AND 조건으로작성한후쿼리편집기에서 OR 조건으로수정해야합니다. 조건을같은행에입력하면 AND 조건, 서로다른행에입력하면 OR 조건으로연결됩니다. 조건지정원리는고급필터와동일합니다. 314_ 컴퓨터활용능력 1 급실기

55 2009 년 11 월 29 일시행 02. 고급필터 _ 참고 : Section 05 고급필터 63 쪽 결과 고급필터 대화상자 03. 조건부서식 _ 참고 : Section 03 조건부서식 47 쪽 결과 조건지정 ROW( 인수 ) 는인수의행번호를반환하는함수이나, 인수를생략하면 ROW 함수가표시되는셀의행번호를반환하므로 =MOD(ROW( ),2)=0 과같이작성해도됩니다 년 4 회 1 급 ( 엑셀 ) D 형 _315

56 2009 년 4 회컴퓨터활용능력 1 급실기 ( 엑셀 ) D 형정답및 문제 2 계산작업 정답 성별별순위기록 (H3) : {=SMALL( IF( LEFT($B$3:$B$23,1)=H$2,$C$3:$C$23 ),$G3 )} 2. 기록내완수한인원수 (H9) : {=SUM( IF($C$3:$C$23<=H8,1,0) )} 3. 지역 (D3) : =VLOOKUP( MID(B3,2,2),$G$13:$H$20,2,FALSE ) 4. 성별 (E3) : =fn성별 (B3) Public Function fn성별 ( 선수코드 ) If Left( 선수코드, 1) = M Then fn성별 = 남 Else fn성별 = 여 End If End Function 5. 비고 (G27) : =IF( AND( ISBLANK(F27),D27>=8000,E27<=1000 ), 베스트셀러, ) 316_ 컴퓨터활용능력 1 급실기

57 2009 년 11 월 29 일시행 문제 3 분석작업 정답 01. 피벗테이블 _ 참고 : Section 11 피벗테이블 133 쪽 피벗테이블필드목록 창 그룹지정 부분합삭제 1 작성된피벗테이블의임의의셀을클릭한후 [ 피벗테이블도구 ] 디자인 레이아웃 보고서레이아웃 테이블형식으로표시를선택한다. 2 직위 (A3) 필드의바로가기메뉴에서 [ 필드설정 ] 을선택한후 필드설정 대화상자에서부분합의 없음 을선택하고 < 확인 > 을클릭한다 년 4 회 1 급 ( 엑셀 ) D 형 _317

58 2009 년 4 회컴퓨터활용능력 1 급실기 ( 엑셀 ) D 형정답및 셀서식 대화상자 02. 매크로작성 _ 참고 : Section 16 매크로작성 182 쪽 평균계산 매크로실행 데이터통합 대화상자 참조영역을지정할때는 직업 (B) 열이범위의첫번째열이되도록지정해야합니다. 318_ 컴퓨터활용능력 1 급실기

59 2009 년 11 월 29 일시행 문제 4 기타작업 정답 01. 차트수정 _ 참고 : Section 17 차트 198 쪽 데이터추가및차트종류변경 1 한계열에대해 2가지형태의차트로표현하기위해 중간 과 기말 계열의데이터를추가해야한다. [D3:E9] 영역을복사 (`C+ C``) 한후차트를선택하고붙여넣기 (`C+ V``) 한다. 2 차트에추가된 중간 과 기말 계열의차트종류를 표식이있는꺾은선형 으로변경한다. 02. 프로시저작성 _ 참고 : Section 18 프로시저 227 쪽 1 폼활성화및초기화프로시저 폼활성화프로시저 Private Sub cmd보유현황 _Click( ) 제품보유현황폼.Show End Sub 폼초기화프로시저 Private Sub UserForm_Initialize( ) cmb 제품명.RowSource = A4:A11 End Sub 2 < 조회 > 버튼에기능구현하기 Private Sub cmd조회 _Click( ) 참조행 = cmb제품명.listindex + 4 txt 생산단가 = Cells( 참조행, 2) txt 생산량 = Cells( 참조행, 3) txt 불량품수 = Cells( 참조행, 4) txt 재고량 = Cells( 참조행, 5) txt 총보유량 = Cells( 참조행, 6) End Sub 참조행 은 cmb 제품명 콤보상자에서선택한제품명이워크시트의몇번째행에있는지를구하기위해사용합니다. 실제데이터가입력되어있는행은워크시트의 4 행이므로콤보상자에서첫번째값을선택했다면참조행은 4 가되어야합니다. 콤보상자의 Listindex 는 0 에서시작하기때문에 4 를더해워크시트에있는자료의위치를정하는것입니다. 3 < 닫기 > 버튼에기능구현하기 Private Sub cmd닫기 _Click( ) Unload Me End Sub 2009 년 4 회 1 급 ( 엑셀 ) D 형 _319