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

资源描述

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

《Excel自定义数字格式详解.docx》由会员分享，可在线阅读，更多相关《Excel自定义数字格式详解.docx（19页珍藏版）》请在冰豆网上搜索。

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

Excel自定义数字格式详解

数字格式是单元格格式中最有用的功能之一，专门用于对单元格数值进行格式化。

单击菜单“格式”-“单元格”，或者按组合键，用户可以在“单元格格式”对话框的“数字”选项卡中看到有关数字格式的各项设置，如图68-

1所示。

通过选择不同的格式设置，甚至应用自定义的格式，能够让单元格的显示更加符合它的表达目标。

图68-1单元格格式”对话框中的数字”选项卡

、八，、、八注意

无论为单元格应用了何种数字格式，都只会改变单元格的显示形式，而不会改变单元存储的

真正内容。

反之，用户在工作表上看到的单元格内容，并不一定是其真正的内容，而可能是

原始内容经过各种变化后的一种表现形式。

如果用户需要在改变格式的同时也改变实际内容，需要借助TEXT或其他函数来实现。

68-1Excel内置的数字格式

Excel内置的数字格式多种多样，能够满足用户在一般情况下的需要。

下面用几个例子来说明同样的数字被设置为不同的数字格式后，显示效果会有哪些改

变。

假设一个单元格中有数字1023.4，在默认情况下，Excel不对单元格设置任何数字格式，此时的格式名称为“常规”，数值按照它的真实面貌显示出来。

用户在“单元格格式”对话框的“数字”选项卡中可以看到一个“示例”项目，它

可以在用户单击“确定”按钮以前就显示当前单元格的数值在应用了指定的数字

格式以后的样式，如图68-2所示

图68-4货币”数字格式

以下是在“分类”列表框中选择“百分比”项后的示例内容，如图68-5所示

在“分类”列表框选择“分数”项，示例内容中的数值以分数形式显示，如

图68-6所示。

图68-6分数”数字格式

“分类”列表框中还有许多其他项目，每个项目中又有多个参数可选，读者可以自己进行尝试，以找到符合需要的最佳格式。

68-2创建自定义数字格式

如果Excel内置的数字格式无法满足用户在实际工作中的需求，还可以创建自定义数字格式。

□KZJCKZI

图68-7自定义数字格式

Step❷

在类型”文本框中输入自定义的数字格式代码，或者修改原有格式代码。

s闻©

单击确定”即完成。

在“类型”下方的列表框中，已经有许多的格式代码，这些代码就是Excel

内置的数字格式所对应的格式代码，或是由用户成功创建的自定义数字格式的格式代码。

如果用户先在“分类”列表框中选定一个内置的数字格式，然后再选定“自定义”项，就能够在“类型”文本框中看到与之对应的格式代码。

在原有格式代码的基础上进行修改，能够更快速地得到自己的自定义格式代码。

68-3自定义数字格式的代码组成规则

许多Excel用户可能不了解自定义数字格式能够让他们几乎随心所欲地显示

单元格数值，或者因为害怕面对长长的格式代码而放弃这个有用的工具。

而实际

上，自定义数字格式代码并没有想像中那么复杂和困难，只要掌握了它的规则，就很容易读懂和书写格式代码来创建自定义数字格式。

自定义格式代码可以为4种类型的数值指定不同的格式：

正数、负数、零值和文本。

在代码中，用分号来分隔不同的区段，每个区段的代码作用于不同类型的数值。

完整格式代码的组成结构为：

大于条件值”格式；小于条件值”格式；等于条件值”格式;文本格式

在没有特别指定条件值的时候，默认的条件值为0,因此，格式代码的组成

结构也可视作：

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

用户并不需要每次都严格按照4个区段来编写格式代码，只写1个或2个区段也是可以的。

表68-1中列出了没有按4区段写代码时，代码结构的变化。

表68-1自定义数据格式代码结构规则

区段数

格式代码作用于所有类型的数值第1区段作用于正数和零值，第二区段作用于负数

F面有一个自定义数字格式代码的例子，它针对4种不同类型的数值定义了

不同的格式:

#,##0.00_;［红色］-#,##0.00;［绿色］G/通用格式;"“"@"”"

图68-8是Excel中应用了这种数字格式的单元格的显示

「at示力

冤磊肌道

促明1

10^.4

VU抻JIM色髓禾

■耐•

主4护溥:

应引耳

图68-8自定义数字格式的单元格的显示

68-4常用自定义数字格式的代码与示例

