OFFSET 동적범위 기초
엑셀 자동화를 위한 필수지식! - OFFSET 동적범위 기초/응용 총정리!
이 강의에서는 새로 추가되는 데이터까지 자동으로 인식하는 OFFSET 동적범위 수식을 다룹니다. 동적범위 작성에 필요한 OFFSET·COUNTA 함수의 동작 원리를 먼저 살펴본 뒤, 데이터 유효성 목록상자, SUM 합계 범위, VLOOKUP 참조 범위에 적용해 자료가 늘어나도 수식을 다시 작성하지 않고 결과가 갱신되도록 구성하는 방법을 정리합니다.
실습자료를 준비했어요
수업에서 사용한 예제 파일과 보충 자료를 한 곳에 정리했습니다!👇
목차 보기
영상강의
관련 기초함수
동적범위 총정리 강의로 미리 예습하기
동적범위의 기본 동작 원리가 궁금하다면 아래 총정리 강의로 핵심 개념을 먼저 복습한 뒤 이번 실습을 진행해 보세요.
강의 요약
이번 강의에서는 엑셀 자동화를 위해 반드시 알아야 하는 핵심 기법인 '동적범위'를 다룹니다. VBA 매크로 없이도 동적범위를 잘 활용하면 피벗테이블과 결합하여 데이터 정리 및 분석 작업을 효과적으로 자동화할 수 있습니다.
먼저 OFFSET 동적범위에 사용되는 기초 함수를 살펴본 뒤, OFFSET 동적범위의 동작 원리를 단계별로 이해해 보겠습니다.
이후 동적범위를 활용한 아래 3가지 실전 응용 기법을 차례대로 살펴봅니다.
동적범위(또는 유동범위, Dynamic Range)란 새로 추가된 데이터를 자동으로 인식하여 범위가 확장되는 기능을 말합니다. 동적범위는 단독으로 사용할 수는 없지만, 다양한 함수 및 기능과 결합하여 엑셀의 효율성을 비약적으로 끌어올릴 수 있는 매우 중요한 기법 중 하나입니다.
특히 자동화가 필요한 '인사관리', '매출관리', '재고관리'처럼 데이터가 지속적으로 갱신되는 자료에 적용하면 작업 속도가 크게 향상되며, 수기 작업에서 발생하는 휴먼에러를 거의 0%에 가깝게 줄일 수 있습니다.
2. Dynamic Range (동적범위)를 적용하는 방법
엑셀에서 동적범위를 적용하는 방법은 크게 다음 3가지로 나눌 수 있습니다.
- OFFSET 함수를 이용한 동적범위
가장 보편적으로 사용되는 동적범위입니다. 수식 작성이 직관적이며 다양한 방식으로 응용하기에도 적합합니다.
다만 넓은 범위에 적용할 경우 처리 속도가 느려질 수 있으므로, 적은 양의 데이터에 사용하기에 적합합니다(약 10만 행 이하).
자세한 내용은 'OFFSET 동적범위' 강의를 참조해 주세요.
- INDEX 함수를 이용한 동적범위
데이터 양이 많거나 더 빠른 처리 속도가 필요할 경우 INDEX 함수 동적범위를 사용합니다.
특히 50만 행을 넘어가는 대용량 데이터를 다룰 때 INDEX 함수를 사용하면 OFFSET 동적범위보다 월등히 빠른 처리 속도를 보여줍니다.
OFFSET과 함께 보편적으로 쓰이는 동적범위 수식이며, 이번 강의에서 자세히 알아봅니다. - 표 기능을 이용한 동적범위
엑셀에 기본 내장된 표 기능을 활용한 동적범위입니다. 초보자도 손쉽게 사용할 수 있는 가장 간편한 방법이며, 표 기능에서 제공되는 다양한 부가 기능을 함께 사용할 수 있다는 장점이 있습니다.
다만 '표' 기능을 기반으로 하므로 일반 범위에 비해 데이터 입력 및 편집에 일부 제약이 있다는 단점도 함께 존재합니다.
각 동적범위의 장단점 비교
| 장단점 비교 | OFFSET/INDEX 동적범위 | 표(TABLE) 기능 동적범위 |
| 장점 | 1. 원시자료(Raw Data)를 더 유연하게 편집할 수 있음. 2. 표 기능보다 적은 단계로 적용 가능 3. 워크시트를 보다 깔끔하게 관리 가능 |
1. 엑셀 내장 기능으로 초보자도 사용 가능 2. 같은 행의 특정 열에 빈칸이 있더라도 동일한 표로 인식하여 범위를 자동 인식 3. 많은 양의 동적범위가 필요할 경우 작업시간 단축에 유리 |
| 단점 | 1. OFFSET과 INDEX 함수에 대한 기본 지식이 필요함. 2. 많은 양의 데이터를 다룰 시 편집 속도 저하가 발생할 수 있음. |
1. 표 기능의 특성상 연속된 열 또는 행에 한해서만 동일한 표로 간주됨. 2. '정규화'되지 않은 자료에는 표 기능으로 동적범위를 적용하기 어려움. |
3-A. OFFSET 함수 구문
3-B. OFFSET 함수 예제 1
| 함수 | 결과값 |
| =OFFSET($A$1,1,2) | C2 |
3-C. OFFSET 함수 예제 2
| 함수 | 결과값 |
| =OFFSET($A$1,3,4,2,2) | E4:F5 |
[링크] MS 홈페이지 OFFSET 함수 사용법 바로가기
4. COUNTA 함수 기초
4-A. COUNTA 함수 구문
4-B. COUNTA 함수 예제
| 예제 | 함수 | 결과값 |
| 예제3 | =COUNTA($A$1:$A$12) | 10 |
| 예제4 | =COUNTA($A:$A) | 10 |
| 예제5 | =COUNTA($B$1:$B$7) | 7 |
| 예제6 | =COUNTA($B:$B) | 9 |
| 예제7 | =COUNTA($1:$1) | 3 |
| 예제8 | =COUNTA($7:$7) | 2 |
[링크] MS 홈페이지 COUNTA 함수 사용법 바로가기
5-A. OFFSET 함수 동적범위 구문
- 상하범위: 상하범위를 확장할 기준이 되는 범위 (주로 고유값이 입력된 범위)
- 좌우범위: 좌우범위를 확장할 기준이 되는 범위 (주로 머리글이 입력된 범위)
- COUNTA 함수를 이용한 동적범위는 범위에 빈칸이 있을 경우 잘못된 범위를 반환합니다. 따라서 범위에 빈칸이 있는 경우에는 MATCH 함수를 응용하여 동적범위를 작성해야 합니다. MATCH 함수를 활용한 동적범위에 대한 자세한 내용은 여기를 클릭해서 확인해 보세요.
- 만약 범위에 수식이 입력되어 있는 경우, 결과값이 공백이더라도 COUNTA 함수는 해당 셀을 빈칸으로 인식하지 않습니다. 이때는 COUNTIF 함수를 사용하여 아래와 같이 상하/좌우 범위를 적용합니다. 자세한 내용은 와일드카드 관련 포스트를 확인해 주세요.
5-B. OFFSET 함수 동적범위 예제 - 1
'// A2:C10 범위를 반환합니다.
5-C. OFFSET 함수 유동범위 예제 2
'// F2:H9 범위를 반환합니다.
데이터 유효성 검사 목록상자에 동적범위를 적용하면 목록상자를 자동화할 수 있습니다. 예제파일의 [데이터유효성-목록] 시트로 이동한 뒤 아래 순서대로 따라해 보세요.
- 수식 ⇨ 이름관리자로 들어갑니다 (단축키: Ctrl + F3).
- 사용자 지정범위의 이름을 'rng담당부서'로 생성합니다. 이후 OFFSET 동적범위 공식을 아래와 같이 입력합니다.
=OFFSET('데이터유효성-목록'!$F$2,,,COUNTA('데이터유효성-목록'!$F$F)-1)
- 데이터 유효성 검사에서 [목록]을 선택한 뒤 원본 입력란을 클릭합니다. 이후 키보드 F3 키를 누르면 방금 생성한 사용자지정범위 목록이 나타납니다. 목록에서 'rng담당부서'를 선택합니다.
- 담당부서 목록에 새로운 데이터를 추가합니다. 이후 목록상자를 클릭하면 새로 입력한 데이터가 자동으로 반영된 것을 확인할 수 있습니다.
SUM 함수의 합계 범위에도 동적범위를 적용할 수 있습니다.
- 이름관리자에서 'rng판매개수'라는 이름으로 사용자지정범위를 생성합니다. 이후 아래 수식을 입력해 동적범위 생성을 마무리합니다.
=OFFSET('SUM함수'!$G$2,,,COUNTA('SUM함수'!$G$2:$G$100))
- I2 셀을 클릭한 뒤 SUM 함수를 입력합니다. 이후 합계 범위로 rng판매개수를 입력하면 작업이 마무리됩니다.
VLOOKUP 함수의 참조범위로 동적범위를 적용하면 신규 데이터도 함수에 자동으로 반영되어 한층 편리하게 작업할 수 있습니다. 아래 예제를 함께 살펴보겠습니다.
- 이름관리자에서 'rng직원목록'이라는 이름으로 아래와 같이 OFFSET 함수를 활용한 동적범위를 생성합니다.
=OFFSET('VLOOKUP함수'!$J$2,,,COUNTA('VLOOKUP함수'!$J:$J)-1,COUNTA('VLOOKUP함수'!$J$1:$M$1))
- VLOOKUP 함수와 동적범위를 응용하기 위해 시트에서 N3 셀을 클릭한 뒤 아래와 같이 함수를 입력합니다.
=VLOOKUP(N2,rng직원목록,2,FALSE)
동적범위를 INDRECT 함수로 참조하여 목록상자를 만드시면 동작하지 않습니다. :)
그럴때에는 범위를 테이블형태로 지정하시거나 INDIRECT 함수안에 다른 공식을 넣어서 다중조건목록상자를 만들 수 있는데요.
관련 내용은 이후 준비하여드리겠습니다. 감사합니다!
두 가지 질문 드립니다.
1. 이름정의할 때 시트 이름이 ' 를 붙이는 건 어떤 의미가 있는지요?
예) '데이터유효성-목록'
=OFFSET('데이터유효성-목록'!$F$2,,,COUNTA('데이터유효성-목록'!$F$F)-1)
2. ,,, 은 어떨 때 사용하는 것인가요? 보통 , 는 한 번만 사용하는데 ,,, 으로 하는 이유를 알고 싶습니다.
2. 콤마는 각 인수를 구분합니다. 인수1, [인수2 빈칸], [인수3 빈칸], 인수4 이런식으로 중간에 빈칸이 있어 여러개의 콤마를 동시에 입력하게 됩니다.
이해되셨나요?^ㅡ^*
고맙습니다 ㅜㅜ 복받으실 꺼에요
한가지 질의사항은 21분 25초 경 =sum(rng판매개수) 입력하는 과정에서 "rng판매개수"로 파란색 선택줄이 내려간 후 마우스로 클릭하지 않고, 키보드로 입력하셨는데 단순히 enter를 눌러서는 입력이 안되고 shift, ctrl, alt 등 같이 조작해보아도 안됩니다.
어떤 방식으로 마우스를 사용치 않고 입력하였는지 문의 드립니다. 감사합니다.^^
rng판매개수를 입력하신 후 탭(Tab) 키를 눌러보시겠어요?
감사합니다!!! 늘 넘사벽 강의 감사합니다!
동적범위에 대해서 질문이 있어서 댓글을 남기게 되었습니다! 아무리 찾아도 안나오네요...
표에서 내용이 있는걸 판단할때 COUNTA함수를 쓰게되는데.. 제가 활용하고싶은 자료에서는
결과값이 “”로 출력된 함수식(예를들면 =“”와같은?)이 쓰여있어서 빈칸으로 인식이 안되고 COUNTA함수로 계수했을때 내용이 있는걸로 인식이 되더라구요..!
이럴땐 어떻게 해야 진짜 내용있는셀로 인식을 해서 동적범위참조를 활용할수있게될까요 ㅠㅠ
만약 함수가 입력되었더라도 값이 빈칸일경우 동적범위에서 제외하고 싶으신거지요?
그럴경우 아래 함수를 사용해보시기 바랍니다.
제 답변이 도움이 되셨길 바랍니다.
감사합니다 ^^
참조된 셀을 삭제할 경우 #REF가 발생하는 것은 근본적으로 해결할 수 없습니다 ^^;
대안책으로 #REF 가 발생하는 셀을 OFFSET 함수로 참조하여 해결할 수도 있지만, 그마저도 전체행이나 전체열이 삭제될경우 다시 #REF 오류가 발생하므로 완전한 해결책이 될수 없습니다.
제 답변이 도움이 되셨길 바랍니다 ^^
감사합니다.
E-Book은 관리상 어려움이 있어, 업데이트를 중단하였습니다.
현재는 모두 홈페이지 게시글로 대체하여 수시로 업데이트 하고 있으니 참고 부탁드리겠습니다 ^^
강의에 사용된 완성파일은 금일 중으로 업데이트 해 드릴테니 내일 다시 방문해주시겠어요?^^
감사합니다!
다름이 아니라 이번 강의를 적용해서 회사 관련 데이터를 정리하고 있는데 문제가 안되는 부분이 생겨서 여쭙니다.
혹시offset을 이용해 동적범위를 하나의 이름을 생성해놓고, 이 이름을 이용해서 배열수식에 적용하면 안되는 건가요? 해보는데 자꾸 값이 안나오네요..
이전까지는 수식이 문제없이 잘 작동했는데 범위를 직접
=IFERROR(INDEX(데이터시트!$C$2:$C$110,MATCH(1,(데이터시트!$A$2:$A$110='선유리공장 거래명세서(제작)-이중관'!C12)*(데이터시트!$B$2:$B$110='선유리공장 거래명세서(제작)-이중관'!AH12),0)),"")
위와 같이 지정했는데 (데이터시트!$C$2:$C$110, 데이터시트!$A$2:$A$110, 데이터시트!$B$2:$B$110)
이 애들을 offset으로 이름으로 지정한뒤 배열수식에 넣으니까 안되네요 ㅠ
위 적어주신 공식은 동적범위로 적용해도 잘 동작할 수 있는 수식입니다.
아마 #REF 오류가 출력되시지 않았을까 생각되는데요.
MATCH... 이후 범위를 동적범위로 적용하셨다면, INDEX 함수의 첫번째 인수 (데이터시트!$C$2:$C$110)도 똑같이 동적범위로 적용해주셔야 합니다 ^^
모든 범위의 넓이가 동일해야 오류없이 정상동작하게 됩니다.
제 답변이 도움이 되셨길 바랍니다.
감사합니다!
COUNTA 함수를 사용한 동적범위는 '빈칸'을 제외하고 범위를 확장하기 때문에, 범위안에 빈칸이 있을경우 옳지않은 범위가 반환됩니다.
그럴 경우 MATCH 함수를 사용한 동적범위를 적용할 수 있는데요.
함수마스터 3강을 확인해보시겠어요?
https://www.oppadu.com/마지막셀-자동-이동-버튼-만들기/
감사합니다!^^*