엑셀 동적 배열과 스피링 오류, 범위를 주면 왜 VALUE! 나?
안녕하세요, IT 전문 블로거입니다.
오늘은 엑셀을 매일같이 손에 들고 사용하시는 분들이 특히나 당황스러운 상황을 경험했을 때를 설명하고자 합니다. 바로 엑셀의 동적 배열 (Dynamic Array) 기능과 함께 작동하면서 발생하는, 조금 복잡해 보일 수 있는 오류 현상에 대해 자세히 이야기 해보겠습니다.
2019 년 마이크로소프트 는 엑셀의 데이터 처리 방식을 전적으로 바꾼 핵심 업데이트 를 진행했습니다. 그중에서도 가장 큰 변화 중 하나가 바로 동적 배열 공식이었습니다. 이전부터 사용하던 엑셀에서 한 번에 데이터를 필터링하거나 정렬할 때, 결과를 하나하나 셀을 채워야 했던 방식과는 달랐습니다. 이제 결과를 자동으로 여러 행으로 확장시키는 ‘스필링 (Spilling)’ 기능이 탑재되었습니다. 예를 들어 FILTER 나 SORT 와 같은 최신 함수들은 범위를 입력하면 이를 받아들고 결과를 자연스럽게 퍼뜨립니다.
하지만, 모든 함수가 이 새로운 방식을 즉시 완벽하게 적응한 것은 아니었습니다. 특히 엑셀 초기 버전인 97 에서는 제공되던 분석 도구 파일 (Analysis ToolPak) 에 포함되었던 일부 함수들에는 아직도 과거의 논리가 남아있습니다. 대표적인 문제가 되는 예를 들어 보면, EOMONTH(월末日 함수) 나 EDATE(일정 기간 전후 날짜 함수) 처럼 특정 값을 기대하며 작동하도록 설계된 함수들이 있습니다.
우리는 보통 엑셀에서 범위인 B8:B10 같은 것을 하나의 입력값으로 주고 싶을 때마다, 엑셀은 자동으로 각각의 값에 대해 여러 번 공식을 실행합니다. 이를 전문어로는 리프팅 (Lifting) 이라고 부릅니다. 대부분의 신형 함수들은 기본적으로 이러한 스플링을 지원하지만, 오래된 함수들은 범위를 주면 에러를 냅니다.#VALUE! 오류가 발생합니다. 이는 함수 내부 코드가 ‘여러 줄의 데이터’ 대신 ‘하나의 값’을 요구하기 때문입니다.
물론 단순히 데이터를 입력하는 데 문제가 있는 것이 아니라, 엑셀이 범위 정보를 어떻게 해석해야 할지에 대한 설계상의 차이에서 기인합니다. 이 문제를 해결하는 가장 효과적인 방법이 있습니다. 정말 간단한 방법인데, 많은 사용자에게는 생소할 수 있습니다. 바로 범위를 참조할 때 앞에 플러스 기호 (+) 를 붙이는 것입니다.
=EOMONTH(B8:B10, 0) 대신에 =EOMONTH(+B8:B10, 0) 로 작성합니다. 이 플러스 기호는 에러를 일으키기 전에 먼저 범위의 배열 값을 평가하게 강제한 뒤 결과를 전달합니다. 그렇게 되면 리프팅과 스플링이 예상대로 작동하게 됩니다. 마치 수동으로 각 행에 대해 공식을 적용해 놓은 듯한 결과를 가져다줍니다. 물론 이러한 해결책은 기본적인 상황에서 유용합니다.
하지만 이 오류가 발생한 이유를 알지 못하고 복잡한 공식을 사용 중일 땐 좀 더 어렵습니다. 하나의 함수에만 문제가 있어도 전체 공식이 터져서 결과를 못 볼 수 있습니다. 특히 주의해야 할 것은 이 문제를 일으키는 함수들의 대부분이 엑셀 97 에서의 분석 도구 파일에서 유래한다는 사실입니다. 시간이 지나며 엑셀 핵심 기능이 되었지만, 내부 로직은 그 옛날 방식인 경우가 많습니다.
영향을 받는 주요 카테고리에는 금융 계산 관련 함수들이 다수 포함되어 있습니다. 예를 들어 이자 계산, 금리 환산, 할인 값 계산 등에 쓰이는 PMT 나 XIRR 같은 함수들도 여기에 포함될 수 있습니다. 또한 엔지니어링 함수들 중에서도 복소수 연산이나 바이너리 변환과 관련된 함수도 이 문제의 범위에 드는 경우가 많습니다.
결론적으로 말씀드리면, 만약 새로운 동적 배열을 사용하면서 갑자기 #VALUE! 오류가 발생하고, 입력 값이 정확하다고 확인되는데도 문제가 된다면, 혹시 범위를 사용하는 오래된 함수에 범위가 들어갔는지 의심해 보세요. 플러스 기호를 추가하는 이 작은 트릭 한 방이면 문제를 해결할 수 있으니 메모해 두시면 좋습니다.
기술의 발전은 항상 새로운 기능뿐만 아니라 구식과의 공존을 요합니다. 이해하고 올바르게 사용할 때 엑셀이 더 강력하게 작동합니다. 오늘 읽으신 내용을 바탕으로 당신의 워크시트가 조금 더 원활하도록 활용해 보시길 권장합니다. 이 글을 마치겠습니다.
이 글은 Exceljet – Quick, clean, and to the point의 기사를 바탕으로 작성되었습니다.
