엑셀 동적범위 완벽정리 - 업무 자동화 실무자 필수스킬!
엑셀 동적범위 총정리 목차 바로가기
영상 강의
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- [기초레벨업] 엑셀 자동화 필수 기능, 동적범위 완벽정리완성파일[기초레벨업] 엑셀 자동화 필수 기능, 동적범위 완벽정리예제파일
엑셀 동적범위란 새롭게 추가/삭제 된 데이터를 인식해서 범위를 자동으로 확장/축소하는 범위입니다.
동적범위는 엑셀의 다양한 기능, 함수와 같이 활용할 수 있습니다. 동적범위는 매크로나 파워쿼리와 같은 외부기능을 사용하지 않고 엑셀 자동화서식을 제작하려면 반드시 알아야 할 실무자 필수기능인데요. 이번 강의를 마무리 하신 뒤, 동적범위에 대한 더 많은 예제는 아래 영상 강의에서 확인 하실 수 있습니다.
상황별 동적범위 공식 총정리 (복/붙 그대로 사용가능!)
기본 공식 (세로방향)
=OFFSET($기준셀,,,COUNTA($세로범위))
엑셀 OFFSET 동적범위 기본 공식 가로/세로 확장되는 동적범위 공식
=OFFSET($기준셀,,,COUNTA($세로범위),COUNTA($가로범위))
엑셀 동적범위 가로/세로 확장 공식 빈칸이 있을 경우 사용하는 동적범위 공식
=OFFSET($기준셀,,,MAX(IFERROR(MATCH("*",$세로범위,-1),0),IFERROR(MATCH(9E+307,$세로범위,1),0)),가로너비)
빈칸을 포함하는 동적범위 공식 값이 있는 셀만 확장하는 동적범위 공식 (수식 여부 상관 X)
=OFFSET($기준셀,,,COUNTIF($세로범위,"?*"))
'세로범위 높이를 COUNTA 함수로 셀 경우, 값이 비어 있더라도 함수가 입력된 셀이 동적범위에 모두 포함됩니다.
수식 여부 상관없이 값이 있는 셀만 카운팅하는 동적범위 공식 많은 실무자분들이 '동적범위'를 공부하면서, OFFSET 함수를 많이 어려워하십니다. 하지만, OFFSET 함수는 매우 간단한 함수여서, 알고나면 동적범위를 정말로 쉽게 만들 수 있는데요. OFFSET 함수는 '기준 셀에서 아래/오른쪽으로 이동한 다음, 해당 셀에서 높이/너비만큼 확장된 범위'를 반환하는 함수입니다.
= OFFSET ( 기준셀, 아래이동, 오른쪽이동, 높이, 너비 )
엑셀 OFFSET 동적범위 기초 예제 예를 들어, 첫번째 예제는 기준셀인 B10셀에서 아래로 2칸, 오른쪽으로 1칸 이동한 C12셀을 반환하고 두번째 예제는 기준셀인 E10셀에서 높이로 3칸 확장된 E10:E12를 범위로 반환합니다.
여기까지 이해하셨다면 동적범위의 절반을 이해하신 겁니다!
마지막 예제를 살펴보겠습니다. 마지막 공식은 나머지는 모두 동일하고, OFFSET 함수의 높이만 COUNTA 함수로 작성되었습니다.
엑셀 OFFSET 동적범위 기본 공식 설명 COUNTA 함수는 범위에서 비어있지 않은 셀의 개수를 반환하는 함수죠. 따라서 COUNTA 함수의 결과로는 6이 반환되고, 기준셀인 H10셀에서 6칸 확장된 범위가 반환됩니다. 만약 범위 안에 입력된 값이 변경되면, OFFSET 함수로 반환되는 범위도 실시간으로 변경되겠죠.
이게 동적범위의 전부입니다! 아주 간단하죠? 여러분께서 조금 더 쉽게 기억하실 수 있도록, 동적범위 작성순서를 아래 5단계로 정리해드렸습니다.
① OFFSET 함수 → ② 시작셀 클릭 → ③ 콤마 3번 → ④ COUNTA 함수 → ⑤ 괄호 2번
동적범위 만들기 5단계 공식 이 5단계만 기억하시면, 누구나 쉽게 동적범위를 만들 수 있습니다! 이제 실전 예제와 함께 자동으로 확장되는 목록상자와 VLOOKUP 함수를 작성해보겠습니다.
동적범위를 사용하면 데이터가 추가/삭제 될 때마다 실시간으로 변경되는 목록상자를 만들 수 있습니다.
- 이름 정의범위 만들기 : 예제파일을 실행한 뒤, [동적범위 기초] 시트로 이동합니다. [수식] - [이름 관리자]를 클릭하거나, 단축키 Ctrl + F3 을 눌러 이름관리자를 실행합니다. 이후 [새로 만들기] 버튼을 클릭하여 새로운 이름정의범위를 추가합니다.
이름관리자에서 새로운 이름정의범위를 등록합니다. - 이름으로는 '과목범위'를 입력 후, 참조 대상으로는 동적범위 5단계 공식에 따라 아래 공식을 순서대로 입력합니다. 수식을 다 입력 후 [확인] 버튼을 누르면 동적범위가 등록 되었습니다.
과목범위를 새로운 동적범위로 등록합니다. 오빠두Tip : 이름 관리자를 사용하지 않고 데이터유효성 검사나 피벗테이블, 차트 등의 원본범위로 동적범위를 직접 입력할 수도 있습니다. - 자동으로 확장되는 목록상자 만들기 : 목록상자를 적용할 셀을 선택 후(예제파일에서는 C7셀), [데이터] 탭 - [데이터 유효성 검사]로 이동합니다. 이후 제한대상으로 '목록'을 선택합니다.
데이터유효성 검사로 목록상자를 추가합니다. - 원본에 커서를 클릭한 뒤, 키보드 F3키를 누르면 '이름 붙여넣기' 대화상자가 나오면서 등록된 이름정의목록이 표시됩니다. 목록에서 '과목범위'를 더블클릭하면 원본범위가 입력됩니다. [확인] 버튼을 눌러 목록상자 적용을 마무리합니다.
원본 범위로 이름정의범위를 등록합니다. 오빠두Tip : 원본 범위를 "=과목범위"로 직접 입력할 수도 있습니다. - 이제 과목범위에 새로운 과목을 추가한 뒤, 목록상자를 클릭하면 데이터가 추가/삭제 될 때마다 목록상자가 자동으로 확장됩니다.
새로운 데이터를 추가하면 목록상자가 자동으로 확장됩니다.
동적범위는 목록상자 외에 다양한 함수에 적용할 수도 있습니다. INDEX/MATCH 공식, VLOOKUP 함수 등 다양한 공식에 적용할 수 있는데요.
실무에서 동적범위를 사용할 때 가장 흔히 겪는 문제는 범위 안에 빈 칸이 섞여있는 경우입니다. 왜냐하면, COUNTA 함수는 비어있지 않은 셀의 개수를 세기 때문에, 범위 안에 빈 셀이 포함된 경우 동적범위로 잘못된 결과가 반환되기 때문인데요.
범위에 빈 칸이 포함되어 있을 경우, 동적범위 공식을 수정해야 합니다 그럴 경우, 아래 동적범위 공식을 사용합니다.
=OFFSET($기준셀,,,MAX(IFERROR(MATCH("*",$세로범위,-1),0),IFERROR(MATCH(9E+307,$세로범위,1),0)),가로너비)MATCH 함수를 사용한 동적범위의 동작원리는 아래 영상강의에서 자세히 설명해드렸습니다.
이번 강의에서는 실무에서 가장 많이 사용하는 함수인 VLOOKUP 함수에 동적범위를 적용해서 문제를 해결해보겠습니다.
- 빈칸이 포함된 경우 동적범위 공식 : 예제파일을 실행한 뒤, [동적범위 응용] 시트로 이동합니다. [수식] 탭 - [이름 관리자]를 클릭하거나 단축키 Ctrl + F3 을 눌러 이름관리자를 실행합니다. 이후 [새로 만들기] 버튼을 클릭하여 새로운 이름정의범위를 추가합니다.
새로운 이름정의범위로 동적범위를 등록합니다. 오빠두Tip : 영상 강의에서는 VLOOKUP 함수 안에 직접 동적범위를 입력하는 방법으로 설명해드렸습니다. - 이름으로는 '직원범위'를 입력한 뒤, 빈 칸이 포함된 경우 사용하는 동적범위 공식을 복사하여, 각 인수를 하나씩 변경합니다.
=OFFSET($기준셀,,,MAX(IFERROR(MATCH("*",$세로범위,-1),0),IFERROR(MATCH(9E+307,$세로범위,1),0)),가로너비)
동적범위 공식을 입력 후, 인수를 하나씩 변경합니다. 오빠두Tip : 이름관리자에서 수식을 수정할 때 방향키를 바로 누르면 셀이 이동하게 됩니다. 그럴 경우, 키보드 F2키를 누른 뒤 방향키를 누르면 수식 위 커서를 이동할 수 있습니다. 자세한 내용은 아래 영상강의를 참고해주세요. - 완성된 공식은 아래와 같습니다. [확인] 버튼을 눌러 마무리하면, 빈칸을 포함하는 동적범위가 등록되었습니다.
=OFFSET($B$8,,,MAX(IFERROR(MATCH("*",$B$8:$B$21,-1),0),IFERROR(MATCH(9E+307,$B$8:$B$21,1),0)),3)
빈칸을 포함한 동적범위가 만들어졌습니다. - VLOOKUP 함수에 동적범위 적용하기 : 이제 범위 밑으로 한칸을 띄고, 새로운 직원을 추가합니다. 이번 강의에서는 예제로 '오빠두'라는 직원을 추가하겠습니다.
밑으로 빈 칸을 포함하여 새로운 데이터를 추가합니다. - 이후 F8 셀에 '오빠두'를 입력한 뒤, G8 셀안으로 동적범위를 사용한 VLOOKUP 함수를 작성합니다. 이후 Ctrl + Shift + Enter 키로 수식을 입력하면 빈칸에 상관없이 범위가 확장되어 직원을 검색할 수 있게 됩니다.
VLOOKUP 함수로 참조하는 범위가 동적으로 확장됩니다. 오빠두Tip : 동적범위를 셀 안에 함수로 사용할 경우, 배열 수식으로 입력되므로 반드시 Ctrl + Shift + Enter로 입력합니다. (365 버전 사용자는 Enter로 입력해도 괜찮습니다.)
365 버전은 배열을 동적으로 반환할 수 있습니다. 따라서 복잡한 공식을 사용하지 않아도 동적범위를 손쉽게 적용할 수 있는데요. 이번 강의에서는 FILTER 함수를 활용한 동적범위 예제를 알아보겠습니다.
- FILTER 함수로 목록만들기 : 예제파일 [동적범위 기초] 시트로 이동한 뒤, D10셀에 아래 FILTER 함수를 입력합니다.
=FILTER(B10:B17,B10:B17<>"")
365 사용자는 FILTER 함수로 동적범위를 간단하게 만들 수 있습니다. - 엔터키로 수식을 입력하면 범위에서 비어있지 않은 목록이 포함된 범위가 자동으로 만들어집니다.
- "#" 기호로 동적범위 만들기 : 시트의 아무 빈 셀을 선택한 뒤, FILTER 함수가 입력된 D10셀을 선택합니다. 이후 뒤에 # 기호를 추가하면 FILTER 함수로 반환된 범위가 자동으로 선택됩니다.
시작셀 뒤로 #(샵) 기호를 추가하면 배열로 반환된 범위가 자동으로 선택됩니다. 오빠두Tip : 365 버전에서는 배열을 반환하는 셀 뒤로 "#" 기호를 추가해서 범위를 동적으로 손쉽게 선택할 수 있습니다. - 빈 셀 목록에서 자동 제외하기 : FILTER 함수를 사용할 경우 또 다른 장점은 범위 안의 빈 셀은 자동으로 제외하고 목록을 만든다는 점입니다. 원본 범위에서 중간에 입력된 과목을 지우면, 빈 셀을 제외하고 목록이 자동으로 만들어 집니다.
FILTER 함수를 사용하면 빈칸 제외된 목록이 바로 만들어집니다. 오빠두Tip : 이 기능은 365 이전 버전에서도 구현 가능하지만, 다소 복잡한 공식을 사용해야 하는데요. 여기에 대한 내용은 이후 별도강의에서 자세히 다뤄드릴 예정입니다.
