3강에서는 다양한 데이터 표시형식을 지정하는 방법에 대해 소개 하겠습니다.

엑셀에서는 기본적으로 데이터를 입력할 때 화면에 표시되는 내용을 셀 서식의 표시형식을 이용해 변경할 수 있습니다. 하지만, 원하는 표시형식이 없을 경우 사용자가 직접 표시형식을 지정할 수 있는데, 엑셀에서 제공하는 서식 코드를 조합해서 사용해야 합니다.

연습 파일 다운로드 ↓

사용자지정서식.xlsx


입금액에 1000 단위 구분 기호(,)를 표시할 때 쉽게 마우스 오른쪽 버튼을 눌러 '쉼표 스타일'을 적용하면 됩니다.

하지만 사용자가 직접 사용되는 서식 코드를 알아두면 데이터에 다양한 표시 형식을 적용할 수 있습니다. 


숫자에 1000 단위 구분 표시를 하는 표시 형식은 #,##0 라는 서식 코드를 사용합니다. 

여기서 사용된 0#은 숫자를 표시하는 서식 코드입니다.

서식 코드 0은 유효하지 않는 0까지 표시하고 #은 유효하지 않는 0을 표시하지 않습니다.

그럼 숫자에 표시 형식을 지정해서 결과를 확인해 보겠습니다. 

[C2:C5]셀 범위를 선택하고 단축키 Ctrl + 1을 눌러 [셀 서식] 대화상자를 실행하고 [표시 형식]탭을 눌러 [사용자 지정] 범주를 선택합니다. #,##0 이라고 입력하고 확인 버튼을 누릅니다. 

결과를 확인해 보겠습니다. 

1. 23567인 경우 23,567로 1000 단위 구분 기호 표시가 생겼습니다

2. 12.8의 경우에는 십 단위의 값이기 때문에 1000 단위 구분 기호를 적용한 의미가 없죠? 하지만 정수처리되어 소수 자리는 표시 되지 않고 반올림되어 13으로 표시되었습니다. 

3. 0.246도 정수로 처리되어 소수 이하자리는 표시되지 않고 0만 표시되었습니다. 

4. 5478612.2의 경우에도 1000 단위 구분 기호가 표시되고 소수 자리는 표시 되지 않습니다. 


[D2:D5]셀 범위를 선택하고 단축키 Ctrl + 1을 눌러 [셀 서식] 대화상자를 실행하고 [표시 형식]탭을 눌러 [사용자 지정] 범주를 선택합니다. #,### 이라고 입력하고 확인 버튼을 누릅니다. 

0을 제외한 나머지 값은 #,##0#,###의 차이가 없습니다

하지만 0.246 의 경우 아무것도 나타나지 않습니다

#,####0공백으로 표시합니다.

엄연히 소수 값이 있는데도 0대신 #으로 서식코드를 사용하면 값이 없는 걸로 인식이 됩니다

그래서 숫자 값의 범위에 천 단위 구분 표시를 하는 경우에는 #,###대신 #,##0 서식 코드를 사용해야 합니다.


이번에는 달성률을 최대 소수 둘째자리까지 백분율로 나타내려고 합니다.

그리고 소수 자릿수는 그림과 같이 한 자리, 두 자리, 세 자리까지 있습니다.

이때 서식코드 0.00“%” 라고 적용하면 52.10%처럼 소수 자리수를 0으로 맞추어 표시합니다

하지만 0.##“%” 라고 적용하면 52.1% 같이 유효하지 않는 자리의 값은 공백으로 표시합니다

18.832는 두 경우 모두 18.83% 으로 표시됩니다.

참고로 백분율을 표시하는 %“%” 큰따옴표로 묶어 표시합니다

큰따옴표를 묶어 표시하지 않고 0.##% 라고 적용하면 달성률 자체가 백분율 값인데 그 값을 다시 백분율 처리하는 결과가 나오게 됩니다.


수량에 천 단위에 콤마를 표시하고 라고 단위 명을 붙여 보겠습니다

수량 범위에 블록을 지정한 후 단축키 Ctrl + 1을 눌러 셀 서식 상자를 실행합니다.

[표시 형식]-[사용자 정의]를 선택하고 #,##0“라고 입력합니다.


단가와 수량을 곱해서 금액을 구하고 수식을 채우기해서 나머지 품명의 금액도 구합니다. 

금액 범위를 선택한 다음 셀 서식 대화상자를 실행하고 [표시 형식] 탭에서 [사용자 지정] 범주를 선택합니다. 

그런 다음 형식 입력창에 #,##0, 라고 입력하고 확인 버튼을 클릭합니다. 

#,##0,처럼 끝에 ,(쉼표)를 붙이면 천 단위 값을 1,000으로 나누어 천 단위를 생략해서 표시하는 서식 코드 입니다.

단위가 높은 값을 천 단위씩 생략해서 표시할 때 사용합니다. #,##0,, 콤마를 두 번 붙이면 백만 단위 생략이 됩니다.



유효하지 않는 0자리에 공백을 추가하는 코드입니다

