OFFSET 함수와 양식컨트롤 이용하여 엑셀 챠트에 스크롤바 만들기 (1)

장기간에 걸친 데이터를 챠트로 표현할 경우 챠트에 슬라이더바가 없으면 데이터의 간격이 너무나 조밀해져 트렌드를 보겠다는 목적이 아니라면 활용에 불편한 점이 많다.  

그리고 데이터가 추가될수록 그 조밀함은 더해지게 되어 데이터 정보를 보기에 불편하다는 점, 그리고 데이터 간격이 일정치 않다는 단점이 있다. 

예를 들면 아래와 같다. 아래는 2011년~2012년에 걸친 730개의 런닝 데이터를 챠트로 표현한 것이다. 주황색선은 누적 거리를 표현한 것이고 각각의 바는 해당일에 달린 거리를 유형별(필드, 트랙, 트레드밀)로 합산하여 나타낸 것이다. 

그러면 위의 챠트에 

1. 체크박스에 체크된 항목만 모아서 일자별 바를 만들고 

2. 누적거리는 보조축에 꺽은 선 형으로 표현하고 

3. 슬라이더 바를 달아 원하는 기간만큼 지정해서 볼 수 있게

만들어보자. 

완성된 모습은 아래와 같다. 

여기에 필요한 것은 

1. 체크여부를 TRUE, FALSE 로 받아서 체크된 데이터만 별도의 테이블에 표현하기

2. SERIES 함수를 이용해서 각 컬럼을 하나의 막대 그래프로 표현하기

3. 이름관리자와 OFFSET 함수를 이용하여 동적 챠트 구현

4. 슬라이더 컨트롤 설정

그리고 엑셀 시트는 다음의 2가지로 구성된다. 

dynamicChartData : 여기에는 데이터가 들어간다. 

Dynamic Chart : 챠트가 표현되는 시트 

STEP 1 : 체크박스로 챠트에 표현할 데이터 선택

엑셀 2007을 기준으로 하면, [개발도구] - [삽입] - [양식 컨트롤] 에서 '확인란'을 선택합니다. 

해당 체크 박스에 적절한 이름을 부여하고, 오른쪽 마우스를 클릭하여 [컨트롤 서식] 을 선택합니다. 컨트롤 탭에서 셀 연결을 하단 이미지에서 보듯이 해당 컬럼의 최하단으로 연결합니다.  

그렇게 되면 체크시 이 셀의 값이 TRUE 로 바뀌게 되고, 체크 해제시 FALSE 로 바뀌게 됩니다.

STEP 2 : 체크박스에 체크된 데이터만 표현하기

이제 데이터 테이블 우측에 테이블을 하나 더 만듭니다. 여기에는 Status 행에서 TRUE 로 표시된 경우만 원데이터를 가져오게 합니다. 그리고 FALSE 의 경우에는 값이 표시되지 않도록 합니다. 이를 위해서 각 셀에 아래의 식을 입력합니다.

그러니깐 2010-04-20 filed 셀(W113)에는 아래와 같이 입력합니다.

LEN(F113)=0 → 데이터가 들어가 있는 F113 의 셀에 아무런 값이 없으면 

"" → 널(null) 값으로 두고, 그렇지 않은 경우에는 

IF(F$1467,F113,NA()) → F$1467, 즉 상태값이 TRUE 이면 F113, 즉 원래값을 가져오고, 상태값이 FALSE 이면 NA() 로 둡니다. NA() 함수는 오류 값 #N/A를 표시하는 함수로, #N/A는 "어떤 값도 사용할 수 없음"을 의미합니다. 

STEP 3 : 누적 세로막대형 그래프 그리기

위와 같이 세로 막대에 각 컬럼의 값을 합산하여 보여주도록 SERIES 함수를 이용합니다. 

먼저 토대가 되는 기본챠트를 만듭니다.

[삽입] - [세로막대형] - [누적 세로막대형] 을 선택합니다. 

[데이터 선택]

[범례 항목(계열)] - [추가]

[계열 이름] 과 [계열 값] 항목에 범위를 선택하여 넣습니다.

[계열 값]은 추후 이름관리자를 통해 변경해서 사용할 예정입니다.

이렇게 만들고 챠트에 마우스를 가져다 놓으면 아래와 같은 함수가 만들어져 있는 것을 확인할 수 있습니다.

=SERIES(dynamicChartData!$W$3,,dynamicChartData!$W$373:$W$1465,1)

SERIES 함수의 인수

인수	필수/옵션	지정 내용
name	옵션	범례에 나타나는 이름
category_labels	옵션	항목 축에 나타나는 레이블로, 생략된 경우에는 연속된 정수를 레이블로 사용
values	필수	값 상자에 나타나는 부분 Excel이 계열 순서에 필요한 순서를 지정하는 값
order	필수	계열 순서 지정 그래프 아래에서 부터 1, 2, 3

나머지 데이터 계열도 위와 같이 입력합니다. 다음편에 이어서....