Excel 设置单元格数字格式让其以输入时的形式显示

当向单元格输入一个数字时，这个数字可能不会以输入时的数值形式出现在工作表中，比如，输入9/10，Excel会显示为9月10日。这是因为Excel把所有的数字和日期都以数字形式保存，默认把9/10这样的分数识别为日期，在屏幕上显示时就会按日期的默认“数字格式”来显示。要改变它，就必须借助单元格数字格式的设置。

Excel提供了大量的数据格式，并将它们分成：常规、数值、货币、会计专用、日期、时间、百分比、分数、科学记数、文本、特殊和自定义等。如果不做设置，输入时将使用默认的“常规”单元格格式。

打开Excel数字格式设置的方法是：

选中需要设置数字格式的单元格或区域，点击右键，选择右键菜单中的“设置单元格格式…”，从而打开包含有“数字”选项卡的“设置单元格格式”对话框，如图 1所示。

图1      设置单元格格式对话框的“数字”选项

1.  设置为约定的数字格式

首先选择要进行设置的单元格或区域，打开“设置单元格格式”对话框，进入“数字”选项卡，如图 1所示。从中选择需要的数字格式。

试一试：

1）   新建一张工作表，在A1:A11中输入如图 2所示的文本，设置为右对齐。

2）   依次设置B1:B11的“数字”格式为：数值(小数位数为2)、货币(小数位数为2，货币符号选“￥”)、会计专用（小数位数为2，货币符号选“￥”，注意“会计专用”格式与“货币”格式的差异）、日期（选择类型为：*2001年3月14日）、时间（选择类型为：13时30分55秒）、百分比（小数位数为2）、分数、科学记数、文本、特殊（类型设为“中文大写数字”）、自定义（类型中输入“000000”）。

3）   然后B1:B11单元格中均填入“1234.56”，各个单元格显示的结果就将如图 2所示。

图2    各种数字格式

*小提示：

1）   如果只对一些数据的格式做一些比较简单的设置，可以直接借助“开始”功能区带中“数字”格式按钮组来实现，如图 3所示。各个按钮的具体含义可以将光标定位在按钮上方小段时间，Excel将自动弹出按钮功能说明。

图3   “数字格式”快捷按钮

2）   会计专用和货币格式的区别是：货币格式是右对齐，而会计专用是两端对齐（即货币符号和数据最后一位都对齐）。

2.  自定义数字格式

在Excel中，可以根据需要自己定义数据格式，当Excel自带的数字格式无法描述实际的数据时，就可以通过自定义格式来设计如何显示数字、文本、日期等数据。如图 2中最后一个数据就是“自定义格式”，定义的格式为“000000”，即邮政编码格式。

创建自定义格式时最多可以指定4种格式，其书写形式是：

正数格式；负数格式；零值格式；文本格式

*注意：

不同的格式部分之间以英文分号分隔（记住一定用英文的“;”），如果要跳过某一部分定义，那么该部分应以分号结束。例如，要创建一个不定义负数格式的自定义格式，其余3部分的书写顺序为：

正数格式；；零值格式；文本格式

*扩展知识点：

在Excel中，如果有需要使用标点符号表示特定含义的，如上方以分号（“;”）表示格式的分隔，那么这些标点符号就必须使用英文标点符号，不能使用中文的标点符号，中文标点符号往往被当作字符处理。

创建自定义数字格式的过程很简单，关键在于如何使用数字格式符号定义所需格式，有关数字格式符号的说明如表 1所示。

表1     数字格式符号

格式化符号

功能

G（通用格式）

对未格式化的单元格使用默认的格式。在列宽允许的情况下，尽可能地显示数字的精度。对于大数值或很小的数值使用科学记数法格式

#

数字位置标志符。只显示有意义的数字而不显示无意义的零，当数字的小数点两边的数字格式比格式中指定的“#”数少，并不显示增加的零。例如，定义格式代码为####.##，则1234.529显示为1234.53，而234.5显示为234.5

0

数字位置标志符。用以指定小数点两边的位数。例如定义格式代码为0.00，则0.567显示为0.57，0.3显示为0.30

?

数字位置标志符。规则与0相同。格式化的数据以小数点对齐

_

下划线。用来使跟在下划线后面的字符跳过一个字符的宽度。例如，在一个正数格式的末尾输入_，将留出等于右括号的宽度。这个特性可使正数和括号内的负数对齐

.

小数点。用以标出小数点的位置

,

逗号。用作千分位符，只需要在第一个千位的位置做出标示

%

将单元格的值乘以100，并以百分数形式显示

E-  E+  e-   e+

科学记数格式符。如果指数代码的右侧含有0（零）或#（数字符号），Excel将按科学记数法显示数字，并插入E或e。右侧0或#的代码个数决定了指数的位数，E-或e-将在指数中添加符号。E+或e+在正指数时添加正号，负指数时添加符号。

: $ ¥ £ € - +  ( ) 空格

这些符号可以直接输入到格式中并按其通常的意义使用

/

在分数里作为分隔符使用

“文本”

显示双引号中指定的文本

*字符

用跟随在星号后面的字符填充剩余的列宽

@

作为一个格式代码，用来指示出用户输入的文字将出现在这个格式中

[颜色]

用指定的颜色格式化单元格内容。颜色代码必须是格式定义代码部分的第一项

[条件值]

当使用一个格式时，在数字格式里使用条件值。条件由比较运算符和数值两部分组成。例如，[Red][<=100]; [Blue][>100]格式以红色字体显示了小于等于100的数字，而以蓝色字体显示了大于100的数字

*提示：

1）   一旦创建了自定义的数字格式，该格式将一直被保存在工作簿中，并且能像其他Excel自带格式一样被使用，直到该格式被删除。

2）   实质上前面介绍的约定数字格式（“数值”、“货币”、“会计专用”等），都是可以通过自定义格式来实现的。读者可以依次选中图 2中的B1:B11单元格，点击右键，打开“设置单元格格式”对话框，然后在“数字”的分类中选中“自定义”，可以查看到这些预定义的约定数字格式实质的格式定义。如图 4显示的是图 2中的B2（即货币格式设置的类型定义）。

图4     货币约定格式的类型定义

*技巧示例：把值为0的单元格显示为“-”。

方法：自定义格式为：

     G/通用格式;[=0]-

            或

     G/通用格式; -G/通用格式;-

*技巧示例：工资表某列为银行账号，前12位数字相同：622200020011，设置该列能快速输入。

方法：自定义格式为：

     “622200020011”@