파워포인트 자막(라이브 캡션) 안될 때 1분 진단과 완전 해결 가이드

날짜:

엑셀 #SPILL! 스필 오류 완벽 해결 가이드: 동적 배열 함수 장애 원인과 실무 복구 방법

날짜:

이 글의 목적은 엑셀 동적 배열 계산에서 발생하는 #SPILL! 스필 오류의 원인별 진단과 복구 절차를 체계적으로 정리하여 현장에서 즉시 문제를 재현·분석·해결할 수 있도록 돕는 것이다.

동적 배열과 스필 메커니즘 이해

엑셀 365 및 2021부터 도입된 동적 배열 엔진은 하나의 수식이 다수의 셀로 결과를 자동 확장하는 스필(Spill) 동작을 수행한다. 스필 범위는 수식이 입력된 앵커 셀(첫 셀)에서 시작하여 결과 배열 크기만큼 아래·오른쪽으로 확장된다. 스필된 결과 범위는 영역 참조 기호 # 로 호출하며, 예를 들어 A1# 은 A1에서 스필된 전체 결과를 의미한다.

스필 엔진은 확장 범위가 완전히 비어 있고 구조적으로 일관되어야 한다. 범위를 가로막는 데이터·개체·합병 셀·표(Table) 등 제약이 있으면 #SPILL! 오류가 발생한다. 오류 아이콘을 클릭하면 원인 설명이 나타나며, 그 설명에 따라 조치하면 된다.

#SPILL! 스필 오류 주요 원인 요약

원인 유형 설명 대표 현상 핵심 해결책
스필 범위에 값 존재 결과가 확장될 셀 범위에 기존 데이터·공식·공백처럼 보이는 문자(예: ' '), 숨겨진 문자(CHAR(160))가 존재한다. 일부 셀만 채워지고 앵커 셀에 #SPILL! 표시 범위 비우기·정리, CLEAR 또는 CLEAN/TRIM 적용
합병(병합) 셀 충돌 스필 경로에 병합된 셀이 하나라도 포함되어 확장 불가하다. 병합 영역과 스필 방향이 겹침 병합 해제 후 서식으로 대체, 필요 시 WRAPROWS/WRAPCOLS 로 재배치
표(Table) 내부 수식 구조적 참조를 사용하는 표 범위 안에서는 동적 배열 스필이 허용되지 않는다. 표 데이터 영역에 입력한 동적 배열 수식이 즉시 #SPILL! 표를 범위로 변환하거나 수식을 표 밖 셀로 이동
스필 범위 경계 불명확 무한 또는 과대한 범위 참조(전체 열/행)로 결과 크기 계산이 불가하다. FILTER 등에서 A:A 같은 전체 열 입력 유효 데이터 영역으로 제한, TAKE/DROP · INDEX 로 경계 설정
다른 스필과 겹침 이미 존재하는 스필 결과 범위와 새로운 스필 범위가 중첩된다. 이웃 시트 계산 후 중첩 구간에서 #SPILL! 앵커 위치 조정 또는 원본 수식의 결과 모양 재설계
시트 개체 충돌 도형·차트·이미지·슬라이서·스마트아트·스파크라인 등이 스필 경로에 놓여 있다. 겉보기에 빈 셀이나 개체가 존재 개체 이동·삭제, 시트 개체 선택으로 위치 확인
데이터 유효성/조건부서식 충돌 스필 대상에 특정 유효성 규칙이나 상충 조건부서식이 걸려 있다. 유효성 경고와 함께 스필 실패 규칙을 범위 밖으로 이동하거나 동적 참조로 수정
수식 입력 모드 제약 수동 계산, 순환 참조, 또는 이전 버전과의 호환 모드에서 동작 불안 재계산 후에도 간헐적 #SPILL! 자동 계산 복귀, 순환 제거, 통합문서 형식 최신화

