Jan의 회사는 최근 Office 365 ProPlus로 업그레이드했습니다. 그것과 함께 #SPILL! LOT를 사용하는 VLOOKUP을 사용할 때 오류가 발생했습니다. 이제 그는 필터와 함께 VLOOKUP을 사용할 수 없습니다. Jan은 이전 VLOOKUP에서 가져온 # N / A를 정렬 및 / 또는 제거하는 것이 쉬울 것이라는 것을 알고 있지만이 오류로 인해 많은 추가 시간과 노력이 필요합니다. 그는이 “기능”을 끄는 방법이 있는지 궁금합니다.

아주 가까운 과거에 Microsoft는 워크 시트 계산 방식을 변경했습니다.

이것은 엄청난 변화 였고 다른 곳에서 읽었을 것입니다. 다음은 변경 사항에 대한 훌륭한 정보를 제공하는 기사입니다.

https://exceljet.net/dynamic-array-formulas-in-excel

기사는 다소 길다. 포함 된 정보를 소화하기 위해 시간을 할애하고 싶을 것입니다. 하지만 Excel에서 많은 수식을 만드는 경우 그렇게하는 것이 좋습니다. 프로그램 변경은 결국 이해해야한다는 것을 의미합니다.

기본적으로 Microsoft는 배열 함수의 개념을 없애고 (아직 작동하지만) 대신 VLOOKUP을 포함한 거의 모든 함수가 값 배열을 반환하도록 허용했습니다. 반환 된 값의 배열이 사용 가능한 공간에 맞지 않으면 새로운 #SPILL! 오류.

솔직히 대답은 #SPILL을 비활성화하는 것이 아닙니다! 오류 정말 그렇게 할 방법이 없습니다. 대답은 Excel이 계산할 때 현재 수행중인 작업을 이해 한 다음 그에 따라 수식을 수정하는 것입니다.

예를 살펴 보겠습니다. 간단한 2 열 데이터 집합에 항목과 가격을 나열하는 워크 시트가 있다고 가정 해 보겠습니다. 그런 다음 오른쪽에 일부 항목을 입력하고 VLOOKUP 함수를 사용하여 각 항목과 관련된 가격을 반환합니다. 이 통합 문서를 이전 버전의 Excel (2019 이전 버전)에서 열면 훌륭한 결과를 얻을 수 있습니다. (그림 1 참조)

image

그림 1. Excel 2010의 간단한 VLOOKUP 수식.

이 스크린 샷은 Excel 2010 시스템을 사용하여 촬영되었지만 Excel 2016 또는 Excel 2019에서 본 경우에도 동일하게 작동합니다.이 예제에서는 셀 F2의 VLOOKUP 수식 (F2 셀이 수식 표시 줄에 표시됨)에 유의하십시오. 선택됨) F2 : F8 범위로 복사됩니다. VLOOKUP 함수는 테이블에서 단일 값을 반환하므로 원하는 결과를 얻을 수 있습니다.

이제 Office 365와 함께 제공되는 Excel 버전에서 동일한 수식을 사용하여 동일한 통합 문서를 만드는 경우 어떻게되는지 살펴 보겠습니다.이 경우 오류가 표시됩니다. (그림 2 참조)

image

그림 2. 최신 버전의 Excel에있는 동일한 간단한 VLOOKUP 수식.

#SPILL! 오류. 이 오류는 VLOOKUP 수식이 이제 하나 이상의 값을 반환 할 수 있기 때문에 발생합니다. 실제로 VLOOKUP의 첫 번째 매개 변수에서 셀 범위를 사용하면 이제 해당 범위의 각 셀에 대한 값을 반환합니다. 따라서 첫 번째 매개 변수에 E2 : E8 범위를 사용하면 VLOOKUP이 7 개의 값을 반환합니다. 즉, 자동으로 값 배열을 반환합니다. 이러한 값을 모두 표시 할 수없는 경우 #SPILL! 오류. 그래서 #SPILL! 셀 F2 : F7의 오류; 그 아래에는 해당 수식에서 반환 된 모든 값이 표시되지 않도록하는 요소가 있습니다. 표시를 중지하는 셀 아래에 아무것도 없기 때문에 셀 F8에 오류가 표시되지 않습니다.

그래서 어떻게 고칠까요? 실제로 문제를 해결할 수있는 세 가지 방법이 있습니다.

이 특정 예에서 가장 쉬운 방법은 F3 : F8 셀의 모든 항목을 삭제하는 것입니다. 이렇게하면 셀 F2의 수식이 “유출”됩니다.

그 아래의 나머지 셀에 올바르게.

두 번째 방법은 셀 F2의 수식을 변경하는 것이므로 다음과 같이 표시됩니다.

=VLOOKUP(@E$2:E$8,A$2:B$19,2)

첫 번째 매개 변수 바로 앞에 @ 기호를 사용합니다. 이렇게하면 VLOOKUP 수식이 단일 값만 반환하도록 Excel에 알립니다. 수식을 조정하는 다른 방법은 셀 F2에 다음과 같이 표시하는 것입니다.

=VLOOKUP(E2,A$2:B$19,2)

이제 수식을 셀 F2에서 F2 : F8의 전체 범위로 복사 할 수 있으며 문제가 없습니다. 왜? (다시) 이러한 인스턴스의 VLOOKUP은 값 배열이 아닌 단일 값만 반환하기 때문입니다.

결론은 공식을 변경하는 가장 좋은 방법은 (1)

반환하도록 VLOOKUP을 요청하는 값의 전체 배열 표시를 차단하는 것이 없는지 확인하거나 (2) 단일 셀만 참조하도록 첫 번째 매개 변수를 수정합니다.

_ExcelTips_는 비용 효율적인 Microsoft Excel 교육을위한 소스입니다.

이 팁 (13750)은 Office 365의 Microsoft Excel Excel에 적용됩니다.