wangyiyong7703 发表于 2023-2-22 16:59:27

四舍六入五成双EXCEL实现

=IF(MID(A2,FIND(".",A2,1)+B2+1,1)*1<5,LEFT(A2,B2+FIND(".",A2,1)),IF(AND(MID(A2,FIND(".",A2,1)+B2+1,1)*1=5,RIGHT(A2,LEN(A2)-FIND(".",A2,1)-B2)*1=5*POWER(10,LEN(A2)-FIND(".",A2,1)-B2-1),INT(MID(A2,FIND(".",A2,1)+B2,1)*1/2)=MID(A2,FIND(".",A2,1)+B2,1)*1/2),LEFT(A2,B2+FIND(".",A2,1)),LEFT(A2,B2+FIND(".",A2,1))*1+POWER(10,-B2)))

解析：
A2存有待修约值，
B2存有修约小数点保留位数N，
逻辑：如果小数点后第N+1位数字小于5 则结果为不进位；
         如果小数点后第N+1位数字等于5 且第N数数字为偶数则结果为不进位
         否则结果全部进位。


返回进位结果LEFT(A2,B2+FIND(".",A2,1))*1+POWER(10,-B2)，
返回不进位结果LEFT(A2,B2+FIND(".",A2,1))
不进位条件：
   小数点N+1位数字MID(A2,FIND(".",A2,1)+B2+1,1)*1小于5；
   小数点N+1位数字MID(A2,FIND(".",A2,1)+B2+1,1)*1等于5，且后面全为0的
   条件RIGHT(A2,LEN(A2)-FIND(".",A2,1)-B2)*1=5*POWER(10,LEN(A2)-FIND(".",A2,1)-B2-1)；         

进位条件：不进位便是进位。

附：
奇偶判定：INT(MID(A2,FIND(".",A2,1)+B2,1)*1/2)=MID(A2,FIND(".",A2,1)+B2,1)*1/2)，成立为偶数
进位后：加数值POWER(10,-B2)
小数点从左到右所在位置：FIND(".",A2,1)

wangyiyong7703 发表于 2023-2-27 14:13:46

有效数字大于1，求公式可在附件中下载。已更新。如有好的意见和建议，请发贴。

wangyiyong7703 发表于 2023-2-27 17:05:27

stacay 发表于 2023-2-27 15:30
EXCEL受限于浮点运算机制，是比较难处理 的。

确实碰到这种情况，改变了思路，才破解了。很不容易。不用宏程序解决确实太难了。我用宏程序测试过，目前还没有发现异常，宏程序在文件里面。可运行

wangyiyong7703 发表于 2023-2-22 22:04:50

还有有效数字，里面公式太复杂了，就不作解析了。

frank001 发表于 2023-2-23 13:38:28

谢谢你的分享！

wangyiyong7703 发表于 2023-2-23 15:16:47

frank001 发表于 2023-2-23 13:38
谢谢你的分享！

k7，原创作品，多多提意见，后期好改进

NMR 发表于 2023-2-23 21:59:06


谢谢你的分享！

冯贵林 发表于 2023-2-24 08:23:22

怎么把它运用到自己的证书模板的数据处理过程中呢

锡林郭勒 发表于 2023-2-24 08:43:58

这可以解决好多问题，

lreplay 发表于 2023-2-24 11:37:56

看不懂，但是大受震撼……

frank001 发表于 2023-2-24 13:09:59

wangyiyong7703 发表于 2023-2-23 15:16
k7，原创作品，多多提意见，后期好改进

公式还有一个能改进的点，小数点后面最后一位为0时，小数位保留就会出现异常

五路车 发表于 2023-2-24 14:59:46

先进的科学技术，提高生产力水平，不错

stacay 发表于 2023-2-24 17:56:55

本帖最后由 stacay 于 2023-2-24 18:04 编辑

如果数据大于0.1可以用下面的公式。小于0.1就不大适用了。

假如原始数据在A1，则在B1或C1或D1其他列中输入以下
=IF(TRUNC(MOD(ABS(A1*POWER(10,2)),2),2)=0.5,ROUNDDOWN(A1,2),ROUND(A1,2))

如果数据不在A1处，如在B1处，则需要把公式里的A1改成B1即可，照此类推
上面公式给出的是保留2位小数，如果保留3位，就把2改成3，保留0位则改成0，保留十位-1，百位-2，照此类推
=IF(TRUNC(MOD(ABS(A1*POWER(10,2)),2),2)=0.5,ROUNDDOWN(A1,2),ROUND(A1,2))

