Excel의 #SPILL 오류란 무엇이며 해결 방법은 무엇입니까?

이 문서는 Excel 365에서 #SPILL 오류의 모든 원인과 해결 방법을 이해하는 데 도움이 됩니다.

#엎지르다! 여러 계산 결과를 생성하는 수식이 유출 범위에 출력을 표시하려고 하지만 해당 범위에 이미 다른 데이터가 포함되어 있을 때 주로 발생하는 새로운 종류의 Excel 오류입니다.

차단 데이터는 텍스트 값, 병합된 셀, 일반 공백 문자 또는 결과를 반환할 충분한 장소가 없는 경우를 포함하는 모든 것일 수 있습니다. 해결 방법은 차단 데이터의 범위를 지우거나 데이터 유형이 포함되지 않은 빈 셀 배열을 선택하는 것입니다.

동적 배열 공식은 결과를 여러 셀 또는 배열로 출력하는 공식이기 때문에 일반적으로 동적 배열 공식을 계산할 때 유출 오류가 발생합니다. Excel에서 이 오류가 발생하는 원인과 해결 방법을 더 자세히 살펴보고 이해해 보겠습니다.

유출 오류의 원인은 무엇입니까?

2018년에 동적 배열이 출시된 이후 Excel 수식은 한 번에 여러 값을 처리하고 둘 이상의 셀에 결과를 반환할 수 있습니다. 동적 배열은 수식이 단일 셀에 입력된 수식을 기반으로 워크시트의 셀 범위에 여러 결과를 반환할 수 있도록 하는 크기 조정 가능한 배열입니다.

동적 배열 수식이 여러 결과를 반환하는 경우 이러한 결과는 자동으로 인접 셀로 넘어갑니다. 이 동작을 Excel에서는 '흘림'이라고 합니다. 그리고 결과가 유출되는 셀의 범위를 '유출 범위'라고 합니다. 유출 범위는 소스 값에 따라 자동으로 확장되거나 축소됩니다.

수식이 여러 결과로 유출 범위를 채우려고 하지만 해당 범위의 무언가에 의해 차단되면 #SPILL 오류가 발생합니다.

Excel에는 이제 동적 배열 기능을 사용하여 문제를 해결하는 9가지 기능이 있습니다. 여기에는 다음이 포함됩니다.

순서
필터
바꾸어 놓다
종류
정렬 기준
란다레이
독특한
검색
XMATCH

동적 배열 수식은 'Excel 365'에서만 사용할 수 있으며 현재 오프라인 Excel 소프트웨어(예: Microsoft Excel 2016, 2019)에서는 지원되지 않습니다.

유출 오류는 데이터 방해로 인해 발생할 뿐만 아니라 #Spill 오류가 발생할 수 있는 몇 가지 이유가 있습니다. #SPILL이 발생할 수 있는 다양한 상황을 살펴보겠습니다! 오류 및 수정 방법.

유출 범위는 비어 있지 않습니다

유출 오류의 주요 원인 중 하나는 유출 범위가 비어 있지 않다는 것입니다. 예를 들어, 10개의 결과를 표시하려고 하지만 유출 영역의 셀에 데이터가 있는 경우 수식은 #SPILL!을 반환합니다. 오류.

예 1:

아래 예에서는 셀의 세로 범위(B2:B5)를 가로 범위(C2:F2)로 변환하기 위해 셀 C2에 TRANSPOSE 함수를 입력했습니다. 열을 행으로 전환하는 대신 Excel에서 #SPILL! 오류.

수식 셀을 클릭하면 아래와 같이 결과를 표시하는 데 필요한 유출 영역/범위(C2:F2)를 나타내는 파란색 파선 테두리가 표시됩니다. 또한 느낌표가 있는 노란색 경고 표시를 볼 수 있습니다.

오류의 원인을 이해하려면 오류 옆에 있는 경고 아이콘을 클릭하고 회색으로 강조 표시된 첫 번째 줄의 메시지를 확인하십시오. 보시다시피 여기 'Spill range is not blank'라고 표시되어 있습니다.

여기서 문제는 유출 범위 D2 및 E2의 셀에 텍스트 문자(비어 있지 않음)가 있으므로 오류가 발생한다는 것입니다.

해결책:

솔루션은 간단합니다. 유출 범위에 있는 데이터를 지우거나(이동 또는 삭제) 방해가 없는 다른 위치로 공식을 이동합니다.

차단을 삭제하거나 이동하는 즉시 Excel에서 수식 결과로 셀을 자동으로 채웁니다. 여기서 D2와 E2의 텍스트를 지우면 수식은 의도한 대로 열을 행으로 바꿉니다.

예 2:

아래 예에서 유출 범위가 비어 있는 것처럼 보이지만 수식은 여전히 유출! 오류. 유출이 실제로 비어 있지 않기 때문에 셀 중 하나에 보이지 않는 공백 문자가 있습니다.

빈 셀에 숨어 있는 공백 문자 또는 기타 보이지 않는 문자를 찾기가 어렵습니다. 원치 않는 데이터가 포함된 이러한 셀을 찾으려면 오류 플로티(경고 표시)를 클릭하고 메뉴에서 '차단하는 셀 선택'을 선택하면 방해하는 데이터가 포함된 셀로 이동합니다.

보시다시피 아래 스크린샷에서 E2 셀에는 두 개의 공백 문자가 있습니다. 해당 데이터를 지우면 적절한 출력을 얻을 수 있습니다.

