엑셀 자동필터와 고급필터

이번 시간에는 컴퓨터활용능력 2급 시험에 출제되는 필터에 대해서 알아보도록 하겠습니다. 필터는 필요한 데이터만 남기고 필요 없는 데이터는 걸러내는 기능인데 엑셀에서는 자동필터와 고급필터 기능이 있습니다. 일반적으로 자동 필터는 셀에 조건을 입력하지 않고 필터 하는 방식이고, 고급 필터는 셀에 조건을 입력한 후 필터를 하는 방식입니다. 물론 고급 필터도 셀에 조건 입력 없이 필터를 하기도 합니다.

1. 엑셀의 필터

    가) 워크시트에 입력된 자료 중 특정 조건에 맞는 자료만을 워크시트에 표시하는 기능

    나) 필터를 실행하면 조건에 만족하는 결과만 보이고 만족하지 못하는 결과는 숨겨지면서 행번호가 파란색으로 바뀐다.

    다) 와일드카드 문자( *, ?) 사용 가능

엑셀 와일드카드 문자

1. 자동필터

    가) 현재 선택한 표를 필터 하는 기능으로 다른 영역에 결과 테이블을 자동으로 생성할 수 없다.

    나) 하나의 필드에 대해서는 AND조건과 OR조건으로 설정이 가능하다.
          예) 부서명이 ‘영업부’이거나 ‘총무부인’

    다) 두 개 이상의 필드에 대해서는 AND 조건만 설정이 가능하고 OR 조건으로 설정은 불가능하다.
          예) 부서명이 ‘영업부’이거나 국어가 90이상인 → 불가능
                부서명이 ‘영업부’이면서 국어가 90 이상인 → 가능

   

2. 고급필터

    가) 두 개 이상의 필드에 대해서 AND와 OR조건 설정이 가능

    나) 필터 결과를 다른 영역에 자동 생성할 수 있다.

    다) 조건 입력하는 방법

            1) 필드명은 반드시 가로방향(열 방향)으로 적어야 한다.

            2) 조건에 논리값(결과가 TRUE나 FALSE로 나오는 값 : 일반적으로 두 값 사이에 비교연산자( = , <, >)가 들어가는 값)를 입력할 때는 표에 있는 필드명은 사용하면 안 된다.

            3) 조건입력을 할 때 AND조건(이고, 이면서, 모두)은 같은 가로 방향(열 방향) 줄에 입력하고, OR조건(~이거나, 또는 , 하나라도)은 줄을 바꿔서 입력한다. 

            4) 함수를 사용하여 조건을 입력하는 경우 원본 필드명과 동일한 필드명을 조건 레이블로 사용하면 안 된다.

고급필터 조건입력하는 방법

 

 

1. 다음 중 필터에 대한 설명으로 옳지 않은 것은?

    가) 필터 기능을 이용하면 워크시트에 입력된 자료들 중 특정한 조건에 맞는 자료들만을 워크시트에 표시할 수 있다.

    나) 자동 필터에서 여러 필드에 조건을 지정하는 경우 각 조건들은 AND 조건으로 설정된다.

    다) 고급 필터를 실행하는 경우 조건을 만족하는 데이터를 다른 곳에 추출할 수 있다.

    라) 고급 필터가 적용된 결과 표를 정렬할 경우 숨겨진 레코드도 정렬에 포함된다.

 

숨겨진 행과 열은 정렬, 인쇄되지 않는다.

답은 라)

 

1-1. 다음 중 필터의 기능에 대한 설명으로 옳지 않은 것은?

    가) 데이터에 필터를 적용하면 지정한 조건에 맞는 행만 표시되고 나머지 행은 숨겨진다.

    나) 자동 필터를 사용하여 데이터를 필터링하면 셀 범위나 표 열에서 원하는 데이터를 쉽고 빠르게 찾아 작업할 수 있다.

    다) 자동 필터에서는 여러 열에 동시에 조건을 설정하고 ‘또는(OR)’으로 결합시킬 수는 없다.

    라) 필터를 사용하려면 기준이 되는 필드를 반드시 오름차순이나 내림차순으로 정렬해야 한다.

 

