MOS Excel 2016 기출문제 3회
Project 1
실 습 파 일 : CohoWinery.xlsx
개 요
당신은 Coho Winery 의 사무실에서 근무하고 있습니다. 제품 매출을 분석하는 데 사용할 Excel 통합 문서를 준비하고 있습니다.
※ 기출문제 1회의 Project 2와 동일
작 업 1
수식을 포함하고 있는 셀들이 수동으로 재계산되거나 통합 문서가 저장되기 전에 항상 다시 계산되도록 계산 옵션을 변경합니다.
☞ 파일 - 옵션: 수식 - 수동 선택, 통합 문서가 저장되기 전에 항상 다시 계산 체크
작 업 2
"세부 판매 정보" 워크시트에서 A3 셀에서 A21 셀까지 "1 월"로 채웁니다. 셀 서식은 변경하지 마십시오.
☞ A2 셀 복사 - 범위 선택(A3:A21) - 홈: 붙여 넣기 옵션 - 값 (V)
작 업 3
"제품" 워크시트에서 셀 E3:E22 에 있는 데이터에 유로(€) 기호를 적용하십시오. 이때 기호가 숫자 앞에 오도록 합니다. 사용자 지정 형식을 사용하지 마십시오.
☞ 범위 선택 - 홈 - 표시형식: 회계표시형식 - € 유로(€ 123) 선택
작 업 4
"제품" 워크시트에서 하나의 함수를 사용하여 셀 H4 에 현재 날짜와 시간을 추가합니다.
☞ H4 - 수식 입력
=NOW()
작 업 5
"판매된 케이스" 워크시트에서 판매된 케이스에 영역형 차트를, 유기농 비율에 보조 축을 사용하여 꺾은선형 차트를 추가합니다.
☞ A1:C13 - 삽입 - 차트: 추가 단추 - 모든 차트: 콤보 - 판매된 케이스: 영역형 - 유기농 비율: 꺽은선형, 보조축 체크
Project 2
실 습 파 일 : ContosoPharmaceuticals.xlsx
개 요
Contoso Pharmaceuticals 에서 당신의 부서는 콜레스테롤에 대한 신약을 시험하는 작업을 수행하고 있습니다.
시험 데이터를 추적하고 분석하기 위해 Excel 통합 문서를 사용합니다.
작 업 1
"LDL 결과" 워크시트에서 차트를 수정하여 그룹 B 의 각 회원에 대해서만 월별 데이터를 표시합니다.
☞ 차트에서 '그룹' 필드 클릭 - 'B' 그룹 만 체크 - 피벗 테이블에서 B 그룹 [+] 눌러 하위 회원 표시
작 업 2
"그룹당 평균값" 워크시트에서 사용자가 특정 HDL 값만 가진 사람들만 표시하는 슬라이서를 추가합니다.
☞ 피벗 테이블 선택 - 피벗 테이블 도구: 분석 - 슬라이서 삽입 - HDL 체크 - 확인
※ 슬라이서는 피벗 테이블 문제 임
작 업 3
"그룹당 평균값" 워크시트에서 피벗 차트의 총합계 행을 제거합니다.
☞ 피벗 테이블 선택 - 피벗테이블 도구 - 디자인 - 총합계 - 행 및 열의 총합계 해제
작 업 4
"원시 데이터" 워크시트의 G2 셀에 콤보 상자 양식 컨트롤을 삽입합니다. 이때 컨트롤 서식에 "의사" 워크시트의 A 열에 있는 세 개의 이름을 범위로 사용하고 G2 셀에 연결되도록 합니다.
☞ 개발 도구 - 컨트롤 삽입 - 콤보 상자(양식 컨트롤) - G2 셀에 삽입 - 컨트롤: 속성 - 입력 범위: 의사 워크시트 A1:A3 - 셀 연결: $G$2 - 확인
작 업 5
"원시 데이터" 워크시트의 HDL 값이 70 이상일 경우 녹색 원을 표시하고, 40 이상이거나 70 미만인 경우에는 노란색 원을 표시하고, 40 미만일 경우에는 빨간색 원을 표시하는 조건부 서식 규칙을 E 열에 적용합니다.
해당 서식은 E 열에 있는 기존 행이나 새 행에 적용되어야 합니다.
☞ E2 에서 Ctrl + Shift + ↓ ↓ - 홈 - 조건부 서식 - 아이콘 집합 - 삼색 신호등 - 다시 조건부 서식 - 규칙 관리 - 규칙 편집 -종류: 숫자 - 녹색: >= 70, 노랑: >= 40 - 확인
※ 처음부터 아이콘 집합의 기타 규칙으로 풀어도 됨
Project 3
실 습 파 일 : ContosoVineyard.xlsx
개 요
당신은 Contoso Vineyard and Winery 에서 근무하고 있습니다. 당신은 와인 제조자가 와인의 다양한 제조 공정을 추적하는 데 사용할 Excel 통합 문서를 만들고 있습니다.
※ 기출문제 2회의 Project 1과 동일
작 업 1
"진행 중 배치" 워크시트에서 C 열과 D 열의 값이 소숫점 세 번째 자리까지 표시되도록 서식을 지정합니다.
서식은 기존 행 및 새 행들에 적용되어야 합니다.
☞ C2:D2 - Ctrl + Shift + ↓ ↓ - 홈 - 표시 형식(추가 단추) - 소수 자리수: 3
작 업 2
"품질 관리" 워크시트에서 모든 주차의 평균 품질 등급이 5 보다 작은 경우, A3:A7 셀 범위에 50% 회색 무늬 스타일과 빨간 무늬 색 채우기를 적용합니다.
☞ A3:A7 - 홈 - 조건부 서식 - 새 규칙 - 수식을 사용하여 서식을 지정할 셀 결정 -
=Average($B3:$M3)<5 - 서식 - 채우기: 무늬 스타일, 무늬 색 지정
작 업 3
문서 폴더에 있는 HarvestLog.xlsx 파일을 사용하여 "이용 가능한 과즙" 워크시트의 A1 셀로 쿼리를 불러옵니다. "품종", "위치" 및 "액량" 열만 포함되도록 하십시오.
☞ A1 - 데이터 - 새쿼리 - 파일에서 - 통합문서에서 - HarvestLog.xlsx 선택 - 2015년도 수확 - 편집 - 날짜, 수확량 열 제거 - 닫기 및 로드 - 다음으로 로드 - 기존 워크시트 - 로드
작 업 4
"진행 중 배치" 워크시트에서 Excel 예측 기능을 사용하여, 배치 번호 1005 에 대해 "최종 알코올 비율"이 15%가 되도록 "원하는 비중"을 계산합니다.
☞ G2 - 데이터 - 가상 분석 - 목표값 찾기 - 찾는 값: 15%, 값을 바꿀 셀: E2 - 확인
작 업 5
"품질 관리" 워크시트에서 표 이름을 "표 3"에서 "주간품질"로 변경합니다.
☞ "품질 관리" 워크시트에서 표 선택 - 표 도구 - 디자인 - 표 이름: 주간품질
작 업 6
"품질 관리" 워크시트에서 배치 번호 C1005 의 품질을 12 주차까지 예측하는 선형 추세선을 추가하시오.
☞ 차트의 C1005 계열 선택 - 차트 도구: 디자인 - 차트 요소 추가 - 추세선 - 기타 추세선 옵션 - 선형, 앞으로: 12
Project 4
실 습 파 일 : HumongousInsurance.xlsx
개 요
당신은 Humongous Insurance 에서 근무하고 있습니다. 고객 견적에 사용할 통합 문서를 준비하고 있습니다.
작 업 1
"보험료 계산기" 워크시트의 B6 셀에 "기본 보험료"와 "가입자당 추가 보험료" 값을 조회한 후 "할인 전 월보험료"을 계산하는 INDEX 함수를 추가합니다. 할인 전 월 보험료는 "기본 보험료" + (참여자 수 - 1) x "가입자당 추가 보험료" 수식을 사용하여 계산합니다.
☞ B6 - 수식 입력
=INDEX(보험료!B2:C5,B3,1)+(B2-1)*INDEX(보험료!B2:C5,B3,2)
※ 콤보상자의 연결된 값은 목록의 순번: 따라서 INDEX 범위에서 Row_Num로 인식됨
작 업 2
사용자들이 워크시트를 추가, 삭제 또는 수정할 수 없도록 암호 "P@ssword"를 사용하여 통합 문서를 보호 합니다.
※ 워크시트를 추가, 삭제 또는 수정할 수 없도록 하는 것은 '통합 문서 구조 보호'이다.
☞ 파일 - 통합 문서 보호 - 통합 문서 구조 보호 - 암호 입력
※ 검토 메뉴의 '통합 문서 보호' 명령은 '통합 문서 구조 보호'이다.
작 업 3
디지털 서명된 매크로만 활성화시킵니다.
☞ 개발 도구 - 매크로 보안 - 디지털 서명된 매크로만 포함 선택
작 업 4
"보험료 계산기" 워크시트에서 B7 셀에 조사식을 추가합니다.
☞ B7 - 수식 - 조사식 창 - 조사식 추가
※ 얄린 조사식 창은 다른 문제를 풀 때 지장을 주므로 닫아 놓아도 된다.
작 업 5
"보험료 계산기" 워크시트의 B4 셀 범위를 "할인액"이라는 이름으로 정의합니다.
☞ B4 - 수식 - 정의된 이름: 이름 정의 - 할인액 - 확인
Project 5
실 습 파 일 : Proseware.xlsx
개 요
당신은 작가에게 클라우드 기반 파일 공유를 제공하는 기업인 Proseware 에서 근무하고 있습니다. 데이터 사용량을 분석하고 마케팅 프로모션을 계획할 수 있게 해주는 스프레드시트를 만드는 중입니다.
작 업 1
"가입자" 워크시트의 F 열에 AND 와 OR 함수를 사용하여 사용자가 등급에 따른 사용 한도를 초과한 경우 TRUE 를 표시합니다. 그 외의 경우에는 FALSE 를 표시합니다.
☞ F2 - 수식 입력 - 아래로 자동 채우기
=OR(AND(D2="실버",E2>실버등급),AND(D2="브론즈",E2>브론즈등급))
※ 실버등급: I3셀 절대주소 / 브론즈등급: I4셀 절대주소
작 업 2
"가입자" 워크시트의 I6 셀에 하나의 조건부 평균 함수를 사용하여 "북동부" 지역의 "골드" 등급에 대한 평균 사용량을 계산합니다.
☞ I6 - 수식 입력
=AVERAGEIFS(E2:E35,B2:B35,"북동부",D2:D35,"골드")
작 업 3
"가입자" 워크시트의 G 열에 지역이 "서부"이고 등급이 "골드"이거나 "실버"일 때 "제안 1"을 표시합니다.
그렇지 않은 경우 "제안 2"를 표시합니다.
☞ G2 - 수식 입력 - 아래로 자동 채우기
=IF(AND(B2="서부",OR(D2="골드",D2="실버")),"제안 1","제안 2")
작 업 4
"지역별" 워크시트 셀 H3 에서 GETPIVOTDATA 를 사용하여 "북동부" 지역의 "실버" 등급 가입자 수를 계산 합니다.
※ GetPivotData 함수는 피벗테이블에서 조건을 만족하는 특정 필드의 값을 반환하는 함수
☞ 먼저, 피벗 테이블에서 "북동부" 지역의 "실버" 등급 가입자 수를 펼쳐 확인 - H3 에서 '=' 입력 후 B3 셀 클릭 - 엔터
※ 물론 피벗 테이블 옵션에 GetPivotData 생성이 설정되어 있어야 한다.(확인: 피벗 테이블 도구 - 분석 - 피벗 테이블: 옵션)
작 업 5
"지역별" 워크시트에 각 지역의 "골드"와 "실버" 등급의 가입자의 수를 보여주는 묶은 세로 막대형 피벗 차트를 만듭니다.
☞ 먼저, 피벗 테이블에서 모든 지역의 [+] 단추를 눌러 "골드", "실버" 등급 가입자 수를 펼쳐 확인
- 피벗 테이블 선택 - 피벗 테이블 도구: 분석 - 피벗 차트 - 묶은 세로 막대형 - 확인
"끝"