表68-2常用自定义数字格式的代码与示例

注释与示例

不设置任何格式，按原始输入的数值显示

■r码

G/通用格式

I儿1；为

Attft僅

苗宣豐樞辰悅島1

54TB

0005占

0OTQ.00

恥5&轴

POWkJML1

数字占位符，只显示有效数字，不显示无意义的零值

0数字占位符，当数字比代码的数量少时，显示无意义的0

5*TI

300m

04W-oo

0Q54.M

WQC',C0

_JL_

从上图可见，可以利用代码0来让数值显示前导零，并让数值固定按指定位数显示。

下

图是使用#与0组合为最常用的带小数的数字格式

数字占位符，需要的时候在小数点两侧增加空格；也可以用于具有不同位数的分数

1血㈣

■133.m

nrrt.^m

WT7

1R.-

7K?

7.ITO

.小数点

%百分数

1M洛和

ns■忆搐氐代別I

闕gMi黑

&一血

Wacm5.4

仇财

55>T'A0.MT

E科学计数符号

\显示格式里的下一个字符

续表

如果使用多个@，则可以重复文本

l靠裔育

fiS'XttXKM1

飞此4拟+1人忧

Jt^t

文本

显示双引号里面的文本

「颜色」可以是［black］/［黑色］、［white］/［白色卜［red］/［红色］、［cyan］/［青色］、［blue］

/［蓝色］、［yellow］/［黄色卜［magenta］/［紫红色］或［green］/［绿色］要注意的是，在英文版用英文代码，在中文版则必须用中文代码

［颜色n］

显示Excel调色板上的颜色，n是0~56之间的一个数值

［:

：

r-［MBLXno

1加*trt赴幻*”艸1

1234H.W却■・啦

［条件值］设置格式的条件

28T5沁I?

Jtwrtmtnttumttttt

tom：

h33耳丹831皿）・《«.・・・，

让产=右在低】冇熬拜加盟Il*<*■tttfittttmt

卿船C6e.］o

I':

1Q&即Q,L£色1。

123.Ti：

3Mj）■.■*.L®,LffO.OO

JRJSL-.ITLawj［titji.«<>,«

有关日期与时间方面的自定义格式方法，请参阅技巧

技巧69随心所欲设置日期格式

日期与时间是用户在Excel中经常需要处理的一类数值，在不同的报告中,会使用不同的日期格式来表示它们。

在默认情况下，当用户在单元格中输入日期或时间的时候，Excel会使用系

、八，、、八

注意

在这里的更改，会影响到多个应用程序而不仅仅是Excel。

Excel也内置了许多日期与时间类的格式，在“单元格格式”对话框的“数字”选项卡中，用户可以在“分类”列表框中选择“日期”或“时间”项下面的某种格式并应用它们如图69-2所示。

用户还可以自定义日期与时间类的数字格式，只是格式代码会简单很多。

表69-1介绍了用于定义日期与时间的代码，关于如何创建自定义数字格式，请参阅68-2小节。

图69-2Excel内置的日期与时间格式

使用没有前导零的数字来显示月份（1〜12）使用有前导零的数字来显示月份（01〜12）

使用英文全称来显示月份（January~December）

使用没有前导零的数字来显示日期（1〜31）

使用英文缩写来显示星期几（Sun〜Sat）

使用英文全称来显示星期几（Sunday—Saturday）

使用没有前导零的数字来显示小时（0〜23）

使用没有前导零的数字来显示分钟（0〜59）

使用没有前导零的数字来显示秒钟（0〜59）

图69-3中用上述代码创建了一些常用的实例，其中的“原始数值”是日期或

时间的序列值。

jbttRdi

AlTwMv%

k：

wE7T呦■»4ywy

*科％:

T曲r'

■■w4inwi

jy-sw-ii

驴』耳弹迪日蟹甲亢下年期£対

E译廿月WB*“沖上年f下午小1=片

o.maijsw-

h'fin

呼诅:

】EM

urn

asrmssst

K：

＞：

2.睡员钳

[hh]；■

-SB3：

n._

□珀幻翻沏

一.d】

图69-3日期/时间自定义格式实例

技巧7。

自定义数字格式的经典应用

在本技巧中，读者可以学习到一些自定义数字格式的经典实例。

有关自定义数字格式的创建与应用方法，请参阅技巧68。

70-1零值不显示

单击菜单“工具选项”，在“选项”对话框的“视图”中，如果取消勾选“零值”复选框，Excel将不显示当前工作表中的零值。

