[기출풀이] 컴활2급실기 2006년3회A형 기출문제 풀이-계산작업

컴활2급실기 2006년3회A형 기출문제의 계산작업입니다.

해당기출문제다운로드:

컴활2급실기_06년3회A형.zip

문제2 계산작업(40점) ‘계산작업’시트에 대하여 다음 작업을 수행하고 저장하시오.

1. [표1]에서 사원번호[A3:A7]의 5번째 자리 숫자를 보고, 직급[D3:D7]을 표시하시오. (8점)

▶ 사원번호의 5번째 자리 숫자가 0이면‘사원’, 1이면‘대리’, 2이면‘과장’
▶ IF, MID 함수 사용

풀이:

D3 셀에 셀포인터를 놓고 다음의 수식을 입력합니다. 수식입력후 점끌기탭(채우기핸들)을 이용 D7까지 수식복사합니다.

=IF(MID(A3,5,1)="0","사원",IF(MID(A3,5,1)="1","대리","과장"))

MID 함수를 이용하여 A3셀의 5번째부터 1자를 추출합니다. IF 함수를 이용하여 추출된 이값이 "0" 인지 비교합니다.

만일 "0"이면 참이므로 참일때 값 "사원"을 최종반환하고 함수를 종료합니다. 만일 거짓이라면 한번더 IF함수와 MID 함수를 이용하여 앞의 과정을 반복합니다.

사원번호 5번째 1자리 값이 "1" 이면 "대리" 로 그렇지 않으면 "0" 도 아니고 "1" 도 아니므로 "2"이겠지요. 결국 두번의 조건을 모두 만족하지 않는다면 "과장"을 반환합니다.

계산작업에는 거의 기본으로 IF함수가 출제 됩니다. 또 이 IF함수는 다른 함수와 함께 중첩되어 사용되는 경우가 대부분입니다. 이런경우 어느 작업이 먼저일어나야 하는지를 파악하여 안쪽으로 중첩하여 줍니다. 최외곽의 함수가 가장 나중에 실행되기 때문이지요.

2. [표2]에서 집행금액이 200,000 이상 300,000 미만인 집행금액의 총합을 구하여 [I3] 셀에 표시하시오. (8점)

▶ SUMIF 함수 사용

풀이:

위의 문제는 집행금액의 값을 비교하여 조건을 만족하는 값들의 합계를 구하는 문제입니다.

SUMIF 함수를 사용하라고 되어있습니다. SUMIF 함수는 범위에서 조건을 만족하는지 비교하여 만족한다면  합계범위의 합계를 구하는 함수입니다.

SUMIF함수는 하나의 조건밖에 비교할수 없습니다. 그런데 문제에서는 두가지의 조건을 묻고 있습니다. 하여 아래와 같이 SUMIF함수를 두번 사용하여 요구하는 결과를 얻을수 있습니다. 먼저 200000 이상인 합계를 구하여 여기에서 300000 이상인 합계를 빼는 것이지요. 이것은 곧 문제의 조건과 일치하는 결과를 얻습니다.

H3셀에 셀포인터를 놓고 다음의 수식을 입력합니다.

=SUMIF(H3:H9,">=200000",H3:H9)-SUMIF(H3:H9,">=300000",H3:H9)

집행금액 H3:H9의 범위에서 조건 ">=20000" 을 만족 (200000이상인) 하는지 비교하여 만족한다면 합계범위 H3:H9 , 곧 집행범위의 합계를 구합니다.

위와 같이 조건범위와 합계범위가 같을때는 합계범위를 생략하셔도 됩니다.
앞의 SUMIF함수에서 20만 이상인 값들의 합계를 구하고 뒤의 SUMIF함수에서 30만 이상의 합계구하여 빼면 20만이상에서 30만 미만인 결과만 남게되지요.

이렇게 하나의 함수를 여러번 이용하여 원하는 결과를 얻어내는 문제들도 있음을 기억하세요.

3. [표3]에서 접수번호[A13:A18]의 마지막 자리 숫자를 보고, 여행장소[D13:D18]를 표시하시오. (8점)

▶ 접수번호의 끝자리가 1이면‘태국’, 2이면‘중국’, 3이면‘일본’, 4이면‘제주도’
▶ CHOOSE와 RIGHT 함수 사용

풀이:

이번 문제는 색인값에 따라 여러값중 하나를 선택하는 CHOOSE함수와 문자열의 오른쪽에서 원하는 개수의 문자를 추출하는 RIGHT함수 를 사용합니다.

문제를 살펴보면 먼저 접수번호의 마지막 자리를 얻을 수 있어야 합니다. RIGHT함수가 이용되겠지요.

