초보자를 위한 엑셀 함수 자동화 : '#' 기호 완벽 정리
엑셀 # 기호 목차 바로가기
영상 강의
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- [기초 입문] 엑셀 함수 자동화 핵심 : #기호 10분 총정리 특강예제파일
엑셀 2021 버전 이후로 '분산 범위' 기능이 새롭게 도입되었습니다. 이전 버전에서는 함수나 수식의 결과값을 고정된 범위에만 배열로 반환할 수 있어서, 배열수식을 활용하는데 많은 제약이 따랐습니다.
하지만 엑셀 최신 버전에 새롭게 도입된 '분산 범위'와 '동적 배열 함수'를 활용하면, 기존에 함수만으로는 구현하기 어려웠던 다양한 작업을 보다 쉽고 편리하게 자동화할 수 있게 되었는데요.
엑셀 2021 버전 이후로 도입된 분산범위와 #기호를 활용하면 기존의 함수 업무를 더욱 편리하게 수행할 수 있습니다. 오늘 강의에서는 이 '분산 범위'를 제대로 활용하기 위한 핵심 기호인 #(해시) 기호 활용법을 알아보겠습니다.
오빠두Tip : 분산범위를 활용한 엑셀 배열 수식의 기초 사용법은 아래 5분 기초 영상강의에서 자세히 정리했습니다.
분산 범위를 동적으로 참조하는 "# 기호"
# 기호는 함수나 수식의 결과로 반환되는 '분산 범위'를 동적으로 참조하는 기호이며, 분산 범위를 제공하는 엑셀 2021 이후 버전부터 사용할 수 있습니다.
# 기호를 사용하면 분산 범위를 동적으로 선택할 수 있습니다. 오빠두Tip : # 기호가 사용된 파일을 엑셀 이전 버전에서 실행하면 =ANCHORARRAY 함수로 작성됩니다. 파일을 처음 실행하면 이전에 작성되어있던 값이 올바르게 보이지만, 데이터가 바뀌거나 수식을 다시 실행하면 #NAME? 오류를 반환합니다.
- 국가 고유목록 만들기 : 우선 UNIQUE 함수를 사용해서 국가의 고유값을 구해보겠습니다. 예제파일을 실행한 후, H3셀을 선택하고 아래와 같이 UNIQUE 함수를 작성합니다.
=UNIQUE(B3:B20)
UNIQUE 함수로 국가 범위의 고유목록을 구합니다. - 작성한 UNIQUE 함수를 입력하면 아래와 같이 국가의 고유 목록이 범위로 반환됩니다. 범위에서 임의의 셀을 하나 선택하면, 분산 범위 주변에 파란색 테두리가 표시되며서 반환된 범위를 확인할 수 있습니다.
함수로 반환된 범위를 선택하면, 파란색 테두리가 표시되면서 범위를 확인할 수 있습니다. 오빠두Tip : 엑셀 UNIQUE 함수와 SORT 함수의 기초 사용법은 아래 5분 기초 영상강의에서 자세히 정리했습니다.
- # 기호로 범위 선택하기 : 예제파일에서 I3셀을 선택한 후, 국가 고유값 목록의 개수를 세어보겠습니다. 아래와 같이 COUNTA 함수를 작성한 후, 고유값 목록이 반환되는 시작셀 뒤에 # 기호를 추가하면 범위가 동적으로 선택됩니다. 범위를 선택한 후, 수식을 입력하면 고유값의 개수가 계산됩니다.
=COUNTA(H3#)
COUNTA 함수의 범위로 H3#을 지정해서, 고유값 개수를 동적으로 집계할 수 있습니다. - 이제 원본 데이터에서 국가 이름을 변경하면, H3셀에 작성한 UNIQUE 함수와 반환된 고유 목록을 참조(H3#)하는 개수가 동적으로 갱신됩니다.
- # 기호 사용시 주의사항 : # 기호는 함수나 수식으로 반환되는 분산 범위만 동적으로 선택할 수 있습니다. 따라서 일반 범위에는 # 기호를 사용할 수 없습니다. 예를 들어, 예제파일에서 아래와 같이 일반 범위의 시작셀 뒤에 # 기호를 추가하면 범위가 선택되지 않는 것을 확인할 수 있습니다.
# 기호는 함수나 수식으로 반환된 분산 범위만 동적으로 선택할 수 있습니다.
분산범위 활용성을 200% 올려주는 '표' 기능
엑셀 표 기능을 분산 범위와 함께 사용하면, 새롭게 추가되는 데이터를 자동으로 함수에 반영하는 자동화 보고서를 만들 수 있습니다. 따라서 데이터가 추가될 때마다, 수식을 매번 손으로 직접 수정하지 않아도 되므로, 업무 생산성을 크게 높일 수 있습니다.
- 일반 범위의 제한사항 : 이전 단계에서 작성한 UNIQUE 함수는 참조 범위로 "B3:B20" 를 선택했습니다. 이 경우, B3:B20 범위 안에서 데이터가 변경되면, UNIQUE 함수의 결과값도 상응하여 변경됩니다.
이전 예제에서 UNIQUE 함수의 범위로 B3:B20을 선택했습니다. - 하지만 선택한 범위의 바로 아래인 B21셀에 새로운 데이터를 추가하면, "B3:B20"은 고정된 범위이므로 새로운 데이터를 인식하지 못하는 제한사항이 있습니다. 이런 문제를 해결하기 위해, 범위를 표로 변경하면 새로운 데이터가 추가될 때 동적으로 인식하는 범위를 만들 수 있습니다.
일반 범위는 새롭게 추가된 데이터를 인식하지 못하는 제한사항이 있습니다. - 범위를 표로 변경하기 : 범위에서 임의의 셀을 선택한 후, 단축키 Ctrl + A 를 누르면 인접한 모든 데이터가 한 번에 선택됩니다. 범위를 선택한 후, [삽입] 탭 - [표] 를 클릭해서 표 만들기 대화상자를 실행합니다.
표로 변환할 범위를 선택한 후, [삽입] - [표]를 클릭해서 표 만들기 대화상자를 실행합니다. - 표 만들기 대화상자가 실행되면, '머리글 포함'을 체크한 후 [확인] 버튼을 클릭해서 범위를 표로 변경합니다.
머리글 포함을 체크한 후, [확인] 버튼을 클릭해서 범위를 표로 변환합니다. - 표 이름 지정하기 : 표를 선택한 후, [테이블 디자인] 탭으로 이동하면 왼쪽에 [표 이름] 에서 표의 이름을 원하는 값으로 변경할 수 있습니다. 이번 강의에서는 '제품 목록'으로 표 이름을 변경하겠습니다.
표를 선택 - 테이블 디자인 - 표 이름에서 표 이름을 원하는 값으로 변경합니다. 오빠두Tip : 표 기능은 앞으로 파워쿼리와 파이썬 등의 다양한 기능을 활용하려면 꼭 알아야 할 중요 기능입니다. 표 기능의 기초 설명은 아래 5분 영상 강의를 참고하세요!
예제1 - 자동으로 확장하는 목록상자
# 기호를 활용하면 데이터가 변할 때마다 목록이 실시간으로 확장/축소되는 목록상자를 만들 수 있습니다.
- 국가 고유 목록 만들기 : 예제파일을 실행한 후, 국가별 고유 목록을 생성합니다. H3셀에 아래와 같이 UNIQUE 함수를 입력합니다. UNIQUE 함수에 들어가는 표 이름은 달라질 수 있습니다.
=UNIQUE(제품목록[국가])
이전 단계에서 추가한 표 범위를 참조한 국가 고유목록을 생성합니다. - 목록상자를 추가할 N2셀을 선택한 후, [데이터] 탭 - [데이터 유효성 검사]를 클릭해서 데이터 유효성 대화상자를 실행합니다. 대화상자가 실행되면, 제한 대상으로 '목록'을 선택합니다.
목록을 적용할 셀을 선택한 후, [데이터] 탭 - [데이터 유효성 검사] 에서 제한 대상으로 [목록]을 선택합니다. - 자동 확장 목록 만들기 : 원본 입력창에 아래와 같이 H3# 으로 범위를 지정한 후, [확인] 버튼을 클릭합니다. (H3셀은 UNIQUE 함수가 작성된 셀이며, H3#은 UNIQUE 함수로 반환되는 분산 범위를 동적으로 선택합니다.)
=H3#
원본 범위로 "=H3#"을 입력합니다. - 이제 표에 새로운 데이터를 추가하거나 변경하면, 목록이 자동으로 확장/축소되는 것을 확인할 수 있습니다.
원본 데이터가 변경되면 자동으로 확장/축소되는 목록상자가 완성되었습니다. 오빠두Tip : UNIQUE 함수와 SORT 함수를 같이 활용하면 오름/내림차순으로 정렬된 목록을 만들 수 있습니다.
예제2 - 조건 개수에 제한 없는 다중조건 목록
UNIQUE와 FILTER 함수를 같이 사용하면 조건 개수에 제한 없는 다중 조건 목록을 만들 수 있습니다.
- 조건을 충족하는 목록 만들기 : 예제1에서 추가한 목록에서 임의의 국가를 선택합니다.
- K3셀을 선택한 후, 아래와 같이 FILTER 함수를 작성합니다. FILTER 함수는 지정한 범위에서 조건을 충족하는 값을 필터링하여 반환합니다. 아래와 같이 FILTER 함수를 작성하면, 표에서 '국가' 필드의 값의 N2 셀에서 선택한 값와 동일한 제조사의 목록을 반환합니다.
=FILTER(제품목록[제조사],제품목록[국가]=N2)
- FILTER 함수로 반환된 목록에는 중복값이 포함될 수 있으므로, UNIQUE 함수를 사용해 고유값만 반환하면 조건을 충족하는 목록이 완성됩니다. K3셀에 작성했던 FILTER 함수를 아래와 같이 UNIQUE 함수로 감싸줍니다.
=UNIQUE(FILTER(제품목록[제조사],제품목록[국가]=N2))
UNIQUE와 FILTER 함수를 사용해 조건을 만족하는 제조사 목록을 생성합니다. - 다중 조건 목록 만들기 : 이제 마지막으로 목록을 생성합니다. 제조사 목록을 만들 N3셀을 선택한 후, [데이터] 탭 - [데이터 유효성 검사] 에서 제한대상으로 '목록'을 선택하고 원본 범위로 "=K3#"을 작성합니다.
목록을 추가할 셀을 선택한 후, 제조사 목록의 분산 범위를 데이터 유효성 검사의 원본 범위로 입력합니다. - [확인] 버튼을 클릭해서 목록을 적용하면, N2셀에서 선택한 국가의 하위 제조사를 나열하는 다중 조건 목록이 완성됩니다.
국가를 선택하면 해당 국가의 제조사만 나열되는 다중조건 목록이 완성되었습니다.
예제3 - 여러 값을 한 번에 검색하는 VLOOKUP 함수
실무에서 자주 사용하는 VLOOKUP 함수와 SUMIF 함수 등에 # 기호를 활용하면 대량의 데이터를 더욱 편리하게 분석할 수 있습니다.
- 차종별 통관번호 일괄 검색하기 : 예제1과 예제2에서 생성한 목록에서 임의의 값을 선택한 후, M6셀에 아래와 같이 선택한 제조사의 자동차 목록을 반환하는 FITLER 함수를 작성합니다.
=FILTER(제품목록[자동차명],제품목록[제조사]=N3)
선택한 제조사의 자동차 목록을 반환하도록 FILTER 함수를 작성합니다. - N6셀을 선택한 후, 아래와 같이 VLOOKUP 함수를 작성합니다. 기존에는 찾을값에 대한 결과를 VLOOKUP 함수로 검색한 후 수식을 자동채우기하였지만, 찾을값으로 # 기호를 활용한 분산 범위를 입력하면, 여러 항목의 VLOOKUP 검색 결과를 한 번에 출력할 수 있습니다.
=VLOOKUP(M6#,통관번호,2,0)
VLOOKUP 함수의 찾을값으로 자동차 목록을 반환하는 분산 범위를 입력해서 여러 항목을 한 번에 검색할 수 있습니다. 오빠두Tip : 분산 범위 영역에 기존 데이터가 작성되어 있을 경우 #SPILL!(또는 #분산!) 오류가 반환됩니다. 그럴 경우, 분산 범위로 반환될 영역에 작성된 기존 데이터를 지워서 해결할 수 있습니다. 엑셀에서 발생하는 다양한 오류에 대한 자세한 설명은 아래 기초 영상강의를 참고하세요!
- 국가 고유목록 만들기 : 우선 UNIQUE 함수를 사용해서 국가의 고유값을 구해보겠습니다. 예제파일을 실행한 후, H3셀을 선택하고 아래와 같이 UNIQUE 함수를 작성합니다.
