엑셀 기초 - 셀 서식 사용자지정 서식(숫자)

작장에서 엑셀을 써야 할 일이 많습니다. 간단한 연락처 리스트를 만드는 것부터, 고객사와 견적을 주고 받을 때, 그날의 매출과 순익을 정리하고, 광고 효과를 파악할 때. 쓸일은 많은데 자주 쓰는 기능 이외에는 잘 몰라 다시 공부합니다. 기왕이면 후배에게도 알려줄 수 있도록 꼼꼼히 정리하려고 합니다.

엑셀 기초 - 셀 서식 사용자 지정 서식(숫자)

(이전 포스팅 참고)

https://irisar.tistory.com/129?category=933309

엑셀 기초 - 셀 서식

셀에는 숫자, 논리 값, 날짜, 시간, 텍스트 등의 데이터를 입력할 수 있습니다. 이렇게 셀에 입력한 사용자 지정 서식은 이렇게 셀에 들어간 각각의 데이터를 내가 원하는 형식에 맞게 보여지게 하는 기능인데요. 숫자라면 소수로 보일지, 3자리 마다 콤마를 쓸지, 음수 일 때 어떻게 보이도록 할지를 결정할 수 있습니다.

 

사용자지정 서식은 셀 서식 창에 직접 타이핑 해 내가 원하는 서식을 만들 수 있는데요. 셀 서식 - 표시 형식 - 사용자 지정을 선택하고, 아래 붉은색 박스에 직접 입력하면 됩니다.

(1) 사용자 지정 숫자 서식 구문

사용자 지정 숫자 서식은 셀 안에 들어 있는 숫자를 어떻게 표현하고 싶은지에 따라 두 가지 방식으로 작성할 수 있습니다.

 

A. 셀의 데이터가 양수인지, 음수인지 또는 0 인지에 따라 표현하는 방식을 달리하고 싶을 때

 

 

 

 

우선, 0과 #으로 숫자 표현하기 

숫자를 표현하는 서식을 만드는 방법은 0과 #과를 조합해 만들 수 있는데요. 보통은 0과 # 모두 숫자로 인식되지만, 0을 사용한 자리에는 무조건 0이 들어가고, #을 사용하면 0이 유효하지 않은 경우 0을 무시합니다.

예를 들어 서식을 [0000]으로 지정해 놓은 셀에 0을 입력하면 무조건 0000이 표시되고, 1이라는 값을 입력하면 앞자리에 0 세 개가 붙어 0001로 표시됩니다. 0을 여러 개 입력해도 설정해 놓은 4개까지만 표시됩니다. 반대로 #는 0을 무시하는데요. 아예 표시되지 않습니다. 그래서 [####]로 서식이 지정된 셀에는 0을 넣어도, 00을 넣어도 00000을 넣어도 값이 표시되지 않습니다.

 

구분	입력값	12345	0	1	123	-11	-100	-1234
서식	0000	12345	0000	0001	0123	-0011	-0100	-1234
	####	12345		1	123	-11	-100	-1234
	###0	12345	0	1	123	-11	-100	-1234

서식을 0000으로 지정해 놓으면 0을 넣어도 0000, 1을 넣으면 0001, 50을 넣으면 0050과 같이 한 자리, 두 자리, 세 자리 숫자 앞에 무조건 0이 하나 더 붙게 됩니다. 숫자 0만 넣었을 때 0을 제대로 표현하고 싶으면, ###0과 같이 써야 합니다. 

 

 

쉼표(,)는 천 단위를 구분하기 위해 사용하는데요. 쉼표를 붙이는 것과 그렇지 않은 것의 차이를 아래 표로 확인해 보세요. 

구분	입력값	1234	0	10000	1000000	10	1	-500	0000
서식	0000	1234	0	10000	1000000	0010	0001	-0500	0000
	0,000	1,234	0	10,000	1,000,000	0,010	0,001	-0,500	0,000
	####	1234		10000	1000000	10	1	-500
	#,###	1,234		10,000	1,000,000	10	1	-500
	#,##0	1,234	0	10,000	1,000,000	10	1	-500	0
	#,##0,	1	0	10	1,000	0	0	0	0
	#,##0,,	0	0	0	1	0	0	0	0
	#,##0,,,	0	0	0	0	0	0	0	0

