Computer >> 컴퓨터 >  >> 소프트웨어 >> Office

엑셀 동적 배열 함수 완벽 가이드: FILTER, UNIQUE, SORT로 보고서 자동화하는 5가지 방법

엑셀 동적 배열 함수 완벽 가이드: FILTER, UNIQUE, SORT로 보고서 자동화하는 5가지 방법

동적 배열(Dynamic Array) 함수는 엑셀에서 가장 강력하고 유용한 기능 중 하나입니다. 더 이상 수백 개의 행에 복잡한 수식을 일일이 복사해 내릴 필요가 없습니다. 수식 하나만 입력하면 결과가 필요한 만큼 자동으로 인접 셀에 '흘러 넘쳐(spill)' 채워집니다. 데이터가 변경되면 결과도 실시간으로 갱신되므로, 보고서와 요약표를 더 효율적으로 만들고 오류 발생 가능성도 크게 줄일 수 있습니다.

이번 튜토리얼에서는 FILTER, UNIQUE, SORT 등 동적 배열 함수가 업무 방식을 어떻게 바꿔주는지 5가지 방법으로 소개합니다.

엑셀의 동적 배열과 스필(Spill) 기능 이해하기

동적 배열: 한 셀에 수식을 입력하면 엑셀이 자동으로 결과를 인접 셀에 채워 넣습니다(스필). 결과 크기가 달라지면(행이 늘거나 줄면) 스필 범위도 자동으로 확장·축소됩니다. 스필된 범위는 다음과 같은 특징으로 식별할 수 있습니다.

  • 수식은 왼쪽 맨 위 셀에만 존재합니다.
  • 나머지 셀에는 옅은 테두리가 표시되며, 클릭하면 회색으로 흐려진 수식을 확인할 수 있습니다.
  • A2#처럼 해시(#) 기호로 전체 스필 범위를 참조할 수 있습니다.

동적 배열은 Microsoft 365(Excel for Microsoft 365), Excel 2021 이상 버전에서 사용할 수 있습니다.

1. UNIQUE로 중복 없는 고유 목록 생성하기

동적 배열 이전에는 중복 제거를 위해 '중복된 항목 제거' 기능이나 복잡한 수식을 사용해야 했습니다. UNIQUE 함수는 스필되는 고유 목록을 만들어 빠른 요약에 딱 좋으며, 드롭다운, 유효성 검사 목록, 피벗 없는 대시보드에 특히 유용합니다.
고유 지역 목록 만들기:

  • 아무 셀이나 선택한 뒤 아래 수식을 입력합니다.

이 수식은 고유한 제품 목록을 스필 형태로 출력합니다.
엑셀 동적 배열 함수 완벽 가이드: FILTER, UNIQUE, SORT로 보고서 자동화하는 5가지 방법
고유 조합 추출:
지역(Region)과 영업 사원(Salesperson)의 고유 조합을 얻으려면:

두 열로 구성된 스필 결과가 반환되며, 모든 고유 조합을 보여줍니다.
엑셀 동적 배열 함수 완벽 가이드: FILTER, UNIQUE, SORT로 보고서 자동화하는 5가지 방법
고유 주문 건수 세기:

  • UNIQUE와 COUNTA를 결합하면 요약 통계를 만들 수 있습니다.

이 수식은 고유 주문의 총 개수를 계산합니다. 데이터 세트에 새 행을 추가하면 목록이 자동으로 갱신됩니다.
엑셀 동적 배열 함수 완벽 가이드: FILTER, UNIQUE, SORT로 보고서 자동화하는 5가지 방법
딱 한 번만 나타나는 값 찾기:
단 한 번만 등장하는 값을 찾을 수도 있습니다.

세 번째 인수(TRUE)는 반복되지 않는 값만 반환합니다.
엑셀 동적 배열 함수 완벽 가이드: FILTER, UNIQUE, SORT로 보고서 자동화하는 5가지 방법
UNIQUE를 데이터 유효성 검사(드롭다운)에 활용하기:

  1. 드롭다운을 만들고 싶은 셀을 선택합니다.
  2. 데이터 탭 >> 데이터 유효성 검사를 선택합니다.
  3. 제한 대상을 목록으로 설정합니다.
  4. 원본에 아래 수식을 입력합니다.

엑셀 동적 배열 함수 완벽 가이드: FILTER, UNIQUE, SORT로 보고서 자동화하는 5가지 방법
이제 드롭다운은 데이터에 기반한 최신 고유 지역 목록을 항상 표시합니다.
UNIQUE로 그룹화 요약표 만들기:
UNIQUE를 SUMIF와 함께 사용하면 동적인 그룹화 요약표를 만들 수 있습니다.
각 지역의 총 매출을 계산해 보겠습니다:

