엑셀 동적차트 기초부터 고급 응용까지 :: 실전 예제 총정리

INDEX 동적범위와 표기능을 응용한 동적차트의 차이점, 그리고 동적범위를 이용한 동적차트 제작방법을 단계별로 살펴봅니다.

홈페이지 » 엑셀 동적차트 기초부터 고급 응용까지 :: 실전 예제 총정리

INDEX 동적범위로 엑셀 동적차트 만들기

동적차트 만들기 목차 바로가기
영상강의
큰 화면으로 보기

예제파일 다운로드

오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.

  • [함수마스터] INDEX 함수 동적범위 및 동적차트
    완성파일
  • [함수마스터] INDEX 함수 동적범위 및 동적차트
    예제파일

관련 기초함수

정말 쉬운, 동적범위 총정리 강의 업데이트!

동적범위의 기본 동작원리가 궁금하시다면 아래 총정리 강의로 미리 예습한 뒤, 이번 강의를 실습해보세요.

강의 요약

이전 강의에서는 OFFSET 함수 동적범위를 이용한 실전 예제를 알아봤습니다. 데이터 유효성 검사, 다른 함수와의 활용 이외에도 동적범위를 이용하면 실시간으로 업데이트되는 '엑셀 동적차트'를 만들 수 있다는 사실 알고 계셨나요?

이번 강의에서는 표기능을 이용한 간단한 동적차트와 INDEX 함수의 동적범위, 그리고 이를 이용한 동적차트 만들기를 단계별로 알아봅니다. (OFFSET 함수를 이용해도, 동일하게 엑셀 동적차트를 만들 수 있습니다.)

동적범위 엑셀 동적차트 완성 GIF
동적범위를 이용한 엑셀 동적차트를 제작방법을 알아봅니다.

INDEX 동적범위로 엑셀 동적차트 만들기

동적범위의 종류

동적범위를 적용하는데 크게 3가지 방법이 있습니다.

  1. OFFSET 함수를 이용한 동적범위
    가장 보편적으로 사용되는 동적범위입니다. 수식을 작성하기 가장 편리하며, 다양한 방식으로 응용하기에도 적합합니다.
    다만, 넓은범위에 적용시 속도저하가 발생할 수 있으므로, 적은양의 데이터에 사용하기 적합합니다. (10만행 이하)
    자세한 내용은 ‘OFFSET 동적범위’ 강의를 참조해주세요.
  2. INDEX 함수를 이용한 동적범위
    데이터 양이 많거나 더욱 빠른 처리속도가 필요할 경우, INDEX 함수 동적범위를 사용합니다.
    특히 50만행이 넘어가는 많은양의 데이터를 다룰시 INDEX 함수를 사용하면 OFFSET 동적범위보다 월등한 처리속도를 보여줍니다.
    OFFSET과 마찬가지로 보편적으로 사용되는 동적범위 수식이며, 이번 강의에서 자세히 알아봅니다.
  3. 표기능을 이용한 동적범위
    엑셀에 기본적으로 내장된 표기능을 사용한 동적범위입니다. 초보자도 이용할 수 있는 가장 쉬운 방법이며 표에서 제공되는 다양한 부가기능을 같이 이용할 수 있는 장점이 있습니다.
    하지만 '표'를 이용하므로 일반 범위를 사용하는 것에 비하여 데이터 입력 및 편집이 다소 제한되는 단점이 있습니다..

각 동적범위의 장/단점 비교

장단점비교 OFFSET/INDEX 동적범위 표(TABLE)기능 동적범위
장점 1. 더욱 융통성있고 유연하게 원시자료(Raw Data)를 편집할 수 있음.
2. 표기능보다 적은 단계로 적용가능
3. 워크시트를 더욱 깔끔하게 사용가능
1. 엑셀에 내장된 기능으로 초보자도 사용가능
2. 같은 행의 특정 열에 빈칸이 있더라도 동일한 표로 인식하여 범위를 자동으로 잡아줌.
3. 많은 양의 동적범위가 필요할 경우 작업시간 단축에 용이
단점 1. OFFSET과 INDEX함수에 대한 기본 지식 필요.
2. 많은 양의 데이터를 다룰 시 편집속도 저하를 일으킬 수 있음.
1. 표기능의 특성상 연속된 열 또는 행에 한하여 동일한 표로 간주됨.
2. ‘정규화’되지 않은 자료에는 표기능으로 동적범위를 적용하기 어려움.

