엑셀 배우기(23) - 표시 형식(6) -사용자 지정(custom number format)(2)

표시형식은 화면 또는 인쇄시 보이는 형식만 지정하는 것으로 실제 계산은 수식 입력줄의 숫자를 기준으로 하는 점 명심해야 합니다.

excel_learning23.xlsx

0.01MB

③ ,(쉼표, 콤마)로 천,백만단위로 반올림 효과 내기

㉮ 천단위로 반올림 표시하기
#,##0로 1000 단위 마다 ,를 입력해서 표시하는데,

B5셀을 선택한 다음 Ctrl + 1키를 누르고 범주에서 사용자 지정을 클릭하고, 형식 입력 칸에서 #,##0의 뒷 부분 ##0을 지워서 #,로 만들면 보기에 1이라고 표시됩니다. 확인 버튼을 누르면

B5셀에 1이라고 표시되는데, 이것은 1,234 / 1,000 = 1.234이므로 정수 부분만 표시해서 1이 된 것입니다.

B5셀의 숫자를 1534로 수정하면 1.5가 반올림돼서 2로 표시됩니다.

B6셀을 선택하고 사용자 지정 표시형식 목록에서 맨 아래에 추가된 #,를 지정하고 확인버튼을 누르면

1235라고 천 단위 , 표시가 안보입니다.

따라서, #,###,라고 수정해야 하는데 0단위도 표시되도록 #,##0,로 수정하고 확인 버튼을 누르면

천단위로 반올림 되면서 4자리 수 이상인 경우 콤마 표시도 잘 들어갑니다.

㉯ 백만단위로 반올림 표시하기
백만단위로 반올림 표시하면서 천단위마다 콤마 표시를 넣으려면
#,##0,, 라고 ,를 두번 넣으면 됩니다.
B6셀의 표시형식을 사용자 지정에서 기존 #,##0,의 맨 뒤에 ,를 추가해서 #,##0,, 라고 수정하고 확인버튼을 누르면

1이라고 표시되는데, 1은 1234567을 백만으로 나눈 값 1.234567에서 소숫점이하 첫째자리에서 반올림하니 1이 된 것입니다.

B6셀의 숫자 뒤에 890을 추가한 다음 엔터 키를 누르면

1,235라고 표시됩니다. 1,234.56789이므로 반올림돼서 1,235가 된 것입니다.

그러나 이렇게 반올림하게 되더라도 백만단위 또는 천단위 미만의 숫자는 여전히 살아 있으므로 의도치 않은 결과가 나올 수 있으므로 round 함수를 이용해 천단위 또는 백만단위 미만의 숫자는 정리하는 것이 바람직합니다.

④ 통화 기호와 숫자 표시 사이에 공백넣기
회계 표시형식을 선택하게 되면 통화 기호와 숫자 사이에 공백이 들어가는 것을 봤으므로,

그것을 어떻게 설정하는지만 알아보겠습니다.

㉮ 회계 표시형식에 해당하는 사용자 정의 표시형식 코드의 의미
Ctrl + 1키를 누르고 사용자 지정을 클릭하게 되면 자동으로 _-₩* #,##0_-;-₩* #,##0_-;_-₩* "-"_-;_-@_- 표시 형식이 선택됩니다.

_-₩* #,##0_-;-₩* #,##0_-;_-₩* "-"_-;_-@_- 이것을 풀어보면
첫번째 양수 코드 섹센에 있는 _-₩* #,##0_- 은
앞 뒤에 _- 즉 공백 한칸을 추가하고,
통화 기호로 ￦을 표시하고,
* (공백 한칸)은 통화 기호와 숫자 사이를 공백으로 채우라는 의미입니다. _-이 공백 한 칸인데, *하고 공백은 공백을 빈 공간만큼 반복적으로 채우는 것이 다른 점입니다.
그리고 #,##0 다시 말해 천 단위마다 ,를 표시하고 0이더라도 0을 표시합니다.

따라서, 여기서 중요한 것은 *표시하고 공백 한 칸입니다.


두번째 음수 코드 섹션에 있는 -₩* #,##0_- 은
앞에 공백 한 칸 추가(_-) 대신 -(음수) 표시가 있다는 점만 다릅니다.

