엑셀 3강] 사용자 지정 서식

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개인 경우 유효하지 않는 자릿수는 표시되지 않습니다. 

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

 

 

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

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

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

 

 

 

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

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

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

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

 

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

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

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