Excel中数字格式的应用归纳(一)
一、快速格式化单元格数字
可以使用工具栏按钮对单元格中的数字进行格式化。如图1所示,这五个按钮依次为:货币、百分比、千位分隔、增加小数位数、减少小数位数。若选定区域或选定多单元格后应用样式,则所应用的样式将应用于所选区域或单元格。
图1
技巧:快速输入百分比数值,即选择“工具—选项”,单击“编辑”选项卡,选中“允许自动百分比输入”,只需输入普通的数值,会被格式化为百分数。
格式化数字的快捷键:
Ctrl+Shift+~ 常规,即未格式化
Ctrl+Shift+$ 货币格式,带有小数点后两位
Ctrl+Shift+% 百分数格式,无小数位
Ctrl+Shift+^ 科学计数法格式,带有两个小数位
Ctrl+Shift+# 日期格式,带年、月、日
Ctrl+Shift+@ 时间格式,带有上午/下午、小时、分
Ctrl+Shift+! 带有两个小数位、千位分隔符、负值前面有一连字符
Excel会根据以下情况对单元格自动应用一种内部数字格式:
(1)包含“/”,可能会被转换成日期或分数格式。
(2)包含“-”,可能会被转换成日期格式。
(3)包含“:”,或后面一个空格和一个字母A或P,可能被转换成时间格式。
(4)包含“E或e”,可能被转换成科学计数法或指数格式。
二、使用“单元格格式”对话框
选“格式—单元格”或从右键菜单中选“设置单元格格式”命令或按Ctrl+1快捷键,将弹出“单元格格式”对话框,在“数字”选项卡中对数字格式进行设置。
三、自定义数字格式
在“单元格格式”对话框中选择“数字”选项卡,选中“自定义”类别,在“类型”中输入代码,即可自定义数字格式。
自定义格式串代码(分四部分):
正数;负数;零值;文本
注:如果只使用一部分,格式串将应用于所有值;如果使用两部分,第一部分应用于正值和零值,第二部分应用于负值;如果使用三部分,则第一部分应用于正值,第二部分应用于负值,第三部分应用于零值;如果使用四部分,则最后一部分应用于文本。
自定义数字格式代码:
G 以常规格式显示数字
# 数字占位符
0 数字占位符,显示无意义的0
? 数字占位符,为无意义的0添加空格字符。可以使小数点对齐
. 小数点
% 百分号
, 千位分隔符
E-E+e-e+ 科学计数
$-+/():space 显示这个字符
\ 在格式中显示下一个字符
* 重复下一个字符填充列宽
_ 留一个与下一个字符同宽的空格
“ ” 显示双引号内的字符
@ 文本占位符
[color] 以指定的颜色来显示字符。可以为:Black,Blue,Cyan,Green,Magenta,Red,White,Yellow
[color n] 显示调色板中相对应的颜色,n为0~56
[condition value] 允许自已设置数字格式的每一段
自定义日期和时间格式代码:
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小时制显示小时数
技巧:如果要在其他的工作薄中使用当前工作簿中的自定义格式,将已使用自定义格式的单元格复制到另一个工作簿中即可。
四、自定义格式举例(后附表格自定义数字格式示例.xls)
五、在公式和函数中自定义格式显示
(一)使用工作簿函数TEXT(数值,格式样式)
说明:第一个参数为数值、计算结果为数字值的公式,或对包含数字值的单元格的引用;
第二个参数为“单元格格式”对话框中“数字”选项卡上“分类”框中的文本形式的数字格式或自定义的数字格式。
注意:(1)第二个参数中不能包含星号 (*);(2)通过“格式”菜单调用“单元格”命令,然后在“数字”选项卡上设置单元格的格式,只会更改单元格的格式而不会影响其中的数值。使用函数 TEXT 可以将数值转换为带格式的文本,而其结果将不再作为数字参与计算。(3)不是所有的格式化码都生效,如关于颜色设置和重复字符串的格式不起作用。
(二)在单元格中显示数字格式的字符串的函数
Function 数字格式(cell) As String
Application.Volatile True
数字格式=cell.Range(“A1”).NumberFormat
End Function
然后用公式=数字格式(B2)即可返回单元格B2中的数字格式。该函数可以用在条件求和公式中,如可创建一个公式只把使用特定数字格式的单元格相加。
六、因格式化单元格数字出现的问题和应注意的问题
?当数字进行数学运算后结果不正确,在某些情况下这是格式设置所引起的。因为单元格中的数字显示的是进行设置后的结果,而其数值是不会改变的,即设置后所看到的可能不是计算中所使用的实际值。解决方法是:设置单元格显示更多的小数位数;或选“工具—选项”,选“重新计算”选项卡后,将“以显示精度为准”复选框选中。
?如果单元格中显示出一连串的#号,说明单元格宽度不够,可以使列变宽后显示出全部数据。
?自定义格式时,必须在英文状态下输入引号、逗点等。
?在中文版的Excel中“Gerneral”要用“G/通用格式代替”。
?对自定义格式进行测试,数字选项卡中的示例部分。
?在中文版的Excel中自定义格式应用颜色时,必须用中文。
UploadFiles/2006-4/424993803.rar
http://club.excelhome.net/dispbbs.asp?BoardID=1&ID=156923&replyID=&skin=0