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

컴활2급실기 2007년3회A형 기출문제 풀이 입니다.
계산작업입니다. 계산작업은 함수위주의 작업입니다. 함께 살펴봅니다.

해당기출문제다운로드:

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

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

1. [표1]에서 주민등록번호[B3:B10]를 보고 성별[C3:C10]을 표시하시오. (8점)

▶ 주민등록번호의 8번째 자리 숫자가 1, 3이면 ‘남’, 2, 4이면 ‘여’
▶ CHOOSE, MID 함수 사용

풀이:

주민등록번호로 '남' ,'여' 를 구분하는 문제입니다. 주민등록번호에서 남여를 구분하는 자리인 8번째자리를 추출하는데 MID 함수를 사용하고 이값을 비교하여 '남','여' 의 값을 반환하는데 CHOOSE 함수를 사용합니다.

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

=CHOOSE(MID(B3,8,1),"남","여","남","여")

수식을 입력후 C10 셀까지 점끌기탭(채우기핸들)을 이용하여 끌어 수식을 복사합니다.

수식을 분석하면 먼저 주민등록번호에서 성별을 구분하는 값인 8번째자리를 추출할수 있어야 합니다. 이를 위해 MID함수를 사용합니다. MID 함수는 문자열에서 원하는 자리에서 1자리를 추출하는 함수입니다. 위의 수식에서는 B3 ,곧 주민등록번호에서 8번째 자리부터 1자리를 추출합니다.

이후 CHOOSE함수를사용하여 추출된 값을 CHOOSE 함수의 색인값(찾을순번) 으로 줍니다. 때문에 1번째 자리는 '남' , 2번째 자리는 '여',3번째 자리는 '남', 4번째 자리에는 '여'를 설정하였습니다.)

CHOOSE함수는 첫번째 인수로 다음의 여러인수들의 순번을 정하여 선택하는 함수이기 때문입니다.

2. [표2]에서 응시구분[F3:F10]이 ‘P-1’인 학생들의 1차[G3:G10] 점수의 평균을 구하고 값 뒤에 ‘점’이 표시되도록 P-1의 1차 평균[H12] 영역에 표시하시오. (8점)

▶ 평균은 소수점 이하 첫째 자리에서 반올림 할 것 (표기 예 : 80.7 => 81점)
▶ DAVERAGE, ROUND 함수와 & 연산자 사용

풀이:

작게 살펴보면 두개의 작업입니다. 먼저 문제의 평균을 구하고 구해진 평균을 반올림하는 작업입니다.

먼저 평균을 구해야 겠지요. 다음에 반올림작업이 행해집니다. 나중에 행해질 작업의 함수가 가장먼저 수식에 입력됨을 아시면 함수의 중첩이 어려울께 없습니다.

H12의 셀에 셀포인터를 놓고 다음의 수식을 설정합니다.

=ROUND(DAVERAGE(E2:H10,3,F2:F3),0) & "점"

DAVERAGE 함수는 데이터베이스 형태로 평균을 구하는 함수입니다. 데이터베이스함수는 먼저 데이터테이블로 지정할 영역을 설정하여 인수로 설정합니다. E2:H10의 표2 를 선택합니다.

이후 함수의 계산작업으로 넘겨질 필드를 설정합니다. 위의 함수에서는 평균을 구합니다. 하여 평균을 구할 필드(레이블명, 레이블명의 주소, 데이터베이스영역에서의 필드순서를 아라비아 숫자로 설정)를 설정합니다.

마지막으로 조건을 설정하여 이조건에 해당하는 값의 데이터레코드(행) 만을 위의 조건에 포함시킵니다.

위의 문제로 요약하면 E2:H10의 데이터베이스범위에서 F2:F3의 조건을 만족하는 데이터의 범위에서 3번째열의 평균을 구합니다.

이렇게 구해진 평균을 ROUND함수를 사용하여 첫째자리에서 반올림합니다. 소수아래는 첫째자리에서 반올림하려면 보여질 자리수를 두번째 인수로 사용합니다. 첫째자리에서 반올림하면 정수부만 표시되기에 보여질 자리는 0 이 되는것이지요.

이렇게 두함수를 중첩하여 값을 얻은뒤 마지막으로 결과 값에 "점"을 붙여 출력하기 위해 '&' 연산자 를 사용합니다.
& 연산자는 문자열을 추가(연결) 하는 연산자입니다.

3. [표3]에서 수강과목수[B17:B24]와 수강과목별 할인율[A28:B31]을 이용하여 수강료[C17:C24]를 계산하시오. (8점)

▶ 한 과목의 수강료는 50000원으로 계산할 것
▶ 수강료 = 수강과목수 × 50000 × (1 - 할인율)
▶ VLOOKUP, HLOOKUP 함수 중 알맞은 함수를 선택하여 사용

풀이:

테이블에서 원하는 값을 찾아 수식에 사용하는 문제입니다. 찾을 값이 열방향으로 배열되어 있기에 VLOOKUP함수를 사용합니다.

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

=B17*50000*(1-VLOOKUP(B17,$A$28:$B$31,2))

수식을 입력후 C24 셀까지 점끌기탭(채우기핸들)을 이용하여 수식복사합니다.
수식을 분석합니다. 기본 수식은 문제에서 요구하는 수식을 사용합니다.

핵심은 문제의 수식에 필요한 할인율을 알아내는 부분입니다.

