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

컴활2급실기 2006년 4회 기출문제 입니다. 계산작업은 주로 함수들로 구성됩니다. 이전 게시물 함수부분을 함께 살펴주세요. 함수 목록입니다.

해당기출문제다운로드:

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

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

1. [표1]에서 [B3:B6] 영역을 참조하여 출발지(서울)에서 도착지(수원)까지의 택배요금을 계산하여 [E8] 셀에 표시하시오. (8점)

▶ INDEX 함수와 MID 함수 사용
▶ 출발지와 도착지의 구분은 ( )안의 두 자리 숫자를 이용한다.

풀이:

테이블에서 INDEX함수를 이용 원하는 값을 찾는 문제입니다. 문제를 머리속에 그리실때는 항상 주가 되는 함수를 먼저 생각하셔야 합니다. 위의 문제에서는 택배요금을 표에서 단순히 찾아내는 문제입니다.

단, 위의 두 함수 INDEX함수 와 MID함수 를 사용하셔야 합니다.

INDEX 함수는 범위나 배열 영역에서 원하는 값을 행과 열 번호를 이용 추출하는 함수입니다.

MID 함수는 문자열의 원하는 위치를 기준으로 원하는 수의 문자를 추출하는 함수입니다.
E8 셀에 셀포인터를 놓고 다음의 수식을 입력합니다.

=INDEX(B3:E6,MID(C8,5,1),MID(D8,5,1))

수식을 풀어보면 B3:E6의 범위영역을 범위로 설정합니다.
행은  MID(C8,5,1) 로 출발코드에서 5번째위치부터 1자 를 추출하여 행번호로 사용합니다. "서울(01)" 에서 5번째는 '1' 이고 1글자를 추출하기에 1이 반환됩니다. MID함수로 반환된 1은 INDEX 함수의 인수로 사용되어 1행을 의미하게 됩니다. 범위에서 일행이기에 1번 행이 선택되지요.

또 MID(D8,5,1) 은 도착코드에서 5번째부터 1자 를 추출하여 INDEX함수의 열번호로 사용합니다. 3이 열번호가 되지요.
곧 위의 수식은 다음과 같습니다.

=INDEX(B3:E6,1,3)

범위 B3:E6 에서 1행 3열의 값을 얻겠다는 말입니다. D3 의 값 , 15000 이 결과로 반환됩니다.

이렇게 간단한 수식을 구지 위와 같이 복잡하게 적은 이유는 문제에서 두함수(INDEX, MID)를 사용하라는 말이 있기때문입니다. 문제의 요구사항에 맞춰 결과를 얻어야 정답으로 간주되다는 점 명심하세요.

2. [표2]에서 ‘실적’이 전체 영업팀의 평균 실적 이상이면 ‘실적우수’, 그렇지 않으면 ‘부진’으로 평가[J3:J9] 영역에 표시하시오. (8점)

▶ IF 함수와 AVERAGE 함수 사용

풀이:

평가를 구합니다. 실적의 평균이상 (평균을 구하기 위해 AVERAGE함수가 사용됩니다) 인지 비교하여(IF함수가 사용됩니다) 참이면 '실적우수', 거짓이면 '부진' 으로 표시합니다.

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

=IF(I3>=AVERAGE($I$3:$I$9),"실적우수","부진")

I3의 값이 평균이상인지 비교합니다. 이를 위해 평균을 구하는 함수로 전체실적의 평균을 구하여 조건비교값으로 사용합니다.
이때 주의 하셔야 할 것이 있습니다. AVERAGE함수, 평균을 구하는 함수의 범위,I3:I9의 범위를 절대참조 해주셔야 한다는 것입니다.

이유는 J3의 평가 하나만을 구하는 것이 아니기 때문입니다. 나머지 J4:J9의 범위에도 수식복사를 하셔야 합니다. 수식복사를 하게되면 복사셀에서의 상대적인 이동 위치만큼 수식에서 참조하던 셀의 주소도 변경됩니다.

J3 를 J4 로 수식복사하면 I3 는 I4 로 I3:I9는 I4:I10으로 변경됩니다. I3가 I4로는 변경되어야겠지만 실적전체범위가 변경되어서는 안됩니다. 하여 실적전체 범위를 절대참조하여 고정하는 것입니다.

이제 IF함수에서 조건비교후 만족하면 "실적우수", 만족하지 않으면 "부진"을 반환하는 것입니다.
이렇게 한셀의 수식이 완성되면 나머지셀들로 점끌기탭(채우기핸들)을 이용 수식복사합니다.

3. [표3]에서 회원거주지[C14:C22]가 ‘서울’인 회원의 수를 구하여 [D24] 셀에 표시하시오. (8점)

▶ COUNT, DCOUNT, DCOUNTA 함수 중 적절한 함수를 선택하여 사용
▶ 표기 예 : 5명

평가:

조건을 만족하는 회원의 수를 구합니다.

COUNT 는 단순히 수치값의 수를 구하는 함수 입니다. 조건은 사용할수 없습니다.
DCOUNT 는 조건을 사용할수 는 있지만 수치값만의 수를 계산합니다. 하여 문자열의 수를 세는 지금의 문제에는 적합하지않습니다.

조건을 사용할수 있고 문자열값을 셀수 있는 함수는 DCOUNTA 데이터베이스함수 입니다.
D24 셀에 셀포인터를 놓습니다. 다음의 수식을 입력합니다.

=DCOUNTA(A13:C22,3,C13:C14) & "명"

