保留位数EXCEL实现

路云 发表于 2023-10-4 10:53

谢谢，就是要多找漏洞

=IF(OR(AND(ISEVEN(MID(A2,FIND(".",A2)+B2,1)*1),MID(A2,FIND(".",A2)+B2+1,1)="5",IFERROR(RIGHT(A2,LEN(A2)-FIND(".",A2)-B2-1)*1,0)=0),MID(A2,FIND(".",A2)+B2+1,1)*1<5),LEFT(A2,FIND(".",A2,1)+B2),IF(IFERROR(FIND(".",LEFT(A2,FIND(".",A2,1)*1+B2)*1+IF(A2*1<0,-1,1)/POWER(10,B2),1),0)=0,LEFT(A2,FIND(".",A2,1)*1+B2)*1+IF(A2*1<0,-1,1)/POWER(10,B2)&"."&REPT("0",B2),LEFT(A2,FIND(".",A2,1)*1+B2)*1+IF(A2*1<0,-1,1)/POWER(10,B2)&REPT("0",B2-(LEN(LEFT(A2,FIND(".",A2,1)*1+B2)*1+IF(A2*1<0,-1,1)/POWER(10,B2))-IFERROR(FIND(".",LEFT(A2,FIND(".",A2,1)*1+B2)*1+IF(A2*1<0,-1,1)/POWER(10,B2),1),0)))))

路云 发表于 2023-10-4 10:53

您确认是用的我的公式。我这边正常啊

用这个试试，都是对的

应该是版本的问题

wangyiyong7703 发表于 2023-10-5 08:51
您确认是用的我的公式。我这边正常啊

您的A列的数据格式不是“数字”，而是“文本”，所以才会得出“正常”的假象。

另外，拟修约数和修约后的最终结果，还应该根据实际需要，为其设置显示格式，如：是否显示正负号、小数位数，包括条件格式等(如7楼截图)。

以下截图示例中，E列用“4舍5入法”修约保留3位小数的修约结果；F列用“4舍6入5留双法”(本人7楼提供的自定义函数)修约保留3位小数的修约结果。G列和H列都是用本人提供的自定义函数修约，保留2位小数的最终修约结果。

1.这个当然要用文本格式来弄，电子表格是智能的，默认自动舍去后面的0，相当于修约过，按正常的情况下，是不能这样操作的。因为用文本格式是必须的。2.您的反例要符合正常的要求，全数的有效位要大于修约后的有效位数。这个可以改进，但没有必要。您的几个反例再弄多一点，经典一点

路云 发表于 2023-10-6 08:51
您的A列的数据格式不是“数字”，而是“文本”，所以才会得出“正常”的假象。另外，拟修约数和修约后的最 ...

谢谢，后期还请您多提宝贵的反例

