기본 콘텐츠로 건너뛰기

[엑셀]FILTER 함수 원리 — 엑셀 안에 숨어 있는 WHERE절

 매장별 판매 데이터 수천 행 중에서 "서울 지역이면서 매출이 100만원 이상인 행"만 뽑아 별도 표로 만들어야 하는 상황이라고 해보자. 예전 같으면 자동 필터를 걸고 눈으로 복사하거나, 고급 필터 대화상자와 씨름해야 했다. 지금은 FILTER 함수 하나면 끝난다. 그런데 이 함수의 동작 방식을 데이터베이스를 다뤄본 사람이라면 꽤 익숙하게 느낄 것이다. 바로 SQL의 WHERE절과 개념적으로 닮아 있기 때문이다.

FILTER의 기본 구조

=FILTER(A2:D1000, B2:B1000="서울")

FILTER는 두 개의 핵심 인수를 받는다. "걸러낸 결과로 보여줄 범위"와 "그 판단 기준이 되는 조건"이다. 이 수식은 B열이 "서울"인 행들만 골라, A부터 D열까지의 전체 행 데이터를 그대로 스필(앞선 글에서 다룬 동적 배열)로 펼쳐 보여준다. SUMIF가 조건에 맞는 값을 '더하는' 함수였다면, FILTER는 조건에 맞는 '행 자체를 그대로 가져오는' 함수라는 점이 가장 큰 차이다.

WHERE절과의 개념적 유사성

SQL에 익숙하다면 아래 두 표현이 사실상 같은 일을 한다는 것을 바로 알아챌 수 있다.

SQL엑셀 FILTER
SELECT * FROM sales WHERE region = '서울'=FILTER(A2:D1000, B2:B1000="서울")
WHERE region = '서울' AND amount >= 1000000=FILTER(A2:D1000, (B2:B1000="서울")*(C2:C1000>=1000000))

두 방식 모두 "전체 데이터 중 특정 조건을 만족하는 행만 선택한다"는 동일한 개념 위에 서 있다. 다만 SQL의 WHERE는 조건을 하나의 불리언 식으로 서술하는 반면, 엑셀의 FILTER는 앞서 SUMPRODUCT에서 다룬 것처럼 조건을 0과 1의 배열로 만들어 곱하는 방식으로 AND를 표현한다는 점이 실행 원리상의 차이다.

다중 조건 필터링

조건이 여러 개일 때는 SUMPRODUCT와 똑같이 곱셈으로 AND를, 덧셈으로 OR을 표현한다.

=FILTER(A2:D1000, (B2:B1000="서울")*(C2:C1000>=1000000))
=FILTER(A2:D1000, (B2:B1000="서울")+(B2:B1000="부산"))

첫 번째는 "서울이면서 매출 100만원 이상"이라는 AND 조건이고, 두 번째는 "서울이거나 부산"이라는 OR 조건이다. 배열수식과 SUMPRODUCT에서 다뤘던 0/1 배열의 곱셈·덧셈 원리가 FILTER에서도 그대로 재사용되는 셈이다.

자주 하는 실수

조건을 만족하는 행이 하나도 없을 경우 FILTER는 기본적으로 #CALC! 오류를 반환한다. 이를 방지하려면 세 번째 인수에 빈 결과일 때 보여줄 대체값을 지정해주면 된다.

=FILTER(A2:D1000, B2:B1000="제주", "해당 데이터 없음")

또한 조건 범위(B2:B1000)와 결과 범위(A2:D1000)의 행 개수가 반드시 일치해야 한다는 점도 SUMIF·SUMIFS와 동일하다. 행 개수가 어긋나면 #VALUE! 오류가 발생한다.

Q. FILTER 결과에 원하는 열만 골라 담을 수 있나요?

가능하다. 첫 번째 인수의 범위를 A2:D1000 전체가 아니라 필요한 열(예: A2:A1000, C2:C1000)만 참조하도록 조정하면 된다.

Q. FILTER와 자동 필터 기능은 무엇이 다른가요?

자동 필터는 화면에 보이는 행만 숨기거나 보여주는 '표시 방식'의 변화지만, FILTER 함수는 조건에 맞는 데이터를 실제로 다른 위치에 새로 뽑아내는 계산 결과라는 점이 본질적으로 다르다.

Q. FILTER로 뽑은 결과를 다시 정렬할 수 있나요?

