保留位數EXCEL實現

A2輸入待修約數字，B2輸入保留小數點位數，C2輸入公式

IF(B2=0,IF(OR(AND(ISEVEN(INT(A2)),A2-INT(A2)=0.5),A2-INT(A2)<0.5),INT(A2),INT(A2)+1),IF(LEN(IF(OR(AND(ISEVEN(INT(A2*POWER(10,B2))),A2*POWER(10,B2)-INT(A2*POWER(10,B2))=0.5),A2*POWER(10,B2)-INT(A2*POWER(10,B2))<0.5),INT(A2*POWER(10,B2))/POWER(10,B2),(INT(A2*POWER(10,B2))+1)/POWER(10,B2)))=LEN(INT(A2)),IF(OR(AND(ISEVEN(INT(A2*POWER(10,B2))),A2*POWER(10,B2)-INT(A2*POWER(10,B2))=0.5),A2*POWER(10,B2)-INT(A2*POWER(10,B2))<0.5),INT(A2*POWER(10,B2))/POWER(10,B2),(INT(A2*POWER(10,B2))+1)/POWER(10,B2))&"."&REPT("0",B2),IF(LEN(INT(A2*POWER(10,B2)))+1>LEN((IF(OR(AND(ISEVEN(INT(A2*POWER(10,B2))),A2*POWER(10,B2)-INT(A2*POWER(10,B2))=0.5),A2*POWER(10,B2)-INT(A2*POWER(10,B2))<0.5),INT(A2*POWER(10,B2))/POWER(10,B2),(INT(A2*POWER(10,B2))+1)/POWER(10,B2)))),(IF(OR(AND(ISEVEN(INT(A2*POWER(10,B2))),A2*POWER(10,B2)-INT(A2*POWER(10,B2))=0.5),A2*POWER(10,B2)-INT(A2*POWER(10,B2))<0.5),INT(A2*POWER(10,B2))/POWER(10,B2),(INT(A2*POWER(10,B2))+1)/POWER(10,B2)))&REPT("0",LEN(INT(A2*POWER(10,B2)))-1-LEN((IF(OR(AND(ISEVEN(INT(A2*POWER(10,B2))),A2*POWER(10,B2)-INT(A2*POWER(10,B2))=0.5),A2*POWER(10,B2)-INT(A2*POWER(10,B2))<0.5),INT(A2*POWER(10,B2))/POWER(10,B2),(INT(A2*POWER(10,B2))+1)/POWER(10,B2))))),IF(OR(AND(ISEVEN(INT(A2*POWER(10,B2))),A2*POWER(10,B2)-INT(A2*POWER(10,B2))=0.5),A2*POWER(10,B2)-INT(A2*POWER(10,B2))<0.5),INT(A2*POWER(10,B2))/POWER(10,B2),(INT(A2*POWER(10,B2))+1)/POWER(10,B2)))))

對9.99999這種情況不適用，還待改進

點個贊，點個贊，點個贊，點個贊。

=IF(MOD(ABS(R5*POWER(10,Y5)),2)<=0.5,ROUNDDOWN(R5,Y5),ROUND(R5,Y5))   
不用那么麻煩吧我用的是這個

