Excel技巧，原来Excel自定义格式可以这样玩！

 

设置单元格格式在Excel中应该来说是一种司空见惯的操作，几乎任何接触到Excel的人都会用到这一功能，但是能把单元格格式（特别是自定义格式）用好、用高级的人却凤毛麟角。

灵活利用 Excel 自定义格式，我们可以完成一些看起来非常神奇、非常高级的效果，比如：

①条件格式

②巧加单位

③输入提速

……

这么好用的工具，多数人因为害怕长长的格式代码而放弃。事实上，自定义格式代码并没有想像中那么困难，只要掌握了它的规则，任何人都可以很容易读懂和书写格式代码。

接下来星爷就由浅入深，从0到1详细讲解 Excel 单元格自定义格式关键技术，相信看完本篇教程，你会惊呼：原来Excel自定义格式可以这样玩！

001 自定义格式概述

▌01 调出单元格格式对话框

选中需要设置格式的单元格，按「CTRL+1」快捷键打开「设置单元格格式」对话框。

在对话框的第一个分区「数字」中，左侧的「分类」列表中有常规、数值、货币等项目，这些项目时预设的格式，根据提示很容易掌握。

我们重点要学习的就是最后一项「自定义」格式，选择「自定义」格式后，在右侧的「类型」编辑框，可以输入自定义格式代码。同时，可以看到下面有很多系统预设的代码。

我们可以直接使用这些预设的代码，也可以根据代码的规则，写出更加符合实际需求的代码。

需要特别说明的是：「自定义数字格式」并不会改变数值本身，只改变数值的显示方式（事实上所有类型的单元格格式都只是改变了数值的显示方式）。

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

▌02 自定义格式区段

自定义格式代码，共有四个区段，在代码中，用分号来分隔不同的区段，每个区段的代码作用于不同类型的数值。完整格式代码的组成结构为：

在没有特别指定条件值的时候，默认的条件值为0，因此，默认情况下格式代码的四个区段对用的对象是这样的：

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

实际使用中，自定义格式代码的四个区段不一定全部使用，这四个区段使用一部分的情况，代码的结构如下表所示：

002 自定义格式基础字符

自定义格式代码是由占位符构成的，各种各样的占位符就像是自定义格式中通用语言一样，Excel能很好地识别有占位符组成的语言。因此，掌握了占位符就相当于掌握了自定义格式代码。

输入格式代码的方法：

①「CTRL+1」快捷键打开「设置单元格格式」

②在数字分区→「自定义」→「类型」，在类型编辑框，即可输入格式代码，如图输入的格式代码为：#,##0

另外，在示例处可以时时预览格式的效果。下文所讲的所有字符都是按照上述方式进行填写，就不在赘述。

▌01、"G/通用格式"

注释：以常规的数字显示,相当于"分类"列表中的"常规"选项。

代码：G/通用格式。

效果：52 显示为 52；52.5 显示为 52.5

▌02、 "#"

注释：数字占位符。只显有意义的零而不显示无意义的零。小数点后数字如大于"#"的数量,则按"#"的位数四舍五入。

代码：###.##

效果：181.2 显示为 181.2, 18.4328 显示为 18.43

▌03、"0"

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

代码：00000。

效果：5201314 显示为 5201314；521 显示为 00521

（5201314的位数大于占位符的位数，因此显示实际数字；520的位数小于占位符位数，因此用0补足）

用0补足的情况，不能改变原有数字的大小，因此整数部分0补足在前面，小数部分，0补足在后面。

比如：代码00.000。520.13显示为 520.130, 5.2显示为05.200

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

 

▌04、"?"

注释：数字占位符。在小数点两边为无意义的零添加空格,以便当按固定宽度时,小数点可对齐；也可以用于具有不同位数的分数。

▌05、"@"

注释：文本占位符。如果只使用单个@, 作用是引用原始文本；如果使用多个@, 则可以重复文本。

注意：@符号的位置决定了Excel输入的数字数据相对于添加文本的位置

▌06、"*"

注释：重复下一次字符,直到充满列宽

代码：@*-。123 显示为 123-------------------

▌07、","

注释：千位分隔符

代码 #,### 。12000 显示为 12,000

