엑셀에서 범위의 데이터를 원하는 조건으로 조회(필터링)하려면 다음과 같이 리본메뉴 > 데이터 > "필터" 기능을 이용하여 원본데이터 자체를 필터링했지만...
Office365버전부터 제공되는 FILTER함수를 이용하면 원본 데이터를 손대지 않고 조건에 따라 별도의 범위에 조회된 결과를 표시할 수 있다.
=FILTER(배열, 포함, [if_empty])
배열 또는 범위에서 원하는 조건으로 데이터를 조회(필터링)한다.
인수 :
- 배열 : 조회(필터링)할 배열 또는 범위
- 포함 : 조회(필터링)의 조건
- if_empty : 찾는 값이 없을 때 표시할 값
사용예 :
1) 범위에서 특정 조건에 해당하는 자료를 조회하기
다음과 같이 "거래처별 판매실적" 자료가 있다고 할 때 FILTER함수를 이용하여 상품이 "노트"인 것만 조회해 보자.
FILTER함수는 조회 결과를 원하는 범위에 표시해 주므로 아래와 같이 적당한 위치에 수식을 입력한다.
=FILTER($B$6:$E$13,($D$6:$D$13="노트"),"찾는 자료가 없음")
수식이 정상적으로 입력되었으면 다음과 같이 조회 결과가 표시된다.
※ 주의 : 조회 결과를 범위로 가져오도록 지정했는데 가져올 범위에 이미 값이 있으면 다음과 같이 #SPILL! 오류가 발생하므로 결과를 가져올 범위가 비어 있어야 한다.
입력된 수식을 풀어보면 다음과 같다.
2) 여러 조건에 해당하는 자료를 조회하기
이번에는 상품이 "노트"이고 판매금액이 30,000원보다 큰 자료를 조회해 보자.
FILTER함수는 조회 결과를 원하는 범위에 표시해 주므로 아래와 같이 적당한 위치에 수식을 입력한다.
=FILTER($B$6:$E$13,($D$6:$D$13="노트")*($E$6:$E$13>30000),"찾는 자료가 없음")
수식이 정상적으로 입력되었으면 다음과 같이 조회 결과가 표시된다.
입력된 수식을 풀어보면 다음과 같다.
=FILTER($B$6:$E$13,($D$6:$D$13="노트")*($E$6:$E$13>30000),"찾는 자료가 없음")
위 식에서 두번째 인수인 ($D$6:$D$13="노트")*($E$6:$E$13>30000)를 유심히 살펴보자.
찾는 조건 범위가 2개인데 중간에 곱셈표시(*)가 붙어 있다.
"*"은 곱셈연산자라고 하는데 AND조건을 적용하는 것이다.
$D$6:$D$13 범위에서 상품이 "노트"이고, $E$6:$E$13 범위에서 판매금액이 30,000보다 큰 것을 찾는다는 의미이다.
3) 조건값을 표에 입력해서 조회하기
이번에는 다음과 같이 조건값을 표에 입력한 후 수식에서 입력값을 참조하여 결과를 조회해 보자.
B38셀에 다음 수식을 입력한다.
=FILTER($B$6:$E$13,IF($C$33="",($B$6:$B$13=$B$6:$B$13),($B$6:$B$13=$C$33))*IF($C$34="",($D$6:$D$13=$D$6:$D$13),($D$6:$D$13=$C$34))*IF(C35="",($E$6:$E$13=$E$6:$E$13),($E$6:$E$13>=$C$35)),"찾는 자료가 없음")
수식이 정상적으로 입력되었으면 판매일자가 2020년 7월 6일이고 상품이 "클립"인 자료가 다음과 같이 조회된다.
입력된 수식을 풀어보면 다음과 같다.
각각의 조회조건에 해당하는 값을 조회하게 되는데
IF($C$33="",($B$6:$B$13=$B$6:$B$13),($B$6:$B$13=$C$33))
IF함수를 이용하여
- 조회조건에 값이 비어 있다면 모든 값을 가져오고,
- 비어 있지 않으면 입력된 조회조건에 해당하는 값을 가져오는
방식이다.
조회조건 필드가 3개이므로 위의 형태를 3개를 만들어서 곱셈연산자(*)를 이용하여 AND조건으로 연결하여 수식을 완성하였다.
※ 주의 :
만약 IF를 함수를 이용하지 않고 다음과 수식을 입력했을 때
- 조회조건에 값을 전부 입력하면 조회가 되지만
- 비어 있는 필드가 있으면 조회되지 않는다.
=FILTER($B$6:$E$13,($B$6:$B$13=$C$33)*($D$6:$D$13=$C$34)*($E$6:$E$13>=$C$35),”찾는 자료가 없음”)
조건값을 입력하는 방식을 사용하면 다음과 같이 조회시스템처럼 수식을 바꾸지 않고 원하는 자료를 조회할 수 있다.
판매일자가 2020년 7월 6일이고 판매금액이 20,000원보다 크거나 같은 것이 조회되었다.
조회조건을 하나도 입력하지 않으면 모든 자료가 조회된다.
[ 엑셀 찾기 및 참조영역 함수 목록 ]
- 엑셀함수 SORT - 범위 또는 배열의 내용을 정렬하기
- 엑셀함수 SORTBY - 범위의 값을 기준으로 범위의 내용을 정렬하기
- 엑셀함수 FILTER - 원하는 조건으로 데이터 조회(필터링)하기
- VLOOKUP의 단점을 해결한 XLOOKUP함수로 표에서 값찾기
- 엑셀함수 HLOOKUP - 표를 수평(가로)으로 따라가면서 값찾기
- 엑셀함수 HYPERLINK - 하이퍼링크 만들기
- 엑셀함수 ROWS - 범위나 배열의 행의 개수 구하기
- 엑셀함수 COLUMNS - 범위나 배열의 열의 개수 구하기
- 엑셀함수 AREAS - 참조영역내의 영역의 개수 구하기
- 엑셀함수 ADDRESS - 행,열 번호로 셀주소 표시하기
- 엑셀함수 TRANSPOSE - 행과 열을 바꾸기
- 엑셀함수 INDIRECT – 참조를 동적으로 바꾸기
- 엑셀함수 LOOKUP - 행 또는 열의 같은 위치에 있는 값 찾기
- 엑셀함수 OFFSET - 행열이동 후 참조구하기
- 엑셀함수 COLUMN - 열번호를 자동으로 매기기
- 엑셀함수 ROW - 행번호를 자동으로 매기기
- 엑셀함수 CHOOSE - 값목록에서 원하는 값 선택하기
- 엑셀함수 MATCH - 특정 범위에서 값의 위치 찾기
- 엑셀함수 INDEX - 특정 범위에서 행과 열을 이용하여 값 찾기
- 엑셀 VLOOKUP 함수로 표에서 값 찾기(간단 버전)
- 엑셀에서 값을 찾을 때 VLOOKUP, HLOOKUP 함수 사용하기
- VLOOKUP으로 안될때 INDEX,MATCH 사용하기
- INDEX, MATCH를 이용한 다중조건으로 값찾기