小心！Excel中这5个Bug，可能会让你白白加班！

点击蓝字【秋叶 Excel】👆

发送【工具】

免费领 3 大 Excel 高效工具！

本文作者：拉登 Dony

本文编辑：雅梨子、竺兰

大家好，我是拉小登，一个爱梳头的 Excel 老师。

今日为大家分享的内容是长数字常见的 5 个 Bug。

新手在学习 Excel 的时候非常容易蒙圈：

一方面，自己对 Excel 不熟悉，不知道如何下手。
另一方面，Excel 中有很多奇葩的 bug，看似正确的内容，结果确认出人意料的错了，非常难发现。

比如，你肯定遇到过身份证号码乱码的问题吧？

输入身份证号码录入的时候都正常，一敲就变成了乱码。

实际上，这个问题的背后，还有很多的隐患，非常容易出现的错误。

今天的文章一定要看完，记住这几个常见的错误，以及解决方法，否则下次遇到这种问题，百度都不知道该搜什么？

数字显示不全

首先是数字不全，是错误频率最高的一个问题。

原因是因为数字太长了，Excel 会认为，数字太长了阅读起来不方便，然后就转成了科学计数法。

下面乱码的意思是，1.23 乘以 10 的 17 次方。

这其实是没有意义的，身份证号码是一串文本，不需要做任何的计算。

所以在填写身份证号码之前，先设置单元格格式为【文本】，然后再输入身份证号码，就能正常的显示了。

数据统计错误

设置【文本】格式把长数字显示完整，虽然看上去是没有什么问题了，但是只要用数字做计算，立马就会出问题。

▋COUNTIF 统计错误

下面的表格中，【商品编码】是一个 20 位的长数字。

现在要根据【商品编码】统计每个商品的销售次数，很明显统计结果是不对的。

公式如下：

=COUNTIF($A$2:$A$11,D2)

【商品编码】的前 15 位是固定的前缀，后 5 位数用来区分不同商品。

虽然我们看到的编码是完整的，但是在函数公式中，依然遵循了【长数字 】的处理规则。

即，只保留前 15 位的数字，后面的数字被舍掉，变成了 0。

所以上面的 3 个商品编码，在公式计算过程中，可以理解成和下面的数据是一样的。

所以，COUNTIF 函数的统计结果，自然就是错误的。

▋解决方法

解决的方法，就是通过添加「前缀」的形式，强制把数字转换成文本。

下面的表格中，通过辅助列给【商品名称】都添加了一个字母「A」，这样数字就变成了的「真文本」。

这个时候，再去用 COUNTIF 函数计数，结果就完全正确了。

❶ 辅助列的公式如下：

="A"&A2

❷ COUNTIF 的公式如下：

=COUNTIF($C$2:$C$11,"A"&E2)

条件格式错误

如果不知道长数字在函数公式中的 Bug，遇到下面这个问题，只能乖乖的去加班了。

而且去百度搜索都不知道搜什么？自己干着急！

▋重复值标记错误

表格中有重复的【商品编码】，现在使用【条件格式】的【重复值】功能标记重复值，结果所有的编码都标记出来了。

条件格式的底层逻辑，也是需要对数字进行计算和判断，原理和函数公式是一样的。

按照前面说的，虽然长数字【商品编码】完整的显示出来了。

但是函数公式在做计算的时候，只能识别前 15 位数字，而前 15 又碰巧都是一样的。

所以重复值标记的时候，就出现了错误。

▋解决方法 1

第 1 种方法，就是前面讲过的，用公式给长数字添加一个字母「A」的前缀，强制转换为文本。

公式如下：

="A"&A2

然后再用条件格式，就可以正常的标记【重复值】了。

▋解决方法 2

第 2 种方法，使用 RIGHT 函数，把编码右边的唯一值提取出来，来做函数公式计算。

比如案例中编码后 6 位是不同的，所以用 Right 函数提取右边 6 位数字。

公式如下：

=RIGHT(A2,6)

对提取结果标记【重复值】也可以正常的计算。

排序错误

所以涉及到计算和判断的功能，都会在「长数字」上【挖坑】，排序筛选也不例外。

▋排序错误

下面的表格中，要根据【商品编码】进行排序，排序前后没有变化。

你应该能猜出来是长数字的问题了吧？

排序只识别到前 15 位数字，而这 15 位都是一样的，所以排序自然没有变化。

▋解决方法

解决方法和前面是一样的：

要么加字字母转换成文本；要么提取唯一的数字。

然后根据辅助列的数据进行排序，就可以了。

序号填充错误

最奇葩的错误，是这个序号填充。

就填充个序号，为什么也有要插一腿「长数字」的 Bug？

▋序号填充错误

写好两个【商品编码】，想要通过拖拽填充的方式，获得 10 个【商品编码】，结果失败了。

不用我说，你也知道原因在哪里了吧？

▋解决方法

把长数字拆分成两个部分：

❶ 不变的部分。即前面的 15 位数字。❷ 变化的部分。后面的流水码。

然后用文本连接符，把两个部分合并到一起，填充公式就可以了。

公式如下：

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

▲ 左右滑动查看

公式也是分为两个部分：

总结一下

好了，稍微总结一下这一小节的知识点。

❶ 数字显示乱码。

长数字默认会变成科学计数法，单元格格式设置为【文本】，再输入数字就可以了。

❷ 长数字计算的 Bug。

在函数公式当中，长数字即便显示完整，也是会被默认转换成数字进行计算，同时前只计算前 15 位，后面的数字会被忽略掉。

如果你的公式是基于 15 位后面的数字计算的，大概率会出现错误。

❸ 长数字错误解决方法。

两种方法，解决长数字计算错误：

① 加字母前缀，转成纯文本。

② 拆分数字，用公式把前缀和流水码合并起来计算。

今日内容分享就到这里了，如果这篇文章对你有帮助，请帮忙「点赞」「在看」「转发」。

这对我很重要，能给我更多动力，持续分享优质的内容~

学好 Excel 不加班，学好 PPT 年终总结不发愁！

为感谢同学们对秋叶 Excel 的陪伴和支持，我们专门为大家准备了 《3 天 PPT 集训营》  。

公众号粉丝仅需 1 元就可以秒杀哦~

只用三天，每天只需要花费 10-20 分钟，就能轻松学习 PPT。

扫码下方二维码报名后添加班班即可参与~

报名后你将获得 3 天 PPT 社群模式学习，各类福利资料干货分享发放，助力大家提升办公效率哦~

《秋叶 PPT3 天集训营》

课程原价 99 元

当下限时 1 元秒杀！！

仅需 3 天，实操练习+名师带学

学习 PPT 高效技巧，事半功倍早下班！

报名即送【100 页秋叶精选素材手册】

完成学习打卡，更有超多福利奖励获取！

快来，学完之后，告别加班，享受生活~

优惠仅限前 100 名，限时秒杀！

赶紧扫码抢课吧！👇👇👇

欢迎加入秋叶 Excel 专属读者群~和群友一起互相交流学习 Excel，互帮互助。

现在进群，还会掉落各种学习资源，助力大家提升办公效率

↓↓↓

解你所惑

不要错过 !

👇👇👇

动动手指

安利给朋友~

👇👇👇