002_컴활_엑셀 고급필터

컴활 1급 시험에서 두 번째로 만나는 엑셀 고급필터문제로 배점이 5점인 문제입니다. 간단한 함수조건과 검색필터를 합하여 출제되는 문제로 비교적 간단한 자주 사용되는 함수인 문자열 함수(And, Mid, Or), Large, Small, Rank, Not, Average, Year, Month 등의 익숙한 함수가 출제되고 있지만, 가끔은 의외로 의표를 찌른 Median, Isodd, Isblank, Quotient, Days360, Percentile, Countifs 함수도 출제가 되어 시험보는 분들이 당황하는 경우가 가끔 생기기도 합니다.  그렇기 때문에 쉽게 안쉼할 수 없는 문제이기도 합니다.

 

 

01. 고급필터는

 

필터는 데이터중에서 특정한 조건을 만족하는 데이터만 추출하는 기능입니다, 원래 데이터 위치만 추출할 수 있는 자동필터와 다른 위치에서도 추출한 결과를 표시할 수 있는 고급필터가 있습니다. 자동필터는 매크로와 함께 가끔 출제되고 있습니다. 여기에서는 고급필터에 대해서면 설명 드리겠습니다.

 

02. 고급필터 방법

 

01) 지정된 영역에 조건범위를 지정한다.

 

지정방법은 ① AND조건(조건을 같은 행에 입력)과 OR조건(조건을 다른 행에 입력)을 각각 또는 통합하여 작성할 수 있으며, ② 조건과 함수입력란에 AND,OR 조건을 동시에 작성하는 방법으로 여기에서는 주로 ②의 방법을 사용하여 지정합니다

 

02) 제목표시줄을 제외한 첫줄을 참조하여 식을 작성한다

 

03) ① 데이터 영역에서 ② 정렬 및 필터탭의 ③ 고급을 클릭한다.

 

 

04) 목록범위가 제목표시줄을 포함하여 원본데이터가 있는 위치를 점선으로 표시되어 있는 지 확인

 

05) 필터결과를 지정된 위치에 표시한다, 추출할 항목이 미리 지정된 경우에는 항목을 미리 복사하여 필터결과를 지정한 위치에 붙여 놓는다.

 

03. 고급필터 지정방법 

 

문제 01) [A2:H26]영역에서 "ID번호"의 두번째 글짜가 0이고, '사물인터넷'이 '전체사물인터넷'의 하위5위 이내에 포함되는 행만을 대상으로 설정하시오(컴퓨터활용능력 1급 실기 2018년 -A)

 

★ 조건은 [A28:A29]영역내에 알맞게 입력하시오(AND, MID, SMALL 함수사용)

★ 결과는 [A31]셀부터 표시하시오

 

01) 지정된 영역에 조건범위를 지정한다.

 

02) 조건은 [A28:A29]영역내에 알맞게 입력하시오

 

① [A28]셀에 조건이라고 입력한다-

② "ID번호"의 두번째 글짜가 0이고,MID(A3,2,1)="0" * 제목표시줄을 제외한 첫줄을 참조 식을 작성

③ G3<=SMALL($G$3:$G$26,5) * SMALL(배열,K):배열에서 K번째 작은 값

④ AND로 식을 완성한다 : =AND(MID(A3,2,1)="0",G3<=SMALL($G$3:$G$26,5))

 

 

 

03) ① 데이터 영역에서 ② 정렬 및 필터탭의 ③ 고급을 클릭한다. 이 때 해당 데이터 영역을 선택한 후에 고급을 클릭한다.

 

 

04) 고급필터를 지정하는 화면이 표시됩니다

 

① 목록범위 : 자동으로 점선으로 표시

② 조건범위 : 조건과 함수로 지정된 [A28:A29] 영역지정

③ 복사위치 : 필터내용을 볼 결과위치인 [A31]셀을 클릭하여 지정

④ 지정이 완료되면 확인을 선택

 

  

05) 지정된 위치인 A31부터 필터내용이 표시됩니다.

 

 

* 문제 03)의 예처럼 필터내용이 표시될 '학년', '반', '이름', '6/9', '출석수' 필드를 미리 지정하여 순서대로 표시하는 경우의 문제도 종종 출제됩니다.

 

<컴활_실기1급_엑셀_02_고급필터.XLS 파일을 첨부합니다, 기출문제를 중심으로 11개의 문제를 시트별로 정리하였으며, 실습을 하시고 싶은 분은 다운로드 저장한 후 실습하시면 됩니다. 실행하기 위해서는 비밀번호가 필요한 데, 비밀번호는 7212번입니다.    파일다운로드  →   컴활_실기1급_엑셀_02_고급필터.xlsx

 

< 실습예제 11선 >

 

 

 문제 02) ‘구입총액’이 상위10위이내이고, ‘구입수량’이 10미만이거나 30이상인 행만을 대상으로 설정하시오. (컴퓨터활용능력 1급 실기 2018년 -A)

 

★ 조건은 [A30:A31]영역내에 알맞게 입력하시오(AND,OR,RANK 함수 사용)

★ 결과는 [A33]셀부터 표시

 

→ =AND(RANK(H4,$H$4:$H$27)<=10,OR(E4<10,E4>=30))

   * RANK(값,참조영역,순위결정방법) : (0이나 생략 : 내림차순, 0이 아닌 값 : 오름차순)

 

문제 03) [B3:T31]영역에서 ‘출석수’가 출석수의 중간값보다 작거나, ‘6/9'일이 빈셀인 행에 대하여 '학년', '반', '이름', '6/9', '출석수'열을 순서대로 표시하시오. (컴퓨터활용능력 1급 실기 2017년 -A)

 

