盘点Excel中的那些有趣的“bug”

叁歲伎倆 2022-10-23 12:41 293阅读 0赞

> 本文由葡萄城技术团队原创并首发
> 
> 转载请注明出处：[葡萄城官网][Link 1]，葡萄城为开发者提供专业的开发工具、解决方案和服务，赋能开发者。

Excel 1.0早在1985年正式进入市场，距今已经有36年了，虽然在推出时市面上已经有了类似软件Lotus 1-2-3，但Excel仍然凭借着其对竞品在功能上的全面超越，再加之和Windows环境的直接捆绑，直接将Excel推向了电子表格办公软件的王者的地位。

但在今天这篇文章里，我们不是为了讲Excel中那些大家已经耳熟能详的功能，让我们一起来看看，即便强大如Excel也会有的一些令人费解的计算结果吧！

## 1900年2月29日  ##

小学生都知道在我们现在使用的公历（格里高利历）中规定：年份是4的倍数，且不是100的倍数的，为闰年；年份为100的倍数，必须是400的倍数才是闰年。

![8434109479da446d0f6aecfcb0e43a72.png][]

那么1900年并不满足上述条件不是闰年，但是在Excel中却存在1900年2月29日这天，通过拖拽填充的方式，可以看到2月28日之后是29日。是因为Excel还在使用4年一润的儒略历吗？经过测试在Excel中并不存在2100年2月29日，那为什么只有1900年这一天错了？

Excel官方给出过回复，这是Excel前身Lotus 1-2-3的一个bug，但是修复问题会导致历史文件出现一天的误差，出于对老文件的兼容，同时这个Bug影响很小，Excel并未修复这个bug，1900年2月29日保留至今 。

## 38/12/31是1938还是2038  ##

进入21世纪一个百年轮回开始，日常中我们习惯只说年份的两位例如1987年简称87年，2002年简称02年。在Excel中也支持2位年份的输入，例如输入87/12/31，自动识别为1987/1231；输入20/12/31自动识别为，2020/12/31。可是在输入30/12/31却又变会1930年了，Excel按照什么规则判断年份呢？

![199b2c5be3f57d46d571a04c9087cec3.png][]

在这里Excel有一个2029规则，记输入00-29中的数字会自动识别为21世纪，其余则是20世纪，如果想输入29年之后的年份，就需要输入四位数字了。当然也可以通过修改操作系统设置来改变2029这个节点，具体可参考Excel的官方说明： [https://docs.microsoft.com/zh-cn/office/troubleshoot/excel/two-digit-year-numbers][https_docs.microsoft.com_zh-cn_office_troubleshoot_excel_two-digit-year-numbers] 。

## 两数不相等  ##

在Excel中输入=1.2-1.1=0.1，回车后计算结果为FALSE，这说明1.2减1.1的结果不是0.1，是Excel算错了吗？可是输入=1.2-1.1确实显示0.1啊。

![9844f3f6cdf784f16aae237a612f3791.png][]

这的确是Excel算错了，但是这个并不是Excel特有的错误，而是计算机2进制特性导致的浮点数计算精读误差问题。简而言之，计算机计算过程使用二进制，对于1.2-1.1的结果为2进制循环小数，在存储时会进行小数截断，导致出现误差。

如果在Excel中设置显示位数20位就会发现实际计算值为0.09999999999999990000。

![1ad6ee1f843907802fa41ff63c8bd98f.png][]

通常对于误差较小的结果，Excel可以自行修正，但是向1.2-1.1的结果Excel却没有处理。

因此，在进行数值比较的时候使用ROUND是一个好习惯，可以根据业务场景涉及到的小数范围设置参数。例如：=ROUND(1.2-1.1, 10)=0.1。

![0e913ecc1b22d8474f056cbda17adfcf.png][]

## 四舍五没入  ##

在上述例子中使用的ROUND函数规则是四舍五入，但是同样因为精度问题造成了一些计算问题 。例如=ROUND((8.92-8.71)/6,2) ,结果是0.03。期待值是0.4，由于误差计算结果并未达到0.35，并没有五入。

![03f9648230c23aec0b6bd53fa8e8a292.png][]

由于计算误差想要得到一个四舍五入的2为小数，需要进行2次ROUND，首先修正误差，然后再四舍五入保留2位小数。对于所有操作计算都做两次ROUND过于繁琐，这里可以使用Excel提供的另一个设置，使用“显示精度”，这样Excel在存储计算时，会按照单元格设置的显示小数位数来保留精度。

![31fae224cb8c0307630a5474ab33a5c9.png][]

1.  在 "文件" 菜单上，单击 "选项"，然后单击 "高级" 类别。
2.  在 "计算此工作簿时" 部分，选择所需的工作簿，然后选中 " 将精度设定为显示" 复选框。

![e892317017b013d4c252c34eb5ab0703.png][]

但是要注意，后续计算需要真实的计算结果还是格式化后的结果，不正确的使用这个功能可能会导致误差越来越大。

## 数字不计算  ##

![b94f8c4a14e601d825bd33f731abee6d.png][]

如截图，单元格中对SUM结果为0，同时单元格左上角有绿色三角提示“以文本形式存储的数字“。这个提示就是SUM结果为0的原因，这些数字实际存储格式为文本，而文本并不参加数字的运算。

这里有个概念是存储格式，当在Excel单元格中输入数字0，Excel默认会以数字格式作为存储，也就是前面说的2进制，但是如果先设置这个单元格类型为文本，则0当作文本。在Excel中数字和日期默认靠右显示，文本靠左。

这也是我们经常在输入电话号码和身份证号是存在的问题，输入身份证号后，显示成了科学计数法，这里就需要先设置单元格为文本再输入。

## 总结 ##

Excel中有很多这样并不是很符合日常行为的操作，以上只是葡萄城在研究Excel电子表格时遇到的其中一部分有趣的内容，相信大家也遇到过什么比较奇妙的计算错误，欢迎留言吐槽。

[Link 1]: https://www.grapecity.com.cn/
[8434109479da446d0f6aecfcb0e43a72.png]: /images/20221023/9d2de58952474f6fac137cad6c525903.png
[199b2c5be3f57d46d571a04c9087cec3.png]: /images/20221023/0b955f2580084036a09e6f9edd5fe9f9.png
[https_docs.microsoft.com_zh-cn_office_troubleshoot_excel_two-digit-year-numbers]: https://docs.microsoft.com/zh-cn/office/troubleshoot/excel/two-digit-year-numbers
[9844f3f6cdf784f16aae237a612f3791.png]: /images/20221023/f78152f0f3404562be335483e2329a81.png
[1ad6ee1f843907802fa41ff63c8bd98f.png]: /images/20221023/baa0a8fb76774ff1a889f379abd0ce77.png
[0e913ecc1b22d8474f056cbda17adfcf.png]: /images/20221023/a3a66cca2af74119983574540dfcdf13.png
[03f9648230c23aec0b6bd53fa8e8a292.png]: /images/20221023/b9730c16a3bc4f6fbfdc4d1a7f2b1b20.png
[31fae224cb8c0307630a5474ab33a5c9.png]: /images/20221023/9e8ade3b734a48318c35d8dfe99e2147.png
[e892317017b013d4c252c34eb5ab0703.png]: /images/20221023/6c6079c36fe34668823ffa277e5a8871.png
[b94f8c4a14e601d825bd33f731abee6d.png]: /images/20221023/20a35b83d27149318dc7dbcefd805d57.png