범위영역 A13:C22 에서 C13:C14 의 조건을 만족하는 값들의 3번째 열의 수를 셉니다.
범위영역과 조건범위가 겹치는 부분이 있지만 상관은 없습니다.

함수 수식 뒤에는  '&' 연산자를 이용 문자열을 연결(추가) 하여 표시합니다. 수식의 계산결과가 문자열과 연결되어 표시됩니다.

4. [표4]에서 회원번호[G14:G21]의 뒤의 1자리가 ‘F’ 또는 ‘f’인 경우 ‘무료’, ‘S’ 또는 ‘s’인 경우 ‘할인’, 나머지는 ‘유료’로 가입구분[H14:H21] 영역에 표시하시오. (8점)

▶ IF, RIGHT 함수 사용

풀이:

이 문제는 문자열에서 문자를 추출 비교하여 조건에 맞는 값을 반환하는 문제입니다.

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

=IF(RIGHT(G14,1)="F","무료",IF(RIGHT(G14,1)="S","할인","유료"))

수식을 입력후 H21 셀까지 점끌기탭(채우기핸들)을 이용하여 수식복사합니다.
위의 수식을 분석해 봅니다. 수식은 복잡해 보이지만 단순한 수식입니다.

=IF(조건, 참값, 거짓값) 이것이 IF함수의 용법입니다. 위의 수식은 여기서 거짓값 대신 다시한번더 IF함수를 사용한 것 밖에 없습니다.

자, 먼저 문제에서 회원번호의 마지막자리을 추출(RIGHT함수가 필요합니다) 비교하여 ( IF함수) 요구하는 값으로 반환합니다.
위의 구문을 차례로 수식으로 적용해 갑니다. 마지막의 작업이 가장먼저 나옵니다.

G14셀의 문자열을 오른쪽 1자리추출하여 "F" 인지 비교하고 참이라면 "무료"를 반환하고 함수를 빠져나옵니다.
거짓이면 안쪽의 IF함수를 실행, 추출문자열을 "S" 인지 비교, 참이면 "할인",거짓이면 "유료"를 셀에 반환합니다.

문제에서는 대소문자가 따로 사용되었는데 왜 수식에서는 대문자 하나만인지 궁금하실 것입니다. 이는 엑셀의 계산수식에서는 대소문자를 구분하지 않습니다. 하여 대문자 "F" 로 비교하였지만 'F' 와 'f' 모두 참의 결과를 얻습니다.

5. [표5]에서 차량번호[G27:G31]를 이용하여 차량 5부제를 실시하려 한다. 차량번호의 끝자리가 1과 6인 경우 ‘월’, 2와 7인 경우 ‘화’, 3과 8인 경우 ‘수’, 4와 9인 경우 ‘목’, 5와 0인 경우 ‘금’으로 쉬는날[I27:I31] 영역에 표시하시오. (8점)

▶ IF, MOD, RIGHT 함수 사용

풀이:

이번 문제는 살짝 복잡한 문제 입니다. 역시 위의 문제와 같이 문자열의 마지막 자리를 추출 비교합니다. 단  끝자리 하나로 2개의값 과 비교합니다. 예로 1과 6인 경우 입니다. 이는 차량 5부제를 감안하세요. 1과6, 2와7 ,3과8 등은 5의차가 납니다.

문제에서는 고맙게도 MOD 함수를 사용하라고 가르쳐 주네요.
먼저 첫번째 차량번호를 문제에 적용해 보겠습니다.

첫번째 차량번호 70조2855 에서 RIGHT함수를 이용 마지막 자리를 추출합니다. 5가 결과로 반환되지요. 이값을 MOD함수를 사용하여 5로 나눈 나머지를 구합니다. 5를 5로 나눈나머지는 0 입니다. 문제에서 0인 경우는 금요일 이지요.

이렇게 MOD 함수로 값 1과 6을 5부제의 5로 나눈 나머지는 동일하게 1 입니다.
2와7을 5로 나눈 나머지도 2로 동일합니다.

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

=IF(MOD(RIGHT(G27,1),5)=1,"월",IF(MOD(RIGHT(G27,1),5)=2,"화",IF(MOD(RIGHT(G27,1),5)=3,
"수",IF(MOD(RIGHT(G27,1),5)=4,"목","금"))))

상당히 복잡해 보입니다. 하지만 하나씩 분석해 보시면 4번의 IF 함수를좀더 중첩한것 밖에는 없습니다.

천천히 살펴보면 G27 셀, 차량번호의 오른쪽에서 1자리를 얻어 5로 나눈 나머지가 1인지 살펴봅니다. 이는 문제에서 1 과 6 인지를 동시에 살피는 것과 같습니다. 조건을 만족하면 "월"의 결과를 얻고 함수를 빠져 나갑니다. 거짓이라면 다시 중첩된 IF 함수를 실행하는 것이지요. 여기서 2와 7인지 비교합니다.

이런형태로 문제의 모든 조건을 비교하는 것입니다.
문제의 핵심은 1과 6 , 2와 7,  3 과 8  을 한번에 알아낼수 있는 MOD 함수의 활용입니다.

위의 규칙성을 살피면 두수들이 5의차를 두고 있다는 것입니다. 이런두수는 MOD 함수를 이용 값을 두수의차 5로 나눈 나머지를 비교하는 것입니다. 1과 6의 나머지 1, 2와 7의 나머지 2, 3과 8의 나머지 3 등인 것이지요. 많이 복잡한가요.

문제에서 사용하라는 함수는 반드시 이유가 있습니다. 이를 문제에 잘 적용하시면 보다 쉽게 문제를 해결하실 수 있을 것입니다.

마늘빵.