엑셀 움직이는 그래프 - egsel umjig-ineun geulaepeu

 [엑셀] 차트와 선을 함께 움직이게 하는 방법

  오늘은 오피스튜터 MVP인 권현욱(exceller)님이 제공해주신 "차트를 작성한 후 선을 표시하는 경우 차트가 변경되었을 때 선도 함께 움직이는 방법"에 대해서 알아보도록 하겠습니다. (사용버전: 엑셀 2003)

 110PPM 부분에 그리기 도구 모음에 있는 선을 이용하여 차트에 직선을 그려 넣으면 어느 공장이 잘했는지 여부를 한눈에 알 수 있습니다.

  그런데 차트의 위치를 조금 아래로 변경해야 할 경우가 생겼습니다.  차트를 클릭하고 조금 아래로 내렸더니 이런… 차트는 이동되었지만 공정불량률 목표를 나타내는 선은 움직이지 않고 따로 노는군요.

  이런 경우 따로 놀지 않고 함께 놀게 만드는 방법이 없을까요? 당연히 있으니까 이번 강좌를 시작했겠지요? ^^

  물론 Shift 키를 누른 채 차트와 선을 지정한 채 원하는 위치로 이동하거나 사전에 그룹으로 지정해 두는 방법도 있습니다만, 이번 시간에는 차트와 선을 한 몸(?)으로 만들어 버리는 방법에 대해 소개해 드리겠습니다.

<1> 선을 그리기 전에 차트를 먼저 선택합니다.

<2> 차트가 선택된 상태에서 앞에서와 마찬가지 방법으로 차트 내부에 선을 그립니다.

<3>차트 위에 직선이 그려졌습니다. 이제 이 차트의 위치를 임의의 위치로 이동시켜 보세요. 그룹 지정이나 다른 조작을 하지 않더라도 둘이 한 몸이 돼서 함께 움직이게 됩니다.

  비단 직선뿐만 아니라 차트 내에 어떤 도형을 삽입하든 차트를 먼저 선택한 상태에서 차트에 그려 넣게 되면 그 개체와 차트는 한 몸이 됩니다. 오늘 소개해 드린 내용은 엑셀, 워드, 파워포인트 등에서도 공통적으로 해당됩니다.

엑셀로 보고서를 쓰면서 참 고마운 기능이 바로 다양한 그래프다.

