excel自定义格式&文本替换函数

excel自定义格式

说到EXCEL中使用自定义格式,首先想到的是快捷键: 先打开单元格式对话框“Ctrl+1”(大键盘的数字键1) ,再使用“Tab ,↑,↓”来选择

要显示负数和零,正数则不显示,自定义为:”；##；0”

要隐藏所有的输入值,自定义为:格式”；；；”

自定义格式的通用模型相当于这样的条件格式:[>0]正数格式;[<0]负数格式;零格式;文本格式;

正数的格式代码为“#，##0.00”，“#”表示只显示有意义的零（其他数字原样显示）,逗号为千分位分隔符，“0”表示按照输入结果显示零,“0.00”小数点后的零的个数表示小数位数;

负数的格式代码为“[Red]-#，##0.00”. “-”表示负数,可选项[Red]定义负数的颜色,可输入“黑色”,“蓝色”,“青色”,“绿色”,“洋红”,“红色”,“白色”或“黄色”.

零的格式代码为“0.00”,小数点后面的“0”的个数表示小数位数;文本的格式代码为“"EXCEL"@”（或"EXCEL”）,EXCEL是要在输入文本的前面或后面显示的字符串,@代表输入单元格的文本,它的位置决定EXCEL在输入文本前面还是后面显示.

以上格式代码可单独使用也可联合使用:

例如单元格内定义为: “52013,148,定义格式为“###0.0”,会显示为“52013.0”;

定义为 "银""码"###0.00"元";再输入”1314.88”会显示为”银码1314.88元”,

定义为"亏""损"-###0.00"元"”再输入“1314.88”会显示为”亏损-1314.88元”

偏注:自定义格式只改变数据的外观,并不改变数据的值,也不影响数据的计算.灵活运用好Excel的自定义格式功能，将会给我们的工作带来非常实惠的妙处.

巧用Excel的自定义格式

一、正负分明

如果要求Excel工作表中正数用“蓝色”显示，负数用“红色”显示，可以这么实现：选中所需的单元格区域，然后打开“单元格格式”对话框（点击“格式→单元格”菜单），单击“数字”选项卡，在“分类”框中单击“自定义”，在“类型”框中键入“［蓝色］［〉0］；［红色］［〈0］”，单击“确定”按钮。现在，Excel就给数字加上不同的颜色了。

二、巧加单位

在Excel工作表中，当你需要给数字加上金额单位（如“万元”）时，选中所需的单元格区域，然后打开“单元格格式”对话框，单击“数字”选项卡，在“分类”框中单击“自定义”，在“类

1

型”列表框中键入“#"万元"”，单击“确定”按钮。

三、输入提速

在你编辑Excel工作表时，可能会遇到大量重复数据需要录入的情况。我们可以这么做：选择需要的单元格区域，然后打开“单元格格式”对话框，单击“数字”选项卡，在“分类”框中单击“自定义”，在“类型”框中输入“☆@炒肉”（“@”是半角字符），单击“确定”按钮。现在，单击刚才选中的单元格区域中一个单元格，并在其中输入“芹菜”二字，按下回车键后，你会惊喜地发现，这个单元格中出现的是“☆芹菜炒肉”。

1、隐藏数据：格式——;;; **;**;**。

2、将数据直接变成万为单位：也有两种，保留1位与4位数。

#!.0,"万元" #!.0000"万元"

3、按小数点对齐。#.???其中几个?即几位。

4、按条件颜色。格式——[绿色][>=60]G/通用格式;[红色]G/通用格式

即大于等于60为绿小于为红，可以为条件格式的一个补充（条件格式只能有三种）

5、将00000000格式的数据转化为日期格式。

0000"."00"."00 0000"-"00"-"00

6、小写中文时11~19会变成一十一~一十九，我要的是十一~十九，怎么办？

[>20][DBNum1];[DBNum1]d

7、怎么让新工作表中有自定义的格式——做成模板！！

2

8、VBA中的可以用Format格式化输出，同样也可以利用函数输出一些自定义的格式。

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

单击菜单“格式”→“单元格”，或者按组合键，用户可以在“单元格格式”对话框的“数字”选项卡中看到有关数字格式的各项设置，如图68-1所示。通过选择不同的格式设置，甚至应用自定义的格式，能够让单元格的显示更加符合它的表达目标。

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

