Excel自定义数字格式详解.docx

1、Excel自定义数字格式详解Excel自定义数字格式详解数字格式是单元格格式中最有用的功能之一，专门用于对单元格数值进行格 式化。单击菜单“格式”-“单元格”，或者按 组合键，用户可以在“单 元格格式”对话框的“数字”选项卡中看到有关数字格式的各项设置，如图 68-1所示。通过选择不同的格式设置，甚至应用自定义的格式，能够让单元格的显 示更加符合它的表达目标。图68-1单元格格式”对话框中的数字”选项卡、八，、八 注意无论为单元格应用了何种数字格式， 都只会改变单元格的显示形式， 而不会改变单元存储的真正内容。反之，用户在工作表上看到的单元格内容，并不一定是其真正的内容， 而可能是原始内容经过

2、各种变化后的一种表现形式。如果用户需要在改变格式的同时也改变实际内 容，需要借助TEXT或其他函数来实现。68-1 Excel内置的数字格式Excel内置的数字格式多种多样，能够满足用户在一般情况下的需要。下面 用几个例子来说明同样的数字被设置为不同的数字格式后， 显示效果会有哪些改变。假设一个单元格中有数字1023.4，在默认情况下，Excel不对单元格设置任 何数字格式，此时的格式名称为“常规”，数值按照它的真实面貌显示出来。用 户在“单元格格式”对话框的“数字”选项卡中可以看到一个“示例”项目，它可以在用户单击“确定”按钮以前就显示当前单元格的数值在应用了指定的数字格式以后的样式，如图6

3、8-2所示图68-4货币”数字格式以下是在“分类”列表框中选择“百分比”项后的示例内容，如图68-5所示在“分类”列表框选择“分数”项，示例内容中的数值以分数形式显示，如图68-6所示。图68-6分数”数字格式“分类”列表框中还有许多其他项目，每个项目中又有多个参数可选，读者 可以自己进行尝试，以找到符合需要的最佳格式。68-2创建自定义数字格式如果Excel内置的数字格式无法满足用户在实际工作中的需求，还可以创建 自定义数字格式。KZJCKZI图68-7自定义数字格式Step 在 类型”文本框中输入自定义的数字格式代码，或者修改原有格式代码。s闻单击确定”即完成。在“类型”下方的列表框中，已

4、经有许多的格式代码，这些代码就是 Excel内置的数字格式所对应的格式代码，或是由用户成功创建的自定义数字格式的格 式代码。如果用户先在“分类”列表框中选定一个内置的数字格式，然后再选定“自 定义”项，就能够在“类型”文本框中看到与之对应的格式代码。在原有格式代 码的基础上进行修改，能够更快速地得到自己的自定义格式代码。68-3自定义数字格式的代码组成规则许多Excel用户可能不了解自定义数字格式能够让他们几乎随心所欲地显示单元格数值，或者因为害怕面对长长的格式代码而放弃这个有用的工具。 而实际上，自定义数字格式代码并没有想像中那么复杂和困难，只要掌握了它的规则， 就很容易读懂和书写格式代码来

5、创建自定义数字格式。自定义格式代码可以为4种类型的数值指定不同的格式：正数、负数、零值 和文本。在代码中，用分号来分隔不同的区段，每个区段的代码作用于不同类型 的数值。完整格式代码的组成结构为：大于条件值”格式；小于条件值”格式；等于条件值”格式;文本格式在没有特别指定条件值的时候，默认的条件值为 0,因此，格式代码的组成结构也可视作：正数格式；负数格式；零值格式；文本格式用户并不需要每次都严格按照 4个区段来编写格式代码，只写1个或2个区 段也是可以的。表68-1中列出了没有按4区段写代码时，代码结构的变化。 表68-1 自定义数据格式代码结构规则区段数格式代码作用于所有类型的数值 第1区段

