엑셀 업무 자동화 필수 스킬! - 동적범위 기초-응용 완벽정리

실습 가이드

엑셀 동적범위는 새로 추가하거나 삭제한 데이터를 자동으로 인식하여 범위를 확장 또는 축소하는 범위입니다.

동적범위는 매크로나 파워쿼리 같은 외부 기능을 사용하지 않고도 엑셀 자동화 서식을 제작할 때 반드시 알아야 하는 실무자 필수 기능입니다. 이번 강의를 마무리하신 뒤, 동적범위를 활용한 더 많은 예제는 아래 영상 강의에서 확인하실 수 있습니다.

복/붙해서 쓰는 동적 범위 기본 공식

기본 공식 (세로 방향)

가로·세로로 함께 확장되는 동적범위 공식

빈 칸이 포함된 경우 사용하는 동적범위 공식

값이 입력된 셀만 인식해 확장하는 동적범위 공식 (수식 여부와 무관)

OFFSET 함수 원리 이해하기

많은 실무자분들이 동적범위를 공부하면서 OFFSET 함수를 어렵게 느끼곤 합니다. 그러나 OFFSET 함수는 원리만 이해하면 매우 간단한 함수로, 동적범위를 손쉽게 만들 수 있습니다. OFFSET 함수는 기준 셀에서 아래 또는 오른쪽으로 이동한 위치에서, 지정한 높이와 너비만큼 확장된 범위를 반환하는 함수입니다.

예를 들어 첫 번째 예제는 기준셀인 B10셀에서 아래로 2칸, 오른쪽으로 1칸 이동한 C12셀을 반환하고, 두 번째 예제는 기준셀인 E10셀에서 높이 3칸만큼 확장된 E10:E12 범위를 반환합니다.

이제 마지막 예제를 살펴보겠습니다. 마지막 공식은 다른 인수는 모두 동일하고, OFFSET 함수의 높이 인수만 COUNTA 함수로 작성되어 있습니다.

COUNTA 함수는 지정한 범위에서 비어 있지 않은 셀의 개수를 반환하는 함수입니다. 따라서 COUNTA 함수의 결과로 6이 반환되고, 기준셀인 H10셀에서 6칸만큼 확장된 범위가 만들어집니다. 범위 안의 데이터가 변경되면, OFFSET 함수가 반환하는 범위도 실시간으로 함께 갱신됩니다.

이것이 동적범위의 전부이며, 원리를 이해하면 의외로 간단합니다. 더 쉽게 기억하실 수 있도록 동적범위 작성 순서를 아래 5단계로 정리해드렸습니다.

이 5단계만 기억하시면 누구나 손쉽게 동적범위를 만들 수 있습니다. 이어서 실전 예제와 함께 자동으로 확장되는 목록상자와 VLOOKUP 함수를 작성해보겠습니다.

자동으로 갱신되는 목록상자 만들기

동적범위를 사용하면 데이터를 추가하거나 삭제할 때마다 실시간으로 항목이 갱신되는 목록상자를 만들 수 있습니다.

1. 이름 정의범위 만들기 : 예제파일을 실행한 뒤 [동적범위 기초] 시트로 이동합니다. [수식] - [이름 관리자]를 클릭하거나 단축키 Ctrl + F3 을 눌러 이름 관리자를 실행한 다음, [새로 만들기] 버튼을 눌러 새로운 이름 정의범위를 추가합니다.
2. 이름은 '과목범위'로 입력하고, 참조 대상에는 앞서 정리한 동적범위 5단계 공식에 따라 아래 수식을 순서대로 입력합니다. 입력을 마치고 [확인] 버튼을 누르면 동적범위가 등록됩니다.
   오빠두Tip : 이름 관리자를 거치지 않고, 데이터 유효성 검사·피벗테이블·차트 등의 원본 범위로 동적범위 공식을 직접 입력하는 것도 가능합니다.
3. 자동으로 확장되는 목록상자 만들기 : 목록상자를 적용할 셀(예제 파일에서는 C7셀)을 선택한 뒤, [데이터] 탭 - [데이터 유효성 검사]로 이동합니다. 이어서 제한 대상으로 '목록'을 선택합니다.
4. 원본 입력란에 커서를 두고 키보드 F3키를 누르면 '이름 붙여넣기' 대화상자가 나타나며, 등록된 이름 정의 목록이 표시됩니다. 목록에서 '과목범위'를 더블클릭하면 원본 범위가 자동으로 입력되고, [확인] 버튼을 눌러 목록상자 적용을 마무리합니다.
   오빠두Tip : 원본 범위에 "=과목범위"라고 직접 입력하는 방법으로도 동일한 결과를 얻을 수 있습니다.
5. 이제 과목 목록에 새로운 항목을 추가한 뒤 목록상자를 클릭하면, 데이터가 추가되거나 삭제될 때마다 목록상자가 자동으로 확장되는 것을 확인할 수 있습니다.

동적범위 활용 #1 : 빈 칸이 포함된 경우

동적범위는 목록상자뿐 아니라 다양한 함수에도 함께 적용할 수 있습니다. INDEX/MATCH 공식이나 VLOOKUP 함수 등 실무에서 자주 쓰이는 공식과 결합하면 활용 폭이 크게 넓어집니다.

