小心!Excel的这5个Bug,正在偷走你的下班时间!

你有没有遇到过这种情况，身份证号刚输进Excel，回车——数字全变成了"E+17"？

还有些数据表面看起来很规范，但是经过统计、排序和去重之后都是错误的。

不要质疑自己，这不是因为你自己的原因。

把底层的东西说出来

在Excel中如果出现超过15位的数字，15位之后全部归零。

注意的是，“全部归零”的意思就是单元格里的内容显示得很清楚，函数、条件格式和排序在后台进行运算的时候也只是按照前面的15个字来进行计算。

弄明白上面所说的这点之后，后面的五个BUG就可以全部解决了。

Bug 1：身份证号一输进去就乱码

这是最普遍的情况，在输入了18位身份证号码之后会变为科学计数法并且后面的数字都会被截断。

因为数字太长了，所以EXCEL就自己把科学计数法给弄出来了。

办法：在输入数字前把该单元格的格式改为“文本”，这样就不会丢失数字了。

Bug 2：COUNTIF计算出的结果和实际不符

商品编码为20位长的数字串，一个个看上去都不同，COUNTIF却告诉你有重复。

因为函数只读取前15个字符，后面的全部当作0来计算，所以不同的编码都被强行判定为同一种。

解决方案：添加一个辅助列，并在其中使用公式="A"&A2来给数字加上字母前缀，从而将其转化为真正的文本格式，再用COUNTIF统计，结果立刻准确。

Bug 3：条件格式把不重复的值标红了

虽然长度不同的编码不一样，但是用条件格式查找重复项的时候却一片通红。

原因就是条件格式的内部计算只能用到第15个字符。

选择其中一种办法来解决：

辅助列用前缀="A"&A2来表示，并且把文字变成实际的文字之后再做重复值的标记；

使用函数=RIGHT(A2,6)，即从A2单元格中提取出流水号部分，并给含有重复数据的那一列进行标注。

Bug 4：排序筛选纹丝不动

按长商品编码排序，点了好几次，顺序毫无变化。

因为排序只认可前面的15名，前后缀都一样，并且后面流水号也被它忽略了。

方法：依然是上面两种方式之一，即在辅助列中添加字母前缀或者利用RIGHT函数取出末位流水号，然后按辅助列排序，立刻恢复正常。

Bug 5：下拉填充编号失灵

把两个长序列，往下拖拽，想自动填充连续编号，但是没有反应。

解决方法是把编码分开来用——固定的前缀和变化中的流水号进行拼接就可以得到结果了：

="202102053645372"&TEXT(ROW(A1),"000000")

引号内的内容为一个固定的前缀，TEXT(ROW(A1),"000000")可以自动生成带有前置零的递增值流水号，向下拖动填充时会逐级增加一位。

说到底，Excel不是坏，它只是"眼神不好"——15位之后的世界，它真的看不见。

要把它变成它可以识别的形式的话，就按照上面所说的去做。

下一次遇见长编码发疯，先别砸键盘，回来翻翻这篇。