Excel中数字格式的应用归纳.docx

1、Excel中数字格式的应用归纳Excel中数字格式的应用归纳Excel中数字格式的应用归纳(一) 一、快速格式化单元格数字 可以使用工具栏按钮对单元格中的数字进行格式化。如图1所示，这五个按钮依次为:货币、百分比、千位分隔、增加小数位数、减少小数位数。若选定区域或选定多单元格后应用样式，则所应用的样式将应用于所选区域或单元格。 技巧:快速输入百分比数值，即选择“工具选项”，单击“编辑”选项卡，选中“允许自动百分比输入”，只需输入普通的数值，会被格式化为百分数。 格式化数字的快捷键: Ctrl+Shift+ 常规，即未格式化 Ctrl+Shift+$ 货币格式，带有小数点后两位 Ctrl+Shi

2、ft+% 百分数格式，无小数位 Ctrl+Shift+ 科学计数法格式，带有两个小数位 Ctrl+Shift+# 日期格式，带年、月、日 Ctrl+Shift+ 时间格式，带有上午/下午、小时、分 Ctrl+Shift+! 带有两个小数位、千位分隔符、负值前面有一连字符 Excel会根据以下情况对单元格自动应用一种内部数字格式: (1)包含“/”，可能会被转换成日期或分数格式。 (2)包含“-”，可能会被转换成日期格式。 (3)包含“:”，或后面一个空格和一个字母A或P，可能被转换成时间格式。 (4)包含“E或e”，可能被转换成科学计数法或指数格式。 二、使用“单元格格式”对话框 选“格式单元

3、格”或从右键菜单中选“设置单元格格式”命令或按Ctrl+1快捷键，将弹出“单元格格式”对话框，在“数字”选项卡中对数字格式进行设置。 三、自定义数字格式 在“单元格格式”对话框中选择“数字”选项卡，选中“自定义”类别，在“类型”中输入代码，即可自定义数字格式。 自定义格式串代码(分四部分): 正数;负数;零值;文本 注:如果只使用一部分，格式串将应用于所有值;如果使用两部分，第一部分应用于正值和零值，第二部分应用于负值;如果使用三部分，则第一部分应用于正值，第二部分应用于负值，第三部分应用于零值;如果使用四部分，则最后一部分应用于文本。 自定义数字格式代码: G 以常规格式显示数字 # 数字占

4、位符 0 数字占位符，显示无意义的0 , 数字占位符，为无意义的0添加空格字符。可以使小数点对齐 . 小数点 % 百分号 ， 千位分隔符 E-E+e-e+ 科学计数 $-+/():space 显示这个字符 在格式中显示下一个字符 * 重复下一个字符填充列宽 _ 留一个与下一个字符同宽的空格 “ ” 显示双引号内的字符 文本占位符 color 以指定的颜色来显示字符。可以为:Black,Blue,Cyan,Green,Magenta,Red,White,Yellow color n 显示调色板中相对应的颜色，n为0,56 condition value 允许自已设置数字格式的每一段 自定义日期和

5、时间格式代码: m 以数字显示月份，去掉前面的0 mm 以数字显示月份，并带上前面的0 mmm 以缩写的方式显示月份 mmmm 以全名显示月份 mmmmm 显示月份的首字母 d 以数字显示日期，去掉前面的0 dd 以数字显示日期，带上前面的0 ddd 以缩写的方式显示日 dddd 用全名显示日 yy或yyyy 以两位数显示年或以四位数显示年 h或hh 以数字显示小时并去掉前面的0或带上前面的0 m或mm 以数字显示分钟前去掉前面的0或带上前面的0 s或ss 以数字显示秒并去掉前面的0或带上前面的0 显示大于24的小时数，或大于60的分钟数或秒数 AM/PM 以12小时制显示小时数 技巧:如果要

6、在其他的工作薄中使用当前工作簿中的自定义格式，将已使用自定义格式的单元格复制到另一个工作簿中即可。 四、自定义格式举例(后附表格自定义数字格式示例.xls) 五、在公式和函数中自定义格式显示 (一)使用工作簿函数TEXT(数值,格式样式) 说明:第一个参数为数值、计算结果为数字值的公式，或对包含数字值的单元格的引用; 第二个参数为“单元格格式”对话框中“数字”选项卡上“分类”框中的文本形式的数字格式或自定义的数字格式。 注意:(1)第二个参数中不能包含星号 (*);(2)通过“格式”菜单调用“单元格”命令，然后在“数字”选项卡上设置单元格的格式，只会更改单元格的格式而不会影响其中的数值。使用函

7、数 TEXT 可以将数值转换为带格式的文本，而其结果将不再作为数字参与计算。(3)不是所有的格式化码都生效，如关于颜色设置和重复字符串的格式不起作用。 (二)在单元格中显示数字格式的字符串的函数 Function 数字格式(cell) As String Application.Volatile True 数字格式=cell.Range(“A1”).NumberFormat End Function 然后用公式=数字格式(B2)即可返回单元格B2中的数字格式。该函数可以用在条件求和公式中，如可创建一个公式只把使用特定数字格式的单元格相加。 六、因格式化单元格数字出现的问题和应注意的问题 ,当数

8、字进行数学运算后结果不正确，在某些情况下这是格式设置所引起的。因为单元格中的数字显示的是进行设置后的结果，而其数值是不会改变的，即设置后所看到的可能不是计算中所使用的实际值。解决方法是:设置单元格显示更多的小数位数;或选“工具选项”，选“重新计算”选项卡后，将“以显示精度为准”复选框选中。 ,如果单元格中显示出一连串的#号，说明单元格宽度不够，可以使列变宽后显示出全部数据。 态下输入引号、逗点等。 ,自定义格式时，必须在英文状,在中文版的Excel中“Gerneral”要用“G/通用格式代替”。 ,对自定义格式进行测试，数字选项卡中的示例部分。 ,在中文版的Excel中自定义格式应用颜色时，必

