들어가기 전에
Excel의 대부분 수식은 단일 셀 결과를 반환합니다. 그러나 Excel 수식이 값 집합(a set of values)을 반환하는 경우 결과가 이웃 셀로 넘어갑니다. Excel 2019 이후 버전에서 지원하는 유출(Spill) 및 동적 배열 수식에 대해 소개합니다.
이 글은 아래 기사 내용을 토대로 작성되었습니다만, 필자의 개인 의견이나 추가 자료들이 다수 포함되어 있습니다.
- 원문: Everything You Need to Know About Spill in Excel
- URL: https://www.howtogeek.com/everything-you-need-to-know-about-spill-in-excel/
알아야 할 몇 가지 개념
유출(Spill)의 실제 사례를 살펴보기 전에 몇 가지 관련 Excel 용어에 대한 정의를 알아둘 필요가 있습니다.
- 유출(Spilling): Excel 수식이 값 집합(배열이라고도 함)을 반환하는 것
- 배열(Array): 셀 범위 내의 데이터 집합. 예를 들어 A1~A20 셀에 데이터가 포함되어 있는 경우 A1:A20이 범위이고 해당 범위 내의 데이터가 배열
- 함수(Function): 수식에 사용될 때 계산을 수행하는 미리 정의된 수식
- 수식(Formula): 함수, 셀 참조, 값, 연산자 및 이름을 등호(=) 기호 뒤에 함께 사용하면 결과를 반환하는 함수, 셀 참조, 값, 연산자 및 이름의 조합
- 동적 배열 수식(Dynamic array formula): 배열을 반환할 수 있는 수식
- 유출된 배열 수식(Spilled array formula): 현재 유출된 배열을 반환하는 수식
유출의 예
Excel에서 유출의 간단한 예를 살펴보겠습니다. 이 Excel 스프레드시트에는 6개의 퀴즈 팀과 1주에서 5주까지의 점수가 포함되어 있습니다.
아래 H2 셀에서 '=B2:B7'은 동적 배열 수식이므로 H2 셀에 입력하면 H2에서 H7까지를 포함하는 유출된 배열(spilled array)이 반환됩니다. 셀 H2를 선택하면 Excel에서 해당 셀 주위에 임시 테두리를 배치하여 유출된 배열임을 알려주는 것을 볼 수 있습니다.
H3에서 H7 셀에 값을 입력하면 Excel에서 방금 만든 유출된 배열을 방해하고 깨뜨려서 #SPILL! 오류가 발생합니다. #SPILL! 오류에 대해서는 이어서 자세히 설명하겠습니다.
OFFSET 함수를 사용하여 이러한 유형의 결과를 좀 더 실용적으로 활용해 볼까요. 예를 들어, Excel에서 셀 I1에 입력한 숫자에 따라 주어진 주에 각 팀의 점수를 알려주기를 원합니다.
이를 위해 I2 셀에 다음과 같이 입력합니다.
=OFFSET(A2,0,I1,6,1)
Excel이 A2 셀에서 시작하여 같은 행에 머물면서 I1 셀에 입력한 열 수만큼 이동하여 아래 6행, 가로 1열의 결과를 반환하기를 원하기 때문입니다. Excel은 6행 높이의 배열을 반환하기 때문에 셀 I2에만 수식을 입력했음에도 불구하고 결과가 셀 I1에서 I7까지 넘칩니다.
데이터 검색 테이블을 정리하기 위해 H 열에 팀 이름도 추가하려고 합니다. '=A2:A7'을 셀 H2에 입력하면 스프레드 배열이 생성됩니다. 또한 스프레드시트의 가독성을 높이기 위해 몇 가지 서식을 적용했습니다.
위와 같은 동적 배열 수식을 사용하면 데이터가 변경되거나 재배열되는 경우 유출된 배열이 그에 따라 조정된다는 이점이 있습니다.
마지막 예제에서는 XLOOKUP 함수를 사용하여 유출 배열을 생성해 보겠습니다. 제 목표는 셀 I1의 값에 따라 특정 팀의 전체 5주 동안의 결과 집합을 표시하는 것입니다. 먼저 동적 배열 수식 '=B1:F1'을 셀 H3에 입력하여 셀 H3에서 L3까지 주 번호의 유출 배열(spilled array)을 생성합니다.
이제 셀 I1에 A2~A7에 나열된 팀 이름이 포함된 드롭다운 목록을 만들겠습니다. 이를 위해 [데이터] 탭 - [데이터 유효성 검사]를 클릭하고 [허용] 필드에서 [목록]을 선택한 다음 A2~A7 셀을 선택하여 목록의 출처를 정의한 다음, [확인]을 클릭합니다.
마지막으로 XLOOKUP 함수를 사용하여 선택한 팀의 5주 동안의 점수를 반환할 수 있습니다. H3 셀에 다음과 같이 입력합니다.
=XLOOKUP(I1,A2:A7,B2:F7)
Excel에서 I1 셀의 팀 이름을 가져와 A2~A7 범위의 팀 이름과 일치시킨 다음 B~F 열에서 해당 배열을 반환하기를 원하기 때문입니다. 결과는 5열이므로 H3~L3 셀에 걸쳐 유출된 배열로 표시됩니다. 이 경우에는 셀 I1의 드롭다운 목록에서 Quizpicable Me 팀을 선택했습니다.
일반적으로 유출된 배열을 생성하는 다른 함수에는 FILTER, SORT, UNIQUE 및 RANDARRAY가 있습니다.
유출된 범위 참조하기
유출된 범위를 참조하려면 동적 배열 수식 내에서 범위 뒤에 유출된 범위 연산자(#)를 사용합니다. 위와 같은 예제를 사용하여 셀 H3에서 L3까지 유출된 배열을 기준으로 특정 팀의 총 점수를 표시하고자 합니다.
L4 셀에 '=SUM(H3#)'라고 입력하면 Excel에서 H3부터 유출된 범위의 배열을 합산하도록 지시합니다.
일반적인 #SPILL! 오류 수정
특정 상황에서는 Excel에서 #SPILL! 오류가 반환됩니다. 스프레드시트에서 이런 일이 발생하는 경우 주의해야 할 사항과 문제를 해결하기 위해 할 수 있는 일은 다음과 같습니다.
| #SPILL! 오류의 원인 | 오류를 수정하는 방법 |
| 유출된 배열이 병합된 셀을 가로지릅니다. | 병합된 셀을 선택하고 홈 탭의 맞춤 그룹에서 "병합 및 가운데 맞춤"을 클릭하여 셀 병합을 해제합니다. |
| 유출된 배열이 이동하려는 셀을 무언가가 막고 있습니다. | 유출된 배열을 차단하고 있는 셀에 있는 데이터를 삭제하거나 이동합니다. |
| 동적 배열 수식의 범위가 워크시트의 가장자리를 넘어 확장됩니다. | 동적 배열 수식을 검토하여 셀 참조가 정확한지 확인하세요. |
| 동적 배열 수식은 서식이 지정된 Excel 표 내에서 사용됩니다. | 동적 배열 수식을 서식이 지정된 테이블 외부의 위치로 이동합니다. 또는 테이블 내의 셀 중 하나를 선택하고 테이블 디자인 탭의 도구 그룹에서 "범위로 변환"을 클릭하여 서식이 지정된 Excel 테이블을 서식이 지정되지 않은 범위로 변환합니다. |
| 동적 배열 수식으로 인해 Excel의 메모리가 부족해졌습니다. | 동적 배열 수식 내에서 더 작은 범위를 참조하세요. |
| 동적 배열 수식에 휘발성 함수가 포함되어 있기 때문에 유출 범위는 알 수 없습니다. | 동적 배열 공식은 길이가 알려지지 않은 유출된 배열에서는 작동하지 않습니다. 불행히도 이 문제를 극복하는 유일한 방법은 가변 길이의 배열을 만드는 동적 배열 공식을 사용하지 않는 것입니다. |
마치며
동적 배열 수식이 전체 열을 참조하는 경우 TRIMRANGE 함수를 사용하여 Excel에서 빈 행을 제외하도록 할 수 있으며, 이는 기본적으로 필요한 셀만 포함되도록 데이터를 잘라내는 것입니다.
'Excel' 카테고리의 다른 글
| 시간을 절약할 수 있는 가장 간과하기 쉬운 Excel 함수 8가지 (3) | 2025.01.06 |
|---|---|
| 흔히 저지르기 쉬운 Excel 피벗 테이블 실수 11가지와 해결 방법 (18) | 2025.01.03 |
| 상황별 적절한 Excel 글꼴 비교 및 추천 (8) | 2024.12.30 |
| Excel에서 OFFSET 함수를 사용하는 방법 (19) | 2024.12.29 |
| 스프레드시트 마법사가 될 수 있는 엑셀 단축키 (25) | 2024.12.27 |
