保留位數(shù)EXCEL實(shí)現(xiàn)

A2輸入待修約數(shù)字，B2輸入保留小數(shù)點(diǎn)位數(shù)，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這種情況不適用，還待改進(jìn)

點(diǎn)個贊，點(diǎn)個贊，點(diǎn)個贊，點(diǎn)個贊。

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

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

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

感謝分享，下載看看

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

其實(shí)沒那么復(fù)雜，只要建一個自定義函數(shù)，應(yīng)用時直接調(diào)用即可。

4舍6入5留雙自定義函數(shù)：

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

        Yround=Round(CDec(a),d)

End Function

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

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

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

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

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

我們的目標(biāo)是避開宏代碼，完全用內(nèi)建函數(shù)實(shí)現(xiàn)

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)))))

代碼進(jìn)行了優(yōu)化
適應(yīng)范圍更廣，不用宏代碼，完全避開了自定義函數(shù)和程序用到宏代碼。

從0.0001到100.0000，全部合格，保留2位小數(shù)

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

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

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

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

公式出現(xiàn)浮點(diǎn)問題，正在改進(jìn)，新的方法有點(diǎn)問題，

邏輯沒有問題，避開浮點(diǎn)算法已經(jīng)找到

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

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

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

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

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))))) 適合負(fù)數(shù)修約

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

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

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

單機(jī)版的麻煩，您的網(wǎng)絡(luò)版加持當(dāng)然可以。

wangyiyong7703 發(fā)表于 2023-10-3 07:48
單機(jī)版的麻煩，您的網(wǎng)絡(luò)版加持當(dāng)然可以。

單機(jī)版的也一樣，又不要用戶去編輯宏代碼，就像調(diào)用普通內(nèi)部函數(shù)一樣。

那您發(fā)一個自定義的函數(shù)，我用二十多年了，沒見過

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

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