[필기 풀이] 컴활1급 기출문제 2017년2회-25번 엑셀 고급 필터

2017년도 2회 컴퓨터활용능력 1급 필기 기출문제 25번을 풀이한 것이다.

엑셀 고급 필터를 사용하는 방법을 예를 들어 묻고 있다. 고급 필터를 사용했을 때의 바른 처리 결과를 찾아보자.

25. 다음 중 고급 필터의 조건 범위를 [E1:G3] 영역으로 지정한 후 고급 필터를 실행했을 때 결과로 옳은 것은?(단, [G3] 셀에는 '=C2>=AVERAGE($C$2:$C$5)'이 입력되어 있다. )

① 코너가 '잡화' 이거나 담당이 '남'으로 끝나고, 코너가 '식료품'이거나 판매금액이 판매금액의 평균 이상인 데이터

② 코너가 '잡화' 이거나 '식료품'이고, 담당에 '남'이 포함되거나 판매금액의 평균이 5,122,000 이상인 데이터

③ 코너가 '잡화' 이고 담당이 '남'으로 끝나거나, 코너가 '식료품'이고 판매금액이 판매금액의 평균 이상인 데이터

④ 코너가 '잡화' 이고 담당이 '남'이 포함되거나, 코너가 '식료품'이고 판매금액의 평균이 5,122,000 이상인 데이터

풀이 :

엑셀 고급 필터를 사용하는 문제이다. 고급 필터의 조건 범위에는 수식이 포함되어 있다. 고급 필터에 포함된 수식은 어떻게 처리할까?

고급 필터에 전달되는 수식은 모두가 논리식이다. 이 논리식의 결과가 TRUE 인가 FALSE 인가에 따라 참/거짓을 가늠하여 필터링하는 것이다. 이 문제에 필요한 몇 가지를 먼저 알고 가자.

엑셀 고급필터 조건 범위 해석법

1. 같은 행의 값은 "이고" 로 해석한다. AND 연산 처리.

2. 다른 행의 값은 "거나" 로 해석한다. OR 연산 처리.

3. 엑셀에서는 AND 연산( 논리곱 )이 OR 연산( 논리합 )에 우선한다. ( 행우선 )

관련 게시물 :

[엑셀 모바일] AND 함수로 모든 조건의 만족(논리곱) 테스트

[엑셀 모바일] OR 함수로 논리합의 조건 만족 테스트

문제 보기의 G3 논리식 해석

수식이 고급필터 조건으로 사용된 G3 셀을 해석해 본다.

=C2>=AVERAGE($C$2:$C$5)

이는 C2 라는 상대참조 단일 셀과 $C$2:$C$5 라는 절대참조 범위의 평균을 비교한다. 상대참조 C2 는 절대참조 범위에 포함되기에

고급 필터는 각 레코드(행)를 분석하여 조건에 부합하는 값들만을 필터링한다. 따라서, 수식에서는 C2 로 주어졌지만, C3, C4, C5셀도 절대참조 평균인 AVERAGE($C$2:$C$5) 과 비교된다. 단일 셀이 상대참조로 작성되고, 고정범위로 잡혀야 하는 평균 범위가 절대참조로 지정된 이유이다.

요약하면, C2 ~ C5 각 판매금액이 C2:C5 의 판매금액 평균과 비교되어, 그 이상이라면, TRUE( 참 )를 반환하고, 미만이라면, FALSE( 거짓 )를 반환하는 것이다.

관련 게시물 :

[필기 풀이] 컴활2급 기출문제 2017년1회-28번 엑셀 수식 조건부서식

[엑셀 문답] 조건부 서식에서 수식 사용의 기본

엑셀 수식 포함 고급 필터 문제 풀이

문제는 E1:G3 를 고급 필터의 조건 범위로 사용한다. 이 범위를 해석해보자.

[그림1] 엑셀 고급 필터 조건 범위 해석

E1:G3 의 조건 범위를 해석해 보면,

1. 코너의 '잡화' 와 담당의 *남( '남' 을 포함 )은 동일 행이다. 따라서, 둘은 AND 연산 처리 ( '이고' 로 연결됨 ) [해석법1]

2. 코너의 '식료품' 과 식의 판매금액이 판매금액 이상 논리식은 동일 행 이다. 따라서, 둘은 AND 연산 처리 [해석법1]

3. 1과 2의 각 연결은 AND 연산이기에 다른 행 조건 처리보다 우선하여 처리 [해석법3]

4. 1과 2는 다른 행이다. 따라서, 두 그룹은 OR 연산 처리 ( '거나' 로 연결 ) [해석법2]

즉, 코너가 '잡화' 이고 담당이 '남' 을 포함하거나, 코너가 '식료품' 이고 판매금액이 판매금액 평균이상인 레코드(행) 를 결과로 표시한다.

참고1 : 엑셀 2007 이상에서 *남 은 '남' 으로 끝남을 의미하는 것이 아니다. '남' 이 포함됨을 의미한다. 즉, 앞에 어떤 문자가 있든 '남' 을 포함하는 셀을 찾는다는 의미이다. 이것은 반드시 알고가자.

관련 문제 :

[실무 엑셀] 엑셀의 기본적인 연산자와 기호의 의미

[필기 풀이] 컴활2급 기출 2017년1회-22번 엑셀 고급필터 다중 조건

1번) 동일 행의 조건들을 '이거나' 로 연결한 것, 다른 행 조건을 '이고' 로 연결한 것이 오류. 즉 모든 해석이 잘못되었다.

2번) 동일 행보다 동일 열을 먼저 처리한 것이 가장 큰 오류이다. 즉, AND 연산이 OR 연산에 우선한다는 것을 어긴 것이다. 또, 판매금액 평균을 C2의 단일 값만으로 비교한 것도 오류이다. [위 논리식 해석 참고]

3번) 정답에 가장 가까운 해석이다. 그러나, 이 보기에도 티끌이 있다. 와일드카드 문자 *남 은 '남' 으로 끝남이라기 보다, '남' 이란 텍스트를 포함함을 말한다. 그래서, [그림1] 에서 '김남희' 가 결과에 포함된 것이다.

참고2 : 엑셀 찾기 및 바꾸기에서 *남 을 찾아보라. '남' 이 포함된 모든 셀이 선택된다. 고급필터도 마찬가지로 처리된다. 비교값으로 '남' 도 찾기해 보라. '남' 으로 시작하는 셀만이 찾기된다.

4번) "코너가 '잡화' 이고 담당이 '남'이 포함되거나" 이 부분은 정확한 표현이다. 그러나, 판매금액 평균을 상대 참조한 C2의 셀 값으로 직접 비교한 것이 오류이다.

관련 문제 :

[필기 풀이] 컴활2급 기출 2016년1회-24번 엑셀 고급 필터 핵심

[필기 풀이] 컴활2급 기출 2016년2회-35번 엑셀 다중 고급 필터

너무도 자주 출제되는 엑셀 고급 필터 문제이다. 다르다면, 수식이 포함된 조건 설정을 사용한다는 것이다.

나의 선택으로는 이 문제에는 정답이 없다. 그러나, 대한상공회의소의 정답은 3번이다. 여러분은 이 보기의 '끝나거나' 를 '포함되거나' 로 기억하기 추천한다. 선택은 여러분의 몫 .....

정답 : 3번

이전 문제 :

[필기 풀이] 컴활1급 기출 2017년2회-24번 엑셀 중복된 항목 제거

마늘빵.