下载掌阅APP，畅读海量书库
立即打开
自定义数字格式是一个非常强大的工具，不仅可以对数字格式进行设置，更重要的是可以标识一些重要的数据。
例如，在预算分析和同比分析中，自定义数字格式尤为重要，可以将超预算的差异数字显示为蓝色字体并添加上箭头标识符号，将预算内的差异数显示为红色字体并添加下箭头标识符号，这样就可以让人一目了然看出哪些项目在预算内，哪些项目超预算了。
自定义数字格式在“设置单元格格式”对话框中进行设置，如图3-21所示，在“分类”列表中选择“自定义”，然后在“类型”输入框中输入自定义格式代码。
图3-21 自定义数字格式
需要注意的是，自定义数字格式，从字面上看，就是对数字设置自定义格式，因此单元格中的数据必须是数字，包括日期和时间，因为日期和时间本质也是数字。
本节使用的案例素材是“案例3-3.xlsx”。
在一个数字单元格里，数字有3种：正数、负数、零。我们可以对这3种数字分别设置格式，因此自定义数字格式的代码结构如下：
正数;负数;零
自定义数字格式代码中，每部分代码之间用分号隔开。
每种数字都可以设置各种格式，例如会计格式、千分位符样式、整数、百分数等；还可以在数字前面或后面添加标识字符，例如上箭头、下箭头、货币符号、文字说明等。
图3-22所示就是一个示例，使用自定义数字格式对正数、负数和零进行不同格式设置，让表格更加清晰。格式代码如下（注意千分位符的格式代码是#,##）。
#,##0.00;-#,##0.00;-
图3-22 自定义数字格式简单示例效果
在数据分析报告中，例如预算分析报告，实际数和预算数的差异是有正有负的，如果这个差异数是同一个格式显示，就无法直观看出各个项目的预算执行情况。
可以通过自定义数字格式的方法，将差异数依据正数、负数以及零，分别设置为不同的颜色，并添加不同的标识符。
图3-23所示就是一个示例，这里对差异数的显示要求如下：
● 正数显示为蓝色字体，千分位数字，不保留小数，数字前面加上三角标记“▲”；
● 负数显示为红色字体，千分位数字，不保留小数，数字前面加下三角标记“▼”，但不显示负号；
● 数字0显示为横杠“-”。
那么，自定义格式代码如下：
▲[ 蓝色]#,##0; ▼[ 红色]#,##0;-
图3-23 将正数和负数分别设置为不同颜色并添加不同标识符号
在格式代码中，颜色名称要用方括号括起来。
对于中文版本的Excel，可以设置的颜色名称是：黑色、绿色、白色、蓝色、黄色、红色等。
如果使用的是英文版本，则需要把方括号中的颜色汉字名称改为颜色英文名称，比如“蓝色”改为“Blue”，“红色”改为“Red”。
对于负数而言，如果想把负数显示为正数，就省略负数前面的负号（-）。
可以根据数字大小来设置不同的格式，此时，在格式代码中，条件要写在方括号中，使用比较运算符进行判断，比较运算符有：等于（=）、大于（>）、大于或等于（>=）、小于（<）、小于或等于（<=）和不等于（<>）。
例如，在图3-24左侧的预算分析表中，预算执行率是一个百分数，要求其显示效果如下：
● 如果执行率小于100%，表示未完成，要显示为红色字体，百分比数字显示为1位小数的百分比格式；
● 如果执行率大于或等于100%，表示超额完成或按预算完成，要显示为蓝色字体，百分比数字显示为1位小数的百分比格式，但不显示负号。
设置后的显示效果如图3-24右侧所示，格式代码如下：
[蓝色]▲[>=1]0.0%;[红色]▼[<1]0.0%
图3-24 预算执行率根据数字大小分别设置不同格式
如果表格中的数字很大，直接显示会使表格阅读性很差，此时便可以将数字缩小位数显示，例如金额（元）缩小1000倍显示（以千元显示），缩小1万倍显示（以万元显示），缩小百万倍显示（以百万元显示）等，这就是缩小位数显示数字。
表3-1所示是数字缩小位数显示常见格式代码。
表3-1 数字缩小位数显示的常见格式代码及显示效果
例如，图3-25左侧是原始数据，数据较大，右侧是以万元为单位显示的效果，表格很清晰。
预算数和实际数的格式代码：
0!,0,
差异数的格式代码如下：
▲[蓝色]0!.0,;▼[红色]0!.0,;-
图3-25 以万元为单位显示数字
日期是一种特殊数字，2024年2月5日就是数字45327，因此，我们也可以对日期设置各种格式，从而让一个枯燥的日期数字显示出我们想要的信息，例如显示中文日期、显示英文日期、显示月份名称、显示星期名称等。
图3-26所示是对日期格式的各种设置及显示效果。
图3-26 日期及其自定义格式显示效果
图3-27所示是一个动态表头的考勤表，具体设计步骤请观看录制的小视频。
图3-27 动态表头的考勤表
本案例的技巧是设计两行日期，第一行日期显示为日数字，第二行日期显示为中文星期简称，这样就是一个动态表头了。
同时，工作表第一行是输入某个月的第一天日期，也可以设置自定义格式，显示“****年*月 考勤表”字样。
在这个表格中，还可以使用条件格式来动态标识工作日和双休日为两种不同颜色，这样的考勤表看起来就更清晰了。关于条件格式，3.4节将进行详细介绍。
输入单元格的时间，默认情况下就是以“h:m:s”的自定义格式显示的，但是，这种默认情况下的自定义显示，在某些表格中会引起误解，造成显示不正确。
例如，要往单元格输入“39小时17分45秒”，如果输入“39:17:45”，那么就会显示为“1900-1-1 15:17:45”，如图3-28所示。
图3-28 时间显示示例
之所以造成这种错误显示，是因为Excel处理时间存在默认规则。37小时已经超过了24小时，会自动进位到1天，就会显示带日期的时间格式。如果不要让超过24小时的时间进位到天，可以使用下面的自定义格式代码：
[h]:m:s
用方括号将小时代码“h”括起来，即“[h]”，就是不允许超过24小时的时间进位到天。
如果要将“39小时17分45秒”显示为总共多少分钟（满60分钟的不允许进位到小时，不够一分钟的秒数扔掉），这个显示效果的格式代码为“[m]”，显示效果为2357，也就是总共2357分钟。
总之，不让谁进位，就把谁用方括号括起来。
1．自定义数字格式的代码结构是什么样的？书写时要注意哪些事项？
2．有一个同比分析表，要求将去年和今年的实际数据缩小1万倍显示，同比增长率（注意同比增长率数字有正有负）显示要求为：正增长率数字显示蓝色字体，两位小数的百分比，数字前面有上箭头；负增长率数字显示红色字体，两位小数的百分比，数字前面有下箭头。那么，去年和今年的实际数据以及同比增长率的自定义数字格式代码怎么编写？请用模拟数据进行设置并检查效果。
3．将日期“2024-2-5”显示为“2024.02.05”的自定义格式代码是什么？
4．将日期“2024-2-5”显示为“2024年2月5日 星期一”的自定义格式代码是什么？
5．将时间“27小时34分钟57秒”显示为99297秒的自定义格式代码是什么？