원인별 정밀 진단 절차

  1. 앵커 셀의 경고 아이콘을 클릭하여 메시지 유형을 확인한다.
  2. 스필 예상 범위를 시각화한다. 앵커 셀을 선택하면 점선 윤곽이 표시된다.
  3. 윤곽 내 모든 셀에 데이터·개체·병합·유효성이 존재하는지 확인한다.
  4. 전체 열 참조·무한 배열 여부를 점검하고 입력 범위를 축소한다.
  5. 표 내부 입력인지 확인하고 필요한 경우 범위로 변환한다.
  6. 동일 범위에 다른 스필이 있는지 확인하고 중첩을 해소한다.
주의 : 외형상 빈 셀이라도 공백 문자, 비가시 문자(예: CHAR(160), 제로폭 문자)가 존재할 수 있다. 필히 정리 함수를 적용하여 청소해야 한다.

실무 복구 체크리스트

점검 항목 권장 조치 관련 함수·메뉴
스필 경로 내 값 범위 선택 후 지우기(모두 지우기) 수행 홈 > 지우기
비가시 문자 보조 열에 정리 수식 적용 후 값 붙여넣기 =TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," ")))
병합 셀 병합 해제 및 가운데 맞춤 등 서식으로 대체 홈 > 병합하고 가운데 맞춤
표 내부 수식 표를 범위로 변환하거나 수식을 표 외부로 이동 표 디자인 > 범위로 변환
개체 겹침 개체 선택 창에서 위치 이동·삭제 홈 > 찾기 및 선택 > 개체 선택
전체 열 참조 정확한 데이터 범위 지정, 동적 이름 범위 사용 INDEX , LET , TAKE/DROP
다른 스필과 중첩 앵커 위치 이동 또는 결과 형상 변경 WRAPROWS/WRAPCOLS , RESHAPE
수동 계산 자동으로 전환 후 강제 계산 수식 > 계산 옵션 > 자동

원인별 해결 예제

1) 스필 범위 비우기와 정리 

  
Option A: 빠른 정리
1) 스필 윤곽 내부 선택
2) 홈 > 지우기 > 모두 지우기
Option B: 숨은 공백 문자 제거
=TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," ")))

위 보조 수식을 사용해 데이터 열을 정제한 뒤 값으로 붙여넣기 한다.

2) 전체 열 참조 축소

FILTER 에서 A:A 와 같은 전체 열을 사용할 때 결과 크기 산정이 불가해질 수 있다. 다음과 같이 동적 경계를 명시한다. 

  
=LET(rng, A1:A1000, FILTER(rng, rng<>""))

또는 실제 데이터 끝행을 찾는 동적 범위를 사용한다. 

  
=LET(col, A:A, last, MATCH(2,1/(col<>"")), rng, A1:INDEX(A:A,last), FILTER(rng, rng<>""))

3) 표(Table) 내부 스필 회피

표 데이터 영역 셀에 동적 배열 수식을 입력하면 #SPILL! 이 발생한다. 해결책은 두 가지이다.

  1. 표를 범위로 변환한다.
  2. 수식을 표 밖 일반 셀에 넣고, 결과를 다시 표로 참조한다. 
  
=SORT(UNIQUE(Table1[품목])) // 표 밖 셀에 입력

4) 병합 셀 제거 및 레이아웃 재구성

병합 셀이 스필 경로를 막으면 해제해야 한다. 병합 대신 열 너비 조정이나 가운데 맞춤 서식을 사용한다. 결과가 폭이 넓다면 WRAPCOLS 또는 WRAPROWS 로 2차원으로 감싼다. 

  
=WRAPCOLS(SEQUENCE(30), 10) // 10열씩 감싸기

5) 중첩 스필 피하기

이미 존재하는 스필 결과와 겹치면 앵커 위치를 변경하거나 결과 모양을 바꾼다. 

  
// 세로 스택을 가로로 전개하여 충돌 회피 =HSTACK(UNIQUE(A1:A10), UNIQUE(B1:B10))

6) 데이터 유효성 및 조건부서식 조정