6、作用于正数和零值，第二区段作用于负数F面有一个自定义数字格式代码的例子，它针对 4种不同类型的数值定义了不同的格式:#,#0.00_;红色-#,#0.00;绿色G/ 通用格式;“ ”图68-8是Excel中应用了这种数字格式的单元格的显示at示力冤磊肌道促明 110.4VU 抻 JIM色髓禾0耐主4护溥:应引耳图68-8自定义数字格式的单元格的显示68-4常用自定义数字格式的代码与示例表68-2 常用自定义数字格式的代码与示例注释与示例不设置任何格式，按原始输入的数值显示r 码G/通用格式I 儿1；为Attft僅苗宣豐樞辰悅島 154TB0005占0OTQ. 00恥5&轴 POWkJML 1数

7、字占位符，只显示有效数字，不显示无意义的零值#0 数字占位符，当数字比代码的数量少时，显示无意义的 0i m5*TI300 m04W-oo0Q54.MWQC , C0_JL_从上图可见，可以利用代码 0来让数值显示前导零，并让数值固定按指定位数显示。下图是使用#与 0组合为最常用的带小数的数字格式数字占位符，需要的时候在小数点两侧增加空格；也可以用于具有不同位数的分数1血 133.mnrrt.mmWT71 R. -07K?7. ITO. 小数点% 百分数1 M 洛和ns忆搐氐代別 I闕gMi 黑&一血Wacm 5.4仇财55 TA 0. MTE 科学计数符号 显示格式里的下一个字符续表如果使用

8、多个，则可以重复文本l 靠裔育fiSXttXKM 1飞此4拟+1人忧Jtt文本显示双引号里面的文本颜色可以是black / 黑色、white /白色卜red /红色、cyan /青色、blue/蓝色、yellow /黄色卜magenta/ 紫红色或green /绿色 要注意的是，在英文版用英文代码，在中文版则必须用中文代码颜色n显示Excel调色板上的颜色，n是0 56之间的一个数值:：r- MB LX no1加* trt赴幻*”艸11234H. W却啦条件值 设置格式的条件28T5 沁 I? J t wrt mtn ttu mt tttttom： h33耳丹831 皿).，让产= 右在 低】

9、 冇熬拜加盟I l * tttfitttt mt卿 船 C6e.oI : 1Q& 即Q, L色 1。123. T i： 3Mj ).*.L, L ffO.OOJRJSL- . IT Lawj tit ji. ,有关日期与时间方面的自定义格式方法，请参阅技巧技巧69 随心所欲设置日期格式日期与时间是用户在Excel中经常需要处理的一类数值，在不同的报告中, 会使用不同的日期格式来表示它们。在默认情况下，当用户在单元格中输入日期或时间的时候， Excel会使用系、八，、八注意在这里的更改，会影响到多个应用程序而不仅仅是 Excel。Excel也内置了许多日期与时间类的格式，在“单元格格式”对话框的

10、“数 字”选项卡中，用户可以在“分类”列表框中选择“日期”或“时间”项下面的 某种格式并应用它们如图69-2所示。用户还可以自定义日期与时间类的数字格式，只是格式代码会简单很多。表 69-1介绍了用于定义日期与时间的代码，关于如何创建自定义数字格式，请参 阅68-2小节。图69-2 Excel内置的日期与时间格式使用没有前导零的数字来显示月份（112） 使用有前导零的数字来显示月份（0112）使用英文全称来显示月份（January December）使用没有前导零的数字来显示日期（131）使用英文缩写来显示星期几（SunSat）使用英文全称来显示星期几（Sunday Saturday）使用没有

11、前导零的数字来显示小时（023）使用没有前导零的数字来显示分钟（059）使用没有前导零的数字来显示秒钟（059）图69-3中用上述代码创建了一些常用的实例， 其中的“原始数值”是日期或时间的序列值。jbttRdiAl Tw Mv %k：w E7T 呦 4 ywy*科 :T 曲 r w 4i nw ijy-sw-ii驴耳弹迪日蟹甲亢下年期対E译廿月WB* “沖上年f下午小1=片o. maijsw-hfin呼诅:】E MQ.urnasrmssstK：J2.睡员钳hh ；- SB3：n. _珀幻翻沏一.d】图69-3日期/时间自定义格式实例技巧7。自定义数字格式的经典应用在本技巧中，读者可以学习到一

