엑셀 동적범위 공식 및 사용법 :: OFFSET 함수 동적범위 알아보기
엑셀 동적범위 목차 바로가기
영상강의
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- [함수마스터] OFFSET 함수 동적범위 알아보기예제파일[엑셀고급] OFFSET 함수 동적범위 알아보기완성파일
관련 기초함수
정말 쉬운, 동적범위 총정리 강의 업데이트!
동적범위의 기본 동작원리가 궁금하시다면 아래 총정리 강의로 미리 예습한 뒤, 이번 강의를 실습해보세요.
강의요약
이번 강의에서는 엑셀 자동화를 위해 반드시 알아야 하는 내용 '동적범위'에 대해 알아봅니다. 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 함수 구문
= OFFSET ( 시작지점, 행(상하)이동, 열(좌우)이동, [상하넓이], [좌우넓이] )3-B. OFFSET 함수 예제 1
OFFSET 기초 예제1 함수 결과값 =OFFSET($A$1,1,2) C2 3-C. OFFSET 함수 예제 2
OFFSET 기초 예제2 함수 결과값 =OFFSET($A$1,3,4,2,2) E4:F5 [링크] MS홈페이지 OFFSET 함수 사용법 바로가기
4. COUNTA 함수 기초
4-A. COUNTA 함수 구문
= 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 함수 동적범위 구문
= OFFSET ( 시작지점, , , COUNTA(상하범위) , COUNTA(좌우범위) )- 상하범위: 상하범위를 확장할 기준이 되는 범위(*주로 고유값 범위)
- 좌우범위: 좌우범위를 학장할 기준이 되는 범위(*주로 머릿글 범위)
- COUNTA 함수를 이용한 동적범위 사용시, 범위에 빈칸이 있을경우 옳지않은 범위를 반환합니다. 따라서 범위에 빈칸이 있을 경우 MATCH 함수를 응용하여 동적범위를 작성합니다. MATCH 함수를 사용한 동적범위에 대한 자세한 내용은 여기를 클릭해서 확인하세요.
- 만약 범위에 수식이 입력되어 있을 시, 결과값이 공백이더라도 COUNTA 함수는 해당셀을 빈칸으로 인식하지 않습니다. 그럴 경우 COUNTIF 함수를 사용하여 아래와 같이 상하/좌우 범위를 적용합니다. 자세한 내용은 와일드카드 관련 포스트를 확인하세요.
COUNTIF ( 범위, "?*" )5-B. OFFSET 함수 동적범위 예제 - 1
OFFSET 함수로 동적범위 만들기 예제1 = OFFSET ( $A$2, , , COUNTA($A:$A) - 1 , COUNTA($1:$1)
'// A2:C10 범위를 반환합니다.5-C. OFFSET 함수 유동범위 예제2
OFFSET 함수로 유동범위 만들기 예제 2 = OFFSET ( $F$2, , , COUNTA($F:$F) - 1 , 3 )
'// F2:H9 범위를 반환합니다.
동적범위를 통해 자동범위인식 드롭다운 버튼 만들기 데이터 유효성검사 목록상자에 동적범위를 적용하면 목록상자를 자동화 할 수 있습니다. 예제파일의 [데이터유효성-목록] 시트로 이동한 뒤, 아래 순서대로 따라해보세요.
- 수식 ⇨ 이름관리자로 들어갑니다. (단축키: Ctrl + F3)
[수식] - [이름관리자] 로 이동합니다. - 사용자 지정범위의 이름을 ‘rng담당부서’로 생성합니다. 이후 OFFSET 동적범위 공식을 아래와 같이 입력합니다.
=OFFSET('데이터유효성-목록'!$F$2,,,COUNTA('데이터유효성-목록'!$F$F)-1)
rng담당부서로 사용자지정범위를 생성합니다. - 데이터 유효성 검사에서 [목록]을 선택 한 뒤, 원본을 클릭합니다. 이후 키보드 F3키를 누르면 방금 생성한 사용자지정범위 목록이 나타납니다. 목록에서 'rng담당부서'를 선택합니다.
방금 생성한 'rng담당부서'를 데이터유효성 목록 범위로 불러옵니다. - 담당부서목록에 새로운 데이터를 추가합니다. 이후 목록상자를 클릭하면 새로 입력한 데이터가 자동으로 반영된 것을 확인할 수 있습니다.
동적범위로 데이터유효성 목록을 통해 드롭다운 버튼을 생성하였습니다.
동적범위와 SUM함수 응용하기 SUM 함수의 합계를 구할 범위에도 동적범위를 적용할 수 있습니다.
- 이름관리자에서 ‘rng판매개수’ 로 사용자지정범위를 생성합니다. 이후 아래 수식을 입력해 동적범위 생성을 완료합니다.
=OFFSET('SUM함수'!$G$2,,,COUNTA('SUM함수'!$G$2:$G$100))
rng판매개수라는 이름으로 사용자지정범위를 생성합니다. - I2셀을 클릭한 뒤, SUM함수를 입력합니다. 이후 rng판매개수를 합계 범위로 입력하면 마무리됩니다.
동적범위를 응용하여 SUM함수와 사용할 수 있습니다.
동적범위와 VLOOKUP 응용하기 VLOOKUP 함수의 참조범위로 동적범위를 적용하면, 신규 데이터도 함수에 자동으로 반영이되어 보다 편리하게 작업할 수 있습니다. 아래 예제를 같이 보겠습니다.
- 이름관리자에서 ‘rng직원목록’이라는 이름으로 아래와 같이 OFFSET 함수를 이용하여 동적범위를 생성합니다.
=OFFSET('VLOOKUP함수'!$J$2,,,COUNTA('VLOOKUP함수'!$J:$J)-1,COUNTA('VLOOKUP함수'!$J$1:$M$1))
rng직원목록이라는 이름으로 OFFET동적범위를 생성합니다. - VLOOKUP함수와 동적범위를 응용하기 위해, 시트에서 N3셀을 클릭한 뒤 아래와 같이 함수를 입력해줍니다.
=VLOOKUP(N2,rng직원목록,2,FALSE)
동적범위를 응용하여 VLOOKUP을 더육 효율적으로 사용할 수 있습니다.
동적범위를 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/마지막셀-자동-이동-버튼-만들기/
감사합니다!^^*