▌08、"\"

注释：用文本格式显示接下来的字符。"文本"：显示双引号里面的文本；"\"：用文本格式显示接下来的字符。和 "" 用途相同都是显示输入的文本, 且输入后会自动转变为双引号表达。

▌09、[]

注释：条件格式代码。可以将条件或者颜色（颜色代码也是一种条件）写入[]，从而实现自定义条件。

（默认情况下自定义格式的四个区段的条件是固定的，即分别为"正数格式;负数格式;零值格式;文本格式",使用[]可以自行设置每个区段的条件）

可以根据单元格内容判断后再设置格式。条件格式化只限于使用三个条件，其中两个条件是明确的，另个是“所有的其他”。

▌10、颜色

注释：用指定的颜色显示字符。可有八种颜色可选：红色、黑色、黄色、绿色、白色、蓝色、青色和洋红。

代码：[蓝色];[红色];[黄色];[绿色]

效果：显示结果为正数为蓝色, 负数显示红色, 零显示黄色, 文本则显示为绿色

注：颜色代码的另一种方式

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

代码：[颜色1];[颜色2];[颜色3];[颜色4]

效果：显示结果为正数为调色板上第1种颜色……

▌11、时间和日期代码

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

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

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

▌12、“_"

注释：在数字格式中创建N个字符宽的空格

▌13、空

注释：表示隐藏

最后，这些代码可以进行组合使用，从而创建出更加复杂的自定义格式。

003 典型应用

▌01、巧显示单位

如果将数值和单元写在同一个单元格中，那么数值将变为无法参与计算的文本形式，这在Excel数据表中是不允许的，如果确实需要显示单位，可以这样处理。

选中需要设置单位的数值列→“Ctrl+1”组合键→“自定义”→“类型”，在原有的“G/通用格式”后面加上单位即可，如图所示，所需要显示的单位为“台”。

这样设置之后，单元格中的显示为“数值+单位”，但是实际上单元格中只有数字，格式为数值格式，可以参与数学运算，如图所示。

▌02、颜色分明

如图我们希望评价各个指标的达成情况，对于达成率>=1的指标，使用“绿色”显示；达成率>=0.95的指标，使用“黄色”指标显示；低于0.95的用红色显示。

选中所需的单元格区域，自定义格式代码中写入代码

[绿色][>=1];[黄色][>=0.95];[红色]

则各个指标会根据设置的代码显示对于的颜色。

▌03、输入提速

当在想工作表中录入数据时，如果遇到大量重复录入的情况，我们可以将一部分数据作为固定值进行显示。

比如我们编写代码：

“微软股份有限公司@”

现在，单击刚才选中的单元格区域中一个单元格，并在其中输入“销售部”几个字，按下回车键后，这个单元格中出现的是“微软股份有限公司销售部”。

这样就能大幅提高输入的效率。

▌04、条件格式

在Excel 自定义数字格式中用户可以进行条件格式的设置。当单元格中数字满足指定的条件时，Excel 可以自动将条件格式应用于单元格。

可以根据单元格内容判断后再设置格式。条件格式化只限于使用三个条件，其中两个条件是明确的，另个是“所有的其他”。

▌05、显示为密文

在Excel 工作表中，有时为了表格的美观或者别的因素，我们希望将单元格中的数值隐藏起来，这时我们使用“;;;”(三个分号)的自定义数字格式就可达到此目的。

注：这样单元格中的值只会在编辑栏出现，并且被隐藏单元格中的数值还不会被打印出来,但是该单元格中的数值可以被其他单元格正常引用。

最后  自定义格式实例

一、 自定义格式的进入

开始——单元格——格式——设置单元格格式－－数字——自定义

二、 基本原理

在格式代码中，最多可以指定四个节；每个节之间用分号进行分隔，这四个节顺序定义了格式中的正数、负数、零和文本；

只指定两个节，则第一部分用于表示正数和零，第二部分用于表示负数；

只指定了一个节，那么所有数字都会使用该格式；

如果要跳过某一节，则对该节仅使用分号即可。

三、 基础字符

1.数字占位符,有三种：? # 0

2.中括号[]表示条件

3.逗号,千位分隔符,如用在代码最后，表示将数字缩小1000倍

