蒲公英 - 制药技术的传播者 GMP理论的实践者

搜索
查看: 6253|回复: 26
收起左侧

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

  [复制链接]
药徒
发表于 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))
回复

使用道具 举报

药徒
发表于 2019-3-1 09:55:35 | 显示全部楼层
公式验证不合适~~~~数值135.425保留2位,结果成了135.43~~数值135.485保留2位,结果成了135.49~~~ 企业微信截图_20190301095326.jpg
回复

使用道具 举报

药徒
发表于 2019-3-1 10:03:04 | 显示全部楼层
谢谢分享     
回复

使用道具 举报

药徒
 楼主| 发表于 2019-3-1 10:13:45 | 显示全部楼层
zlanylz 发表于 2019-3-1 09:55
公式验证不合适~~~~数值135.425保留2位,结果成了135.43~~数值135.485保留2位,结果成了135.49~~~

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

使用道具 举报

药徒
 楼主| 发表于 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))
欢迎指教



mod浮点运算误差

mod浮点运算误差
回复

使用道具 举报

药徒
发表于 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位时,还是有问题~~~
企业微信截图_20190301110218.jpg
回复

使用道具 举报

药徒
发表于 2019-3-1 11:04:49 | 显示全部楼层
楼主费点心修正,我们也好不用去验证宏,可以正大光明的使用~~~

点评

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

使用道具 举报

药徒
 楼主| 发表于 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))
公式不变,调整精度呢?
文件——选项——高级——将精度设为所显示的精度


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


2.png
回复

使用道具 举报

药徒
 楼主| 发表于 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))
公式不变,调整精度 ...

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

点评

我以前学习到的方法是: 保留到整数 =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 以此类推  详情 回复 发表于 2019-3-1 17:31
回复

使用道具 举报

药徒
发表于 2019-3-1 12:22:24 | 显示全部楼层
楼主加油!
回复

使用道具 举报

药师
发表于 2019-3-1 14:19:21 | 显示全部楼层
锲而不舍,终成正果。加油!
回复

使用道具 举报

药徒
发表于 2019-3-1 14:22:32 | 显示全部楼层
楼主加油,虽然我用不到。
回复

使用道具 举报

药徒
发表于 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
以此类推
回复

使用道具 举报

药徒
 楼主| 发表于 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浮点运算存在误差。 122.png
122.png
回复

使用道具 举报

药生
发表于 2019-3-2 11:33:20 | 显示全部楼层
zlanylz 发表于 2019-3-1 11:04
楼主费点心修正,我们也好不用去验证宏,可以正大光明的使用~~~

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

使用道具 举报

药徒
发表于 2019-3-3 09:24:07 | 显示全部楼层
joshua 发表于 2019-3-2 11:33
还是弄成宏比较好吧? 使用起来就像系统自带函数一样;至于验证 公式和宏验证不是差不多吗

请问宏如何验证?谢谢

点评

那我就试着讲一下我的理解好了 ——首先要了解用“宏”(其实是 “公式”)实现有什么好处—— 1-对开发者而言可以不用写写逻辑复杂的公式(看了之前几位的公式版 普遍公式很长,而且因为公式只能写在1行,嵌套多层i  详情 回复 发表于 2019-3-3 19:33
回复

使用道具 举报

药生
发表于 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函数是 四舍六入的),另外也是因为浮点数精度的问题,这个版本应该会在个别情况下 出现修约不准的情况,要完善<要懂开发的才能做好验证>
  1. Public Function RoundX(ByVal rng As Range, ByVal i As Integer)
  2.     If IsNumeric(rng.Value) And rng.Value <> "" Then
  3.         RoundX = Round(rng.Value, i)
  4.     Else
  5.         MsgBox "被修约的对象不是数字或者为空,请检查!!", vbCritical, "类型错误警告!"
  6.         RoundX = "错误"
  7.     End If
  8. End Function
复制代码

四舍六入.xlsm

15.11 KB, 下载次数: 5

售价: 5 金币  [记录]

回复

使用道具 举报

药徒
发表于 2019-3-4 09:01:03 | 显示全部楼层
joshua 发表于 2019-3-3 19:33
那我就试着讲一下我的理解好了
——首先要了解用“宏”(其实是 “公式”)实现有什么好处——
1-对开发 ...

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

四舍六入五留双_终极完美版.rar

9.27 KB, 下载次数: 35

点评

Cdec(value) 是讲数字转换为 十进制数 进行计算,不转换的话 不管什么类型数字都是用二进制进行计算,会有浮点数精度 导致135.425 这种修约错误的情况;+上Cdec之后 就不会出错了(作者这个解决方式真挺好  详情 回复 发表于 2019-3-4 13:08
回复

使用道具 举报

药生
发表于 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
回复

使用道具 举报

药徒
 楼主| 发表于 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))

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

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

×发帖声明
1、本站为技术交流论坛,发帖的内容具有互动属性。您在本站发布的内容:
①在无人回复的情况下,可以通过自助删帖功能随时删除(自助删帖功能关闭期间,可以联系管理员微信:8542508 处理。)
②在有人回复和讨论的情况下,主题帖和回复内容已构成一个不可分割的整体,您将不能直接删除该帖。
2、禁止发布任何涉政、涉黄赌毒及其他违反国家相关法律、法规、及本站版规的内容,详情请参阅《蒲公英论坛总版规》。
3、您在本站发表、转载的任何作品仅代表您个人观点,不代表本站观点。不要盗用有版权要求的作品,转贴请注明来源,否则文责自负。
4、请认真阅读上述条款,您发帖即代表接受上述条款。

QQ|手机版|蒲公英|ouryao|蒲公英 ( 京ICP备14042168号-1 )  增值电信业务经营许可证编号:京B2-20243455  互联网药品信息服务资格证书编号:(京)-非经营性-2024-0033

GMT+8, 2025-2-8 20:22

Powered by Discuz! X3.4运维单位:苏州豚鼠科技有限公司

Copyright © 2001-2020, Tencent Cloud.

声明:蒲公英网站所涉及的原创文章、文字内容、视频图片及首发资料,版权归作者及蒲公英网站所有,转载要在显著位置标明来源“蒲公英”;禁止任何形式的商业用途。违反上述声明的,本站及作者将追究法律责任。
快速回复 返回顶部 返回列表