스필 대상 범위에 고정된 유효성 규칙이 있으면 오류가 날 수 있다. 규칙 범위를 스필 결과 A1# 로 연결하거나, 스필 대상 밖으로 이동한다. 

  
데이터 유효성(목록): =A1# // 스필 결과를 직접 참조

동적 배열 함수별 흔한 스필 시나리오

  • FILTER : 필터 결과가 가변적이므로 표 내부 입력·전체 열 참조를 피한다. 경계를 LET 으로 정의한다.
  • SORT / UNIQUE : 정렬·유일화 결과가 길게 확장된다. 스필 경로가 비어 있는지 확인한다.
  • SEQUENCE : 큰 크기 지정 시 시트 끝까지 확장되며 개체·병합과 충돌할 수 있다. 크기를 합리적으로 제한한다.
  • TOCOL / TOROW : 다중 범위 결합 시 중첩 스필 가능성이 높다. 결과 폭·높이를 사전 계산한다.
  • VSTACK / HSTACK : 서로 다른 길이 병합 시 빈칸이 채워지며 하단·우측 여유 공간 확인이 필요하다.

성능과 안정성을 고려한 설계 패턴

  1. 경계 명시 : 전체 열 대신 동적 마지막 행을 계산하여 입력 범위를 제한한다.
  2. 스필 결과 재활용 : 동일 계산 반복을 피하고 A1# 로 재사용한다.
  3. 모듈화 LET 으로 중간 계산을 이름으로 묶는다.
  4. 형상 제어 TAKE , DROP , CHOOSEROWS , CHOOSECOLS 로 크기를 통제한다. 
  
=LET( src, CHOOSECOLS(A1:D1000,1,3), clean, FILTER(src, INDEX(src,,1)<>""), uniq, UNIQUE(clean), TAKE(SORT(uniq,1,1), 100) )

개체 충돌 빠른 탐지 팁 

  
1) 홈 > 찾기 및 선택 > 선택 창(Selection Pane) 열기
2) 도형/그림/차트 목록에서 눈 아이콘으로 가시성 전환
3) 스필 윤곽과 겹치는 개체를 이동
주의 : 스파크라인은 셀 내부 개체이므로 스필 경로에 존재하면 오류를 유발한다. 스파크라인을 다른 영역으로 옮기거나 지운다.

스필을 의도적으로 억제하거나 제한하는 방법

  • 암시 교차 억제자(@) : 동적 배열을 단일값으로 강제하면 스필을 막을 수 있다. 단, 분석 의도가 훼손될 수 있으니 주의한다.
  • 인덱싱으로 단일 셀 선택 INDEX 로 결과 배열에서 특정 요소만 반환한다. 
  
// 첫 번째 항목만 반환하여 스필 방지 =INDEX(SORT(UNIQUE(A1:A100)),
1)

스필 결과 활용 베스트 프랙티스

  • 차트 원본 : 차트의 범위를 A1# 로 지정하여 결과 길이 변화에 자동 대응한다.
  • 데이터 유효성 : 드롭다운 목록을 스필 결과에 연결해 동적 목록을 제공한다.
  • 피벗 전처리 : 스택·유니크 결과를 중간 시트에 스필한 뒤 피벗 원본으로 사용한다.

오류 메시지 유형별 해법 매핑

오류 지시문 예시 원인 해석 즉시 조치
Spill range isn’t blank 스필 경로에 값/문자 존재 모두 지우기 후 재계산
Spill range has merged cells 병합 셀 포함 병합 해제
Spill range has table 표 내부에 수식 표 밖으로 이동 또는 범위로 변환
Spill range overlaps 다른 스필과 중첩 앵커 이동·형상 수정
Spill range contains object 도형·차트·그림 충돌 개체 이동·삭제
Spill range too big 무한·과대한 결과 경계 제한, 행·열 수 축소

현장 예제: 고객·품목 동적 목록 구축