12、些自定义数字格式的经典实例。有关自定义 数字格式的创建与应用方法，请参阅技巧 68。70-1零值不显示单击菜单“工具选项”，在“选项”对话框的“视图”中，如果取消 勾选“零值”复选框，Excel将不显示当前工作表中的零值。利用自定义数字格式，用户不但能够实现同样的效果，而且能够让工作表中 的一部分单元格不显示零值而其他的单元格仍然显示零值。G/通用格式;G/通用格式；上面的格式代码中，第3区段留空，这样就可以使零值显示为空白。当然, 对应于正数与负数的第1、2区段，也可以另行定义格式。70-2快速放缩数值许多用户在工作中常常需要处理很大的数字，利用下面的自定义数字格式, 能够在不改变数值本身的

13、同时，把它们进行缩放，如图 70-1所示。購丸 代VII23i*：師柴叱值l?X4E I123*品J肿137. K STi札g.耳厅-1234113or-12JK . L)o_l0.900,00L21-M图70-2智能显示百分比格式代码:10.00%;#.00_%这段代码有两个区段，第1个区段使用了一个判断，对应数值小于 1时的格 式，第2个区段则对应不小于1时的格式。在第2个区段中，百分号前使用了一 个下划线，目的是保留一个与百分号等宽的空格。70-4显示分数Excel内置了一些分数的格式，用户还可以使用自定义数字格式得到更多分 数的表示方法，比如在显示的时候加上“又”字、加上表示单位的符号

14、，或者使 用一个任意的数字作为分母，如图 70-3所示。nn3, 253 1/9t ?冲E 它X2535tl/4IL亍&敕X2SS 4/16豐示井炖3” 253 S/26c ?/203L1SS 13/50i IfSO图70-3更多显示分数的自定义数字格式有关分数的其他信息，请参阅技巧 54。70-5隐藏某些类型的内容可以使用自定义数字格式隐藏某些类型的输入内容，或者把某些类型的输入 内容用特定的内容来替换，如图70-4所示。37LZ3123. W大于rW TlT；230元县亍丈*.帀昱=梵罕kndlEiiel只豐示文尊予H飲李丁甸oo o. w a . *FU1十附亭.戈七芾1卜霊EkcI *

15、0.00 CL 側 0 * W2 L J :J 图70-4使用自定义数字格式隐藏某种类型的数值在技巧68中谈到过：无论为单元格应用了何种数字格式，都只会改变单元格 的显示内容，而不会改变单元存储的真正内容。因此， Excel没有提供直接的方 法来让用户得到自定义数字格式的显示值。如果复制一个有格式的单元格到另一 个单元格，用户只能得到原始数值。S切QStep 但是，仍有多种技巧可以帮助用户达到这一目的，下面介绍一种最简单有效 的方法。选定应用了数字格式的单元格或单元格区域。快速地连续两次按Ctrl+C组合键，Office剪贴板出现在任务窗格中，如图71-1所示。图71-1连续两次按Ctrl+C会让Office剪贴板出现选定目标单元格或单元格区域， 单击Office剪贴板中刚才复制项目旁边的 下拉箭头，在弹出的菜单中选择 粘贴”命令，如图71-2所示。这样，原 始区域的数据会被粘贴到目标区域。单击菜单编辑”-选择性粘贴”，在选择性粘贴”对话框中选择 文本”, 然后单击 确定”，如图71-3所示。F i WT 1匚*UH悅窖的旳辛-图71-3选择性粘贴”对话框中选择 文本经过两次粘贴，用户最终可以得到与原始区域显示值完全相同的内容。