가능하다. FILTER의 결과를 SORT 함수로 한 번 더 감싸면 조건에 맞는 데이터를 걸러낸 뒤 원하는 기준으로 정렬까지 한 번에 처리할 수 있다.

마무리

FILTER는 "전체 데이터에서 조건을 만족하는 행만 그대로 가져온다"는 점에서 데이터베이스의 WHERE절과 같은 문제를 풀고 있지만, 그 조건을 표현하는 방식은 엑셀 특유의 0/1 배열 연산에 뿌리를 두고 있다. 다음 글에서는 이렇게 걸러낸 데이터에서 다시 중복을 제거하는 UNIQUE 함수가, 수학의 집합(set) 개념과 어떻게 연결되는지 살펴본다.

라벨:

이 블로그의 인기 게시물

[엑셀]엑셀 날짜는 사실 숫자다 — 1900년부터 세어온 일련번호의 정체

 날짜가 적힌 셀에 그냥 1을 더하면 무슨 일이 벌어질까? 신기하게도 결과는 정확히 '다음 날 날짜'가 된다. 문자처럼 보이는 날짜에 어떻게 숫자를 더했는데 날짜가 하루 늘어날 수 있을까. 답은 간단하다. 엑셀에서 날짜는 애초에 문자가 아니라 숫자이기 때문이다. 날짜가 숫자로 저장된다는 사실 확인하기 날짜가 입력된 셀의 서식을 '일반'으로 바꿔보면 그 정체가 바로 드러난다. 2026-01-15라고 표시되던 셀이 갑자기 46037 같은 숫자로 바뀐다. 이 숫자가 바로 엑셀이 내부적으로 이 날짜를 저장하고 있는 실제 값, 즉 시리얼 넘버(일련번호)다. 화면에 "2026-01-15"라고 보이는 것은 앞서 TEXT 함수 편에서 다룬 것처럼, 이 숫자 위에 날짜 서식이라는 표시 규칙이 덧씌워진 결과일 뿐이다. 1900년을 기준으로 세는 시리얼 넘버 시스템 엑셀은(윈도우 버전 기준) 1900년 1월 1일을 시리얼 넘버 1로 정하고, 그날부터 하루가 지날 때마다 숫자를 1씩 늘려가는 방식으로 날짜를 표현한다. 46037이라는 숫자는 곧 "1900년 1월 1일로부터 46,037번째 되는 날"이라는 뜻이다. 날짜에 숫자를 더하거나 빼는 계산이 자연스럽게 되는 이유가 바로 여기에 있다. 날짜끼리의 덧셈·뺄셈은 사실 이 일련번호끼리의 평범한 산술 연산일 뿐이다. =DATE(2026,1,15) + 30 이 수식은 2026년 1월 15일의 시리얼 넘버에 30을 더한 것과 같으므로, 결과는 정확히 30일 뒤의 날짜인 2026년 2월 14일이 된다. 시간은 어떻게 표현될까 — 소수점 이하가 하루의 몇 분의 몇인지 날짜가 정수로 표현된다면, 시간은 그 정수의 소수점 이하 부분으로 표현된다. 하루 24시간을 1로 놓았을 때, 낮 12시는 하루의 정확히 절반이므로 0.5가 된다. 예를 들어 46037.5라는 값은 "2026년 1월 15일 낮 12시"를 의미한다. 날짜와 시간이 함께 입력된 셀을 다룰 때 정수 부...
자세한 내용 보기

