엑셀 동적범위 공식 및 사용법 :: 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을 더육 효율적으로 사용할 수 있습니다.
