초보자를 위한 엑셀 함수 자동화 : '#' 기호 완벽 정리

실습 가이드

엑셀 2021 버전 이후 '분산 범위' 기능이 새롭게 도입되었습니다. 이전 버전에서는 함수나 수식의 결과값을 고정된 범위에만 배열로 반환할 수 있어 배열 수식을 활용하는 데 많은 제약이 따랐습니다.

하지만 엑셀 최신 버전에 추가된 '분산 범위'와 '동적 배열 함수'를 활용하면, 기존에 함수만으로 구현하기 어려웠던 다양한 작업을 보다 쉽고 편리하게 자동화할 수 있습니다.

이번 강의에서는 분산 범위를 제대로 활용하기 위한 핵심 기호인 #(해시) 기호 활용법을 단계별로 살펴봅니다.

분산 범위를 동적으로 참조하는 "# 기호"

# 기호는 함수나 수식의 결과로 반환되는 '분산 범위'를 동적으로 참조하는 기호이며, 분산 범위를 지원하는 엑셀 2021 이후 버전부터 사용할 수 있습니다.

오빠두Tip : # 기호가 사용된 파일을 엑셀 이전 버전에서 실행하면 =ANCHORARRAY 함수로 자동 변환됩니다. 파일을 처음 열었을 때는 이전에 저장된 값이 정상적으로 보이지만, 데이터가 변경되거나 수식이 다시 계산되면 #NAME? 오류가 반환됩니다.

1. 국가 고유목록 만들기 : 먼저 UNIQUE 함수를 사용해 국가의 고유값을 구해보겠습니다. 예제파일을 실행한 후, H3셀을 선택하고 아래와 같이 UNIQUE 함수를 작성합니다.
   =UNIQUE(B3:B20)

   

2. UNIQUE 함수를 입력하면 아래와 같이 국가의 고유 목록이 범위로 반환됩니다. 반환된 범위에서 임의의 셀을 하나 선택하면, 분산 범위 주변에 파란색 테두리가 표시되어 함수가 반환한 범위를 한눈에 확인할 수 있습니다.

   

   오빠두Tip : 엑셀 UNIQUE 함수와 SORT 함수의 기초 사용법은 아래 5분 기초 영상강의에서 자세히 정리했습니다.
   