필터를 사용할때 정렬이 되어 있을 필요는 없다.

답은 라)

 

1-2. 다음 중 고급 필터 실행을 위한 조건 지정 방법에 대한 설명으로 옳지 않은 것은?

    가) 함수나 식을 사용하여 조건을 입력하면 셀에는 비교되는 현재 대상의 값에 따라 TRUE나 FALSE가 표시된다.

    나) 함수를 사용하여 조건을 입력하는 경우 원본 필드명과 동일한 필드명을 조건 레이블로 사용해야 한다.

    다) 다양한 함수와 식을 혼합하여 조건을 지정할 수 있다.

    라) 고급 필터에서 다른 필드와의 결합을 OR 조건으로 지정하려면 조건을 다른 행에 입력한다

 

함수를 사용하여 조건을 입력하는 경우 원본 필드명과 동일한 필드명을 조건 레이블로 사용하면 안 된다.

답은 나)

2. 다음 중 자동필터가 설정된 표에서 사용자 지정 필터를 사용하여 검색이 불가능한 조건은?

    가) 성별이 '남자'인 데이터

    나) 성별이 '남자'이고, 주소가 '서울'인 데이터

    다) 나이가 '20'세 이하이거나 '60'세 이상인 데이터

    라) 주소가 '서울'이거나 직업이 '학생'인 데이터

 

자동필터는 다)처럼 하나의 필드('나이')에 대해서는 AND, OR조건 설정이 가능하나 라)처럼 두 개 이상의 필드(주소, 직업)에 대해서는 OR조건을 설정할 수 없다.

답은 라)

 

3. 다음 중 데이터 관리 기능인 자동 필터에 대한 설명으로 옳지 않은 것은?

    가) 필터는 데이터 목록에서 설정된 조건에 맞는 데이터만을 추출하여 나타내기 위한 기능으로 워크시트의 다른 영역으로 결과 테이블을 자동 생성할 수 있다.

    나) 두 개 이상의 필드(열)로 필터링 할 수 있으며, 필터는 누적 적용되므로 추가하는 각 필터는 현재 필터 위에 적용된다.

    다) 필터는 필요한 데이터 추출을 위해 조건을 만족하지 않는 데이터를 잠시 숨기는 것이므로 목록 자체의 내용은 변경되지 않는다.

    라) 자동 필터를 사용하여 추출한 데이터는 레코드(행) 단위로 표시된다.

 

자동 필터와 고급 필터의 차이점은 자동 필터는 다른 위치에 결과값을 자동 생성할 수 없고, 서로 다른 필드 간에 OR조건을 설정할 수 없다.

답은 가)

 

4. 성명 필드에 아래와 같이 [사용자 지정 자동 필터]의 조건을 설정하였다. 다음 중 결과로 표시되는 성명으로 옳지 않은 것은? 

    가) 남이수

    나) 이연

    다) 연지혜

    라) 홍지연

 

이*(이로 시작하는) 또는 *연*(연을 포함하는)

답은 가)

 

5. 다음 중 아래와 같이 조건을 설정한 고급 필터의 실행 결과에 대한 설명으로 옳은 것은? 

소속	근무경력
<>영업팀	>=30

    가) 소속이 '영업팀'이 아니면서 근무경력이 30년 이상인 사원 정보

    나) 소속이 '영업팀'이면서 근무경력이 30년 이상인 사원 정보

    다) 소속이 '영업팀'이 아니거나 근무경력이 30년 이상인 사원 정보

    라) 소속이 '영업팀'이거나 근무경력이 30년 이상인 사원정보

 

<>영업팀은 영업팀이 아니다는 조건이고, “<>영업팀”과 “>=30”이 같은 행에 입력되어 있기 때문에 AND(이고, 이면서) 조건이다.

답은 가)

 

6. 다음 중 아래의 고급 필터 조건에 대한 설명으로 옳은 것은? 

