01. 엑셀에 일어난 조용한 혁명

엑셀을 오래 써본 사람이라면 한 번쯤 겪어봤을 장면이 있다.
데이터 수천 줄을 앞에 두고 "조건에 맞는 것만 뽑아줘"라는 요청을 받았을 때,
우리는 필터를 걸고, 복사하고, 다른 시트에 붙여넣고, 다시 원본이 바뀌면 처음부터 반복했다.

혹은 더 고수라면 INDEX + SMALL + IF + ROW를 조합한
암호문 같은 배열 수식을 만들고,
마지막에 경건하게 Ctrl + Shift + Enter를 눌렀다.
그리고 수식 양옆에 중괄호 { }가 붙으면 "아, 됐다" 하며 안도했다.

그 시절은 끝났다.
2018년 9월, 마이크로소프트는 Office 365(현 Microsoft 365) 참가자 프로그램에
동적 배열(Dynamic Arrays)이라는 엔진 레벨의 변화를 투입했다.
그리고 2020년 1월, 정식 채널에 전면 배포되면서
엑셀의 계산 엔진은 30여 년 만에 근본부터 다시 쓰였다.

02. '스필(Spill)' — 흘러넘침이라는 개념

동적 배열을 이해하는 열쇠는 단 하나, 스필(Spill)이다.
직역하면 '엎질러짐', 엑셀 한국어판에서는 분산 또는 흘러넘침으로 번역된다.

수식이 여러 개의 값을 반환하면,
엑셀은 그 값을 담기 위해 인접한 빈 셀로 결과를 자동 확장한다.
수식이 실제로 입력된 칸은 딱 하나뿐이다. 이것을 앵커 셀(anchor cell)이라 부른다.

스필 범위의 시각적 특징

스필된 영역을 클릭하면 파란 실선 테두리가 전체 범위를 감싼다.
앵커 셀이 아닌 칸을 클릭하면 수식 입력줄의 글자가 회색으로 표시된다.
이는 "여기는 계산 결과일 뿐, 수식의 본체가 아니다"라는 신호다.

스필 연산자 # — 가장 저평가된 문법

