엑셀: 셀 서식으로 주식처럼 증감(▲▼)표시를 나타내기(사용자지정 서식 설명)

엑셀을 사용하다 보면 화면에

  효과적인 표시를 위해

[셀서식]의 [표시형식]을

자주 만지게 됩니다. 그런데

[사용자 지정] 서식의 경우에는

기본적인 표시 원리를

알고 있어야 실무에 잘 활용할 수 있습니다.

사용자지정 서식에 대해 설명 후

주식처럼 숫자의 증감이 표시되는

사용자서식 설정법을 알아보겠습니다.

 

 

 사용자 지정 서식 익히기

 

셀 서식을 불러옵니다.

마우스 우클릭으로 불러올 수 있습니다.

단축키 Ctrl+1을 외워두면 편합니다.

 

맨밑에 [사용자 지정]으로 이동하면

다양한 [형식]이 나옵니다.

이 형식을 구성하는 문자들을 <서식코드>라고 합니다.

 

원하는 형식이 없다면

사용자는 서식코드를

직접 입력하여 새로운 형식을

만들 수 있습니다.

그러기 위해서는

서식코드의 특징을 알고 있어야 합니다.

 

G/표준

 

 

G/표준: 셀에 입력한 그대로 표시합니다.

 

가장 기본 형식입니다.

아래처럼 입력한대로 화면에 표시합니다.

그러나 긴 숫자를 넣으면 자동으로

간단히 표시해 버립니다.

또한 앞에 0을넣거나 소수점 맨뒤에 0을

넣으면 자동으로 생략해 버립니다.

 

그래서 숫자를 제대로 나타내기에는

G/표준 형식은 부적합합니다.

 

0과 0.00

0: 1의 자리까지만 표시

0.00: 소수점 둘째자리까지만 표시

 

0은 자릿수를 의미 합니다.

 

G/표준과 다른 점은 긴 숫자도 간단히

표시하지 않습니다.

0.00형식의 경우 소숫점자리가

0이라고 하더라도 생략하지 않습니다.

그러나 맨 앞에 0은 필요없는 숫자라고

판단하여 생략합니다.

왼쪽은 [0 형식], 오른쪽은 [0.00 형식]입니다.

설정해준 자릿수까지만 표시되고 있습니다.

 

#,##0과 #,##0.00

#,##0: 천단위 콤마/  1의 자리 표시

#,##0.00: 천단위 콤마/ 소수둘째자리 표시

 

천 단위마다 콤마 표시가 생깁니다.

천 단위로 콤마 들어가는 것 빼고는

앞 서본 0과 0.00 형식과 같은

특징을 갖습니다.

 

#은 자릿수를 의미합니다.

앞서 살펴본 0도 자릿수입니다.

0과 #의 차이점은 알아두셔야 합니다.

 

둘의 차이점은

0은 의미없는 끝자리0을 생략하지 않지만

#은 의미없는 끝자리0을 생략합니다.

 

예를 들어 숫자 0을 입력하면

서식코드 0은 0이 표시되지만

서식코드 #은 0이 표시되지 않습니다.

 

아래처럼 셀 형식에

서식코드 #을 입력하여 형식을 만들었습니다.

0을 입력하면 0이 표시 되지 않는 것을

확인할 수 있습니다.

만약 천단위 구분되면서 0일 때는 표현이 되지

않는 서식을 사용하고 싶을 때는

서식코드 #,###을 직접 입력하면 됩니다.

 

그러나

#,### 보다는

천단위 구분도 되고 0까지 정확히 표현되는

#,##0 형식이 많이 사용됩니다.

 

_-* #,##0_-;-* #,##0_-;_-* "-"_-;_-@_-

 

 

위의 형식은 복잡해 보입니다.

그 이유는 [사용자지정] 서식의

표시형식 4개 구역에 

모두 코드를 입력했기

때문입니다.

 

이 서식코드들의