利用自定义数字格式，用户不但能够实现同样的效果，而且能够让工作表中的一部分单元格不显示零值而其他的单元格仍然显示零值。

G/通用格式;G/通用格式；

上面的格式代码中，第3区段留空，这样就可以使零值显示为空白。

当然,对应于正数与负数的第1、2区段，也可以另行定义格式。

70-2快速放缩数值

许多用户在工作中常常需要处理很大的数字，利用下面的自定义数字格式,能够在不改变数值本身的同时，把它们进行缩放，如图70-1所示。

■購丸代VI

I23<54T£S

gIS

21:

疏社11

-lint

aoti.

理g耳日檢灿I

O,«t,

>i*「：

師柴叱值

X4EI

123*品J肿

137.KSTi

札g."耳厅-

1234«

113

-12J

-J2,J

忙：

①

拽？

趺诜

CLO

trr

埔”時貧*dl

12MM

12.MU

0"-QdOO

K耳口陞耿值

lSUfW

i；,3丹

^L0*vr：

123.4（

-OJ2

工叫

应f坑忆世■巾

GOO

（XQrt

123VM

】注仙£

也啊"V

牌*殊吒曲值

ISfW

]£3.46千

OlOCv呼"

13.^4

战霑烯起號價

-I2M

■12.M

01*00

吨£13宣化细

也06

o*"a.

图70-1使用自定义数字格式缩放数值

70-3智能显示百分比

F面的自定义数字格式只让小于1的数字按“百分比”格式显示，大于等于

1的数字使用标准格式显示，同时还让所有的数字排列整齐，如图70-2所示

JC轴融fliit尿方代硏说明

札滋

乱血

好幡JL壬苜笄tt

1,00

[.116.>K.«.L）o_l

0.9

0,00®

1-M

图70-2智能显示百分比

格式代码:

[<1]0.00%;#.00_%

这段代码有两个区段，第1个区段使用了一个判断，对应数值小于1时的格式，第2个区段则对应不小于1时的格式。

在第2个区段中，百分号前使用了一个下划线，目的是保留一个与百分号等宽的空格。

70-4显示分数

Excel内置了一些分数的格式，用户还可以使用自定义数字格式得到更多分数的表示方法，比如在显示的时候加上“又”字、加上表示单位的符号，或者使用一个任意的数字作为分母，如图70-3所示。

3,25

31/9

冲

E它

X25

35tl/4

IL亍&敕

X2S

S4/16"

豐示井炖

3”25

3S/26

/20

3L1S

S13/50

iIfSO

图70-3更多显示分数的自定义数字格式

有关分数的其他信息，请参阅技巧54。

70-5隐藏某些类型的内容

可以使用自定义数字格式隐藏某些类型的输入内容，或者把某些类型的输入内容用特定的内容来替换，如图70-4所示。

LZ3

123.W

大于rWTlT；

230'

元县亍丈*.帀昱=梵罕

kndl

Eiiel

只豐示文尊・予H〒飲李

丁甸

^ooo.wa.**

FU1十附亭.戈七芾1■卜霊〒

EkcI***■

0.00CL側0**

LJ:

图70-4使用自定义数字格式隐藏某种类型的数值

在技巧68中谈到过：

无论为单元格应用了何种数字格式，都只会改变单元格的显示内容，而不会改变单元存储的真正内容。

因此，Excel没有提供直接的方法来让用户得到自定义数字格式的显示值。

如果复制一个有格式的单元格到另一个单元格，用户只能得到原始数值。

S切Q

Step❷

但是，仍有多种技巧可以帮助用户达到这一目的，下面介绍一种最简单有效的方法。

选定应用了数字格式的单元格或单元格区域。

快速地连续两次按＜Ctrl+C＞组合键，Office剪贴板出现在任务窗格中，如

图71-1所示。

图71-1连续两次按＜Ctrl+C＞会让Office剪贴板出现

选定目标单元格或单元格区域，单击Office剪贴板中刚才复制项目旁边的下拉箭头，在弹出的菜单中选择粘贴”命令，如图71-2所示。

这样，原始区域的数据会被粘贴到目标区域。

单击菜单编辑”-选择性粘贴”，在选择性粘贴”对话框中选择文本”,然后单击确定”，如图71-3所示。

iWT1

匚*

■悅窖的旳辛-

图71-3选择性粘贴”对话框中选择文本

经过两次粘贴，用户最终可以得到与原始区域显示值完全相同的内容。

展开阅读全文