wangyiyong7703 发表于 2023-2-24 21:10:05

frank001 发表于 2023-2-24 13:09
公式还有一个能改进的点，小数点后面最后一位为0时，小数位保留就会出现异常 ...

有改进版，这个只是初稿

wangyiyong7703 发表于 2023-2-24 21:10:48

冯贵林 发表于 2023-2-24 08:23
怎么把它运用到自己的证书模板的数据处理过程中呢

好弄注意引用即可

wangyiyong7703 发表于 2023-2-24 21:45:11

stacay 发表于 2023-2-24 17:56
如果数据大于0.1可以用下面的公式。小于0.1就不大适用了。

假如原始数据在A1，则在B1或C1或D1其他列中输入 ...

连续0和连续9就失效，好多文献的公司也是这样的，连续9正在想办法，连续0没有问题

wangyiyong7703 发表于 2023-2-25 15:13:17

漏洞补好，1以下不含1，=IF(IFERROR(FIND(REPT("9",B2),A2,1),1)=3,("1."&REPT("0",B2-1)),IF(IFERROR(FIND(REPT("9",B2),(LEFT(A2,FIND(".",A2,1))&RIGHT(A2,LEN(A2)-INT(ABS(LOG(A2)))-FIND(".",A2,1))),1),1)=3,IF((LEFT(A2,FIND(".",A2,1))&RIGHT(A2,LEN(A2)-INT(ABS(LOG(A2)))-FIND(".",A2,1)))*1<("0."&REPT("9",B2)&"5")*1,("0."&REPT("0",INT(ABS(LOG(A2))))&REPT("9",B2)),("0." & REPT("0",INT(ABS(LOG(A2)))-1)&"1"& REPT("0",B2-1))),IF(OR(LEFT(RIGHT(LEFT(A2,FIND(".",A2,1)) & RIGHT(A2,LEN(A2)-INT(ABS(LOG(A2)))-FIND(".",A2,1)),LEN(LEFT(A2,FIND(".",A2,1)) & RIGHT(A2,LEN(A2)-INT(ABS(LOG(A2)))-FIND(".",A2,1)))-B2-2),1)*1<5,AND(5&REPT("0",LEN(RIGHT(LEFT(A2,FIND(".",A2,1)) & RIGHT(A2,LEN(A2)-INT(ABS(LOG(A2)))-FIND(".",A2,1)),LEN(LEFT(A2,FIND(".",A2,1)) & RIGHT(A2,LEN(A2)-INT(ABS(LOG(A2)))-FIND(".",A2,1)))-B2-2))-1)=RIGHT(LEFT(A2,FIND(".",A2,1)) & RIGHT(A2,LEN(A2)-INT(ABS(LOG(A2)))-FIND(".",A2,1)),LEN(LEFT(A2,FIND(".",A2,1)) & RIGHT(A2,LEN(A2)-INT(ABS(LOG(A2)))-FIND(".",A2,1)))-B2-2),ISEVEN(RIGHT(LEFT(LEFT(A2,FIND(".",A2,1)) & RIGHT(A2,LEN(A2)-INT(ABS(LOG(A2)))-FIND(".",A2,1)),B2+2),1)*1))),ROUNDDOWN(LEFT(A2,FIND(".",A2,1)) & RIGHT(A2,LEN(A2)-INT(ABS(LOG(A2)))-FIND(".",A2,1)),B2),ROUNDUP(LEFT(A2,FIND(".",A2,1)) & RIGHT(A2,LEN(A2)-INT(ABS(LOG(A2)))-FIND(".",A2,1)),B2))/POWER(10,INT(ABS(LOG(A2)))) & REPT("0",B2+INT(ABS(LOG(A2)))-IF(ROUNDUP(LEFT(A2,FIND(".",A2,1)) & RIGHT(A2,LEN(A2)-INT(ABS(LOG(A2)))-FIND(".",A2,1)),B2)=1,1,INT(ABS(LOG(A2)))-2)-LEN(IF(OR(LEFT(RIGHT(LEFT(A2,FIND(".",A2,1)) & RIGHT(A2,LEN(A2)-INT(ABS(LOG(A2)))-FIND(".",A2,1)),LEN(LEFT(A2,FIND(".",A2,1)) & RIGHT(A2,LEN(A2)-INT(ABS(LOG(A2)))-FIND(".",A2,1)))-B2-2),1)*1<5,AND(5&REPT("0",LEN(RIGHT(LEFT(A2,FIND(".",A2,1)) & RIGHT(A2,LEN(A2)-INT(ABS(LOG(A2)))-FIND(".",A2,1)),LEN(LEFT(A2,FIND(".",A2,1)) & RIGHT(A2,LEN(A2)-INT(ABS(LOG(A2)))-FIND(".",A2,1)))-B2-2))-1)=RIGHT(LEFT(A2,FIND(".",A2,1)) & RIGHT(A2,LEN(A2)-INT(ABS(LOG(A2)))-FIND(".",A2,1)),LEN(LEFT(A2,FIND(".",A2,1)) & RIGHT(A2,LEN(A2)-INT(ABS(LOG(A2)))-FIND(".",A2,1)))-B2-2),ISEVEN(RIGHT(LEFT(LEFT(A2,FIND(".",A2,1)) & RIGHT(A2,LEN(A2)-INT(ABS(LOG(A2)))-FIND(".",A2,1)),B2+2),1)*1))),ROUNDDOWN(LEFT(A2,FIND(".",A2,1)) & RIGHT(A2,LEN(A2)-INT(ABS(LOG(A2)))-FIND(".",A2,1)),B2),ROUNDUP(LEFT(A2,FIND(".",A2,1)) & RIGHT(A2,LEN(A2)-INT(ABS(LOG(A2)))-FIND(".",A2,1)),B2))))))