동적 배열의 진짜 무기는 해시 참조(#)다.
=E2# 라고 쓰면 "E2에서 시작된 스필 범위 전체"를 의미한다.
행이 3개든 300개든, 참조는 알아서 따라간다.

=COUNTA(E2#)        ' 스필 결과의 개수
=SUM(F2#)           ' 스필된 금액 열 합계
=SORT(E2#)          ' 스필 결과를 다시 정렬

#SPILL! 오류 — 새로 생긴 에러의 정체

엑셀 오류 목록에 새 식구가 생겼다. #SPILL!이다.
"결과를 펼치고 싶은데 자리가 없다"는 뜻이다. 원인은 대체로 네 가지다.

03. UNIQUE — 중복 제거 버튼과의 작별

데이터 탭의 '중복된 항목 제거'는 편리하지만 치명적 단점이 있다.
원본을 파괴한다. 그리고 일회성이다.
데이터가 갱신되면 또 눌러야 한다.

구문

UNIQUE(array, [by_col], [exactly_once])

세 번째 인수의 반전

많은 사람이 놓치는 부분이다.
=UNIQUE(A2:A100) 은 "중복을 하나로 합친 목록"을 준다. (A, B, C)
=UNIQUE(A2:A100, , TRUE) 는 "중복이 아예 없던 값"만 준다. (한 번만 나온 것)

후자는 이상치 탐지에 강력하다.
예: 정상적으로는 두 번씩 기록되어야 할 입·출고 전표 번호 중
한 번만 찍힌 번호를 찾으면 그게 바로 누락 건이다.

다중 열 UNIQUE — 조합의 고유값

=UNIQUE(B2:D500)

3개 열을 통째로 넣으면 행 단위 조합으로 중복을 판정한다.
"지역 + 제품군 + 담당자"의 유일한 조합 목록이 한 번에 나온다.
피벗테이블 없이도 마스터 키를 뽑아낼 수 있다는 뜻이다.

정렬까지 한 방에

=SORT(UNIQUE(FILTER(C2:C9999, C2:C9999<>"")))

이 한 줄이 하는 일을 풀어 쓰면 이렇다.
① 빈칸을 걸러내고 → ② 중복을 제거하고 → ③ 오름차순 정렬.
예전이라면 세 단계의 수작업이었고, 원본이 바뀌면 다시 해야 했다.
지금은 Enter 한 번이며, 이후 영원히 자동이다.

04. FILTER — VLOOKUP의 한계를 넘어서

VLOOKUP과 XLOOKUP은 근본적으로 '첫 번째 하나'만 찾는 함수다.
하지만 실무 질문의 절반은 "해당되는 거 전부 보여줘"다.
FILTER는 정확히 그 지점을 겨냥한다.

구문

FILTER(array, include, [if_empty])

array는 가져올 데이터 범위,
include는 TRUE/FALSE로 구성된 같은 높이(또는 같은 너비)의 배열,
if_empty는 조건에 맞는 게 없을 때 표시할 값이다.

AND 조건 · OR 조건

FILTER에는 조건을 여러 개 받는 인수가 없다.
대신 불리언 대수를 쓴다. 이게 FILTER의 정체성이다.

' AND — 곱하기 (*)
=FILTER(A2:E999, (C2:C999="서울")*(D2:D999>=1000000), "없음")

' OR — 더하기 (+)
=FILTER(A2:E999, (C2:C999="서울")+(C2:C999="부산"), "없음")

' 혼합 — 괄호로 우선순위 통제
=FILTER(A2:E999, ((C2:C999="서울")+(C2:C999="부산"))*(E2:E999="완료"), "없음")

' NOT — 부등호 또는 뺄셈
=FILTER(A2:E999, (C2:C999<>"서울"), "없음")

원리는 단순하다. TRUE는 1, FALSE는 0으로 취급된다.
1 × 1 = 1(둘 다 참), 1 × 0 = 0(하나라도 거짓).
1 + 0 = 1(하나만 참이어도 됨). 논리 게이트를 산수로 구현하는 셈이다.

열도 골라낸다 — 가로 방향 FILTER

include 인수에 행 방향 배열을 넣으면 열을 걸러낸다.

=FILTER(A1:H500, A1:H1<>"내부용")

머리글 행에 '내부용'이라 표시된 열만 쏙 빼고 출력한다.
외부 제출용 자료를 만들 때 수동 열 삭제를 할 필요가 사라진다.

부분 일치 검색 — 검색창 만들기

=FILTER(A2:E999, ISNUMBER(SEARCH($H$1, B2:B999)), "결과 없음")

H1 셀에 키워드를 치면 실시간으로 목록이 좁혀진다.
매크로 한 줄 없이 검색 기능이 있는 대시보드가 완성된다.
H1이 비어 있으면 SEARCH가 모든 행에 1을 반환하므로 전체가 표시된다 — 의도치 않게 편리한 기본 동작이다.

05. 6인의 원년 멤버 — 동적 배열 함수 완전 정리

2020년 정식 출시 당시 함께 등장한 여섯 함수는 서로 조합될 때 진가를 발휘한다.

SORT — 정렬을 수식으로

SORT(array, [sort_index], [sort_order], [by_col])

sort_order는 1(오름차순) 또는 -1(내림차순).
데이터 탭의 정렬 버튼과 달리 원본을 건드리지 않는다.
원본 시트는 입력 순서 그대로 두고, 보고서 시트에서만 정렬해 보여줄 수 있다.

SORTBY — 다른 기준으로 정렬

SORTBY(array, by_array1, [order1], by_array2, [order2], ...)

SORT가 "몇 번째 열로 정렬"이라면, SORTBY는 "이 배열을 기준으로 정렬"이다.
결과에 포함되지 않은 열을 기준으로 삼을 수 있다는 게 결정적 차이다.

' 이름만 출력하되, 매출 순으로 정렬
=SORTBY(B2:B500, E2:E500, -1)

' 다중 기준: 부서 오름차순 → 연봉 내림차순
=SORTBY(A2:E500, C2:C500, 1, E2:E500, -1)

SEQUENCE — 숫자를 만들어내는 함수

SEQUENCE(rows, [columns], [start], [step])

연속된 숫자 배열을 생성한다. 단순해 보이지만 활용이 넓다.

' 이번 달 날짜 전체 자동 생성
=SEQUENCE(DAY(EOMONTH(TODAY(),0)), 1, DATE(YEAR(TODAY()),MONTH(TODAY()),1), 1)

' 12개월 월말 날짜
=EOMONTH(DATE(2024,1,1), SEQUENCE(12,1,0,1))

' 원금균등 상환 스케줄의 회차 번호
=SEQUENCE(36)

RANDARRAY — 난수 블록

RANDARRAY([rows],[cols],[min],[max],[whole_number])

테스트 데이터 생성, 몬테카를로 시뮬레이션, 무작위 샘플 추출에 쓰인다.
=SORTBY(A2:A500, RANDARRAY(499)) 는 명단을 무작위로 섞는 마법의 한 줄이다.
조 편성, 당첨자 추첨에 그대로 쓸 수 있다.

XLOOKUP과 XMATCH — 배열 시대의 조회

엄밀히는 동적 배열 '전용' 함수는 아니지만, 같은 시기 배포되며 배열 반환이 가능해졌다.
XLOOKUP은 여러 열을 한 번에 반환할 수 있다.

' 사번으로 이름·부서·직급을 한 번에
=XLOOKUP($H$2, A2:A999, B2:D999)

' 못 찾았을 때 처리까지 내장 (IFERROR 불필요)
=XLOOKUP(H2, A2:A999, B2:B999, "미등록")

' 역방향 검색 (가장 최근 기록 찾기)
=XLOOKUP(H2, A2:A999, D2:D999, "", 0, -1)

06. 2022년 2차 웨이브 — 배열 성형 함수들

2022년, 마이크로소프트는 14개의 함수를 추가로 투입했다.
이들은 데이터를 만드는 게 아니라 모양을 바꾸는 데 특화되어 있다.

TEXTSPLIT / TEXTBEFORE / TEXTAFTER

'텍스트 나누기' 마법사가 수식이 되었다.

' 쉼표로 가로 분할
=TEXTSPLIT(A2, ",")

' 행·열 동시 분할 (세미콜론=행, 쉼표=열)
=TEXTSPLIT(A2, ",", ";")

' 이메일에서 도메인만
=TEXTAFTER(A2, "@")

' 파일명에서 확장자 제거 (뒤에서 첫 번째 점 기준)
=TEXTBEFORE(A2, ".", -1)

VSTACK / HSTACK — 시트 통합의 종결자

여러 시트의 데이터를 세로로 이어붙인다. Power Query 없이도 가능하다.

=VSTACK(1월!A2:E100, 2월!A2:E100, 3월!A2:E100)

' 빈 행 제거까지
=LET(d, VSTACK(1월!A2:E100, 2월!A2:E100),
     FILTER(d, INDEX(d,,1)<>""))

TOCOL / TOROW / WRAPROWS / WRAPCOLS

2차원을 1차원으로 펴거나, 1차원을 격자로 접는다.
=TOCOL(A1:E20, 1) 은 빈 셀을 무시하며 한 줄로 만든다.
흩어진 데이터를 정규화할 때 놀라울 만큼 유용하다.

TAKE / DROP / CHOOSEROWS / CHOOSECOLS

=TAKE(A2:E999, 10)            ' 상위 10행
=TAKE(A2:E999, -5)            ' 하위 5행
=DROP(A1:E999, 1)             ' 머리글 제거
=CHOOSECOLS(A2:H999, 1, 3, 7) ' 1·3·7번째 열만
=TAKE(SORT(A2:E999, 5, -1), 10) ' 매출 TOP 10

마지막 줄을 보라. 정렬 후 상위 추출이 한 줄이다.
'매출 상위 10개 거래처'를 뽑는 작업이 이보다 간결할 수 있을까.

GROUPBY / PIVOTBY — 피벗테이블의 수식화 (2024)

2024년 등장한 최신 함수다. 피벗테이블을 수식으로 구현한다.

=GROUPBY(C2:C999, E2:E999, SUM, 3, 0)

지역별 매출 합계를, 머리글 포함으로, 정렬까지 해서 반환한다.
피벗테이블과 달리 '새로 고침' 버튼을 누를 필요가 없다.
원본이 바뀌면 즉시 반영된다. 이것이 수식 기반 집계의 압도적 장점이다.

07. LAMBDA와 LET — 수식이 언어가 되다

2021년 등장한 LAMBDA는 엑셀 역사상 가장 철학적인 변화였다.
사용자가 직접 함수를 만들 수 있게 된 것이다. VBA 없이, 순수 수식으로.

LET — 변수 선언

=LET(
   데이터, FILTER(A2:E999, C2:C999="서울"),
   건수, ROWS(데이터),
   합계, SUM(INDEX(데이터,,5)),
   평균, 합계/건수,
   평균
)

같은 계산을 반복하지 않으므로 속도가 빨라지고 가독성이 올라간다.
대용량 데이터에서 FILTER를 세 번 쓰던 수식을 LET으로 묶으면 체감 성능 차이가 크다.

LAMBDA — 나만의 함수

' 이름 관리자에 '부가세' 로 등록
=LAMBDA(금액, ROUND(금액*0.1, 0))

' 시트에서 사용
=부가세(D2)

재귀 호출도 지원한다.
이름을 자기 자신 안에서 호출하면 반복 처리가 가능해진다.
엑셀 수식이 튜링 완전(Turing-complete)해졌다는 평가가 나온 이유다.

헬퍼 함수 8종

LAMBDA를 인수로 받는 함수들이다.

' 각 행의 최댓값 한 번에
=BYROW(B2:F100, LAMBDA(r, MAX(r)))

' 누계 컬럼 생성
=SCAN(0, D2:D100, LAMBDA(a,b, a+b))

08. 실전 시나리오 — 대시보드 한 장 만들기

이론은 충분하다. 실제로 조립해보자.
상황: 전국 매출 원장(A~F열, 약 5천 행)이 있고, 팀장이 이렇게 말한다.
"지역 고르면 그 지역 거래 내역이랑 요약 지표가 바로 나오게 해줘. 매일 갱신되게."

STEP 1. 드롭다운 만들기

' H1 셀 근처 임시 영역 (예: N2)
=SORT(UNIQUE(FILTER(C2:C5000, C2:C5000<>"")))

이후 H2 셀에 데이터 유효성 검사 → 목록 → 원본 =$N$2#
지역이 추가되어도 드롭다운이 자동으로 늘어난다.

STEP 2. 상세 내역 출력

=LET(
  원본, A2:F5000,
  조건, (C2:C5000=$H$2)*(F2:F5000="완료"),
  결과, FILTER(원본, 조건, "해당 데이터 없음"),
  SORT(결과, 5, -1)
)

선택한 지역의 '완료' 건만, 금액 내림차순으로 정렬해 출력한다.

STEP 3. 요약 지표

' 건수
=IF(ISTEXT(INDEX(J2#,1,1)), 0, ROWS(J2#))

' 총 매출
=SUM(CHOOSECOLS(J2#, 5))

' TOP 3 거래처
=TAKE(CHOOSECOLS(J2#, 2), 3)

STEP 4. 카테고리별 집계

=GROUPBY(CHOOSECOLS(J2#,4), CHOOSECOLS(J2#,5), SUM, 3, 0, -1)

완성이다. 매크로 0줄, 피벗테이블 0개.
원본에 행을 추가하면 모든 지표가 동시에 갱신된다.
이런 구조를 한 번 익혀두면, 재능넷의 지식인의 숲에 올라오는
수많은 엑셀 자동화 사례들이 왜 하나같이 동적 배열을 기반으로 하는지 이해하게 된다.

09. 호환성 — 반드시 알아야 할 현실

여기서 냉정해질 필요가 있다.
동적 배열은 모든 엑셀에서 작동하지 않는다.

_xlfn 접두사의 의미

동적 배열 수식이 담긴 파일을 구버전에서 열면
=_xlfn.UNIQUE(A2:A100) 처럼 이상한 접두사가 붙고 #NAME? 오류가 뜬다.
_xlfn은 "이 버전이 모르는 함수"라는 표식이다.

스필된 범위는 =_xlfn.ANCHORARRAY(E2) 로 변환되며 역시 오류가 난다.
따라서 외부 배포용 파일을 만들 때는 두 가지 선택지가 있다.

암시적 교차 연산자 @

구버전 파일을 최신 엑셀에서 열면 수식에 @가 자동으로 붙는 경우가 있다.
예: =SUM(@A:A)
이는 "예전처럼 한 값만 가져오라"는 하위 호환 장치다.
동적 배열로 동작시키려면 @를 지우면 된다. 겁먹을 필요 없다.

10. 성능과 설계 — 고급자의 주의사항

전체 열 참조를 피하라

=FILTER(A:F, C:C="서울") 는 이론상 104만 행을 스캔한다.
한두 개면 견디지만 수십 개가 되면 파일이 마비된다.
데이터를 표(Table)로 만들고 구조적 참조 매출표[[#모두],[지역]]를 쓰거나,
현실적 상한선(예: 1:50000)을 지정하자.

체인 참조의 함정

스필 범위를 계속 물고 늘어지는 구조(A→B→C→D)는 편리하지만,
중간 하나가 #SPILL!이 되면 아래 전체가 연쇄 붕괴한다.
중요한 계산 체인은 LET으로 하나의 수식 안에 묶어두는 편이 안정적이다.

삭제할 때는 앵커 셀을

스필 결과 중간 행을 지우려 하면 "배열의 일부를 변경할 수 없습니다" 경고가 뜬다.
수식을 없애려면 반드시 왼쪽 위 앵커 셀을 지워야 한다.

조건부 서식과의 궁합

조건부 서식 규칙의 적용 범위에는 # 참조를 직접 넣을 수 없다.
대신 결과가 나올 최대 범위를 넉넉히 지정하고,
=AND($A1<>"", 조건) 형태로 빈 행을 배제하는 방식이 실무 정석이다.

11. 사고방식의 전환 — 셀에서 배열로

기술적 설명을 넘어, 이 변화의 본질을 짚고 싶다.

과거의 엑셀 사용자는 '셀 단위 사고'를 했다.
한 칸에 수식을 쓰고, 그걸 드래그로 복사하고, 범위가 늘면 다시 드래그했다.
수식은 정적이었고, 사람이 범위를 관리했다.

동적 배열 이후의 사용자는 '데이터셋 단위 사고'를 한다.
"이 데이터 덩어리를 어떻게 변형할까"를 고민한다.
필터링 → 정렬 → 집계 → 성형. 이것은 사실상 SQL이나 파이썬 판다스의 사고방식이다.

SELECT * FROM 매출 WHERE 지역='서울' ORDER BY 금액 DESC LIMIT 10

이 변화가 실무자에게 주는 의미는 두 가지다.

첫째, 재현성이다.
마우스로 한 작업은 기록되지 않지만, 수식은 남는다.
"이 숫자 어떻게 나온 거예요?"라는 질문에 수식 하나로 답할 수 있다.
감사 추적과 검증이 가능한 문서, 즉 신뢰할 수 있는 문서가 된다.

둘째, 확장성이다.
동적 배열로 짠 보고서는 데이터가 100행이든 10만 행이든 구조를 바꿀 필요가 없다.
한 번 만들면 계속 쓴다. 이것이 진정한 자동화의 정의다.

그래서 요즘 실무 엑셀 교육의 커리큘럼이 재편되고 있다.
VLOOKUP 중첩과 피벗테이블 클릭 순서를 외우던 시대에서,
배열을 다루는 문법을 익히는 시대로 넘어가는 중이다.

12. 자주 묻는 질문 정리

Q. 피벗테이블은 이제 필요 없나요?

아니다. 수백만 행 규모의 대용량 집계, 슬라이서·타임라인을 통한 인터랙티브 탐색,
데이터 모델(Power Pivot) 기반 다중 테이블 관계 분석은 여전히 피벗의 영역이다.
동적 배열은 중소 규모 + 자동 갱신 + 커스텀 레이아웃에서 압도적이다.
둘은 경쟁이 아니라 역할 분담이다.

Q. Power Query와는 어떤 관계인가요?

Power Query는 외부 데이터 수집과 전처리(ETL)에 강하다.
여러 파일 병합, 웹 크롤링, 데이터 타입 정제는 Power Query가 맞다.
다만 새로 고침이 필요하다는 단점이 있다.
수집은 Power Query, 실시간 가공과 표현은 동적 배열 — 이 조합이 현재의 베스트 프랙티스다.

Q. FILTER 결과가 세로로 한 칸만 나와요

array 인수와 include 인수의 높이가 다른 경우다.
FILTER(A2:E100, C2:C99="서울") — 99행 vs 98행 불일치.
두 범위의 시작 행과 끝 행을 반드시 맞춰야 한다. #VALUE!의 주요 원인이기도 하다.

Q. UNIQUE 결과에 0이 섞여 나와요

빈 셀은 UNIQUE에서 숫자 0으로 해석된다.
=UNIQUE(FILTER(A2:A100, A2:A100<>"")) 로 빈칸을 먼저 제거하면 해결된다.

Q. 대소문자를 구분하고 싶어요

FILTER와 UNIQUE는 기본적으로 대소문자를 구분하지 않는다.
EXACT 함수를 조건에 넣으면 구분할 수 있다.
=FILTER(A2:C99, EXACT(B2:B99, "ABC"))

Q. 결과를 가로로 눕히고 싶어요

TRANSPOSE로 감싸면 된다.
=TRANSPOSE(SORT(UNIQUE(A2:A50)))
동적 배열 환경에서는 TRANSPOSE도 CSE 없이 그냥 Enter로 작동한다.

마치며 — 오늘 당장 바꿔볼 것

모든 걸 한 번에 익힐 필요는 없다.
내일 출근해서 딱 세 가지만 시도해보자.

이 세 가지만 몸에 붙어도 반복 업무 시간이 눈에 띄게 줄어든다.
그리고 어느 순간 LET과 LAMBDA가 자연스러워지면,
당신은 이미 엑셀을 도구가 아니라 언어로 다루는 사람이 되어 있을 것이다.

엑셀은 1985년에 태어나 40년을 살아남았다.
그 생명력의 비결은 변하지 않아서가 아니라, 결정적인 순간에 변했기 때문이다.
동적 배열은 그 결정적 변화 중 가장 최근이자, 가장 근본적인 것이었다.

이런 실무 노하우와 자동화 템플릿에 대한 더 깊은 이야기는
재능넷의 지식인의 숲에서 계속 다뤄지고 있으니,
문서작성 역량을 한 단계 끌어올리고 싶다면 꾸준히 들여다보길 권한다.

수식 하나가 스스로 자라나는 시대.
이제 셀을 드래그하는 대신, 데이터에게 질문하는 법을 배울 차례다.