Excel 자료 Excel 초급과정 (사용자 정의 서식 만들기)

[Laurentius O Toole]

15. 사용자 정의 서식 만들기

지금부터 할 작업은 왠만한 엑셀 고수들도 잘 모르는 기능입니다.
여러분들이 직접 엑셀에서 제공하지 않는 서식을 만드는 것입니다.

그냥 아무렇게나 만드는 것이 아니라 정해져 있는 형식을 따라야하겠죠? 그 방법을 배워보겠습니다.

이번에는 사용자가 직접 서식을 만들어볼 차례입니다.

엑셀에서 제공하는 서식은 다양하기는 하지만 제공되지 않는 서식을 사용하고 싶으면 임의의 형식을 직접 만들어 사용할 수

있습니다.

사용자정의 서식은 셀 서식 대화상자의 표시형식에서 "사용자 정의"항목을 선택한 후 만들 수 있습니다.

사용자 정의 서식은 시중의 책에서 자세히 다루지 않기 때문에 여기에서 끝장을 보시기 바랍니다.


아무리 들여봐도 무슨 말인지 모르겠죠? 0이나 #, 그리고 ?는 어디에 어떻게 사용하는지.
먼저 이러한 서식 코드를 이해해야 합니다.

엑셀의 사용자 정의 서식 코드는 크게 텍스트 코드와 날짜/시간 코드, 그리고 문자열/공백에 관한 코드가 있습니다.

다음 표를 참고해서 사용자 정의 서식에 사용되는 코드에 대해서 알아두시기 바랍니다.

1. 엑셀의 사용자 정의 서식에서 사용되는 텍스트 코드

