你想怎么显示你的数字?Excel数字基于条件的自定义格式

Excel的自定义格式也是一个很强大的存在。尤其是关于数字的自定义格式。今天我们就详细介绍一下数字自定义格式的原理。

我们以前介绍过一些自定义格式,例如如何以万为单位显示数字,还发过视频教程。但是都是只介绍了结果,没有讲原理。今天我们就详细介绍一下原理,这样在你需要的时候就可以自己定义格式了。

介绍

自定义格式也是个被严重忽视的功能。因为不了解自定义格式,所以在Excel中会出现很多看似合理,但是会给数据带来很大麻烦。比如,

这个50KG的数据就会带来麻烦,因为它没法直接进行求和平均等汇总计算。所以,在分析之前你必须把它拆成两列:

平白多了一步操作不说,这个数据其实看起来并不自然,我们还是喜欢把它们放在一起的样子😉。

那么,我们就可以使用一个很简单的自定义格式:

0"KG"

就可以既保持单元格中是数值,又可以在数值后面直接显示单位,见下图

我们可以看到在显示效果上,左右两个表是一样的,但是,从编辑框里可以看到,左边单元格实际内容是数值+单位,而右边单元格实际内容就是数值。

这个自定义格式很简单。但是为什么要这么写呢?原理是什么?

自定义格式的原理

随便选中一个单元格,点击鼠标右键,点击“设置单元格格式”(或者使用快捷键Ctrl+1),都可以打开单元格格式对话框:
“数字”选项卡中,左边最后一个就是“自定义”,右边列出了所有内置的自定义格式,你可以在上面“类型”文本框中添加你自己的自定义格式。

回归线经验:加上这些内置的自定义格式和你自己创建的自定义格式,Excel允许一个工作簿子最多支持200-250个自定义格式。具体上限是多少,主要依赖你用的是哪种语言的版本。

在上面的对话框中,那些内置的自定义格式代码中,出现的各种符号:0,#,?等等,会让第一次接触的人感到一头雾水,其实它们就是一些代码,告诉Excel,遇到这样的情况就需要做那样的处理。下图详细介绍了大部分常见的代码符号的含义:

还需要理解的是,格式的定义分为四个Section(部分):正数,负数,0,文本,各部分之间以;隔开。当然你在定义的时候,可以不用写全4个Section,但是需要按照一定规则写:

  1. 只有一个部分
    如果你的定义中只有一个部分,那么就表示所有的数值都满足这个格式。例如,自定义格式#"KG",就表示无论是正数,负数还是0,都会在后面显示一个单位KG

  2. 只有两个部分
    如果你的定义中只有两个部分,那么这两部分按照顺序就是:非负数;负数。例如,自定义格式是#"KG";#,那么只有输入0或者正数时才会显示单位,负数不显示单位。

  3. 只有三个部分
    如果自定义格式只有三个部分,按照顺序必须是:正数;负数;0。参上上面的例子,不再赘述

  4. 四个部分
    如果自定义格式完全定义了四个部分,按照顺序就是:正数;负数;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轴的小技巧

好了,今天就分享到这里了。

取得本文模板文件的方式:

本文没有模板文件
(0)

相关推荐

  • 花了20小时整理的Excel自定义格式代码大全

    相信每一位使用Excel的读者朋友都设置过单元格格式,有时候通过巧妙设置单元格格式可以极大地简化我们的工作. 例如之前一篇关于甘特图(◀此处为传送链接)的推文就提到可以通过自定义单元格格式的方式来实现 ...

  • Excel常用函数之TEXT函数

    在说TEXT函数之前,请先回忆一下Excel自定义单元格公式的知识,如果没有印象,请先不要看这个函数,去复习一下自定义单元格格式的内容先.如果有印象,再继续往下看.今天要学习的TEXT函数的功能是将数 ...

  • 玩儿转奇妙的自定义格式

    点击上方 蓝色 文字  关注我们吧! 送人玫瑰,手有余香,请将文章分享给更多朋友 动手操作是熟练掌握EXCEL的最快捷途径! 数字格式是单元格格式中最有用的功能之一,专门用于对单元格数值进行格式化. ...

  • 图表数据标签能批量修改为想要显示的吗?

    图表数据标签能批量修改为想要显示的吗? 原始表格如图1,要生成图2图表,柱形图展示不同产品每月销量,数据标签显示不同产品在每月销量中的占比. 图 1 图 2 选中数据区域A1:E13,插入堆积柱形图, ...

  • 玩转Excel数字提取!唉~就是玩!

    案例01 | 提取单个数值 传统方法: =--MIDB(A2,SEARCHB('?',A2),LEN(A2)*2-LENB(A2)) 小白估计一时半会很难学会,这里涉及到MIDB截取函数.SEARCH ...

  • Excel数字在哪列就获取哪列标题(Vlookup妙用)

    Excel之数字在哪列就获取哪列的标题(Vlookup妙用)

  • 想要做出干净漂亮的Excel表格?简单几步这样做!

    我们从Excel模板库(文件 - 新建)中来看这个家庭库存表: 第一印象说不出哪里好,就是看上去舒服,干净漂亮! 如果把上图中的表格粘贴成数值并添加表格线,比如一下你就知道有多丑: 模板库下载的表格到 ...

  • EXCEL数字变成了小数点+E+22?

    想必大家都遇到这样的问题,就是输入的数字超过一定位数就会显示为科学计数形式,我试了下,大概超过11位就会出现这种情况,比如: 大家可以看到,数字11位我们看到还是简单数字格式,但是超过11个变成12位 ...

  • Excel技巧连载21和22:数字单位为万元的自定义格式讲解

    Excel技巧21:Excel数字后面加单位 需要在所有数字后面添加单位:元. 添加之后的效果如下所示: 选中单元格区域,设置单元格格式,输入:0"元",确定即可. 雅雯想说的是, ...

  • Excel数字+文字混合数据计算妙招!

    Excel数字+文字混合数据计算妙招!

  • Excel数字变怎么办

    Excel数字变怎么办

  • 分离Excel数字和文本,用这两种方法就够了

    今天给大家介绍两种,分离数字与文本的方法,帮你快速分离出文本当中的数字与文字. 分列 如果你的数字与文本之间,是有固定的符号来隔开的,那么就可以采用分列的方法来进行分割. 我们一起来看下面这个案例. ...