의미를 이해해야 필요할 때 쓰거나

수정해서 쓸 수 있습니다.

 

[사용자 지정]의 표시형식 구조

[사용자 지정]의 표시형식은

4개의 구역으로 이루어집니다.

각 구역을 이루고 있는 서식코드들은

입력된 값의 조건에 따라 발동됩니다.

 

앞서 본 형식들은 세미콜론으로 구역을

나누지 않고 1개의 서식코드만 썼기

때문에 4개의 구역에

같은 서식코드가 적용됩니다.

그러나 아래의 서식코드는

4개의 구역에

모두 서식코드가 

입력되어 있습니다.

 

위의 형식은 세미콜론으로 

4개 구역으로 구분되었습니다.

 

 사용된 코드 뜻은 다음과 같습니다.

코드	뜻	형식 입력	값 입력	표현 결과
_	언더바 다음 문자 크기만큼 띄웁니다 _-를 입력하면 -만큼 띄우게 되는 겁니다.	#,##0_-	1000	1,000
*	* 다음 문자 1개를 셀너비만큼   끝까지 반복합니다.	*@#,##0	1000	@@@@@1,000
#,##0	천단위를 표시합니다.	#,##0	1000	1,000
@	셀이 텍스트 서식이 되며 좌측으로 정렬됩니다. @앞에 텍스트는 그대로 표시합니다.	사람@	1000	서울1000

 

① 양수 구역:   _-* #,##0_-

임의의 양수를 입력했을 시

앞에는 -만큼 띄우고

뒤에는 -만큼 띄워지게 됩니다.

*은 바로 뒤에 공백이기 때문에

표시되는 값이 없습니다.

 

② 음수 구역:   -* #,##0_-

양수 구역과 크게 다르지 않습니다.

다만 맨 앞에 -(마이너스) 기호가 있습니다.

 화면에 그대로 표시되야하는 문자를

 서식에 넣어야 할때는

“ ”안에 입력해야 합니다.

예를들어 뒤에 원이 표시되길 원한다면

#,##0"원"

이렇게 표시해야 합니다.

 

그러나 일부는 “ ”가 없어도 화면에

그대로 표시되는 경우가 있습니다.

사용자지정 서식에 “ " 없이 입력하여도 화면에 그대로 표시되는 기호	구분
+, -	더하기, 빼기
( ) { } < >	괄호
. ~ : & ~	문장부호
스페이스바	공백
▲▼★ 등	특수문자

 

상기표에 따라서

맨앞에 있는 -(마이너스)는 그대로 표시되며

뒤에는 -만큼 띄워지게 됩니다.

여기서는 *이 역할을 합니다.

*(공백)은 공백이 끝까지 

반복 된다는 뜻입니다.

그래서 -9999를 입력하면

-(마이너스)와 숫자 사이에

공백이 생겨서 표시됩니다.

엑셀 사용하시다가

-(마이너스) 표시가 숫자와 떨어져 있어서

보기 불편하셨다면

이 서식코드를 수정하여 사용하시면 됩니다.

-* #,##0_-

여기서 *을 삭제해 주시면 됩니다.

별을 삭제하니 -가 숫자에 가까이 있습니다.

③ 0의 구역:   _-* "-"_-;

[사용자지정] 서식에서는 “ ”안에 문자를

넣으면 그대로 화면에 표시됩니다.

그래서 0을 입력할 시

“ ”안에 입력된대로 -가 표시됩니다.

 

④ 텍스트 구역: _-@_-

@는 텍스트서식으로

바꾸는 코드입니다.

텍스트 서식이 적용됩니다.

그리고 앞뒤로 공백이 -만큼 

생성됩니다.

 

[사용자지정] 서식의 내용은

이 정도만 알고 있어도

충분히 활용할 수 있습니다.

 

 숫자 증감 표시 되는 셀 서식의 설정

사용자지정 서식에서 조건을

입력하여 주식처럼 증감표시가 되도록

