고급 필터는 자료를 조건에 맞게 걸러내는 기능입니다. 원래 표에서 원하는 부분만 남길 수도 있고 다른 곳에 표를 다시 만들 수도 있습니다. 원하는 부분만 남기면 나머지는 사라지지 않고 생략됩니다. 이번 포스팅은 다른 곳에 표를 만들어 보겠습니다.
고급 필터 쓰는 법 – 간단하게
1) 조건식을 빈 곳에 쓴다.
2) 데이터 리본 - [정렬 및 필터] - [고급]을 누른다.
3) 목록 범위는 자료로 정한다.
4) 조건 범위는 1에서 쓴 조건식으로 정한다.
5) 바라는 곳을 복사 위치로 한다.
(‘다른 장소에 복사’를 선택했다면)
6) 확인을 누른다.
(자세한 건 예제와 함께)
주의. 목록 범위는 자료 제목도 들어가야 합니다.
조건식 쓰는 법 - 간단하게
1) 조건을 가리는 항목을 쓴다.
2) 그 밑에 조건을 쓴다.
3) 끝
(자세한 건 예제와 함께)
시작하기 전에 예제파일 받아가세요!
하루 30분 고급필터.xlsx
우리만의 약속
첫째. 예제 파일로 한 번씩 예제 연습하기
둘째. 아리송하면 복습하기
셋째. 부담 없이 잔잔히 즐기기
* 이 포스팅은 엑셀2016을 기반으로 썼습니다.
예제 1) ‘중간고사’가 50 이상인 행을 표시할 것
중간고사
>=50
예제 2) ‘성별’이 여자인 행을 표시할 것
성별
여자
조건이 여럿일 때
여러 조건이 있다면 AND인지 OR인지 잘 판단해야 합니다.
AND : 여러 조건을 동시에 만족해야 함
OR : 여러 조건 중 하나라도 만족하면 됨
‘성별이 여자고 점수가 50점 이상’은 여자와 50점 이상을 모두 만족해야 하므로 AND입니다. ‘성별이 여자거나 점수가 50점 이상’은 둘 중 하나만 만족하면 조건에 맞으므로 OR입니다.
외우자. AND는 같은 줄에 OR은 다른 줄에!
예제 3) ‘성별’이 남자고 ‘나이’가 50 이하인 행을 표시할 것
성별 나이
남자 <=50
예제 4) ‘성별’이 남자거나 ‘나이’가 50 이하인 행을 표시할 것
성별 나이
남자
<=50
원하는 열만 나타내고 싶을 때
바라는 제목들을 입력하고 복사 위치로 지정한다.
예제 5) ‘중간고사’가 70점 이상인 행을 ‘이름, 나이, 등록일’만 표시할 것
① 미리 원하는 항목을 써둔다
② '복사 위치'를 이 항목들로 한다.
예제 6) ‘이름’에 ‘다’가 들어가는 행만 표시할 것
조건
=FIND("다", B3)>=1
왜 ‘이름’이 아니라 ‘조건’이죠?
고급 필터 조건에 함수나 계산값을 쓰고 싶으면 원본 자료에 없는 제목을 쓰거나 비워야 합니다!
참고. 제목을 비워도 조건 범위는 빈칸까지 넣어야 합니다.
예제 7) ‘등록일’이 7월인 행을 표시할 것
조건
=MONTH(E3)=7
예제 8) ‘등록일’이 2월이거나 10월인 행을 표시할 것
조건 1 조건 2
=MONTH(E3)=2
=MONTH(E3)=10
예제 9) ‘등록일’의 일이 10 미만인 행을 표시할 것
조건
=DAY(E3)<10
예제 10) ‘성별’이 남자가 아닌 행을 표시할 것
~가 아닌 조건은 <>가 좋습니다.
성별
<>남자
예제 11) ‘기말고사’가 ‘기말고사’ 평균 이상인 행을 표시할 것
조건
=H3>AVERAGE($H$3:$H$22)
예제 12) ‘중간고사’, ‘기말고사’가 전부 70 이상인 행을 표시할 것
첫 번째 방법
중간고사 기말고사
>=70 >=70
두 번째 방법
COUNTIF 함수는 조건에 맞는 수를 구하는 함수입니다.
70 이상을 만족하는 점수가 둘인 행을 찾게 합시다.
조건
=COUNTIF(G3:H3, ">=70")=2
예제 13) ‘기말고사’가 ‘중간고사’보다 높은 행을 표시할 것
조건
=G3<H3
예제 14) ‘회원번호’ 세 번째 글자가 6 이상인 행을 표시할 것
조건
=MID(F3, 3, 1)*1>=6
참고. LEFT, MID, RIGHT 함수는 글자를 텍스트로 빼내는 함수입니다. 텍스트를 숫자로 바꾸기 위해 *1을 해줍니다.
예제 15) ‘기말고사’ 상위 5등을 표시할 것
LARGE 함수를 응용합니다.
LARGE는 범위에서 ~번째로 큰 값을 반환합니다.
조건
=H3>=LARGE($H$3:$H$22, 5)
참고. LARGE 함수의 반대는 SMALL입니다.
SMALL은 범위에서 ~번째로 작은 값을 반환합니다.
여러 조건 한 셀에 쓰기
여러 조건은 나누어 써야 쉽지만, 시험에서 한 줄, 한 셀에 다 쓰라고 시키는 때도 있습니다. 이땐 AND와 OR 함수를 써야 합니다.
예제 16) ‘성별’이 여자고 ‘중간고사’가 30점 미만인 행을 표시할 것(조건은 셀 하나에 쓸 것)
조건
=AND(C3="여자", G3<30)
예제 17) ‘나이’가 30 미만이거나 50 초과면서, '중간고사'와 '기말고사'가 모두 80 이하인 행을 표시할 것(조건은 셀 하나에 쓸 것)
조건
=AND(OR(D3<30,D3>50),AND(G3<=80,H3<=80))
참고. 조건이 복잡할수록 큰 틀부터 짜맞춥시다.