3. # 기호로 범위 선택하기 : 예제파일에서 I3셀을 선택한 후, 국가 고유값 목록의 개수를 세어보겠습니다. 아래와 같이 COUNTA 함수를 작성한 다음, 고유값 목록이 반환되는 시작 셀 뒤에 # 기호를 추가하면 분산 범위가 동적으로 선택됩니다. 범위를 선택한 후 수식을 입력하면 고유값의 개수가 즉시 계산됩니다.
   =COUNTA(H3#)

   

4. 이제 원본 데이터에서 국가 이름을 변경하면, H3셀에 작성한 UNIQUE 함수의 결과와 그 결과를 참조(H3#)하는 개수가 동시에 동적으로 갱신됩니다.
5. # 기호 사용시 주의사항 : # 기호는 함수나 수식으로 반환되는 분산 범위만 동적으로 선택할 수 있습니다. 따라서 일반 범위에는 # 기호를 적용할 수 없습니다. 예를 들어, 예제파일에서 일반 범위의 시작 셀 뒤에 # 기호를 추가해도 아래와 같이 범위가 선택되지 않습니다.

   

분산범위 활용성을 200% 올려주는 '표' 기능

엑셀 표 기능을 분산 범위와 함께 사용하면, 새로 추가되는 데이터를 자동으로 함수에 반영하는 자동화 보고서를 만들 수 있습니다. 데이터가 추가될 때마다 수식을 직접 수정할 필요가 없어 업무 생산성을 크게 높일 수 있습니다.

1. 일반 범위의 제한사항 : 이전 단계에서 작성한 UNIQUE 함수는 참조 범위로 "B3:B20"을 선택했습니다. 이 경우 B3:B20 범위 안에서 데이터가 변경되면 UNIQUE 함수의 결과값도 그에 맞춰 갱신됩니다.

   

2. 하지만 선택한 범위의 바로 아래인 B21셀에 새로운 데이터를 추가하면, "B3:B20"은 고정된 범위이므로 새로 추가된 데이터를 인식하지 못하는 한계가 있습니다. 이러한 한계를 해결하려면 범위를 표로 변환해, 새로운 데이터가 추가될 때마다 동적으로 인식되는 범위를 만들어야 합니다.

   

3. 범위를 표로 변경하기 : 범위에서 임의의 셀을 선택한 후, 단축키 Ctrl + A 를 누르면 인접한 모든 데이터가 한 번에 선택됩니다. 범위를 선택한 다음 [삽입] 탭 - [표]를 클릭하여 표 만들기 대화상자를 실행합니다.
   
4. 표 만들기 대화상자가 실행되면 '머리글 포함'을 체크한 후 [확인] 버튼을 클릭해 범위를 표로 변환합니다.
   
5. 표 이름 지정하기 : 표를 선택한 후 [테이블 디자인] 탭으로 이동하면 왼쪽의 [표 이름] 입력란에서 표의 이름을 원하는 값으로 변경할 수 있습니다. 이번 강의에서는 표 이름을 '제품목록'으로 지정합니다.

   

   오빠두Tip : 표 기능은 앞으로 파워쿼리와 파이썬 등 다양한 기능을 활용하기 위해 반드시 익혀두어야 하는 핵심 기능입니다. 표 기능의 기초 설명은 아래 5분 영상 강의를 참고하세요.
   

예제1 - 자동으로 확장하는 목록상자

# 기호를 활용하면 데이터가 변할 때마다 목록이 실시간으로 확장·축소되는 목록상자를 만들 수 있습니다.

1. 국가 고유 목록 만들기 : 예제파일을 실행한 후, 국가별 고유 목록을 생성합니다. H3셀에 아래와 같이 UNIQUE 함수를 입력합니다. UNIQUE 함수에 들어가는 표 이름은 작성한 표에 따라 달라질 수 있습니다.
   =UNIQUE(제품목록[국가])

   

2. 목록상자를 추가할 N2셀을 선택한 후 [데이터] 탭 - [데이터 유효성 검사]를 클릭해 데이터 유효성 대화상자를 실행합니다. 대화상자가 실행되면 제한 대상으로 '목록'을 선택합니다.
   
3. 자동 확장 목록 만들기 : 원본 입력창에 아래와 같이 H3#으로 범위를 지정한 후 [확인] 버튼을 클릭합니다. (H3셀은 UNIQUE 함수가 작성된 셀이며, H3#은 UNIQUE 함수로 반환되는 분산 범위를 동적으로 선택합니다.)
   =H3#

   

4. 이제 표에 새로운 데이터를 추가하거나 기존 데이터를 변경하면 목록이 자동으로 확장·축소되는 것을 확인할 수 있습니다.

   

   오빠두Tip : UNIQUE 함수와 SORT 함수를 함께 활용하면 오름차순·내림차순으로 정렬된 목록을 만들 수 있습니다.
   

예제2 - 조건 개수에 제한 없는 다중조건 목록

UNIQUE 함수와 FILTER 함수를 함께 사용하면 조건 개수에 제한 없는 다중 조건 목록을 만들 수 있습니다.

1. 조건을 충족하는 목록 만들기 : 예제1에서 추가한 목록에서 임의의 국가를 선택합니다.
2. K3셀을 선택한 후 아래와 같이 FILTER 함수를 작성합니다. FILTER 함수는 지정한 범위에서 조건을 충족하는 값만 필터링하여 반환합니다. 아래와 같이 작성하면 표의 '국가' 필드 값이 N2셀에서 선택한 값과 동일한 행의 제조사 목록을 반환합니다.
   =FILTER(제품목록[제조사],제품목록[국가]=N2)
3. FILTER 함수가 반환한 목록에는 중복값이 포함될 수 있으므로, UNIQUE 함수로 감싸 고유값만 반환하면 조건을 충족하는 목록이 완성됩니다. K3셀에 작성한 FILTER 함수를 아래와 같이 UNIQUE 함수로 감싸줍니다.
   =UNIQUE(FILTER(제품목록[제조사],제품목록[국가]=N2))

   

4. 다중 조건 목록 만들기 : 마지막으로 목록상자를 생성합니다. 제조사 목록을 만들 N3셀을 선택한 후 [데이터] 탭 - [데이터 유효성 검사]에서 제한 대상으로 '목록'을 선택하고 원본 범위로 "=K3#"을 입력합니다.

   

5. [확인] 버튼을 클릭해 목록을 적용하면, N2셀에서 선택한 국가에 해당하는 제조사만 나열되는 다중 조건 목록이 완성됩니다.

   

예제3 - 여러 값을 한 번에 검색하는 VLOOKUP 함수

실무에서 자주 사용하는 VLOOKUP 함수와 SUMIF 함수에 # 기호를 활용하면 대량의 데이터를 더욱 편리하게 분석할 수 있습니다.

1. 차종별 통관번호 일괄 검색하기 : 예제1과 예제2에서 생성한 목록에서 임의의 값을 선택한 후, M6셀에 아래와 같이 선택한 제조사의 자동차 목록을 반환하는 FILTER 함수를 작성합니다.
   =FILTER(제품목록[자동차명],제품목록[제조사]=N3)

   

2. N6셀을 선택한 후 아래와 같이 VLOOKUP 함수를 작성합니다. 기존에는 찾을값에 대한 결과를 VLOOKUP 함수로 검색한 뒤 수식을 자동 채우기로 확장해야 했지만, 찾을값에 # 기호로 분산 범위를 입력하면 여러 항목의 VLOOKUP 검색 결과를 한 번에 출력할 수 있습니다.
   =VLOOKUP(M6#,통관번호,2,0)

   

   오빠두Tip : 분산 범위 영역에 기존 데이터가 작성되어 있을 경우 #SPILL!(또는 #분산!) 오류가 반환됩니다. 이 경우 분산 범위가 채워질 영역에 작성된 기존 데이터를 지우면 오류를 해결할 수 있습니다. 엑셀에서 발생하는 다양한 오류에 대한 자세한 설명은 아래 기초 영상강의를 참고하세요.