昨天在帮一位坛友解答中学体育成绩评分插值计算问题时,因为时间进制问题写了一个很长的公式。简单用模拟表介绍一下,A列显示的时间实际上是输入D列的数值 通过自定义格式 0"′"00!" 实现的。如果不转换就插值计算 很显然4’56-4’59评分会不公正。因此考虑通过 ROUND(INT(A2/100)+MOD(A2,100)/60,8) 对A列进行转换,让TREND的[known_x's]用E列的对应值。因为涉及跨表,性别判断,偏移取值,然后全部在内存数组中插值计算,公式搞得有点长。如果前面再有小时或者秒后面再有数值,数值转换部分公式长度还要加倍,各位坛友有没有比较简单的转换方法让类似的时间转换变得简洁一些。
附上解答中的公式,请指正
=IFERROR(ROUND(TREND(OFFSET(评分标准!$A$4,MATCH(E4,OFFSET(评分标准!$B$4:$B$28,,D4="女"))-1,0,2),INT(OFFSET(OFFSET(评分标准!$B$4,,D4="女"),MATCH(E4,OFFSET(评分标准!$B$4:$B$28,,D4="女"))-1,0,2)/100)+ROUND(MOD(OFFSET(OFFSET(评分标准!$B$4,,D4="女"),MATCH(E4,OFFSET(评分标准!$B$4:$B$28,,D4="女"))-1,0,2),100)/60,8),ROUND(INT(E4/100)+MOD(E4,100)/60,10)),1),120)*(E4>0)
原贴 http://club.excelhome.net/thread-1539605-1-1.html
|