wangyiyong7703 发表于 2023-2-25 15:17:06

frank001 发表于 2023-2-24 13:09
公式还有一个能改进的点，小数点后面最后一位为0时，小数位保留就会出现异常 ...

请您亲测一下，多提提意见，原创不易

wangyiyong7703 发表于 2023-2-25 15:17:22

stacay 发表于 2023-2-24 17:56
如果数据大于0.1可以用下面的公式。小于0.1就不大适用了。

假如原始数据在A1，则在B1或C1或D1其他列中输入 ...

请您亲测一下，多提提意见，原创不易

wangyiyong7703 发表于 2023-2-25 16:04:31

大于1的公式，目前10的N次方没有弄，=IF(AND(IFERROR(FIND(REPT("9",C2),(A2/POWER(10,INT(LOG(A2))+1)),1),1)=3,(A2/POWER(10,INT(LOG(A2))+1))*1<("0."&REPT("9",C2)&"5")*1),("0."&REPT("9",C2)),IF(AND(IFERROR(FIND(REPT("9",C2),(A2/POWER(10,INT(LOG(A2))+1))*1,1),1)=3,OR((A2/POWER(10,INT(LOG(A2))+1))*1>("0."&REPT("9",C2)&"5")*1,(A2/POWER(10,INT(LOG(A2))+1))*1=("0."&REPT("9",C2)&"5")*1)),("1."&REPT("0",C2-1)),IF(IFERROR(FIND(REPT("9",C2),(LEFT((A2/POWER(10,INT(LOG(A2))+1)),FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1))&RIGHT((A2/POWER(10,INT(LOG(A2))+1)),LEN((A2/POWER(10,INT(LOG(A2))+1)))-INT(ABS(LOG((A2/POWER(10,INT(LOG(A2))+1)))))-FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1))),1),1)=3,IF((LEFT((A2/POWER(10,INT(LOG(A2))+1)),FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1))&RIGHT((A2/POWER(10,INT(LOG(A2))+1)),LEN((A2/POWER(10,INT(LOG(A2))+1)))-INT(ABS(LOG((A2/POWER(10,INT(LOG(A2))+1)))))-FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1)))*1<("0."&REPT("9",C2)&"5")*1,("0."&REPT("0",INT(ABS(LOG((A2/POWER(10,INT(LOG(A2))+1))))))&REPT("9",C2)),("0." & REPT("0",INT(ABS(LOG((A2/POWER(10,INT(LOG(A2))+1)))))-1)&"1"& REPT("0",C2-1))),IF(OR(LEFT(RIGHT(LEFT((A2/POWER(10,INT(LOG(A2))+1)),FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1)) & RIGHT((A2/POWER(10,INT(LOG(A2))+1)),LEN((A2/POWER(10,INT(LOG(A2))+1)))-INT(ABS(LOG((A2/POWER(10,INT(LOG(A2))+1)))))-FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1)),LEN(LEFT((A2/POWER(10,INT(LOG(A2))+1)),FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1)) & RIGHT((A2/POWER(10,INT(LOG(A2))+1)),LEN((A2/POWER(10,INT(LOG(A2))+1)))-INT(ABS(LOG((A2/POWER(10,INT(LOG(A2))+1)))))-FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1)))-C2-2),1)*1<5,AND(5&REPT("0",LEN(RIGHT(LEFT((A2/POWER(10,INT(LOG(A2))+1)),FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1)) & RIGHT((A2/POWER(10,INT(LOG(A2))+1)),LEN((A2/POWER(10,INT(LOG(A2))+1)))-INT(ABS(LOG((A2/POWER(10,INT(LOG(A2))+1)))))-FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1)),LEN(LEFT((A2/POWER(10,INT(LOG(A2))+1)),FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1)) & RIGHT((A2/POWER(10,INT(LOG(A2))+1)),LEN((A2/POWER(10,INT(LOG(A2))+1)))-INT(ABS(LOG((A2/POWER(10,INT(LOG(A2))+1)))))-FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1)))-C2-2))-1)=RIGHT(LEFT((A2/POWER(10,INT(LOG(A2))+1)),FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1)) & RIGHT((A2/POWER(10,INT(LOG(A2))+1)),LEN((A2/POWER(10,INT(LOG(A2))+1)))-INT(ABS(LOG((A2/POWER(10,INT(LOG(A2))+1)))))-FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1)),LEN(LEFT((A2/POWER(10,INT(LOG(A2))+1)),FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1)) & RIGHT((A2/POWER(10,INT(LOG(A2))+1)),LEN((A2/POWER(10,INT(LOG(A2))+1)))-INT(ABS(LOG((A2/POWER(10,INT(LOG(A2))+1)))))-FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1)))-C2-2),ISEVEN(RIGHT(LEFT(LEFT((A2/POWER(10,INT(LOG(A2))+1)),FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1)) & RIGHT((A2/POWER(10,INT(LOG(A2))+1)),LEN((A2/POWER(10,INT(LOG(A2))+1)))-INT(ABS(LOG((A2/POWER(10,INT(LOG(A2))+1)))))-FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1)),C2+2),1)*1))),ROUNDDOWN(LEFT((A2/POWER(10,INT(LOG(A2))+1)),FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1)) & RIGHT((A2/POWER(10,INT(LOG(A2))+1)),LEN((A2/POWER(10,INT(LOG(A2))+1)))-INT(ABS(LOG((A2/POWER(10,INT(LOG(A2))+1)))))-FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1)),C2),ROUNDUP(LEFT((A2/POWER(10,INT(LOG(A2))+1)),FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1)) & RIGHT((A2/POWER(10,INT(LOG(A2))+1)),LEN((A2/POWER(10,INT(LOG(A2))+1)))-INT(ABS(LOG((A2/POWER(10,INT(LOG(A2))+1)))))-FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1)),C2))/POWER(10,INT(ABS(LOG((A2/POWER(10,INT(LOG(A2))+1)))))) & REPT("0",C2+INT(ABS(LOG((A2/POWER(10,INT(LOG(A2))+1)))))-IF(ROUNDUP(LEFT((A2/POWER(10,INT(LOG(A2))+1)),FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1)) & RIGHT((A2/POWER(10,INT(LOG(A2))+1)),LEN((A2/POWER(10,INT(LOG(A2))+1)))-INT(ABS(LOG((A2/POWER(10,INT(LOG(A2))+1)))))-FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1)),C2)=1,1,INT(ABS(LOG((A2/POWER(10,INT(LOG(A2))+1)))))-2)-LEN(IF(OR(LEFT(RIGHT(LEFT((A2/POWER(10,INT(LOG(A2))+1)),FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1)) & RIGHT((A2/POWER(10,INT(LOG(A2))+1)),LEN((A2/POWER(10,INT(LOG(A2))+1)))-INT(ABS(LOG((A2/POWER(10,INT(LOG(A2))+1)))))-FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1)),LEN(LEFT((A2/POWER(10,INT(LOG(A2))+1)),FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1)) & RIGHT((A2/POWER(10,INT(LOG(A2))+1)),LEN((A2/POWER(10,INT(LOG(A2))+1)))-INT(ABS(LOG((A2/POWER(10,INT(LOG(A2))+1)))))-FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1)))-C2-2),1)*1<5,AND(5&REPT("0",LEN(RIGHT(LEFT((A2/POWER(10,INT(LOG(A2))+1)),FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1)) & RIGHT((A2/POWER(10,INT(LOG(A2))+1)),LEN((A2/POWER(10,INT(LOG(A2))+1)))-INT(ABS(LOG((A2/POWER(10,INT(LOG(A2))+1)))))-FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1)),LEN(LEFT((A2/POWER(10,INT(LOG(A2))+1)),FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1)) & RIGHT((A2/POWER(10,INT(LOG(A2))+1)),LEN((A2/POWER(10,INT(LOG(A2))+1)))-INT(ABS(LOG((A2/POWER(10,INT(LOG(A2))+1)))))-FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1)))-C2-2))-1)=RIGHT(LEFT((A2/POWER(10,INT(LOG(A2))+1)),FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1)) & RIGHT((A2/POWER(10,INT(LOG(A2))+1)),LEN((A2/POWER(10,INT(LOG(A2))+1)))-INT(ABS(LOG((A2/POWER(10,INT(LOG(A2))+1)))))-FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1)),LEN(LEFT((A2/POWER(10,INT(LOG(A2))+1)),FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1)) & RIGHT((A2/POWER(10,INT(LOG(A2))+1)),LEN((A2/POWER(10,INT(LOG(A2))+1)))-INT(ABS(LOG((A2/POWER(10,INT(LOG(A2))+1)))))-FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1)))-C2-2),ISEVEN(RIGHT(LEFT(LEFT((A2/POWER(10,INT(LOG(A2))+1)),FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1)) & RIGHT((A2/POWER(10,INT(LOG(A2))+1)),LEN((A2/POWER(10,INT(LOG(A2))+1)))-INT(ABS(LOG((A2/POWER(10,INT(LOG(A2))+1)))))-FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1)),C2+2),1)*1))),ROUNDDOWN(LEFT((A2/POWER(10,INT(LOG(A2))+1)),FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1)) & RIGHT((A2/POWER(10,INT(LOG(A2))+1)),LEN((A2/POWER(10,INT(LOG(A2))+1)))-INT(ABS(LOG((A2/POWER(10,INT(LOG(A2))+1)))))-FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1)),C2),ROUNDUP(LEFT((A2/POWER(10,INT(LOG(A2))+1)),FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1)) & RIGHT((A2/POWER(10,INT(LOG(A2))+1)),LEN((A2/POWER(10,INT(LOG(A2))+1)))-INT(ABS(LOG((A2/POWER(10,INT(LOG(A2))+1)))))-FIND(".",(A2/POWER(10,INT(LOG(A2))+1)),1)),C2)))))))*POWER(10,INT(LOG(A2))+1)

