时间:早晨。
地点:长丰刑警支队会议室。
人物:前队长关宏峰(也可能是通缉犯关宏宇)。现队长:周巡。警队新人:周舒彤。法医:高亚楠。
……咔咔咔咔咔咔咔……悬疑判案开始了…………
周舒彤介绍案情:周队,我们凌晨接到报案,有人在Excel表格里计算出了看见星光的成长值,上面要求我们在一个小时之内查出来……凶……凶手是用什么函数公式算出看见星光的成长值的?……
周巡:等会等会,你说什么?Excel?这事也归我们管?我们是刑警队,你有没有搞错啊?
周舒彤:周队,这是市局要求的。
周巡:……我去,看见星光还和市局有关系,流弊啊……
关宏峰(宇):小周你继续说吧。
周舒彤用电脑打开表格:关老师,情况就是像表格所演示的,有人在F2单元格计算出了看见星光的成长值……
周巡:……等会,你刚刚说上面要求我们查出凶手是用什么函数公式算出数据的?弱不弱智啊?鼠标点下F2单元格,看看编辑栏不就知道了吗?
高亚楠插话说:周队,目前还不知道凶手用了什么技术手段,屏蔽了F2单元格的所有信息,编辑栏查不到任何线索。
周巡:……老关这事你怎么看?
关宏峰(宇):用FORMULATEXT函数读一下F2单元格,看看能读出计算公式吗?
高亚楠:试过了。F2单元格被技术手段屏蔽了,FORMULATEXT返回错误值。我们只能确定凶手使用了函数公式,但具体是哪条函数公式不清楚。
周舒彤:啊,那可怎么办呀?
周巡看着关宏峰(宇),关宏峰(宇)抬起一只手揉着下巴沉思。
关宏峰(宇):这是一个单条件查询的表格问题,如果凶手是用函数公式进行计算的……一般会有5个常用的计算套路。
1,=VLOOKUP(E2,A:C,3,0)
2,=INDEX(C:C,MATCH(E2,A:A,))
3,=OFFSET(C:C,MATCH(E2,A:A,),)
4,=LOOKUP(1,0/(A1:A10=E2),C1:C10)
5,=SUMIF(A:A,E2,C:C)
关宏峰(宇):这5个公式各有特点,很好区分,我们稍加测试,应该不难得出答案。
周舒彤:关老师,它们都有什么特点啊?
关宏峰(宇):小周,你把E2单元格的值,修改为*星*,看看F2单元格的计算结果有没有发生改变。
周舒彤:关老师,F2没有发生变化。
关宏峰(宇):这说明F2单元格的计算公式,不是=LOOKUP(1,0/(A1:A10=E2),C1:C10)。这条公式是用等号判断A列的值是否等于E2,等号不支持使用通配符。如果F2单元格是这条LOOKUP公式,它应该返回错误值。另外其它4条函数支持通配符运算。
周巡看着关宏峰(宇)若有所思。
周舒彤:关老师,那会不会是SUMIF函数呢?
关宏峰(宇):小周,你再把A3和E2的值都改为看见星光,看看F2计算结果有没有变化。
周舒彤:关老师,F2的值还是没有变化。看来也不是SUMIF,SUMIF是条件求和函数,如果符合条件的有多个,它会把他们求和,SUMIF的计算结果应该是C2 C3=73。
关宏峰(宇):小周分析的没错。
周巡伸懒腰:行啊,老关,没看出来啊,你俩表格也玩的这么遛。
周舒彤:关老师,剩下3个选项怎么排除啊?
关宏峰(宇):小周,你把A2单元格的值改为0.204,把E2单元格的值改为=0.203 0.001,看下F2计算结果有没有变化。
周舒彤:关老师,F2的值还是没有变化。可是关老师,这能说明什么吗?
周舒彤她一脸茫然的看着关宏峰(宇)。
关宏峰(宇)抬起一只手揉着下巴:这说明F2单元格的公式,既不是INDEX(C:C,MATCH(E2,A:A,)),也不是OFFSET(C:C,MATCH(E2,A:A,),)
周舒彤:啊?
大家都看着关宏峰(宇)。
关宏峰(宇)解释:不管是INDEX函数还是OFFSET函数,都是用MATCH函数去匹配查询值在查询范围中的位置。MATCH函数有一个不为人知的特点,它对数据的计算精度和一般函数不一样,如果使用了MATCH函数,这里应该返回错误值。
周舒彤:关老师,我听不懂。
周巡:你这孩子,大学都怎么读的?这都还听不明白?……老关,你给她好好讲讲,我也没听明白。
关宏峰(宇):……计算机是二进制,咱们人类用的是十进制。Excel在对数值进行运算的时候,不管是加减乘除还是乘幂,都需要先将十进制转换为二进制,计算完了,再转换成十进制呈现出来……换来换去,就产生了浮点运算。
周舒彤:可是,关老师,这和MATCH函数有什么关系?
关宏峰(宇):小周,0.204等不等于0.203 0.001?
周舒彤:等于啊。=0.204=(0.203 0.001),这条公式的计算结果也为TRUE。
关宏峰(宇):这只能说明等号判断它俩是相等的,当然,咱们人类的数学运算上,它们也是相等的。这么说吧,Excel对0.203 0.001计算中产生了浮点,为什么产生浮点你已经了解了。
关宏峰(宇)继续说:……不同函数对浮点的计算精度不同。等号和VLOOKUP等函数,只比对数值的15位精度,它们认为0.204和0.203 0.001是相等的,但MATCH函数的计算精度要高于等号,它就认为两者是不相等的,所以它的计算结果应该是错误值。和MATCH函数相同情况的还有DELTA函数,=DELTA(0.204,0.203 0.001),这条公式返回结果也为0,意思是两个值不相等。
周舒彤:关老师,那我以前经常用MATCH函数算数据,岂不是错了很多?
周巡:……Excel真是坑爹啊。不过这案子总算结了,看来凶手就是用了VLOOKUP函数。大家散了吧,我早饭还没着落,老关……
关宏峰(宇):凶手也有可能不是使用的VLOOKUP函数。
全场静默。
周巡:不是,老关,你什么意思?刚刚不是说只有5种常用的函数公式吗?4种排除了,不就还剩下VLOOKUP了?
关宏峰(宇):那是一般情况下,但如果凶手是个二货……小周,你把A2的值改为二货,看看F2单元格的值有没有发生变化?
周舒彤:关老师,F2的值没有发生变化。关老师,这不对啊,如果凶手使用了VLOOKUP函数,找不到查询结果,F2应该返回错误值啊。
关宏峰(宇)起身走到电脑前,将表格C2的值删除,他看着F2的值随之变成了空白,嘴角露出痞子的微笑:
没有什么不对,真相只有一个,那就是凶手是个二货,他F2单元格使用的公式是………………………………………………
…………
……
…………………………=C2。
联系客服