이번 강좌는 짤막한 강좌를 모두 완독 하신 애독자 윤*혁님의 질문으로 표에 값을 추가할 때마다 변하는 동적 차트를 만들어 보겠습니다.
실습 파일 다운로드
일반적으로 엑셀에서 차트를 만드는 방법은 차트를 만들 데이터 범위를 선택하고 차트 종류를 선택하면 쉽게 만들 수 있습니다.
원본으로 사용한 데이터 표의 행(일자)이 열(팀명) 보다 항목수가 많아 가로(항목) 축이(항목) 일자가 되고 팀명이 범례 항목(계열)으로 (계열) 표시되어 작성되었습니다.
단순하게 차트 데이터 범위를 지정하고 차트를 만들면 지금처럼 자동으로 범례 항목(계열)과 가로(항목) 축 레이블이 구분되어 작성되지만, 차트 데이터 범위를 동적 범위로 지정한다고 해서 차트가 변하지는 않습니다.
표에 데이터를 추가하거나 삭제할 때마다 변하는 동적 차트를 만들려면 직접 범례 항목(계열)과 가로(항목) 축 레이블을 각각 동적 범위로 사용해야 합니다.
그럼 먼저 차트에 사용할 데이터를 OFFSET 함수를 사용하여 계열과 항목 범위를 동적으로 인식하도록 이름을 설정해보겠습니다.
1. [수식] 탭 → [정의된 이름] 그룹 → [이름 정의]를 선택합니다.
2. [새 이름] 대화 상자가 실행되면 이름 입력창에 일자를 입력하고 참조 대상 입력창에 클릭합니다.
OFFSET 함수 식을 입력하기에 창이 작아 불편하므로 창 크기를 아래와 늘려줍니다.
3. =OFFSET(을 입력하고 [B3]을 선택한 후 쉼표를 입력하고 0, 0,을 입력합니다.
그 다음 COUNTA(함수를 입력하고 [B] 열 번호를 클릭한 후 괄호를 닫고 쉼표를 입력한 후 1을 입력하고 OFFSET 함수 괄호를 닫습니다.
완성된 수식은 아래와 같습니다.
=OFFSET(동적차트!$B$3,0,0,COUNTA(동적차트!$B:$B),1)
이해를 돕기 위해 OFFSET 함수에 사용된 인수를 소개하겠습니다.
첫 번째 인수는 이름을 정의할 기준이 되는 셀입니다.
두 번째, 세 번째 인수는 기준 셀에서 실제 이름을 지정할 시작 셀의 행과 열의 상대 번지를 지정하는 인수입니다.
기준 셀이 이름을 지정할 시작 셀이므로 행과, 열의 상대 번지는 0입니다.
네 번째 인수가 동적으로 범위를 구합니다.
COUNTA 함수는 셀의 개수는 세는 함수입니다.
[B] 열 머리글을 마우스로 클릭하면 $B:$B라고 입력되는데 B열 전체를 의미합니다.
COUNTA 함수가 B열 전체 개수를 셉니다.
다섯 번째 인수는 기준 셀에 몇 개의 열을 이름 범위로 지정할지 정합니다.
다시 수식을 확인해 보면 =OFFSET(동적차트!$B$3,0,0,COUNTA(동적차트!$B:$B),1)
기준 셀 [B3]에서 9행, 1열이 이름 범위로 지정됩니다.
4. [수식] 탭 → [정의된 이름] 그룹 → [이름 관리자]를 선택합니다.
방금 추가한 이름 일자를 확인할 수 있습니다.
일자를 선택하고 아래쪽 참조 대상 입력창에 클릭하면 시트 상에 이름으로 정의된 범위가 초록색 점선 범위로 확인할 수 있습니다.
이름이 제대로 정의되었는지 지금처럼 확인하면 됩니다.
같은 방법으로 A팀에서 D팀까지 동적 범위를 이름으로 정의해보겠습니다.
5. [이름 관리자] 대화상자에서 [새로 만들기] 버튼을 클릭합니다.
6. 이름 입력창에 ‘A팀’을 입력하고 참조 대상 입력창에 아래와 같이 수식을 입력합니다.
=OFFSET(동적차트!$C$3,0,0,COUNTA(동적차트!$C:$C)-1,1)
각 팀의 동적 범위를 설정하는 수식은 일자 범위를 지정하는 방법과 동일하지만 COUNTA 함수 결과에서 1을 빼줘야 합니다.
아래 그림을 참고하세요.
A팀 이름을 선택하고 참조 대상 입력창을 클릭합니다.
시트 상에 A팀 범위가 제대로 인식하는지 확인합니다.
A팀 범위를 설정한 것과 같은 방법으로 B팀에서 D팀까지 동적 범위를 설정합니다.
이제 차트를 수정해보겠습니다.
1. 차트를 선택하고 [차트 디자인] 탭 → [데이터] 그룹 → [데이터 선택]을 선택합니다.
[데이터 원본 선택] 대화 상자가 실행되면 오른쪽 [가로(항목) 축 레이블]에서 [편집] 버튼을 클릭합니다.
축 레이블 범위 입력창에 =동적차트!일자를 입력하고 [확인] 버튼을 클릭합니다.
여기서 =동적차트! 는 현재 시트명을 의미합니다.
일자는 조금 전에 동적 범위로 지정한 이름입니다.
이번에는 [범례 항목(계열)]의 [편집] 버튼을 클릭합니다.
계열 값 입력창에 =동적차트!A팀을 입력하고 [확인] 버튼을 클릭합니다.
같은 방법으로 B팀을 선택하고 [편집] 버튼을 눌러 계열 값을 지정합니다.
C, D 팀도 [편집] 버튼을 눌러 같은 방법으로 설정하면 됩니다.
자~ 모든 작업이 끝났다면 표에 데이터를 추가하면 차트가 변경되는지 확인해 보겠습니다.
표 맨 아래에 7/10 데이터를 추가했더니(연도는 생략 가능) 오른쪽 차트에 바로 반영이 됩니다.
잘되시나요?
응답하세요~~~ㅋ
마지막으로 하나 더!
[I3:L6] 셀 범위를 봐주세요.
여기도 표에 값이 추가될 때마다 합계와 평균이 자동으로 구해지도록 하려고 합니다.
이미 팀 별 데이터 동적 범위로 이름을 지정해 두었기 때문에 바로 인수로 이름을 지정하면 됩니다.
=SUM(A팀)으로 수식을 수정하면 데이터가 추가되거나 삭제될 때 자동으로 변경됩니다.
평균도 마찬가지죠? =AVERAGE(A팀)으로 수식을 수정하면 됩니다.
이전에 작성한 동적 범위 강좌를 참고하세요.
도움이 되실 겁니다.
엑셀 49강] Offset 함수를 사용하여 동적 범위 설정하기
유효성 검사와 Offset 함수를 사용하여 동적 범위를 설정하는 방법에 대해서 소개하겠습니다. 실습 데이터 다운로드 ↓ | 데이터 유효성 설정 먼저 품명 셀의 목록을 클릭하여 소모품명을 선택할 수 있도록 데이..
hantip.net
엑셀 50강] 이름 범위를 동적 범위로 설정하여 함수식에서 활용하기
동적 범위를 이름으로 정의하는 방법과 활용법에 대해 소개하겠습니다. 범위를 이름으로 정의해 두면 함수식을 작성할 때 편리합니다. 특히나 동적 범위로 만들면 데이터가 추가 또는 삭제가 되더라도 함수식을 수..
hantip.net
필요하신 분들께 도움이 되기를 바라며 강좌는 마무리하겠습니다.
'엑셀' 카테고리의 다른 글
| 엑셀 실무] VLOOKUP, INDEX, MATCH 함수를 사용하여 다른 시트에서 이미지 가져오기 (0) | 2019.07.29 |
|---|---|
| 엑셀 실무] 표 서식을 활용하여 동적 차트 쉽게 만들기 (0) | 2019.07.21 |
| 엑셀 차트] 원하는 대로 만들어 지지 않는 차트 해결편 2탄 → 일자별 세로 막대 차트 (0) | 2019.07.09 |
| 엑셀 차트] 원하는대로 만들어지지 않는 차트 해결편 1탄 → 콤보 차트 (0) | 2019.07.09 |
| 엑셀 보고서용 차트 만들기 5편] 차트에 표현할 값 범위가 편차가 클 경우 콤보 차트 (0) | 2019.06.30 |