myoliveryong 发表于 2023-2-25 20:04:36

wangyiyong7703 发表于 2023-2-25 16:04
大于1的公式，目前10的N次方没有弄，=IF(AND(IFERROR(FIND(REPT("9",C2),(A2/POWER(10,INT(LOG(A2))+1)),1) ...

你为啥要来计量，不去IT？还是从IT过来的？

Ming1212 发表于 2023-2-26 10:10:42

谢谢你的分享！

wangyiyong7703 发表于 2023-2-26 12:47:24

有效数位，补全A2输待修约值，B2输入有效数位，C2输入公式IF(IFERROR(FIND(".",A2,1),0)=0,"",RIGHT(A2,LEN(A2)-IFERROR(FIND(".",A2,1),0)))；D2输入公式：IF(INT(LOG(A2))<-1,(IF(TRUNC(A2)=0,"",TRUNC(A2))&C2)*1,IF(TRUNC(A2)=0,"",TRUNC(A2))&C2)；结果显示公式：IF(LEN((LEFT(D2,B2)+IF(OR(LEFT(RIGHT(D2,LEN(D2)-B2),1)*1<5,AND(ISEVEN(MID(D2,B2,1)),("5"&REPT("0",LEN(RIGHT(D2,LEN(D2)-B2))-1))-RIGHT(D2,LEN(D2)-B2)=0)),0,1)))=1,(LEFT(D2,B2)+IF(OR(LEFT(RIGHT(D2,LEN(D2)-B2),1)*1<5,AND(ISEVEN(MID(D2,B2,1)),("5"&REPT("0",LEN(RIGHT(D2,LEN(D2)-B2))-1))-RIGHT(D2,LEN(D2)-B2)=0)),0,1)),LEFT((LEFT(D2,B2)+IF(OR(LEFT(RIGHT(D2,LEN(D2)-B2),1)*1<5,AND(ISEVEN(MID(D2,B2,1)),("5"&REPT("0",LEN(RIGHT(D2,LEN(D2)-B2))-1))-RIGHT(D2,LEN(D2)-B2)=0)),0,1)),1)&"."&IF((LEN(LEFT(D2,B2))-LEN((LEFT(D2,B2)+IF(OR(LEFT(RIGHT(D2,LEN(D2)-B2),1)*1<5,AND(ISEVEN(MID(D2,B2,1)),("5"&REPT("0",LEN(RIGHT(D2,LEN(D2)-B2))-1))-RIGHT(D2,LEN(D2)-B2)=0)),0,1))))=0,RIGHT((LEFT(D2,B2)+IF(OR(LEFT(RIGHT(D2,LEN(D2)-B2),1)*1<5,AND(ISEVEN(MID(D2,B2,1)),("5"&REPT("0",LEN(RIGHT(D2,LEN(D2)-B2))-1))-RIGHT(D2,LEN(D2)-B2)=0)),0,1)),LEN((LEFT(D2,B2)+IF(OR(LEFT(RIGHT(D2,LEN(D2)-B2),1)*1<5,AND(ISEVEN(MID(D2,B2,1)),("5"&REPT("0",LEN(RIGHT(D2,LEN(D2)-B2))-1))-RIGHT(D2,LEN(D2)-B2)=0)),0,1)))-1),LEFT(RIGHT((LEFT(D2,B2)+IF(OR(LEFT(RIGHT(D2,LEN(D2)-B2),1)*1<5,AND(ISEVEN(MID(D2,B2,1)),("5"&REPT("0",LEN(RIGHT(D2,LEN(D2)-B2))-1))-RIGHT(D2,LEN(D2)-B2)=0)),0,1)),LEN((LEFT(D2,B2)+IF(OR(LEFT(RIGHT(D2,LEN(D2)-B2),1)*1<5,AND(ISEVEN(MID(D2,B2,1)),("5"&REPT("0",LEN(RIGHT(D2,LEN(D2)-B2))-1))-RIGHT(D2,LEN(D2)-B2)=0)),0,1)))-1),LEN(RIGHT((LEFT(D2,B2)+IF(OR(LEFT(RIGHT(D2,LEN(D2)-B2),1)*1<5,AND(ISEVEN(MID(D2,B2,1)),("5"&REPT("0",LEN(RIGHT(D2,LEN(D2)-B2))-1))-RIGHT(D2,LEN(D2)-B2)=0)),0,1)),LEN((LEFT(D2,B2)+IF(OR(LEFT(RIGHT(D2,LEN(D2)-B2),1)*1<5,AND(ISEVEN(MID(D2,B2,1)),("5"&REPT("0",LEN(RIGHT(D2,LEN(D2)-B2))-1))-RIGHT(D2,LEN(D2)-B2)=0)),0,1)))-1))-1))&IF(INT(LOG(A2))-(LEN(LEFT(D2,B2))-LEN((LEFT(D2,B2)+IF(OR(LEFT(RIGHT(D2,LEN(D2)-B2),1)*1<5,AND(ISEVEN(MID(D2,B2,1)),("5"&REPT("0",LEN(RIGHT(D2,LEN(D2)-B2))-1))-RIGHT(D2,LEN(D2)-B2)=0)),0,1))))=0,""," E "&INT(LOG(A2))-(LEN(LEFT(D2,B2))-LEN((LEFT(D2,B2)+IF(OR(LEFT(RIGHT(D2,LEN(D2)-B2),1)*1<5,AND(ISEVEN(MID(D2,B2,1)),("5"&REPT("0",LEN(RIGHT(D2,LEN(D2)-B2))-1))-RIGHT(D2,LEN(D2)-B2)=0)),0,1))))))

dxquan 发表于 2023-2-26 13:50:34

一个字“牛”

xuhuilai 发表于 2023-2-27 08:48:11

牛啊，大佬。

nn141008 发表于 2023-2-27 11:09:41

大写的牛！！
页: [1] 2
查看完整版本: 四舍六入五成双EXCEL实现