路云 发表于 2023-10-6 14:07
以下截图示例中，E列用“4舍5入法”修约保留3位小数的修约结果；F列用“4舍6入5留双法”(本人7楼提供的自定 ...

正常情况下，一些文献提供的公式基本上都能满足，可惜的是特殊情况下是不能满足的。0.100000009，9.99999999.我的算法正是进行文本操作，避开了浮点的问题。

wangyiyong7703 发表于 2023-10-6 14:11
1.这个当然要用文本格式来弄，电子表格是智能的，默认自动舍去后面的0，相当于修约过，按正常的情况下，是 ...

1.这个当然要用文本格式来弄，电子表格是智能的，默认自动舍去后面的0，相当于修约过，按正常的情况下，是不能这样操作的。因为用文本格式是必须的。

这个理由是不成立的。如果是“文本”格式，中间有可能夹杂了其他文本字符，您的公式照样发现不了，如：

2.您的反例要符合正常的要求，全数的有效位要大于修约后的有效位数。

修约前的全数有效位数多少，完全取决于数据处理的结果(如：多次测量结果的平均值)，大于、小于、等于修约后的小数位数都是有可能的。

正常情况下，一些文献提供的公式基本上都能满足，可惜的是特殊情况下是不能满足的。0.100000009，9.99999999.我的算法正是进行文本操作，避开了浮点的问题。

我提供的自定义函数，不存在浮点的问题。

路云 发表于 2023-10-6 14:37
1.这个当然要用文本格式来弄，电子表格是智能的，默认自动舍去后面的0，相当于修约过，按正常的情况下， ...

您函数中有相双精度计算，主要是没有办法补位，这个功能比较麻烦。不然的话是很简单的

wangyiyong7703 发表于 2023-10-6 18:16
您函数中有相双精度计算，主要是没有办法补位，这个功能比较麻烦。不然的话是很简单的 ...

没看明白什么叫“有相双精度计算”？您说的“补位”是啥意思？我最初也是与您的想法一样，想通过您这种方式实现。结果研究了很长时间，也没有找到一种能够完全自动识别与控制的方法，而且迭代、嵌套、交叉引用太多，无法做成“傻瓜型”，满足现场检测/校准人员的录入操作需求。最终还是用自定义函数方式解决问题，使过程大为简化。最终要结合单元格格式、条件格式等设置，实现数据有效性自动识别、敲录错误自动拦截提醒、自动生成符合性判定结论等功能。

这个也是很简单的，f(x)是公式中要求得的结果，f(x) & "00000"即可，补0即可。我们把主函数写出来，电脑修约过度然后根据情况进行补位即可。我们是要避开VB，只用内建函数写

路云 发表于 2023-10-6 19:08
没看明白什么叫“有相双精度计算”？您说的“补位”是啥意思？我最初也是与您的想法一样，想通过您这种方 ...

还是要有一定的规范，不可能做一个适用所有情况。否则VB也做不过来。通过规范使用者的操作行为。简化函数。

wangyiyong7703 发表于 2023-10-6 20:37
这个也是很简单的，f(x)是公式中要求得的结果，f(x) & "00000"即可，补0即可。我们把主函数写出来，电脑修 ...

这个也是很简单的，f(x)是公式中要求得的结果，f(x) & “00000”即可，补0即可。我们把主函数写出来，电脑修约过度然后根据情况进行补位即可。我们是要避开VB，只用内建函数写

不需要在公式中用“& 00000”方式补0，只需对修约后的测量结果所在单元格设置数字格式即可实现。

还是要有一定的规范，不可能做一个适用所有情况。否则VB也做不过来。通过规范使用者的操作行为。简化函数。

通过VBA编制的“4舍6入5留双修约自定义函数”，本来就是适用于所有数据按此修约规则的修约。就如同求平均值的内部函数“AVERAGE( )”和求和函数“SUM( )”一样，这么方便、高效的函数您不用，非要逐个数据采取加减乘除四则运算的办法去求。我也实在是想不明白图什么？

现场实际操作的检测/校准人员，对EXCEL的操作技能并不都十分精通，尤其对各种函数的应用、单元格格式的设置、条件格式的应用更是知之甚少，甚至一无所知。VBA本身就是微软为个性化应用提供的高效应用工具，非常方便。我们不需要把VBA看得如临大敌，谈宏色变，没那么可怕。我们的目的，就是要根据实际需要编制相应的标准化《原始记录》模板表单，让所有不懂EXCEL操作人员，只要会敲入数据，经过几分钟的简单培训，就能够掌握、编制、生成每一份数据统一、规范、美观的《原始记录》，而且不会出现误操作(因为所有不让操作人员编辑的单元格都做了屏蔽设置，未经授权是点不进去的)。

一条一条的驳，高手高手。

路云 发表于 2023-10-6 22:41
这个也是很简单的，f(x)是公式中要求得的结果，f(x) & “00000”即可，补0即可。我们把主函数写出来，电脑 ...

麻烦您做一个不用VB的函数，或者完善一个您的函数，0.1000的结果显示为0.1，我怎么也看不明白。您的函数中如果输入项有字母，也能智能识别用作数值处理。我说的是我不想用VB这个宏功能。您能行么

补充内容 (2023-10-9 14:46):
您 的公式行么

路云 发表于 2023-10-6 22:41
这个也是很简单的，f(x)是公式中要求得的结果，f(x) & “00000”即可，补0即可。我们把主函数写出来，电脑 ...

那麻烦您把你的单机板的excel文件传到坛子里面，看能否用。我们学习学习

修约表现，两个公式都有问题，表现不一样。16-18位，24-27位有异常。VB自定义的公式10位以后就开始一直不中，但要配合高端操作设置格式

wangyiyong7703 发表于 2023-10-7 15:39
修约表现，两个公式都有问题，表现不一样。16-18位，24-27位有异常。VB自定义的公式10位以后就开始一直不中 ...

1、我的自定义函数(见7楼)您不是已经试用了吗，单机版与网络版都能用，只是要将文件另存为“Excel启用宏的模板(*.xltm)”即可。

2、对于单元格来说，其默认的数字格式是“G/通用格式”(即“常规”格式)，这种“常规”格式，其默认的有效显示的数字位数只能10位。键入的数值位数超过15位时则会截尾，且数值在10^-10～10¹⁰时，最多显示前10位。若数值大于10¹⁰或小于10^-10时，则以科学计数法显示。所以必须配合单元格的数字格式设置(即：将数字格式设置为“自定义”格式)，才能正常显示。

3、除了科学研究，一般的检测/校准领域，基本上不会遇到需要保留10位及以上“有效数字”的情形，所以该自定义函数在10^-10～10¹⁰数值范围内应用，是没有任何问题的。如果真有这种需求，那恐怕就不是VBA宏能够解决的了。

4、您是将数值转换成文本来处理，修约结果也是文本，无法显示正数的符号“+”。且数字中夹杂有其他字符，也同样会在修约结果中显示。所以这种处理方式实质上是“文本字符的截取与组合”。

路云 发表于 2023-10-8 17:22
1、我的自定义函数(见7楼)您不是已经试用了吗，单机版与网络版都能用，只是要将文件另存为“Excel启用宏的 ...

还是不了解。您的自定义函数尽然比不上内建函数的表现。还说vbA解决不了这个问题，您敢打赌么，解决了怎么办

wangyiyong7703 发表于 2023-10-8 18:03
还是不了解。您的自定义函数尽然比不上内建函数的表现。还说vbA解决不了这个问题，您敢打赌么，解决了怎 ...

打赌就没有意思了。我只是说“恐怕就不是VBA宏能够解决的了”。EXCEL的数值处理范围区间大小是有限的。您所提供的公式中，除了“POWER()”是数学类“工作表函数”外，基本上全是文字处理类或逻辑判断类的“工作表函数”，而不是数值处理的内建函数。

本主题讨论的是数据(非文本数据)的修约问题，EXCEL中与数值修约相关的，就只有“ROUND()”、“ROUNDDOWN()”和“ROUNDUP()”三个“工作表函数”。最为接近的就是“ROUND()”函数，仅仅是修约规则有那么一点点差异。“ROUND()”函数的修约规则是“四舍五入”，自定义函数的修约规则是“四舍六入五留双”。

其实“ROUND()”函数有两个，一个就是“工作表函数”(即您所说的“内建函数”)，另一个是“VB函数”，尽管这两个函数名称一模一样，但两者的算法规则是不同的。

路云 发表于 2023-10-8 17:22
1、我的自定义函数(见7楼)您不是已经试用了吗，单机版与网络版都能用，只是要将文件另存为“Excel启用宏的 ...

1.麻烦您举一些实质性的问题进行讨论，这样很累，没有意义，之前您找到的很多有意义的反例很好；
2.纯数字文本也可以参与其它计算，强制进行转换。这里解决的是尾数补０的情况，用文本表示是不得已的事。
3.修约后尾数0后，系统自动去除，您的自定义函数是一个致命的问题。如果靠设置去解决，那您的公式确实看起来很好。
4.但是实际上不用设置格式，如果要用vb的话，彻底解决位数过大的问题。VB轻松解决。
5.你用设置格式解决尾数补0问题，确实不是很高端，我也是想避开。其实有很多内建函数配合格式设置可以做到，完全不用ＶＢ，这个早就实现了。
6.发挥内建函数作用，不靠其他方法是我追求的。因为您用vb做的自定义函数没多大意思，我实不屑取，避开vb,才是我们想要的。用vb超百位的修约我也可以做到,早就做到了。