INDEX 함수 알아보기

= INDEX ( 참조범위, 행번호(위아래), [열번호(좌우)] )
  • 행번호와 열번호는 '1'부터 시작합니다.
  • 행번호나 열번호로 '0'을 입력시, '전체행' 또는 '전체열'을 참조합니다. 즉, 결과값으로 배열(범위)를 반환합니다.

INDEX 함수 기초설명 바로가기

엑셀 INDEX 함수 동적범위 3
INDEX 동적범위 간단예제
수식 결과값
= INDEX($A$1:$D$4,1,3) 3
= INDEX($A$1:$D$4,4,5) #N/A   '// 범위를 벗어난 값이므로 #N/A를 반환합니다.
= INDEX($A$1:$D$4,0,3) {3, 7, 11, 15} '// 3번째열, 배열을 반환합니다.
= SUM(INDEX($A$1:$D$4,1,0)) 10 '// = 1+2+3+4 의 합계
= SUM(INDEX($A$1:$D$4,0,0)) 136 '// 전체 범위 합계
INDEX 함수 동적범위 공식
=시작셀:INDEX(참조범위, COUNTA(상하범위), COUNTA(좌우범위) )
  • 시작셀 : 동적범위가 시작될 시작셀입니다.
  • 상하범위 : 새로운 값이 추가될 시, 항상 값이 입력되는 비어있지 않는 범위 (예: 고유값범위)를 입력합니다.
  • 좌우범위 : 항상 값이 입력되는 좌우범위 (예: 머릿글범위)를 입력합니다.
  • COUNTA 함수 사용으로 인한 빈칸인식 문제해결 방법은 함수마스터 3강에서 자세히 다뤄드렸습니다.