서식 코드	의미
0	사용자 서식 정의에서 숫자 0은 자릿수를 정의합니다. 정수의 경우 0으로 지정한 자릿수보다 큰 수가 입력될 경우 그대로 출력되며, 작을 때는 정수의 앞에 0이 표시됩니다. 소수점이 있는 실수에서 정수 부분은 동일하게 처리되고 입력된 수의 소수 이하 자릿수가 서식보다 크면 반올림되며, 작으면 남는 자릿수만큼 0으로 채워지게 되죠 .셀에는 소수점이 반올림되어 표시되지만 실제 데이터는 입력한 그대로 유지됩니다.
. (마침표)	소수점 왼쪽과 오른쪽에 자릿수를 표시하려면 표시 형식에 소수점(.)을 사용하죠. 소수점 왼쪽에 #만 붙히면 1부터 작은 숫자는 소수점부터 시작됩니다.
, (쉼표)	쉼표(,)는 천단위 구분기호입니다.
#	사용자 정의에서 #는 의미 없는 0을 제거하는 기능입니다. 쉼표나 다른 분리기호와 함께 사용되며, 이 기호만 사용한 서식에 0을 입력하는 경우 셀에는 아무것도 나타나지 않게 됩니다. 천 단위마다 쉼표를 가지고 최소한 하나의 숫자를 나타나게 하려면 #,##0으로 서식을 설정하면 됩니다.
?	?는 숫자자리를 표시하는 기능입니다. ?을 사용하면 숫자가 서식에 지정한 것 보다 더 적은 자릿수를 가질때 0이 추가로 표시됩니다. 그리고 소수점의 위치를 맞추기 위해 소수점 양쪽의 의미없는 0대신에 공백을 추가하게 됩니다. 이 기호는 자릿수가 다양한 분수에도 사용할 수 있죠. 3.5를 3 1/2로 나타내려면 서식을 # ?/?로 설정하면 되겠죠?
/	/는 일반적으로 알고 있듯이 분수기호입니다. 숫자자리 표시 기호들과 함께 사용해 분수서식을 갖지 않는 일반 수치를 분수 서식으로 나타냅니다. 예를들어 5.25를 5 1/4로 나타내려면 # ???/???로 서식 설정을 하면 됩니다.
$	$는 통화서식입니다. 화폐단위를 의미하는 문자로서 0이나 #서식의 앞에 붙여 사용합니다. 원화기호(￦)를 대신 사용할 수도 있죠.
;	세미콜론은 섹션구분 서식으로 서식을 연속으로 설정할 경우 사용됩니다. 섹션은 양수, 음수, 0, 문자서식의 네 부분으로 나눌 수 있으며, 별도의 섹션을 지정하지 않을 경우 양수 서식으로 처리합니다.
[색 n]	서식에 색을 설정하는 기능입니다. 검정〕,〔파랑〕,〔녹색〕,〔자주〕등과 같이 색을 직접 입력해도 되고, 만약 글자를 쓰지 않고 색상표를 이용하려면 서식부분에 〔색n〕(여기서 n은 1부터 56까지의 숫자)를 기입하면 됩니다. 색번호는 도구-옵션 메뉴의 '색'을 선택했을 때, 맨 왼쪽 위가 1번이고, 오른쪽 아래가 56번입니다. 예를 들어볼까요? 정수를 파란색으로 나타내려면 서식을 [파랑〕#,###으로 하면 됩니다.

window.google_render_ad();

서식 코드를 이용해서 사용자 정의 서식을 만들 때 ;를 붙여서 섹션을 구분할 수 있다고 했는데 이 섹션 구분에 대해

조금 더 알아보기로 하겠습니다. 사용자 정의 서식에서는 다음과 같이 4개까지의 서식 코드를 지정할 수 있습니다.

양수서식 ; 음수서식 ; 0 ; 문자서식
세미콜론(;)으로 구분된 항목은 양수, 음수, 영, 텍스트의 순서로 정의합니다.

두 항목만 지정하면 첫째 항목은 양수와 영에 대해, 둘째 항목은 음수에 대해 사용됩니다.

한 항목만 정의하면 모든 숫자가 이 서식을 사용합니다. 또한 항목을 생략하는 경우 해당 항목에 대해 마침 세미콜론을

사용할 수 있습니다. 

예를 들어 양수는 파란색, 음수는 빨간색, 0은 하이픈(-), 문자는 노란색으로 나타낼 경우는 다음과 같이 섹션을 구분해서

서식을 정의해주면 됩니다.

〔파랑〕;〔빨강〕; - ; 〔노랑〕

이번에는 날짜와 시간에 관한 서식 코드를 알아볼까요?

2. 날짜와 시간에 관한 서식 코드
날짜와 시간에 관련해서 년, 월, 일을 표시하려면 섹션에 y, m, d의 서식코드를 조합하여 사용합니다.
예를 들어 1997년 11월 15일로 표시하려면 서식을 yyyy년mm월dd일로 지정해 주면 됩니다.

시간의 시, 분, 초를 표시하려면 h, m, s를 조합하여 지정할 수 있습니다.

또한 서식에 AM/PM이 있으면 시간은 12시간제로 표시되구요, 24시간제로 표시하고 싶으면 뒤에 AM/PM을 없애면 됩니다.

24시간 이상의 시간이나 60이상의 분이나 초는 시간 코드 가장 왼쪽에 대괄호를 사용해야 합니다.

예를 들어 1시 10분을 70분으로 표시하려면 서식 코드를 [mm]:ss로 지정하는 겁니다. 

3. 문자열과 공백에 관한 서식 코드
엑셀에서 문자열이나 기호는 큰 따옴표를 사용하거나 앞에 백 슬래시(\)를 사용해 나타냅니다.

$, -, +, \, (), ;. 공백 등의 특수문자를 표시할 때는 큰 따옴표를 사용하지 않아도 되며, !, ^, &, ', ', ~, {, }, =, >, <등의 기호를

입력하면 백슬래시가 자동으로 나타나게 됩니다.

이러한 문자열 서식은 엑셀의 고급사용을 가능하게 하는 것이므로 잘 알아두어야 합니다.

서식 코드	의미
_ (밑줄)	_는 간격을 지정하는 서식입니다. 숫자 서식에서 밑줄 다음에 오는 한 문자의 폭만큼 공백을 표시하는거죠. 예를들어 밑줄뒤에 닫는 괄호를 입력하면 양수가 괄호안의 음수와 함께 바르게 정렬되는 것이죠. 즉, 셀에서 양수 1234와 음수(1234)의 오른쪽 숫자 위치를 맞추려면 서식을 #,##0_);[빨강](#,##0)으로 지정하면 되는것입니다.
@	@는 문자삽입의 기능을 가지는 서식코드입니다. 숫자서식에 문자열 섹션을 포함시키기 위해서 숫자서식에 기호 @를 입력하는 것입니다. 셀에 입력한 문자열은 @기호가 표시된 섹션에 따라 서식이 지정됩니다. 소식에 문자열 섹션이 없으면 입력한 문자는 서식에 영향을 받지 않게 되는 것입니다. 예를들어 김형백이라고 입력을 했을 때 "정보문화사의 김형백입니다."로 표시하고 싶으면 서식을 '정보문화사의 @입니다'와 같이 지정해두면 되는 것입니다.

어느 정도 감은 잡겠는데, 정확하게 이해가 안되죠?
지금부터 실습을 통해서 사용자 정의 서식에 대해서 완벽하게 이해를 하고 넘어가겠습니다.

참고로, 셀 서식 대화상자에 기본적으로 제공된 서식을 편집해도 기존의 서식이 삭제되지는 않습니다. 

이런 서식을 한번 만들어볼까요? 만약 셀의 값이 100000을 넘으면 파란색으로 표시되면서 끝에 "원"자가 붙게 하고,

음수라면 빨간색으로 괄호 안에 수치를 표시하며 역시 "원"자를 붙이고, 0이 입력되면 Zero가 표시되고,

일반 문자가 입력되면 "정보문화사의 ~ 입니다."라고 표시되게 해보겠습니다.

복잡한 것 같지만, 앞에서 살펴본 서식 코드를 잘 이해한다면 전혀 어렵지 않습니다.

우선 섹션으로 구분된 서식을 한번 살펴볼까요?

* 위 식에서 Zero는 "zero"로 해주셔야 정상적으로 작동합니다.
Zero는 문자이기 때문에 식에서 " "로 감싸주는 것입니다.

① 우선 서식을 지정할 범위를 선택한 후 마우스 오른쪽 버튼을 클릭해서 [셀 서식]을 선택합니다.

② 셀 서식 대화상자가 나타나면 [표시형식] 탭을 클릭하고 "범주"에서 "사용자 정의"항목을 선택합니다.


③ "형식" 입력란을 마우스로 클릭합니다. 그 후 [파랑][>100000]#,###_-"원"; [빨강][<0](#,###)"원";"Zero";"정보문화사의"

    @ "입니다."를 입력합니다. 입력이 정확하게 되었으면 [확인]을 클릭합니다.


④ 사용자 정의 서식이 지정된 곳에 다음과 같이 데이터를 입력합니다. 사용자 정의 서식에서 지정한대로 셀에 나타나게 됩니다.


앞서 배운 조건부 서식과 이 기능을 잘 활용하면 효과적인 데이터 입출력을 할 수 있을 것입니다.

복잡하게 함수를 이용하지 않아도 간단히 끝낼 수 있죠? 계속해서 응용하고 활용해 보세요.

다음 표는 서식 코드별로 표시되는 예를 정리한 것이니 참고하면 사용자 정의 서식에 대한 이해에 도움이 될 것입니다.

1> 기본 사용자 정의 서식

서식 코드	표시
0.#	.631을 0.6으로
####.#	1234.59를 1234.6으로
#.0#	12를 12.0으로, 1234.568을 1234.57로
???.???	44.398, 102.65, 2.8의 소수점을 맞춤
# ???/???	5.25를 5 1/4로, 5.3을 5 3/10으로
#.000	8.9를 8.900으로
#,	12000을 12
#,###	12000을 12,000
0.0,,	12200000을 12.2

2> 날짜와 시간의 사용자 정의 서식

서식 코드	표시
Yy	연도를 00-99로
Yyyy	연도를 1900-9999로
Ddd	요일을 Sun-Sat로
Dddd	요일을 Sunday-Saturday로
Mm	월을 01-12로
M	월을 1-12로
Mmmmm	월을 월 이름의 첫 자로
Mmm	월을 Jan-Dec로
Mmmm	월을 January-December로
Dd	일을 01-31로
D	일을 1-31로
Mm	분을 00-59로
M	분을 0-59로
[mm]:ss	분을 63:46과 같이 경과 시간으로
Hh	시간을 00-23으로
H	시간을 0-23으로
[h]:mm	시간을 25.02와 같이 경과 시간으로
h AM/PM	시간을 4 AM으로
h:mm AM/PM	시간을 4:36 PM으로
h:mm:ss A/P	시간을 4:36:03 P로
Ss	초를 00-59로
S	초를 0-59로
[ss]	초를 경과 시간으로
h:mm:ss.00	초를 소수로

사용자 정의 서식을 처음 접하는 분들은 지금 어리둥절할 수도 있습니다.
직접 해보세요. 엑셀의 기능에 감탄할걸요?

남들이 잘 모르는 기능을 업무에 적용할 수 있을 때, 조금씩 우리도 발전하는거 아닐까요?