RIGHT(A13,1)  : A13 셀의 오른쪽에서 한자리를 추출합니다. 이값은 CHOOSE함수의 색인값을 사용됩니다. CHOOSE함수의 색인값은 색인 뒤의 여러값들중 몇번째값을 추출할 것인지를 선택하는 숫자입니다.

이제 수식을 완성합니다. D13 셀에 셀포인터를 놓고 다음의 수식을 입력합니다.

=CHOOSE(RIGHT(A13,1),"태국","중국","일본","제주도")

수식입력후 D18셀까지 점끌기탭(채우기핸들)을 드래그하여 수식복사합니다.

4. [표4]에서 대출 기준표[F22:J23]를 이용하여 연봉[G13:G19]에 따른 대출가능액 [H13:H19]를 표시하시오. (8점)

▶ 연봉이 5000 이상이면 대출가능액이 2000, 4000 이상 5000 미만이면 1500, 3000이상 4000 미만이면 1000, 1000 이상 3000 미만이면 200
▶ HLOOKUP 함수 사용

풀이:

찾기함수 HLOOKUP 함수를 이용하여 값을 찾는 문제입니다.
H13셀에 셀포인터를 놓습니다. 다음의 수식을입력합니다. 수식입력후 H19셀까지 수식복사합니다.

=HLOOKUP(G13,$G$22:$J$23,2)

G13의 찾는값 연봉을 기준표 G22:J23 의 2열에서 찾습니다. "찾는방법" 이 생략되어 있습니다. 이는 유사값을 찾는다는 옵션 TRUE 를 설정한 것과 같습니다.

이렇게 옵션을 생략한 이유는 '기준표'가 수치이고 또 첫행이 오름차순정렬되어있기 때문입니다. 생략하실때는 반드시 오름차순정렬되어 있어야 한다는 것을 기억하세요.

TRUE일때 찾는값이 없으면 찾는값보다 작은 가장 큰값을 찾습니다.  만일 연봉이 2000 이면 기준표에서 찾을 수 없습니다. 이때 찾는방법이 생략되어 있거나 TRUE 로 설정되어 있으면 찾는값 2000보다 작은 가장큰값인 1000을 일치하는 값으로 찾습니다. 결과는 200이 되는 것이지요.

위의 문제에서 찾는 방법을 FALSE 로 설정하여 정확하게 일치하는 값을 찾는 다면 2000 같은 경우는 기준표에서 찾을수 없기에 #N/A 에러를 발생하게 됩니다.

또 문제에서 구간을 설정해 두었기도 하여 찾는방법을  TRUE로 설정하기 위해 생략하였습니다.

'찾는범위' 를 절대참조한 이유는 H13 셀의 수식입력후 H19 셀까지 수식복사를 하기 위해서 입니다. 절대참조를 하여야 수식복사시 그 참조범위가 이동되지 않습니다.

5. [표5]에서 개인의 수학[B27:B32] 점수가 전체의 수학 평균점수 이상이고 개인의 영어[C27:C32] 점수가 전체의 영어 평균점수 이상이면‘합격’, 그렇지 않으면‘불합격’으로 평가[D27:D32]에 표시하시오. (8점)

▶ IF, AND, AVERAGE 함수 사용

풀이:

수학과 영어 점수(AND함수) 를 전체의 평균(AVERAGE함수)으로 비교(IF함수)하여 '합격', '불합격' 을 평가합니다.
D27 셀에 셀포인터를 놓고 다음의 수식을 입력합니다. 수식을 입력하신후 B32셀까지 채우기핸들을 이용하여수식을 복사합니다.

=IF(AND(B27>=AVERAGE($B$27:$B$32),C27>=AVERAGE($C$27:$C$32)),"합격","불합격")

수식은 길지만 어렵지 않습니다.

=IF(조건,"합격","불합격") 과 같습니다. 조건부분 AND(B27>=AVERAGE($B$27:$B$32),C27>=AVERAGE($C$27:$C$32)) 수식만 잘 이해 하시면 되겠습니다.

조건 부분 수식을 풀어보면 AND 함수를 사용하여 여러 조건일 경우 모두를 만족하여야 결과로 TRUE 를 반환합니다.
AND안의 조건식 B27>=AVERAGE($B$27:$B$32) 는 B27 수학의 값을 전체수학의 평균과 비교하여 그이상이면 참이 됩니다.

AVERAGE함수 안에 절대주소로 참조된 이유는 수식을 다른셀로 수식복사할때 상대참조의 경우 이 주소가 변하기 때문입니다. 평균값을 구하는 범위는 어느셀에서도 같아야 하기에 절대참조하여 고정한 것입니다. 절대참조하실 때는 상대주소상태에서 F4 번 기능키를 사용하시면 됩니다.

마늘빵.