실무에서 동적범위를 사용할 때 가장 흔히 만나는 문제는 범위 안에 빈 칸이 섞여 있는 경우입니다. COUNTA 함수는 비어 있지 않은 셀의 개수만 세기 때문에, 범위에 빈 셀이 포함되면 동적범위가 잘못된 결과를 반환하기 때문입니다.

이런 상황에서는 아래 동적범위 공식을 사용합니다.

MATCH 함수를 활용한 동적범위의 동작 원리는 아래 영상 강의에서 자세히 설명해드렸습니다.

이번 강의에서는 실무에서 가장 많이 사용하는 함수인 VLOOKUP 함수에 동적범위를 적용해 빈 칸 문제를 해결해보겠습니다.

1. 빈 칸이 포함된 경우 동적범위 공식 : 예제파일을 실행한 뒤 [동적범위 응용] 시트로 이동합니다. [수식] 탭 - [이름 관리자]를 클릭하거나 단축키 Ctrl + F3 을 눌러 이름 관리자를 실행한 다음, [새로 만들기] 버튼을 눌러 새로운 이름 정의범위를 추가합니다.
   오빠두Tip : 영상 강의에서는 VLOOKUP 함수의 인수 안에 동적범위 공식을 직접 입력하는 방법으로 설명해드렸습니다.
2. 이름은 '직원범위'로 입력한 뒤, 빈 칸이 포함된 경우 사용하는 동적범위 공식을 복사하여 각 인수를 하나씩 변경합니다.
   =OFFSET($기준셀,,,MAX(IFERROR(MATCH("*",$세로범위,-1),0),IFERROR(MATCH(9E+307,$세로범위,1),0)),가로너비)

   

   오빠두Tip : 이름 관리자에서 수식을 수정할 때 방향키를 바로 누르면 셀이 이동합니다. 이런 경우 키보드 F2 키를 누른 뒤 방향키를 누르면 수식 안에서 커서를 이동할 수 있습니다. 자세한 내용은 아래 영상 강의를 참고해주세요.

   업무시간 2배 이상 줄여주는, 엑셀 필수 단축키 (하나만 기억하세요!)

3. 완성된 공식은 아래와 같습니다. [확인] 버튼을 눌러 마무리하면, 빈 칸을 포함하는 동적범위가 등록됩니다.
   =OFFSET($B$8,,,MAX(IFERROR(MATCH("*",$B$8:$B$21,-1),0),IFERROR(MATCH(9E+307,$B$8:$B$21,1),0)),3)

   

4. VLOOKUP 함수에 동적범위 적용하기 : 이제 직원 목록 아래로 한 칸을 비우고 새로운 직원을 추가합니다. 이번 강의에서는 예제로 '오빠두'라는 직원을 추가하겠습니다.
5. 이어서 F8 셀에 '오빠두'를 입력한 뒤, G8 셀 안에 동적범위를 사용한 VLOOKUP 함수를 작성합니다. Ctrl + Shift + Enter 키로 수식을 입력하면, 빈 칸이 있어도 범위가 자동으로 확장되어 새로 추가한 직원을 정상적으로 검색할 수 있습니다.
   오빠두Tip : 동적범위를 셀 안에 함수 인수로 사용할 때는 배열 수식으로 입력되므로, 반드시 Ctrl + Shift + Enter로 입력해야 합니다. 365 버전 사용자는 일반 Enter로 입력해도 정상적으로 동작합니다.

동적범위 활용 #2 : 2021 이후 버전

365 버전부터는 배열을 동적으로 반환하는 기능이 추가되었습니다. 덕분에 복잡한 공식을 사용하지 않고도 동적범위를 손쉽게 적용할 수 있습니다. 이번 강의에서는 FILTER 함수를 활용한 동적범위 예제를 살펴보겠습니다.

1. FILTER 함수로 목록 만들기 : 예제파일의 [동적범위 기초] 시트로 이동한 뒤, D10셀에 아래 FILTER 함수를 입력합니다.
   =FILTER(B10:B17,B10:B17<>"")

   

2. 엔터키로 수식을 입력하면, 범위에서 비어 있지 않은 항목만 모은 목록이 자동으로 만들어집니다.
3. "#" 기호로 동적범위 만들기 : 시트의 빈 셀을 선택한 뒤, FILTER 함수가 입력된 D10셀을 다시 선택합니다. 이어서 셀 주소 뒤에 # 기호를 추가하면, FILTER 함수가 반환한 범위 전체가 자동으로 선택됩니다.
   오빠두Tip : 365 버전에서는 배열을 반환하는 셀 주소 뒤에 "#" 기호를 추가하여 동적 범위를 손쉽게 선택할 수 있습니다.
4. 빈 셀 자동 제외하기 : FILTER 함수의 또 다른 장점은 범위 안의 빈 셀을 자동으로 제외하고 목록을 구성한다는 점입니다. 원본 범위 중간에 입력된 과목을 지우면, 빈 셀을 건너뛰고 나머지 항목으로 목록이 자동 갱신됩니다.
   오빠두Tip : 이 기능은 365 이전 버전에서도 구현 가능하지만, 다소 복잡한 공식을 사용해야 합니다. 이 부분은 이후 별도 강의에서 자세히 다뤄드릴 예정입니다.