小阿飛飛 發表于 2023-9-28 15:34
=IF(MOD(ABS(R5*POWER(10,Y5)),2)

顯然主邏輯程序是對的，但你要考慮特殊情況，在特殊情況下如0.999995，0.100005，您還能得出您想要的答案么。這個問題的難點就是特殊情況BUG，不考慮特殊情況，答案很多。還有浮點問題。好多。但怎樣規避是個問題。

感謝分享，下載看看

wangyiyong7703 發表于 2023-9-29 12:13
顯然主邏輯程序是對的，但你要考慮特殊情況，在特殊情況下如0.999995，0.100005，您還能得出您想要的答案 ...

其實沒那么復雜，只要建一個自定義函數，應用時直接調用即可。

4舍6入5留雙自定義函數：

Function Yround(ByVal a As Double, Optional ByVal d As integer)

        Yround=Round(CDec(a),d)

End Function

注：參數a為傳值型雙精度變量(擬修約數)，參數d為可選整型參數(需保留的小數位數)。

如您1樓的例子，只需在A2單元格輸入擬修約數，B2單元格輸入欲保留的小數位數，在C2單元格中直接調用自定義函數(輸入“=Yround(A2, B2)”)即可得到修約結果。無論A2單元格的擬修約數是正數還是負數，都會按自動按照“4舍6入5留雙”的修約規則進行修約。

路云 發表于 2023-9-30 23:16
其實沒那么復雜，只要建一個自定義函數，應用時直接調用即可。4舍6入5留雙自定義函數：Function Yround(ByV ...

自定義函數也用到宏代碼，不能這樣操作，一般人用起來不方便

路云 發表于 2023-9-30 23:16
其實沒那么復雜，只要建一個自定義函數，應用時直接調用即可。4舍6入5留雙自定義函數：Function Yround(ByV ...

我們的目標是避開宏代碼，完全用內建函數實現

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

代碼進行了優化
適應范圍更廣，不用宏代碼，完全避開了自定義函數和程序用到宏代碼。

從0.0001到100.0000，全部合格，保留2位小數

wangyiyong7703 發表于 2023-10-1 14:12
自定義函數也用到宏代碼，不能這樣操作，一般人用起來不方便

建立自定義函數，目的就是要讓普通用戶能用起來呀。用時就像內部函數一樣調用即可。你鍵入那么復雜的函數都能用，難道鍵入“＝Yroug(A2, B2)”還更難？

您這個公式我試了一下，存在以下問題：

-1.035000000，修約結果沒有按照“4舍6入5留雙”的修約規則修約至-1.04，而是修約至-1.03。

公式出現浮點問題，正在改進，新的方法有點問題，

邏輯沒有問題，避開浮點算法已經找到

路云 發表于 2023-10-1 20:30
建立自定義函數，目的就是要讓普通用戶能用起來呀。用時就像內部函數一樣調用即可。你鍵入那么復雜的函數 ...

然后有時候是正常的，兩種情況都有

路云 發表于 2023-10-1 20:30
建立自定義函數，目的就是要讓普通用戶能用起來呀。用時就像內部函數一樣調用即可。你鍵入那么復雜的函數 ...

沒有那容易，必須啟用宏，如果啟用宏，會帶來很多麻煩

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+1/POWER(10,B2),1),0)=0,LEFT(A2,FIND(".",A2,1)*1+B2)*1+1/POWER(10,B2)&"."&REPT("0",B2),LEFT(A2,FIND(".",A2,1)*1+B2)*1+1/POWER(10,B2)&REPT("0",B2-(LEN(LEFT(A2,FIND(".",A2,1)*1+B2)*1+1/POWER(10,B2))-IFERROR(FIND(".",LEFT(A2,FIND(".",A2,1)*1+B2)*1+1/POWER(10,B2),1),0)))))

=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+1/POWER(10,B2),1),0)=0,LEFT(A2,FIND(".",A2,1)*1+B2)*1+1/POWER(10,B2)&"."&REPT("0",B2),LEFT(A2,FIND(".",A2,1)*1+B2)*1+1/POWER(10,B2)&REPT("0",B2-(LEN(LEFT(A2,FIND(".",A2,1)*1+B2)*1+1/POWER(10,B2))-IFERROR(FIND(".",LEFT(A2,FIND(".",A2,1)*1+B2)*1+1/POWER(10,B2),1),0))))) 用的新的算法歡迎指正

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))))) 適合負數修約

wangyiyong7703 發表于 2023-10-1 21:41
沒有那容易，必須啟用宏，如果啟用宏，會帶來很多麻煩

不明白您說的很多麻煩到底是什么麻煩。我們單位在LIMS系統中應用這么多年，從來沒有聽說過遇到什么麻煩。網絡版office安裝時就已經進行了安全設置，自定義函數都放在安全可信路徑下，原始記錄等都已制作成了智能型空白模板，試驗/檢測人員調用模板生成原始記錄的操作非常簡單，對于選擇項只需點擊下拉列表框、復選框、控件等進行選擇，只需錄入檢測數據，所有的數據處理和結果都是自動完成，試驗/檢測人員根本沒有感覺，也無需其他任何操作，完全做成了一種“傻瓜型”的智能Excel模板。

路云 發表于 2023-10-2 22:45
不明白您說的很多麻煩到底是什么麻煩。我們單位在LIMS系統中應用這么多年，從來沒有聽說過遇到什么麻煩。 ...

單機版的麻煩，您的網絡版加持當然可以。

wangyiyong7703 發表于 2023-10-3 07:48
單機版的麻煩，您的網絡版加持當然可以。

單機版的也一樣，又不要用戶去編輯宏代碼，就像調用普通內部函數一樣。

那您發一個自定義的函數，我用二十多年了，沒見過

wangyiyong7703 發表于 2023-10-3 13:53
那您發一個自定義的函數，我用二十多年了，沒見過

我說的“用戶”，不是指OFFICE用戶，而是指具體填寫原始記錄的檢測/校準人員。如果見過，那就不叫“自定義函數”了。