9、须用中文。 在Excel中如何自定义数字格式 虽然Excel为用户提供了大量的数字格式，但还是有许多用户因为工作、学习方面的特殊要求，需要使用一些Excel未提供的数字格式，这时我们就需要利用Excel的自定义数字格式功能来帮助实现用户的这些特殊要求。 一、在Excel中创建自定义数字格式的方法 1、选择要设置格式的单元格或单元格区域。 2、单击“格式”菜单中的“单元格”命令，然后单击“数字”选项卡。 3、在“分类”列表中，单击“自定义”选项。 4、在“类型”框中，编辑数字格式代码以创建所需的格式。 在Excel自定义数字格式使用如下的表达方式: 正数的格式 负数的格式 零的格式 文本的格式

10、#,#0.00 Red-#,#0.00 0.00 TEXT 在Excel自定义数字格式的格式代码中，用户最多可以指定四个节;每个节之间用分号进行分隔，这四个节顺序定义了格式中的正数、负数、零和文本。如果用户在表达方式中只指定两个节，则第一部分用于表示正数和零，第二部分用于表示负数。如果用户在表达方式中只指定了一个节，那么所有数字都会使用该格式。如果在表达方式中要跳过某一节，则对该节仅使用分号即可。 二、自定义数字格式实例 (一)自动添加文本 在日常财务工作中，常常需要在金额数字后加单位“元”，这时就可以使用“0.00元”的自定义数字格式，当用户在单元格中录入数字后，Excel就会自动在数字后加

11、上单位“元”。 如果需要将单元格中录入的数字“32”自动转换成“高三十二班”格式，只要将单元格的数字格式定义为“高DBNum1G/通用格式班”即可。 (二)在自定义数字格式中使用颜色 要设置格式中某一部分的颜色，只要在该部分对应位置用方括号键入颜色名称或颜色编号即可。Excel中可以使用的颜色名称有黑色、蓝色、青色、绿色、洋红、红色、白色、黄色八种不同的颜色，此外Excel还可以使用颜色X的方式来设置颜色，其中X为1-56之间的数字，代表了56种不同的颜色。 例如:当用户需要将单元格中的负数数字用蓝色来表示，只要使用“#,#0.00;蓝色-#,#0.00”自定义数字格式，用户在单元格中录入负数

12、时，Excel就会将数字以蓝色显示。 (三)在自定义数字格式中使用条件格式 在Excel自定义数字格式中用户可以进行条件格式的设置。当单元格中数字满足指定的条件时，Excel可以自动将条件格式应用于单元格。Excel自定义数字格式中可以使用如下六种标准的比较运算符: 运算符 含义 = 等于 大于 = 大于等于 = 小于等于 不等于 在Excel中要想设置满足指定条件数字的格式，在自定义数字格式代码中必须加入带中括号的条件，条件由比较运算符和数值两部分组成。 例如:在学生成绩工作表中，当我们想以红色字体显示大于等于90分的成绩，以蓝色字体显示小于60分的成绩时，其余的成绩则以黑色字体显示，这时只

13、需将自定义数字格式设置为“红色=90;蓝色=90;蓝色=90;蓝色=90G/通用格式;蓝色60G/通用格式;黑色G/通用格式”，你只需选择此格式即可达到同样的使用效果。 (四)隐藏单元格中的数值 在Excel工作表中，有时为了表格的美观或者别的因素，我们希望将单元格中的数值隐藏起来，这时我们使用“;”(三个分号)的自定义数字格式就可达到此目的。这样单元格中的值只会在编辑栏出现，并且被隐藏单元格中的数值还不会被打印出来,但是该单元格中的数值可以被其它单元格正常引用。 奇妙的自定义数字格式 数字格式是单元格格式中最有用的功能之一，专门用于对单元格数值进行格式化。 单击菜单“格式”?“单元格”，或者

14、按组合键，用户可以在“单元格格式”对话框的“数字”选项卡中看到有关数字格式的各项设置，如图68-1所示。通过选择不同的格式设置，甚至应用自定义的格式，能够让单元格的显示更加符合它的表达目标。 图68-1 “单元格格式”对话框中的“数字”选项卡 注意 无论为单元格应用了何种数字格式，都只会改变单元格的显示形式，而不会改变单元存储的真正内容。反之，用户在工作表上看到的单元格内容，并不一定是其真正的内容，而可能是原始内容经过各种变化后的一种表现形式。如果用户需要在改变格式的同时也改变实际内容，需要借助TEXT或其他函数来实现。 68-1 Excel内置的数字格式 Excel内置的数字格式多种多样，能

15、够满足用户在一般情况下的需要。下面用几个例子来说明同样的数字被设置为不同的数字格式后，显示效果会有哪些改变。假设一个单元格中有数字1023.4，在默认情况下，Excel不对单元格设置任何数字格式，此时的格式名称为“常规”，数值按照它的真实面貌显示出来。用户在“单元格格式”对话框的“数字”选项卡中可以看到一个“示例”项目，它可以在用户单击“确定”按钮以前就显示当前单元格的数值在应用了指定的数字格式以后的样式，如图68-2所示。 图68-2 “常规”数字格式就是没有任何格式 在“分类”列表框中选择“数值”项，然后勾选“使用千位分隔符”复选框，示例内容会发生改变，如图68-3所示。 图68-3 “数值”数字格式 在“分类”列表