Excel: 自定义数字格式

文章背景：在Excel中，根据单元格数字显示的需要，可以使用不同的单元格格式（如日期，时间，货币等），有时需要自定义数字格式。下面介绍自定义格式的一些语法规则。

首先需要说明的是，自定义数字格式并不改变数值本身，只改变数值的显示方式（事实上所有类型的单元格格式都只是改变了数值的显示方式）。

1 自定义格式区段

2 自定义格式基础字符

2.1 双引号（" "）和反斜杠（\）

2.2 下划线（"_"）

2.3 "@"

2.4 "*"

2.5 0 (zero)

2.6 #

2.7 ?

2.8 . (period)

2.9 , (comma)

2.10 Specify conditions

1 自定义格式区段

自定义格式代码，最多可以有四个区段，在代码中，用分号来分隔不同的区段，每个区段的代码作用于不同类型的数值，具体表示如下：

<POSITIVE>;<NEGATIVE>;<ZERO>;<TEXT>

示例：

[Blue]#,##0.00_);[Red](#,##0.00);0.00;"sales "@

效果：

实际应用中，四个区段不一定全部使用；基于区段使用个数的不同，不同区段的作用如下表所示：

2 自定义格式基础字符

2.1 双引号（" "）和反斜杠（\）

To display both text and numbers in a cell, enclose the text characters in double quotation marks (" ") or precede a single character with a backslash (\）.

2.2 下划线（"_"）

To create a space that is the width of a character in a number format, include an underscore character (_), followed by the character that you want to use.

For example, when you follow an underscore with a right parenthesis, such as _), positive numbers line up correctly with negative numbers that are enclosed in parentheses.

2.3 "@"

If included, a text section is always the last section in the number format. Include an "at" character (@) in the section where you want to display any text that you type in the cell.

2.4 "*"

To repeat the next character in the format to fill the column width, include an asterisk (*) in the number format.

For example, type 0*- to include enough dashes after a number to fill the cell, or type *0 before any format to include leading zeros.

2.5 0 (zero)

This digit placeholder displays insignificant zeros if a number has fewer digits than there are zeros in the format. For example, if you type 8.9, and you want it to be displayed as 8.90, use the format #.00.

2.6 #

This digit placeholder follows the same rules as the 0 (zero). However, Excel does not display extra zeros when the number that you type has fewer digits on either side of the decimal than there are # symbols in the format. For example, if the custom format is #.##, and you type 8.9 in the cell, the number 8.9 is displayed.

2.7 ?

This digit placeholder follows the same rules as the 0 (zero). However, Excel adds a space for insignificant zeros on either side of the decimal point so that decimal points are aligned in the column. For example, the custom format 0.0? aligns the decimal points for the numbers 8.9 and 88.99 in a column.

2.8 . (period)

This digit placeholder displays the decimal point in a number.

If a number has more digits to the right of the decimal point than there are placeholders in the format, the number rounds to as many decimal places as there are placeholders. If there are more digits to the left of the decimal point than there are placeholders, the extra digits are displayed. If the format contains only number signs (#) to the left of the decimal point, numbers less than 1 begin with a decimal point; for example, .47.

2.9 , (comma)

Displays the thousands separator in a number. Excel separates thousands by commas if the format contains a comma that is enclosed by number signs (#) or by zeros. A comma that follows a digit placeholder scales the number by 1,000. For example, if the format is #.0, , and you type 12,200,000 in the cell, the number 12200.0 is displayed.

2.10 Specify conditions

To specify number formats that will be applied only if a number meets a condition that you specify, enclose the condition in square brackets. The condition consists of a comparison operator and a value.

For example, the following format displays numbers that are less than or equal to 100 in a red font and numbers that are greater than 100 in a blue font.

[Red][<=100];[Blue][>100]

To apply conditional formats to cells (for example, color shading that depends on the value of a cell), on the Home tab, in the Styles group, click Conditional Formatting.

参考资料：

[1] 原来Excel自定义格式可以这样玩（https://zhuanlan.zhihu.com/p/31578032）

[2] Review guidelines for customizing a number format（https://support.microsoft.com/en-us/office/review-guidelines-for-customizing-a-number-format-c0a1d1fa-d3f4-4018-96b7-9c9354dd99f5?ui=en-us&rs=en-us&ad=us）

[3] 花了20小时整理的Excel自定义格式代码大全（https://zhuanlan.zhihu.com/p/334303598）