[필기 풀이] 컴활2급 기출 2016년3회-33번 엑셀 절대참조 함수식

컴퓨터활용능력 2016년도 3회 필기 기출 문제 33번을 풀이한 것이다.
문제가 요구하는 바른 엑셀 함수식을 찾는 문제이다. 상대 참조와 절대 참조의 차이와 사용법을 알고 있는지도 함께 묻고 있다.

33. 아래 워크시트에서 [A2:B8] 영역을 참조하여 [E3:E7] 영역에 학점별 학생 수를 표시하고자 한다. 다음 중 [E3] 셀에 수식을 입력한 후 채우기 핸들을 이용하여 [E7] 셀까지 계산하려고 할 때 [E3] 셀에 입력해야 할 수식으로 옳은 것은?

[그림0] 엑셀 함수식 문제 보기

① =COUNTIF(B3:B8, D3)
② =COUNTIF($B$3:$B$8, D3)
③ =SUMIF(B3:B8, D3)
④ =SUMIF($B$3:$B$8, D3)

풀이 :

필요에 맞는 바른 함수식을 작성해 보자. 문제의 요구는 B3:B8 범위의 학점을 분석하여 학점별 학생수를 구하는 것이다. 이번 문제의 핵심 키워드는 '학생수' 와 '채우기 핸들' 이다. 학점별 학생수를 구하는 수식을 작성하고 이 수식을 채우기 핸들로 수식복사 한다는 것이다.

[엑셀 문답] 엑셀의 채우기 핸들을 바로 알고 싶어요 :
http://secstart.tistory.com/615

수식 복사가 필요한 경우, 수식 작성의 유의점을 짚어보면서 문제를 풀어보자.

보기의 함수식 풀이

1번) =COUNTIF(B3:B8, D3)
조건부 개수를 구하는 엑셀 함수인 COUNTIF 를 사용한 수식이다. E3 셀에서만 함수식을 작성한다면, 문제 없는 수식이다.

그러나, 채우기 핸들로 E7 셀까지 수식 복사 한다면 복사 셀의 함수식은 옳지 않다. E4 셀의 수식을 보면 =COUNTIF(B4:B9, D4) 로 복사되었다. 여기서 중요한 부분은 COUNTIF 함수의 범위 인수 B4:B9 이다.

[그림1] 엑셀 COUNTIF 수식복사 오류

[그림1] 은 상대 참조된 함수식을 수식 복사 하였기에 생기는 문제이다.

[엑셀 모바일] COUNTIF 함수로 조건부 셀 개수 세기 :
http://secstart.tistory.com/772

2번) =COUNTIF($B$3:$B$8, D3)
1번) 과 같은 수식이다. 단, 보기 1번) 의 오류인 상대 참조가 절대 참조로 바뀌었다. E4 셀의 수식을 [그림1] 과 비교해보라. 절대 참조의 이유가 보일 것이다.

E3 셀의 수식을 E7 셀까지 채우기 핸들로 수식 복사한다면 절대 참조된 범위 인수는 변경되지 않고, 상대 참조된 조건값 인수만이 행방향으로 계속 증가되어 참조된다. 문제가 요구하고 있는 수식이다.

[그림2] 엑셀 COUNTIF 범위 절대 참조

[엑셀] 상대 참조, 절대 참조, 혼합 참조의 차이 배우기 :
http://secstart.tistory.com/154

3번) =SUMIF(B3:B8, D3)
4번) =SUMIF($B$3:$B$8, D3)
조건부 합계를 구하는 엑셀 함수인 SUMIF 함수를 사용한다. 합을 구할 범위 인수가 생략되어 있다. 따라서, B3:B8 범위의 합을 구한다. 그러나 이 범위는 텍스트인 학점들로 채워져있다. 따라서 결과는 0 이 될 수 밖에 없다.

3번) 보기는 1번) 보기와 같은 상대 참조 오류까지 포함되어 있다. 둘 모두 잘못된 수식이다.

[엑셀 모바일] SUMIF 함수로 조건부 합계 구하기 :
http://secstart.tistory.com/714

엑셀 함수식에서의 상대 참조와 절대 참조의 차이를 배울 수 있는 컴퓨터활용능력 필기 문제이다. 직접 작성해 보면 더욱 기억에 남을 것이다. 이렇게 컴활 문제는 엑셀 배우기에 많은 도움이 된다.

아,,, 상대 참조를 절대 참조로 변경하는 단축키는 F4 이다.

[엑셀 기초] 작업 시간 단축을 위한 기타 유용한 단축키 모음 :
http://secstart.tistory.com/729

정답 : 2번

[엑셀 문답] 조건부 평균 함수식이 오답인 이유? :
http://secstart.tistory.com/845

[필기 풀이] 컴활2급 기출 2016년3회-32번 엑셀 상대참조 매크로 :
http://secstart.tistory.com/856

마늘빵.