[개미의 걸음 2과목 스프레드시트 일반] 기본작업② 고급 필터

고급필터

비교 조건을 사용한 데이터 추출 작업에 사용되는 필터

기본조건 지정방법	조건을 지정할 범위의 첫 행에는 원본 데이터 목록의 필드명을 입력하고, 그 아래 행에 조건을 입력 조건을 모두 같은 행에 입력하면 AND조건, 다른 행에 입력하면 OR 조건
고급조건 지정방법	함수나 식의 계산 값을 고급 필터의 찾을 조건으로 지정하는 방식[다양한 함수와 식 혼합 가능] 조건 지정 범위의 첫 행(조건 필드명)에는 원본 데이터의 필드명과 다른 필드명을 입력하거나 생략하고, 그 아래 행에 조건을 입력 함수나 식을 사용해 조건 입력시, 셀에는 비교되는 현재 대상의 값에 따라 True/False로 표시됨

 

셀 참조[F4키 사용]

상대 참조	수식을 입력한 셀의 위치가 변동되는 주소 참조가 상대적으로 변경됨
	ex>  B4
절대 참조	수식을 입력한 셀의 위치와 관계없이 고정된 주소 참조가 변경되지 않음
	ex>  $B$4
혼합 참조 [열고정/ 행고정]	열 고정 혼합 참조 :열만 절대 참조가 적용됨 행 고정 혼합 참조 : 행만 절대 참조가 적용됨
	ex>  $B4 / B$4
다른 워크시트의 셀 참조	다른 워크 시트에 있는 셀의 데이터를 참조할 경우, 시트 이름과 셀 주소 사이를 느낌표(!)로 구분 시트 이름에 한글, 영어 외의 문자가 있을 경우, 작은 따옴표(' ')로 묶어줌
	ex>  Sheet1!B4
3차원 참조	여러 시트의 동일한 셀이나 셀 범위에 대한 참조 참조하는 시트가 연속적으로 나열되어 있고, 셀 주소가 모두 동일할 때는 첫 번째 시트와 마지막 시트의 이름을 콜론(:)으로 연결하고 셀 주소를 한 번만 지정하면 됨 SUM, AVERAGE, STDEV 등의 함수를 사용 가능 배열 수식에는 3차원 참조를 사용할 수 없음
	ex> SUM(Sheet1:Sheet3!B4)
다른 통합문서의 셀 참조	다른 통합 문서에 있는 셀의 데이터를 참조할 경우, 통합 문서의 이름을 대괄호( [ ] )로 묶어줌 경로명은 작은 따옴표(' ')로 묶어줌
	ex> 'C:\[엑셀소스]Sheet1'

 

 

실기 예제 문제

출처 : 대한상공회의소 홈페이지

1. 조건 설정하기[Q3:Q4]

• 문제에서 지정한 영역 내[Q3:Q4]에서 조건 입력[고급 조건 사용]
     → 첫번째 행은 필드명이므로 생략
     → 두번째 행에 조건 입력
• 지정한 범위가 넓을 경우, 해당 영역 내에서 입력[일반적으로 기본 조건 사용]
    → 첫번째 행은 필드명
    → 두번째 행부터 조건 입력
          AND 조건 : 동일한 행에 조건 입력
          OR  조건 : 서로 다른 행에 조건 입력
• 함수 아이콘 클릭 후 입력하면 좀 더 쉽게 조건 설정 가능
• 해당 함수 사용법을 모를 경우, 수식입력줄에서 해당 함수를 클릭하면 쉽게 조건 설정 가능
• 일반적으로 조건 설정 시, 알아서 탐색하므로 범위가 아닌 하나의 셀만 조건식에 입력
  비교값에서 범위를 지정할 경우에는 절대참조를 위해 F4를 누름!!

*	글자 수 제한없이 조건 검색
	ex> 피보험자의 이름이 '신'으로 시작 → 신*
?	글자 수 1개만 조건 검색
	ex> 피보험자의 이름 두번째 글자가 '자' → ?자*

# 일반식과 논리식

