앞선 글에서 배열수식은 "여러 개의 계산 결과를 하나의 셀에 압축해서 담아내는 방식"이었다고 설명했다. 그런데 만약 그 결과를 굳이 압축하지 않고, 계산된 값의 개수만큼 그대로 여러 셀에 펼쳐 보여준다면 어떨까? 이 발상에서 출발한 것이 바로 동적 배열, 흔히 '스필(Spill)'이라고 부르는 기능이다.
예전 엑셀이 결과를 펼쳐 보여주지 못했던 이유
2018년 이전의 엑셀에서는 한 셀에 입력한 수식은 오직 그 셀 하나에만 값을 반환한다는 규칙이 있었다. A1에 수식을 입력했는데 계산 결과가 5개라면, 나머지 4개는 갈 곳이 없어 SUM 같은 함수로 강제로 하나의 값으로 합쳐야 했다. 여러 개의 결과를 여러 셀에 나눠 보여주려면 A1부터 A5까지를 미리 선택한 뒤 배열수식을 입력하는 번거로운 과정을 거쳐야 했고, 이마저도 중간에 데이터가 몇 개로 늘어날지 미리 알아야 한다는 한계가 있었다.
스필의 원리 — 결과 개수만큼 자동으로 펼치기
동적 배열 방식에서는 이 제약이 사라진다. A1에 결과가 5개인 수식을 입력하면, 엑셀은 A1은 물론 A2, A3, A4, A5까지 필요한 만큼 자동으로 값을 채워 넣는다. 이렇게 계산 결과가 입력한 셀을 넘어 주변 셀로 자연스럽게 퍼져나가는 현상을 물이 흘러넘치는 모습에 빗대어 '스필'이라고 부른다.
=SEQUENCE(5)이 수식을 A1에 입력하면 1부터 5까지의 숫자가 A1:A5에 자동으로 펼쳐진다. 이때 실제로 수식이 입력된 셀은 A1 하나뿐이며, A2부터 A5는 A1의 계산 결과가 흘러넘친 '유령 셀' 같은 존재다. 그래서 A2를 클릭해서 직접 값을 수정하려고 하면 편집이 되지 않고, 대신 A1의 수식을 바꿔야 전체 결과가 함께 바뀐다.
스필 범위를 통째로 참조하는 # 연산자
A1의 결과가 몇 칸으로 퍼질지 모르는 상태에서, 이 전체 결과를 다른 수식에서 참조하고 싶을 때는 # 기호를 사용한다.
=SUM(A1#)여기서 A1#은 "A1에서 시작해 스필된 전체 범위"를 뜻한다. 나중에 원본 데이터가 바뀌어 스필 범위가 3칸에서 7칸으로 늘어나도, A1#을 참조하는 수식은 별도로 범위를 수정할 필요 없이 항상 최신 범위를 자동으로 따라간다. 이것이 동적 배열이 '동적'이라는 이름을 갖게 된 이유이기도 하다.
스필이 막히는 경우 — #SPILL! 오류
스필은 결과가 퍼져나갈 자리가 비어 있어야 정상적으로 작동한다. 만약 A2나 A3처럼 결과가 채워져야 할 자리에 이미 다른 값이 입력되어 있다면, 엑셀은 어느 칸을 덮어써야 할지 판단할 수 없으므로 A1에 #SPILL! 오류를 표시한다. 이 오류를 만나면 원인은 대부분 단순한데, 스필 범위 안쪽 어딘가에 미리 입력된 값이 남아 있는 경우다. 해당 셀들을 비워주면 오류는 바로 해결된다.
Q. 스필된 셀 하나만 따로 복사해서 다른 곳에 붙여넣을 수 있나요?
값으로 붙여넣기(수식이 아닌 값만)를 사용하면 가능하다. 수식 자체를 복사하려면 원본 셀(스필이 시작되는 첫 셀)의 수식을 복사해야 한다.
Q. FILTER나 UNIQUE 같은 함수도 스필과 관련이 있나요?
그렇다. FILTER, UNIQUE, SORT, SORTBY 같은 함수들은 애초에 결과 개수가 몇 개가 될지 미리 알 수 없는 함수들이라, 동적 배열(스필) 원리가 없었다면 존재하기 어려웠을 함수들이다.
마무리
스필은 결국 "결과의 개수를 미리 정해두지 않아도, 엑셀이 알아서 필요한 만큼 셀을 채워준다"는 유연함을 위해 만들어진 구조다. 다음 글에서는 이 스필 원리 위에서 동작하는 대표 함수인 FILTER가, 조건에 맞는 데이터를 걸러내는 방식을 데이터베이스의 WHERE절과 비교하며 살펴본다.