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

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

엑셀 고급 필터의 조건으로 수식을 설정하는 방법을 묻는 문제이다. 요구를 만족하는 두 보기의 수식을 찾아보자.

22. 다음 중 아래 워크시트의 [A1:E9] 영역에서 고급 필터를 실행하여 영어 점수가 영어 평균 점수를 초과하거나 성명의 두 번째 문자가 '영'인 데이터를 추출하고자 할 때, 조건으로 (가)와 (나)에 입력할 내용으로 옳은 것은?

① (가) =D2>AVERAGE(D2:D9) (나) =“=?영*”

② (가) =D2>AVERAGE(D2:D9) (나) =“=*영?”

③ (가) =D2>AVERAGE($D$2:$D$9) (나) =“=?영*”

④ (가) =D2>AVERAGE($D$2:$D$9) (나) =“=*영?”

풀이 :

엑셀의 고급 필터란, 원본 데이터 중에서 조건 설정을 만족하는 데이터만을 필터링하여 추출하는 '정렬 및 필터' 도구이다. 

먼저, 보기를 보면 문제가 요구하는 OR( 논리합:다른행 ) 연결된 고급 필터의 두 조건값이 수식임을 기억하자.

관련 게시물 : 고급 필터 선행 학습 - 와일드카드 문자와 논리연산자

조건 (가)와 (나)의 요구 만족 수식

[그림1] 엑셀 논리합 고급필터 조건 수식

(가) 조건 : 영어 점수가 영어 평균 점수를 초과하는 수식. 

ㄱ. G2 셀은 수식이기에 등호( = )로 시작한다.

ㄴ. 원본의 첫 번째 영어값( D2 )과 영어 평균( AVERAGE(D2:D9) )의 초과를 비교한다.

ㄷ. 각 레코드( 행 )마다의 영어 단일값은 참조가 변경되어야 하고( 상대참조 ), 영어 평균은 참조가 고정되어야 하기에( 절대참조 ) 참조 방식이 다르다.

ㄹ. 이를 조합한 논리 수식은 =D2>AVERAGE($D$2:$D$9) 이다.

참고1 : 만일, 영어 평균을 이미 알고 있다면, G1 의 필터명이 '영어' 로 대치되고, G2 의 수식 대신 텍스트 논리식( >84.375 )이 대신할 수 있다. 그러나, 문제는 조건 필드명이 '영어평균' 이고, 영어 평균 계산 수식을 필터에 포함하여야 하기에 함수식으로 작성되었다.

[그림2] 엑셀 다중 고급 필터 텍스트 조건값

(나) 조건 : 성명 원본의 두 번째 문자가 '영' 인 레코드 필터링.

ㄱ. 첫 번째 문자 위치에 와일드카드 문자 ? 로 1문자 자리 확보. ( ? )

ㄴ. 두 번째 문자로 '영' 을 조건 비교. ( ?영 )

ㄷ. 2~3개의 문자가 뒤에 추가될 수 있기에 와일드카드 문자 * 로 모든 자리 확보. ( ?영* )

ㄹ. 필드명이 '성명' 이고, 이상의 텍스트 조건값 설정도 완료이나, 보기에서는 수식을 요구한다.

ㅁ. 따라서, 수식( = )으로 시작하고, 쌍따옴표로 감싸 텍스트 문자열로 수식( "=?영*" ) 전달.

ㅂ. 이상을 조합하여 H4 셀에 텍스트 문자열로 수식 전달. ( ="=?영*" )

참고2 : 고급필터의 조건은 ( 필드명(열레이블)과 ) 텍스트 혹은 수식으로 설정된다.

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

엑셀 고급 필터 다중 조건 문제 풀이

1번) 2번) 의 (가) 수식 : =D2 > AVERAGE( D2:D9 )

(가) 수식은 영어와 영어 필드 평균을 비교하는 수식이다. 이 수식으로 D2 셀에 대해서는 정상적인 조건 비교가 가능하다. 

그러나, 레코드가 바뀌어 D3 셀의 영어값과 영어 평균을 비교할 때는 =D3 > AVERAGE( D3:D10 ) 과 같이 수식이 변경된다. 여기서, 상대참조로 인해 평균의 참조 범위가 바뀌었음을 알 수 있다. 따라서, 이 평균 범위를 절대참조로 고정해 주어야 하는 것이다.

결국, 1번)과 2번)의 (가) 수식은 잘못된 필터링 결과를 반환한다. 오답!!

2번) 4번) 의 (나) 수식 : =“=*영?”

와일드카드 문자 * 은 모든 문자를 의미한다. '영' 이란 문자 앞에 몇 개의 문자가 있어도 상관 없다는 것이다. 

와일드카드 문자 ? 는 한 문자를 의미한다. 즉, 뒤에서 2번째 문자가 '영' 인 값을 찾는 조건이다.

결국, 나머지 3번) 의 두 수식이 (가)와 (나)의 위치를 차지하는 것이다.

참고3 : (나)의 고급필터 조건 설정은 다음의 세가지가 같은 처리를 한다.

a. ?영* : 와일드카드 텍스트

b. ="?영*" : a 를 결과로 반환하는 수식

c. ="=?영*" : b 와 같은 기능을 하는 =?영* 을 결과로 반환하는 수식. =?영* 직접 입력시는 수식오류 발생. 그래서, 쌍따옴표를 묶은 텍스트.

 

관련 문제 :

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

[필기 풀이] 컴활2급 기출문제 2016년3회-24번 엑셀 고급 필터

엑셀의 자동 필터와는 다르게 조건을 직접 입력하여 설정하는 필터 기능인 고급 필터이다. 복잡할 수도 있지만 그래서, 보다 자세한 필터링을 가능하게 하는 기능이다. 자주 출제되는 부분이니 열공하시길...

관련 문제 : [필기 풀이] 컴활2급 기출문제 2016년2회-21번 엑셀 자동 필터

정답 : 3번

이전 문제 :

[필기 풀이] 컴활2급 기출문제 2017년1회-21번 엑셀 데이터 유효성 목록

마늘빵.