엑셀 Filter 함수

1. 의미

범위 내에서 조건을 만족하는 데이터를 추출해 주는데 배열로 반환합니다.

 

2. 구문

=FILTER(array,include,[if_empty])

 

- array : 배열 또는 필터링할 데이터 영역입니다.

- include : 높이 또는 너비가 위 배열과 동일한 조건식입니다.

- if_empty : 대괄호 사이에 있으므로 옵션이며, 조건에 맞는 데이터가 없을 경우에 반환할 값을 지정하는 것입니다.

조건에 맞는 데이터가 없을 때 if_empty 값을 지정하지 않으면 #CALC! 오류가 발생합니다,.

 

3. 예제 파일

Filter함수.xlsx

0.01MB

 

A열부터 C열까지 데이터가 있고, E열과 F열에 조건이 있으며, 

데이터 유효성 검사 시 목록으로 사용할 원본이 G와 H열에 있고,

i열부터 K열에 조건을 만족하는 데이터를 표시하려고 합니다.

 

E2셀의 원본을 지정할 때는 날짜(숫자)이므로 Count함수를 사용했는데,

F2셀의 원본을 지정할 때는 H열이 문자이므로 Count가 아닌 CountA함수를 사용해야 합니다.

 

 

4. 고급 필터와 비교

Filter 함수는 데이터 탭, 정렬 및 필터의 고급 필터와 같은 결과를 가져오는데,

고급 필터가 명령이라면 Filter함수는 함수이고,

고급 필터는 조건 범위와 복사 위치가 다른데,

Filter 함수는 조건이 함수 내에 입력되는 것이 다릅니다.

 

가. 고급 필터의 사용 예 1

먼저 데이터 탭, 정렬 및 필터 그룹의 고급 필터 명령을 누릅니다.

 

그러면 목록 범위로 데이터 영역이 자동 지정되는데, 조건 범위로 날짜와 지점명이 있는 E1에서 F2를 지정합니다.

 

그리고, 복사 위치는 '현재 위치에 필터'로 되어 있어 선택을 할 수 없게 되어 있는데,

'다른 장소에 복사'를 선택하고, M1셀을 클릭합니다.

 

이제 확인 버튼을 누르면 M1셀부터 O4셀에 날짜가 일치하는 3건이 표시됩니다.

날짜가 모두 보이지 않으므로 M열과 N열 사이의 경계선을 더블 클릭해서 자동 조절합니다.

 

그러면 아래와 같이 날짜가 제대로 보입니다.

 

나. 고급 필터의 사용 예 2

지점명도 선택해서 넣으면 2가지 조건을 만족하는 데이터만 추출되며,

둘 중 하나라도 만족하는 데이터를 추출하려면 지점명을 한 줄 아래인  F3셀에 입력해야 합니다.

 

F3셀의 목록 상자 버튼을 눌러 A라고 입력하고

다시 고급 필터를 하면 또 '현재 위치에 필터'가 선택되어 있는데, '다른 장소에 복사'를 선택한 후 확인 버튼을 누르

 

날짜가 23/4/10이고, 지점명이 A인 데이터 한 건만 추출됩니다.

 

5. Filter 함수 사용 예

Filter 함수는 include에 조건을 입력하는데, 다중 조건을 입력할 때처럼

조건을 모두 만족해야 하는 And 조건인 경우에는 *(곱하기) 연산자를 사용하고,

조건 하나라도 만족하면 되는 Or조건인 경우는 +(더하기) 연산자를 사용합니다.

 

가. 사용예 1 - And 조건

i2셀에 

=filter(a2:c14,(a2:a14=e2)*(b2:b14=f2)) 라고 입력하고 엔터 키를 누르면

 

M열에서 O열의 고급 필터 결과와 같이 i2셀에서 K2셀에 결과가 표시됩니다.

 

나. 고급 필터와 차이점 2

① 고급 필터의 경우 머리글이 필터 결과와 함께 표시되는데,

필터 함수의 경우는 조건에 맞는 데이터만 표시되므로 머리글을 별도로 입력해야 합니다.

 

② 필터 함수의 경우는 배열 형태로 반환하기 때문에 결과 주변에 파란색 테두리가 표시됩니다.

 

