E X A M I N A T I O N 실전모의고사 F 형 프로그램명 EXCEL 제한시간 45분 수험번호 : 성명 : < 유의사항 > 인적사항누락및잘못작성으로인한불이익은수험자책임으로합니다. 암호상자에다음의암호를입력하여문제파일을엽니다. 암호 : 9$4957 작성된답안의파일명은지정된경로및파일명을변경하지마시고저장해야합니다. 임의로변경시실격처리됩니다. < 답안파일명예 > C:\OA\ 수험번호 8 자리.xlsm 외부데이터위치 : C:\OA\ 파일명 별도의지시사항이없는경우, 다음과같이처리하면실격처리됩니다. 제시된시트및객체의순서를임의로변경한경우제시된시트및객체의이름을임의로변경한경우제시된시트및객체를임의로추가또는삭제한경우 답안은반드시문제에서지시또는요구한셀에입력하여야하며, 수험자가임의로셀의위치를변경하여입력한경우에는채점대상에서제외됩니다. 아울러지시하지않은셀의이동, 수정, 삭제, 변경등으로인해셀의위치가변경된경우에도관련문제모두채점대상에서제외됩니다. 차트의개체가중첩되어있거나, 동일한계산결과시트가복수로존재할경우에는해당개체나시트는채점대상에서제외됩니다. 별도의지시사항이없는경우, 주어진각시트의설정값또는기본설정값 (Default) 으로처리하십시오. 저장시간은별도로주어지지아니하므로제한된시간내에저장을완료해야합니다. 본문제에사용된용어는 MicroSoft Office 2007 기준으로작성되었습니다. 대한상공회의소 481 2 부실전편
문제 1 기본작업 (25 점 ) 주어진시트에서다음의과정을수행하고저장하시오. 1.` `도서대여 시트에서 외부데이터가져오기` 기능을이용하여 <` 도서대여.accdb`> 의데이터를다음조건에따라표시하시오.(15점 ) <`도서대여`> 테이블의데이터를 `도서번호`, `대여권수`, `이름`, `대여일`, `반납예정일` 열의순서로가져오시오. `도서번호` 가 A200이거나 A300이고, `대여일` 이 2000-03-30 이전인행만을대상으로하시오. 1차적으로는 `도서번호` 를기준으로오름차순정렬하고, 2차적으로는 `이름` 을기준으로오름차순으로정렬하시오. 데이터를되돌릴위치는기존워크시트의 [B3] 셀로지정하시오. 가져온데이터의표를 범위로변환 을실행하여정상범위로변환하시오. 2. 직위 시트에서다음과같이조건부서식을설정하시오.(5 점 ) 전문가의조언 실제시험에서는문제파일을자동으로불러오지만교재에서는 C:\ 길벗컴활 1급 \01 엑셀 \04 실전모의고사 폴더에서해당유형의문제파일 (1급 F형.xlsm) 을직접찾아서실행시켜야합니다. 실제시험에서는외부데이터가 C:\OA 폴더에있지만교재에서는 C:\ 길벗컴활1급 \01 엑셀 \04 실전모의고사 폴더에있습니다. [A2:L23] 영역에대해 입사일자 의월이8이고 업무수행 이2의배수인행전체에대해글꼴스타일은 `기울임꼴`, 글꼴색은 `파랑` 으로적용되는조건부서식을작성하시오. 단, 규칙유형은 수식을사용하여서식을지정할셀결정 을이용하시오. 3. 직위 시트에서다음과같이고급필터를수행하시오.(5 점 ) 부서명 이기획부이면서 직위 가사원이고, 업무수행 이 100인데이터중에서 이름, 입사일자, 점수, 평가 필드만순서대로표시하시오. 조건은 [A25:L27] 영역내에알맞게입력하시오. 결과는 [A28] 셀부터표시하시오. 문제 2 계산작업 (30 점 ) 계산작업 ` 시트에서다음의과정을수행하고저장하시오. 1. [ 표1] 에서고향주소와생년월일을이용하여해당조를 [F3:F8] 영역에계산하시오.(6 점 ) 조는고향주소가 `서울시` 이거나 `부천시` 이거나 `안양시` 이고, 태어난해가 1966년이하이면 A조`, 그렇지않으면 B조 를적용함 IF, AND, OR, LEFT, RIGHT, YEAR, MONTH 중알맞은함수를선택하여사용 2. [ 표1] 에서 [H3] 셀에나이가 35세이상이고, 가족수가 4명이상인회원의회비합을계산하여표시하시오.(6 점 ) 나이는생년월일을이용하여계산 ( 현재년도-출생년도 ) YEAR, LEFT, VALUE, SUM, TODAY 함수를사용한배열수식사용 3. 사용요금을계산하는사용자정의함수 `hs 사용요금` 을작성하여계산을수행하시오.(6 점 ) `hs사용요금` 은사용시간과할인시간을인수로받아사용요금을계산하여되돌려줌 실전모의고사 F 형 482
hs사용요금은사용시간이 30시간이상이면 ( 사용시간 - 할인시간 ) * 2500으로계산하고 30 시간미만이면 ( 사용시간 - 할인시간 ) 3000으로계산 `hs사용요금` 함수를이용하여사용시간 (C17:C25) 과할인시간 (D17:D25) 에대한사용요금을 [E17:E25] 영역에계산 Public Function hs사용요금 ( 사용시간, 할인시간 ) End Function 4. [ 표4] 에서중간값의차를 [B29:B31] 영역에계산하여표시하시오.(6 점 ) 레슨비용 (F17:F25) 의전체중간값과각운동의중간값의차를계산 중간값의차는양수로표시 MEDIAN, MODE, ABS, IF 중알맞은함수를사용한배열수식사용 5. [ 표3] 을참조하여 [D29] 셀에운동명이 `수영` 이고사용시간이 20 이상인회원중최소사용요금을계산하여표시하시오.(6 점 ) MIN, IF 함수를사용한배열수식사용 문제 3 분석작업 (20 점 ) 주어진시트에서다음의과정을수행하고저장하시오. 1.` `도서대여현황` 시트에대하여다음과같이피벗테이블을작성하시오.(10 점 ) 피벗테이블의외부데이터기능을이용하여 `도서대여.accdb` 의 <` 도서대여`> 테이블에서 `이름`, `주소`, `대여일`, `나이` 의열만이용하시오. 레이아웃과위치는 < 그림 > 을참조하여작성하시오. 나이의평균은 전체에대한비율 로표시하시오. 대여일은월을기준으로그룹을지정하시오. 값영역에표시되는각평균나이의비율은셀서식을이용하여소수 1자리까지표시하시오. 483 2 부실전편
2.` ` 자동차판매 ` 시트에대하여다음과같은기능을수행하는매크로를작성하시오.( 각 5 점 ) 1 데이터통합기능을이용하여 [A3:D8], [F3:I8], [K3:N8] 영역에대해각영업소별 `쏘나더`, `마가넷`, `크레포스` 의최대값을 [B12] 셀부터작성하는매크로를생성하고, 매크로이름은 `최대판매대수` 로정의하시오. `최대판매대수` 매크로는 `최대판매대수` 도형에지정하시오. 2 데이터통합기능을이용하여 [A3:D8], [F3:I8], [K3:N8] 영역에대해각영업소별 `쏘나더`, `마가넷`, `크레포스` 의최소값을 [B12] 셀부터작성하는매크로를생성하고, 매크로이름은 `최소판매대수` 로정의하시오. `최소판매대수` 매크로는 `최소판매대수` 도형에지정하시오. 셀포인터의위치에관계없이매크로가실행되어야정답으로인정됨 문제 4 기타작업 (25 점 ) 주어진시트에서다음의과정을수행하고저장하시오. 1.` `중간고사현황` 시트에서다음의지시사항및그림에따라차트를수정하시오.( 각 2점 ) 차트는반드시문제에서제공한차트를사용하여야하며, 신규로차트작성시 0 점처리됨 1 그림과같이차트제목, 가로 ( 항목 ) 축제목, 세로 ( 값 ) 축제목을입력하고 [B15:H29] 영역에위치시키시오. 2 그림과같이세로 ( 값 ) 축의최대값, 최소값, 주단위를설정하시오. 3 범례의배치를 `위쪽`, 글꼴은 굴림체, 테두리색은 `빨강` 으로지정하시오. 4 `수학` 계열에레이블이표시되도록설정하시오. 5 옆면영역을 `종이가방` 질감으로채우시오. 2.` `티켓관리` 시트에서다음과같은작업을수행하고저장하시오.( 각 5점 ) 1 < 티켓예매`> 버튼을클릭하면 `티켓관리` 폼이나타나도록프로시저를작성하고폼이실행되면 [G4:G7] 영역의값들이콤보상자 ( 영화명 ) 의목록에추가되고목록중첫번째항목이콤보상자에표시되도록프로시저를작성하시오. 실전모의고사 F 형 484
2 매수의스핀 ( 매수스핀 ) 버튼을누르면증감된숫자가매수 ( 매수 ) 에표시되고, 금액 ( 금액 ) 에는단가 매수가표시되도록프로시저를작성하시오. 3 티켓관리` 폼에데이터를입력하고, < 입력`> 버튼 ( 입력 ) 을클릭하면폼의영화명 ( 영화명 ), 단가 ( 단가 ), 매수 ( 매수 ), 금액 ( 금액 ) 의데이터가워크시트의가장마지막행의해당위치에표시되도록프로시저를작성하시오. 금액은천단위구분기호를표시하여입력하도록설정하시오. 할인금액은입력받은금액의값이 40000 이상이면금액의 10% 가표시되고, 아니면아무것도표시되지않도록프로시저를작성하시오. 폼의 <`입력`> 버튼을클릭하면영화명 ( 영화명 ), 단가 ( 단가 ), 매수 ( 매수 ), 금액 ( 금액 ) 에는새로운값이입력될수있도록설정하시오. 485 2 부실전편
E X A M I N A T I O N 실전모의고사정답및해설 F 형 문제 1 기본작업 정답 01. 외부데이터가져오기 02. 조건부서식 03. 고급필터 실전모의고사 F 형 486
문제 1 따라하기 01. 외부데이터가져오기 1. ` 쿼리마법사 - 열선택 ` 대화상자 02. 조건부서식 ` 새서식규칙 ` 대화상자 2. ` 쿼리마법사 - 데이터필터 ` 대화상자 03. 고급필터 1. 조건과추출할필드 2. ` 고급필터 ` 대화상자 3. ` 쿼리마법사 ```-``` 정렬순서 ` 대화상자 487 2 부실전편
문제 2 계산작업 정답 1 2 3 4 5 문제 2 따라하기 1 2 3 조 (F3) =IF( AND( OR( LEFT(B3,3)= 서울시,LEF`T(B3,3)= 부천시, LEFT(B3,3)= ` 안양시 ),YEAR(C3)<=1966), A 조, ``B 조 ) 회비합 (H3) {=SUM( (YEAR( TODAY(````)`)` - YEAR(C3:C8)>=35) * (VALUE( LEFT(D3:D8,1) )>=4) * E3:E8`)} 사용요금 (E17) =hs사용요금 (C17,D17) [ 사용자정의함수 ] Visual Basic Editor의모듈에다음과같이코드를입력한다. Public Function hs 사용요금 ( 사용시간, 할인시간 ) If 사용시간 >= 30 Then hs 사용요금 = ( 사용시간 - 할인시간 ) * 2500 Else hs 사용요금 = ( 사용시간 - 할인시간 ) * 3000 End If End Function 4 5 중간값의차 (B29) {=ABS( MEDIAN($F$17:$F$25)-MEDIAN( IF($B$17: $B$25=A29, $F$17:$F$25)) )} 최소사용요금 (D29) {=MIN( IF( (B17:B25= 수영 ) * (C17:C25>=20), E17:E25` ) )} 실전모의고사 F 형 488
문제 3 분석작업 정답 02. 매크로 1 최대판매대수 매크로 2 최소판매대수 매크로 문제 3 따라하기 01. 피벗테이블 1. ` 데이터베이스선택 대화상자 3. ` 피벗테이블필드목록 창 2. ` 쿼리마법사 - 열선택 대화상자 4. ` 값필드설정 대화상자 489 2 부실전편
5. ` 그룹화 ` 대화상자 3. ` 통합 ` 대화상자에서그림과같이지정하고, < 확인 > 을클릭한다. 6. 셀서식지정 4. 임의의셀을클릭한후 기록중지 ( )` 아이콘을클릭한다. 2 `최소판매대수 매크로 1. ` 최소판매대수` 도형에 `최소판매대수` 매크로를지정한다. 2. [A11:D16] 영역을블록으로지정하고, [ 데이터 ] 데이터도구 통합을클릭한다. 3. ` 통합 대화상자에서함수만 `최소값` 으로변경하고, < 확인 > 을클릭한다. 02. 매크로 1 `최대판매대수 매크로 1. ` 최대판매대수` 도형에 `최대판매대수` 매크로를지정한다. 2. [A11:D16] 영역을블록으로지정하고, [ 데이터 ] 데이터도구 통합을클릭한다. 4. 임의의셀을클릭한후 기록중지 ( )` 아이콘을클릭한다. 실전모의고사 F 형 490
문제 4 기타작업 정답 01. 차트서식 2 세로 ( 값 ) 축최대값, 최소값, 주단위설정 02. VBA 1 < 티켓예매 > 버튼과폼초기화프로시저작성하기 < 티켓예매 > 버튼클릭프로시저 Private Sub CommandButton1_Click( ) 티켓관리.Show End Sub 폼초기화프로시저 Private Sub UserForm_Initialize( ) 영화명.RowSource = G4:G7` 영화명 = 영화명.List(0, 0) End Sub 2 < 매수스핀 > 버튼변경프로시저 5 옆면영역을질감으로채우기 Private Sub 매수스핀 _Change( ) 매수 = 매수스핀금액 = 단가 * 매수 End Sub 3 < 입력 > 버튼클릭프로시저 Private Sub 입력 _Click( ) 입력행 = [a3].row + [a3].currentregion.rows.count Cells( 입력행, 1) = 영화명 Cells( 입력행, 2) = 단가 Cells( 입력행, 3) = 매수 Cells( 입력행, 4) = Format( 금액, #,### ) If 금액 >= 40000 Then Cells( 입력행, 5) = 금액 * 0.1 End If 영화명 = ` 단가 = ` 매수 = ` 금액 = ` End Sub ` 입력행 ` 변수에기준이되는셀 [a3] 의행번호 3 과 [a3] 에연결된데이터범위의행수 1 을더하여치환합니다 (3+1=4). ` 영화명 ` 의값을 4 행 1 열에입력합니다. 나머지도동일한방법으로수행합니다. ` 금액 ` 의값이 40000 이상이면 4 행 5 열에 금액 의 10% 를입력합니다. 491 2 부실전편
E X A M I N A T I O N 실전모의고사 G 형 프로그램명 EXCEL 제한시간 45분 수험번호 : 성명 : < 유의사항 > 인적사항누락및잘못작성으로인한불이익은수험자책임으로합니다. 암호상자에다음의암호를입력하여문제파일을엽니다. 암호 : 7$4256 작성된답안의파일명은지정된경로및파일명을변경하지마시고저장해야합니다. 임의로변경시실격처리됩니다. < 답안파일명예 > C:\OA\ 수험번호 8 자리.xlsm 외부데이터위치 : C:\OA\ 파일명 별도의지시사항이없는경우, 다음과같이처리하면실격처리됩니다. 제시된시트및객체의순서를임의로변경한경우제시된시트및객체의이름을임의로변경한경우제시된시트및객체를임의로추가또는삭제한경우 답안은반드시문제에서지시또는요구한셀에입력하여야하며, 수험자가임의로셀의위치를변경하여입력한경우에는채점대상에서제외됩니다. 아울러지시하지않은셀의이동, 수정, 삭제, 변경등으로인해셀의위치가변경된경우에도관련문제모두채점대상에서제외됩니다. 차트의개체가중첩되어있거나, 동일한계산결과시트가복수로존재할경우에는해당개체나시트는채점대상에서제외됩니다. 별도의지시사항이없는경우, 주어진각시트의설정값또는기본설정값 (Default) 으로처리하십시오. 저장시간은별도로주어지지아니하므로제한된시간내에저장을완료해야합니다. 본문제에사용된용어는 MicroSoft Office 2007 기준으로작성되었습니다. 대한상공회의소 실전모의고사 G 형 492
문제 1 기본작업 (25 점 ) 주어진시트에서다음의과정을수행하고저장하시오. 1.` `학과성적` 시트에서 외부데이터가져오기` 기능을이용하여 <` 학생성적.accdb`> 의데이터를다음조건에따라표시하시오.(15점 ) <` 학생성적`> 테이블의데이터를 `학번`, `이름`, `학과코드`, `TOEIC`, `컴퓨터`, `교양`, `평가` 열의순서로가져오시오. ``학과코드 가 È1 이거나 È2 이고, ``TOEIC` 이 50 이상인행만을대상으로하시오. 1차적으로는 `학과코드` 를기준으로오름차순정렬하고, 2차적으로는 `학번` 을기준으로내림차순으로정렬하시오. 데이터를되돌릴위치는기존워크시트의 [A3] 셀로지정하시오. 가져온데이터의표를 범위로변환 을실행하여정상범위로변환하시오. 2. ` 게임매장 ` 시트에서다음과같이조건부서식을설정하시오.(5 점 ) [A4:F14] 영역에대해 매출액 이가장크거나 이익금 이가장큰행전체에대해글꼴스타일은 굵은기울임꼴, 글꼴색은 ``파랑` 으로적용되는조건부서식을작성하시오. 단, 규칙유형은 수식을사용하여서식을지정할셀결정 을이용하시오. 3. ` 게임매장 ` 시트에서다음과같이고급필터를수행하시오.(5 점 ) 수량` 이 20 이상인것중에서 매출액 이 100000 이상또는 이익금 이 80000 이상이거나, 수량이 10 이하이고대상이 16세인데이터를표시하시오. 조건은 [A16:F19] 영역내에알맞게입력하시오. 결과는 [A22] 셀부터표시하시오. 문제 2 계산작업 (30 점 ) 계산작업 ` 시트에서다음의과정을수행하고저장하시오. 1. [ 표1] 에서직무별각직급의사원수를 [B3:C6] 영역에계산하시오.(6 점 ) 숫자뒤에 `명` 이표시되도록할것 ( 예 : 8명 ) [A9:E29] 영역 ([ 표2]) 을참조하여계산 SUM 함수와 & 연산자를사용한배열수식사용 2. [ 표1] 에서직무별로성적의 50% 위치의백분위수를 [D3:D6] 영역에계산하여표시하시오.(6 점 ) [A9:E29] 영역 ([ 표2]) 을참조하여계산 PERCENTILE, IF 함수를사용한배열수식사용 3. [ 표3] 에서직위를이용하여상여비율을 [J3:J11] 영역에계산하여표시하시오.(6 점 ) [G16:I20] 영역 ([ 표4) 을참조하여계산 INDEX, HLOOKUP, VLOOKUP 중알맞은함수를사용하여계산 493 2 부실전편
4. 지급액을계산하는사용자정의함수 `fn 지급액 ` 을작성하여계산을수행하시오.(6 점 ) `fn 지급액 ` 은기본급과상여비율을인수로받아지급액을계산하여되돌려줌 지급액은기본급 (1 + 상여비율 ) `fn지급액` 함수를이용하여기본급 (I3:I11) 과상여비율 (J3:J11) 에대한지급액을 [K3:K11] 영역에계산 Public Function fn지급액 ( 기본급, 상여비율 ) End Function 5. [ 표5] 에서직위별상여금차지율을 [H24:H28] 영역에계산하여표시하시오.(6 점 ) [G2:K11] 영역 ([ 표3]) 을참조하여계산 직위별상여금차지율은직위별상여금 / 총상여금 (J12) 으로계산하여적용 ( 상여금 = 기본급 상여비율 ) 상여금차지율은소수셋째자리에서자리내림하여둘째자리까지표시할것 ROUNDDOWN, SUM 함수를사용한배열수식사용 문제 3 분석작업 (20 점 ) 주어진시트에서다음의과정을수행하고저장하시오. 1.` `사이트관리` 시트에서다음과같이피벗테이블을작성하시오.(10점 ) ` 피벗테이블의외부데이터기능을이용하여 사이트운영비.accdb` 의 <` 사이트비교`> 테이블에서 `개설일`, `쇼핑몰`, `DB운영비`, 회원수 의열만을이용하여그림과같이피벗테이블보고서를작성하시오. 개설일은월단위로그룹을지정하시오. DB운영비에 `쉼표스타일 (,) 을적용하시오. `6월` 의 ` 종합쇼핑몰` 에관한자료만별도의시트에작성하시오 ( 시트명을 `6월종합쇼핑몰` 로지정하고, `사이트관리` 시트앞에위치시킨다.). 2.` ` 매출이익 ` 시트에대하여다음과같은기능을수행하는매크로를작성하시오.( 각 5 점 ) 1 목표값찾기기능을이용하여영업이익률 (H3) 이 70% 가되려면인건비 (C3) 가얼마가되어야하는지를계산하는매크로를생성하고, 매크로이름은 `영업이익률증가` 로정의하시오. `영업이익률증가` 매크로는 < 목표값찾기 > 버튼에지정하시오. 2 목표값찾기기능을이용하여영업이익률 (H3) 이 60% 가되려면인건비 (C3) 가얼마가되어야하는지를계산하는매크로를생성하고, 매크로이름은 `원상태로` 로정의하시오. 실전모의고사 G 형 494
원상태로` 매크로는 <`원상태로 > 버튼에지정하시오. 셀포인터의위치에관계없이매크로가실행되어야정답으로인정됨 문제 4 기타작업 (25 점 ) 주어진시트에서다음의과정을수행하고저장하시오. 1. `판매현황` 시트에서다음의지시사항및그림에따라차트를수정하시오.( 각 2점 ) 차트는반드시문제에서제공한차트를사용하여야하며, 신규로차트작성시 0 점처리됨 1 차트제목의글꼴스타일은 `기울임꼴`, 크기는 14, 글꼴색은 `파랑 으로지정하시오. 2 값축의글꼴크기는 9, 표시형식은 쉼표스타일 (,) 로표시하시오. 3 범례의배치를 아래쪽, 글꼴은 굴림체, 크기는 9로지정하시오. 4 차트영역을 `파랑박엽지 질감으로채우시오. 5 2003년 2월에대해서만데이터레이블을 `값 으로지정하시오. 2.` `송금현황` 시트에서다음과같은작업을수행하고저장하시오.( 각 5점 ) 1 < 송금 > 버튼을클릭하면 `계좌이체` 폼이나타나도록프로시저를작성하시오. 2 그림과같이사용자정의폼 계좌이체 에서송금자 (cmb송금자) 를선택하고, < 계좌조회 > 버튼 (cmd계좌조회) 을클릭하면송금자 (cmb송금자) 에해당하는계좌 (txt계좌), 금액 (txt금액) 에해당하는시트의자료가폼에나타나도록 Listindex 속성을이용하여작성하시오. 폼에데이터가입력될때표의행과폼의입력내용이일치하도록작성하시오. 3 송금현황 시트가활성화되면 [F2] 셀에입력된 길벗은행 이지워지도록프로시저를작성하시오. 495 2 부실전편
E X A M I N A T I O N 실전모의고사정답및해설 G 형 문제 1 기본작업 정답 01. 외부데이터가져오기 02. 조건부서식 03. 고급필터 실전모의고사 G 형 496
문제 1 따라하기 01. 외부데이터가져오기 1. ` 쿼리마법사 - 열선택 대화상자 02. 조건부서식 ` 새서식규칙 대화상자 2. ` 쿼리마법사 - 데이터필터 대화상자 03. 고급필터 1. ` 다음그림과같이조건지정 2. ` 고급필터 ` 대화상자 3. ` 쿼리마법사 ```-``` 정렬순서 대화상자 497 2 부실전편
문제 2 계산작업 정답 1 2 3 4 5 문제 2 따라하기 1 6 급 (B3) {=SUM( ($A$10:$A$29=$A3) * ($B$10:$B$29=B$2) ) & 명 } 2 50 번째백분위수 (D3) {=PERCENTILE( IF($A$10:$A$29=A3, $E$10:$E$29), 0.5 )} 3 상여비율 (J3) 4 =VLOOKUP(H3, $G$16:$I$20, 3, FALSE) 지급액 (K3) =fn 지급액 (I3, J3) [ 사용자정의함수 ] Visual Basic Editor 의모듈에다음과같이코드를입력한다. Public Function fn 지급액 ( 기본급, 상여비율 ) fn 지급액 = 기본급 * (1 + 상여비율 ) End Function 5 상여금차지율 (H24) {=ROUNDDOWN( SUM( ($H$3:$H$11=G24) * ($I$3:$I$11*$J$3:$J$11) )/$J$12, 2 )} 실전모의고사 G 형 498
문제 3 분석작업 정답 01. 피벗테이블 ( 6 월 의종합쇼핑몰 ) 02. 매크로 1 영업이익률증가 매크로 2 원상태로 매크로 문제 3 따라하기 01. 피벗테이블 1. ` 데이터베이스선택 대화상자 2. ` 쿼리마법사 - 열선택 대화상자 499 2 부실전편
3. ` 피벗테이블필드목록 창 2. [ 데이터 ] 데이터도구 가상분석 목표값찾기를선택한다. 3. ` 목표값찾기 대화상자에서그림과같이지정하고, < 확인 > 을클릭한다. 4. ` 그룹화 대화상자 4. ` 목표값찾기상태 대화상자에서 < 확인 > 을클릭하고, 기록중지 ( )` 아이콘을클릭한다. 2 `원상태로` 매크로 1.<`원상태로`> 버튼에 `원상태로` 매크로를지정한다. 2. [ 데이터 ] 데이터도구 가상분석 목표값찾기를선택한다. 3. ` 목표값찾기 대화상자에서그림과같이지정하고, < 확인 > 을클릭한다. 5. `6 월 ` 의 ` 종합쇼핑몰 ` 에대한자료추출 [F7] 셀을더블클릭 추가된시트의이름을 `6 월종합쇼핑몰 ` 로변경한다. 02. 매크로 1 `영업이익률증가` 매크로 1. <`목표값찾기`> 버튼에 `영업이익률증가` 매크로를지정한다. 4. ` 목표값찾기상태 대화상자에서 < 확인 > 을클릭하고, ` 기록중지 ( )` 아이콘을클릭한다. 실전모의고사 G 형 500
문제 4 기타작업 정답 01. 차트서식 2 세로 ( 값 ) 축서식지정 차트가선택된상태에서는리본메뉴와미니도구모음에서 쉼표스타일 (,) 을지정할수없으므로 축서식 대화상자의 표시형식 탭에서지정해야합니다. 1. `세로 ( 값 ) 축의미니도구모음에서글꼴크기를 9로지정한다. 2. 세로 ( 값 ) 축의바로가기메뉴에서 [ 축서식 ] 을선택한후 축서식 대화상자의 표시형식 탭에서범주와소수자릿수, 기호를그림과같이지정한후 < 닫기 > 를클릭한다. 5 데이터요소에값표시 ` 오른쪽딸깍 ` 딸깍 02. VBA 1 < 송금 > 버튼클릭프로시저 Private Sub CommandButton1_Click( ) 계좌이체.Show End Sub 2 < 계좌조회 > 버튼클릭프로시저 4 차트영역서식변경 Private Sub cmd 계좌조회 _Click( ) 참조행 = cmb 송금자.ListIndex + 4 cmb 송금자 = Cells( 참조행, 1) txt 계좌 = Cells( 참조행, 2) txt 금액 = Cells( 참조행, 3) End Sub cmb 송금자.ListIndex 는콤보상자에서선택한송금자의상대위치를반환합니다. 콤보상자에서상대적인위치는 0 에서시작하므로 최도움 을선택했다면 cmb 송금자.ListIndex 는 2 를반환합니다. 워크시트에서 최도움 에대한정보는 6 행에입력되어있으므로 최도움 이있는행을지정하기위해 cmb 송금자.ListIndex 에서반환한값 2 에 4 를더한것입니다. 결론적으로 4 를더한이유는참조표의실제데이터의위치가워크시트의 4 행에서시작하기때문입니다. 3 시트활성화프로시저 Private Sub Worksheet_Activate( ) [F2].Clear End Sub 501 2 부실전편
E X A M I N A T I O N 실전모의고사 H 형 프로그램명 EXCEL 제한시간 45분 수험번호 : 성명 : < 유의사항 > 인적사항누락및잘못작성으로인한불이익은수험자책임으로합니다. 암호상자에다음의암호를입력하여문제파일을엽니다. 암호 : 2!1076 작성된답안의파일명은지정된경로및파일명을변경하지마시고저장해야합니다. 임의로변경시실격처리됩니다. < 답안파일명예 > C:\OA\ 수험번호 8 자리.xlsm 외부데이터위치 : C:\OA\ 파일명 별도의지시사항이없는경우, 다음과같이처리하면실격처리됩니다. 제시된시트및객체의순서를임의로변경한경우제시된시트및객체의이름을임의로변경한경우제시된시트및객체를임의로추가또는삭제한경우 답안은반드시문제에서지시또는요구한셀에입력하여야하며, 수험자가임의로셀의위치를변경하여입력한경우에는채점대상에서제외됩니다. 아울러지시하지않은셀의이동, 수정, 삭제, 변경등으로인해셀의위치가변경된경우에도관련문제모두채점대상에서제외됩니다. 차트의개체가중첩되어있거나, 동일한계산결과시트가복수로존재할경우에는해당개체나시트는채점대상에서제외됩니다. 별도의지시사항이없는경우, 주어진각시트의설정값또는기본설정값 (Default) 으로처리하십시오. 저장시간은별도로주어지지아니하므로제한된시간내에저장을완료해야합니다. 본문제에사용된용어는 MicroSoft Office 2007 기준으로작성되었습니다. 대한상공회의소 실전모의고사 H 형 502
문제 1 기본작업 (25 점 ) 주어진시트에서다음의과정을수행하고저장하시오. 1.` `중간평가 시트에서 외부데이터가져오기` 기능을이용하여 <` 근무평가.accdb`> 의데이터를다음조건에따라표시하시오.(15점 ) <`사원중간평가`> 테이블의데이터를 `이름`, `입사`, `상반기`, `하반기`, `업무능력`, `2차평균`, `평가` 열의순서로가져오시오. `이름` 이강청기, 김구완, 김민정, 김수경이고, `업무능력` 이 A인행만을대상으로하시오. 1차적으로는 `입사` 를기준으로내림차순정렬하고, 2차적으로는 `평가` 를기준으로내림차순정렬하시오. 데이터를되돌릴위치는기존워크시트의 [A3] 셀로지정하시오. 가져온데이터의표를 범위로변환 을실행하여정상범위로변환하시오. 2. 연말정산 시트에서다음과같이조건부서식을설정하시오.(5 점 ) [A4:F17] 영역에대해서 직무 가일반직이고 호봉 이과장으로시작하는행전체에대해글꼴스타일은 `기울임꼴, 글꼴색은 `파랑 으로적용되는조건부서식을작성하시오. 단, 규칙유형은 수식을사용하여서식을지정할셀결정 을이용하시오. 3. 연말정산 시트에서다음과같이고급필터를수행하시오.(5 점 ) `호봉 의끝에서 3자리가연구원이고, 연봉 을기준으로세번째까지큰데이터를표시하시오. 조건은 [A19:F21] 영역내에알맞게입력하시오. 결과는 [A22] 셀부터표시하시오. 문제 2 계산작업 (30 점 ) 계산작업 ` 시트에서다음의과정을수행하고저장하시오. 1. 성과급을계산하는사용자정의함수 ` 성과급 ` 을작성하여계산을수행하시오.(6 점 ) ` 성과급 ` 은판매실적을인수로받아성과급을계산하여되돌려줌 성과급은판매실적과성과급비율의곱으로계산 ( 성과급비율은판매실적이 20000 이상이면 25%, 10000 이상이면 20%, 10000 미만이면 15% 로할것 ) ` 성과급` 함수를이용하여판매실적 (E3:E11) 에대한성과급을 [F3:F11] 영역에계산 Public Function 성과급 ( 판매실적 ) End Function 2. [ 표 1] 의 [G3:G11] 영역에지급급여를계산하여표시하시오.(6 점 ) [B14:E15] 영역 ([ 표 2]) 을참조하여계산 총급여 = 기본급 + 성과급 503 2 부실전편
지급급여 = 총급여 (1 - 세금공제율 ) INDEX, HLOOKUP, VLOOKUP 중알맞은함수를선택하여사용 3. [ 표3] 에서직급별판매실적 (E3:E11) 의중앙값을 [B19:B22] 영역에계산하시오.(6 점 ) [A2:G11] 영역 ([ 표1]) 을참조하여계산 MEDIAN, IF 함수와 & 연산자를사용한배열수식사용 4. [ 표3] 에서직급별성과급의합계를 [C19:C22] 영역에계산하시오.(6 점 ) 성과급합계는판매실적이 10000 이상인사원의성과급합계임 [A2:G11] 영역 ([ 표1]) 을참조하여계산 SUM 함수를사용한배열수식사용 5. [E19] 셀에판매실적이 15,000 이하이고, 성과급이 1,000 이하인사원의인원수를계산하여표시하시오.(6 점 ) 조건은 [E21:F23] 영역에직접입력하여계산 [A2:G11] 영역 ([ 표1]) 을참조하여계산 COUNT, DCOUNTA, COUNTIF 함수중알맞은함수를선택하여사용 문제 3 분석작업 (20 점 ) 주어진시트에서다음의과정을수행하고저장하시오. 1.` `제품별판매현황 시트에서다음과같이피벗테이블을작성하시오.(10 점 ) 피벗테이블의외부데이터기능을이용하여 `가전제품판매.accdb 의 < 대리점별판매현황 > 테이블에서 제품명, 등급, 판매량, 단가 의열만이용하여피벗테이블을작성하시오. 레이아웃과위치는 < 그림 > 을참조하여작성하시오. 완성된피벗테이블보고서는 보고서 1 자동서식으로지정하시오. 데이터필드의계산필드삽입에서판매량과단가의곱을계산하는 판매액 필드를추가하여표시하시오. 실전모의고사 H 형 504
2. ` 매출정산 ` 시트에대하여다음과같은기능을수행하는매크로를작성하시오.( 각 5 점 ) 1 부분합을이용하여 담당자 별로 매출금액, 받은금액, 미수금 의합계를계산한후평균을계산하는매크로를생성하고, 매크로이름을 부분합작성 으로정의하시오. 담당자를기준으로내림차순으로정렬하시오. 평균과합계는표시되는순서에상관없이처리하시오. 부분합작성 매크로를 < 부분합 > 버튼에지정하시오. 2 부분합작성 매크로에서설정한부분합을해제시키는매크로를생성하고, 매크로이름을 부분합제거 로정의하시오. 부분합제거 매크로를 < 부분합제거 > 버튼에지정하시오. 셀포인터의위치에관계없이매크로가실행되어야정답으로인정됨 문제 4 기타작업 (25 점 ) 주어진시트에서다음의과정을수행하고저장하시오. 1.` `사업소설립` 시트에서다음의지시사항및그림에따라차트를작성하시오.( 각 2점 ) 1 그림과같이차트제목을지정하여차트를작성한후 [A2:E12] 영역에위치시키시오. 2 차트의제목을글꼴은 `궁서체, 글꼴크기는 14로설정하시오. 3 범례의글꼴크기는 8, 테두리색은 검정, 테스트 1, 채우기색은 흰색, 배경 1, 그림자는 오프셋대각선왼쪽위 로설정하시오. 4 데이터계열에레이블을설정하시오. 5 [ 별및현수막 ] 의폭발 1과화살표를이용하여아래그림과같이삽입한후 선색 은 검정, 텍스트 1, 선너비는 1pt, 채우기는 채우기없음 으로설정하시오. 505 2 부실전편
2.` `도서관리` 시트에서다음과같은작업을수행하고저장하시오.(15점 ) 1 < 도서대여 > 버튼을클릭하면 `도서대여` 폼이나타나도록프로시저를작성하고폼이실행되면오늘날짜가텍스트박스 ( 날짜 ) 에표시되고아래제시된값이콤보상자 ( 구분 ) 의목록에추가되도록프로시저를작성하시오 (With, Additem 이용 ). 학생군인일반 2 ` 도서대여` 폼에데이터를입력하고, < 입력 > 버튼 ( 입력 )`을클릭하면폼의날짜 ( 날짜 ), 구분 ( 구분 ), 권수 ( 권수 ), 금액 ( 금액 ) 의데이터가워크시트의가장마지막행의해당위치에표시되도록프로시저를작성하시오. 단, 폼의 < 입력`> 버튼을클릭하면구분 ( 구분 ), 권수 ( 권수 ), 금액 ( 금액 ) 에는새로운값이입력될수있도록설정하시오. 금액에는천단위구분기호가표시되어입력되도록프로시저를작성하시오. 3 `도서대여` 폼의 < 종료 > 버튼 ( 종료 ) 을클릭하면 `도서대여` 폼이화면과메모리에서사라지도록프로시저를작성하시오. 실전모의고사 H 형 506
E X A M I N A T I O N 실전모의고사정답및해설 H 형 문제 1 기본작업 정답 01. 외부데이터가져오기 02. 조건부서식 03. 고급필터 507 2 부실전편
문제 1 따라하기 01. 외부데이터가져오기 1. ` 쿼리마법사 - 열선택 대화상자 02. 조건부서식 ` 새서식규칙 대화상자 2. ` 쿼리마법사 - 데이터필터 대화상자 강청기김구완김민정김수경 03. 고급필터 1. [A19] 셀에 ` 호봉수, [A20] 셀에 `=RIGHT(B4, 3)= 연구원, [B19] 셀에 높은연봉, [B20] 셀에 `=D4>=LARGE ($D$4:$D$17,3) 을입력한다. 2. ` 고급필터 대화상자 3. `` 쿼리마법사 - 정렬순서 대화상자 실전모의고사 H 형 508
문제 2 계산작업 정답 1 2 3 4 5 문제 2 따라하기 1 성과급 (F3) = 성과급 (E3) [ 사용자정의함수 ] Visual Basic Editor의모듈에다음과같이코드를입력한다. Public Function 성과급 ( 판매실적 ) If 판매실적 >= 20000 Then 성과급 = 판매실적 * 0.25 ElseIf 판매실적 >= 10000 Then 성과급 = 판매실적 * 0.2 Else 성과급 = 판매실적 * 0.15 End If End Function 3 중앙값 (B19) {=MEDIAN( IF($C$3:$C$11=(A19 & 급 ), $E$3:$E$11) )} 4 성과급합계 (C19) {=SUM( ($C$3:$C$11=(A19 & 급 )) * ($E$3:$E$11>=10000) * ($F$3:$F$11) )} 5 인원수 (E19) =DCOUNTA(A2:G11, 1, E21:F22) 조건지정 : 조건이 AND 조건이므로같은행에입력한다. 2 지급급여 (G3) =(D3+F3) * (1-HLOOKUP(D3+F3, $B $14:$E$15, 2)) 이문제는특정필드의값을구하는것이아니고조건에맞는레코드의개수를세는것이므로 DCOUNTA 함수의두번째인수인필드번호는데이터베이스범위내에있는임의의필드번호를입력하면됩니다. 즉 =DCOUNTA(A2:G11,1,E21:F22) 대신 =DCOUNTA(A2:G11,3,E21:F22) 를입력해도됩니다. 509 2 부실전편
문제 3 02. 매크로 1 부분합작성 매크로 분석작업 정답 문제 3 따라하기 01. 피벗테이블 1. ` 데이터베이스선택 대화상자 2. ` 쿼리마법사 - 열선택 대화상자 실전모의고사 H 형 510
3. ` 피벗테이블필드목록 창 4. [ 데이터 ] 윤곽선 부분합을클릭한후 부분합 대화상자에서그림과같이지정하고 < 확인 > 을클릭한다. 4. 계산필드추가 1 피벗테이블이작성된임의의셀을클릭한후 [ 피벗테이블도구 ] 옵션 도구 수식 계산필드를선택한다. 2 ` 계산필드삽입 대화상자에서그림과같이지정하고 < 추가 > 를클릭한다. 5. [A5:I28] 영역을다시블록으로지정하고 [ 데이터 ] 윤곽선 부분합을클릭한후 부분합 대화상자에서그림과같이지정하고 < 확인 > 을클릭한다. 3 ` 계산필드삽입 대화상자에서 < 확인 > 을클릭한다. 02. 매크로 1 ` 부분합작성 매크로 1.<` 부분합 > 버튼에 부분합작성 매크로를지정한다. 2. [A5:I23] 영역을블록으로지정한후 [ 데이터 ] 정렬및필터 정렬을클릭한다. 3. ` 정렬 대화상자에서그림과같이지정하고, < 확인 > 을클릭한다. 6. 임의의셀을클릭한후 기록중지 ( ) 아이콘을클릭한다. 2 부분합제거 매크로 1. ` 부분합제거 버튼에 부분합제거 매크로를지정한다. 2. `부분합이작성되어있는 [A5:I33] 영역의임의의셀을클릭한후 [ 데이터 ] 윤곽선 부분합을클릭한다. 3. ` 부분합 대화상자에서 < 모두제거 > 를클릭한다. 4. ` 기록중지 ( ) 아이콘을클릭한다. 511 2 부실전편
문제 4 기타작업 정답 01. 차트작성및서식 1 차트작성 1 < 도서대여 > 버튼과폼초기화프로시저작성 [G2:G9], [I2:I9] 영역을 C을누른채차례로선택한후 < 도서대여 > 버튼클릭프로시저 [ 삽입 ] 차트 원형 3차원원형 3차원원형을선택 Private Sub CommandButton1_Click( ) 한다. 도서대여.Show End Sub 3 범례의그림자지정 1. 범례 의바로가기메뉴에서 [ 범례서식 ] 을선택한다. 2. 범례서식 대화상자의 채우기 탭에서 단색채우기 폼초기화프로시저를선택하고색을 흰색, 배경 1 로지정한다. 3. 범례서식 대화상자의 테두리색 탭에서 실선 을선택하고색을 검정, 텍스트 1 로지정한다. 4. 범례서식 대화상자의 그림자 탭에서그림자 오프셋대각선왼쪽위 를지정한후 < 닫기 > 를클릭한다. 5 도형삽입 1. [ 삽입 ] 일러스트레이션 도형 별및현수막 폭발1을선택한후적당한크기로드래그한다. 2. 삽입된도형의바로가기메뉴에서 [ 도형서식 ] 을선택한후 도형서식 대화상자의 채우기 탭에서 채우기없음, 선스타일 탭에서너비를 1로지정하고 < 닫기 > 를클릭한다. 3. [ 삽입 ] 일러스트레이션 도형 선 화살표 ( ) 를선택한후적당한크기로드래그한다. 4. 삽입된두도형을모두선택한후 [ 그리기도구 ] 서식 도형스타일 도형윤곽선 검정, 텍스트 1을선택한다. 02. VBA Private Sub UserForm_Initialize( ) 날짜 = Date With 구분.AddItem 학생.AddItem 군인.AddItem 일반 End With End Sub 2 < 입력 > 버튼클릭프로시저 Private Sub 입력 _Click( ) 입력행 = [a3].row + [a3].currentregion.rows.count Cells( 입력행, 1) = CDate( 날짜 ) Cells( 입력행, 2) = 구분 Cells( 입력행, 3) = 권수 Cells( 입력행, 4) = Format( 금액, #,### ) 구분 = 권수 = 금액 = End Sub `입력행` 변수에기준이되는셀 [a3] 의행번호 3과 [a3] 에연결된데이터범위의행수1을더하여치환합니다 (3+1=4). `날짜` 의값을날짜형식으로변경하여 4행 1열에입력합니다. 나머지도동일한방법으로수행합니다. 3 < 종료 > 버튼클릭프로시저 Private Sub 종료 _Click( ) Unload Me End Sub 실전모의고사 H 형 512
E X A M I N A T I O N 실전모의고사 I 형 프로그램명 EXCEL 제한시간 45분 수험번호 : 성명 : < 유의사항 > 인적사항누락및잘못작성으로인한불이익은수험자책임으로합니다. 암호상자에다음의암호를입력하여문제파일을엽니다. 암호 : 7&7199 작성된답안의파일명은지정된경로및파일명을변경하지마시고저장해야합니다. 임의로변경시실격처리됩니다. < 답안파일명예 > C:\OA\ 수험번호 8 자리.xlsm 외부데이터위치 : C:\OA\ 파일명 별도의지시사항이없는경우, 다음과같이처리하면실격처리됩니다. 제시된시트및객체의순서를임의로변경한경우제시된시트및객체의이름을임의로변경한경우제시된시트및객체를임의로추가또는삭제한경우 답안은반드시문제에서지시또는요구한셀에입력하여야하며, 수험자가임의로셀의위치를변경하여입력한경우에는채점대상에서제외됩니다. 아울러지시하지않은셀의이동, 수정, 삭제, 변경등으로인해셀의위치가변경된경우에도관련문제모두채점대상에서제외됩니다. 차트의개체가중첩되어있거나, 동일한계산결과시트가복수로존재할경우에는해당개체나시트는채점대상에서제외됩니다. 별도의지시사항이없는경우, 주어진각시트의설정값또는기본설정값 (Default) 으로처리하십시오. 저장시간은별도로주어지지아니하므로제한된시간내에저장을완료해야합니다. 본문제에사용된용어는 MicroSoft Office 2007 기준으로작성되었습니다. 대한상공회의소 513 2 부실전편
문제 1 기본작업 (25 점 ) 주어진시트에서다음의과정을수행하고저장하시오. 1. `사이트운영비 시트에 외부데이터가져오기` 기능을이용하여 <` 사이트운영비.accdb`> 의데이터를다음의조건에따라표시하시오.(15점 ) <` 사이트비교`> 테이블의데이터를 `쇼핑몰`, `DB운영비`, `광고비`, `배너비`, `총액`, `개설일` 열의순서로가져오시오. ` 쇼핑몰` 이종합쇼핑몰이고, DB운영비, 광고비, 배너비가 300,000 미만인행만을대상으로하시오. `개설일` 을기준으로오름차순으로정렬하시오. 데이터를되돌릴위치는기존워크시트의 [A3] 셀로지정하시오. 가져온데이터의표를 범위로변환 을실행하여정상범위로변환하시오. 2. ` 자산내역 ` 시트에서다음과같이조건부서식을설정하시오.(5 점 ) [A2:I17] 영역에대해 `자산코드` 의왼쪽에서세번째문자가 G이고 내용연수 가 6 이상인데이터의행전체에대해글꼴스타일은 굵은기울임꼴, 글꼴색은 빨강 으로적용되는조건부서식을작성하시오. 단, 규칙유형은 수식을사용하여서식을지정할셀결정 을이용하시오. 3. ` 자산내역 ` 시트에서다음과같이고급필터를수행하시오.(5 점 ) ` 비품명 이디스크이면서 `구입수량` 이 5 이상인자료와 `비품명` 이컴퓨터이면서 ` 구입수량 이 3 이상인자료의 `비품명`, 내용연수, `경과연수`, `구입수량 필드만순서대로표시하시오. 조건은 [A19:I22] 영역내에알맞게입력하시오. 결과는 [A24] 셀부터표시하시오. 문제 2 계산작업 (30 점 ) 계산작업 ` 시트에서다음의과정을수행하고저장하시오. 1. [ 표1] 에서부서별남 (1), 여 (2) 사원의예금액평균을 [B3:C5] 영역에계산하시오.(6 점 ) 주민등록번호의 8번째자리가 1이면 `남`, 2이면 `여` 사원을의미함 [A9:I18] 영역 ([ 표2]) 을참조하여계산 AVERAGE, IF, MID 함수를사용한배열수식사용 2. [ 표2] 의 [D9:D18] 영역에주민등록번호를이용하여생년월일을계산하여표시하시오.(6 점 ) 주민등록번호앞의 6자리를이용하여생년월일표시 ( 예 : 781110`-2453210 `1978-11-10) DATE, MID 함수사용 실전모의고사 I 형 514
3. [ 표2] 의 [G9:G18] 영역에대출가능액을계산하여표시하시오.(6 점 ) 대출가능액은예금액이 8000 이상이고, 기혼이면예금액의 100%, 예금액이 5000 이상이면예금액의 80%, 그렇지않으면예금액의 60% 대출이가능함 CHOOSE, IF, AND, OR 중알맞은함수를선택하여사용 4. 총대여액을계산하는사용자정의함수 `sh 총대여액` 을작성하여계산을수행하시오.(6 점 ) `sh총대여액` 은대출가능액과지원액을인수로받아총대여액을계산하여되돌려줌 총대여액은대출가능액과지원액의합으로계산 `sh총대여액` 함수를이용하여대출가능액 (G9:G18) 과지원액 (H9:H18) 에대한총대여액을 [I9:I18] 영역에계산 Public Function sh총대여액 ( 대출가능액, 지원액 ) End Function 5. [ 표4] 의 [B22:B23] 영역에결혼여부별로예금액이가장작은사원의이름을계산하여표시하시오.(6 점 ) [A9:I18] 영역 ([ 표2]) 을참조하여계산 IF, MIN, MATCH, INDEX 함수를사용한배열수식사용 문제 3 분석작업 (20 점 ) 주어진시트에서다음의과정을수행하고저장하시오. 1.` `사원퇴직금` 시트에대하여다음과같이피벗테이블을작성하시오.(10 점 ) 피벗테이블의외부데이터기능을이용하여 `퇴직금현황.accdb` 의 <`퇴직금현황`> 테이블에서 `성명, `부서명, `직책`, `기본급`, `퇴직금` 의열만이용하시오. 레이아웃과위치는 < 그림 > 을참조하여작성하시오. 값영역의빈셀에는 `없음` 을표시하시오. 기본급과퇴직금의합계에천단위구분기호 (,) 를표시하시오. 2. ` 근무자료 ` 시트에대하여다음과같은기능을수행하는매크로를작성하시오.( 각 5 점 ) 1 데이터통합기능을이용하여 [A2:D9], [F2:I9], [A12:D19], [F12:I19] 영역에대해 `성혁재`, `서영실`, `심현아` 사원의결근일수와근무일의합계를 [L3] 셀부터작성하는매크로를생성하고, 매크로이름은 `근무자료1` 로정의하시오. `근무자료1` 매크로는 `총합계` 도형에지정하시오. 515 2 부실전편
2 데이터통합기능을이용하여 [A2:D9], [F2:I9], [A12:D19], [F12:I19] 영역에대해 `성혁재`, `서영실`, `심현아` 사원의결근일수와근무일의평균을 [L3] 셀부터작성하는매크로를생성하고, 매크로이름은 `근무자료2` 로정의하시오 `근무자료2` 매크로는 `총평균` 도형에지정하시오. 셀포인터의위치에관계없이매크로가실행되어야정답으로인정됨 문제 4 기타작업 (25 점 ) 주어진시트에서다음의과정을수행하고저장하시오. 1.` `성적표` 시트에서다음의지시사항및그림에따라차트를작성하시오.( 각 2점 ) 1 그림과같이차트제목, 항목축제목, 값축제목을지정하여차트를작성한후 [A9:F22] 영역에위치시키시오. 2 차트제목은글꼴을 굴림체, 크기를 15로설정하시오. 3 `평균` 계열에레이블이표시되도록설정하시오. 4 그림과같이값축의주단위를설정하시오. 5 그림영역서식의채우기색을 주황 으로설정하시오. 2.` `원서접수현황` 시트에서다음과같은작업을수행하고저장하시오.( 각 5점 ) 1 < 원서접수 > 버튼을클릭하면 `원서접수` 폼이나타나도록프로시저를작성하시오. 2 폼이실행되면아래와같은기능을수행하도록프로시저를작성하시오. [G4:H5] 영역의값들이콤보상자 ( 종목 ) 의목록에추가되고콤보상자 ( 급수 ) 의목록에 `1급`, `2급`, `3급` 이표시되도록프로시저를작성하시오. 워크시트가장마지막데이터의이름, 응시종목, 응시급수, 접수비가 `원서접수` 폼의이름 ( 이름 ), 응시종목 ( 종목 ), 응시급수 ( 급수 ), 접수비 ( 접수비 ) 에각각표시되도록프로시저를작성하시오. 실전모의고사 I 형 516
3 `원서접수` 폼의 < 종료 > 버튼 ( 종료 ) 을클릭하면 `원서접수` 폼이화면과메모리에서사라지도록프로시저를작성하시오. 517 2 부실전편
E X A M I N A T I O N 실전모의고사정답및해설 I 형 문제 1 기본작업 정답 01. 외부데이터가져오기 02. 조건부서식 03. 고급필터 실전모의고사 I` 형 518
문제 1 따라하기 01. 외부데이터가져오기 1. ` 쿼리마법사 - 열선택 대화상자 3. ` 쿼리마법사 ```-``` 정렬순서 ` 대화상자 2. ` 쿼리마법사 - 데이터필터 ` 대화상자 02. 조건부서식 ` 새서식규칙 대화상자 03. 고급필터 1. `[A19] 셀에 비품명 `, [A20] 셀에 ` 디스크 `, [A21] 셀에 컴퓨터, [B19] 셀에 구입수량, [B20] 셀에 `>=5, [B21] 셀에 `>=3 을입력한다. 2. `[A24:D24] 영역에각각 비품명, 내용연수, 경과연수, 구입수량 을입력한다. 3. ` 고급필터 대화상자 519 2 부실전편
문제 2 계산작업 정답 1 2 3 4 5 문제 2 따라하기 1 남 (1)(B3) {=AVERAGE( IF( ($A$9:$A$18=$A3) * (MID($C$9:$C $18,8,1)=MID(B$2,3,1)), $E$9:$E$18) ) } 2 생년월일 (D9) =DATE( MID(C9,1,2), MID(C9,3,2), MID(C9,5,2) ) 3 대출가능액 (G9) =IF( AND(E9>=8000,F9= 기혼 ), E9*100%, IF(E9 >=5000,E9*80%,E9*60%) ) 4 총대여액 (I9) =`sh 총대여액 (G9, H9) [ 사용자정의함수 ] Visual Basic Editor 의모듈에다음과같이코드를입력한다. Public Function sh 총대여액 ( 대출가능액, 지원액 ) sh 총대여액 = 대출가능액 + 지원액 End Function 실전모의고사 I` 형 520
5 최소예금자 (B22) {=INDEX($A$9:$I$18, MATCH( MIN( IF($F$9: $F$18=A22, $E$9:$E$18) ), ($F$9:$F$18=A22) * ($E$9:$E$18), 0), 2)} {=INDEX($A$9:$I$18, MATCH( MIN( IF($F$9:$F$18=A22, $E$9: $E$18) ), ($F$9:$F$18=A22) * ($E$9:$E$18), 0), 2)} 의의미 MIN( IF($F$9:$F$18=A22, $E$9:$E$18) ) : 결혼여부를비교하여결혼여부가 기혼 인사원들의예금액중최소값을구합니다. MATCH(, ($F$9:$F$18=A22) * ($E$9:$E$18), 0) : 번에서구한최소예금액값을예금액범위 ( 결혼여부가 기혼 인사람의예금액 ) 에서찾아그위치를일련번호로반환합니다. INDEX($A$9:$I$18,, 2) : 번에서구한일련번호를행번호로하고, 열번호는 2 로하여 [A9:I18] 영역에서행번호와열번호에해당하는내용을반환합니다. 문제 3 분석작업 정답 02. 매크로 1 근무자료1 매크로 2 근무자료2 매크로 문제 3 따라하기 01. 피벗테이블 1. ` 데이터베이스선택 대화상자 2. ` 쿼리마법사 - 열선택 대화상자 521 2 부실전편
3. ` 피벗테이블필드목록 창 3. ` 통합 대화상자에서그림과같이지정하고, < 확인 > 을클릭한다. 4. ` 피벗테이블옵션 ` 대화상자 4. 임의의셀을클릭한후 기록중지 아이콘 ( ) 을클릭한다. 2 `근무자료2 매크로 1. `총평균` 도형에 `근무자료2 매크로를지정한다. 2. [K2:M5] 영역을블록으로지정한후 [ 데이터 ] 데이터도구 통합을클릭한다. 3. `통합 대화상자에서함수만 `평균` 으로변경하고, < 확인 > 을클릭한다. 02. 매크로 1 `근무자료1 매크로 1. `총합계 도형에 `근무자료1 매크로를지정한다. 2. `[K2:M5] 영역을블록으로지정한후 [ 데이터 ] 데이터도구 통합을클릭한다. 4. 임의의셀을클릭한후 ` 기록중지 아이콘 ( ) 을클릭한다. 실전모의고사 I` 형 522
문제 4 기타작업 정답 01. 차트작성및서식 1 차트작성 1. [A2:A7], [C2:E7] 영역을 C을누른채차례로선택한후 [ 삽입 ] 차트 가로막대형 2차원가로막대형 누적가로막대형을선택한다. 2. 차트제목을 학생별성적표, 가로축제목을 점수, 세로축제목을 성명 으로지정한다. 02. VBA 1 < 원서접수 > 버튼클릭프로시저 Private Sub CommandButton1_Click( ) 원서접수.Show End Sub 2 폼초기화프로시저 Private Sub UserForm_Initialize( ) 종목.RowSource = G4:H5 급수.Addltem 1 급 급수.Addltem 2 급 급수.Addltem 3 급 입력행 = [a3].row + [a3].currentregion.rows.count - 1 이름 = Cells( 입력행, 1) 종목 = Cells( 입력행, 2) 급수 = Cells( 입력행, 3) 접수비 = Cells( 입력행, 4) End Sub 입력행 변수에 [a3] 셀의행번호와 [a3] 과연결된범위에있는데이터범위의행수를더하여치환합니다. `-1` 은기준행 (3행 ) 을제외하고, 순수하게데이터범위의행수를구하기위해사용된것입니다. 7행 1열 ( 입력행,1) 의값을폼의 이름 컨트롤에입력합니다. 나머지도동일한방법으로수행합니다. 3 < 종료 > 버튼클릭프로시저 Private Sub 종료 _Click( ) Unload Me End Sub 523 2 부실전편
E X A M I N A T I O N 실전모의고사 J 형 프로그램명 EXCEL 제한시간 45분 수험번호 : 성명 : < 유의사항 > 인적사항누락및잘못작성으로인한불이익은수험자책임으로합니다. 암호상자에다음의암호를입력하여문제파일을엽니다. 암호 : 4!1343 작성된답안의파일명은지정된경로및파일명을변경하지마시고저장해야합니다. 임의로변경시실격처리됩니다. < 답안파일명예 > C:\OA\ 수험번호 8 자리.xlsm 외부데이터위치 : C:\OA\ 파일명 별도의지시사항이없는경우, 다음과같이처리하면실격처리됩니다. 제시된시트및객체의순서를임의로변경한경우제시된시트및객체의이름을임의로변경한경우제시된시트및객체를임의로추가또는삭제한경우 답안은반드시문제에서지시또는요구한셀에입력하여야하며, 수험자가임의로셀의위치를변경하여입력한경우에는채점대상에서제외됩니다. 아울러지시하지않은셀의이동, 수정, 삭제, 변경등으로인해셀의위치가변경된경우에도관련문제모두채점대상에서제외됩니다. 차트의개체가중첩되어있거나, 동일한계산결과시트가복수로존재할경우에는해당개체나시트는채점대상에서제외됩니다. 별도의지시사항이없는경우, 주어진각시트의설정값또는기본설정값 (Default) 으로처리하십시오. 저장시간은별도로주어지지아니하므로제한된시간내에저장을완료해야합니다. 본문제에사용된용어는 MicroSoft Office 2007 기준으로작성되었습니다. 대한상공회의소 실전모의고사 J 형 524
문제 1 기본작업 (25 점 ) 주어진시트에서다음의과정을수행하고저장하시오. 1.` `가공현황 시트에서 외부데이터가져오기` 기능을이용하여 <`제품가공현황.accdb`> 의데이터를다음조건에따라표시하시오.(15점 ) <` 팀별가공현황`> 테이블의데이터를 `` 가공품명`, ` 가공팀`, ` 가공일`, `` 가공수량`, `` 가공원가``, ``목표매출액`` 열의순서로가져오시오. `가공팀` 이 `제조2팀` 이거나 `제조6팀` 이고, `가공품명` 이 `오렌지가공` 인행만을대상으로하시오. 1차적으로는 `가공팀` 을기준으로오름차순정렬하고, 2차적으로는 `가공수량` 을기준으로내림차순정렬하시오. 데이터를되돌릴위치는기존워크시트의 [A3] 셀로지정하시오. 가져온데이터의표를 범위로변환 을실행하여정상범위로변환하시오. 2. 계좌관리 시트에서다음과같이조건부서식을설정하시오.(5 점 ) [B3:H13] 영역에대해서 `개설지점 이송파지점이거나개설일자의연도가 1995 년이후인데이터의행전체에대해글꼴스타일은 `굵게`, 글꼴색은 `파랑 ` 으로적용되는조건부서식을작성하시오. 단, 규칙유형은 수식을사용하여서식을지정할셀결정 을이용하시오. 3. 계좌관리 시트에서다음과같이고급필터를수행하시오.(5 점 ) `개설지점 이강남지점또는수원지점중에서, `잔액` 이 0 미만이거나 60000 이상인데이터를표시하시오. 조건은 [B15:H21] 영역내에알맞게입력하시오. 결과는 [B22] 셀부터표시하시오. 문제 2 계산작업 (30 점 ) 계산작업 ` 시트에서다음의과정을수행하고저장하시오. 1. [ 표 1] 에서평균 (F3:F23) 을이용하여평점을 [G3:G23] 영역에계산하시오.(6 점 ) [K3:M11] 영역 ([ 표 2]) 을참조하여계산 VLOOKUP 함수사용 2. 판정을계산하는사용자정의함수 `sh 판정 ` 을작성하여계산을수행하시오.(6 점 ) `sh 판정 ` 은국어, 영어, 수학, 결석, 평균을인수로받아판정을계산하여되돌려줌 판정은국어, 영어, 수학이모두 60점이상이고, 결석이 1보다작고, 평균이 70점이상이면 합격 과국어, 영어, 수학의평균을연결하여표시하고, 그렇지않으면 불합격 을표시 ( 예 : 합격73) 평균은 INT 함수를사용하여정수만표시 `sh판정` 함수를이용하여국어 (B3:B23) 와영어 (C3:C23), 수학 (D3:D23), 결석 (E3:E23), 평균 (F3:F23) 에대한판정을 [H3:H23] 영역에계산 525 2 부실전편
Public Function sh판정 ( 국어, 영어, 수학, 결석, 평균 ) End Function 3. [ 표2] 의 [N3:P11] 영역에평점별각반의학생수를표시하시오.(6 점 ) [A3:I23] 영역 ([ 표1]) 을참조하여계산 SUM 함수를이용한배열수식사용 4. [ 표3] 의 [K17:M17] 영역에반별평균 (F3:F23) 을계산하되반별최고평균점수를제외한평균의반별평균을표시하시오.(6 점 ) [A2:I23] 영역 ([ 표1]) 을참조하여계산 정수부분까지만표시 AVERAGE, INT, IF, MAX 함수를사용한배열수식사용 5. [ 표4] 의 [K22:M22] 영역에반별로최대평균값과최소평균값의차이를계산하여표시하시오.(6 점 ) [A3:I23] 영역 ([ 표1]) 을참조하여계산 MAX, MIN, IF 함수를사용한배열수식사용 문제 3 분석작업 (20 점 ) 주어진시트에서다음의과정을수행하고저장하시오. 1.` `수산물가격표` 시트에대하여다음과같이피벗테이블을작성하시오.(10점 ) 피벗테이블의외부데이터기능을이용하여 `수산물가격표.accdb` 의 <` 수산물가격표`> 테이블에서 `품목`, `산지`, `상품`, `중품`, `하품` 의열만이용하시오. 레이아웃과위치는 < 그림 > 을참조하여작성하시오. 값영역의빈셀에는 *** 을표시하고, 열의총합계와행의총합계는표시하지마시오. 산지는 남해 만나타나도록하시오. 상품, 중품, 하품의합계에통화기호를표시하시오. 실전모의고사 J 형 526
2.` ` 콘도현황 ` 시트에대하여다음과같은기능을수행하는매크로를작성하시오.( 각 5 점 ) 1 목표값찾기기능을이용하여유형만고객의할인금액 (E9) 이 30,000이되려면사용요금 (D9) 이얼마가되어야하는지계산하는매크로를생성하고, 매크로이름은 `할인증가 ` 로정의하시오. `할인증가` 매크로는 < 목표값찾기1> 버튼에설정하시오. 2 목표값찾기기능을이용하여유형만고객의할인금액 (E9) 이 20,000이되려면사용요금 (D9) 이얼마가되어야하는지계산하는매크로를생성하고, 매크로이름은 `할인감소 ` ` 로정의하시오. `할인감소` ` 매크로는 <`목표값찾기2> 버튼에지정하시오. 셀포인터의위치에관계없이매크로가실행되어야정답으로인정됨 문제 4 기타작업 (25 점 ) 주어진시트에서다음의과정을수행하고저장하시오. 1.` `자급율 ` 시트에서다음의지시사항및그림에따라차트를수정하시오.( 각 2점 ) 차트는반드시문제에서제공한차트를사용하여야하며, 신규로차트작성시 0 점처리됨 1 차트종류를 < 그림 > 과같이변경하시오. 2 원본데이터를 [A3:C8] 영역으로변경하시오. 3 차트의제목은 `년도별내 / 외재현황`, 가로 ( 항목 ) 축제목은 `년도` 로입력하시오. 4 세로의주눈금선이나타나도록하시오. 5 차트영역의테두리스타일은 둥근모서리, 네온은 강조색 1, 8pt 네온 이나타나도록하시오. 527 2 부실전편
2.` `고과점수` 시트에서다음과같은작업을수행하고저장하시오.( 각 5점 ) 1 < 고과점수관리창`> 버튼을클릭하면 `사원고과` 폼이나타나도록프로시저를작성하고폼이실행되면 [H4:L8] 영역의값들이콤보상자 ( 부서 ) 의목록에추가되도록프로시저를작성하시오. 2` 사원고과` 폼의 < 종료 > 버튼 ( 종료 ) 을클릭하면 `사원고과` 폼이화면과메모리에서사라지도록프로시저를작성하시오. 3` 고과점수 시트에서셀의데이터가변경 (Change) 되면해당셀의글꼴이 굴림체 로설정되도록프로시저를작성하시오. 실전모의고사 J 형 528
E X A M I N A T I O N 실전모의고사정답및해설 J 형 문제 1 기본작업 정답 01. 외부데이터가져오기 02. 조건부서식 03. 고급필터 529 2 부실전편
문제 1 따라하기 01. 외부데이터가져오기 1. ` 쿼리마법사 - 열선택 대화상자 02. 조건부서식 ` 새서식규칙 ` 대화상자 2. ` 쿼리마법사 - 데이터필터 대화상자 03. 고급필터 1. 아래그림과같이조건지정 2. ` 고급필터 ` 대화상자 3. ` 쿼리마법사 - 정렬순서 대화상자 실전모의고사 J 형 530
문제 2 계산작업 정답 1 2 3 4 5 문제 2 따라하기 1 평점 (G3) =VLOOKUP(F3, $K$3:$M$11,3) 2 판정 (H3) =sh판정 (B3,C3,D3,E3,F3) [ 사용자정의함수 ] Visual Basic Editor의모듈에다음과같이코드를입력한다. Public Function sh 판정 ( 국어, 영어, 수학, 결석, 평균 ) If 국어 >= 60 And 영어 >= 60 And 수학 >= 60 And 결석 < 1 And 평균 >= 70 Then sh 판정 = 합격 & Int(( 국어 + 영어 + 수학 )/3) Else sh 판정 = 불합격 End If End Function 3 평점별각반학생수 (N3) {=SUM( ($G$3:$G$23=$M3) * ($I$3:$I$23=N$2) )} 4 반별평균 (K17) {=INT( AVERAGE( IF( ($I$3:$I$23=K16) * (($I$3:$I$ 23=K16) * ($F$3:$F$23)< >MAX(($I$3:$I$23=K16) *($F$3:$F$23))), $F$3:$F$23) ) )} {=INT( AVERAGE( IF( ($I$3:$I$23=K16) * (($I$3:$I$23=K16) * ($F$3: $F$23)< >MAX(($I$3:$I$23=K16) * ($F$3:$F$23) ) ), $F$3:$F$23) ) )} 의의미 ($I$3:$I$23=K16) * (($I$3:$I$23=K16) * ($F$3:$F$23)< >MAX(($I$3: $I$23=K16) * ($F$3:$F$23))) : 반이같고, 평균이최고평균 ( 반이같은평균에서 ) 과같지않은평균을대상으로합니다. AVERAGE( IF(, $F$3:$F$23) ) : 을만족할경우해당평균의평균을구합니다. INT( ) : 를정수형태로반환합니다. 5 반별평균차 (K22) {=MAX( IF($I$3:$I$23=K21, $F$3:$F$23) ) - MIN( IF($I$3:$I$23=K21, $F$3:$F$23) )} 531 2 부실전편
문제 3 분석작업 정답 02. 매크로 1 할인증가 매크로 2 할인감소 매크로 문제 3 따라하기 01. 피벗테이블 1. ` 데이터베이스선택 대화상자 2. ` 쿼리마법사 - 열선택 대화상자 실전모의고사 J 형 532
3. ` 피벗테이블필드목록 창 5. 행레이블 데이터선택 4. ` 피벗테이블옵션 대화상자 6. 부분합제거 품목 이표시되어있는셀의바로가기메뉴에서 [ 필드설정 ] 을선택한후그림과같이지정하고, < 확인 > 을클릭한다. 7. 데이터정렬 [A2:D14] 영역을블록으로지정한후미니도구모음에서 가운데맞춤 ( ) 을클릭한다. 02. 매크로 1 `할인증가` 매크로 1. < 목표값찾기1`> 버튼에 `할인증가` 매크로를지정한다. 2. `목표값찾기 대화상자에서그림과같이지정하고, < 확인 > 을클릭한다. [ 피벗테이블도구 ] 디자인 레이아웃 총합계 행및열의총합계해제를선택해도됩니다. 533 2 부실전편
3. ` 목표값찾기상태 대화상자에서 < 확인 > 을클릭하고, 기록중지 ( )` 아이콘을클릭한다. 3. ` 목표값찾기상태 대화상자에서 < 확인 > 을클릭하고, 기록중지 ( )` 아이콘을클릭한다. 2 `할인감소` 매크로 1. < 목표값찾기2`> 버튼에 `할인감소` 매크로를지정한다. 2. `목표값찾기` 대화상자에서그림과같이지정하고, < 확인 > 을클릭한다. 문제 4 기타작업 정답 01. 차트서식 2 원본데이터변경 1. [C3:C8] 영역을범위로지정한후 C+C를눌러복사한다. 2. 차트를선택한후 C+V를눌러붙여넣기한다. 4 세로 ( 값 ) 축의주눈금선표시차트를선택한후 [ 차트도구 ] 레이아웃 축 눈금선 기본세로눈금선 주눈금선을선택한다. 5 네온표시차트를선택한후 [ 차트도구 ] 서식 도형스타일 도형효과 네온 강조색 1, 8pt 네온을선택한다. 02. VBA 1 < 고과점수관리창 > 버튼과폼초기화프로시저작성 < 고과점수관리창 > 버튼클릭프로시저 폼초기화프로시저 Private Sub UserForm_Initialize( ) 부서.RowSource = H4:L8 End Sub 2 < 종료 > 버튼클릭프로시저 Private Sub 종료 _Click( ) Unload Me End Sub 3 워크시트변경이벤트프로시저 Private Sub Worksheet_Change(ByVal Target As Range) Target.Font.Name = 굴림체 End Sub Private Sub CommandButton1_Click( ) 사원고과.Show End Sub 실전모의고사 J 형 534