엑셀 FILTER 함수 사용법: 여러 조건, AND와 OR

=FILTER(A2:C7,B2:B7="North")는 A2:C7에서 B열의 지역이 North인 행을 모두 반환합니다. 셀 하나에 입력하면 일치하는 행이 아래와 오른쪽 셀로 분산됩니다. B열의 지역을 North로 바꾸거나 North를 South로 바꾸면 목록이 갱신됩니다.

수식초기화

E2fx

=FILTER(A2:C7,B2:B7="North")

	A	B	C	D	E	F	G
1	Name	Region	Sales		Name	Region	Sales
2	Ann	North	120		Ann	North	120
3	Ben	South	80		Cara	North	200
4	Cara	North	200		Finn	North	60
5	Dan	East	150
6	Eve	South	95
7	Finn	North	60

셀을 클릭하면 수식이 보입니다. 숫자나 수식을 바꾸면 시트가 다시 계산됩니다.

수식은 E2에만 있습니다. E:G의 다른 채워진 셀은 분산된 결과입니다. F3을 클릭하면 E2의 수식에 속한 셀임을 알 수 있습니다. 그 영역에 무언가 입력되어 있으면 FILTER는 행 대신 #SPILL!을 표시합니다(#SPILL! 오류 참고).

FILTER 구문

=FILTER(array, include, [if_empty])

• array는 돌려받을 것입니다. 열 하나, 여러 열, 또는 표 전체입니다.
• include는 B2:B7="North"처럼 array의 행마다 TRUE나 FALSE를 하나씩 내는 조건입니다. 행 수가 array와 정확히 같아야 합니다. (열을 필터링하려면 열마다 값 하나를 주세요.)
• if_empty는 일치하는 행이 없을 때 표시할 내용입니다. 이 인수가 없으면 빈 결과는 #CALC! 오류가 됩니다.

FILTER는 Excel 2021, Excel 2024 또는 Microsoft 365가 필요합니다. Excel 2019 이하에서는 #NAME?이 표시되며, 그런 버전에서는 데이터 탭의 필터 버튼으로 필터링합니다. Google Sheets에도 FILTER가 있으며, 거기서는 각 조건을 별도의 인수로 줄 수도 있습니다.

텍스트 비교는 대소문자를 구분하지 않습니다: B2:B7="north"도 North와 일치합니다. FILTER는 행을 원래 순서대로 유지하며, 결과를 정렬하는 것은 아래에서 보여 드리는 별도의 단계입니다.

셀 값으로 필터링하기

수식에 "North"를 직접 쓰면 매번 수식을 고쳐야 합니다. 값을 셀에 넣고 그 셀과 비교하세요. F1에서 다른 지역을 고르면 결과가 따라옵니다:

수식초기화

E3fx

=FILTER(A2:C7,B2:B7=F1,"No sales")

	A	B	C	D	E	F	G
1	Name	Region	Sales		Region	North▾
2	Ann	North	120
3	Ben	South	80		Ann	North	120
4	Cara	North	200		Cara	North	200
5	Dan	East	150		Finn	North	60
6	Eve	South	95
7	Finn	North	60

셀을 클릭하면 수식이 보입니다. 숫자나 수식을 바꾸면 시트가 다시 계산됩니다.

West에는 행이 없으므로 고르면 if_empty 텍스트인 No sales가 표시됩니다.

숫자도 같은 방식입니다. F1에 100이 있을 때 C2:C7>=F1은 매출이 100 이상인 행을 모두 남기고, C2:C7>F1은 100 초과로 만듭니다.

여러 조건을 쓰는 FILTER (AND)

두 조건이 모두 참일 때만 행을 남기려면 조건을 곱합니다. 다음은 매출이 100을 넘는 North 행을 반환합니다:

수식초기화

E2fx

=FILTER(A2:C7,(B2:B7="North")*(C2:C7>100))

	A	B	C	D	E	F	G
1	Name	Region	Sales		Name	Region	Sales
2	Ann	North	120		Ann	North	120
3	Ben	South	80		Cara	North	200
4	Cara	North	200
5	Dan	East	150
6	Eve	South	95
7	Finn	North	60

셀을 클릭하면 수식이 보입니다. 숫자나 수식을 바꾸면 시트가 다시 계산됩니다.