만들어 보겠습니다.

 

범위선택

 

서식이 적용될 곳은 증감이 표시되는 곳입니다.

범위를 선택 후 Ctrl+1을 입력합니다.

사용자지정 표시형식 구조에

적용될

서식코드를 만들면 됩니다.

<양수; 음수; 0; 텍스트>

순으로 서식코드를

만들면 됩니다.

 

양수 서식코드 만들기

 

양수일 때는 ▲이 숫자 앞에 붙습니다.

텍스트에 색을 입힐 때는 [ ]안에 한글로

색을 써넣습니다.

검정, 흰색, 노랑, 빨강,

파랑, 자홍, 녹청, 녹색

총 8가지를 색으로 쓸 수 있습니다.

 “ ” 없이도 화면에 표시되는 기호로

▲도 있습니다. 그래서 그냥 써넣습니다.

그리고 천단위 표시를 해줍니다.

 

양수 서식코드: [빨강]▲#,##0;

 

*ㅁ 누르고 한자키를 누르면

특수문자 ▲ 가 나옵니다.

**만약 증감율로 계산되었을 경우

#.##0대신 0.0%를 입력합니다.

 

음수 서식코드 만들기

 

음수일 때는 ▼가 앞에 붙습니다.

빨강대신 파랑

▲대신 ▼이 들어갑니다.

나머지는 양수일 때와 똑같습니다

 

음수 서식코드: [파랑]▼#,##0;

 

0 서식코드 만들기

 

증감이 없을 때는

-(마이너스) 기호만 나오도록 합니다.

-를 입력합니다.

 

0 서식코드: -

 

텍스트 서식코드 만들기

 

텍스트는 사용할 일이 없으므로 비워둡니다.

 

최종 서식코드

 

앞에서 만든 서식코드를 합쳐서

사용자 지정 형식에 입력합니다.

 

입력할 서식코드

[빨강]▲#,##0;[파랑]▼#,##0;-

그리고 확인을 누르면

 

 사용자 지정에 조건 입력하기

사용자 지정의 표시형식의 구조는

조건의 형태입니다.

 

양수일 때: 서식코드1로 표시됨

음수일 때: 서식코드2로 표시됨

0일 때: 서식코드3 표시됨

텍스트 때: 서식코드4 표시됨

 

각각 다른  4개의 조건에서

표시되는 서식코드가 결정됩니다.

 

그러나 이거 말고도 특정 조건을

정해서 해당 조건을 충족 시에

서식코드가 적용되게 할 수도 있습니다.

 

조건은 2개 까지 지정할 수 있습니다.

 

조건1: 서식코드1

조건2: 서식코드2

그 외: 서식코드3

 

조건은 [ ] 대괄호 안에 넣으면 됩니다.

위의 이미지에서

10월 성적 중

90점 이상은 빨강으로 강조하고

50점 이하는 파랑으로 강조하겠습니다.

 

범위 선택

 

10월 성적을 범위로 선택합니다.

 

90점 이상 서식코드 만들기

 

색이 제일 앞에 나옵니다.

그리고 조건을 대괄호 안에 입력합니다.

90점 이상이므로 부등호는 >=입니다.

 

서식코드: [빨강][>=90]#,##0;

 

조건이 만족되면

[빨강] #,##0

두가지가 적용 되겠죠?

 

50점이하 서식코드 만들기

50점이하 이므로 부등호는 <=입니다.

 

서식코드: [파랑][<=50]#,##0;

 

그 외 서식코드 만들기

 

100이상의 단위가 안나오므로

천단위를 구분할 필요가

없습니다.

0을 입력합니다.

 

서식코드: 0

 

최종 서식코드

 

합쳐진 아래의 서식코드를

사용자 지정에 입력 후 확인을 누릅니다.

 

[빨강][>=90]#,##0;[파랑][<=50]#,##0;0

 

 

아래와 같이 최종적으로 반영이 됩니다.