엑셀 차트로 등락률 그래프(이퀄라이저) 그리기 2

앞에서 이퀄라이저 그래프 그리기를 해 보았습니다.

그런데 이퀄라이저 그래프를 그리기 위해 음수열과 양수열을 별도로 추가하고 마지막에는 추가한 열들을 보이지 않게 열 숨기기를 하였습니다.

여기서 어떤 분들은 열을 추가하거나 숨기지 않고 그릴수 있는 방법은 없을까? 궁금해 하시는 분도 있을 거라 생각해서 차트를 그릴때 이름정의를 이용해서 계열값을 입력하는 방법에 대해 설명 드리겠습니다.

<음수값 열과 양수값 열을 넣지 않고 그래프 그리기>

수식이해하기

아래 수식은 등락률 좌우측에 그려지는 그래프 계열값의 배열수식을 설명 하기 위해 다시 설명드립니다.

등락을 표시하는 파란막대와 빨간막대 좌 우/측에 회색으로 된 막대는 등락률이 음수일때는 등락률의 최대값, 양수일 때는 등락률의 최소값을 사용하였습니다. 

이퀄라이저 그래프 그리기 1 보기

☞ 수식설명

1. 음수값 수식 = IF(E5<0,0,MIN($E$5:$E$18)) : 등락률이 0보다 작을 때는 0, 0보다 크거나 같을때는 등락률에서 최소값을 반환

2. 양수값 수식 = IF(E5>0,0,MAX($E$5:$E$18)) : 등락률이 0보다 클때는 0, 0보다 작거나 같을때는 등락률에서 최대값을 반환

배열수식을 이름정의하기(위 수식과 아래 수식을 비교 해보세요)

1. 리본메뉴 [수식] - [이름정의] 해서

2. 아래의 2가지의 이름(음수값, 양수값) 을 정의

☞ 배열수식설명

1. 음수값 수식 = IF(이퀄라이저!$D$5:$D$18<0,0,MIN(이퀄라이저!$D$5:$D$18)) : 등락률이 0보다 작을 때는 0, 0보다 크거나 같을때는 등락률에서 최소값을 반환하는 배열수식

2. 양수값 수식 = IF(이퀄라이저!$D$5:$D$18>0,0,MAX(이퀄라이저!$D$5:$D$18)) : 등락률이 0보다 클때는 0, 0보다 작거나 같을때는 등락률에서 최대값을 반환하는 배열수식

데이터 선택(계열추가)

1. [차트]에서 우측마우스 클릭 [데이터 선택]

2. [데이터 원본 선택] 창 [범례 항목(계열)]에서 [추가] 버튼을 클릭

3. 아래 그림과 같이 [음수값]과 [양수값]의 계열을 추가 

※ 계열값에 이름정의 넣기

차트의 데이터 계열값은 데이터 유효성 목록에 입력하듯이 그냥 = 음수값 이라고 하면 수식 오류 값이 나타남

계열값에 이름정의 넣는 방법   =  '파일명.확장자' ! 정의된이름

1. 음수 계열값 = '이퀄라이저 그래프2.xlsx'!음수값

2. 양수 계열값 = '이퀄라이저 그래프2.xlsx'!양수값

차트(그래프) 색상 조정

막대그래프의 색상을 회색으로 테두리는 없음으로 변경(양수값, 음수값 모두)

※ 수식입력창에 보면 선택한 그래프의 계열값이 ['이퀄라이저 그래프2.xlsx'!양수값] 임을 알 수 있습니다.

열숨기기와 이름정의 비교

※ 이번 시간에서 핵심은 이름정의와 배열수식을 활용하여 차트를 그리는 것입니다. 

① 여러 조건의 그래프를 한 개의  차트에 그린다거나 

② 별도의 데이터 표를 그리지 않고 차트를 그리기 위해서

③ 네이버 폼이나 구글 폼에서 설문조사 결과를 다양한 그래프로 그릴때

는 반드시 차트에 이름정의를 넣는 방법과 배열수식을 이해 하셔야 합니다.

관련글 보기

이퀄라이저 그리기 1

이퀄라이저 그리기3

색연필 막대 그래프 그리기

첨부파일 보기

이퀄라이저 그래프2.xlsx