똥이 로그

문제 3 분석작업 (20점) 주어진 시트에서 다음 작업을 수행하고 저장하시오.

1. '분석작업-1' 시트에 대하여 다음의 지시사항을 처리하시오. (10점)

[부분합] 기능을 이용하여 '상공카드 이용금액 명세서' 표에 <그림>과 같이 카드종류별로 '이용금액'과 '적립포인트'의 합계와 최대값을 계산하시오.

▶ 정렬은 '카드종류'를 기준으로 오름차순으로 처리하시오.

▶ 합계와 최대값은 위에 명시된 순서대로 처리하시오.

- 부분합을 하기전에 정렬을 먼저 해주세요.

- 표에 있는 항목중 랜덤으로 클릭해주고 정렬을 '카드종류'를 기준으로 오름차순 정렬해주세요.

- 그리고 부분합에서 그룹화할 항목을 카드종류로 바꿔줍니다.

- 완료 항목을 보시면 요약이 밑에 부분합되어 있으니 먼저 사용할 함수를 합계를 사용하여 부분합을 해줍니다.

- 그리고 그 후 최대값도 부분합 해줍니다.

- 이때 새로운 값으로 대치 항목 체크를 없애줍니다.

※ 부분합에서 조심할 점

1. 정렬 먼저 해주기

2. 어떤 것이 먼저 부분합되어 있는지 확인하기

3. 그룹화할 항목 = 정렬 기준

4. 두번째 부분합부터는 새로운 값으로 대치 항목 체크 풀어주기

2. '분석작업-2' 시트에 대하여 다음의 지시사항을 처리하시오. (10점)

[목표값 찾기] 기능을 이용하여 '3월 급여 명세서' 표에서 실지급액[B15]이 4,000,000이 되려면 수당 [B6]이 얼마가 되어야 하는지 계산하시오.

- 가상분석-목표값찾기를 눌러줍니다.

- 수식 셀에 목표값 항목인 B15를 지정해주시고 찾는 값에 4000000을 입력해주세요.

- 값을 바꿀 셀에는 목표값 달성을 위해서 바뀌어야할 항목인 B6을 지정해주고 확인을 눌러줍니다.

공감과 댓글은 큰 힘이 됩니다. :)

1. [표1]에서 판매량 [C3:C10]과 상여금표 [B13:D14]를 이용하여 상여금 [D3:D10]을 계산하시오. (8점)

 ▶ 상여금표의 의미 : 판매량의 순위가 1~2위면 1,500,000, 3~4위면 1,000,000, 5위 이하는 500,000임

 ▶ HLOOKUP과 RANK 함수 사용

- D3에 "=HLOOKUP(RANK(C3,$C$3:$C$10),$B$13:$D$14,2)" 을 입력하여 줍니다.

- 그리고 결과값을 드래그하여 D10까지 채워줍니다.

2. [표2]에서 1~4회차 [G4:J13]까지 출석 ("o")이 3회 이상이면 "이수", 그렇지 않으면 공백으로 이수여부[K4:K13]에 표시하시오. (8점)

 ▶ IF와 COUNTBLANK 함수 사용

- K4에 "=IF(COUNTBLANK(G4:J4)<=1,"이수","")"를 입력해주세요.

- 그리고 나온 값을 드래그해 값을 채워주세요.

3. [표3]에서 지원부서 [B18:B25]가 "홍보부"인 지원자들의 총점 [F18:F25] 중 최대값과 최소값을 [F26] 셀에 [표시 예]와 같이 표시하시오. (8점)

 ▶ 표시 예 : 100(최소 88)

 ▶ 조건은 [H25:H26] 영역에 입력하시오.

 ▶ DMAX와 DMIN 함수와 & 연산자 사용

 ▶ 함수는 DMAX, DMIN 순서로 적용하시오.

- 먼저 조건을 채워주세요 (조건 : 지원부서가 홍보부)

- 값을 구할 곳에 "=DMAX(A17:F25,6,H25:H26)&"(최소 "&DMIN(A17:F25,6,H25:H26)&")""을 입력하여 주세요.

