【原】看了315晚会，我也来曝光Excel骗人事件

315晚会曝光很多问题：

泰国香米不是泰国产；

美容针可能导致毁容；
头盔却不能保安全；

直播骗老人；

。。。。。。

今天，卢子也来曝光Excel造假事件，比如通过单元格骗术，欺骗我们的眼睛。

1.1+1=3的疑惑？

两个1相加结果为3，难道Excel坏了？

其实，这就是单元格骗术的一种，叫设置单元格格式。将单元格设置为数值格式，小数位数为0。

当你设置单元格为常规，数字恢复原来的面貌，1.3+1.4=2.7。

这也就是会计在对账的时候，眼睛看见两个相同的金额相减，却显示不平。

设置单元格只是欺骗眼睛，而嵌套ROUND函数才是真正的改变。

=IF(ROUND(A2,0)-ROUND(B2,0)=0,"平","不平")

2.1=√，2=×的疑惑？

在单元格输入1回车变成√，输入2回车变成×，怎么Excel会自动修改内容？

其实，这种是自定义单元格格式，输入数字肯定比输入√×要方便快捷。选择区域，按Ctrl+1，自定义，输入下面的代码。

[=1]√;[=2]×

通过自定义，很多重复的内容都不用输入，可以提高输入内容的效率。

比如，在人员前面全部添加Excel不加班几个字。@代表所有文本，"Excel不加班"@也就是在所有文本前面添加Excel不加班这几个字。

"Excel不加班"@

同理，可以给金额后面添加单位万元。0.00就是将金额设置为2位小数。

0.00万元

3.揭秘自定义格式三分天下

什么叫三分天下呢？

其实就是三个分号;;;，将所有字符分成四种，看似简单，实际却千变万化。

正数;负数;零;文本

01 将1900/1/0显示空白

比如用VLOOKUP引用人员的离职日期，因为王五空着，就显示1900/1/0，怎么显示空白？

日期属于正数，也就是说，除了正数，其他都空着，只写分号就行。

yyyy/m/d;;;

现在王五就显示空白。

02 将1变成√，0变成×

这是前面1显示√，2显示×的拓展。学了三分天下，可以自定义更简单的代码。

1属于正数，0属于零，可以将自定义代码简化成：

√;;×;

自定义格式还能设置字体颜色，比如将√显示绿色，将×显示红色。

[绿色]√;;[红色]×;

设置完，就可以看到内容变了颜色。

4.将假的变成真的

99%的自定义用法，都可以通过TEXT转变成真实存在的内容。

另外，再分享4个TEXT的用法，等你自定义熟练了，就能运用自如。

01 发票号都是8位，从系统导出后前面的0消失，如何补齐？

常规格式发票号前面的0都会消失，只有文本格式情况下的0才不会消失。事先录入的话，可以将整列设置为文本格式。针对已经录入的，可以用TEXT函数来处理。需要多少位，第2参数就写多少个0。

=TEXT(A2,"00000000")

02 将8位数的出生日期转变成以-作为分隔符号的出生日期。

很多人首先想到的是用这样的公式，可惜出错。

这种是针对标准日期才可以这样用的。

8位数字需要用其他方法处理。

0-00-00跟e-mm-dd是两码事，别乱用。

03 标准日期格式转变成年月日星期的格式。

e代表4位的年，m代表月，d代表日，aaaa代表星期几，这些都是固定的，要牢记。

标准日期你想转变成任意一种日期格式都非常方便。比如转换成中英文星期几的简写。

3个a代表中文简写，3个d代表英文简写。

=TEXT(B2,"aaa")

=TEXT(B2,"ddd")

04 将金额大于0的显示赢，小于0的显示亏，0显示平。

对于这种问题，我们首先想到的是IF函数。

=IF(B2>0,"赢",IF(B2=0,"平","亏"))

其实也可以用TEXT函数来实现。

=TEXT(B2,"赢;亏;平")

2个;隔开，用法是：正数的显示值;负数的显示值;零的显示值。

其实最标准的用法是用3个;隔开，俗称三分天下。单元格的内容除了数值还可能是文本。加一个;让文本显示成需要显示的结果。为了方便说明用法，我们将标题的B1也算进去。

@就代表文本显示本身，类似于数值格式中的0。

愿你有孙悟空的火眼金睛，能够一眼辨别真假。

作者：卢子，清华畅销书作者，《Excel效率手册 早做完，不加班》系列丛书创始人，个人公众号：Excel不加班（ID：Excelbujiaban）