국사	영어	평균
>=80	>=85
		>=85

    가) 국사가 80 이상이거나, 영어가 85 이상이거나, 평균이 85 이상인 경우

    나) 국사가 80 이상이거나, 영어가 85 이상이면서 평균이 85 이상인 경우

    다) 국사가 80 이상이면서 영어가 85 이상이거나, 평균이 85 이상인 경우

    라) 국사가 80 이상이면서 영어가 85 이상이면서 평균이 85 이상인 경우

 

국사와 영어는 같은 행에 입력되어 있으므로 AND(이고, 이면서) 조건이고 평균은 다른 행에 입력되어 있으므로 OR(이거나, 또는) 조건이다.

답은 다)

 

7. 다음 중 고급 필터를 이용하여 전기세가 ‘3만원 이하’ 이거나 가스비가 ‘2‘2만 원 이하’인 데이터 행을 추출하기 위한 조건으로 옳은 것은? 

 

이거나 즉 OR 조건이므로 3만원 이하와 2만원 이하는 서로 다른 행에 입력해야 한다.
그리고 고급 필터 조건 입력에서 필드명(여기서는 ‘전기세’와 ‘가스비’)은 행방향(세로방향)으로 입력하면 안 된다.

답은 나)

 

8. 다음 중 성명이 '정'으로 시작하거나 출신지역이 '서울'인 데이터를 추출하기 위한 고급 필터 조건은? 

 

이거나 즉 OR 조건이므로 ‘정*’과 ‘서울’은 서로 다른 행에 입력해야 하고, 고급 필터 조건 입력할 때 필드명은 행방향(세로방향)으로 입력하면 안 된다.

답은 나)

 

9. 다음 중 근무기간이 15년 이상이면서 나이가 50세 이상인 직원의 데이터를 조회하기 위한 고급필터의 조건으로 옳은 것은? 

 

이면서 즉, AND 조건이므로 ‘>=15’와 ‘>=50’은 같은 행에 입력을 해야 한다. 고급 필터 조건 입력 시 필드명은 행방향(세로방향)으로 입력하면 안 된다.

답은 가)

 

10. 다음 중 아래 그림의 표에서 조건범위로 [A9:B11] 영역을 선택하여 고급필터를 실행한 결과의 레코드 수는 얼마인가? 

    가) 0

    나) 3

    다) 4

    라) 6

 

합계가 95 미만이면서 90 초과인 개수는 2개(김진아, 장영주 합계)이고
합계가 70 미만인 개수는 1개(김시내 합계)이므로
전체 레코드 수는 3개가 된다.

답은 나)

 

11. 다음 중 아래 그림과 같이 사원에 대한 근속연수 데이터에 주어진 조건으로 고급 필터를 실행한 경우의 결과값은? 

 

성명이 김으로 시작하면서 근속연수가 10 초과인 자료는 ‘김일민’이고,
직위가 사원이면서 근속연수가 5 미만인 자료는 ‘김유민’, ‘석영희’이므로 ‘김일민’, ‘김유민’, ‘석영희’ 레코드가 표시되면 된다.

답은 다)

 

12. 다음 중 아래 시트의 [A1:C8] 영역에 고급 필터 기능을 이용하여 판매수량이 전체 판매수량의 평균 이상의 데이터를 추출하기 위한 조건으로 옳은 것은? 

고급 필터 조건 입력을 할 때 논리식(TRUE나 FALSE로 표현 가능한 값으로 적는 식)을 적게 되면 조건의 제목은 표의 제목(여기서는 ‘지역’, ‘판매수량’, ‘판매금액’)을 사용하면 안 된다. 즉 가) 번과 다) 번은 될 수 없다.
그리고 평균 범위는 절대 참조로 수식을 입력해야 한다.

답은 라)

 

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

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

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

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

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

 

와일드카드 문자
1. * : 글자 수에 제한이 없음
2. ? : 글자 수에 제한이 있음. ? 1개당 1글자
예를 들면 “*영”이라는 조건은 글자 수는 상관없이 영으로 끝나기만 하면 되고
“?영”은 글자수가 2글자이면서 영으로 끝나는 이라는 뜻이 됨
2번째 문자가 영이라고 했으므로 조건은 “?영*”이 되어야 한다.
그리고 평균 범위는 변하면 안 되기 때문에 절대 참조로 범위를 선택해야 한다.

답은 다)