★ 조건은 [V3:V4]영역내에 알맞게 입력하시오.(ISBLANK, OR, MEDIAN 함수 사용)

★ 결과는 [X3]셀부터 표시하시오.

 

→ =OR(T4<MEDIAN($T$4:$T$31),ISBLANK(S4))

   * MEDIAN(인수1, 인수2,...) : 인수들의 중간값 *ISBLANK(검사대상)

 

문제 04) [B2:G43] 영역에서 ‘작업사항’이 공백이 아니면서, '작업사항"이 품절도서가 아닌 행에 대하여 '입력일자,'신청자 이름''서명','저자','작업사항" 열을 순서대로 표시하시오. (컴퓨터활용능력 1급 실기 2017년 -B)

 

★ 조건은 [I2:I3]영역내에 알맞게 입력하시오. (AND, ISBLANK, NOT 함수 사용)

★ 결과는 [I7]셀부터 표시하시오.

 

→ =AND(NOT(ISBLANK(G3)),G3<>"품절도서") → =AND(G3<>“”,G3<>"품절도서")

    * 아니면서=and

 

문제 05) [A2:H32] 영역에서 학년이 “중1”이고, 현재강의수가 현재강의수의 90%위치의 백분율 수보다 크거나 같은 행만을 표시하시오. (컴퓨터활용능력 1급 실기 2016년 -B)

 

★ 조건은 [J2:J3]영역내에 알맞게 입력하rh, '강의코드‘, ’수업시작일‘, ’강사명‘, ’강의과목‘ 필드만 추출하시오. (AND, ISBLANK, NOT 함수 사용)

★ 결과는 [J5]셀부터 표시하시오.

 

→ =AND(D3="중1",F3>=PERCENTILE($F$3:$F$32,0.9))

    * PERCENTILE (Array, K) : K는 0에서 1까지 백분위수 

 

문제 06) [A2:I22] 영역에서 계약일의 연도가 2010이후이고, 병원비가 병원비의 상위10보다 큰 행만을 표시하시오.(컴퓨터활용능력 1급 실기 2015년 -B)

 

★ 조건은 [A24:A25]영역에서 알맞게 입력하시오. (AND, LARGE, YEAR 함수 사용)

★ 결과는 [A27]셀부터 표시하시오.

 

→ =AND(YEAR(E3)>=2010,G3>LARGE($G$3:$G$22,10))

    * LARGE(배열,K) : 배열에서 K번째 큰 값 *YEAR(일련번호 또는 문자열) : 연도부분

 

문제 07) [A2:L24] 영역에서 ‘환자구분’이 ‘건강보험’이고, ‘진료일자’와 ‘조제일자’가 같거나 ‘조제일자’가 1일이 많은 행만을 표시하시오.(컴퓨터활용능력 1급 실기 2014년 -A)

 

★ 조건은 [A26:A27]영역에 AND, DAYS360 함수를 이용하여 알맞게 입력하시오.

★ 결과는 [A30]셀부터 표시하시오.

 

→ =AND(G3="건강보험",DAYS360(A3,B3)<=1)

    * DAYS360(시작일, 종료일, 방식) : 1년을 360일로 가정, 시작일과 종료일 사이의 일수

 

문제 08) [A2:J21]영역에서 고객번호 앞의 4자리가 “3001”이거나 사용량과 전월사용량의 평균이 150이상, 250미만인 행만을 표시하시오. (컴퓨터활용능력 1급 실기 2014년 -C)

 

★ 조건은 [A23:J26]영역내에 알맞게 입력하시오. (LEFT, AVERAGE, AND, OR 함수 이용)

★ 결과는 [A27]셀부터 표시하시오.

 

→ =OR(LEFT(A3,4)="3001",AND(AVERAGE(D3,F3)>=150,AVERAGE(D3,F3)<250))

 

문제 09) ‘과정명’이 CAD이거나 ‘과정코드’가 J로 시작하는 데이터를 ‘과정명’, ‘과정코드’, ‘평균’ 만 순서대로 표시하시오

 

★ 조건은 [H3:H5] 영역내에 알맞게 입력하시오.

★ 결과는 [H7]셀부터 표시하시오. 

   

 

문제 10) [B4:M41] 영역에서 ‘기본제공데이터량(MB)'를 5로 나눈 홀수인 데이터의 ’고객명‘, ’고객등급‘, ’요금상품‘ 필드‘만을 순서대로 표시하시오. (수시 출제문제)

 

★ 조건은 [B43:B44] 영역내에 알맞게 입력하시오. (QUOTIENT, ISODD 함수사용)

★ 결과는 [D43]셀부터 표시하시오.

 

→ =ISODD(QUOTIENT(H5,5))

    * ISODD(숫자) : 숫자값이 홀수이면 True를 반환 → =ISODD(7) = TRUE

    * QUOTIENT(피제수,제수) : 피제수를 제수로 나누어 몫을 구한 값   

 

 

문제 11) 연료가 '가솔린'이고, '출시가', '구입가'가 각각 4000 이상인 테이터 중 ‘차량코드‘, ’구분‘, ’연비‘, ’배기량‘, ’최대출력‘만을 표시하시오. (수시 출제문제)

 

★ 조건은 [K2:K3] 영역내에 알맞게 입력하시오. (AND, cOUNTIF 함수 이용)

★ 결과는 [M2]셀부터 표시하시오.

 

→ =AND(E3="가솔린",COUNTIFS(I3,">=4000",H3,">=4000"))

   * COUNTIFS(조건범위1, 조건1, 조건범위2, 조건2..) : 여러 범위에 걸쳐 조건을 적용하고 모든 조건 을 만족하는 셀의 개수