이 글의 목적은 엑셀에서 배열 수식이 오류를 반환할 때 원인을 신속히 진단하고 재현 가능한 해결 절차를 제시하여 현업 사용자가 즉시 문제를 해소하도록 돕는 것이다.
1. 배열 수식의 동작 원리 이해
배열 수식은 하나의 입력으로 다수의 결과를 반환하거나, 다수의 입력에 대해 누적 연산을 수행하는 수식 구조를 의미한다.
엑셀 365 및 2019 이후의 동적 배열 엔진에서는 결과가 다셀로 자동 확장되며 이를 스필이라 부른다.
레거시 배열 수식은 Ctrl+Shift+Enter 입력을 요구하며, 편집 표시로 중괄호가 나타나지만 사용자가 직접 입력하지는 않는다.
동적 배열 시대에는 대부분의 최신 함수가 자동 스필을 지원하나, 테이블 객체나 병합 셀 등 물리적 제약에 의해 스필이 차단될 수 있다.
주의 : 같은 통합 문서에서 동적 배열과 레거시 배열이 혼재하면 계산 규칙과 참조 방식이 달라 오동작이 발생할 수 있다.
2. 빈번한 오류 코드와 의미
| 오류코드 | 의미 | 배열 수식 관점 원인 요약 |
|---|---|---|
| #SPILL! | 스필 범위가 막혀 확장 실패 | 병합 셀, 테이블, 다른 값 존재, 시트 경계, 숨김 개체가 범위를 점유 |
| #VALUE! | 형식 부적합 또는 길이 불일치 | 텍스트-숫자 혼합, 2차원⇄1차원 불일치, 배열 연산 불가 항목 포함 |
| #N/A | 검색 실패 | XLOOKUP, MATCH 등에서 대상 미존재, 대소문자·공백 차이 |
| #CALC! | 계산 불가 | BYROW/BYCOL 맵핑 실패, 배열 길이 불일치, 비유효 스칼라 반환 |
| #REF! | 참조 무효 | 스필 원본 삭제, 행/열 삭제, 잘못된 오프셋 |
| #NUM! | 수치 오류 | 루트 음수, 로그 0/음수, 반복 계산 발산 |
| #NAME? | 이름 인식 실패 | 버전 미지원 함수, 정의되지 않은 이름, 지역 함수명 혼용 |
| #DIV/0! | 0으로 나눔 | 분모가 0 또는 빈값을 0으로 해석 |
| #NULL! | 교차 범위 없음 | 잘못된 공백 교차 연산자 사용 |
3. 10단계 신속 진단 체크리스트
- 스필 범위에 값·병합 셀·테이블이 점유하는지 확인한다.
- 대상 범위가 시트 경계를 넘어서는지 확인한다.
- 수식이 1차원 결과를 2차원 영역에, 또는 반대로 출력하려는지 확인한다.
- 데이터 형식이 숫자·텍스트·날짜로 일관되는지 점검한다.
- 자동 계산이 꺼져 있는지 확인한다.
- 테이블 내 셀에 스필 수식을 배치했는지 확인한다.
- 함수 가용 버전을 점검한다.
- 이름 정의·명명된 범위·구조적 참조가 유효한지 확인한다.
- 순환 참조가 없는지 계산 옵션에서 확인한다.
- 평가 도구와 F9 부분 평가로 어느 항에서 오류가 발생하는지 분해한다.
4. #SPILL! 오류 해결 절차
4.1 원인 분류
- 물리적 차단: 스필 범위 내 값, 병합 셀, 테이블
- 경계 문제: 시트 끝에 걸림
- 불안정 스필: 변동 크기 배열이 다른 개체와 충돌
4.2 조치
- 스필 대상 영역을 비우거나 병합을 해제한다.
- 스필 수식은 표가 아닌 일반 범위에 둔다.
- 필요 시 출력 방향을 TRANSPOSE로 회전한다.
- 스칼라화가 필요하면 INDEX로 단일 요소를 반환한다.
=SEQUENCE(10,1) '세로 10행 스필 =TRANSPOSE(SEQUENCE(1,10)) '가로→세로 회전 =INDEX(FILTER(A2:A100, A2:A100>=0), 1) '첫 요소만 스칼라 반환 주의 : 표 내부 셀에 동적 스필 수식을 입력하면 #SPILL!이 발생한다.
5. #VALUE! 오류 해결 절차
5.1 길이·차원 불일치
같은 연산에 참여하는 배열은 행·열 길이가 호환되어야 한다.
'잘못된 예: 3행 × 1열 + 1행 × 3열 ={1;2;3}+{1,2,3} '차원 불일치로 #VALUE! '해결: BROADCAST 호환 구조로 재작성 =SEQUENCE(3,1)+TRANSPOSE(SEQUENCE(1,3)) 5.2 텍스트·숫자 혼합
숫자처럼 보이는 텍스트가 포함되면 배열 연산에서 #VALUE!가 발생한다.
=SUM(--TEXTSPLIT(A1,",")) '--로 숫자 강제 변환 =SUM(VALUE(TEXTSPLIT(A1,","))) 'VALUE 사용 5.3 데이터형 불일치
날짜는 일련번호이므로 지역 형식과 분리하여 처리한다.
=DATEVALUE(TEXT(B2,"yyyy-mm-dd")) 6. #N/A 오류와 견고한 검색
#N/A는 검색 실패를 의미한다.
=XLOOKUP(E2, A:A, B:B) '기본: 정확히 없으면 #N/A =IFNA(XLOOKUP(E2, A:A, B:B), "미존재") '부드러운 처리 =LET(q, TRIM(LOWER(E2)), XLOOKUP(q, LOWER(TRIM(A:A)), B:B, "미존재")) 배열 검색은 공백·대소문자·숨은 문자에 취약하므로 사전 정규화가 필요하다.
=MAP(A2:A100, LAMBDA(x, TRIM(CLEAN(x)))) 7. #CALC! 발생 조건과 우회
BYROW, BYCOL, MAP, REDUCE, SCAN 등 람다형 배열 함수는 매핑 또는 축소 과정에서 비정상 반환이 있으면 #CALC!를 낸다.
=BYROW(A2:D10, LAMBDA(r, IF(COUNT(r)=0, NA(), SUM(r)))) '#N/A로 의도적 표식 =BYCOL(A2:D10, LAMBDA(c, LET(v, FILTER(c, c<>""), IF(COUNTA(v)=0, 0, AVERAGE(v))))) 주의 : 람다 내부에서 스칼라를 기대하는 위치에 배열을 반환하면 #CALC!가 발생한다.
8. 차원 설계와 출력 제어
배열 수식의 가장 흔한 구조적 문제는 2차원 출력을 1차원 영역에 강제하는 경우이다.
=TOCOL(A2:D10) '열 기준 세로 벡터화 =TOROW(A2:D10) '행 기준 가로 벡터화 =WRAPROWS(A2:A100, 5) '세로를 가로 5개씩 감싸기 =WRAPCOLS(A2:A100, 5) =VSTACK(A2:A5, C2:C5) '세로 결합 =HSTACK(A2:D2, F2:I2) '가로 결합 9. 테이블, 병합, 숨김과 상호작용
- 표는 스필을 허용하지 않으므로 표 외부 셀에 수식을 두고, 결과를 표에 참조로 연결한다.
- 병합 셀은 즉시 해제한다.
- 숨겨진 도형·검토 노트·코멘트가 스필 영역을 점유할 수 있으므로 개체 선택 창에서 정리한다.
10. 데이터 정규화와 전처리 패턴
'공백·제어문자 제거 =MAP(A2:A, LAMBDA(x, TRIM(CLEAN(SUBSTITUTE(x,CHAR(160)," "))))) '숫자만 추출 =BYROW(A2:A, LAMBDA(r, VALUE(TEXTJOIN("",,IF(ISNUMBER(--MID(r,SEQUENCE(LEN(r)),1)), MID(r,SEQUENCE(LEN(r)),1), ""))))) 11. 오류 관리 표준 템플릿
| 상황 | 권장 패턴 | 예시 |
|---|---|---|
| 검색 실패 | IFNA 핸들링 | =IFNA(XLOOKUP(k,lk,rv),"없음") |
| 0으로 나눔 | 분모 가드 | =IF(sum=0,0,num/sum) |
| 형 변환 실패 | VALUE/N | =SUM(N(A2:A100)) |
| 비정형 길이 | IFERROR+FILTER | =IFERROR(FILTER(rng,cond),"") |
| 스칼라 강제 | INDEX | =INDEX(arr,1) |
12. 지역 설정과 구분 기호 이슈
쉼표 대신 세미콜론을 사용하는 지역에서는 함수 인수 구분자가 다르다.
'쉼표 지역 =FILTER(A2:B100, A2:A100>=0, "없음") '세미콜론 지역 =FILTER(A2:B100; A2:A100>=0; "없음") 주의 : 소수점 기호와 날짜 서식이 다른 파일을 교차 편집하면 텍스트→숫자 변환 실패로 #VALUE!가 쉽게 발생한다.
13. 레거시 배열 수식 전환 가이드
Ctrl+Shift+Enter 기반 수식을 동적 배열로 치환하면 유지보수성이 크게 향상된다.
| 레거시 | 동적 배열 대체 | 비고 |
|---|---|---|
{=SUM(IF(A2:A100>0, A2:A100))} | =SUM(FILTER(A2:A100, A2:A100>0)) | 조건 합계 |
{=MAX(IF(B2:B100=C2, A2:A100))} | =MAX(IFNA(FILTER(A2:A100, B2:B100=C2), "")) | 조건 최대 |
{=SMALL(IF(C2:C100="Y", A2:A100), SEQUENCE(5))} | =TAKE(SORT(FILTER(A2:A100, C2:C100="Y")),5) | 상위 n개 |
14. 스필 참조와 범위 고정
스필된 범위는 앵커 기호로 전체를 참조한다.
=SUM(B2#) 'B2에서 스필된 전체 합계 =INDEX(B2#, 1) '첫 항목 =TAKE(B2#, 10) '상위 10개 15. 평가 도구와 F9 디버깅
- 문제 셀을 선택하고 수식 탭에서 수식 평가를 실행한다.
- F9로 부분 선택을 평가하여 중간 배열의 실제 값을 확인한다.
- Ctrl+Z로 평가로 인한 임시 치환을 즉시 되돌린다.
'부분 평가 예시 =SUM( FILTER(A2:A100, A2:A100>=0) ) 'FILTER(...)만 선택 후 F9로 결과 배열을 확인 16. 계산 옵션, 반복 계산, 변동 함수
- 자동 계산이 수동이면 스필 결과가 업데이트되지 않는다.
- ITERATIVE CALCULATION을 켜면 순환 참조가 의도치 않게 숨겨질 수 있다.
- RAND, NOW 등 변동 함수는 재계산마다 스필 크기를 바꿔 충돌을 유발할 수 있다.
17. 구조적 참조와 배열 상호작용
표의 구조적 참조는 동적 배열과 함께 사용할 때 특별한 주의가 필요하다.
=FILTER( Table1[Amount], Table1[Flag]="Y" ) =LET(t, Table1, FILTER(t[Amount], t[Flag]="Y")) 주의 : 표 내부 셀에서 스필 수식을 사용하지 말고, 외부 셀에 수식을 둔 다음 구조적 참조로 입력을 공급한다.
18. 정렬·고유·필터 조합의 안정 패턴
=SORT(UNIQUE(TAKE(FILTER(A2:D100, D2:D100="OK"),,2))) =LET(d, FILTER(A2:C100, B2:B100>0), SORTBY(d, INDEX(d,,3), -1)) 19. 다차원 집계의 안전 수식
=BYROW(A2:D10, LAMBDA(r, SUM(r))) '행 합계 =BYCOL(A2:D10, LAMBDA(c, AVERAGE(c))) '열 평균 =MMULT(--(UNIQUE(A2:A100)=TRANSPOSE(A2:A100)), SEQUENCE(ROWS(UNIQUE(A2:A100)),1,1,0)) '고급 예시 20. 텍스트 분해·재조합 시 오류 방지
=LET(parts, TEXTSPLIT(A2, ","), IFERROR(TAKE(parts,,3), "")) '부족 길이 가드 =TEXTJOIN("-",, IFERROR(TAKE(TEXTSPLIT(A2,","),,3), "")) '재조합 21. 숫자와 날짜 안정 변환
=--SUBSTITUTE(A2, ",", "") '천 단위 구분자 제거 =DATE(YEAR(B2), MONTH(B2), DAY(B2)) '날짜 강제 정규화 22. 실무 시나리오별 처방전
| 시나리오 | 증상 | 원인 | 처방 |
|---|---|---|---|
| 보고서 자동 리스트 | #SPILL! | 스필 경로에 값 존재 | 출력 범위 비우기, 표 밖으로 이동 |
| 부서별 집계 | #VALUE! | 차원 불일치 | TOCOL/TOROW로 벡터화 후 연산 |
| 품목 매칭 | #N/A | 공백·대소문자 차이 | TRIM/LOWER 전처리, IFNA로 처리 |
| 람다 계산 | #CALC! | 스칼라 기대 위치 배열 반환 | INDEX로 스칼라화 |
| 배포 파일 | #NAME? | 상대방 버전 미지원 | 호환 함수로 재작성, 레거시 제공 |
23. 배포·협업 시 호환 전략
- 동적 배열 사용 파일은 최소 요구 버전을 명시한다.
- 핵심 시트에는 레거시 호환 수식을 병행 제공한다.
- 입력 시트 잠금과 데이터 유효성 검사로 형식 오류를 사전 차단한다.
24. 점검 자동화 매크로 샘플
'스필 충돌 지점 하이라이트 Sub MarkSpillBlocks() Dim rng As Range, c As Range Set rng = Selection For Each c In rng If c.HasSpill Then If Not c.SpreadsheetInquireFunctions Is Nothing Then End If End If Next c End Sub 위 코드는 구조 예시이며 실제 환경에서는 각 셀의 SpillParent와 SpillRange를 점검하는 API를 활용하여 차단 요소를 찾아야 한다.
25. 현장 점검 체크리스트
- 스필 대상 범위 비웠는가
- 병합·숨김·표가 없는가
- 입력 데이터 형식이 일관되는가
- 차원 설계와 출력 방향이 맞는가
- 필요 시 스칼라화 또는 벡터화 처리했는가
- 에러 가드(IFNA, IFERROR, 분모 가드)를 적용했는가
- 버전 호환과 이름 정의가 유효한가
- 자동 계산과 순환 참조 옵션이 적정한가
26. 즉시 사용 가능한 패턴 모음
'정렬된 고유 목록 =SORT(UNIQUE(A2:A100)) '조건부 필터 후 요약 =LET(d, FILTER(A2:C100, C2:C100="OK"), HSTACK(UNIQUE(INDEX(d,,1)), BYROW(UNIQUE(INDEX(d,,1)), LAMBDA(k, SUM(IF(INDEX(d,,1)=k, INDEX(d,,2), 0)))))) '스칼라 가드 =LET(v, FILTER(A2:A100, A2:A100>0), IF(COUNTA(v)=0, "", INDEX(v,1))) '안전 합계 =SUM(IFERROR(--A2:A100, 0)) FAQ
스필 범위를 테이블로 자동 반영하려면 어떻게 하나
스필 수식을 표 밖 셀에 두고, 표에서는 =B2# 형태로 전체 범위를 참조하여 표시한다.
레거시 배열과 동적 배열이 섞인 파일에서 우선 정리할 항목은 무엇인가
레거시 배열 수식을 동적 배열로 우선 전환하고, 불가한 경우에는 해당 시트에만 계산 범위를 고정한다.
XLOOKUP이 없는 환경에서는 어떤 조합을 쓰나
INDEX/MATCH 조합을 사용하되 IFNA 대신 IFERROR를 적용하고, 정렬 조건이 있으면 MATCH의 마지막 인수를 0으로 둔다.
배열 길이가 유동적일 때 그래프 범위를 어떻게 연결하나
스필 앵커 B2#를 이름으로 정의하고 차트 범위에 해당 이름을 지정한다.