我们在excel里输入数据的时候,需要设置单元格格式,例如数值、货币、日期、文本等等,当这些格式不能满足我们个性化需求,不能提高工作效率的时候,我们就需要使用自定义格式了。
自定义格式就像是一个魔术师,能欺骗你的眼睛,改变数据的外表,但不能改变数据的本质。
所以,如果设置了自定义格式的数据被函数公式引用,或作为其参数,一定要看清数据本质,否则可能返回错误结果。
●1●
通用格式—批量添加单位
“G/通用格式”:以常规的数字显示,相当于“分类”列表中的”“常规”选项。例:自定义格式设置为:”G/通用格式”。10显示为10;10.1显示为10.1。
如果想为数值添加单位,例如添加元,自定义格式:G/通用格式元
●2●
数字占位符及应用
“#”为数字占位符。只显有意义的零而不显示无意义的零。小数点后数字数量如果大于“#”的数量,则按“#”的位数四舍五入。例:自定义格式设置为:”###.##”,那么1.1还显示1.1;234.567显示234.57。
”0”也是数字占位符。如果单元格的内容大于占位符,则显示实际数字,如果小于占位符的数量,则用0补足。例:自定义格式设置为:”000.00”, 那么657439.6显示657439.60,1.1显示001.10;234.567显示234.57。
”0”占位符的应用—快速录入编号
许多编号的组成是前面有一串相同的字母或数据,利用这一规律,我们同样可以通过自定义格式快速录入编号。
完成效果:录入1将自动显示“集团-0001”。
自定义格式:"集团-"0000
通过上面快速录入编号的学习,你知道怎样快速录入分段电话号码或银行卡号了吗?
●3●
文本占位符—自动填充重复内容
文本占位符是”@”。要在输入数据之前自动添加文本,使用自定义格式为:“文本内容” @;要在输 入数据之后自动添加文本,使用自定义格式为:@ “文本内容”。
当我们录入的数据规律一致,并且有重复的内容时,使用文本占位符”@”,可以只录入不同的内容,重复的内容会自动填充。
比如我需要录入许多的科室,可以设置自定义格式为“人社局@科”,其中的“@”会显示我们录入的内容,“@”前面和后面为统一内容。
完成效果:在单元格中录入“养老”,会自动显示“人社局养老科”;录入“生育”,则会显示“人社局生育科”。
自定义格式:人社局@科
那么,以下这两种情况,你知道怎么设置了吗?
1、只要输入部门,就会自动在部门前加上公司名称“阿里集团”
2、只要输入姓名,就会自动在姓名后加上公司名称“阿里集团”
●4●
设置仿真密码
“*”:重复下一个字符,直到充满列宽,可用于仿真密码保护。
完成效果:1、输入任意数据,如”南阳”,显示为:南阳-------------
2、输入任意数值,显示为:************
自定义格式:
1、自定义格式设置为:@*-
2、自定义格式设置为:**
●5●
万元/百万元显示
万元的自定义格式:0!.0,万
百万元的自定义格式:0.00,,百万
●6●
条件判断—判断成绩等级
可以对单元格内容判断后再设置格式。条件格式只限于使用三个条件,其中两个条件是明确的,另一个是“所有的其它”。
以判断考试成绩为例:输入分数,自动显示成绩判断
完成效果:90分以上,显示输入的分数和“优秀”两字,60分以上,显示输入的分数和“及格”两字,其它分数,显示“不及格“三字
自定义格式:[>=90]0 优秀;[>=60]0 及格;0 不及格
●7●
条件判断—判断业绩上升还是下降
完成效果:上升显示蓝色向上的箭头,下降显示红色向下的箭头。
自定义格式:[蓝色]↑0.0%;[红色]↓0.0%;0.0%
●8●
日期显示设置
按星期显示日期,这个在值班表中经常用到。将日期显示为中文星期的自定义格式:aaaa
将日期显示为英文星期的自定义格式:dddd
当然,也可以日期和星期同时显示:yyyy/m/d aaaa
数据统计中,有时需要让日期显示为月份,可以自定义格式:m月份
●9●
快速输入√和×
在许多表格中,我们会使用特殊符号√和×,但是这种特殊符号输入的时候,特别麻烦。如果我们利用自定义格式来输入,那就太轻松了。
完成效果:输入1会自动变成“√”,输入2会变成“×”。
自定义格式:[=1]"√";[=2]"×"
●10●
隐藏单元格
隐藏单元格的自定义格式为 ;;;(三个分号)
●11●
计算时间间隔
完成效果:计算两个时间之间有几小时几分几秒
自定义格式:[H]小时m分s秒
自定义单元格的应用还有不少,工作中遇到问题要善于思考和总结,你会发现Excel真的挺有意思的!