일반식	논리식
주어진 데이터와 필드명이 일치해야 됨 [일반적으로 기본 조건 사용시 사용]	주어진 데이터와 필드명이 일치하면 안됨 [일반적으로 고급 조건 사용시 사용]
식에 첫 행의 셀 주소가 없음	식에 첫 행의 셀 주소가 포함
AND, OR 사용 불가	AND, OR 사용 가능
셀에 결과값이 식으로 나옴	셀에 결과값이 TRUE, FALSE로 나옴
ex> 지급금액이 평균 이상인 자료만 표시     ">="&AVERAGE($L$4:$L$40)	ex> 지급금액이 평균 이상인 자료만 표시     =L4>=AVERAGE($L$4:$L$40)

• 고급필터 사용시, 일반식과 논리식을 함께 사용할 때, 일반식에는 필드명을 사용해야됨에 유의!!!

알아두면 유용한 함수

LEFT(문자열, 자릿수)	해당 문자열을 왼쪽에서부터 자릿수만큼 추출 [결과값은 문자열!!!!] 숫자일 경우는 결과값을 숫자로 바꾸거나(VALUE 사용) 비교 숫자를 " "를 사용해 문자로 변경
MID(문자열, 시작값, 자릿수)	해당 문자열을 시작 값에서부터 주어진 자릿수만큼 추출 [결과값은 문자열!!!] 숫자일 경우는 결과값을 숫자로 바꾸거나(VALUE 사용) 비교 숫자를 " "를 사용해 문자로 변경
RIGHT(문자열, 자릿수)	해당 문자열을 오른쪽에서부터 자릿수만큼 추출 숫자일 경우는 결과값을 숫자로 바꾸거나(VALUE 사용) 비교 숫자를 " "를 사용해 문자로 변경
VALUE(문자열)	해당 문자열을 숫자 데이터로 변경해주는 함수
FIND(찾는 문자, 문자열, 시작위치)	해당 문자열에서 찾는 문자를 몇번째 위치의 글자부터 찾는 함수 문자열 전체에서 찾을 경우 생략 찾는 문자가 없는 경우, ERROR 발생
IFERROR(식, ERROR시 출력될 값)	해당 식에서 ERROR가 발생할 경우, 지정한 값으로 출력하는 함수
SMALL(범위, n)	범위 내에서 n번째로 작은 값 추출 [범위는 반드시 절대참조!]
LARGE(범위, n)	범위 내에서 n번째로 큰 값 추출  [범위는 반드시 절대참조!]
YEAR(날짜)	지정된 날짜에서 연도만 추출
MONTH(날짜)	지정된 날짜에서 월만 추출
DAY(날짜)	지정된 날짜에서 일만 추출
HOUR(시간)	지정된 시간에서 시만 추출
MINUTE(시간)	지정된 시간에서 분만 추출
SECOND(시간)	지정된 시간에서 초만 추출
RANK(인수, 범위, 논리값) RANK.EQ(인수, 범위, 논리값)	지정된 범위 안에서 인수의 순위를 구해주는 함수 동일한 값들은 동일하지 않을 경우 나올 수 있는 순위들 중 가장 높은 순위를 동일하게 표시 논리 값이 0이거나 생략되면 내림차순, 0이외의 값은 오름차순
AVERAGE(인수1, 인수2, ...)	인수들의 평균 값을 구해주는 함수

※ 오른쪽 3글자가 100미만이거나 연도가 2006~2008년에 해당하는 자료

    → value(right(B2,3))<100
    → right(B2,3)<"100"

 

2. 원하는 필드만 가져오기

• 주어진 데이터에서 [ctrl]키를 눌린채 필요한 필드명들만 선택하여 [Ctrl+C]한 뒤, 결과를 표시할 셀[Q11]에 [Ctrl+V]
    → 필드명들이 연속된 경우 드래그하여 선택
• 선택 해제를 위해 [ESC]키를 눌림

 

3. 고급 필터 사용

• 주어진 데이터 전체 범위 지정[B3:O40] 후, [데이터 → 정렬 및 필터 그룹 →고급] 클릭
     ※ 위에서 아래로 드래그해서 범위 지정!
• 이때, 필드명이 생략된 셀도 함께 범위로 지정!
• 문제에서 주어진 조건 범위가 넓은 경우, 그 중 조건을 입력한 셀들만 범위를 지정