=SUMIF(B2:B61, I15#, G2:G61)

이 수식은 스필된 전체 지역 목록(I15#)을 참조합니다. SUMIF가 지역별 총 매출을 반환하며, Sales 시트에 행을 추가하거나 수정하면 지역 목록과 합계가 자동으로 조정됩니다.
엑셀 동적 배열 함수 완벽 가이드: FILTER, UNIQUE, SORT로 보고서 자동화하는 5가지 방법
이제 지역을 변경하면 선택에 따라 총 매출(Total Revenue)이 자동으로 업데이트됩니다.

2. FILTER로 동적 보고서를 위한 자동 데이터 필터링

기존 필터링 방식은 수동 필터나 복잡한 수식에 의존했습니다. FILTER() 함수는 동적 배열에서 가장 강력한 도구 중 하나입니다. 조건에 맞는 행만 추출하여 표 형태로 스필해 주기 때문에, 이 함수 하나만으로 즉시 업데이트되는 보고서를 만들 수 있습니다.
지역별 매출 필터링:

  • FILTER 함수의 동작을 확인하려면 지역 선택용 드롭다운을 활용해 보세요.
=FILTER(A2:G61, B2:B61="East")
  • 더 동적으로 만들려면 드롭다운이 있는 조건 셀을 참조합니다.
=FILTER(A2:G61, B2:B61=I4)

이 수식은 지역이 'East'인 모든 행을 스필합니다. 데이터를 추가하거나 삭제하면 미니 보고서가 자동으로 늘어나거나 줄어듭니다. I4를 'North'로 바꾸면 보고서가 자동으로 갱신됩니다. VBA도, 수동 새로 고침도 필요 없습니다.
엑셀 동적 배열 함수 완벽 가이드: FILTER, UNIQUE, SORT로 보고서 자동화하는 5가지 방법
이렇게 하면 데이터의 정적 복사본이 필요 없어지고, 보고서는 항상 원본 데이터를 그대로 반영합니다.
복수 조건 필터링:
East 지역이면서 금액이 $1,000 초과인 행만 필터링하려면:

=FILTER(A2:G61, (B2:B61="East")*(G2:G61>1000), "No matches")

별표(*)는 AND 조건으로 작동하며, 더하기(+)는 OR 조건에 사용할 수 있습니다.
드롭다운에서 조건을 선택하는 인터랙티브 대시보드도 손쉽게 만들 수 있습니다.
엑셀 동적 배열 함수 완벽 가이드: FILTER, UNIQUE, SORT로 보고서 자동화하는 5가지 방법

3. SORT()와 SORTBY()로 자동 정렬 목록 만들기

예전에는 정렬을 위해 데이터를 복사하거나 테이블 기능을 사용해야 했습니다. SORT 함수는 동적으로 스필되는 정렬 뷰를 만들어 주며, 데이터 세트에 추가·삭제·수정이 생길 때마다 자동으로 재정렬합니다.
자동 정렬되는 매출 리더보드:

이 수식은 전체 데이터 범위를 7번째 열(금액) 기준으로 내림차순(-1) 정렬합니다. 원본 데이터는 그대로 유지되며, 새로운 최고 매출 기록을 추가하면 자동으로 올바른 위치에 나타납니다.
엑셀 동적 배열 함수 완벽 가이드: FILTER, UNIQUE, SORT로 보고서 자동화하는 5가지 방법
다단계 정렬:
영업 사원 순으로 먼저 정렬한 뒤, 금액순으로 정렬하려면:

=SORT(A2:G61, {3,7}, {1,-1})

중괄호는 배열을 만듭니다. 즉, 3번째 열 기준 오름차순 정렬 후, 7번째 열 기준 내림차순으로 정렬합니다.
엑셀 동적 배열 함수 완벽 가이드: FILTER, UNIQUE, SORT로 보고서 자동화하는 5가지 방법
다른 범위 기준으로 정렬:
SORTBY 함수는 한 범위를 다른 범위의 값에 따라 정렬할 수 있게 해 줍니다.
모든 열을 유지한 채 전체 범위를 영업 사원 이름순으로 정렬하려면:

=SORTBY(A2:G61, C2:C61, 1)

4. FILTER + UNIQUE 결합으로 맞춤형 고유 요약 만들기

고급 요약이 필요하다면 여러 함수를 조합할 수 있습니다. 먼저 필터링한 후 고유값만 추출하여, 깔끔하고 자동 업데이트되는 목록을 스필합니다. 함수들을 조합하면 스스로 관리되는 보고서를 만들 수 있습니다.

  • 셀을 선택하고 아래 수식을 입력합니다.
=UNIQUE(FILTER(D2:D61, B2:B61="North"))

North 지역의 제품을 먼저 필터링한 뒤, 고유값만 스필합니다.

  • 다음으로 SORT 함수를 추가해 요약을 정렬합니다.
=SORT(UNIQUE(FILTER(D2:D61, B2:B61="North")))

이제 수식은 정렬된 고유 제품 목록을 스필합니다.
엑셀 동적 배열 함수 완벽 가이드: FILTER, UNIQUE, SORT로 보고서 자동화하는 5가지 방법
이 조합은 {=INDEX(…)} 같은 번거로운 배열 수식을 대체합니다. 데이터나 조건을 바꾸면 결과가 매끄럽게 갱신되므로, 지역별 제품 재고 보고서 등에 활용하기 좋습니다.

5. FILTER, UNIQUE, SORT를 결합한 동적 요약 페이지 만들기

마지막으로 이 함수들을 모두 결합하여, 몇 개의 조건 셀만으로 업데이트되는 미니 요약/리포팅 페이지를 만들어 보겠습니다.
지역 단위 대시보드를 구성합니다. 지역 드롭다운(UNIQUE 기반), 필터링된 지역별 목록(FILTER), 지역별 인기 제품 목록(FILTER + SORT)입니다.

1단계: UNIQUE로 지역 드롭다운 만들기

고유 지역 목록을 생성한 뒤, 이를 활용해 드롭다운을 만듭니다.

2단계: 지역별 판매 상세 정보

=FILTER(SalesData!A2:G61, SalesData!B2:B61=B4, "No sales in this region")

이 수식은 지역을 기준으로 판매 데이터를 필터링합니다. 드롭다운에서 지역을 바꾸면 판매 표가 자동으로 업데이트됩니다.
엑셀 동적 배열 함수 완벽 가이드: FILTER, UNIQUE, SORT로 보고서 자동화하는 5가지 방법

3단계: 선택한 지역의 인기 제품

선택한 지역에서 어떤 제품이 가장 많이 팔리는지 파악합니다.

  • Product(제품)와 Total Revenue(총 매출) 헤더가 있는 작은 표를 만듭니다.
  • L4 셀에 선택한 지역에서 판매된 고유 제품 목록을 가져옵니다:
=UNIQUE(FILTER(SalesData!D2:D61, SalesData!B2:B61=B4))

해당 지역의 제품 목록이 스필됩니다.

  • M4 셀에 해당 지역의 제품별 총 매출을 계산합니다:
=SUMIFS(SalesData!G2:G61, SalesData!B2:B61, B4, SalesData!D2:D61, L4#)

L4#의 각 제품에 대응하는 총 매출 목록이 스필 형태로 반환됩니다.
엑셀 동적 배열 함수 완벽 가이드: FILTER, UNIQUE, SORT로 보고서 자동화하는 5가지 방법

  • 매출 기준(내림차순)으로 정렬해서 보려면, 두 개의 스필 열을 함께 정렬합니다:
=SORT(CHOOSE({1,2}, L4#, M4#), 2, -1)

여기서 CHOOSE({1,2}, L4#, M4#)는 두 열짜리 배열(제품, 총 매출)을 만듭니다. 2는 '2번째 열(총 매출) 기준 정렬'을, -1은 내림차순을 의미합니다.
엑셀 동적 배열 함수 완벽 가이드: FILTER, UNIQUE, SORT로 보고서 자동화하는 5가지 방법
[선택 지역] 인기 제품 동적 보고서 완성:

  • B4의 지역 드롭다운을 변경해 보세요. 모든 요약이 함께 업데이트됩니다.
  • Sales 시트에 새 데이터를 추가하면 보고서에 자동으로 반영됩니다.
  • 수식 복사도, 수동 정렬도, 피벗 테이블 새로 고침도 필요 없습니다.

엑셀 동적 배열 함수 완벽 가이드: FILTER, UNIQUE, SORT로 보고서 자동화하는 5가지 방법

마무리

이 튜토리얼에서는 FILTER, UNIQUE, SORT 등 동적 배열 함수가 업무 방식을 바꾸는 5가지 방법을 살펴보았습니다. 동적 배열 함수는 스프레드시트를 관리하는 반복 노동을 없애 줍니다. 수식을 복사하고 깨진 참조를 수정하는 대신 분석에 집중할 수 있습니다. 보고서는 스스로 업데이트되고, 대시보드도 자동으로 갱신됩니다. 이 함수들을 사용하기 시작하면 요약표와 대시보드에 딱이라는 것을 깨닫게 될 것입니다. 수동 새로 고침도, 복잡한 배열 수식도, 보조 열도 없이 즉시 업데이트되는 보고서를 만들 수 있으니까요.


무료 고급 엑셀 연습 문제와 해답 받기!