MOS Excel 2007 Excel 2007 평가항목 [ 출제범위 ] 평가항목 데이터관리및분석 부분합사용범위에그룹및개요조건추가필터정의및적용데이터유효성사용목록범위만들기및수정시나리오만들기및표시도구사용하여데이터분석피벗테이블, 피벗차트보고서만들기조회함수사용 (VLOOKUP 사용 ) 데이터베이스함수사용참조하는셀추적및오류추적데이터유효성사용수식만들기및수정수식에이름이지정된범위사용 XML 웹커리만들기 시험구성 데이터및내용서식 지정 사용자지정서식만들기및적용조건부서식사용그래픽서식다이어그램및차트서식 작업그룹공동작업 워크시트및통합문서, 셀보호파일암호적용공유통합문서만들기통합문서병합통합문서의변경사항추적 통합문서및데이터 관리 EXCEL 에데이터가져오기 EXCEL 에서데이터내보내기워크시트및통합문서를웹에게시서식파일만들기및편집데이터통합사용통합문서만들기 Excel 사용자지정 도구모음및메뉴사용자지정매크로만들기, 편집및실행 EXCEL 오류수정 - 1 -
❶ Office Excel 2007 화면구성및개요 ❷ ❸ ❹ ❺ ❻ ❼ ❶ Office 단추 : 새로만들기, 열기, 저장, 다른이름으로저장, 인쇄, 준비, 게시, 닫기등메뉴모음단추 ❷ 리본메뉴 : Office 2007 버전부터도입된매뉴얼방식으로비주얼화된매뉴얼방식이라누구나손쉽게사용할수있도록변경됨. 매뉴얼을찾는순서는메뉴탭선택 그룹확인 명령실행 ❸ 이름상자 : 선택한셀주소 ( 이름 ) 나범위가표시됨. 이름정의도가능 ❹ 수식입력줄 : 셀에입력된데이터나수식이표시되며, 수식을직접작성, 수정작업이가능함. ❺ 열머리글 : 열단위데이터선택이나삽입, 삭제작업이가능함. ❻ 행머리글 : 행단위데이터선택이나삽입, 삭제작업이가능함. ❼ 시트탭 : 시트선택이나삽입, 삭제작업이가능함. ( 여러개워크시트가모여하나의통합문서를구성 ) 셀포인터오른쪽아래모서리의작은사각점을채우기핸들이라하고, 이채우기핸들로드래그하여데이터를입력하는기능을자동채우기라한다. 문자및숫자데이터를복사, 숫자및날짜데이터의연속데이터입력, 수식복사, 서식만복사할수있다. - 2 -
[ 기능연습 1] 다음의작업을완성하시오. 1) 제품코드열 [B4:B15] 에 AB-001 에서 AB-012 까지 1씩증가되는값을입력하시오. 2) 생산일열 [C4:C15] 에는평일만입력하시오. 3) [F4] 셀의수식을 [F5:F15] 셀범위에복사하시오. 셀범위를다른위치로이동하거나, 복사하여작업속도를높일수있다. 셀이동및복사방법은리본메뉴, 바로가기메뉴를이용하여수행하며, 붙여넣기옵션태그의활용이중요하다. [ 기능연습 2] 다음의작업을완료하시오. 1) 영업1팀시트의 [B16] 셀을잘라내어 [B1] 셀로이동하시오. 2) 영업1팀시트의 [B3:B13] 셀범위를통합시트의 [B2:B12] 셀범위에주변서식에맞춰복사하시오. 3) 영업2팀시트의 [G4:G13] 셀범위를통합시트의 [D3:D12] 셀범위에값으로복사하시오. 워크시트의셀내용을임의로변경하지못하도록보호하거나, 일부영역만편집을허용하도록설정할수있다. [ 기능연습 3] 다음의작업을완료하시오. 1) [D5:F17] 셀범위만편집을허용하여워크시트를 Mos라는암호로보호하시오. 2) 암호를해제한다음 D열을숨기기한후암호 123을사용하여시트를보호하고잠긴셀을선택할수없도록옵션을설정하시오. 찾기및선택기능을이용하여워크시트내의특정단어나또는셀에적용되어있는셀서식을찾거나바꾸기할수있다. [ 기능연습 4] 다음의작업을완료하시오. 1) 워크시트에서기획실이라는단어를찾아기획부로모두바꾸시오. 2) B6셀에적용된서식과같은서식을모두찾아 C6셀에적용되어있는서식으로바꾸시오. 3) B5셀에적용된서식과같은서식을모두찾아 D6셀에적용되어있는서식으로바꾸시오. 피벗테이블이란특정데이터를중심으로데이터베이스를요약하고분석한결과를표시하는대화형테이블이다. 피벗테이블을작성하여많은양의자료를요약분석하여보여줌으로써사용자가전체적인내용을일목요연하게분석할수있다. [ 기능연습 5] 다음의작업을완료하시오. 1) 새워크시트에피벗테이블을만드시오. 부서는행레이블, 성별은열레이블, 성명은보고서필터, 워드는 - 3 -
값으로설정하시오. 워드값필드는평균으로하고필드이름은워드평균, 표시형식은숫자로하시오. 그리고워드와엑셀의합을구하는과목합이라는필드를피벗테이블에삽입하시오. 2) 새워크시트에묶은가로막대형피벗차트를만드시오. 성별은보고서필터, 부서는축필드, 엑셀은범례필드, 사원번호는값으로설정하시오. 차트레이아웃 7로변경하고성별이남자만표시되도록차트를필터링하시오. 또한열을 20개단위로묶고 50에서시작하여 100으로끝나도록피벗테이블을수정하시오. 차트는숫자데이터를쉽게비교및분석할수있도록함으로써차트를통해현상이나추이를편리하게파악하도록한다. [ 기능연습 6] 다음의작업을완료하시오. 1) 판매현황시트에서 [B4:E8] 셀범위의데이터를이용하여 [ 표식이있는꺾은선형 ] 차트를만들고, 차트레이아웃을 [ 레이아웃 3] 으로변경하시오. 2) 차트에 [F4:F8] 셀범위의데이터를추가한후, 영업현황시트에있는 G5:G8 영역을사용하여합계이름의데이터계열을추가하시오. 3) 영업현황시트에서 [B4:F8] 셀범위의데이터를이용하여 [ 꺽은선형 ] 차트를만들고, 차트스타일은 [ 스타일 4], [ 최고 / 최저값연결선 ] 을차트에표시하시오. 수식입력줄을이용하여덧셈 (+), 뺄셈 (-), 곱셈 (*), 나눗셈 (/) 사칙연산자와상수값또는셀값을이용하여수식을간단히작성하여결과를확인할수있다. 이때, 수식의시작은 (=) 기호로시작하여야하며, 다른셀의값을이용하여수식을구하는것을셀참조라한다. [ 기능연습 7] 다음의작업을완료하시오. 1) 매출이익시트의차월이월데이터인 [H5] 셀에전월이월수량과매입수량을더한값에매출수량을빼는수식을작성하시오. 그런다음 [H5] 셀의수식을 [H6:H13] 셀범위에복사하시오. 2) 매출이익시트의매출이익데이터인 [I5] 셀에매출금액에서매입금액을뺀값에마진율이름을곱하는수식을작성하시오. 그런다음 [I5] 셀의수식을 [I6:I13] 셀범위에복사하시오. 셀내의다양한데이터에대하여기본적인수식을사용하지않고, 복잡한계산을하는경우에함수를사용하게되는데기본적으로자주사용하는함수는 SUM, AVERAGE, MAX, MIN, COUNT 등이있다. [ 기능연습 8] 다음의작업을완료하시오. 1) [G5:G17] 셀범위에제품합계를, [D18:F18] 셀범위에제품별합계를구하시오. 2) [D19:F19] 셀범위에는제품별평균을구하시오. 3) [D20:F20] 셀범위에는제품별최대값을구하시오. 4) [D21:F21] 셀범위에는제품별최소값을구하시오. 5) [G2] 셀에는 VTR 데이터를이용하여영업소의개수를구하시오. - 4 -
날짜및시간관련함수를이용하여엑셀에서다양한날짜 / 시간을추출하여사용할수있다. 대표적인함수로 YEAR, MONTH, DAY, TIME, DATE 등이있다. [ 기능연습 1] 다음의작업을완료하시오. 1) D3을이용하여 K3:K5셀에시험응시년도, 시험응시월, 시험응시일을구하시오. 2) F3:H3셀의시, 분, 초를이용하여 K6셀에시험시간을구하시오. 3) 2011년, 04월, 01일값을이용하여 K7셀에날짜를 DATE 함수로구하시오. 논리함수는조건에따라참일때와거짓일때다른값을반환해주는함수이다. 대표적인논리함수는 IF 함수를사용하며 AND 함수와 OR 함수를이용하여다양한조건을표현할수있다. [ 기능연습 2] 다음의작업을완료하시오. 1) 반편성열 [E4:E18] 셀범위에성별이남이면 A반을, 아니면 B반을반환하도록수식을작성하시오. 2) 연장자열 [G4:G18] 셀범위에는출생년도가 1990년이전이면 5를반환하고아니면 0을반환하시오. 3) 평가열 [K4:K18] 셀범위에출석시간이 25 이상이고, 총점이 200 이상이면수료를, 아니면재수강을반환하도록수식을작성하시오. 조건에만족하는값들의합이나평균을구하는함수로는 SUMIF, SUMIFS, AVERAGEIF, AVERAGEIFS 함수가있다. 이런함수들을이용하여간단히통계값을구할수있다. [ 기능연습 3] 다음의작업을완료하시오. 1) [K3:K4] 셀범위에각거래처별수량의합계를구하시오. 2) [L3:L4] 셀범위에각거래처별수량의평균을구하시오. 3) K8셀에는수량이 20 이상이고단가가 6000 이상인총액합계를구하시오. 4) L8셀에는단가가 6000 미만이고부가세가 5000을넘는총액평균을구하시오. 참조테이블형태로작성된데이터가있다면찾기 / 참조함수를이용하여손쉽게데이터를찾아가져올수있다. 단, 찾기 / 참조함수사용시참조테이블이수직 ( 열 ) 인지수평 ( 행 ) 인지에따라함수선택을유의하여사용하여야한다. 찾기 / 참조함수는 VLOOKUP과 HLOOKUP 함수가널리사용된다. [ 기능연습 4] 다음의작업을완료하시오. 1) [C3] 셀에지점테이블 [L3:M7] 표를참조하여지점코드별지점명을찾아가져오는수식을추가하시오. 그런다음이수식을 [C4:C19] 셀범위에복사하시오. 2) [J3] 셀에고객등급테이블 [B22:F23] 을참조하여누적포인별고객등급을찾아오는수식을추가하시오. 이수식을 [J4:J19] 셀범위에복사하시오. - 5 -
수식분석그룹에는참조되는셀추적, 참조하는셀추적, 그리고수식을셀에표시하는수식표시기능등여러가지수식을분석하는데필요한기능들이일목요연하게정리되어있다. [ 기능연습 5] 다음의작업을완료하시오. 1) E14 셀에서참조되는셀추적기능을실행하고 G12 셀에서는참조하는셀추적기능을실행하시오. 2) 워크시트에입력되어있는모든수식을표시하시오. 외부엑세스데이터베이스파일이나웹파일, 텍스트파일등을엑셀로가져올수있다. [ 기능연습 6] 다음의작업을완료하시오. 1) [ 첨부파일 ] 폴더에있는고객관리.mdb 파일의고객테이블을열때데이터새로고침과 5분간격으로새로고침속성을설정하여 A1셀로가져오시오. 2) 내보내기기능을이용하여고객백업으로저장하시오. 외부데이터연결속성에 대해설정하는기능이다. [ 기능연습 7] 다음의작업을완료하시오. 1) 파일을열때데이터새로고침하고다른작업하면서새로고침되지않도록고객관리연결속성을수정하시오. 다량의데이터를효율적으로활용할수있는기능으로데이터베이스형태로데이터를관리하고분석할수있다. 또한필터기능은원하는레코드를추출할수있는데, 다양한조건을통해원하는데이터만손쉽게찾아낼수있다. [ 기능연습 8] 다음의작업을완료하시오. 1) 고객정보시트의 B27셀에지점이강남, 강북, 강동인레코드를필터링하기위한조건목록을만들고필터링한결과를 B33셀에복사하시오. 2) [B3:J20] 표에서지점이강남이고, 누적포인트가 300점이상인레코드를필터링하시오. 3) 고객관리시트에서동일한레코드를고객정보시트의 B49로필터링하시오. 단, 동일레코드는한번만출력하고조건은임의의셀에입력하여처리하시오. - 6 -
유효성검사는사용자가셀에정확한데이터를입력하도록제한해주는기능이다. 정수범위로제한된값만입력하거나, 주민등록번호와같은데이터의경우 13자만입력하도록텍스트길이를제한하거나, 목록에서값을선택하도록설정하는기능이다. 또한유효성검사규칙을안내하는설명메시지와유효성검사규칙에어긋나는데이터가입력되었을때오류메시지를표시할수있다. [ 기능연습 1] 다음의작업을완료하시오. 1) [J6:J8] 목록에서하나의값만갖도록 [E6:E22] 셀을수정하시오. 2) [D6:D22] 셀범위에남, 여항목을목록으로제한하도록만드시오. 3) [F6:H22] 셀범위에 0에서 100사이의정수값만갖도록제한하고어긋나는데이터는빨간동그라미를표시하시오. 4) 데이터표시트에서 [F5:J12] 범위에데이터표기능을이용하여값을채우시오. 행입력셀은 C5셀로지정하고열입력셀은 C4셀로지정하시오. 코드그룹에는 Microsoft Visual Basic 편집창, 매크로작성몇편집, 삭제, 매크로보안과관련된기능들이정리되어있다. 이단원에는사용자가직접함수를만들어사용하는것에대해학습합니다. [ 기능연습 2] 다음의작업을완료하시오. 1) 단가의 10% 할인을계산하는할인10 이름의새사용자정의함수를만들어 [F5:F20] 에적용시키시오. 2) 단가의 20% 할인을계산하는할인20 이름의새사용자정의함수를만들어 [G5:G20] 에적용시키시오. 3) 수량이 30보다크면단가의 30% 할인을계산하는대량할인이름의새사용자정의함수를만드시오. 대량할인이름의새사용자정의함수를 [H5:H20] 셀에적용하시오. 코드그룹에는 Microsoft Visual Basic 편집창, 매크로작성몇편집, 삭제, 매크로보안과관련된기능들이정리되어있다. 이단원에는매크로작성, 편집, 삭제, 매크로보안에대해학습합니다. [ 기능연습 3] 다음의작업을완료하시오. 1) 다음과같은기능을하는서식이라는이름의새매크로를만드시오. - 글꼴크기 :12, 글자체 : 궁서체, 정렬방식 : 왼쪽맞춤, 행높이 :20으로설정하시오. 2) 서식이라는이름의매크로를 B5:B20 범위에적용하시오. 3) 선택된셀의텍스트에주황색을적용하는주황글이라는이름의새매크로를주황글자색설명과 CTRL+d 라는바로가기키를지정해만드시오. 그런다음 D5:D20 셀에적용시키시오. 4) 서식이라는이름의매크로를삭제하시오. 5) 매크로보안수준을모든매크로제외 ( 알림표시없음 ) 으로변경하시오. - 7 -
XML이란웹상에서구조화된문서를전송가능하도록설계된마크업언어이다. 고정된요소만을사용하는 HTML과는달리자유롭게문서의요소와속성, 개체를선언하여자료를구조적으로표현할수있다. [ 기능연습 4] 다음의작업을완료하시오. 1) XML 요소를 A2셀에서부터일치하는열과매핑을하시오. 그런다음 [ 첨부파일 ] 폴더에있는판매정보.XML 파일의 XML 데이터를가져오시오. 단, XML 데이터를매핑할때프롬프트되면기존서식을사용하도록하세요. 2) 워크시트에매핑된데이터를 [ 내문서 ] 폴더에판매직원이라는이름의 XML 파일로내보내시오. 다량의데이터에대해분석하는기능으로특정필드를기준으로소계를구하거나통계수치가표시되어있는데이터목록에윤곽단추를표시하여그룹별로표시여부를설정할수있다. [ 기능연습 5] 다음의작업을완료하시오. 1) 판매현황시트에서거래처별수량, 총액의합계를구하고, 그룹마다페이지구분선을추가하시오. 다른사항은기본설정을모두수락하시오. 2) 만주문구에해당하는데이터만상세하게표시하는윤곽을설정하시오. 3) 부서별판매현황시트의영업5과부터영업8과에해당하는레코드데이터를표시하지않도록윤곽을설정하시오. Office 2007의통합문서파일형식은 xlsx이며, 이전버전파일형식으로저장하거나, 서식파일, 웹페이지, 매크로통합문서등의다양한형태의파일로저장할수있으며통합문서를열거나수정할때암호를설정하여문서를보호할수도있다. [ 기능연습 6] 다음의작업을완료하시오. 1) 통합문서를열거나수정할수없도록 MOS라는암호를설정하여저장하시오. 단, 저장시파일이름은연습으로입력하고, 저장폴더는 [ 내문서 ] 로설정하시오. 2) 통합문서를 [ 바탕화면 ] 폴더에연수결과이름의 Excel 97-2003 통합문서로저장하시오. - 8 -
MOS Excel Expert 2007 모의고사 1 회 [ 문제 1번 ] 다음의작업을완성하시오. 1) 매크로보안수준을모든매크로제외 ( 알림표시 ) 로변경하시오. 2) D열과 E열을숨기기한후암호 369를사용하여시트를보호하고잠긴셀을선택할수없도록옵션을설정하시오. [ 문제 2번 ] 다음의작업을완료하시오. 1) G3셀에과목이영어회화초급, 영어회화중급인레코드를필터링하기위한조건목록을만들고필터링한결과를 B16셀에복사하시오. 2) [B4:B13] 셀범위만편집을허용하여워크시트를 MOS라는암호로보호하시오. [ 문제 3번 ] 다음의작업을완성하시오. 1) G7 셀에서참조되는셀추적기능을실행하고 F10 셀에서는참조하는셀추적기능을실행하시오. 2) 선택된셀의텍스트에연한파랑를적용하는글자색이라는이름의새매크로를연한파랑색설명과 CTRL+g라는바로가기키를지정해만드시오. 그런다음 E4:G13 셀에적용시키시오. [ 문제 4번 ] 다음의작업을완료하시오. 1) 새워크시트에피벗테이블을만드시오. 품명은행레이블, 단가는열레이블, 제품코드는보고서필터, 판매액은값으로설정하시오. 판매액값필드는평균으로하고필드이름은판매액평균, 표시형식은통화로하시오. 또한열을 5000원단위로묶고 3,000에서시작하여 100,000으로끝나도록피벗테이블을수정하시오. [ 문제 5 번 ] 다음의작업을완성하시오. 1) D4:D8 영역을사용하여단가이름의데이터계열을추가하시오. 그런다음차트에하강선을표시하시오. 2) 거래처목록에서하나의값만갖도록 [B4:B8] 셀을수정하시오. [ 문제 6번 ] 다음의작업을완료하시오. 1) 상반기시트의평균열과하반기시트의평균열의통합된평균을통합시트의평균열에나타내시오. 2) 통합시트의 [C5:C16] 셀범위에 100에서 510.5사이의소수값만갖도록제한하고어긋나는데이터는빨간동그라미를표시하시오. [ 문제 7번 ] 다음의작업을완성하시오. 1) 새워크시트에누적세로막대형피벗차트를만드시오. 품명은보고서필터, 제품코드는축필드, 매출총액은값으로설정하시오. 값필드는평균으로변경, 차트레이아웃 6으로변경하고품명이서울구이김만표시되도록차트를필터링하시오. [ 문제 8 번 ] 다음의작업을완료하시오. 1) [C2:C11] 셀범위에상반기, 하반기값으로제한하는목록을만드시오. 2) C2 셀에적용된서식과같은서식을모두찾아 B1 셀에적용되어있는서식으로바꾸시오. [ 문제 9번 ] 다음의작업을완성하시오. 1) 다음과같은기능을하는표서식이라는이름의새매크로를만드시오. - 글꼴크기 :10 글자체 : 굴림체, 정렬방식 : 가운데맞춤, 행높이 :20으로설정하시오. 표서식매크로를상반기, 하반기목록값에적용하시오. - 9 -
MOS Excel Expert 2007 모의고사 1 회 [ 문제 10번 ] 다음의작업을완료하시오. 1) J5 셀에는부서명이영업1부이고상기목표가 50000 이상인상기실적의합을구하시오. 2) 상기실적의 5% 를계산하는보너스라는이름의새사용자정의함수를만들어 H5:H37 영역에적용하시오. [ 문제 11번 ] 다음의작업을완성하시오. 1) [ 첨부파일 ] 폴더에있는고객관리백업.mdb 파일의비디오테이블을열때데이터새로고침과 20분간격으로새로고침속성을설정하여 A1셀로가져오시오. 내보내기기능을이용하여백업으로저장하시오. 2) 다른작업하면서새로고침되지않도록고객관리백업연결속성을수정하시오. [ 문제 12 번 ] 다음의작업을완료하시오. 1) 글자크기는 10, 행높이는 18 로설정하는서식매크로를만들어 B4:D9 범위에적용하시오. 2) 서식매크로를삭제하시오. [ 문제 13번 ] 다음의작업을완성하시오. 1) 실적시트의 [E5:J15] 범위에데이터표기능을이용하여값을채우시오. 행입력셀은 B3, 열입력셀은 B4로지정하시오. 2) 매출이익시트에서 H5셀값과마진율이라고정의된이름을곱한값을 I5셀에구하시오. 그런다음 I13셀까지수식복사하시오. [ 문제 14번 ] 다음의작업을완료하시오. 1) [ 첨부파일 ] 폴더에있는실적분석.xlsx 파일의 J3:J20 범위의평균을 K3에구하시오. 2) 새워크시트에피벗테이블을만드시오. 성명은행레이블, 직위는열레이블, 부서는보고서필터, 1차고과, 2차고과는값으로설정하시오. 1차고과와 2차고과의합을구하는합계라는필드를삽입하시오. [ 문제 15 번 ] 다음의작업을완성하시오. 1) 판매현황시트에서동일한레코드를제품목록시트의 F3 셀에필터링하시오. 단, 동일레코드는한번만출력하고조건은임의의셀에입력하여처리하시오. [ 문제 16 번 ] 다음의작업을완료하시오. 1) 코드표를이용하여월급여열 [F5:F19] 에작업자코드별월급여액을찾아오는함수식을작성하시오. 2) [G5:G19] 셀에월급여와세금을곱하는수식을작성하시오. [ 문제 17번 ] 다음의작업을완성하시오. 1) XML 요소를 B2셀에서부터일치하는열과매핑을하시오. 그런다음 [ 첨부파일 ] 폴더에있는사원정보.XML 파일의 XML 데이터를가져오시오. 단, XML 데이터를매핑할때프롬프트되면기존서식을사용하도록하세요. [ 문제 18번 ] 다음의작업을완료하시오. 1) 단가가 100,000 이상인제품에대해서만가격의 3% 할인을계산하는할인액이라는새사용자정의함수를만들어 H5:H20 범위에적용하시오. 2) 생산일의월이 1월이면반품을아니면판매라는값을구하는수식을 I5셀에구한후 I20셀까지수식복사하시오. - 10 -
MOS Excel Expert 2007 모의고사 2 회 [ 문제 1 번 ] 다음의작업을완성하시오. 1) H3 셀에필기와실기의평균을구하는수식을입력하시오. H22 셀까지수식복사를하시오. 2) 평균이 60 이상이면합격아니면불합격을구하는수식을 I3 셀에입력하시오. I22 셀까지수식복사하시오. [ 문제 2번 ] 다음의작업을완료하시오. 1) I8 셀에서참조되는셀추적기능을실행하고 G10 셀에서는참조하는셀추적기능을실행하시오. 2) D열에서 G열까지를숨기기한후암호 110을사용하여시트를보호하고개체편집할수있도록옵션을설정하시오. [ 문제 3 번 ] 다음의작업을완성하시오. 1) [C5:E9] 셀범위만데이터를입력할수있도록허용하여워크시트를 MOS 라는암호로보호하시오. 2) 매크로보안수준을모든매크로제외 ( 알림표시없음 ) 로변경하시오. [ 문제 4번 ] 다음의작업을완료하시오. 1) 새워크시트에 3차원원형피벗차트를만드시오. 품명은보고서필터, 제품코드는축필드, 매출총액은값으로설정하시오. 값필드는평균으로변경, 차트레이아웃 6으로변경하고품명이태양체리시럽만표시되도록차트를필터링하시오. [ 문제 5번 ] 다음의작업을완성하시오. 1) 새워크시트에피벗테이블을만드시오. 제품코드는행레이블, 수량은열레이블, 품명은보고서필터, 매출총액은값으로설정하시오. 매출총액값필드는평균으로하고필드이름은총액평균, 표시형식은통화로하시오. 또한열을 20 단위로묶고시작은 10, 끝은 100으로피벗테이블을수정하시오. [ 문제 6번 ] 다음의작업을완료하시오. 1) 차트1차시트의차트에데이터시트에있는 F3:F18 영역을사용하여경력이름의데이터계열을추가하시오. 그런다음차트에최고 / 최저값연결선을표시하시오. 2) 차트1차시트의 B3셀에적용된서식과같은서식을모두찾아 B2셀에적용되어있는서식으로바꾸시오. [ 문제 7번 ] 다음의작업을완성하시오. 1) J4셀에부서명이영업1부, 영업2부인레코드를필터링하기위한조건목록을만들고필터링한결과를 B39 셀에복사하시오. 2) [G5:G37] 셀범위에 0에서 100사이의정수값만갖도록제한하고어긋나는데이터는빨간동그라미를표시하시오. [ 문제 8번 ] 다음의작업을완료하시오. 1) 3월시트의 I3 셀에는적용구분이식비이고내용이외식인카드의합을구하시오. 2) 데이터표시트에서 [E3:J13] 범위에데이터표기능을이용하여값을채우시오. 행입력셀은 B3, 열입력셀은 B2로지정하시오. [ 문제 9 번 ] 다음의작업을완성하시오. 1) E4 셀값과포인트율이라고정의된이름을곱한값을 F4 셀에구하시오. F18 셀까지수식복사하시오. 2) 할인율인 [H4:H18] 셀범위에참조표를이용하여실적포인트별할인율을찾아오는수식을작성하시오. - 11 -
MOS Excel Expert 2007 모의고사 2 회 [ 문제 10번 ] 다음의작업을완료하시오. 1) 실적포인트의 5% 를계산하는할인액이라는이름의새사용자정의함수를만들어 H4:H18에적용하시오. 2) 선택된셀의텍스트에기울림, 파랑을적용하는글꾸미기라는이름의새매크로를기울림파랑색설명과 CTRL+k라는바로가기키를지정해만드시오. 그런다음 B4:B18 셀에적용시키시오. [ 문제 11번 ] 다음의작업을완성하시오. 1) [ 첨부파일 ] 폴더에있는고객.mdb 파일의대여테이블을열때데이터새로고침과 10분간격으로새로고침속성을설정하여 B3셀로가져오시오. 내보내기기능을이용하여고객명단으로저장하시오. 2) 파일을열때데이터새로고침되지않도록고객연결속성을수정하시오. [ 문제 12번 ] 다음의작업을완료하시오. 1) [H4:H7] 목록에서하나의값만갖도록 [D4:D18] 셀을수정하시오. 2) 다음과같은기능을하는 MOS서식이라는이름의새매크로를만드시오. - 글자체 : 돋움체, 정렬방식 : 오른쪽맞춤, 열너비 :12로설정하시오. MOS서식매크로를 [E4:F18] 범위에적용한후 MOS서식매크로를삭제하시오. [ 문제 13번 ] 다음의작업을완성하시오. 1) 수량이 60보다큰제품에대해서만단가의 3% 할인을계산하는할인이라는새사용자정의함수를만들어 I5:I20 범위에적용하시오. 2) 새워크시트에피벗테이블을만드시오. 제품코드는행레이블, 제품명은보고서필터, 순이익은값으로설정하시오. 순이익의 5% 를초과수익으로계산하는초과수익이라는필드를피벗테이블에삽입하시오. [ 문제 14 번 ] 다음의작업을완료하시오. 1) 상반기시트의총매출과하반기시트의총매출의통합된평균을총계시트의매출평균열에나타내시오. 2) 총계시트의 F4 셀과 G4 셀에 2011 년 3 월 11 일 9 시 10 분 20 초값을이용해수식을넣으시오. [ 문제 15 번 ] 다음의작업을완성하시오. 1) 제품판매시트에서동일한레코드를중복레코드시트의 B2 셀에필터링하시오. 단, 동일레코드는한번만출력하고조건은임의의셀에입력하여처리하시오. [ 문제 16번 ] 다음의작업을완료하시오. 1) XML 요소를 A2셀에서부터일치하는열과매핑을하시오. 그런다음 [ 첨부파일 ] 폴더에있는업체정보.XML 파일의 XML 데이터를가져오시오. 단, XML 데이터를매핑할때프롬프트되면기존서식을사용하도록하세요. [ 문제 17 번 ] 다음의작업을완성하시오. 1) [ 첨부파일 ] 폴더에있는접수자.xlsx 파일의필기와실기평균을 F4 셀과 G4 셀에수식으로넣으시오. [ 문제 18 번 ] 다음의작업을완료하시오. 1) 생산일의년도가 2011 년이면신품을아니면재고라는값을구하는수식을 H5 셀에구한후 H20 셀까지수식 복사하시오. - 12 -
MOS Excel Expert 2007 실전모의고사 [ 문제 1번 ] 다음의작업을완성하시오. 1) F열과 G열의수식이숨겨지도록서식을설정하고암호 342를사용하여시트를보호하고잠긴셀을선택할수있도록옵션을설정하시오. 2) 매크로보안수준을모든매크로제외 ( 알림표시 ) 로변경하시오. [ 문제 2번 ] 다음의작업을완료하시오. 1) 차트분석워크시트에서이용자수워크시트의 C8:F8 셀범위의데이터를사용하여 2차데이터계열을추가하시오. 2) 이용자수시트의 B3셀에적용된서식과같은서식을모두찾아 B4셀에적용되어있는서식으로바꾸시오. [ 문제 3번 ] 다음의작업을완성하시오. 1) 시험워크시트에서동일한레코드를최종워크시트의 B2셀로필터링하시오. 2) 시험워크시트의 [G3:I20] 셀범위에 70에서 100사이의정수값만갖도록제한하고어긋나는데이터는빨간동그라미를표시하시오. [ 문제 4번 ] 다음의작업을완료하시오. 1) B2셀에 [ 첨부파일 ] 폴더에있는판매관리.accdb 파일의판매내역테이블을가져오시오. 파일을열때와 30 분간격으로데이터연결을새로고치도록속성을설정하시오. 상반기이름으로내보내도록저장하시오. 2) 파일을열때데이터새로고침되지않고백그라운드로새로고침되도록연결속성을수정하시오. [ 문제 5번 ] 다음의작업을완성하시오. 1) 데이터표시트의 F3:L8 셀범위에변수두개의데이터표를만드시오. 행입력셀을 C4셀로설정하고열입력셀을 C3셀로설정하시오. 2) 영업계획시트에서 2010년값과예상성장률이라고정의된이름을곱한값을 2010년값과더하는수식을 F7셀에구하시오. 수식을 F13셀까지복사하시오. [ 문제 6번 ] 다음의작업을완료하시오. 1) I5셀에서시작해서부서명이산업영업부, 전략영업부또는해외영업부인레코드를필터링하는조건목록을만들어 I10셀에필터링된값만복사하시오. 2) 사용자가부서명열만선택하고데이터를입력할수있도록암호 132를사용하여시트를보호하시오. [ 문제 7번 ] 다음의작업을완성하시오. 1) 2009년실적.xlsx의영업실적워크시트에있는 C5:D8셀값을사용하여현재통합문서의계획워크시트의 C2셀에평균을계산하시오. 2) 상품워크시트에서 D18셀의시간, 30분, 45초숫자값으로시간을만드는수식을 A19셀에만드시오. [ 문제 8번 ] 다음의작업을완료하시오. 1) 다음과같은기능을하는레이블이름의새매크로를만드시오. 바로가기키는 CTRL+e로설정하시오. - 글꼴크기 : 12포인트, - 정렬방식 : 가운데맞춤 - 행높이 : 21.00(28 픽셀 ) 레이블매크로를 [A3:F3] 범위에적용한후레이블매크로를삭제하시오. [ 문제 9번 ] 다음의작업을완성하시오. 1) 매출통합워크시트의데이터영역 (C3:G14) 에상반기및하반기워크시트에서통합한평균매출액을표시하시오. - 35 -
MOS Excel Expert 2007 실전모의고사 [ 문제 10번 ] 다음의작업을완료하시오. 1) 결과워크시트에서지역코드이름목록에서하나의값만입력받도록 D3:D22 셀범위를수정하시오. 2) 재고워크시트에서열이 500개씩그룹으로묶이고 1에서시작해서 3500 항목에서끝나도록피벗테이블을수정하시오. [ 문제 11번 ] 다음의작업을완성하시오. 1) 새워크시트에서다음과같이피벗테이블을만드시오. 월을보고서필터로, 성명을열레이블로, 제품을행레이블로, 수량을값으로설정하시오. 값필드는평균으로, 값필드의이름은평균수량으로변경하고, 소수점이하한자리의숫자서식을적용하시오. [ 문제 12번 ] 다음의작업을완료하시오. 1) XML 요소를 B4셀에서부터일치하는열과매핑을하시오. 그런다음 [ 첨부파일 ] 폴더에있는인사정보.XML 파일의 XML 데이터를가져오시오. 단, XML 데이터를매핑할때프롬프트되면기존서식을사용하도록하세요. [ 문제 13번 ] 다음의작업을완성하시오. 1) 새워크시트에 3차원원형피벗차트를다음과같이만드시오. 월및사이즈는보고서필터로, 대분류는축필드로, 판매가는값으로설정하시오. 차트에차트레이아웃 4를, 차트스타일은스타일 28을적용하고, 월이 1월이표시되도록차트를필터링하시오. [ 문제 14번 ] 다음의작업을완료하시오. 1) C2셀의직접및간접적으로참조하는모든셀을화살표로표시하시오. 2) 선택된셀의텍스트에굵게, 기울림꼴을적용하는강조서식이름의새매크로를굵게기울림설명과 CTRL+r이라는바로가기키를지정해만드시오. 그런다음 B4:B10 셀에적용시키시오. [ 문제 15번 ] 다음의작업을완성하시오. 1) 분석워크시트에서판매금액의 3% 를계산하는카드수수료라는필드를피벗테이블에삽입하시오. 2) 매출워크시트의 J3셀에날짜텍스트를표시하는레이블을삽입하시오. 셀이변경될때레이블이이동하거나크기가바뀌지않도록레이블서식을지정하시오. [ 문제 16번 ] 다음의작업을완료하시오. 1) 코드표를참조하여작업자코드에대한기술등급과월급여를반환하는수식을 E5셀과 F5셀에만드시오. E5셀과 F5셀의수식을 19행까지복사하시오. 2) 월급여의 2% 를계산하는세금이라는이름의새사용자정의함수를만들어 G5:G19에적용하시오. [ 문제 17 번 ] 다음의작업을완성하시오. 1) 수량이 100 개이상이면금액의 20% 할인을계산하는할인금액이라는새사용자정의함수를만드시오. 그리고 G2:G65 셀범위에할인금액을구하시오. 단, 기존모듈을사용하시오. [ 문제 18번 ] 다음의작업을완료하시오. 1) 판매워크시트에서대분류가원피스이고원가가 20000을넘는모든상품에대한판매가의합계를구하는수식을 I3셀에만드시오. 2) 예약워크시트에서생년월일의월과오늘날짜 J1셀의월이같으면생일을반환하고아니면공백을반환하는수식을 J4셀에만드시오. J4셀의수식을 J33셀까지복사하시오. - 36 -