때로 보이지 않는 문자는 셀의 채우기 색과 같은 글꼴 색으로 서식이 지정된 텍스트이거나 숫자 코드 ;;;로 서식이 지정된 셀 값 사용자 지정일 수 있습니다. ;;;로 셀 값의 서식을 사용자 지정하면 글꼴 색상이나 셀 색상에 관계없이 해당 셀의 모든 항목이 숨겨집니다.

유출 범위는 병합된 세포를 포함합니다

가끔은 #SPILL! 유출 범위에 병합된 셀이 포함된 경우 오류가 발생합니다. 동적 배열 수식은 병합된 셀에서 작동하지 않습니다. 이 문제를 해결하려면 유출 범위의 셀 병합을 해제하거나 병합된 셀이 없는 다른 범위로 수식을 이동하기만 하면 됩니다.

아래 예에서 유출 범위가 비어 있어도(C2:CC8) 수식은 유출 오류를 반환합니다. 셀 C4와 C5가 병합되기 때문입니다.

병합된 셀이 오류의 원인인지 확인하려면경고 표시 및 원인 확인 - '유출 범위에 셀이 병합되었습니다'.

해결책:

셀 병합을 해제하려면 병합된 셀을 선택한 다음 '홈' 탭에서 '병합 및 가운데' 버튼을 클릭하고 '셀 병합 해제'를 선택합니다.

큰 스프레드시트에서 병합된 셀을 찾는 데 어려움이 있는 경우 경고 표시 메뉴에서 '차단되는 셀 선택' 옵션을 클릭하여 병합된 셀로 이동합니다.

표의 유출 범위

유출된 배열 수식은 Excel 테이블에서 지원되지 않습니다. 동적 배열 수식은 단일 개별 셀에만 입력해야 합니다. 유출된 배열 수식을 테이블에 입력하거나 유출 영역이 테이블에 떨어지면 유출 오류가 발생합니다. 이 경우 표를 일반 범위로 변환하거나 수식을 표 외부로 이동해 보십시오.

예를 들어 Excel 표에 다음과 같은 범위 수식을 입력하면 수식 셀뿐만 아니라 테이블의 모든 셀에 넘침 오류가 발생합니다. Excel은 표에 입력된 모든 수식을 표의 열에 있는 모든 셀에 자동으로 복사하기 때문입니다.

또한 수식이 테이블에 결과를 유출하려고 하면 유출 오류가 발생합니다. 아래 스크린샷에서 유출 영역은 기존 테이블 내에 있으므로 유출 오류가 발생합니다.

이 오류의 원인을 확인하려면 경고 기호를 클릭하고 오류 이유를 확인하십시오 - '표의 유출 범위'

해결책:

오류를 수정하려면 Excel 표를 범위로 되돌려야 합니다. 그렇게 하려면 표 내의 아무 곳이나 마우스 오른쪽 버튼으로 클릭하고 '표'를 클릭한 다음 '범위로 변환' 옵션을 선택합니다. 또는 테이블 내 아무 곳이나 마우스 왼쪽 버튼으로 클릭한 다음 '테이블 디자인' 탭으로 이동하여 '범위로 변환' 옵션을 선택할 수 있습니다.

유출 범위를 알 수 없음

Excel에서 유출된 배열의 크기를 설정할 수 없는 경우 유출 오류가 발생합니다. 경우에 따라 공식을 통해 각 계산 단계 간에 동적 배열의 크기를 조정할 수 있습니다. 동적 배열의 크기가 계산이 통과하는 동안 계속 변경되고 균형이 맞지 않으면 #SPILL! 오류.

이러한 유형의 유출 오류는 일반적으로 RAND, RANDARRAY, RANDBETWEEN, OFFSET 및 INDIRECT 기능과 같은 휘발성 기능을 사용할 때 발생합니다.

예를 들어 B3 셀에서 아래 수식을 사용하면 유출 오류가 발생합니다.

=SEQUENCE(RANDBETWEEN(1, 500))

예제에서 RANDBETWEEN 함수는 숫자 1에서 500 사이의 임의의 정수를 반환하며 출력은 계속 변경됩니다. 그리고 SEQUENCE 함수는 스필 배열에서 생성할 값의 수를 모릅니다. 따라서 #SPILL 오류가 발생합니다.

경고 기호 – '유출 범위를 알 수 없음'을 클릭하여 오류의 원인을 확인할 수도 있습니다.

해결책:

이 수식의 오류를 수정하려면 계산에 다른 수식을 사용하는 것이 유일한 선택입니다.

유출 범위가 너무 큽니다.

때때로 워크시트가 처리하기에는 너무 큰 범위를 출력하는 수식을 실행할 수 있으며 워크시트의 가장자리를 넘어 확장될 수 있습니다. 그런 일이 발생하면 #SPILL을 얻을 수 있습니다! 오류. 이 문제를 해결하려면 전체 열 대신 특정 범위 또는 하나의 셀을 참조하거나 '@' 문자를 사용하여 암시적 교차를 활성화할 수 있습니다.

아래 예에서 A열의 매출 수치의 20%를 계산하고 B열의 결과를 반환하려고 하지만 그 대신 Spill 오류가 발생합니다.

B3의 수식은 A3 값의 20%를 계산한 다음 A4 값의 20%를 계산하는 식입니다. 백만 개 이상의 결과( 1,048,576개)를 생성하고 B3 셀에서 시작하여 B 열에 모두 유출되지만 워크시트의 끝에 도달합니다. 모든 출력을 표시할 공간이 충분하지 않으므로 #SPILL 오류가 발생합니다.

보시다시피 이 오류의 원인은 – '유출 범위가 너무 큽니다'입니다.