4.分号，隔开条件，末尾可省

占位符	注释		自定义	常规	格式后
G/通用格式	以常规的数字显示，相当于”分类”列表中的”常规”选项。			47.5	47.5
#	数字占位符。只显有意义的零而不显示无意义的零。小数点后数字如大于”#”的数量，则按”#”的位数四舍五入。		#,##0	1556.122	1,556
			###.##	1810.2	1810.20
0	数字占位符。如果单元格的内容大于占位符，则显示实际数字，如果小于点位符的数量，则用0补足。		00000	1234567	1234567
				123	00123
			00.000	1234.1	1234.100
				12.2	12.200
				2.3	02.300
?	数字占位符。在小数点两边为无意义的零添加空格，以便当按固定宽度时，小数点可对齐，另外还用于对不等到长数字的分数		??.??	12.1234	12.12
			???.???		12.123
\	相当于""，都是显示输入的文本，且输入后会自动转变为双引号表达。		"人民币"#,##0,,"百万"	1234567890	人民币1,235百万
			"￥"#,##0,,/百万	1234567890	¥1,235百万
！	显示“"”。由于引号是代码常用的符号。在单元格中是无法用"""来显示出来“"”。要想显示出来，须在前加入“！”		#!"	12	12"
			#！"!"		12""
@	文本占位符，如果只使用单个@，作用是引用原始文本。要在输入数字数据之后自动添加文本，使用自定义格式为：”文本内容”@；要在输入数字数据之前自动添加文本，使用自定义格式为：@”文本内容”。@符号的位置决定了Excel输入的数字数据相对于添加文本的位置。如果使用多个@，则可以重复文本。		"有限公司"@"部"	财务	有限公司财务部
			@@@		财务财务财务
*	重复下一次字符，直到充满列宽		@*-	123	123----------------------
			**;**;**;**		*************************
_	在数字格式中创建N个字符宽的空格		____###	123	123
，	千位分隔符		#,###	12	12,000
[]	使用颜色代码或者使用条件：		[红色][<=100];[蓝色][>100]	59	59
	§[颜色N]：是调用调色板中颜色，N是0~56之间的整数，例如：1是红色、2是黑色、3是黄色。			123	123
			[颜色4]	123	123
	§[条件]：可以单元格内容判断后再设置格式。条件格式化只限于使用三个条件，其中两个条件是明确的，另个是“所有的其他”。		[>0]"正数"；[=0];"零"；"负数"	1	正数
				-6	-负数
				0	零
颜色	用指定的颜色显示字符。可有八种颜色可选：红色、黑色、黄色，绿色、白色、兰色、青色和洋红。
条件	可以单元格内容判断后再设置格式。条件格式化只限于使用三个条件，其中两个条件是明确的，另个是“所有的其他”。条件要放到方括号中。必须进行简单的比较。
空	表示隐藏		;;;	wew
四、	基本用法
	要求	设置自定义格式	输入	效果	备注
常用格式	显示四个节意义：	正数;"负数";"零";"文本"	-65	负数
	在原有的文本上加上新文本或数字	@"有限公司"	深圳	深圳有限公司
	数值的大写格式1	[DBNum1]G/通用格式"元整"	1112	一千一百一十二元整	或选择"特殊"－"中文小写数字"
	数值的大写格式2	[DBNum2]G/通用格式	1112	壹仟壹佰壹拾贰	或选择"特殊"－"中文大写数字"
	带单位符号的数值	0.00"元"	1112.2	1112.20元
	电话区号输入[包含3或4位]	[<=400]000;0000	755	0755	当<=400时,显示3位,否则4位
	仿真密码保护	**;**;**;**	1111	********************
	隐藏单元格中的所有值	;;;	12345
	重复的字符[用星号之后的字符填充]	0*-	12	12------------------