4. [표4]에서 성별 [B30:B37]이 "여"이면서 지역 [C30:C37]이 "인천"인 사원들의 판매금액 [D30:D37] 합계를 [D38] 셀에 계산하시오. (8점)

 ▶ COUNTIFS, SUMIFS, AVERAGEIFS 함수 중 알맞은 함수 사용

- D38에 "=SUMIFS(D30:D37,B30:B37,"여",C30:C37,"인천")"을 입력하여 주세요.

5. [표5]에서 상식[H30:H38]과 영어[I30:I38]의 합이 140 이상이고 컴퓨터[J30:J38]가 80 이상이면 "합격", 이외에는 공백을 결과[K30:K38]에 표시하시오. (8점)

 ▶ IF, SUM, AND 함수 사용

- K30에 "=IF(AND(SUM(H30:I30)>=140,J30>=80),"합격","")"을 입력하여 주세요.

- 값을 아래로 드래그해 표를 채주세요.

공감과 댓글은 큰 힘이 됩니다 :)

2. '기본작업-2' 시트에 대하여 다음의 지시사항을 처리하시오. (각 2점)

 ① [A1:H1] 영역은 '병합하고 가운데 맞춤', 크기 15, 글꼴 스타일 '굵게', 글꼴 색 '표준 색 - 파랑'으로 지정하시오.

- A1부터 H1영역을 드래그 해준뒤 지시사항대로 처리하여줍니다.

② 제목 문자열의 앞뒤에 특수문자 "◈"을 삽입하시오.

- 제목의 양 옆에 'ㅁ-한자'를 눌러 ◈ 다음 기호를 찾아 넣어주세요

③ [A4:A8], [A9:A13], [A14:A18] 영역은 '병합하고 가운데 맞춤', [A3:H3], [A4:A18] 영역은 셀 스타일을 '강조색1'로 지정하시오.

- [A4:A8], [A9:A13], [A14:A18] 영역을 Ctrl을 눌러 드래그 해주세요.

- [A3:H3], [A4:A18] 영역을 Ctrl을 눌러 드래그한 후 셀 스타일을 '강조색1'로 지정해주세요.

④ [H7] 셀에 "최고 수령예정액"이라는 메모를 삽입한 후 '자동 크기'로 지정하고, 항상 표시되도록 하시오.

- [H7] 셀에 "최고 수령예정액"이라는 메모를 삽입한 후 메모서식-맞춤-자동 크기를 클릭하고, 메모 표시가 항상 표시되도록 해줍니다.

⑤ [A3:H18] 영역은 '모든 테두리(田)'를 적용한 후 '굵은 상자 테두리(田)'를 적용하여 표시하오.

- 영역을 드래그하고 테두리를 지정해주세요.

3. '기본작업-3' 시트에 대하여 다음의 지시사항을 처리하시오. (5점)

 [B4:B18] 영역에서 '부서명'에 "영업"이 포함되어 있는 셀에는 채우기 색 '표준 색 - 노랑'을, [G4:G18] 영역에서 상위 2위 이내인 셀에는 글꼴 스타일 '굵게', 글꼴 색 '표준 색 - 파랑'을 지정하는 조건부 서식을 작성하시오.

 ▶ 단, 규칙 유형은 '셀 강조 규칙'과 '상위/하위 규칙'을 사용하시오.

- [B4:B18] 영역을 드래그해준 뒤 조건부 서식-셀 강조 규칙-텍스트 포함 에 "영업"을 입력해 줍니다.

- 적용할 서식은 채우기 색 표준 색 - 노랑을 선택하여 줍니다.

- [G4:G18] 영역을 드래그해준 뒤 조건부 서식-상위/하위 규칙-상위 10개 항목 에 "2"를 입력해 줍니다.

- 적용할 서식은 글꼴 스타일 '굵게', 글꼴 색 '표준 색 - 파랑'을 선택하여 줍니다.

공감과 댓글은 큰 힘이 됩니다 :)