엑셀 동적 배열 함수 FILTER와 UNIQUE 결합을 통한 자동 업데이트 명단 만들기

6월 27, 2026

FILTER와 UNIQUE 함수를 결합하면 원본 데이터가 변경될 때마다 중복 없이 자동으로 갱신되는 명단을 단일 수식으로 구현할 수 있다. 이 글은 두 동적 배열 함수의 정확한 구문과 결합 원리, 자동 업데이트를 보장하는 참조 설계 방법, 그리고 실무에서 발생하는 오류의 원인과 해결책을 2026년 6월 기준 최신 사양에 따라 체계적으로 정리한다. 핵심 결론은 명확하다. 보조 열이나 VBA, 수동 새로 고침 없이 =UNIQUE(FILTER(원본, 조건)) 단 하나의 수식만으로 조건 충족 항목의 중복 제거 명단을 영구적으로 자동화할 수 있다는 점이다.

1. 기본 개념 및 정의

동적 배열(Dynamic Array)은 하나의 셀에 입력된 단일 수식이 결과의 크기에 맞춰 인접한 여러 셀로 자동 확장되는 계산 방식이며, 이 확장 동작을 스필(Spill)이라 한다. 스필 범위의 좌측 상단 셀에만 실제 수식이 존재하고, 나머지 셀은 그 결과가 흘러넘쳐 표시되는 영역이다. 이 동적 배열 엔진은 마이크로소프트 365, 엑셀 2024, 엑셀 2021 및 웹용 엑셀에서 지원되며, 엑셀 2019 이하 버전에서는 작동하지 않는다. 구버전에서는 동일 함수명을 입력해도 인식되지 않거나 Ctrl+Shift+Enter 방식의 레거시 배열 수식으로만 처리된다.

FILTER 함수는 지정한 조건을 충족하는 행만을 원본에서 추출하여 반환한다. 구문은 =FILTER(array, include, [if_empty])로 구성된다. array는 추출 대상이 되는 원본 범위이며, include는 각 행의 포함 여부를 TRUE 또는 FALSE로 평가한 논리 배열이다. 세 번째 인수 if_empty는 조건을 충족하는 행이 하나도 없을 때 표시할 값으로, 생략하면 결과가 없을 경우 #CALC! 오류가 반환된다. include 인수는 원본의 행 수와 동일한 길이의 1과 0(또는 TRUE/FALSE) 배열이어야 하며, 1이 부여된 행만 결과에 포함된다.

UNIQUE 함수는 범위나 배열에서 중복을 제거한 고유값 목록을 반환한다. 구문은 =UNIQUE(array, [by_col], [exactly_once])이다. by_col은 비교 방향을 결정하는 인수로, 생략하거나 FALSE이면 행 단위로 비교하고 TRUE이면 열 단위로 비교한다. exactly_once는 출력 기준을 결정하며, 기본값 FALSE는 중복을 가진 값도 하나로 합쳐 모두 출력하는 반면 TRUE는 원본에 정확히 한 번만 등장한 값만 반환한다. UNIQUE는 대소문자를 구분하지 않으므로 "APPLE", "Apple", "apple"은 동일 값으로 처리된다. 다만 텍스트 "10"과 숫자 10은 자료형이 다르므로 별개의 값으로 간주된다는 점에 유의해야 한다.

두 함수의 결합은 처리 순서에 의해 의미가 결정된다. FILTER가 먼저 조건에 맞는 부분 집합을 추출하고, 그 결과를 UNIQUE가 감싸 중복을 제거하는 구조다. 즉 =UNIQUE(FILTER(원본, 조건))은 "조건을 만족하는 행들 중에서 서로 다른 값만 골라낸 명단"을 산출한다. 두 함수 모두 출력이 동적 배열이므로, 원본 데이터나 조건이 바뀌면 결과가 즉시 재계산되어 자동으로 갱신된다. 이것이 수동 고급 필터나 중복 제거 도구와 본질적으로 구별되는 지점이다. 후자는 값을 복사해 붙여넣는 정적 방식이어서 원본 변경이 결과에 반영되지 않는다.

2. 핵심 활용 방법 및 단계별 가이드

2-1. 세부 단계 1: 결합 수식의 구성과 입력

가장 기본적인 자동 업데이트 명단은 다음과 같은 절차로 구성된다.

원본 데이터를 준비한다. 예를 들어 A열에 부서명, B열에 직원 이름이 행 단위로 입력되어 있다고 가정한다.
결과를 표시할 빈 셀을 선택한다. 이때 결과가 흘러넘칠 아래쪽 공간이 충분히 비어 있어야 한다.
특정 부서에 속한 직원 중 중복 없는 명단을 얻으려면 =UNIQUE(FILTER(B2:B100, A2:A100="영업부"))를 입력한다. 내부 FILTER가 영업부 행의 이름만 추출하고, 외부 UNIQUE가 동명이인이 아닌 중복 입력을 제거한다.
결과가 없을 경우를 대비하려면 FILTER의 세 번째 인수를 지정해 =UNIQUE(FILTER(B2:B100, A2:A100="영업부", "해당 없음"))처럼 작성한다.