주로 소수점이 포함된 숫자를 소수점 기준으로 정렬할 때 사용합니다.

소수 데이터 범위에 블록을 지정하고 Ctrl + 1를 눌러 셀 서식 대화상자를 실행합니다. 

사용자 지정을 선택하고 형식에 ???.???으로 입력하고 확인을 누릅니다.


최대 소수자리 3자리까지 표시하고 소수 이하 자리수가 2개 또는 1개인 경우 유효하지 않는 자릿수는 표시되지 않습니다

그리고 소수점을 기준으로 데이터를 정렬할 수 있습니다.


 

문자 데이터 앞 또는 뒤에 특정 문자를 표시하려고 합니다

셀 또는 범위에 입력받은 문자를 표시하는 서식코드는 @입니다

거래처 범위에 부산-” 라는 문자를 표시하려고 합니다. 부산-”@ 라고 형식을 지정하면 됩니다.


 

금액 범위의 빈 공간에 “*” 문자를 반복해서 채우려고 합니다

이때 사용자 지정 서식에 * 문자를 입력하고 그 뒤에 입력한 문자가 반복 채워집니다

** 입력하면 * 뒤에 “*” 문자가 셀의 빈 공간에 반복해서 채워집니다

예를 들어 *#라고 입력하면 “#”표시가 반복해서 채워집니다.

 

위에서 소개한 방법외에도 아주 다양한 표시형식들이 있습니다.

앞으로 차차 소개를 하겠습니다.

짤막한 강좌 한정희 강사였습니다.

 


  1. ^^ 2018.02.18 23:08

    "4. 소수점 기준으로 정렬"에서 오타가 하나 있는것 같아요.
    Ctrl+A가 아니라 Ctrl+1이 아닐까요??

  2. WE 2019.01.28 18:31

    선생님 질문있습니다!
    중간에??.??? 이런식으로 사용하셨는데
    0은 숫자를 나타내고
    #은 숫자인데 0은 공백으로표핸호고
    @는 문자를 나타내는데

    ???표는 문자 숫자중에 어디에 해당하는 수식인가요??
    #대신 쓰면 되는 건가요??

    • WE 2019.01.28 18:39

      그리고 *다음에 임의의 공백을 채우는것으로 *제외한 글자를 넣었더니 안된다고 하는데 이런 것들은 안되는건가보죠 ㅜㅜ

    • 답변이 늦었습니다. 평일에 많이 바빠 답변할 시간이 없었네요. ㅋㅋ
      ??.??? 표시는 소숫점을 기준으로 정렬할 때 숫자를 표시 형식으로 사용하면 됩니다.

    • 그리고 * 다음에 어떠한 문자도 다 가능합니다.

  3. jinia 2019.02.01 18:14


    한가지 질문 좀 드리려구요.

    #.##0표시 형식을 적용한 경우 내용 중 큰따옴표를 묶어 표시하지 않고 0.##% 라고 적용하면 달성률 을 다시 백분율 처리하는 결과가 나온다고 되어있는데요, 어떤 의미인지 잘 모르겠습니다…
    일단 따옴표를 붙여야 원하는 결과가 나온다는건 알겠는데요,
    따옴표가 없는 경우에는 어떻게 계산되는건가요?

    • 안녕하세요. 답변이 좀 늦었습니다.
      0.##"%"와 0.##%의 차이를 다시 설명하겠습니다.
      "%" 를 큰따옴표로 묶어서 표시하면 단위 %를 단순히 문자로만 표시합니다. 예를 들어 1.50에 0.##"%"라고 적용하면 1.5%가 되는거죠.

      하지만 0.##%라고 표시형식을 적용하면 1.50을 백분율로 변환해서 150%가 되어버리는 거죠.

      답변이 도움이 되었길 바랍니다.

  4. 엑셀의 달인 2019.03.11 10:08

    실무자로 엑셀을 나름 쓰고 있지만, 정식공부한 적은 없어 간혹 불편한 적이 많았는데, 정말 도움이 많이 되고 좋습니다.
    하루 한강좌씩 선생님의 강의를 천천히 들으며 제대로 공부를 할 수 있을 거 같아 벌써 행복하네요.
    업무시간 단축의 꿈을 이룰 수 있을 것 같아요~
    감사합니다~

  5. 엑셀의 달인 2019.03.11 10:13

    반올림, 정수표시를 함수를 쓰지 않고 #,##0로 할 수 있다니!
    너무 감사합니다~~
    역시 사람은 배워야 하나 봅니다~~

  6. 부산 해운대구 양운고 졸업생 2019.04.09 14:03

    어제부터 선생님 강의 보면서 엑셀 배우고 있는 취준생입니다.

    강의보다 거래처 이름들 보니 어? 친숙한 동과 구 이름들이ㅋㅋㅋ
    부산 못 간지 몇 년 됐는데 빨리 취직해서 꼭 놀러가고 싶네요 ㅠㅠ

    엑셀 강의 감사합니다 선생님!!

+ Recent posts