Ann(120)과 Cara(200)가 통과합니다. Finn은 North이지만 60은 100을 넘지 않으므로 빠집니다.

곱하는 이유는 이렇습니다. 각 조건은 TRUE와 FALSE로 된 열이고, 계산에서 TRUE는 1, FALSE는 0으로 셉니다. 모든 인수가 1일 때만 행이 1이 되므로 *가 AND처럼 동작합니다. 조건마다 괄호가 필요하며 원하는 만큼 이을 수 있습니다: (B2:B7="North")*(C2:C7>100)*(C2:C7<500).

여기서는 AND()가 동작하지 않습니다. AND(B2:B7="North",C2:C7>100)은 범위 전체를 행마다 하나씩이 아니라 TRUE나 FALSE 하나로 줄여 버리므로 FILTER가 잘못된 모양을 받습니다.

OR를 쓰는 FILTER

조건 중 하나 이상이 참일 때 행을 남기려면 조건을 더합니다. 다음은 North와 East 행을 반환합니다:

수식초기화

E2fx

=FILTER(A2:C7,(B2:B7="North")+(B2:B7="East"))

	A	B	C	D	E	F	G
1	Name	Region	Sales		Name	Region	Sales
2	Ann	North	120		Ann	North	120
3	Ben	South	80		Cara	North	200
4	Cara	North	200		Dan	East	150
5	Dan	East	150		Finn	North	60
6	Eve	South	95
7	Finn	North	60

셀을 클릭하면 수식이 보입니다. 숫자나 수식을 바꾸면 시트가 다시 계산됩니다.

두 조건을 모두 만족하는 행은 합이 2가 되고, FILTER는 결과가 0이 아닌 행을 모두 남기므로 합이 OR처럼 동작합니다. 둘을 섞을 수도 있습니다: ((B2:B7="North")+(B2:B7="East"))*(C2:C7>100)은 (North 또는 East)이면서 100 초과라는 뜻입니다. 여기서는 Ann, Cara, Dan을 반환합니다.

일치하는 것이 없으면 FILTER는 #CALC!를 반환합니다

통과하는 행이 없으면 FILTER는 반환할 것이 없습니다. 세 번째 인수가 없으면 #CALC! 오류가 되고, 있으면 직접 정한 텍스트가 나옵니다:

수식초기화

E2fx

=FILTER(A2:A7,B2:B7="West")

	A	B	C	D	E	F
1	Name	Region	Sales		No if_empty	With if_empty
2	Ann	North	120		#CALC!	No match
3	Ben	South	80
4	Cara	North	200
5	Dan	East	150
6	Eve	South	95
7	Finn	North	60

#CALC! 계산 결과가 없습니다. 예를 들어 아무것도 찾지 못한 FILTER입니다.

E2는 #CALC!를, F2는 No match를 표시합니다. B3을 South에서 West로 바꾸면 두 수식 모두 Ben을 반환합니다. 아무것도 표시하지 않으려면 빈 문자열을 쓰세요: =FILTER(A2:A7,B2:B7="West","").

이 시트는 열 하나만 필터링하는 방법도 보여 줍니다. array가 A2:A7이므로 이름만 돌아옵니다. 표의 열 중 일부만 가져오려면 결과를 CHOOSECOLS로 감싸세요: =CHOOSECOLS(FILTER(A2:C7,B2:B7="North"),1,3)은 지역을 빼고 이름과 매출을 반환합니다. CHOOSECOLS는 Microsoft 365나 Excel 2024가 필요합니다.

FILTER 결과 정렬하기

FILTER는 표에 나오는 순서대로 행을 반환합니다. 결과를 정렬하려면 SORT로 감싸세요. 여기서는 North 행을 매출이 큰 순서로 정렬합니다. 3은 정렬 기준이 되는 결과의 열이고, -1은 내림차순을 뜻합니다.

수식초기화

E2fx

=SORT(FILTER(A2:C7,B2:B7="North"),3,-1)

	A	B	C	D	E	F	G
1	Name	Region	Sales		Name	Region	Sales
2	Ann	North	120		Cara	North	200
3	Ben	South	80		Ann	North	120
4	Cara	North	200		Finn	North	60
5	Dan	East	150
6	Eve	South	95
7	Finn	North	60