注意

无论为单元格应用了何种数字格式，都只会改变单元格的显示形式，而不会改变单元存储的真正内容。反之，用户在工作表上看到的单元格内容，并不一定是其真正的内容，而可能是原始内容经过各种变化后的一种表现形式。如果用户需要在改变格式的同时也改变实际内容，需要借助TEXT或其他函数来实现。

68-1 Excel内置的数字格式

3

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

图68-2 “常规”数字格式就是没有任何格式

在“分类”列表框中选择“数值”项，然后勾选“使用千位分隔符”复选框，示例内容会发生改变，如图68-3所示。

图68-3 “数值”数字格式

在“分类”列表框中选择“货币”项，然后在“货币符号（国家/地区）”列表框中选择“￥中文（中国）”，则示例内容将再次发生改变，如图68-4所示。

4

图68-4 “货币”数字格式

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

图68-5 “百分比”数字格式

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

图68-6 “分数”数字格式

“分类”列表框中还有许多其他项目，每个项目中又有多个参数可选，读者可以自己进行尝阿萨里尔服饰，打造职业时尚活力。

5

阿萨里尔服饰，打造职业时尚活力。

试，以找到符合需要的最佳格式。

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

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

6

在“单元格格式”对话框中，选择“分类”为“自定义”，如图68-7所示。

图68-7 自定义数字格式

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

单击“确定”即完成。

在“类型”下方的列表框中，已经有许多的格式代码，这些代码就是Excel内置的数字格式所对应的格式代码，或是由用户成功创建的自定义数字格式的格式代码。

如果用户先在“分类”列表框中选定一个内置的数字格式，然后再选定“自定义”项，就能够在“类型”文本框中看到与之对应的格式代码。在原有格式代码的基础上进行修改，能够更快速地得到自己的自定义格式代码。

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

许多Excel用户可能不了解自定义数字格式能够让他们几乎随心所欲地显示单元格数值，或者因为害怕面对长长的格式代码而放弃这个有用的工具。而实际上，自定义数字格式代码并没有想像中那么复杂和困难，只要掌握了它的规则，就很容易读懂和书写格式代码来创建自定义数字格式。

自定义格式代码可以为4种类型的数值指定不同的格式：正数、负数、零值和文本。在代码中，用分号来分隔不同的区段，每个区段的代码作用于不同类型的数值。完整格式代码的组成结构为：

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

在没有特别指定条件值的时候，默认的条件值为0，因此，格式代码的组成结构也可视作：

7

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

用户并不需要每次都严格按照4个区段来编写格式代码，只写1个或2个区段也是可以的。表68-1中列出了没有按4区段写代码时，代码结构的变化。

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

区 段 数

1

2

3

代 码 结 构

格式代码作用于所有类型的数值

第1区段作用于正数和零值，第二区段作用于负数

第1区段作用于正数，第二区段作用于负数，第三区段作用于零值

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

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

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

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

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

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

代 码

G/通用格式

#

注释与示例

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

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

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

从上图可见，可以利用代码0来让数值显示前导零，并让数值固定按指定位数显示。下图是使用#与0组合为最常用的带小数的数字格式

?

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

8

.

%

小数点

百分数

, 千位分隔符

E

科学计数符号

显示格式里的下一个字符

续表

代 码

*

注释与示例

重复下一个字符来填充列宽

_ 留出与下一个字符等宽的空格

利用这种格式可以很容易地将正负数对齐

“文本” 显示双引号里面的文本

@ 文本占位符，如果只使用单个@，作用是引用原始文本

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

[颜色] 颜色代码

「颜色」可以是[black]／[黑色]、[white]／[白色]、[red]／[红色]、[cyan]／[青色] 、[blue]／[蓝色]、[yellow]／[黄色]、[magenta]／[紫红色]或[green]／[绿色]

要注意的是，在英文版用英文代码，在中文版则必须用中文代码

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

[条件值] 设置格式的条件

9

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

°″″

Excel自定义格式参数

2006-11-12 15:50

代码结构组成代码码分为四个部分，中间用“；”号分隔，具体如下：

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

各个参数的涵义

“G/通用格式”：以常规的数字显示，相当于“分类”列表中的“常规”选项。

