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

路云 發(fā)表于 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)))))

路云 發(fā)表于 2023-10-4 10:53

您確認(rèn)是用的我的公式。我這邊正常啊

用這個試試，都是對的

應(yīng)該是版本的問題

wangyiyong7703 發(fā)表于 2023-10-5 08:51
您確認(rèn)是用的我的公式。我這邊正常啊

您的A列的數(shù)據(jù)格式不是“數(shù)字”，而是“文本”，所以才會得出“正常”的假象。

另外，擬修約數(shù)和修約后的最終結(jié)果，還應(yīng)該根據(jù)實際需要，為其設(shè)置顯示格式，如：是否顯示正負(fù)號、小數(shù)位數(shù)，包括條件格式等(如7樓截圖)。

以下截圖示例中，E列用“4舍5入法”修約保留3位小數(shù)的修約結(jié)果；F列用“4舍6入5留雙法”(本人7樓提供的自定義函數(shù))修約保留3位小數(shù)的修約結(jié)果。G列和H列都是用本人提供的自定義函數(shù)修約，保留2位小數(shù)的最終修約結(jié)果。

1.這個當(dāng)然要用文本格式來弄，電子表格是智能的，默認(rèn)自動舍去后面的0，相當(dāng)于修約過，按正常的情況下，是不能這樣操作的。因為用文本格式是必須的。2.您的反例要符合正常的要求，全數(shù)的有效位要大于修約后的有效位數(shù)。這個可以改進，但沒有必要。您的幾個反例再弄多一點，經(jīng)典一點

路云 發(fā)表于 2023-10-6 08:51
您的A列的數(shù)據(jù)格式不是“數(shù)字”，而是“文本”，所以才會得出“正常”的假象。另外，擬修約數(shù)和修約后的最 ...

謝謝，后期還請您多提寶貴的反例

