【四舍六入五成双】Excel最短纯公式非宏版

chnhqp · 发表于 2019-3-1 09:22:10

欢迎您注册蒲公英

您需要登录才可以下载或查看，没有帐号？立即注册

x

本帖最后由 chnhqp 于 2019-3-1 09:38 编辑

=if((A2*B2-0.5）/int(A2*B2)=1,int(A2*B2)+mod(int(A2*B2),2),if(A2*B2-int(A2*B2)>0.5,int(A2*B2)+1,int(A2*B2)))/B2
A2为原始数据；B2为小数点保留位数，例如：1位小数为10，2位小数为100以此类推；公式复制在C2栏内。

刚看到一个蒲友发布的公式，很棒。突然想到自己手中也有一个不错的公式，在此分享给大家。

=IF(MOD(ABS(A1*POWER(10,B1)),2)=0.5,ROUNDDOWN(A1,B1),ROUND(A1,B1))

A1为原始数据，B1为小数点保留位数。公式复制在C1栏。可以将B1替换成自己需要的保留位数的数字，让公式更简单且不需要设置B列。

公式含义：POWER（X,Y）：X的Y次幂；ABS（X）：X的绝对值；MOD（Y，2）：Y除以2的余数；ROUNDDOWN（X,2）:舍去X小数点2位以后的数字；ROUND（Y,2）：对Y进行四舍五入，保留2位小数。

补充内容 (2019-3-1 10:39):
感谢zlanylz验证出公式的问题。

经过查找原因，找到是由于MOD函数浮点运算错误造成。
公式经过修正后为：=IF(ROUND(MOD(ABS(A1*POWER(10,B1)),2),2)=0.5,ROUNDDOWN(A1,B1),ROUND(A1,B1))
请大家测试。

补充内容 (2019-3-6 17:53):
=IF((A10*POWER(10,B10)-0.5)/INT(A10*POWER(10,B10))=1,ROUNDDOWN(A10,B10),ROUND(A10,B10))

麻烦大家帮忙测试一下这个公式是否可行。谢谢~~~~

补充内容 (2019-3-7 09:20):
再来优化一下公式。
=IF((A1*POWER(10,B1)-0.5)=INT(A1*POWER(10,B1)),ROUNDDOWN(A1,B1),ROUND(A1,B1))

zlanylz · 发表于 2019-3-1 09:55:35

公式验证不合适~~~~数值135.425保留2位，结果成了135.43~~数值135.485保留2位，结果成了135.49~~~ 企业微信截图_20190301095326.jpg

gproe · 发表于 2019-3-1 10:03:04

谢谢分享

chnhqp · 发表于 2019-3-1 10:13:45

zlanylz 发表于 2019-3-1 09:55
公式验证不合适~~~~数值135.425保留2位，结果成了135.43~~数值135.485保留2位，结果成了135.49~~~

测试过了，的确某些数字存在“浮点运算误差”。公式待修正。

chnhqp · 发表于 2019-3-1 10:46:58

mod函数浮点运算误差公式经修正为：=IF(ROUND(MOD(ABS(A1*POWER(10,B1)),2),2)=0.5,ROUNDDOWN(A1,B1),ROUND(A1,B1))
欢迎指教

zlanylz · 发表于 2019-3-1 11:02:27

chnhqp 发表于 2019-3-1 10:46
mod函数浮点运算误差公式经修正为：=IF(ROUND(MOD(ABS(A1*POWER(10,B1)),2),2)=0.5,ROUNDDOWN(A1,B1),ROUND ...

当保留3位4位时，还是有问题~~~

zlanylz · 发表于 2019-3-1 11:04:49

楼主费点心修正，我们也好不用去验证

宏，可以正大光明的使用~~~

chnhqp · 发表于 2019-3-1 11:56:44

zlanylz 发表于 2019-3-1 11:04
楼主费点心修正，我们也好不用去验证宏，可以正大光明的使用~~~

=IF(MOD(ABS(A1*POWER(10,B1)),2)=0.5,ROUNDDOWN(A1,B1),ROUND(A1,B1))
公式不变，调整精度呢？
文件——选项——高级——将精度设为所显示的精度

这活生生的变成了讨论帖，欢迎交流。

chnhqp · 发表于 2019-3-1 11:59:28

chnhqp 发表于 2019-3-1 11:56
=IF(MOD(ABS(A1*POWER(10,B1)),2)=0.5,ROUNDDOWN(A1,B1),ROUND(A1,B1))
公式不变，调整精度 ...

好像还是不行哦，束手无策的我

15026923454 · 发表于 2019-3-1 12:22:24

楼主加油！

xqliu · 发表于 2019-3-1 14:19:21

锲而不舍，终成正果。加油！

哇-咔咔 · 发表于 2019-3-1 14:22:32

楼主加油，虽然我用不到。

wyqsz · 发表于 2019-3-1 17:31:51

chnhqp 发表于 2019-3-1 11:59
好像还是不行哦，束手无策的我

我以前学习到的方法是：
保留到整数 =ROUND(A1,0)-(MOD(A1,2)=0.5)
保留到一位 =(ROUND(A1*10,0)-(MOD(A1*10,2)=0.5))/10
保留到二位 =(ROUND(A1*100,0)-(MOD(A1*100,2)=0.5))/100
以此类推

chnhqp · 发表于 2019-3-2 09:37:13

wyqsz 发表于 2019-3-1 17:31
我以前学习到的方法是：
保留到整数 =ROUND(A1,0)-(MOD(A1,2)=0.5)
保留到一位 =(ROUND(A1*10,0)-(MOD ...

和我遇到的问题一样，MOD浮点运算存在误差。

joshua · 发表于 2019-3-2 11:33:20

zlanylz 发表于 2019-3-1 11:04
楼主费点心修正，我们也好不用去验证宏，可以正大光明的使用~~~

还是弄成宏比较好吧？使用起来就像系统自带函数一样；至于验证公式和宏验证不是差不多吗

李光耀 · 发表于 2019-3-3 09:24:07

joshua 发表于 2019-3-2 11:33
还是弄成宏比较好吧？使用起来就像系统自带函数一样；至于验证公式和宏验证不是差不多吗

请问宏如何验证？谢谢

joshua · 发表于 2019-3-3 19:33:44

李光耀发表于 2019-3-3 09:24
请问宏如何验证？谢谢

那我就试着讲一下我的理解好了
——首先要了解用“宏”(其实是 “公式”)实现有什么好处——
1-对开发者而言可以不用写写逻辑复杂的公式（看了之前几位的公式版普遍公式很长，而且因为公式只能写在1行，嵌套多层if，不容易理解）
2-对使用者而言，最好直接像使用系统函数一样，不用关心具体实现过程（最好都不让他们看到实现过程）
3-之前用公式实现的普遍要1列用于存放修约位数，这个增加了多余数据，而且不太符合用户的使用习惯
4-宏可以实现四舍六入基础更多更好的交互功能，比如被修约对象不是数值时可以警告等
5-公式复杂了之后，如果经常复制容易出错

注：roundX是自定义的四舍六入函数, 像系统函数一样使用,而且用户不用看到背后很长的公式

不用单独存储修约位数

可以实现监测修约数字类型等交互功能

-------宏的缺点----------
1、必须要启用宏（如果excel完全禁用的话可能看不到提升），而且需要以带有这个宏的文件去编自己的文件
2、对开发者门槛高一点，需要有一定VB基础(具体业务代码可能很简单，但那是在你对excel各种对象、VBA语句、VBA自带函数比较熟悉的前提下而言的)

-----关于验证--------
1-黑盒测试：这个对于公式版或者宏版应该没啥区别，就是多举一些数字各种极端情况去试存不存在修约错误的情况
2-白盒测试：关于原代码审核这块，主要是分析公式或者宏代码在逻辑上有没有明显的漏洞，这个本质上两者应该是一样的，甚至我看到前面有几个公式版的公式逻辑更加复杂一点，大家感觉宏很高深，并不是宏业务逻辑代码多么复杂，而是对宏的运行机制、语法规则不了解，所以这个你只有去学一点VB语法和Excel对象才能会，当人这个99%的制药人估计都不会

3-安全性验证：这个本质上“宏”会更有优势一点，excel的公式要保护起来只能通过锁定单元格、保护excel表等实现，但是你在修订表格的时候还是会开发编辑公式、而且excel自带的保护功能实在是太弱了，甚至不用第三方工具即可实现破解；而“宏”你要修改后台代码相对就难很多，1、90%的人不知道怎么去查看VBA工程，2、VBA工程通过密码加密后你就不能查看代码了（目前没有特别简单的破解方法）3、宏后台运行的特点，自然不会诱导用户去查看后台代码（平常我们使用系统函数时也不会想去看看系统函数的后台实现逻辑）

--最后附上代码---
PS: 这个是利用了VBA里面自带的round函数（excel里面的round 函数是四舍五入的，VBA里面的round函数是四舍六入的），另外也是因为浮点数精度的问题，这个版本应该会在个别情况下出现修约不准的情况，要完善<要懂开发的才能做好验证>

Public Function RoundX(ByVal rng As Range, ByVal i As Integer)
If IsNumeric(rng.Value) And rng.Value <> "" Then
RoundX = Round(rng.Value, i)
Else
MsgBox "被修约的对象不是数字或者为空，请检查！！", vbCritical, "类型错误警告！"
RoundX = "错误"
End If
End Function

复制代码

zlanylz · 发表于 2019-3-4 09:01:03

joshua 发表于 2019-3-3 19:33
那我就试着讲一下我的理解好了
——首先要了解用“宏”(其实是 “公式”)实现有什么好处——
1-对开发 ...

这里有别人原创的宏~~~Excel中的四舍六入五成双
但我没理解宏中CDEC的定义~~~

joshua · 发表于 2019-3-4 13:08:43

zlanylz 发表于 2019-3-4 09:01
这里有别人原创的宏~~~Excel中的四舍六入五成双
但我没理解宏中CDEC的定义~~~

Cdec(value) 是讲数字转换为十进制数进行计算，不转换的话不管什么类型数字都是用二进制进行计算，会有浮点数精度导致135.425 这种修约错误的情况；＋上Cdec之后就不会出错了（作者这个解决方式真挺好

）

复制代码

Public Function RoundX(ByVal rng As Range, ByVal i As Integer)
If IsNumeric(rng.Value) And rng.Value <> "" Then
RoundX = Round(Cdec(rng.Value), i)
Else
MsgBox "被修约的对象不是数字或者为空，请检查！！", vbCritical, "类型错误警告！"
RoundX = "错误"
End If
End Function

chnhqp · 发表于 2019-3-6 17:52:08

zlanylz 发表于 2019-3-4 09:01
这里有别人原创的宏~~~Excel中的四舍六入五成双
但我没理解宏中CDEC的定义~~~

=IF((A10*POWER(10,B10)-0.5)/INT(A10*POWER(10,B10))=1,ROUNDDOWN(A10,B10),ROUND(A10,B10))

麻烦再来测试一下这个公式。

【四舍六入五成双】Excel最短纯公式非宏版

欢迎您注册蒲公英

点评

点评

点评

点评

浏览过的版块