本帖最后由 胡剑0227 于 2012-2-15 15:35 编辑
MATCH的执行精度问题年前带数组班的时候就发现了，还是班长hustnzj在讨论一个问题的发现了，当时想写一下，后来由于某些事情就溜掉了。今天偶尔用MATCH做做题，又撞上了，这下不让它溜走了，呵呵。
如上图所示，B6单元格显示为【232】，公式为 【=MOD("02:27"-"22:35",1)*1440】，使用F9求值显示结果为【232】，并且直接使用比较运算符【=】将B6单元格和纯数值232的C17单元格进行比较依然返回TURE，如下图C19单元格公式所示。但是，对，此处如你所料是一个转折，使用MATCH函数将B6单元格和序数数组1-1440进行匹配却返回【231】，而不是期望中的【232】，如上图B9单元格所示，其对的具体公式为【=MATCH(B6,ROW($1:$1440),1)】。 为什么会这样呢？有限精度么！有限精度带来的误差引起的。 没错，但毕竟稍微笼统了一些，不便于进一步的了解进而找出相对应的策略来处理这个问题。Excel的精度是15位有效数字，在论坛泡过一阵的人都了解这点，从F9显示232、用比较运算符【=】判决等于232可以得出在15位精度下B6单元格确实等于232。但MATCH函数的比对结果显示，在MATCH函数内部执行过程中B6单元格并没有被当成232，而是十分接近232且小于232的一个数值，即MATCH函数内部可以区分出B6单元格与232之间的细微差别，也即MATCH函数内部的精度是更高的。 于是，问题就这样出现了：MATCH函数内部执行代码的高精度，使得15位数字精度造成的误差不能被忽略，最终导致逻辑和预想不符合。这就好比用极高分辨率的摄像头近距离拍明星的脸，这个时候就能暴露出那些原本近乎完美的脸原来也是那般坑坑洼洼，要解决这个问题就是像素不能那么高，这样就能忽略那些小毛刺了。
解决方案1：
于是，参考【降低摄像机像素】的思路，我们也可以人为加入舍入函数，从效果上达到降低MATCH函数执行精度的目的。模型，高分辨率的摄像头直接拍明星的脸自然可以捕捉到那些坑坑洼洼，但如果事先用低分辨率摄取图像屏蔽那些坑坑洼洼，即像素中不存在这些信息了，那么后续用高分辨率摄像头再拍经低分辨率摄像头摄像的图片时就再也不可能捕捉到那些坑坑洼洼了。这就是具体的原理，在本题中就是对B6单元格进行舍入处理再作为MATCH函数的第一参数 【=MATCH(INT(B6),ROW($1:$1440),1)】，如下图所示，其中INT函数就起到了低像素摄像机的功能。
解决方案2：
记得做物理电学实验时学过一个替换法，该方法实现比较简单，但思路别具一格。要测试一用电器的电阻，虽然有经典的公式=电压/电流，但是要测电压和电流就会引入额外的误差，替换法的思路就是用微调电阻替换用电器，微调电阻使得高敏度电流、电压表的读数和用电器一致。即只关心电路状态达到一致，并不真正使用那些数值，这样对于用电器和微调电阻来说所有的误差都是等价的，额外的误差都相互抵消了...
上图，即是演示了一把误差和相互抵消的例子，其中查找值 和 查找序列 的产生机制是完全一样的，因此最后依然可以做到完全匹配，此时C55单元格即使使用精确匹配依然可以返回正确的值。
指导思路：
1、通过舍入函数在效果上降低查找类函数的执行精度，使得有限精度带来的计算误差不影响最终的结果；
2、找值和查找序列使用相同的机制产生，让误差相互抵消，使得最后的结果符合预期逻辑；
===================================背景知识===============================================
1.日期和时间的实质是序列值，数字1对应一天，数字1的24等分对应一天中的一个小时，依次类推1分钟就对应1的1440等分...
2.符合日期和时间格式的文本参与必须由数值参与的运算时，可以被Excel智能识别出的对应的序列值。如公式【=MOD("02:27"-"22:35",1)*1440】中参与减法运算的两个时间。
 该贴已经同步到 胡剑0227的微博
龙年日历下载 | 
沪公网安备 31011702000001号 沪ICP备11019229号-2
发表于 2012-2-12 11:29
发表于 2012-2-12 11:32