세번째 0 코드 센션에 있는 _-₩* "-"_- 은
앞뒤에 한 칸 공백을 추가하는 것과 통화 기호 ₩는 같고, 영을 0이 아니라 "-"이기 때문에 -(하이픈)을 문자로 표시하는 것입니다.

네번째 문자 코드 센션에 있는 _-@_- 은
앞뒤 한 칸 공백 추가는 동일하고 @는 입력된 문자열을 표시하는 것입니다.

㉯ 회계 표시형식에 해당하는 사용자 지정형식에 0과 문자열 입력하기
B7셀에 0을 입력하면 ₩와 -으로 표시되는데, 중간에 공백이 채워졌습니다.

B7셀에 문자는이라고 입력하면 한 칸 떨어져서 문자는이 표시됩니다.

수식 입력줄을 마우스로 클릭해서 커서를 넣었다가 마우스로 C7셀 등 다른 셀을 클릭하면 왼쪽에서 한 칸 떨어지는 동작을 볼 수 있습니다.

㉰ 입력된 문자열 보이지 않게 하기
네번째 문자 코드섹센에서 @를 제거하고 _-_-로 변경하면 글자를 입력하더라도 수식 입력줄에만 보일 뿐 셀에는 표시되지 않습니다.

⑤ 숫자의 크기에 따라 폰트 색상 바꾸기
숫자의 크기에 따라 폰트의 색상을 바꾸려면 마찬가지로 ;(세미콜론)으로 구분해서 구간별 색을 지정하면 됩니다.

기존에 있는 #,##0;[빨강]-#,##0를 선택한 다음

[파랑][>100]#,##0;[빨강][<=100]#,##0 로 변경하고 확인버튼을 입력합니다.

100보다 크다면 폰트 색을 파랑, 100이하라면 빨강으로 지정하는 것입니다.

그런데, 마이크로소프트 공식 도움말 사이트의 색 표시를 보면 파랑인데 파란색, 노랑인데 노랑색으로 잘못되어 있습니다.

예를 들어 파란색으로 지정하고 확인버튼을 누르면 아래와 같이 에러가 나므로 파랑으로 입력해야 합니다.

에러 메시지 창을 확인 버튼을 눌러 닫고 파란색을 파랑으로 수정하고 아래 확인버튼을 누릅니다.

그리고, B8셀에 200이라고 입력하면

50으로 수정하면 빨간색으로 표시됩니다.

파란색으로 돌리기 위해 Ctrl + Z키 또는 홈 탭 실행 취소 그룹의 위 취소 명령을 누릅니다.

이번에는 구간을 3개로 지정하기 위해
B9셀을 클릭한 다음
[녹색][>1000]#,##0;[파랑][>100]#,##0;[빨강][<=100]#,##0 라고 앞에 [녹색][>1000]#,##0;를 추가하면
아래와 같이 에러 메시지가 표시됩니다.

그래서 세번째 빨강 다음의 조건 [<=100]을 지우고 확인 버튼을 누르니 에러없이 잘 됩니다.

사실 세번째 조건이 없더라도 앞의 두 조건에 맞지 않는 것이니까 조건을 줄 필요가 없긴 합니다.

B9셀에 2000도 넣어보고, 200도 넣어보고 50도 넣어보기 바랍니다. 2000을 넣어보니 녹색으로 잘 표시됩니다.

⑥ 숫자의 앞을 0으로 채우기
0의 길이만큼 부족한 자릿수를 0으로 채우기 때문에 사용자 정의 형식에 사용할 숫자의 길이를 먼저 정해야 합니다.

B10셀에 일반 형식으로 12345를 입력한 다음 Ctrl + 1키를 누르고, 사용자 지정을 클릭하고 비슷한 #,##0을 클릭한 다음

숫자를 7자리로 하고 싶다면 0,000,000이라고 지정하고 확인버튼을 누릅니다.

그러면 7자리에서 부족한 2자리를 0으로 채워서 0,012,345가 됩니다.

만약 ,를 넣지 않고 표시형식을 0000000라고 0을 7번 채우면 0012345라고 표시됩니다.

그런데 * 기호가 반복기능이라고 해서 *0이라고 설정해도 빈 공간이 0으로 채워지지 않고,

*- 이라고 하이픈을 반복하게 하려고 해도 빈 공간에 공백만 들어갈 뿐입니다.

excel_learning23(completed).xlsx

0.01MB

내용이 많아져서 다음 편에서 계속 다루겠습니다.