복수 조건을 적용할 때는 논리 배열을 연산자로 결합한다. AND 조건은 곱셈 기호(*)로, OR 조건은 덧셈 기호(+)로 표현한다. 각 조건은 1과 0의 배열을 생성하며, 곱셈은 모든 조건이 1일 때만 최종 1을 남기므로 AND 논리가 되고, 덧셈은 어느 하나라도 1이면 결과가 1 이상이 되므로 OR 논리가 된다. 예컨대 영업부이면서 직급이 대리인 인원의 고유 명단은 =UNIQUE(FILTER(B2:B100, (A2:A100="영업부")*(C2:C100="대리")))로 작성한다. 셀 참조로 조건값을 분리해 두면 해당 셀만 바꿔 명단을 즉시 전환할 수 있어 대화형 보고서 구성에 적합하다.

2-2. 세부 단계 2: 자동 업데이트를 보장하는 참조 설계와 주의사항

명단이 새로 추가되는 데이터까지 자동으로 흡수하려면 참조 범위 자체가 가변적이어야 한다. 고정된 A2:A100 형태는 100행을 넘어선 신규 데이터를 포착하지 못한다. 이를 해결하는 방법은 다음과 같다.

원본을 엑셀 표(Table)로 변환한 뒤 구조적 참조를 사용한다. 표는 행이 추가되면 범위가 자동 확장되므로 수식 수정 없이 갱신이 유지된다. 다만 동적 배열 수식 자체는 표 내부 셀에 입력할 수 없으며, 입력 시 #SPILL! 오류가 발생하므로 결과 수식은 반드시 표 바깥의 일반 범위에 두어야 한다.
전체 열 참조(A:A, B:B)를 사용한다. 이 방식은 신규 데이터를 자동 포함하나, 머리글과 빈 셀까지 평가 대상이 되어 결과에 0이나 공백이 끼어들 수 있다.
2026년 현재 사양에서는 범위 끝의 빈 영역을 자동으로 잘라내는 TRIMRANGE 함수와 마침표(.) 기반의 트림 참조 연산자가 제공된다. 이를 활용하면 전체 열 참조의 편의성과 정밀한 범위 제어를 동시에 확보할 수 있어, 보조 열이나 OFFSET 기반 이름 정의 없이도 가변 범위를 구성할 수 있다.

주의해야 할 사항도 명확하다.

빈 셀은 UNIQUE 결과에서 0으로 표시될 수 있으므로, 공백을 배제하려면 FILTER 단계에서 (B2:B100<>"") 조건을 함께 적용한다.
스필 범위 안에 이미 다른 값이 입력되어 있으면 #SPILL! 오류가 발생한다. 결과 영역을 항상 비워 두어야 한다.
다른 통합 문서를 참조하는 동적 배열은 원본 문서가 닫혀 있을 때 #REF! 오류를 반환하므로 양쪽 문서를 모두 열어 두어야 한다.

3. 실무 적용 예시 및 자주 묻는 질문(FAQ)

두 열을 결합한 고유 명단이 필요한 경우: 성과 이름을 합쳐 동명 구분이 가능한 명단을 만들려면 =UNIQUE(FILTER(A2:A100&" "&B2:B100, C2:C100="재직"))처럼 앰퍼샌드(&)로 텍스트를 연결한 뒤 필터링한다.
가나다순 정렬까지 동시에 적용하려면: SORT 함수로 한 번 더 감싸 =SORT(UNIQUE(FILTER(...)))로 구성한다. 정렬 역시 동적이므로 원본 변경 시 순서가 자동 재정렬된다.
단 한 번만 등장한 값만 필요한 경우: UNIQUE의 세 번째 인수를 TRUE로 지정해 =UNIQUE(FILTER(...), , TRUE)로 작성하면 중복 출현 항목을 제외하고 유일 등장 항목만 남긴다.
결과가 #CALC! 오류로 표시되는 경우: 조건을 충족하는 행이 없을 때 발생하므로 FILTER의 if_empty 인수를 지정해 대체 문자열을 반환하도록 한다.
함수가 #NAME? 오류로 인식되지 않는 경우: 사용 중인 버전이 동적 배열을 지원하지 않는 것이다. 마이크로소프트 365, 엑셀 2024, 엑셀 2021 중 하나로 업그레이드해야 한다.
드롭다운 목록과 연동하려면: 결과 스필 범위의 첫 셀 주소 뒤에 #를 붙인 스필 참조(예: E2#)를 데이터 유효성 검사의 원본으로 지정하면, 명단이 늘거나 줄 때 드롭다운 항목도 자동으로 따라 변한다.

[결론]

FILTER와 UNIQUE의 결합은 조건 추출과 중복 제거라는 두 단계를 하나의 수식으로 통합하여, 원본이 변할 때마다 자동으로 재계산되는 명단을 구현하는 표준 기법이다. 핵심은 세 가지로 요약된다. 첫째, =UNIQUE(FILTER(원본, 조건))이 기본 골격이며 AND는 곱셈, OR는 덧셈으로 조건을 확장한다. 둘째, 진정한 자동 업데이트를 위해서는 엑셀 표의 구조적 참조나 TRIMRANGE 및 트림 참조 연산자로 가변 범위를 설계해야 한다. 셋째, #SPILL!은 결과 영역 비우기, #CALC!는 if_empty 지정, #NAME?은 버전 업그레이드로 각각 해결한다. 이 기법은 보조 열과 VBA, 수동 새로 고침을 모두 제거하여 유지 보수 비용을 최소화하는 동시에 데이터 정합성을 보장한다.

이 블로그 검색

인포데이터랩