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

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

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

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

동적차트 만들기 목차 바로가기
강의 요약

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

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

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

예제파일 다운로드

관련 기초함수
선택된 범위에서 '비어있지 않은 셀'의 개수를 구합니다.
선택한 범위에서 원하는 위치의 값을 반환합니다.

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

동적범위의 종류

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

  1. OFFSET 함수를 이용한 동적범위
    가장 보편적으로 사용되는 동적범위입니다. 수식을 작성하기 가장 편리하며, 다양한 방식으로 응용하기에도 적합합니다.
    다만, 넓은범위에 적용시 속도저하가 발생할 수 있으므로, 적은양의 데이터에 사용하기 적합합니다. (10만행 이하)
    자세한 내용은 ‘OFFSET 동적범위’ 강의를 참조해주세요.

  2. INDEX 함수를 이용한 동적범위
    데이터 양이 많거나 더욱 빠른 처리속도가 필요할 경우, INDEX 함수 동적범위를 사용합니다.
    특히 50만행이 넘어가는 많은양의 데이터를 다룰시 INDEX 함수를 사용하면 OFFSET 동적범위보다 월등한 처리속도를 보여줍니다.
    OFFSET과 마찬가지로 보편적으로 사용되는 동적범위 수식이며, 이번 강의에서 자세히 알아봅니다.
  3. 표기능을 이용한 동적범위
    엑셀에 기본적으로 내장된 표기능을 사용한 동적범위입니다. 초보자도 이용할 수 있는 가장 쉬운 방법이며 표에서 제공되는 다양한 부가기능을 같이 이용할 수 있는 장점이 있습니다.
    하지만 '표'를 이용하므로 일반 범위를 사용하는 것에 비하여 데이터 입력 및 편집이 다소 제한되는 단점이 있습니다..
  4. 각 동적범위의 장/단점 비교
    장단점비교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 8 votes
게시글평점
현재 페이지 댓글알림 신청
알림 설정
guest
14 Comments
Inline Feedbacks
View all comments
박기범
게시글평점 :
     

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

Andre
게시글평점 :
     

안녕하세요?

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

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

감사합니다.

전유민
게시글평점 :
     

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

흑흑흑
게시글평점 :
     

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

박장수
게시글평점 :
     

강의 잘 들었습니다. 21:05 부분에서 제 엑셀로 차트를 만들면, 동영상에서 보이는 엑셀과 달리, 범례에 "계열1", "계열2", "계열3", "계열4"로 표시되고, 가로축 맨 왼쪽 값에 "날짜" 가 있습니다. 범례에 왜 계열값이 표시되는지, 강사님 엑셀과 같이 표현하려면 어떻게 해야하는지 궁금합니다.

치킨치킨
게시글평점 :
     

안녕하세요. 강사님 너무나 즐겁게 듣고 있는 수강생입니다.
올려주신 강의 예제를 통해 업무를 진행하고 있어 감사할 따름입니다.

버전은 365를 사용하고 있으며,

현재 올려주신 예제와 같이 동적차트에서 보조축을 추가하고 꺾은선이 아닌 묶은 세로형 막대 차트를 사용하려고 합니다.
그런데 선생님이 올려주신 예제와 제가 만든 차트, 동일하게 각 '매출액'과 '판매개수'의 차트가 겹쳐 나타나는 것을 확인하였습니다.

해당 부분에 대해 각 '계열겹치기','간격 너비'를 설정해보았지만 변동되지 않았으며,
찾아본 결과 '각 차트 데이터 사이에 빈 값을 넣어 사용/빈값이 존재하는 열의 숭김처리'하는 방법의 사례는 확인하였습니다.

그러나 해당 방법은 2010 버전에서의 임시방편이라 생각이 들기도 하고

365버전에서는 개선이 된 것인지, 아니면 혹은 지금도 그 방법이외에는 뾰족한 수가 없는지 고민하고 있습니다.

부디 답변 남겨주시면 너무나 감사합니다ㅠ

고재봉
게시글평점 :
     

감사합니다.

유은혜
게시글평점 :
     

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

14
0
여러분의 생각을 댓글로 남겨주세요.x