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

本文由葡萄城技术团队原创并首发

转载请注明出处:葡萄城官网,葡萄城为开发者提供专业的开发工具、解决方案和服务,赋能开发者。

 

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

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

 

1900年2月29日 

小学生都知道在我们现在使用的公历(格里高利历)中规定:年份是4的倍数,且不是100的倍数的,为闰年;年份为100的倍数,必须是400的倍数才是闰年。 

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

 

 

那么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按照什么规则判断年份呢? 

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

 

 

在这里Excel有一个2029规则,记输入00-29中的数字会自动识别为21世纪,其余则是20世纪,如果想输入29年之后的年份,就需要输入四位数字了。当然也可以通过修改操作系统设置来改变2029这个节点,具体可参考Excel的官方说明: 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啊。 

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

 

 

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

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

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

 

 

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

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

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

 

四舍五没入 

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

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

 

 

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

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

 

 

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

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

 

 

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

 

数字不计算 

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

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

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

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

总结

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

 

发表评论

相关文章