路云 發(fā)表于 2023-10-6 14:07
以下截圖示例中，E列用“4舍5入法”修約保留3位小數(shù)的修約結(jié)果；F列用“4舍6入5留雙法”(本人7樓提供的自定 ...

正常情況下，一些文獻提供的公式基本上都能滿足，可惜的是特殊情況下是不能滿足的。0.100000009，9.99999999.我的算法正是進行文本操作，避開了浮點的問題。

wangyiyong7703 發(fā)表于 2023-10-6 14:11
1.這個當(dāng)然要用文本格式來弄，電子表格是智能的，默認(rèn)自動舍去后面的0，相當(dāng)于修約過，按正常的情況下，是 ...

1.這個當(dāng)然要用文本格式來弄，電子表格是智能的，默認(rèn)自動舍去后面的0，相當(dāng)于修約過，按正常的情況下，是不能這樣操作的。因為用文本格式是必須的。

這個理由是不成立的。如果是“文本”格式，中間有可能夾雜了其他文本字符，您的公式照樣發(fā)現(xiàn)不了，如：

2.您的反例要符合正常的要求，全數(shù)的有效位要大于修約后的有效位數(shù)。

修約前的全數(shù)有效位數(shù)多少，完全取決于數(shù)據(jù)處理的結(jié)果(如：多次測量結(jié)果的平均值)，大于、小于、等于修約后的小數(shù)位數(shù)都是有可能的。

正常情況下，一些文獻提供的公式基本上都能滿足，可惜的是特殊情況下是不能滿足的。0.100000009，9.99999999.我的算法正是進行文本操作，避開了浮點的問題。

我提供的自定義函數(shù)，不存在浮點的問題。

路云 發(fā)表于 2023-10-6 14:37
1.這個當(dāng)然要用文本格式來弄，電子表格是智能的，默認(rèn)自動舍去后面的0，相當(dāng)于修約過，按正常的情況下， ...

您函數(shù)中有相雙精度計算，主要是沒有辦法補位，這個功能比較麻煩。不然的話是很簡單的

wangyiyong7703 發(fā)表于 2023-10-6 18:16
您函數(shù)中有相雙精度計算，主要是沒有辦法補位，這個功能比較麻煩。不然的話是很簡單的 ...

沒看明白什么叫“有相雙精度計算”？您說的“補位”是啥意思？我最初也是與您的想法一樣，想通過您這種方式實現(xiàn)。結(jié)果研究了很長時間，也沒有找到一種能夠完全自動識別與控制的方法，而且迭代、嵌套、交叉引用太多，無法做成“傻瓜型”，滿足現(xiàn)場檢測/校準(zhǔn)人員的錄入操作需求。最終還是用自定義函數(shù)方式解決問題，使過程大為簡化。最終要結(jié)合單元格格式、條件格式等設(shè)置，實現(xiàn)數(shù)據(jù)有效性自動識別、敲錄錯誤自動攔截提醒、自動生成符合性判定結(jié)論等功能。

這個也是很簡單的，f(x)是公式中要求得的結(jié)果，f(x) & "00000"即可，補0即可。我們把主函數(shù)寫出來，電腦修約過度然后根據(jù)情況進行補位即可。我們是要避開VB，只用內(nèi)建函數(shù)寫

路云 發(fā)表于 2023-10-6 19:08
沒看明白什么叫“有相雙精度計算”？您說的“補位”是啥意思？我最初也是與您的想法一樣，想通過您這種方 ...

還是要有一定的規(guī)范，不可能做一個適用所有情況。否則VB也做不過來。通過規(guī)范使用者的操作行為。簡化函數(shù)。

wangyiyong7703 發(fā)表于 2023-10-6 20:37
這個也是很簡單的，f(x)是公式中要求得的結(jié)果，f(x) & "00000"即可，補0即可。我們把主函數(shù)寫出來，電腦修 ...

這個也是很簡單的，f(x)是公式中要求得的結(jié)果，f(x) & “00000”即可，補0即可。我們把主函數(shù)寫出來，電腦修約過度然后根據(jù)情況進行補位即可。我們是要避開VB，只用內(nèi)建函數(shù)寫

不需要在公式中用“& 00000”方式補0，只需對修約后的測量結(jié)果所在單元格設(shè)置數(shù)字格式即可實現(xiàn)。

還是要有一定的規(guī)范，不可能做一個適用所有情況。否則VB也做不過來。通過規(guī)范使用者的操作行為。簡化函數(shù)。

通過VBA編制的“4舍6入5留雙修約自定義函數(shù)”，本來就是適用于所有數(shù)據(jù)按此修約規(guī)則的修約。就如同求平均值的內(nèi)部函數(shù)“AVERAGE( )”和求和函數(shù)“SUM( )”一樣，這么方便、高效的函數(shù)您不用，非要逐個數(shù)據(jù)采取加減乘除四則運算的辦法去求。我也實在是想不明白圖什么？

現(xiàn)場實際操作的檢測/校準(zhǔn)人員，對EXCEL的操作技能并不都十分精通，尤其對各種函數(shù)的應(yīng)用、單元格格式的設(shè)置、條件格式的應(yīng)用更是知之甚少，甚至一無所知。VBA本身就是微軟為個性化應(yīng)用提供的高效應(yīng)用工具，非常方便。我們不需要把VBA看得如臨大敵，談宏色變，沒那么可怕。我們的目的，就是要根據(jù)實際需要編制相應(yīng)的標(biāo)準(zhǔn)化《原始記錄》模板表單，讓所有不懂EXCEL操作人員，只要會敲入數(shù)據(jù)，經(jīng)過幾分鐘的簡單培訓(xùn)，就能夠掌握、編制、生成每一份數(shù)據(jù)統(tǒng)一、規(guī)范、美觀的《原始記錄》，而且不會出現(xiàn)誤操作(因為所有不讓操作人員編輯的單元格都做了屏蔽設(shè)置，未經(jīng)授權(quán)是點不進去的)。

一條一條的駁，高手高手。

路云 發(fā)表于 2023-10-6 22:41
這個也是很簡單的，f(x)是公式中要求得的結(jié)果，f(x) & “00000”即可，補0即可。我們把主函數(shù)寫出來，電腦 ...

麻煩您做一個不用VB的函數(shù)，或者完善一個您的函數(shù)，0.1000的結(jié)果顯示為0.1，我怎么也看不明白。您的函數(shù)中如果輸入項有字母，也能智能識別用作數(shù)值處理。我說的是我不想用VB這個宏功能。您能行么

補充內(nèi)容 (2023-10-9 14:46):
您 的公式行么

路云 發(fā)表于 2023-10-6 22:41
這個也是很簡單的，f(x)是公式中要求得的結(jié)果，f(x) & “00000”即可，補0即可。我們把主函數(shù)寫出來，電腦 ...

那麻煩您把你的單機板的excel文件傳到壇子里面，看能否用。我們學(xué)習(xí)學(xué)習(xí)

修約表現(xiàn)，兩個公式都有問題，表現(xiàn)不一樣。16-18位，24-27位有異常。VB自定義的公式10位以后就開始一直不中，但要配合高端操作設(shè)置格式

wangyiyong7703 發(fā)表于 2023-10-7 15:39
修約表現(xiàn)，兩個公式都有問題，表現(xiàn)不一樣。16-18位，24-27位有異常。VB自定義的公式10位以后就開始一直不中 ...

1、我的自定義函數(shù)(見7樓)您不是已經(jīng)試用了嗎，單機版與網(wǎng)絡(luò)版都能用，只是要將文件另存為“Excel啟用宏的模板(*.xltm)”即可。

2、對于單元格來說，其默認(rèn)的數(shù)字格式是“G/通用格式”(即“常規(guī)”格式)，這種“常規(guī)”格式，其默認(rèn)的有效顯示的數(shù)字位數(shù)只能10位。鍵入的數(shù)值位數(shù)超過15位時則會截尾，且數(shù)值在10^-10～10¹⁰時，最多顯示前10位。若數(shù)值大于10¹⁰或小于10^-10時，則以科學(xué)計數(shù)法顯示。所以必須配合單元格的數(shù)字格式設(shè)置(即：將數(shù)字格式設(shè)置為“自定義”格式)，才能正常顯示。

3、除了科學(xué)研究，一般的檢測/校準(zhǔn)領(lǐng)域，基本上不會遇到需要保留10位及以上“有效數(shù)字”的情形，所以該自定義函數(shù)在10^-10～10¹⁰數(shù)值范圍內(nèi)應(yīng)用，是沒有任何問題的。如果真有這種需求，那恐怕就不是VBA宏能夠解決的了。

4、您是將數(shù)值轉(zhuǎn)換成文本來處理，修約結(jié)果也是文本，無法顯示正數(shù)的符號“+”。且數(shù)字中夾雜有其他字符，也同樣會在修約結(jié)果中顯示。所以這種處理方式實質(zhì)上是“文本字符的截取與組合”。

路云 發(fā)表于 2023-10-8 17:22
1、我的自定義函數(shù)(見7樓)您不是已經(jīng)試用了嗎，單機版與網(wǎng)絡(luò)版都能用，只是要將文件另存為“Excel啟用宏的 ...

還是不了解。您的自定義函數(shù)盡然比不上內(nèi)建函數(shù)的表現(xiàn)。還說vbA解決不了這個問題，您敢打賭么，解決了怎么辦

wangyiyong7703 發(fā)表于 2023-10-8 18:03
還是不了解。您的自定義函數(shù)盡然比不上內(nèi)建函數(shù)的表現(xiàn)。還說vbA解決不了這個問題，您敢打賭么，解決了怎 ...

打賭就沒有意思了。我只是說“恐怕就不是VBA宏能夠解決的了”。EXCEL的數(shù)值處理范圍區(qū)間大小是有限的。您所提供的公式中，除了“POWER()”是數(shù)學(xué)類“工作表函數(shù)”外，基本上全是文字處理類或邏輯判斷類的“工作表函數(shù)”，而不是數(shù)值處理的內(nèi)建函數(shù)。

本主題討論的是數(shù)據(jù)(非文本數(shù)據(jù))的修約問題，EXCEL中與數(shù)值修約相關(guān)的，就只有“ROUND()”、“ROUNDDOWN()”和“ROUNDUP()”三個“工作表函數(shù)”。最為接近的就是“ROUND()”函數(shù)，僅僅是修約規(guī)則有那么一點點差異。“ROUND()”函數(shù)的修約規(guī)則是“四舍五入”，自定義函數(shù)的修約規(guī)則是“四舍六入五留雙”。

其實“ROUND()”函數(shù)有兩個，一個就是“工作表函數(shù)”(即您所說的“內(nèi)建函數(shù)”)，另一個是“VB函數(shù)”，盡管這兩個函數(shù)名稱一模一樣，但兩者的算法規(guī)則是不同的。

路云 發(fā)表于 2023-10-8 17:22
1、我的自定義函數(shù)(見7樓)您不是已經(jīng)試用了嗎，單機版與網(wǎng)絡(luò)版都能用，只是要將文件另存為“Excel啟用宏的 ...

1.麻煩您舉一些實質(zhì)性的問題進行討論，這樣很累，沒有意義，之前您找到的很多有意義的反例很好；
2.純數(shù)字文本也可以參與其它計算，強制進行轉(zhuǎn)換。這里解決的是尾數(shù)補０的情況，用文本表示是不得已的事。
3.修約后尾數(shù)0后，系統(tǒng)自動去除，您的自定義函數(shù)是一個致命的問題。如果靠設(shè)置去解決，那您的公式確實看起來很好。
4.但是實際上不用設(shè)置格式，如果要用vb的話，徹底解決位數(shù)過大的問題。VB輕松解決。
5.你用設(shè)置格式解決尾數(shù)補0問題，確實不是很高端，我也是想避開。其實有很多內(nèi)建函數(shù)配合格式設(shè)置可以做到，完全不用ＶＢ，這個早就實現(xiàn)了。
6.發(fā)揮內(nèi)建函數(shù)作用，不靠其他方法是我追求的。因為您用vb做的自定義函數(shù)沒多大意思，我實不屑取，避開vb,才是我們想要的。用vb超百位的修約我也可以做到,早就做到了。