保留位數(shù)EXCEL實(shí)現(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)是用的我的公式。我這邊正常啊

用這個(gè)試試，都是對(duì)的

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

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

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

另外，擬修約數(shù)和修約后的最終結(jié)果，還應(yīng)該根據(jù)實(shí)際需要，為其設(shè)置顯示格式，如：是否顯示正負(fù)號(hào)、小數(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.這個(gè)當(dāng)然要用文本格式來(lái)弄，電子表格是智能的，默認(rèn)自動(dòng)舍去后面的0，相當(dāng)于修約過(guò)，按正常的情況下，是不能這樣操作的。因?yàn)橛梦谋靖袷绞潜仨毜摹?.您的反例要符合正常的要求，全數(shù)的有效位要大于修約后的有效位數(shù)。這個(gè)可以改進(jìn)，但沒(méi)有必要。您的幾個(gè)反例再弄多一點(diǎn)，經(jīng)典一點(diǎn)

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

謝謝，后期還請(qǐng)您多提寶貴的反例

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

正常情況下，一些文獻(xiàn)提供的公式基本上都能滿足，可惜的是特殊情況下是不能滿足的。0.100000009，9.99999999.我的算法正是進(jìn)行文本操作，避開(kāi)了浮點(diǎn)的問(wèn)題。

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

1.這個(gè)當(dāng)然要用文本格式來(lái)弄，電子表格是智能的，默認(rèn)自動(dòng)舍去后面的0，相當(dāng)于修約過(guò)，按正常的情況下，是不能這樣操作的。因?yàn)橛梦谋靖袷绞潜仨毜摹?/font>

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

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

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

正常情況下，一些文獻(xiàn)提供的公式基本上都能滿足，可惜的是特殊情況下是不能滿足的。0.100000009，9.99999999.我的算法正是進(jìn)行文本操作，避開(kāi)了浮點(diǎn)的問(wèn)題。

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

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

您函數(shù)中有相雙精度計(jì)算，主要是沒(méi)有辦法補(bǔ)位，這個(gè)功能比較麻煩。不然的話是很簡(jiǎn)單的

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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