들어가기 전에
엑셀 데이터에서 행을 추가하다가 수식이 깨지는 경험을 해 보았다면, 셀 참조를 직접 수정하는 것의 번거로움을 잘 알 것이라 생각합니다. 이럴 때 # 기호를 사용하면 데이터가 늘어나거나 줄어드는 것에 따라 수식을 자동으로 조정하여 이 문제를 해결할 수 있습니다. "범위 분할 연산자(spill range operator)"라고도 부르는 # 기호를 Excel 수식에서 사용하는 방법을 소개합니다.
이 글은 아래 기사 내용을 토대로 작성되었습니다만, 필자의 개인 의견이나 추가 자료들이 다수 포함되어 있습니다.
- 원문: This one Excel symbol will change how you write formula
- URL: https://www.makeuseof.com/excel-symbol-change-how-write-formulas/
자동으로 배열 범위를 추적하는 #기호
# 기호는 지정한 셀부터 시작하여 동적 배열의 모든 셀을 참조합니다. =G2# 를 입력하면 Excel은 G2와 그 아래에 분산된 데이터가 포함된 모든 셀을 포함합니다.
SORT, FILTER, UNIQUE와 같은 Excel 함수와 잘 호환되어 표 크기를 조정하는 번거로움을 해소합니다 . 해시(#)로 범위를 참조하면 Excel에서 행 개수를 세지 않고도 전체 출력을 추적합니다.
하지만 함정이 있습니다. # 기호는 정적 데이터가 아닌 spill된 배열을 참조할 때만 작동합니다. A2에 "Product"라는 단어만 포함되어 있으면 A2#을 사용할 수 없습니다. 먼저 데이터를 spill해야 합니다.
좋은 방법은 데이터를 Excel 표로 변환하는 것입니다. 데이터 범위(A1:E11)를 선택하고 Ctrl + T를 누른 후 확인을 클릭합니다 . Excel에서는 이 범위에 "Table1"과 같은 이름이 지정됩니다. 이 경우에는 Table3이고, 새 행이 추가되면 자동으로 확장됩니다.
G2 셀에 다음을 입력합니다.
=Table3
전체 테이블의 스필된 복사본이 생성됩니다. 데이터는 G2에서 모든 행과 모든 열로 스필됩니다. Table3에 행을 추가하면 G2의 스필된 범위가 자동으로 업데이트됩니다.
이렇게 펼쳐진 배열을 사용하면 다음 수식을 다른 곳에서 사용할 수 있습니다.
=SORT(G2#, 4, -1)
G2# 참조에는 G2에서 넘어온 모든 행과 열이 포함됩니다. 4는 네 번째 열(가격)을 기준으로 정렬하며, -1은 내림차순을 의미합니다. 이제 테이블에 제품을 추가하면 SORT 수식이 즉시 해당 제품을 선택합니다.
# 기호는 연결된 수식에서 유용합니다. M3에 수식을 입력하여 정렬된 분산 배열을 만듭니다. 그런 다음 다른 셀에서 다음 수식을 사용하여 정렬된 데이터를 필터링합니다.
=FILTER(M3#, INDEX(M3#, , 2)="Electronics")
SORT 수식을 변경하면 FILTER 수식도 자동으로 업데이트되므로 별도로 변경할 필요가 없습니다.
'#분산!' 오류 이해 및 해결 방법
셀에 #분산!(#SPILL!) 이 표시되면 Excel에서 여러 결과를 반환하려고 했지만, 스필 범위를 가로막는 무언가가 있어서 반환할 수 없다는 의미입니다. 수식은 작동하지만 출력을 넣을 곳이 없는 것뿐입니다.
Excel에서 결과를 분할해야 하는 경로에 데이터, 서식 또는 병합된 셀이 있을 때 발생합니다. 셀 하나에 공백이 있어도 오류가 발생합니다.
G2에 =Table3을 입력하고 모든 고유 범주를 나열한다고 가정해 보겠습니다. M2 셀에 다음 수식을 사용하여 두 번째 열(범주)에서 고유 값을 추출합니다. Excel은 전자제품, 가구, 그리고 기타 범주의 네 가지 결과를 반환해야 한다고 판단합니다.
=UNIQUE(INDEX(G2#, , 2))
M3 또는 M4 셀에 마침표나 공백이라도 데이터가 포함되어 있으면 수식은 M2 셀을 넘어갈 수 없습니다. 이제 #SPILL! 오류를 클릭하면 Excel에서 오류를 막고 있는 셀을 정확하게 표시합니다. 결과가 표시되어야 하는 셀 주변에 점선 테두리가 표시되고, 막고 있는 셀은 강조 표시됩니다.
하지만 이러한 일반적인 Excel 오류는 쉽게 해결할 수 있습니다 . 차단된 셀을 지우세요. Excel에서 채우려는 범위를 선택하고 Delete 키를 누르세요 . 해당 셀이 비어 있으면 수식이 즉시 사라집니다.
필요한 데이터가 포함되어 있어서 해당 셀을 지울 수 없다면 수식을 다른 위치로 옮기세요. M열 대신 P열에 넣거나, 몇 행 아래 빈 공간에서 시작하세요.
셀을 병합하면 #SPILL! 오류도 발생합니다. 병합 경로에서 셀을 병합한 경우, 비어 있는 것처럼 보이더라도 병합을 해제해야 합니다. [홈] 탭 > [병합하고 가운데 맞춤]을 클릭하여 텍스트 병합을 해제하세요.
마치며
# 기호는 Microsoft 365(Excel 365) 또는 Excel 2021 이상에서만 작동합니다. Excel 2019 이하 버전을 사용하는 경우 동적 배열과 스필 범위 연산자를 사용할 수 없습니다. Microsoft 365 구독자라면 # 기호가 이미 활성화되어 있습니다. 별도의 활성화나 설정 변경은 필요 없습니다. SORT, FILTER, UNIQUE와 같은 동적 배열 함수를 사용하기 시작하면 자동으로 작동합니다.
'Excel' 카테고리의 다른 글
| 매주 몇 시간씩 절약할 수 있는 잘 알려지지 않은 엑셀 기능 5가지 (0) | 2026.04.09 |
|---|---|
| 파워 쿼리를 사용하여 지저분한 Excel 데이터를 정리하는 4가지 방법 (0) | 2026.04.08 |
| 피벗 테이블로 고생하기 전에 이 Excel 함수를 알았더라면 (0) | 2026.04.03 |
| Excel의 배열 상수에 대해 알아야 할 모든 것 (0) | 2026.04.02 |
| Excel에서 여전히 IF를 중첩 사용하고 있다면 이제 이렇게 하세요 (0) | 2026.03.31 |
