关于Excel单元格自定义格式,看这一篇教程就够了

每天一篇Excel技术图文
微信公众号:Excel星球
NO.640-什么是单元格自定义格式
作者:看见星光
微博:EXCELers / 知识星球:Excel

HI,大家好,我是星光。在「零基础学Excel系列」的上一篇教程,给大家分享了什么是数字格式化、为什么数字格式以及如何实现数字格式化……今天再把那章的坑填了,给大家讲下什么是单元格自定义格式。也就是什么是占位符?什么是条件区间?什么是自定义条件区间……

握握手,阅前温馨提示,本节内容稍显复杂,如果您是表格初学者,建议走马观花过一遍留个印象即可,不必追求全盘理解——这真的会掉头发。

1丨

什么是单元格自定义格式

在Excel中,按<Ctrl+1>组合键打开【设置单元格格式】对话框。在【数字】选项卡下单击【自定义】命令,可以看到如下图所示的界面。

在类型列表框中有很多看起来稀奇古怪的代码,它们大部分对应了各种内置的单元格格式,比如数值、会计、日期等。而所谓的自定义单元格格式,就是调用或重组这些代码,实现复杂的数字格式化需求。

我举个简单例子,批量为数据增加计量单位,格式代码如下▼

G/通用格式分

是不是很简单?

然而简单是暂时的,困难是永恒的,摊手,冷漠脸,跟我来。

2丨

什么是占位符

自定义格式主要通过占位符来实现,所谓占位符,就是一种能够代替某些类型的数据占住固定位置的特殊符号。主要有以下几种:

0 #@*?,\!ymdhsa

接下来就把菇凉们喊出来,一个一个轮流介绍给您呐。

……

第1个0

0是数字占位符,也就是说它是帮数字占位置用的,一个0就代表一个数字。如果实际数字的个数大于0的个数,则显示实际数字,否则用0补足。但如果是小数点后的数字大于0的个数,则四舍五入截取。

我举个例子,如下图所示,将D列格式设置为00。3位数516依然显示为3位数;而不足2位数的,则在数值前面以0补足位数,比如5,变成了05。

第2个#

#也是数字占位符。如果小数点后的数字大于0的个数,也会四舍五入截取。但和0不同的是,它只显有意义的零而不显示无意义的零。

举个例子。如下图所示。D列是源数据,E列自定义格式###.##,F列自定义格式000.00。对比D3/E3/F3的数据可以看出三者之间的明显区别。

第3个@

@是文本占位符——嘿!别问我文本是什么,忘记了就出门右拐,菜单->教程->零基础->什么是数据类型。

一个@代表文本值自身,不是代表一个字符。比如需要将下图C列的文本值重复显示一次,可以使用格式代码@@

第4个,

半角逗号是千分位分隔符,代码#,###,可以将12500显示为12,500。

第5个\和!

\和!都是强制转义符,可以将特殊含义的字符转义为文本。比如需要将数值以万元为单位显示,代码0!.0,万元,或者0\.0,万元,都可以。代码中0,表示千分位,0!.表示在千分位前强制添加一个小数点,并使用一个0占据万位。

第6个*

星号*可以重复下一个字符,直至列宽填满。比如自定义格式**,则输入任何正数或零,均会显示为重复的星号,可用于模拟密码保护。

第7个?
问号?也是数字占位符,但相比0和#用处比较少。它可以在小数点两边为无意义的零添加空格。使不等长的数据按小数点对齐。

下图中E列使用了代码???.??,使数据按小数点为中心位置对齐。

8个颜色占位符

颜色占位符中文表达式有八种颜色可选:红色、黑色、黄色、绿色、白色、蓝色、青色和洋红。

但可以使用[颜色1],[颜色2]……这样的表达方式,表达56种不同的颜色。不同数字代表的颜色如下图所示:

之前给大家分享的短视频,制作带涨跌箭头的销售数据,就是用的这个技巧▼

,时长

00:37

9个日期

在英文中Y是year的首字母,M是month的首字母,D是day的首字母;因此Y代表年,M代表月,D代表日。一般情况下不区分大小写。

