MOS Excel expert 2016 실전모의고사 1
프로젝트 1
개요 : AdventureWorks는 신규 고객들을 유치하기 위한 방안으로 피트니스 강좌를 시작하려고 합니다. 여러분은 Excel을 사용하여 강좌 출석 데이터를 분석하려고 합니다.
작업 1) “강좌 출석” 워크시트에서 C 열에 입력된 모든 시간 값이 “h AM/PM”으로 표시되도록 서식을 지정합니다. 분 단위는 표시되어서는 안됩니다.
C열 선택 - 홈 - 표시형식 그룹 - 자세히 클릭 - 사용자 지정 범주 - 형식에 ‘h AM/PM' 입력 - 확인
작업 2) “강좌 출석” 워크시트에 있는 열 H에서 OR 함수를 사용하여 수강생 수가 모든 강의에서 평균 수강생 수 보다 크거나 청강생 수가 1보다 크면 TRUE를 표시합니다. 그 외의 경우에는 FALSE를 표시합니다.
H2선택 - ‘=OR(F2>AVERAGE($F$2:$F$31),G2>1)’ 입력 - H31셀까지 복사
작업 3) “강좌 출석” 워크시트에 있는 B 열에서 A열에 표시된 주중 날짜에 해당하는 요일이 숫자 1~7로 대체 표기되는 함수를 입력합니다. 일요일은 숫자 1로, 토요일은 숫자 7로 표기되어야 합니다.
B2셀 선택 - ‘=WEEKDAY(A2)’ 입력 - B31셀까지 복사
작업 4) “강사별 출석” 워크시트에 시간별 범위를 사용하여 데이터를 표시할 수 있는 슬라이서를 추가합니다. 시간 값은 시, 분, 초로 지정되어야 합니다.
피벗테이블 내 클릭 - 피벗 테이블 도구 - 분석 - 필터 그룹 - 슬라이서 삽입 - 슬라이서 삽입 대화상자에서 ‘시간’ 체크 - 확인 - 시간 대화상자
작업 5) “강사별 출석” 워크시트에 있는 각각의 과정에 대해 최대 수강생 수와 최대 청강생 출석 수를 보여주는 표식이 있는 꺽은선형 피벗 차트를 만듭니다.
‘강사별 출석’ 시트의 데이터 내에 클릭 - 삽입 - 차트 그룹 - 피벗 차트 - 차트 삽입 대화상자 - 표식이 있는 꺽은선형 차트 선택 - 피벗 차트 필드에서 ‘합계:수강생’ 클릭 - 값 필드 설정 - ‘최대값’ 으로 변경 - ‘합계:청강생’ 클릭 - 값 필드 설정 - ‘최대값’ 으로 변경 - 축(범주)에서 강사 제외
프로젝트 2
개요 : Graphics Design Institute는 대회 평가를 통한 장학금 및 기업 장학금을 제공하고 있습니다. 당신은 장학금 금액을 계산할 스프레드시트를 만들고 있습니다.
작업 1) “기업 후원” 워크시트의 K1 셀에 합계 열 있는 모든 회사의 아티스트별 총 후원액 평균을 구하는 수식을 추가합니다.
K1셀 선택 - ‘=AVERAGE(후원[[#요약],[아티스트1]:[아티스트5]])’
작업 2) 데이터가 변경될 때 수식들이 자동으로 계산되는 것을 방지하도록 Excel 옵션을 수정합니다. 통합 문서가 저장되기 전에 수식들을 다시 계산되어야 합니다.
파일 - 옵션 - 수식 범주 - 계산 옵션 그룹 - ‘수동’ 선택 - ‘통합 문서가 저장되기 전에 항상 다시 계산’ 체크 - 확인
작업 3) “기업 후원” 워크시트에서 셀 A2:A11 범위에 회사별 아티스트들의 ,총 후원액이 3000달러를 초과하는 회사명에 RGB “146”, “208”, “80” 채우기를 적용하는 조건부 서식 규칙을 추가합니다.
A2:A11 범위 선택 - 홈 - 스타일 그룹 - 조건부 서식 - 새규칙 - ‘수식을 사용하여 서식을 지정할 셀 결정’ - ‘=sum($B2:$F2)>3000‘ 입력 후 오른쪽 하단 ’서식‘ 클릭 - 색 - 다른색 - 사용자 지정 탭 - 각각 RGB “146”, “208”, “80” 입력 - 확인
작업 4) “수상자 발표” 워크시트에 있는 차트를 “수상 차트”라는 이름의 서식 파일로 Charts 폴더에 저장합니다.
“수상자 발표” 워크시트 - 차트 선택 - 차트위에서 마우스 오른쪽 클릭 - ‘서식 파일로 저장’ - 자동으로 열리는 폴더(Charts)에 파일 이름 ‘수상 차트’ 입력 - 저장
작업 5) “심사위원 투표” 워크시트의 데이터를 암호 “Password”를 입력하지 않으면 사용자가 변경하지 못하도록 설정합니다. 사용자가 암호입력 없이 셀, 열 및 행을 선택하고 서식을 지정할 수 있어야 합니다.
홈 - 셀 그룹- 서식 - 보호 - 시트 보호 - ‘시트 보호 해제 암호’에 “Password” 입력 - ‘워크시트에서 허용할 내용’ 목록에서 ‘잠긴 셀 선택’, ‘잠기지 않은 셀 선택’, ‘셀 서식’, ‘열 서식’, ‘행 서식’의 확인란 체크 - 확인
프로젝트 3
개요 : Northwind Traders사는 스웨덴에서 미국의 여러 소매처에 제품을 수출하고 있습니다. 당신은 선적 정보를 추적하고 분석하기 위해 Excel을 사용하고 있습니다.
작업 1) “미국 선적” 워크시트에서 스페인어(멕시코) 지역의 14 de marzo de 2012 날짜 형식을 A열에 지정합니다.
A열 선택 - 홈 - 표시형식 - 자세히 - 범주 - 날짜 - 로캘(위치)를 ‘스페인어(멕시코)’ 선택 - 형식 ‘14 de marzo de 2012’ 선택 - 확인
작업 2) 표의 열 수식과 일치하지 않는 셀에 대해 오류를 표시하는 규칙을 설정합니다.
파일 - 옵션 - 수식 - 오류 검사 규칙 - ‘한 표에서 다른 계산된 열 수식이 사용된 셀 표시 ’ 체크 확인
작업 3) “미국 선적” 워크시트에서 각 “제품”에 대한 “가격”은 묶은 세로막대형으로 표시하고 “마진율”은 꺽은 선형으로 보조축으로 설정합니다. 가로축 레이블은 “제품”으로 설정합니다. (단, 범례 항목(계열)은 가격, 마진율만 포함하도록 설정합니다.)
제품, 가격, 마진율 열 선택 - 삽입 - 차트 자세히 클릭 - 모든차트 탭 - 콤보 선택 - 가격 계열에서 차트 종류를 세로막대형 선택 - 마진율 계열에서 차트 종류를 꺾은선형 선택, 보조축 체크 - 확인
작업 4) “도시 및 제품별” 워크시트에서 먼저 도시별로 데이터를 그룹화 한 다음 제품 유형별 제품이 선적된 월별로 그룹화합니다.
피벗테이블 - 각 ‘제품’의 + 기호 클릭하여 날짜 보이기 설정 후, 임의 날짜 선택하여 마우스 오른쪽 클릭 - ‘그룹’ 선택 - ‘월‘ 만 선택 - 확인
작업 5) “도시 및 제품” 워크시트에 있는 데이터의 레이아웃을 테이블 형식으로 표시하고 각각의 제품 다음에 빈 줄을 삽입합니다.
피벗테이블 선택 후 - 피벗 테이블 도구 - 디자인 - 레이아웃 그룹 - 보고서 레이아웃 - 테이블 형식으로 표시 - 빈 행 - 각 항목 다음에 빈 줄 삽입
프로젝트 4
개요 : 당신은 Relecloud의 영업 부서에서 근무하고 있습니다. 관리자외의 회의를 앞두고 연말 매출 내역 요약 작업을 하고 있습니다.
작업 1) “내제목”이라는 스타일의 서식에 빨간색 아래쪽 이중 테두리를 추가합니다.
홈 - 스타일 그룹 - 셀 스타일 - 맨 위 ‘내제목’ 스타일 위에서 마우스 오른쪽 클릭 - 수정 - 스타일 대화상자 - 서식 클릭 - 셀 서식 대화상자 - 테두리 탭 - 선 스타일은 ‘이중’ 선 - 색 은 ‘빨간색‘ 선택 - ’아래쪽 테두리’ 클릭
작업 2) “연간 매출” 워크시트에 있는 셀 L3에 조건부 합계 함수를 사용하여 보너스를 받은 미국 직원들에 대해 총 매출 금액을 계산합니다.
L3셀 선택 - ‘=SUMIFS(D3:D52,C3:C52,"미국*",F3:F52,"예")’ 입력
작업 3) “연간 매출” 워크시트의 H열에 VLOOKUP 함수를 사용하여 “커미션 요율” 테이블의 커미션 요율로부터 각 직원이 초과된 할당량에 대해 받게 될 커미션 요율을 계산합니다. 참고 표에 있는 값은 변경하지 마십시오.
H3셀 선택 - ‘=VLOOKUP('연간 매출'!G3,커미션!$A$3:$B$10,2,TRUE)’ - H52까지 복사
작업 4) “연간 매출” 워크시트에서 셀 범위 C3:C52 범위를 “지역”이라는 이름으로 정의합니다.
C3:C52 범위 선택 - 이름상자 - ‘지역’ 입력 - 엔터
작업 5) “매출요약”이라는 이름으로 지정된 범위를 K3:L6만 포함하도록 수정합니다.
수식 - 정의된 이름 그룹 - ‘이름 관리자’ 선택 - ‘매출요약’ 선택 - 편집 - 참조 대상 범위를 K3:L6으로 재설정
프로젝트 5 <- 실전1, 프로젝트 5 와 같은 문제임
개요 : 당신은 영업 관리자가 사용할 Northwind 판매 관련 Excel 통합 문서를 작성하는 중입니다.
작업 1) “베스트셀러” 워크시트의 F5 셀에 2014년에 최다 판매된 전기차 모델을 조회하기 위해 큐브 함수와 데이터 모델을 사용하는 수식을 추가합니다.
=CUBERANKEDMEMBER("ThisWorkbookDataModel",CUBESET("ThisWorkbookDataModel","([범위].[연도].[2014]*[범위].[모델].[All].Children)",,2,"[Measures].[합계: 연간 매출]"),1)
작업 2) “결제 대금 계산기” 워크시트의 셀 E7에 결제 기한이 매월 초라고 하고, 월별 결제 금액을 계산하는 수식을 추가합니다. 원금에서 “계약금”을 뺍니다.
E7셀 선택 - ‘=PMT(E4/12,E6*12,B6-E5, ,1)’
작업 3) “재고” 워크시트에서 재고 자동차 수가 “지난 달 판매” 대수의 두 배 이상이거나, 해당 모델의 “연간 매출”을 12로 나눈 값보다 큰 경우 “예”를 표시하는 수식을 H 열에 추가합니다. 그렇지 않으면 “아니오”를 표시합니다.
H2셀 선택 - ‘=IF(OR(D2>=2*E2,D2>G2/12),"예","아니오")’ - H40셀까지 자동 채우기
작업 4) “재고” 워크시트에서 “지난 달 판매” 숫자가 “재고 보유”의 90%를 초과하는 경우, 해당 텍스트에 굵게 서식을 지정하고, RGB 색상 “0”, “176”, “80”을 지정하여 데이터 행의 모든 텍스트에 적용합니다.
A2:H40 드래그 선택 - 홈 - 스타일 그룹 - 조건부서식 - 새규칙 - ‘수식을 사용하여 서식을 지정할 셀 결정’ 선택 - ‘다음 수식이 참인 값의 서식 지정’에 ‘=$E2>$D2*0.9’ 입력 - 서식 선택 - 글꼴 스타일 ‘굵게’, 색에서 ‘다른색‘ - ‘사용자 지정’ 탭 선택 - RGB 각각 입력
작업 5) “결제 대금 계산기” 워크시트의 E6 셀에 사용자가 1보다 작은 값 또는 5보다 큰 값 또는 수수점 자리가 포함된 값을 입력하는 경우 “유효하지 않음”이라는 제목의 중지 스타일을 사용하여 “1에서 5”라는 오류 메시지를 표시하는 데이터 유효성 검사를 실행합니다.
E6 셀 선택 - 데이터 - 데이터 도구 그룹 - 데이터 유효성 검사 - 데이터 유효성 대화상자 - 설정 탭 - 제한 대상 ‘정수’ - 제한 방법 ‘해당 범위’ - 최소값 ‘1’ , 최대값 ‘5’ - 오류 메시지 탭 - 스타일 ‘중지’, 제목에 ‘유효하지 않음’ 입력 - 오류 메시지에 ‘1에서 5’ 입력
작업 6) “매출 분석” 워크시트에서 모델들이 각 연도 안에 그룹화되도록 차트를 수정합니다.
피벗 차트 선택 - 피벗 차트 필드에서 축(범주)에서 ‘연도’를 ‘모델’ 위로 이동
"끝"