[030] Excel 기본작업 - 조건식을 사용하여 고급필터 활용해보기

컴활 1급 출제자는 종종 고급필터 문제에서 

수험자에게 조건식을 활용할 것을 요구하는데,

고급필터 문제 중 가장 수준이 높고 까다로우므로

오답률이 상당히 높습니다.

수험자가 자주 멘붕에 빠지는 부분에 초점을 맞춰서

조건식을 사용한 고급필터에 대하여 알아보도록 하겠습니다.

[표1] 에서 판매가의 50%가 7500 이상이고 제품분류가 S1인 항목만을 나타내는 

고급필터 작업을 수행해야 하는 상황이라고 해 봅시다.

그리고 

수험자가 위에서 제시한 조건을 작성할 공간은 H3:H4(빨간박스)로 한정된다고 합시다.

여기서 수험자는 첫 번째 멘붕에 빠지게 됩니다.

이전까지는 조건식을 작성할 수 있는 조건 범위가 넓게 주어졌었는데

지금은 식을 작성할 수 있는 셀이 단 한 칸(파란박스)밖에 되지 않기 때문입니다. 

식을 작성할 수 있는 셀이 단 한 칸이라는 것은

넌 조건식을 작성해서 문제를 풀어야 한다는 출제자의 간접적인 명령입니다.

그러므로 조건식을 작성해야 합니다.

조건식은

'판매가' 필드에 있는 값을 2로 나누었을 때 7500 이상이 되는 항목과

'제품분류' 필드에 있는 값이 S1 항목이

두 개 다 만족했을 때 값이 추출되도록 연산식을 작성하면 됩니다.

=AND($D4/2>=7500,$B4="S1") 로 연산식을 작성했습니다. 각 연산식을 색깔별로 뜯어보면

$D4 / 2 >= 7500                                        판매가 필드에 있는 모든 자료를 2로 나눈 값이 7500 이상

= AND ( $D4 / 2 >= 7500 ,            )                                       이고

= AND ( $D4 / 2 >= 7500 , $B4 = "S1" )                                        제품분류 필드에 있는 모든 자료가 S1인 것

을 의미하므로 조건식과 일치하는 연산식이라고 볼 수 있습니다.

이제 조건식을 다 작성했습니다.

엔터를 누르면 FALSE라는 단어가 셀에 출력될 것입니다.

이것은 현재 D4셀과 B4셀이 내가 지정한 조건식과 일치하지 않는다는 뜻입니다.

셀에 이렇게 적혔다고 조건식이 D4셀과 B4셀만 검사하는 것은 아닙니다.

고급필터를 수행하게되면 D4셀 B4셀 뿐만 아니라

D5, B5, D6, B6... 모든 셀을 검색하게되므로 신경쓰지 않아도 됩니다.

이렇게 조건식을 작성한 후 수험자에게 두 번째 멘붕이 오는 타임이 있는데

필드 명(초록박스)을 어떻게 정해야 할 것인지 입니다.

조건식 안에 제품분류 필드와 판매가 필드는 모두 포함되어있는데

필드명을 작성할 수 있는 셀은 하나 뿐입니다.

필드명도 연산식을 사용해야 하는것일까요?

결론부터 말씀드리자면 초록박스에는 표 안에 있는 필드명을 사용하면 안됩니다.

왜냐하면 표1의 필드명(빨간 박스)과 

조건식의 필드명(초록 박스)이 같게 되면

고급필터는 연산식이 아닌 문자로 인식된 값을 연산식에서 가져오기 때문입니다.

즉, 엑셀은 위 표에서 연산식 =AND($D4/2>=7500,$B4="S1") 이 적용된 값을 가져오는 것이아니라

연산식 그 자체를 문자로 인식해서 가져오게 됩니다.

즉, =AND($D4/2>=7500,$B4="S1")이라는 값 자체를 수식이 아닌 문자로 인식해서 

=AND($D4/2>=7500,$B4="S1")이라는 문자와 같은 문자가 [표 1]에 있는지를 찾아본다는 것입니다.

이러한 현상을 피하기 위해서

초록박스 부분의 명칭은 [표 1]의 필드명과 다르게 설정해야합니다.

이것이 앞서 배웠던 고급필터와 다른 점입니다.

나머지 진행은 앞서 배운 고급필터 부분과 모두 동일합니다.

저는 연산식 윗 부분(빨간 박스)을 필드명 대신 '조건식' 이라고 작성했습니다.

그러면 고급필터가 제대로 실행되어 파란박스처럼 정확한 답이 나옵니다.