할인률은 아래의 할인율 표에서 '수강과목수' 를 이용하여 찾습니다. 찾기 함수 VLOOKUP 함수가 사용됩니다.

먼저 C17셀의 값을 구한다고 가정하고 찾는값은 동일행의 과목수 B17 값입니다. 이값을  찾을범위 A28:B31 에서 찾습니다. 절대참조하여 찾는 이유는 C17의 수식이 완성되면 아래로 수식복사를 합니다. 이때 찾을범위가 이동되는 것을 막기 위해 찾을범위를 절대참조하는 것입니다.찾는값 B17의 값을 찾을범위의 첫열에서 찾아 값을 찾으면 찾을범위 의 2번째 열의 값을 반환합니다.

VLOOKUP 함수의 세번째 범위인 찾는방법인수(TRUE혹은FALSE) 가 생략된 것은 TRUE 를 설정한 것과 같습니다.

또 TRUE 은 정확하게 일치하는 값이 없을경우 찾는값보다 작은 가장큰 값을 찾은값으로 반환합니다. 만일 위에서 과목수가 3이었다면 찾는범위에는 3이 없습니다. 이때는 찾는값 3보다 작은 가장큰값인 2를 찾은값으로 선택하고 할인율 5%를 반환하는 것입니다.

이렇게 VLOOKUP 함수에서 TRUE의 찾는 옵션을 사용하기 위해서는 반드시 찾는범위의 첫열이 오름차순정렬되어 있어야 정확한 값을 얻습니다.

문제에서 숫자를 찾고 찾는 범위가 오름차순 정렬되어있다면 찾는방법을 TRUE 를 생략하는 것이 좋습니다.

또 문자열을 찾거나 찾는범위가 오름차순 정렬되어 있지 않다면 반드시 FALSE 를 찾는방법옵션으로 설정해 주셔야 합니다. 이때는 찾을 값을 찾지 못하면 #N/A 에러를 발생한다는 것도 기억해 두세요.

찾는방법이 많은 분들이 VLOOKUP 을 어렵게 생각하는 가장큰 이유입니다. 찾는방법의 두옵션 TRUE와 FALSE 를 기억해 두세요. TRUE(숫자,오름차순정렬), FALSE(문자, 정렬안된 찾을범위) .

4. [표4]에서 각 분원의 1월부터 3월의 수강생수의 평균이 전체[F17:H24] 수강생수의 평균 이상이면 ‘우수’, 그렇지 않으면 공백으로 평가[I17:I24]에 표시하시오. (8점)

▶ IF, AVERAGE 함수 사용

풀이:

평균을 구하여 전체평균과 비교후 '우수' 와 공란으로 가려내는 문제입니다.

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

=IF(AVERAGE(F17:H17)>=AVERAGE($F$17:$H$24),"우수","")

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

수식을 살펴보면 먼저 F17:H17의 평균을 구합니다.이때는 한행의 평균을 구하는 것이기에 상대참조합니다. 그래야 수식복사시 자동으로 참조영역이 바뀌기 때문이지요.

이값을 전체평균인 F17:H24 의 평균과 비교합니다. 이때는 아무리 수식복사를 하더라도 전체평균의 범위는 변하지 않아야 합니다. 그래서 절대참조를 사용하여 고정하였습니다.

위의 두 평균값을 비교하여 그 행의 평균이 전체 평균이상이면 "우수" 를 표시하고 그렇지 않으면 공란을 표시하기 위해 쌍따옴표둘을 붙여 설정("") 합니다.

위의 문제에서는 절대참조를 사용할 범위를 명확히 파악하시면 아주 쉬운 문제입니다.

5. [표5]에서 오늘부터 시험일시[B36:B43]까지의 남은 일수를 잔여일수[C36:C43]에 표시하시오. (8점)

▶ TODAY, DAYS360 함수 사용

▶ 단, 1년을 360일로 가정하며 미국식 산술법을 사용

풀이:

두 날짜의 사이의 일수를 계산하는 문제입니다. DAY360함수는 두날짜 사이의 날수를 계산합니다. 첫번째인수를 두번째인수의 날짜보다 이전 날짜로 넣어야 양수로 일수가 계산됩니다.

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

=DAYS360(TODAY(),B36)

수식을입력하고 C43 셀까지 점끌기탭(채우기핸들)을 이용하여 드래그앤드롭하여 수식복사합니다.

수식을 분석합니다. 두날짜의 사이의 일수를 계산하는데 먼저 시험일은 앞으로 다가올 날수 이기에 두번째 날짜에 넣습니다.

첫번째 날짜로는 TODAY함수를 사용하여 오늘의 날짜를 구합니다.

문제에서 미국식 산술법을 사용하라고 되어 있습니다. DAYS360 함수는 두가지의 방식이 있습니다. 첫번째 미국식과 두번째 유럽식입니다.

미국식과 유럽식으로 나누는 이유는 1년을 360일로 가정하는 방법에 차이가 있기 때문입니다.

위의 문제에서는 이 옵션을 생략하였습니다. 생략하거나 FALSE 를 사용하면 미국식 방법으로 360일로 가정합니다. 주로 사용되는 옵션입니다. TRUE 를 사용하면 유럽의 방식으로 1년을 360일로 가정합니다.

시험에서는 미국식과 유럽식의 세부적인 가정방법을  아실필요는 없습니다. 단지 문제에서 제시하는 대로 미국식(FALSE) ,유럽식(TRUE) 를 사용하시면 됩니다.

@마늘빵.