你想怎么显示你的数字?Excel数字基于条件的自定义格式
Excel的自定义格式也是一个很强大的存在。尤其是关于数字的自定义格式。今天我们就详细介绍一下数字自定义格式的原理。
我们以前介绍过一些自定义格式,例如如何以万为单位显示数字,还发过视频教程。但是都是只介绍了结果,没有讲原理。今天我们就详细介绍一下原理,这样在你需要的时候就可以自己定义格式了。
介绍
这个50KG的数据就会带来麻烦,因为它没法直接进行求和平均等汇总计算。所以,在分析之前你必须把它拆成两列:
平白多了一步操作不说,这个数据其实看起来并不自然,我们还是喜欢把它们放在一起的样子😉。
那么,我们就可以使用一个很简单的自定义格式:
0"KG"
就可以既保持单元格中是数值,又可以在数值后面直接显示单位,见下图
我们可以看到在显示效果上,左右两个表是一样的,但是,从编辑框里可以看到,左边单元格实际内容是数值+单位,而右边单元格实际内容就是数值。
自定义格式的原理
回归线经验:加上这些内置的自定义格式和你自己创建的自定义格式,Excel允许一个工作簿子最多支持200-250个自定义格式。具体上限是多少,主要依赖你用的是哪种语言的版本。
还需要理解的是,格式的定义分为四个Section(部分):正数,负数,0,文本,各部分之间以;隔开。当然你在定义的时候,可以不用写全4个Section,但是需要按照一定规则写:
只有一个部分
如果你的定义中只有一个部分,那么就表示所有的数值都满足这个格式。例如,自定义格式#"KG",就表示无论是正数,负数还是0,都会在后面显示一个单位KG只有两个部分
如果你的定义中只有两个部分,那么这两部分按照顺序就是:非负数;负数。例如,自定义格式是#"KG";#,那么只有输入0或者正数时才会显示单位,负数不显示单位。只有三个部分
如果自定义格式只有三个部分,按照顺序必须是:正数;负数;0。参上上面的例子,不再赘述四个部分
如果自定义格式完全定义了四个部分,按照顺序就是:正数;负数;0;文本。比如,如果自定义格式是;;;;,那么无论单元格里输入什么内容,该单元格什么都不会显示。
举例说明
[蓝色]$#,##0.00;[红色]$(#,##0.00);[颜色7]-;[洋红]"文本"
下面是实际数值与显示结果的对比:
这个自定义格式定义了4部分,分别控制正数,负数,0,和文本的显示。其余的都是很直接的,不用过多解释。
再看一个例子:
[>=1000000]#,##0,,"M";[>=1000]#,##0,"K";0
显示结果如下:
这是一个使用比较条件的例子。其中,[>=1000000]#,##0,,"M"定义了所有大于一百万的数字应该舍入到最近的百万单位显示。第二个条件[>=1000]#,##0,"K"则只有在第一个条件不满足时才来判断,因此所有那些在1000到1000000之间的都被舍入到最近的以千为单位显示。其余的那些小于1000的都显示为原数字(小数位数为0).
使用条件的例子我们以前还介绍过一个,也是一个非常有想法的应用:一个定制图表Y轴的小技巧
取得本文模板文件的方式: