舍入那点事

流浪铁匠

【我帖子文字多且需要重新编辑添加附图,总会部分被审核ing,心急的请直接看附件】
excel的舍入函数,从舍入能力来说,主要分为4类

1楼上图


普通级:熟悉红色的在数值舍入类问题中最常见的8+1个函数函数
入门级:以上很多函数的2参是可以为负数的
进阶级:注意各函数的参数间符号不一致时的结果差异,与数值为负数时各组函数的结果的异同
骨灰级:round的2参不是取整的,其舍入点为0.99999976146501这个常见特殊值且2参类型疑似长整型(上限值明显)【骨灰级详细内容请见本文最后部分,至少要清楚那2个ceiling的衍生隐藏函数……】

流浪铁匠

二)舍入函数分类
以上舍入函数主要分为3类
1,根据指定位数的舍入
2,根据指定倍数的舍入或取余
3,文本型格式处理
另外很多函数本身具有精确(/大致)取整能力,不在此次介绍内


相同点
1),参数均为2个,1参为数值,2参为舍入位数
2),2参数支持负数,为负数时圆整到小数点左边
3)1参忽略数字格式(文本型数字和逻辑值可直接被计算)
4)这4个函数在正数与负数时舍入规律无差异

不同点:
1,舍入规则,这条没什么可说的
2,trunc的2参可缺省,另外3个不行,至少要保留逗号
3,round的2参临界点为之前注意到的0.99999976146501,而其它几个函数的2参默认取整
4,round是这批函数里唯一有2参上限的函数(后文有介绍)



2,指定倍数的舍入函数



ceiling与floor有衍生函数,后面会详细对比,所以这里不再列举其衍生函数               
               
相同点        
1,参数均为2个,1参为数值,2参为舍入倍数        
2,1参忽略格式差异(文本型数字和逻辑值可直接被计算)        
3,1参为正数与负数时舍入规则存在差异        
               
不同点        
1,mround的2个参数的符号必须一致,而ceiling/floor仅不支持1参为正数时2参为负数的情况,mod的2个参数无正负号限制        
2,ceiling与floor的舍入规则在1参为正数与负数时是不一样的(这点在单独这2个函数的衍生函数对比时有详细介绍)        



3,取整型




这几个函数没什么好说的                        
唯一需要注意的还是负数时的舍入规则                        
int为向下取整,无论正负                        
odd/even正数时向上舍入,负数时向下舍入,即数值都是沿绝对值增大的方向向上舍入                        
而quotient对负数的处理能力类似trunc而不是int,但quotient不能缺省2参且不能为0        

sbdk007

铁匠出品，必是精品

流浪铁匠

(由于插入图片需要重新编辑,目前帖子都需要审核会有一定等待时间，这是我内容的3楼)

三)舍入函数是可以相互替代【审核太耽误时间,附件里序号忘记改了,不影响阅读不改了】

1),int进行向上舍入
2)even的向上舍入





3)odd的四舍五入
4)round的取整


以上仅为几个简单例子,熟悉后你会发现基本各种常见的舍入规则可以借助各个舍入函数均能实现

流浪铁匠

四),有趣的发现               
(1)2010的隐藏函数               
题外话,本来不打算做这篇舍入函数篇的,做的原因也和本篇有关               
因为在2010时知道有2个新增舍入函数(ceiling/floor.precise),但在2013中被更换为另外2个了(ceiling/floor.math)               
之前没注意具体名字,直到这次使用2个版本对比才发现，上述的这2个函数不是被微软删除了,而是成为类似datedif的隐藏函数,原因不明               
而且还有个iso.ceiling隐蔽性更高(后续根据资料了解的ecma.ceiling才是隐蔽性最高的……基本性质同ceiling)               



原函数floorceiling在1参为正数时不支持2参为负数的情况
2010的floor/ceiling.precise与iso.ceiling解决了这个问题,但会把2参全部转为绝对值进行舍入,结果在1/2参数均为负数时与floor的结果是有差异的
至于2013的floor/ceiling.math解决了这个问题,而且能指定负数时的舍入方向,达到floor/ceiling和floor/ceiling.precise的2种舍入效果
且无论1/2参数的符号是否一致都不会产生错误值
从这点考虑不难理解微软隐藏2010的这2个函数ceiling/floor.precise的原因了,因为这2个函数在后续版本有更好的函数来替代
功能不完善，被2013的新函数替代了
但是,令人奇怪的是2010的iso.ceiling与ecma.ceiling,本身无对应floor函数且功能不完善,且在后续版本被隐藏且未被删除
这点造成了ceiling的衍生函数总数多于floor


(2) 变身函数rmb
这是这期笔记的第2个有趣发现
这个函数在不同语言下会变成不同的函数
中文rmb,日文yen,朝鲜won,英文dollar(dollar在语言为中文时也存在但返回的符号为$)



(3) 最特殊的舍入函数--  round
这里说的特殊不是舍入规则,而是达到一定临界数值后的函数表现
截至目前发现了round的2个特殊临界表现:
1),熟悉的0.99999976146501
round是目前对舍入函数测试中唯一发现2参(舍入位数参数)的舍入点为0.99999976146501的
(仅针对常规舍入函数),fixed/rmb/dollar也是这个特殊舍入点



其他舍入函数测试同理,不考虑那几个文本函数里具有舍入能力的函数,
常规舍入函数里唯有round为这个特殊舍入点

2),参数的明确数据类型-- 长整型

一次无意中的测试,发现了round的一个特殊性质:
当round的1参为不小于1的数值时,2参的数值大小不能达到长整型的上限2147483647
一旦达到,无论原数值是多少,舍入后的结果为0
而这个发现能证明round的2参必然为长整型,才会由于某些设计原因出现这个舍入bug



注意这里最后2个数据在2参不同时的各测试结果        
1参整数位数和2参数值共同决定了这个临界点,二者的整数之和不能大于2147483647        
        
        
5)有个性的函数        
支持数组,但不一定直接支持区域引用        
翻帖子的时候发现有些帖子里称 quotient不支持数组用法,因为引用区域时使用区域数组该函数结果报错        



但其实我们可能只是缺少一次四则运算把直接的区域引用变成内存数组
这是个很奇怪的性质,在部分日期类函数(weeknum与edate等等)与这里提到的quotient等函数上出现

solextrade

感谢铁匠精心整理，果断收藏！