단순히 숫자의 나열을 시각화(쉽게 말해 그림

숫자의 흐름이나 특이점들을 읽어낼 수 있다. 이런 점에서 엑셀의 그래프 기능은 탁월하다고 생각한다.

그런데 문제는 한 번 그린 그래프와 연관된 수치가 수정되면 매번 다시 그려야 하는 일이

종종 생기고는 한다. 다음과 같은 경우에 말이다.

[ 월별 지점 및 전체 평균 판매수량 ]

위 표처럼 월별 판매량을 매월 집계한다고 가정해 보자.

매월 판매량이 입력될 때마다 그래프를 다시 그려야 한다면, 환장할 일이다. 정말~

그렇다면, 값만 입력하면 그래프가 쏴~악

이 작업을 하려면, offset함수, count함수, 그리고 이름정의라는 엑셀의 기능을 알아야 한다.

(얕은 Tip이라 했는데, 너 요즘~

1.

엑셀이 셀로 구성되어 있고  행과 열로 좌표를 만들 수 있다는 것을 가정한다면, 너무 쉬운 기능이다.

필요한 것은 출발점, 움직일 위치(몇 행과 몇 열을 움직일 것인가이다), 그리고 불러올 범위를 움직여온 위치에서 부터 결정할 수 있다.

잘 생각해 보면, 엑셀과 대화하는 느낌이다.

 월이 있는 셀(A6)을 출발점으로 해서 79까지 가려면, 아래로 한 행을 내려과 우측으로 3열을 움직이면 된다.

여기까지 이용할 수도 있고 특정 범위를 찾게 할 수도 있는 데, 이때 높이와 넒이를 결정하면 된다.

79에서 2행과 2열만큼의 범위를 선택한다는 것을 위 그림을 통해 알 수 있다.

이제 이를 offset함수를 통해 표현해 보면,

- reference: 출발점

- rows: 움직일 행 값

- cols: 움직일 열 값

- height: 움직인 위치에서 선택할 높이(행의 갯수)

- width: 움직인 위치에서 선택할 넓이(열의 갯수)이다.

일단 79까지 이동한다고 가정하면,  

이동해서 2행과 2열을 선택해야 한다면,

위와 같이 이용할 수 있는데, 값은 #VALUE!라고 뜬다. 이유는 범위값을 어떻게 하라는 말을 엑셀에게 하지 않아서 이다(

이제 친절하게 Offset함수로 설정된 범위값을 모두 합해 보라고 하자.

 값은 313 = 79 + 95 + 65 + 74인 것을 확인할 수 있다.

2. 이제 Count함수에 대해 알아보자.

Count함수는 선택한 범위 내에 숫자가 입력되어 있는 셀의 개수를 세어주는 함수인데, 일단 한 번 보자.

위 그림처럼 B6에서 M6까지의 범위에 숫자가 입력되어 있는 셀의 개수는 0개이다(월 입력 범위이므로).

또한 B7에서 M7까지에서는 총 5개의 셀에 숫자가 채워져 있는 것을 알 수 있다.

3. 이름정의

이게 뭔가~

엑셀에 있는 표를 통해 그래프를 그리기 위해서는 그래프에게 사용할 값들을 알려줘야 한다.

가장 기본적으로 필요한 값은 계열이름(그래프에 표현될 대상정도라고자 할까?

생각해 보면, 내가 입력만 하고 싶은 값은 위 세 가지로 구성되어 있을 확률이 매우 크다.

그렇기 때문에, 이 값들을 알아서 찾아주기만 한다면 난 앉아서 그래프를 다시 그리지 않아도 된다는 결론이고

내가 원하는 바가 바로 그것이라는 것을 알 수 있다.

이름 정의는 수식 > 이름정의를 클릭하면 정의할 수 있다.

그리고 안에 입력하고 이름을 정의할 값은 내가 그래프를 그리려고 할 때 필요한 값이라는 것을 잊지말자.

다시 말해, 표에서 월로 표시되어 있는 기간이 X축에 그리고 각 지점의 값과 전체 평균에 거기에 대응되도록 한다는 것을

생각한다면, 어떤 대상에 대해 이름을 정의해야 할지 알게 된다. 바로 월, A지점, 전체평균에 대해 이름을 정의하고 그 값들이

변함에 따라 그래프가 그려져야 하는 것이다. 

- 이름(N): 내가 불러올 값에 대해 부여한 이름

- 범위(S): 특정 Sheet에서만 쓸 것인지, 아니면 문저 전체에서 쓸 것인지를 결정하는 것으로

          만약 여려 Sheet에 동일 이름이 있을 수 있다면, 특정 Sheet에서만 사용되도록 정의해야 한다.

- 설명(O): 이름에 대한 설명을 적는 것으로 생략해도 무방하다.

- 참조 대상(R): 정의한 이름을 통해 불러올 값을 정의하게 된다.

자, 이제 정의 함 해볼까나 ~

첫 번째는 '월'이라고 정의한 이름이 활용할 값을 설정했는데,

의미는 Sheet1!$A$6에서 시작해서 아래로 0행, 오른 쪽으로 1열을 움직여서, 높이 1과 COUNT(Sheet1!$B$6:$M$6)) 값의

넓이만큼 값을 가져 오라는 의미이다. Count함수는 B6에서 M6까지 숫자가 입력된 셀의 개수만큼이 입력되게 된다.

두 번째는 '지점'이라고 정의하고 그 활용 값을 설정하는 것으로

의미는 첫 번째 함수를 해석하는 것과 같다.

같은 방식으로 전체 평균에 대해 '전체'로 정의한 값을 지정해 보자.

이렇게 그래프에 활용할 값을 다 정의했다면, 우선 평소대로 그래프를 그려보자.

그래프를 그릴 표 전체에 대해 블럭 지정 후 꺽은선 그래프를 선택하면,

위와 같이 그래프가 생성되는 것을 알 수 있다. 그리고 생성된 그래프는 1월부터 12월까지 모두 표시되어 있는 것을 볼 수 있는데

우리 목표는 현재 값이 입력된 마지막 월까지만 표시되고 입력할 때마다 월이 늘어나기를 바라는 것이다.

이제 평소과 같이 생성된 그래프의 데이터값 정의(이름정의를 활용한)를 통해 동적 그래프를 그려보자.

그래프 위에서 오른쪽 마우스 클릭을 통해 데이터 선택(E)을 선택해 보자.

그러면 위와 같이 데이터를 선택할 수 있는 있는 창이 뜨고

A지점, 전체평균, 그리고 가로(항목) 축 레이블을 우리가 사전에 정의한 값들로 대체해 보자.

A지점 선택 후 편집을 클릭하면, 아래와 같은 창이 나오고

계열값에 파일명과 해당 파일에서 정의한 이름(지점)을 계열 값(V)에 입력하고 확인을 누르면 된다.

='동적그래프.xlsx'!지점

'전체'로 정의한 전체 평균에 대해서도 동일한 규칙으로 정의하면 된다. 그리고 확인을 누르면,

위와 같이 값이 입력되어 있는 월만으로 구성된 그래프를 구할 수 있다.

이제 값을 추가로 입력할 때, 그래프가 자동으로 그려지는 지 확인해 보자. 된다면 대박이다. 안된다면 물론 ~

[ 월별 지점 및 전체 평균 판매수량 ]

이제 동적그래프를 알게 됐고 쓸 줄 알게 됐으니...

이제 일 좀 끝내고 칼퇴 좀 하자~ ^^ ​