保留位數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超百位的修約我也可以做到,早就做到了。