셀을 클릭하면 수식이 보입니다. 숫자나 수식을 바꾸면 시트가 다시 계산됩니다.

Cara(200)가 먼저 오고, 그다음 Ann(120)과 Finn(60)이 옵니다. 위쪽 행만 반환하려면 TAKE로 한 번 더 감싸세요: =TAKE(SORT(FILTER(A2:C7,B2:B7="North"),3,-1),2)는 처음 두 행을 남깁니다(TAKE는 Microsoft 365나 Excel 2024가 필요합니다). 다른 정렬 옵션은 SORT와 SORTBY에서 다룹니다.

텍스트가 포함된 행 FILTER하기

FILTER에는 와일드카드가 없으므로 B2:B7="*th*"는 텍스트 *th* 그 자체를 찾습니다. 이름에 어떤 텍스트가 들어 있는 행을 남기려면 SEARCH로 각 셀을 검사하세요. SEARCH는 텍스트를 찾으면 위치를, 찾지 못하면 오류를 반환하므로 ISNUMBER로 감쌉니다:

수식초기화

E2fx

=FILTER(A2:C7,ISNUMBER(SEARCH("an",A2:A7)))

	A	B	C	D	E	F	G
1	Name	Region	Sales		Name	Region	Sales
2	Ann	North	120		Ann	North	120
3	Ben	South	80		Dan	East	150
4	Cara	North	200
5	Dan	East	150
6	Eve	South	95
7	Finn	North	60

셀을 클릭하면 수식이 보입니다. 숫자나 수식을 바꾸면 시트가 다시 계산됩니다.

Ann과 Dan을 반환합니다. SEARCH는 대소문자를 무시하므로 "an"이 Ann의 An과도 일치합니다. 대소문자를 구분하려면 SEARCH 대신 FIND를 쓰세요.

연습: 조건이 두 개인 FILTER

수식초기화

E2fx

	A	B	C	D	E	F	G
1	Name	Region	Sales		Name	Region	Sales
2	Ann	North	120
3	Ben	South	80
4	Cara	North	200
5	Dan	East	150
6	Eve	South	95
7	Finn	North	60

셀을 클릭하면 수식이 보입니다. 숫자나 수식을 바꾸면 시트가 다시 계산됩니다.

직접 해 보세요: E2에서 매출이 85를 넘는 South 담당자의 행(세 열 모두)을 반환하세요.

확인정답 보기

연습: 셀 값으로 FILTER하고 대체 값 표시하기

수식초기화

F3fx

	A	B	C	D	E	F
1	Name	Region	Sales		Region	North
2	Ann	North	120
3	Ben	South	80		Names
4	Cara	North	200
5	Dan	East	150
6	Eve	South	95
7	Finn	North	60

셀을 클릭하면 수식이 보입니다. 숫자나 수식을 바꾸면 시트가 다시 계산됩니다.

직접 해 보세요: F3에서 F1에 입력한 지역의 담당자 이름(A열만)을 나열하세요. 없으면 None을 표시합니다.

확인정답 보기

FILTER의 흔한 실수

• 높이가 다른 범위. =FILTER(A2:C7,B2:B6="North")는 6행짜리 표에 대해 5행만 검사하므로 엑셀은 #VALUE!를 반환합니다. include가 array와 같은 행에서 시작하고 끝나게 하세요.
• 원본이 빈 곳에 나오는 0. FILTER는 array의 빈 셀에 대해 0을 반환합니다. 필터링하기 전에 빈 셀을 빈 텍스트로 바꾸세요: =FILTER(IF(A2:C7="","",A2:C7),B2:B7="North").
• 열 전체 참조. =FILTER(A:C,B:B="North")도 동작하지만, 수식이 A~C열 안에 있으면 자기 자신을 참조하게 됩니다. 결과를 표 옆에 두거나 A2:C1000처럼 고정된 범위를 쓰세요.
• 숫자를 따옴표로 감싸기. C2:C7>"100"은 숫자를 텍스트와 비교하므로 아무것도 남지 않습니다. C2:C7>100으로 쓰세요.
• 필터 버튼처럼 동작하리라는 기대. FILTER는 일치하는 행을 새 위치에 복사하고 표는 그대로 둡니다. 표 자체에서 행을 숨기려면 데이터 > 필터를 쓰세요.