목표는 거래내역에서 고객명과 품목을 중복 없이 정렬하고, 선택 드롭다운으로 제공하는 것이다. 

  
//
1) 원본 정리 =LET(src, A2:C10000, FILTER(src, INDEX(src,,1)<>""))
//
2) 고객·품목 유일 목록
=LET(clean, A2:B10000,
u1, UNIQUE(INDEX(clean,,1)),
u2, UNIQUE(INDEX(clean,,2)),
HSTACK(SORT(u1), SORT(u2)))

//
3) 드롭다운 연결
데이터 유효성(목록): =
    <고객목록 앵커>
#

버전·호환성 고려 사항

  • 동적 배열 미지원 버전에서 파일을 열면 스필 수식이 정상 동작하지 않는다. 분석용은 365/2021 이상에서 유지한다.
  • 공유 통합문서나 엄격한 호환 모드(.xls)에서는 스필이 제한될 수 있다. 형식을 .xlsx로 저장한다.
  • 외부 링크가 많은 파일은 계산 순환·지연으로 가짜 스필 오류처럼 보일 수 있다. 불필요 링크를 정리한다.

자동화 점검 스크립트(고급)

VBA를 활용해 스필 경로의 병합·개체·값 존재 여부를 일괄 점검할 수 있다. 

    
Sub CheckSpillObstacles() Dim rng As Range, c As Range, shp As Shape Set rng = Selection If rng Is Nothing Then Exit Sub Dim msg As String: msg = "" For Each c In rng If c.MergeCells Then msg = msg & "병합: " & c.Address & vbCrLf If Len(c.Value2) > 0 Then msg = msg & "값 존재: " & c.Address & vbCrLf Next c For Each shp In ActiveSheet.Shapes If Not Intersect(shp.TopLeftCell, rng) Is Nothing Then msg = msg & "개체: " & shp.Name & vbCrLf Next shp If msg = "" Then msg = "스필 경로에 장애 없음" MsgBox msg End Sub

문제 재발 방지 규칙

  1. 스필 결과를 다른 계산의 입력으로 사용할 때는 항상 # 로 전체 범위를 참조한다.
  2. 표 내부에는 동적 배열 수식을 넣지 않는다. 표 밖에서 계산하고 표는 참조만 한다.
  3. 전체 열/행 참조는 금지하고, 마지막 행 탐지·동적 이름 범위를 사용한다.
  4. 레이아웃에 병합을 사용하지 않는다. 시각적 정렬은 서식으로 대체한다.
  5. 시트 개체는 데이터 영역 밖에 배치하여 계산과 충돌하지 않게 한다.

빠른 솔루션 레시피 

    
1) 앵커 셀 경고 아이콘 확인
2) 윤곽선 내 모두 지우기
3) 병합 해제 및 개체 이동
4) 표 내부 수식이면 표 밖으로 이동
5) 전체 열 참조를 동적 경계로 교체
6) 다시 계산(F9) 후 스필 확인

FAQ

표 내부에서 동적 배열을 써야 한다면 방법이 없나?

표 셀에 직접 입력하는 대신 표 밖 셀에서 스필시키고, 표 열에 =앵커# 로 연결한다. 표는 결과를 참조만 한다.

스필 결과를 다른 시트에서 안정적으로 참조하려면?

항상 앵커 주소에 # 를 붙인다. 예: =Sheet1!A1# 형태로 전체를 가져오면 행·열 수 변화에도 안전하다.

동적 배열이 너무 커서 스필이 막힌다. 대안은?

TAKE / DROP / INDEX 로 결과 크기를 제한한다. 필요 시 WRAPROWS / WRAPCOLS 로 2차원 재배치하여 충돌을 피한다.

#SPILL!이 가끔만 발생한다. 왜 그런가?

외부 연결·순환 참조·수동 계산 등이 원인일 수 있다. 자동 계산으로 전환하고 순환을 제거하며, 스필 경로를 항상 비워두는 레이아웃으로 수정한다.

스필을 한 셀 값만 쓰고 싶다. 어떻게 하나?
스필을 한 셀 값만 쓰고 싶다. 어떻게 하나?

INDEX 로 특정 요소를 지정하거나 @ 연산자를 사용해 단일값으로 강제한다.