③ 고급 필터의 경우는 날짜가 제대로 표시되는데, Filter함수의 경우는 날짜가 숫자로 표시됩니다.

 

④ Filter함수의 경우는 배열 형태로 값을 반환하기 때문에 첫 번째 셀에만 수식을 입력해도 결과가 목록으로 지정한 부분이 자동으로 표시되는데,

고급 필터의 경우는 복사할 위치를 M1에서 O1이라고 지정해야 합니다.

 

다. 사용예 2 - Or 조건

i2셀의 수식 =FILTER(A2:C14,(A2:A14=E2)*(B2:B14=F2)) 에서

*만 +로 수정하면 됩니다.

 

그러면 날짜가 23/4/10이거나 지점명이 A인 데이터가 I2셀부터 7줄 표시되며, 마찬가지로 주변에 파란색 테두리가 표시됩니다.

 

라. 만족하는 값이 없을 때의 처리

i2셀의 수식에서 +를 다시 *로 수정하고, 지점명을 F2에서 지점명이 없는"D"로 수정하고 엔터키를 누르

 

#CALC! 오류가 발생합니다.

 

① if_empty 값 1

세 번째 인수로 공백인 ""을 넣으면 오류가 사라지고, 빈 셀로 바뀝니다.

 

② if_empty 값 2

공백이 아니라 "해당 조건을 만족하는 데이터가 없습니다"라고 설명을 표시하도록

수식을 =FILTER(A2:C14,(A2:A14=E2)*(B2:B14="D"),"해당 조건을 만족하는 데이터가 없습니다") 로 수정합니다.

그러면 아래와 같이 i2셀에 "해당 조건을 만족하는 데이터가 없습니다"라고 표시됩니다.

 

마. 필터 함수의 수정

필터 함수의 수식은 i2셀에 있는 것이기 때문에 i3셀 등에 결과가 표시되더라도 i3셀로 셀 포이터를 옮기면 수식이 흐릿하게 보이고,

 

수식 입력 줄을 마우스로 클릭하거나 F2키를 누르면 수식이 없으므로 빈칸으로 표시됩니다.

 

따라서, Filter함수의 수식은 왼쪽 위 셀에 있는 수식을 수정해야 합니다.

 

바. * 또는 + 대신 And, Or 함수 사용 불가능

① If함수는 가능

if함수를 사용할 때는 조건으로 And 또는 Or 함수를 사용해서

=IF(AND(A8=E2,B8=F2),C2,"")라고 입력하면 두 가지 조건을 만족하는 342가 구해지는데 숫자가 아닌 날짜 표시형식으로 보이므로 

 

표시 형식을 숫자로 바꾸면 

 

342로 표시됩니다.

 

② Filter함수는 불가능

And 함수를 이용해 * 연산자를 수정하면

=FILTER(A2:C14,AND((A2:A14=E2),(B2:B14="A")),"해당 조건을 만족하는 데이터가 없습니다")

가 되는데, 엔터 키를 누르면 #VALUE! 오류가 발생합니다.

 

이것은 두 번째 인수 include에 해당하는 AND((A2:A14=E2),(B2:B14="A"))의 결과가 첫 번째 인수인 array와 높이가 같아야 하는데,

AND((A2:A14=E2),(B2:B14="A"))를 마우스로 끌어서 범위를 선택한 후 F9키를 누르면

배열이 아니라 단일 값인 False가 나오기 때문입니다.

 

원래 수식으로 돌아가기 위해 Esc키를 누릅니다.

 

수식을 연산자 +를 이용해 Or 조건으로 수정하고 

(A2:A14=E2)+(B2:B14="A") 부분을 마우스로 끌어서 선택한 후 F9키를 누르

 

{1;0;0;1;0;0;2;1;1;1;0;0;1}라고, 배열 형식으로 값이 반환되며, 1은 True(조건에 맞음)이고, 0은 False(조건에 맞지 않음)입니다.

 

따라서, 첫 번째, 네 번째 줄의 데이터가 화면에 표시되는 것입니다.

 

이것이 And나 Or 함수를 사용할 수 없는 이유입니다.

Filter함수(완성).xlsx

0.01MB