INDEX 함수 동적범위 만들기 예제
=$A$2:INDEX($A:$C,COUNTA($A:$A),COUNTA($1:$1))
INDEX 동적범위 예제1
INDEX 함수 동적범위 만들기 예제
  • COUNTA($A:$A) '// = 9 를 반환합니다. 항상 값이 비어있지 않은 아이디 범위를 상하범위로 입력합니다.
  • COUNTA($1:$1) '// = 3 을 반환합니다. 머릿글 범위를 좌우범위로 입력합니다.
  • INDEX($A:$C, COUNTA($A:$A), COUNTA($1:$1)  '// $C$10 셀이 결과값으로 반환됩니다.
  • $A$2:INDEX($A:$C, COUNTA($A:$A), COUNTA($1:$1)  '// $A$2:$C$10 범위가 결과값으로 반환됩니다.
전체 시트를 동적범위로 설정하는 방법
=$A$1:INDEX($1:$1048576,COUNTA($A:$A),COUNTA($1:$1))
INDEX 동적범위 시트 전체 참조
시트 전체 범위를 참조하여 동적범위를 생성할 수 있습니다.
  • $A:$A 과 $1:$1 범위는 고유값, 머릿글 위치에 따라 변경할 수 있습니다.
  • 해당 공식은 시트안에 '1개'의 데이터세트만 존재한다는 가정하에 사용할 수 있습니다. 시트안에 여러개의 표가 있을시, 각각의 표에 동적범위 공식을 적용합니다.
  • 엑셀 2003 이전 버전 사용자는 $1:$65535 으로 INDEX 함수의 참조범위를 변경합니다.

실시간으로 업데이트 되는 엑셀 동적차트 만들기 

표기능을 사용하여 동적차트 만들기

  1. 예제파일의 '동적차트(표)' 시트로 이동합니다. 데이터의 아무곳이나 클릭한 뒤, [삽입] - [표]를 선택하여 범위을 표로 변환합니다. (또는 단축키 Ctrl + T 를 입력합니다.)

    표 만들기
    범위를 표로 번환합니다. (단축키 : Ctrl + T)

  2. 표를 선택한 뒤, [삽입] - [추천차트]를 선택하여 차트를 삽입합니다. (또는 단축키 Alt - N - R 을 차례대로 입력합니다.)

    표 차트 형식 변경
    표에서 새로운 차트를 삽입합니다.

  3. 차트가 생성되었지만, 매출액과 판매개수 사이의 격차로 인해 판매개수가 아래로 눌려있습니다. 차트를 더욱 보기 좋게 변경해주겠습니다.

    판매개수 눌림
    숫자 단위차이로 인해, 판매개수가 아래 눌려서 생성됩니다.

  4. 차트를 우클릭 한 뒤, [차트종류변경] - [혼합] 으로 이동합니다. 이후 '판매개수' 계열 보초축에 체크합니다.

    동적차트 혼합차트로 변경
    판매개수 계열을 보조축에 출력합니다.

  5. 이후 표에 새로운 내용이 입력되면, 차트가 자동으로 갱신됩니다.

    표기능 엑셀 동적차트 완성 GIF
    표를 이용한 엑셀 동적차트가 완성되었습니다.

  6. 단, 표기능을 사용하여 동적차트를 만들 경우 2가지 주의사항이 있습니다.
    - 병합된 셀이 포함된 범위를 표로 변환하면, 병합된셀이 분리되어 표가 생성됩니다. 따라서 범위에 병합된 셀이 포함되어 있을 시, 동적범위를 사용하여 동적차트를 제작합니다.
엑셀 동적차트 표 범위 문제
표는 연속된 데이터만 같은 표의 데이터로 인식합니다.

- 표는 연속된 행 또는 열의 데이터만 표의 내용으로 인식합니다. 따라서 새로운 데이터는 반드시 연속된 형태로 입력해야합니다.

동적범위를 사용하여 동적차트 만들기

본 강의에서는 INDEX 함수 동적범위를 사용하였지만, OFFSET 함수를 사용해도 동일하게 동적차트를 제작할 수 있습니다.

  1. 예제파일의 '동적차트(동적범위)' 시트로 이동합니다. 이후 [수식] - [이름관리자] 로 이동하여 새로운 이름정의범위를 생성합니다. (또는 단축키 Ctrl + F3키를 입력하면 이름관리자로 이동합니다.)
    엑셀 동적범위 만들기
    새로운 동적범위를 생성합니다.

    - 차트에는 머릿글이 '제외'된 데이터가 입력됩니다.
    - 셀 병합이 된 범위를 동적범위로 설정할 경우, 실제 데이터가 입력된 범위(=맨 좌측범위)로 설정하는것에 주의합니다.

    이름정의범위 이름 참조대상 수식
    rng날짜 =$A$2:INDEX($A:$A,COUNTA($A:$A))
    rng매출액 =$B$2:INDEX($B:$B,COUNTA($B:$B))
    rng판매개수 =$D$2:INDEX($D:$D,COUNTA($D:$D))
  2. 데이터 범위를 선택한 뒤, [삽입] - [추천차트] 로 이동하여 차트를 생성합니다. (또는 단축키 Alt - N - R 를 차례대로 입력하여 추천차트로 이동합니다.)

    엑셀 차트 삽입하기
    범위를 선택한 뒤, 새로운 차트를 생성합니다.

  3. 매출액과 판매개수 사이의 격차가 커서, 판매개수 값이 아래로 눌린 형태로 생성되었습니다. 차트를 더욱 보기좋게 변경하기 위해 차트를 우클릭한 뒤, [차트종류변경] - [혼합]으로 이동합니다. 이후 '판매개수'의 '보조축'을 활성화합니다.

    엑셀 동적차트 혼합차트로 변경하기
    '차트종류변경' 에서 '판매개수'에 보조축을 활성화합니다.

  4. 차트에 연결된 데이터범위를 동적범위로 수정합니다. 차트를 우클릭한 뒤, [데이터선택]으로 이동합니다.

    차트 데이터 선택 이동
    차트를 우클릭한 뒤, 데이터선택으로 이동합니다.

  5. 병합된 셀로 인한 '빈 데이터 계열'은 목록에서 제거합니다.

    동적차트 빈 데이터 계열 제거하기
    빈 데이터 계열은 목록에서 제거합니다.

  6. [범례항목]의 '매출액'과 '판매개수'의 [편집]을 클릭하여 각 계열값을 이전에 생성해둔 동적범위로 변경합니다.
    동적범위 데이터 계열 추가
    기존의 계열값 범위를 미리 생성해둔 '동적범위'로 변경합니다.

    - 단, 동적범위 입력시 '반드시' 시트명을 포함하여 동적범위를 입력하는것에 주의합니다. (예: '시트명'!rng범위)
    - 데이터범위 변경 후 판매개수가 아래로 눌려 출력될 시, 다시 '차트종류변경'으로 이동하여 '혼합'차트로 재변경합니다.

    계열이름 계열 값
    ='동적차트(동적범위)'!$B$1 (= 매출액) ='동적차트(동적범위)'!rng매출액
    ='동적차트(동적범위)'!$D$1 (= 판매개수) ='동적차트(동적범위)'!rng판매개수
  7. 가로축항목도 [편집]을 클릭하여 계열값을 동적범위로 변경합니다.
    엑셀 동적차트 축 레이블 추가
    차트의 레이블범위도 '동적범위'로 변경합니다.
    축 레이블 범위
    ='동적차트(동적범위)'!rng날짜
  8. 엑셀 동적차트 만들기가 완료되었습니다. 이제 표에 새로운 데이터를 추가하면 동적 차트가 실시간으로 업데이트 됩니다.
    동적범위를 사용한 동적차트는 병합된 셀에서도 차트를 만들수 있습니다. 따라서, 표기능을 사용한 동적차트보다 더욱 유연하게 차트를 제작할 수 있는 장점이 있습니다.

    동적범위 엑셀 동적차트 완성 GIF
    동적범위를 이용한 엑셀 동적차트 만들기가 완료되었습니다.

링크: MS 홈페이지 엑셀 동적범위 관련 상세설명 보러가기

5 11 투표
게시글평점
guest
25 댓글
Inline Feedbacks
모든 댓글 보기
박기범
박기범
2020년 1월 7일 12:01 오전
게시글평점 :
     

안녕하세요.
예제와 같이 ppt예제 sheet 에서 "=INDEX($A$1:$G$9,0,3)" 의 내용을 I9에서 했을때는 결과값이 나오는데, 그 밑에 I10, I11,I12 셀에서는 "#VALUE!" 값이 뜹니다.
왜 이런지 알려주실 수 있을까요? 바쁘신데 감사합니다.

Andre
Andre
2020년 2월 7일 7:12 오후
게시글평점 :
     

안녕하세요?

예제중 계열값에 rng매출액, rng 판매개수 등을 기입하면

오류가 발생하면서 (오류타입 : =1+1, sell shows : 2) 이런 오류 메세지가 뜨는데요...왜 이런 걸 까요?

감사합니다.

전유민
전유민
2020년 3월 6일 5:29 오후
게시글평점 :
     

좋은 정보들을 알려주셔서 감사합니다.

흑흑흑
흑흑흑
2020년 3월 12일 10:28 오전
게시글평점 :
     

안녕하세요 강의 보고 실무에 응용하려고 합니다 🙂 덕분에 좋은 팁을 얻게 되어 감사합니다 실무에 적용 중 어떤 새 이름은 동적차트가 잘 적용되지만 어떤 이름은 자꾸 값이 중간이 잘려 나옵니다.. 해결… 더보기 »

박장수
박장수
2020년 4월 5일 5:22 오후
게시글평점 :
     

강의 잘 들었습니다. 21:05 부분에서 제 엑셀로 차트를 만들면, 동영상에서 보이는 엑셀과 달리, 범례에 "계열1", "계열2", "계열3", "계열4"로 표시되고, 가로축 맨 왼쪽 값에 "날짜" 가 있습니다. 범례에 왜 계열값이 표시되는지,… 더보기 »

치킨치킨
치킨치킨
2020년 4월 6일 2:38 오후
게시글평점 :
     

안녕하세요. 강사님 너무나 즐겁게 듣고 있는 수강생입니다. 올려주신 강의 예제를 통해 업무를 진행하고 있어 감사할 따름입니다. 버전은 365를 사용하고 있으며, 현재 올려주신 예제와 같이 동적차트에서 보조축을 추가하고 꺾은선이 아닌 묶은… 더보기 »

고재봉
고재봉(@-298)
2020년 4월 7일 11:30 오후
게시글평점 :
     

감사합니다.

유은혜
유은혜
2020년 4월 7일 11:43 오후
게시글평점 :
     

=Raw!$A$1:INDEX(Raw!$1:$1048576,COUNTA(Raw!$A:$A),COUNTA(Raw!$1:$1))
안녕하세요.
업무파일을 업로드 할수가 있어서 수식만 카피하여 문의 드려요.
위에 수식을 사용하였더니 행범위가 70번째까지밖에 잡히지가 않아요.. 혹시 위 수식보시고 문제가 어떤지 파악이 가능할까요? 감사합니다. 많은 도움 받고 있습니다.

우지원
우지원
2020년 7월 3일 3:33 오후
게시글평점 :
     

오빠두님 팬입니다. 우선 항상 좋은 정보 제공해주셔서 감사드립니다. 오빠두를 통해서 동적함수등등 배우고 있는데요, offset함수를 이용해서 데이터 명칭을 지정한후, index match함수를 이용하여 결과값들을 자동으로 들고오는데요, offset함수에 지정된 데이터 값들 중에서 중간에… 더보기 »

Last edited 1 년 전 by 아리감독
아리감독
아리감독(@wooarisnaver-com)
2020년 7월 5일 10:54 오전
답글 남기기  오빠두엑셀

제가 너무 두서없이 질문을 드렸지만 정확하게 이해해주시고 답변해주신 오빠두님 정말 감사합니다. 동일한 넓이와 길이가 되어야되네요.
진심으로 감사드립니다.
언제나 응원합니다. 수고하십시오.

YOO
YOO
2020년 9월 15일 3:09 오전
게시글평점 :
     

안녕하세요, 동적 범위에 관한 강의 잘 보았습니다. 항상 좋은 내용 가르쳐주셔서 감사드립니다. 다름이 아니라 동적 차트 관련하여 궁금증이 있는데요. 엑셀에 새로운 값을 입력하면, 가로축 값(위에서는 날짜)이 자동으로 추가되는 게 아니라,… 더보기 »

YOO
YOO
2020년 9월 15일 10:38 오후
답글 남기기  오빠두엑셀

답변 감사드립니다!

피죤
피죤(@jg5673naver-com)
2020년 9월 29일 9:41 오후
게시글평점 :
     

강의 잘 들었습니다. 반복 연습이 필요할 것 같군요.

딱대
딱대(@hhheusnaver-com)
2020년 11월 24일 5:33 오후
게시글평점 :
     

COUNTA($A:$A) '// = 9 를 반환합니다. 항상 값이 비어있지 않은 아이디 범위를 상하범위로 입력합니다. COUNTA($1:$1) '// = 3 을 반환합니다. 머릿글 범위를 좌우범위로 입력합니다. INDEX($A:$C, COUNTA($A:$A), COUNTA($1:$1)  '// $C$10 셀이 결과값으로 반환됩니다. ㄴ--여기가 이해가 안가서요… 더보기 »

Vagabond
Vagabond(@moon_sknaver-com)
2020년 12월 6일 9:59 오전
게시글평점 :
     

친절한 강의 감사합니다~ ^^;

cgh0222
cgh0222
2021년 6월 14일 2:52 오후
게시글평점 :
     

정말 유용한 강의 잘 들었습니다. 많은 도움을 받았습니다 질문의 요지는 "동적범위가 적용된 탭을 복제해서 동적범위를 유지 하면서 사용할 수 있는 방법은 없나요?" 입니다. 강의 해주신 내용을 어느 정도 숙지해서 동적… 더보기 »

Last edited 5 월 전 by cgh0222
25
0
여러분의 생각을 댓글로 남겨주세요.x