数字格式	将 1234.59 显示为 1234.6	####.#	100.2	100.2
	将 8.9 显示为 8.900	#.000	0.88	.880
	将 .631 显示为 0.6	0.#	12	12.
	12显示为12.0；4.568显示为4.57	#.0#	12	12.0
	对齐小数点	???.???	1	1.
	分数时除号对齐	# ???/???	2.25	2 1/4
	科学计数法	#.###E+00	3456	3.456E+03
	千位分隔符   若要放大显示可灵活设置。				以万为单位：0!.0,万元
	千位分隔符	#,###	12000	12,000
	千位分隔符[将数字放大1000倍]	#,	12000	12
	千位分隔符[以百万为单位]	0.0,,	12000000	12.0
	颜色  若要设置格式中某一部分的颜色，请在该部分对应位置用方括号键入下列八种颜色名称之一。 颜色代码必须为该部分的第一项。				除了这8种颜色外， 还可以调用调色板其他颜色： 如[COLOR16] 或[颜色16]
	[黑色]	[蓝色]	[洋红色]	[红色]
	[青色]	[绿色]	[白色]	[黄色]
	数值范围用颜色区分	[红色][<=100];[蓝色][>100]	99	99
日期和时间	日[d]、月[m]、年[y]、小时[h]、分钟[m]、秒展开
	将月份显示为 1–12	m	=NOW()	12
	将月份显示为 01–12	mm	=NOW()	12
	将月份显示为 Jan–Dec	mmm	=NOW()	Dec
	将月份显示为 January–December	mmmm	=NOW()	December
	将月份显示为该月份的第一个字母	mmmmm	=NOW()	D
	将日期显示为 1–31	d	=NOW()	16
	将日期显示为 01–31	dd	=NOW()	16
	将日期显示为 Sun–Sat	ddd	=NOW()	Wed
	将日期显示为 Sunday–Saturday	dddd	=NOW()	Wednesday
	将年份显示为 00–99	yy	=NOW()	99
	将年份显示为 1900–9999	yyyy	=NOW()	2020
	将小时显示为 0–23	H	=NOW()	11
	将小时显示为 00–23	hh	=NOW()	11
	将分钟显示为 0–59	m	m或mm必须紧跟在h或hh代码之后,或后面紧接ss代码 否则,将显示月而不是分
	将分钟显示为 00–59	mm
	将秒显示为 0–59	s	=NOW()	11
	将秒显示为 00–59	ss	=NOW()	11
	使小时显示类似于 4 AM	h AM/PM	=NOW()	11 AM
	使时间显示类似于 4:36 pm	h:mm AM/PM	=NOW()	11:09 AM
	使时间显示类似于 下午4时36分	上午/下午h"时"mm"分"	=NOW()	上午11时09分
	使时间显示类似于 4:36:03 P	h:mm:ss A/P	=NOW()	11:09:11 A
	按小时计算的一段时间	[h]:mm	1	24:00
	按分钟计算的一段时间	[mm]:ss	1	1440:00
	按秒计算的一段时间	[ss]	1	86400
	百分之几秒	h:mm:ss.00	=NOW()	11:09:11.28
组合与拓展应用	显示数据意义	0.00 "剩余";-0.00 "短缺"”	-112	-112.00 短缺
	正数与括号内负数对齐	#,##0_);(#,##0);-0-_)	4	4	下划线+右括号:
	页码格式	"-"??0"-"	56	- 56-	使正数和括号内的负数对齐
	将日期显示为年月日（数字）	yyyy"年"m"月"d"日"	2005/5/10	2005年5月10日
	将日期显示为年月日(全中文）	[DBNum1]yyyy"年"m"月"d"日"	2005/12/1	二○○五年十二月一日
	将月份显示为 一月～十二月	[$-804]mmmm	=NOW()	12月
	将日期显示为 "一～日"	aaa	=NOW()	三	或：[$-804]ddd
	将日期显示为 "星期一～日"	aaaa	=NOW()	星期三	或：[$-804]dddd
	添加空格	_ ￥* #,##0_ ;_ ￥* -#,##0_ ;_ ￥* "-"_ ;_ @_	89	¥89	￥在左端(空一格),输入的数字在右端(空一格)
	英寸（分数）	# ##/##!"	2.25	2 1/4"	1,以此类推，还有：0!？及0.00!* 等 2,!符号也可用\代替
	角度显示	[hh]°m!'s!"	56:12:34	56°12'34"
	角度计算	[hh]°m′s″	23:54:45	80°7′19″
	角度转换为秒	[ss]!"	23:54:45	86085"