[Excel Tip] 1. 차트에서 값이 '0'일 때 데이터 레이블 제거하기

excel_gmdj_20130327.xlsx

■ 오늘의 TIP : 차트에서 값이 '0'일 때 데이터 레이블 제거하기

    - 사용자 정의 서식에 대한 기본적 이해(완료 예제 파일 첨부)

 엑셀에서 차트를 작성하다보면 보는 이의 편의성을 위해 종종 데이터 레이블을 사용하곤 합니다. 

그러나 간혹 아래의 차트와 같이 시계열변동에 관한 차트를 만드는 경우, 아직 다가오지 않은 기간에도 

데이터 레이블이 불필요하게 표시되는 문제가 있는데, 오늘은 이 문제를 근본적으로 해결해 보도록 하겠습니다. 

# 그림 1. 원본 차트

 

위의 차트에서 수치가 '0'일 때, 데이터 레이블을 보이지 않게 하는 방법은 두 가지가 있습니다.

그 중에 하나는 편법이요, 하나는 정법(正法)인데, 

편법은 세로 축서식의 최소값을 0보다 큰 값으로 고정하는 방법입니다. 

위의 표에서 좌측 세로축 서식에서 최소값을 1로 고정하면 수치가 '0'인 경우 데이터레이블은 표시되지 않습니다. 

# 그림 2. 세로축 최소값을 '1'로 변경 후.

 

하지만 편법은 어디까지나 편법일 뿐...

위 문제를 해결하기 위한 올바른 방법은 '사용자 정의 서식'의 사용에 있습니다.

엑셀에서는 문자 및 숫자에 대한 여러가지 기본서식(일반, 회계, 날짜, 숫자, 통화 등)을 제공합니다.

다만 제공되지 않는 서식을 사용하고 싶은 경우 엑셀에서 제공하는 기호를 이용하여 서식을 직접 만들어 사용할 수 있으며, 

이를 사용자 정의 서식이라고 합니다. 

사용자 정의 서식에 사용되는 기호표는,

1) 0 : 숫자의 자리수 정의

2) # : 서식을 표시할 때 사용하지 않는 '0'을 제거

3) , : 천단위 정의

4) . : 소숫점 정의

5) ; : 섹션 구분, 여러 개의 서식을 중복으로 사용할 수 있도록 함.

(사실 몇 가지 더 있지만 더 들어가면 이해가 어렵기 때문에 다음 기회로 미루도록 하겠습니다.)

기본서식 중 '통화'를 사용자 정의에 사용되는 기호로 풀어보자면 아래와 같습니다. 

#,##0

이 서식을 풀어보자면, 

1) #으로 사용하지 않는 '0'은 표시하지 말라

2) 천단위마다 쉼표(,)를 찍어라.

3) 0이 숫자의 일단위에 있으니 일단위 미만의 숫자는 표시하지 말라

이 정도가 되겠습니다. 

만약 셀에 입력된 숫자가 3565410이라고 한다면, 통화서식으로 변경 시 3,565,410으로 표시되겠죠?

그리고 0.5라면 0이 표시됩니다. 

이번에는 이걸 좀 더 확장해서 서식은 통화로 하되, 소숫점 두자리를 늘려 보겠습니다.

소숫점은 자리수와 관련이 있기 때문에 0이라는 기호가 사용되며, 소수점의 정의는 '.'으로 하게 됩니다.

따라서 기본 통화의 서식인 #,##0에 소수점을 먼저 추가하여 #,##0.으로 만들고,

여기에 추가되는 자리수인 소숫점 두자리를 표시하기 위해 #,##0.00으로 만들어 줍니다. 

이러면 아까의 3,565,410은 3,565,410.00으로 표시되고 0.5는 0.50으로 표시가 됩니다. 

여기까지 이해하신 분들은 일단 한숨 돌리시길 바라며....

------------------------------------------------------------------------------------------------

준비되신 분은 다음 코스인 섹션 기호 즉 세미콜론(;)으로 넘어가 보도록 하겠습니다. 

세미콜론의 경우 한 셀에 여러가지의 서식을 중복으로 적용할 수 있도록 합니다.

이를 테면 0이상인 경우 소숫점 이하는 표시하지 않는 통화 서식을 이용하라는 서식은 다음과 같습니다.

[>=0]#,##0

그리고 0보다 작을 경우 소숫점을 두 자리까지 표시하는 통화서식을 이용하라는 서식은 다음과 같습니다.

[<0]#,##0.00

이 두 개의 서식을 중복으로 적용할 때 바로 위에서 말씀 드렸던 세미콜론(;)이 사용됩니다.

[>=0]#,##0;[<0]#,##0.00

(위 서식의 경우 0이상의 경우가 아니라면 모두 0보다 작은 경우가 되므로 좀더 축약하여,

[>=0]#,##0;#,##0.00 혹은 #,##0;[<0]#,##0.00 로 바꾸셔도 됩니다.)

이 서식을 사용하게 되면 아까는 따로 따로 표시되던 서식이 하나로 통합되어

셀에 3,565,410이 입력되면 3,565,410으로 표시되고 0.5가 입력되면 0.50으로 표시가 됩니다. 

이를 응용해서 데이터 레이블의 표시 형식을 사용자 정의 서식으로 만들자면,

원하는 조건은 0이하의 값은 표시하지 말라이므로, 사용자 정의 서식은 아래와 같이 만들어지게 됩니다.

[<=0]#;#,##0

여기서도 한숨 돌려 주세요. 힘드시죠? 이해합니다. 

그런데 이제 거의 끝까지 다 왔습니다. 

커피 한 잔씩 알아서 타오시고...마지막까지 힘내시길 바랍니다. 

마지막으로 이렇게 만든 서식을 차트에 적용하는 법을 알려 드리도록 하겠습니다.

위의 표를 예로 들면, 순서는 다음과 같습니다.

1) 편집할 데이터 레이블 선택 후 마우스 우클릭

2) 데이터 레이블 서식 선택

3) 데이터 레이블 서식 > 표시 형식

4) 표시 형식 > 사용자 지정

5) 서식코드(T)에 [<=0]#;#,##0 입력 후 '추가' 버튼 클릭

# 그림 3. 데이터 레이블 서식창

 

이제 이 창을 닫으면 다음 그림과 같이 0이하의 값은 차트에서 표시되지 않습니다. 

# 그림4. 완료 화면

 

여기까지 모두 고생하셨습니다.

사실 사용자 정의서식은 엑셀 초보 단계에서는 상당히 상위에 랭크되어 있는 기능입니다. 

(참고로 엑셀의 기본 기능을 마스터하고 VBA를 능숙히 다룰 정도면 초보는 아니다라고 말할 수 있겠습니다.)

그래서 보통은 엑셀의 기본기능을 익히는 단계 중 중반 정도에나 다루는 부분이기도 한데,

일단은 맛을 보여 드리는 의미에서 제일 먼저 다루게 되었습니다. 

사용자 정의 서식은 엑셀을 익히고 활용하는 데 있어 상당히 중요한 역할을 차지하고 있으니 모두 유념하시길 바랍니다.

다음 포스팅은 언제가 될 지 모르겠지만 되도록 빨리 올려 보도록 하겠습니다.

즐거운 한 주 되세요.^^*

Fin.