例：代码：“G/通用格式”。10显示为10；10.1显示为10.1。

“0”：数字占位符。如果单元格的内容大于占位符，则显示实际数字，如果小于点位符的数量，则用0补足

。

例：代码：“00000”。1234567显示为1234567；123显示为00123

代码：“00.000”。100.14显示为100.140；1.1显示为01.100

“#”：数字占位符。只显有意义的零而不显示无意义的零。小数点后数字如大于“#”的数量，则按“#”

的位数四舍五入。

例：代码：“###.##”,12.1显示为12.10;12.1263显示为：12.13

“？”：数字占位符。在小数点两边为无意义的零添加空格，以便当按固定宽度时，小数点可对齐，另外还用于对不等到长数字的分数

例：分别设置单元格格式为“??.??“和“???.???”,对齐结果如下：

10

例：设置单元格自定义格式“# ??/???”。“1.25”显示为“1 1/4”

“.”：小数点。如果外加双引号则为字符。

例：代码“0.#”。“11.23”显示为“11.2”

“%”：百分比。

例：代码“#%”。“0.1”显示为“10%”

“，”：千位分隔符。数字使用千位分隔符。如时在代码中“，”后空，则把原来的数字缩小1000倍。

例：代码：“#，###”。“10000”显示为“10，000”

代码：“#，”。10000显示为10。

代码：“#，，”。“1000000”显示为“1”。

“/”：显示下一个字符。和“”””用途相同都是显是输入的文本，且输入后会自动转变为双引号表达。不同的“/”是显后面的文本，双引号是显示双引中间的文本。

例：代码“ABC”或“”ABC””。均显示为“ABC”

“*”：重复下一次字符，直到充满列宽。

例：代码：“@*-”。“ABC”显示为“ABC-------------------”

“_”（下划线）：留下一个和下一个字符同等宽度的空格

例：代码：$#,##0.00_);($#,##0.00)

“文本”：显示双引号中的文本。

例：代码：“”中国”@”显示为“中国河南”

[颜色]：用指定的颜色显示字符。可有八种颜色可选：红色、黑色、黄色，绿色、白色、兰色、青色和洋红。

例：代码：“[青色];[红色];[黄色];[兰色]”。显示结果为正数为青色，负数显示红色，零显示黄色，文

本则显示为兰色

11

[颜色N]：是调用调色板中颜色，N是0~56之间的整数。

例：代码：“[颜色3]”。单元格显示的颜色为调色板上第3种颜色。

[条件]：可以单元格内容判断后再设置格式。条件格式化只限于使用三个条件，其中两个条件是明确的，另个是“所有的其他”。条件要放到方括号中。必须进行简单的比较。

例：代码：“[>0]”正数”；[=0];”零”；负数”。显示结果是单元格数值大于零显示正数，等于0显示零，小于零显示“负数”。

“！”：显示“"”。由于引号是代码常用的符号。在单元格中是无法用"""来显示出来“"”。要想显示出来，须在前加入“！”

例：代码：“#!"”。“10”显示“10"”

代码：“#!"!"”。“10”显示“10""” 2.1.1.2 时间和日期代码常用日期和时间代码

“YYYY”或“YY”：按四位（1900~9999）或两位（00~99）显示年

“MM”或“M”：以两位（01~12）或一位（1~12）表示月。

“DD”或“D”：以两位（01~31）或一位（1-31）来表示天。

例：代码：“YYYY-MM-DD”。2005年1月10日显示为：“2005-01-10”

代码：“YY-M-D”。2005年10月10日显示为：“05-1-10”

“AAAA”：日期显示为星期。

“H”或“HH”：以一位（0~23）或两位（01~23）显示小时

“M”或“MM”：以一位（0~59）或两位（01~59）显示分钟

“S”或“SS”：以一位（0~59）或两位（01~59）显示秒

例：代码：“HH:MM:SS”。“23:1:15”显示为“23:01:15”

[H]或[M]或[SS]：显示大于24小时的小时或显示大于60的分或秒。

下面介绍几个常遇到的实例

把12345显示为1.2

代码：“0.,”

12

设置千元显示且四舍五入保留两位小数要求：把“12345”显示为：12.35

代码：“#.00,”

在数字中任意插入字符要求：把“20050512”设置为：2005-05-12