[엑셀]배열수식이란 무엇인가 — 하나의 셀에 여러 계산이 담기는 원리 스칼라 연산과 배열 연산의 차이

 셀 하나를 클릭했는데 그 안에서 수백 번의 계산이 동시에 일어나고 있다면 믿어지는가. 앞선 글에서 다룬 SUMPRODUCT나 SUMIF의 조건 판정이 바로 그런 경우였다. 겉으로는 셀 하나에 결과값 하나만 보이지만, 그 이면에서는 범위 전체를 대상으로 한 계산이 통째로 진행되고 있었다. 이 글에서는 이 현상의 정체, 즉 배열수식이라는 개념을 원리 차원에서 짚어본다. 스칼라 연산과 배열 연산의 차이 평소 우리가 쓰는 대부분의 수식은 '스칼라 연산'이다. =A2+B2 처럼 셀 하나와 셀 하나를 계산해서 값 하나를 내놓는 방식이다. 입력도 하나, 출력도 하나다. 반면 '배열 연산'은 여러 개의 값을 하나의 묶음(배열)으로 취급해서 한꺼번에 계산한다. =A2:A10+B2:B10 이라고 쓰면, 이는 A2+B2, A3+B3, ... A10+B10이라는 아홉 번의 계산을 동시에 수행하라는 뜻이 된다. 입력은 여러 개(범위 대 범위)지만, 이 계산 결과를 최종적으로 어떻게 압축해서 보여줄지는 그 다음 문제다. 결과가 여러 개인데 셀은 하나뿐이라면? 여기서 배열수식의 진짜 개념이 드러난다. 아홉 번의 계산 결과 아홉 개의 값이 나왔는데, 이를 담을 셀이 하나뿐이라면 엑셀은 어떻게 해야 할까. 과거 방식에서는 이 아홉 개의 값을 SUM이나 COUNT 같은 함수로 다시 하나로 압축해서 보여주는 방식을 썼다. 예를 들어 {=SUM(A2:A10*B2:B10)} 이라는 수식은 A와 B 각 행을 곱한 아홉 개의 결과를 배열로 만든 뒤, 그 아홉 개를 SUM으로 다시 하나로 합쳐 셀 하나에 담아내는 구조다. 여기서 중괄호 {} 는 사용자가 직접 입력하는 기호가 아니라, 예전 엑셀에서 Ctrl+Shift+Enter를 눌러 "이건 배열수식입니다"라고 선언했을 때 엑셀이 자동으로 붙여주던 표시였다. 왜 예전에는 이런 특별한 입력이 필요했나 일반 함수는 셀 하나 대 셀 하나로 계산하도록 설계되어 있었기 때문에, 여러 값을 배열째로 넘기려면 엑셀에게...
자세한 내용 보기

[엑셀]COUNTIF/COUNTIFS — 카운팅과 조건 매칭의 관계 SUMIF와의 구조적 유사성과 차이

 출석부를 관리하다 보면 "결석이 3회 이상인 학생 수"나 "이번 달 지각 횟수"처럼 특정 조건을 만족하는 항목이 '몇 개'인지 세야 하는 순간이 자주 온다. 앞선 글에서 다룬 SUMIF가 조건에 맞는 값들을 '더하는' 함수였다면, 이번에 다룰 COUNTIF는 조건에 맞는 항목이 '몇 개'인지를 세는 함수다. 이름도 구조도 SUMIF와 매우 닮았지만, 자세히 들여다보면 미묘하지만 중요한 차이가 하나 숨어 있다. COUNTIF의 기본 구조 =COUNTIF(A2:A100, "결석") COUNTIF는 조건을 검사할 범위와 조건, 이렇게 두 개의 인수만 받는다. A2:A100 범위에서 "결석"이라는 값과 일치하는 셀이 몇 개인지를 세어 그 개수를 반환한다. SUMIF와 COUNTIF의 구조적 유사성 지난 글에서 SUMIF의 원리를 "조건 범위를 참/거짓 배열로 바꾸고, 그 배열을 합계 범위 위에 겹쳐서 참인 위치만 더한다"고 설명했다. COUNTIF도 앞부분까지는 완전히 동일하다. 조건 범위를 검사해서 참/거짓 배열을 만드는 과정까지는 똑같다. 차이는 그다음이다. SUMIF는 이 배열을 다른 범위(합계 범위) 에 겹쳐서 값을 더하지만, COUNTIF는 겹칠 다른 범위 자체가 필요 없다. 그냥 참/거짓 배열에서 TRUE의 개수만 세면 끝나기 때문이다. 즉 "무엇을 더할지"를 지정할 필요가 없으므로 인수가 하나 줄어드는 것이다. 이렇게 보면 COUNTIF(범위, 조건)는 개념적으로 SUMIF(범위, 조건, 참거짓배열자체) 와 비슷한 일을 하는 셈이다. COUNTIFS — 여러 조건의 동시 만족 =COUNTIFS(A2:A100, "결석", B2:B100, ">=3") COUNTIFS 역시 SUMIFS와 동일한 방식으로 확장된다. 각 조건 범위마다 참/거짓 배열을 만들고, 이 배열들을...
자세한 내용 보기