스프레드시트 최신기출문제 1회 오답노트

[계산작업 시트]

[문제 1] [표1]의 권장소비자가격, 제조일자, 보존기간(개월)과 [표2]를 이용하여 [H3:H26] 영역에 할인판매금액을 계산하여 표시하시오

▶ 할인판매금액 = 권장소비자가격 * (1-할인율)

▶ 할인율은 (유통기한 - 기준일)/30 을 기준으로 표2에서 찾아 계산 

▶ 유통기한은 제조일자에서 보존기간(개월)이 지난 날로 계산

▶ VLOOKUP, EDATE, QUOTIENT 함수 사용

1. 유통기한을 먼저 구한다.     =EDATE(제조일자-보존기간)       =EDATE(F3,G3)

2. 남은기간을 구한다.             =(유통기한-기준일)/30               =(EDATE(F3,G3)-J1)/30

3. 남은기간을 정수로 구한다.  =QUOTIENT(할인율,1)                 =QUOTIENT((EDATE(F3,G3)-J1)/30,1)

4. 표2에서 할인율을 구한다.   =VLOOKUP(값,범위,열번호,유형)  =VLOOKUP(QUOTIENT((EDATE(F3,G3)-J1)/30,1),L3:M6,2,TRUE)

5. 할인판매금액을 구한다.     =권장소비자가격 * (1-할인율)       =E3*(1-VLOOKUP(QUOTIENT((EDATE(F3,G3)-J1)/30,1),L3:M6,2,TRUE))

6. 절대/상대/혼합참조로 수정 후 채우기 핸들을 이용하여 [표1]을 채워준다.

  =E3*(1-VLOOKUP(QUOTIENT((EDATE($F3,$G3)-$J$1)/30,1),$L$3:$M$6,2,TRUE))

 

[문제 3] [표1]을 이용하여 [H30:H34] 영역에 구분별 최다 판매 제품명과 판매수량을 표시하시오

▶ MATCH, INDEX, MAX 함수를 사용한 배열 수식으로 작성

1. 건강보조제의 최다 판매 제품을 구분한다.  =MAX((C3:C26=G30)*(I3:I26))

2. 최다판매제품의 위치를 구한다.                 =MATCH(MAX((C3:C26=G30)*(I3:I26)),(C3:C26=G30)*(I3:I26),0)

3. 최다판매제품의 제품명을 구한다.             =INDEX(A3:I26,MATCH(MAX((C3:C26=G30)*(I3:I26)),(C3:C26=G30)*(I3:I26),0),2)

4. 제품명과 판매수량을 절대/상대/혼합참조로 수정 후 채우기 핸들을 이용하여 [표4]를 채워준다.

  제품명  ->  =INDEX($A$3:$I$26,MATCH(MAX(($I$3:$I$26)*($C$3:$C$26=$G30)),($I$3:$I$26)*($C$3:$C$26=$G30),0),2)

  판매수량  ->  =INDEX($A$3:$I$26,MATCH(MAX(($I$3:$I$26)*($C$3:$C$26=$G30)),($I$3:$I$26)*($C$3:$C$26=$G30),0),9)

[Ctrl] + [Shift] + [Enter] 잊지 말기!!!

 

[문제 4] [표1]의 제품코드를 이용하여 [표5]의 [M30:M33] 영역에 전체에 대한 섭취횟수별 비율을 계산하여 표시하시오

▶ 제품코드의 3~4번째 글자가 섭취횟수를 표시함

▶ MID, SUM, COUNTA 함수를 사용한 배열 수식으로 작성

1. 섭취횟수를 구한다.                                                                           =MID(A3:A26,3,2)

2. 섭취횟수별 개수를 구한다.                                                                 =SUM((MID(A3:A26,3,2)*1=K30)*1)

3. 전체에 대한 섭취횟수별 비율을 계산한다.                                             =SUM((MID(A3:A26,3,2)*1=K30)*1)/COUNTA(A3:A26)

4. 절대/상대/혼합참조로 수정 후 채우기 핸들을 이용하여 [표5]를 채워준다.  =SUM((MID($A$3:$A$26,3,2)*1=K30)*1)/COUNTA($A$3:$A$26)