代码：“0000-00-00”

在文本或数字前添加字符要求：“郑州市”显示为“河南省郑州市”

代码：“"河南省"@”

要求：“1035010121”显示为“手机号码1034010121”

代码：“"手机号码"@”或“"手机号码"#”

另外日期格式的显示方法

"e":显示四位年份，2003版本有效。

"bbbb":显示四位佛历年份，即以公元前543年为纪年元年，对1900年以后的日期有效。

"mmm":显示英文月份的简称。

"mmmm":显示英文月份的全称。

"ddd":显示英文星期几的简称。

"dddd":显示英文星期几的全称。

关于特殊数字的显示

中文小写数字 [DBNum1][$-804]G/通用格式

例：代码：“[DBNum1][$-804]G/通用格式”。“1”显示为“一”

代码：“[DBNum1][$-804]G/通用格式”。“13”显示为“一十三”

中文小写数字 [DBNum2][$-804]G/通用格式

例：代码：“[DBNum2][$-804]G/通用格式”。“1”显示为“壹”

代码：“[DBNum2][$-804]G/通用格式”。“13”显示为“壹拾叁”

中文小写数字 [DBNum3][$-804]G/通用格式

13

例：代码：“[DBNum3][$-804]G/通用格式”。“123”显示为“1百2十3”

特殊说明

因为参数的特殊性，所以自定义的参数也是有关键字的。如函数=TEXT(A1,"b0000")就会显示错误。因为“b”就是保留的关键字，在自定义格式输入“b”系统就会自动填入“bb”。bb就是佛历年份，即以公元前543年为纪年元年，对1900年以后的日期有效。“bbbb”就是四位佛历年份。要解决=TEXT(A1,"b0000")的错误问题，需要这样定义函数=TEXT(A1,"""b""0000")。在自定义格式中定义就是“"b"0000”。其它的关键字自己体会如：“d”、“e”............

替换字符-文本替换函数 substitute & replace

在许多时候,可能需要对某个文本字符串中的部分内容进行替换,除了使用EXCEL的"替换"功能外,还可以使用文本替换函数.常用的文本替换函数为SUBSTITUTE函数和REPLACE函数,它们的语法分别是:

SUBSTITUTE(text,old_text,new_text,instance_num)

REPLACE(old_text,start_num,num_shars,new_text)

如果明确知道目标字符,但是不知道其在字符串中的具体位置,可以使用SUBSTITUTE函数.

例一:A1单元格内容为"教师职别档次",下面的公式将去除文本字符串中的两个空格,返回"教师职别档次".

=SUBSTITUTE(A1" ","")

例二:A1单元格的内容为"我爱EXCELPX,我爱EXCEL!",下面的公式可以将第二个"爱"替换为"喜欢",返回"我爱EXCELPX,我喜欢EXCEL".

14

=SUBSTITUTE(A1,"爱","喜欢",2)

如果目标字符并不固定,但能够明确知道其在字符串中的具体位置,可以使用REPLACE函数来实现替换.

例三:A1单元格的内容为"",下面的公式可以用一个空字符串替换第6个字符,即删除"-",返回结果为"".

=REPLACE(A1,6,1,"")

REPLACE函数还有个妙用,就是可以在字符串的指定位置插入字符.

例四:A1单元格的内容为"A123456789",用下面的公式可以在原字符串第5个字符前插入字符串"abc",得到"A123abc456789".

以上函数还可以通过嵌套,让它们在公式中执行多重替换的任务,如下面的公式可以清除A1单元格中的数字0~7

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,0,),1,),2,),3,),4,),5,),6,),7,)

例：我好像见过你?

函数 结果 说明

=SUBSTITUTE(A1,"我","他",1) 他好像见过你? 将例句中第1个“我”替换为“他”

=SUBSTITUTE(A1,"你","他") 我好像见过他? 省略instance_num，则将例句中第1个“你”

替换为“他”

例：123456

函数 结果 说明

=TEXT(B1,"0.0") 123456.0 将B1中的数字转化为0.0格式显示的文本

15

=TEXT(B1,"0.0%") 12345600.0% 将B1中的数字转化为0.0%格式显示的文本

如果多重嵌套麻烦的话，可以使用定义名称ｘｆｅｒ的方式简化

16