특히, 끝부분에 쉼표를 여러 개 붙이면 쉼표 하나 당 천 단위를 줄일 수 있습니다. ( , 하나가 더 붙을 때마다 000 세 개가 없어집니다) 쉼표를 어디에 넣느냐에 따라 5,000,000 / 500,0000 / 50,00000으로 보이게 할 수도 있습니다.  

 

숫자가 양수 일 때, 음수 일 때, 0일 때 다르게 표현하고 싶다면, 위에서 설명드렸던 것처럼 콜론(;)을 구분해 [양수];[음수];[0];[텍스트] 일 때 표시되는 모습을 달리 보여 줄 수 있습니다. 텍스트에 특별한 효과를 주지 않는다면 [양수];[음수];[0] 까지만 지정해도 됩니다. 

 

숫자가 0일 때 0 대신 다른 값을 표시하고 싶다면, "문자"를 넣어도 좋습니다. 또는 0 값을 

구분	입력값	10000	10	0	1234	-567	테스트
서식	###0;-###0;####	10000	10		1234	-567	테스트
	#,##0;-#,##0;0	10,000	10	0	1,234	-567	테스트
	#,##0;-#,##0;-	10,000	10	-	1,234	-567	테스트
	#,##0;-#,##0;없음	10,000	10	없음	1,234	-567	테스트
	[파랑]#,##0;[빨강]-#,##0;0	10,000	10	0	1,234	-567	테스트
	#,##0;-#,##0;0;[녹색]	10,000	10	0	1,234	-567	테스트

 

 

B. 셀의 데이터의 조건에 따라 - ex. 값이 10000보다 큰지, 작은지에 따라 다르게 표시하고 싶을 때 

 

 

셀에 입력한 값이 어떤 특정 기준을 넘었을 때, 달리 보이게 하고 싶다면 조건에 따라 다르게 표현하도록 서식을 지정할 수도 있습니다. 예를 들면 시험 성적을 입력한 표를 만드는데 80점 이상은 파란색, 40점 이하는 빨간색, 그 외는 자홍색으로 표시되도록 하고 싶을 때 이용하면 좋은데요.

[조건 1] 서식 1; [조건 2] 서식 2; 서식 3과 같이 작성하면 됩니다. 위 표와 같이 만들고 싶다면, 아래와 같이 작성하면 됩니다. 

[>=80][파랑]#,##0;[<=40][빨강]#,##0;[자홍]

[>=80] :: 조건 1 - 숫자가 80 이상이면,

[파랑]#,##0 :: 서식 1 - 파란색으로 표시하고, 숫자는 천 단위로 쉼표를 넣어주고, 0 값이 제대로 보이게 해라

 

[<=40] :: 조건 2 - 숫자가 40 이하이면,

[빨강]#,##0 :: 서식 2- 빨간색으로 표시하고, 숫자는 천 단위로 쉼표를 넣우족, 0 값이 제대로 보이게 해라

 

[자홍] :: 그 외 - 조건 1, 조건 2에 해당하지 않으면 자홍색으로 표시해라

 

(2) 숫자 서식 응용

0과 # 그리고 기타 문자를 이용해 숫자를 다양한 형태로 보이게 할 수 있습니다. 가장 간단한 예로 엑셀에서 전화번호를 입력한다고 하면, 셀에 010-1234-5678 이런 식으로 중간에 '-'까지 모두 타이핑해야 해서 귀찮아지는데요. 사용자 서식을 아래와 같이 설정해 놓으면 숫자만 입력해도 전화번호처럼 보이게 할 수 있습니다. 

 

 

특정 범위에 해당하는 숫자를 문자로 바뀌게도 할 수 있습니다. 위에서 보여드린 표처럼 학생들의 성적을 정리한다고 할 때, 점수가 아니라 등급으로 표현해 볼 수도 있는데요. 조건을 활용해 100~80까지는 A 등급, 79~41은 B등급, 40~0은 C등급으로 표시되게 만들어 볼 수 있습니다.

[>=80]"A";[<=40]"C";"B"

[>=80] :: 조건 1 - 점수가 80 이상이면,

"A" :: 서식 1 - 문자 A로 표시한다.

 

[<=40] :: 조건 2 - 점수가 40 이하이면,

"C" :: 서식 2 - 문자 B로 표시한다.

 

"B" :: 그 외 - 조건 1, 조건 2에 해당하지 않으면, B로 표시한다.