YYYY是4位年,比如1998,YY是2位,比如98。全年也可以用小写字母e表示。

MM是2位月,比如02,M是1位月,比如2。

DD是2位日,比如02,D是1位日,比如2。

aaaa可以将日期值显示为中文全称星期几,比如星期三等。

10个时间

h代表小时,s代表秒;hh:mm:ss表示小时:分:秒。此外[h]、[m]、[s]分别表示累加的小时、分钟、秒。

如下图所示的F列,使用代码[m],表示将小时折算为分钟后的总值。

除此之外还有数字大写等代码,在设置单元格格式对话框中,先点击特殊命令下的选项,再点击自定义格式,即可看到相关代码。

3丨

什么是条件区间

聊完了什么是占位符,最后再给大家聊一下条件区间。

在前面什么是数据类型一课里给大家讲过,Excel的数据类型可以划分为数值、文本、逻辑值和错误值——有朋友说还有日期,严格来说,日期可以划分到数值里去。

错误值并不常见,遇见它就是个错误。逻辑值也不常见,它就是个二极管傻黑青年,非真即假,不对就错。数值和文本值最常见。数值又可以分为正数、负数、零。因此,自定义格式被划分为了4个条件区间,不同区间以分号间隔:

正数;负数;零;文本
或者说:
[>0];[<0];[=0];文本
我举个例子,如下图所示,A列的数据存在正数、负数、零、文本、逻辑值和日期。设置自定义格式代码正数;负数;零;文本。代码的意思是正数返回'正数',负数返回'负数',零返回'零',文本返回'文本'.

结果如B列所示(注意:逻辑值被划分到了文本区间)

……

有朋友会问,这4个区间都是必须有的吗?当然不是,实际应用中,我干杯你随意,你可以使用3个,也可以使用2,甚至是1个,但不同个数的区间含义并不相同。

如果使用三个条件区段,其含义为:

[>0];[<0];[=0]
也就是删掉了文本区间,分别代表正数;负数;零。如果出现文本值,则未被特殊处理,依然返回原值。
……

如果使用两个区间,则含义为▼

[>=0];[<0]
注意第1个区间是大于等于0,而不只是正数。正数和零被划分到了第1个区间,负数在负数区间,其它不在区间内,返回原值。

……

如果只使用一个区间,其含义为:

[数值]

这包含了正负数和零。如下图所示的E列数据,数值部分均被改变为字符串正数,其中负数会改变为-正数,其它不在区间内,返回原值。

……

4丨

什么是自定义条件区间

除了自带的条件区间外,自定义格式还支持自定义条件区间。新手期比较少用,简单了解一下就好。

自定义条件的四区间可以表示为:

[条件1];[条件2];[不满足条件的其他部分];[文本]
自定义条件的三区段可以表示为:
[条件1];[条件2];[不满足条件的其他部分]
自定义条件的两区段可以表示为:
[条件];[不满足条件]

我举个简单的小栗子。

如下图所示,C列是成绩,需要将大于等于80分的成绩显示为良好;大于等于60分的显示为及格;其余不满足条件的显示不及格;但文本依然显示文本值自身。

代码如下▼

[>=80]良好;[>=60]及格;不及格

结果如下图所示。

……

没了,爱我Excel,怕了吗?!

上千篇原创教程

随学随用 随用随学👆

需要系统学习Excel,却找不到优质教程?学习Excel的过程中遇到疑难问题,却找不到人及时作出解答?加入我的付费社群,和微软最有价值专家(MVP)全面精进Excel,学习+答疑都不再是问题……

👇👇👇

加入我的Excel会员,全面学习Excel
透视表 函数 图表 VBA PQ想学啥学啥
👀

Excel星球

建议常用Excel的职场人关注,海量教程随学随用,随用随查。 主创:看见星光,微软全球最有价值专家、Excel图书作者、培训师。 内容:每日四文,一篇函数教程、一篇VBA教程、一个短视频小技巧、一篇杂文。
1427篇原创内容
公众号

👇本文由公众号“Excel星球”首发。

(0)

相关推荐