如何在excel表中将数据设置为3位有效数据?

发布网友发布时间：2022-04-23 05:56

共2个回答

热心网友时间：2023-07-02 01:08

在Excel提供的"数学与三角函数"中提供了一个名为ROUND(number,num_digits)的函数，它的功能就是根据指定的位数，将数字四舍五入。这个函数有两个参数，分别是number和num_digits。其中number就是将要进行四舍五入的数字；num_digits则是希望得到的数字的小数点后的位数。
例如：单元格B2中为初始数据0.123456，B3的初始数据为0.234567，将要对它们进行四舍五入。在单元格C2中输入"=ROUND(B2,2)"，小数点后保留两位有效数字，得到0.12、0.23。在单元格D2中输入"=ROUND(B2,4)"，则小数点保留四位有效数字，得到0.1235、0.2346。

热心网友时间：2023-07-02 01:08

你可以看看下面这篇文章，应该是很复杂的一件事。1、工作表函数解法：
名称定义
NUM=解法!$A3

DIG=解法!$H$2

TRN=解法!$G$2

OFF=ROUND(ABS(NUM),-(INT(LOG(ABS(NUM)))-DIG+1))-(--RIGHT(NUM/10^(INT(LOG(ABS(NUM)))-DIG+1),2)=0.5)*(MOD(RIGHT(INT(ABS(NUM)/10^(INT(LOG(ABS(NUM)))-DIG+1)),1),2)=0)*10^INT(LOG(ABS(NUM))-DIG+1)

PLS=(10^INT(LOG(OFF))=OFF)*(OFF>ABS(NUM))*TRN
RST=IF(NUM=0,"0",IF(SIGN(NUM)=-1,"-","")&TEXT(OFF,"0"&IF((DIG=1)*(INT(LOG(OFF))=0)*(PLS=0),"",IF((INT(OFF)=OFF)*(DIG=1)*(PLS=0),"",".")&REPT("0",DIG+PLS-1))&IF(LOG(OFF)<1,REPT("0",-INT(LOG(OFF))),"E+###")))
2、自定义函数解法
'ROUND2(数值,保留有效位数,返回文本或数值,遇进位时增加有效位开关）

Function Round2(Num As Double, DIG As Byte, Optional TorV As Boolean, Optional Trn As Boolean) As Variant
Dim Temp1 As Double
Dim TFM As String
Dim Temp2 As String
Dim Tempoff As Double
'-----------------------------------------------
' 鉴于vba中的round与工作表的round不同，这里
' 使用工作表中的round，因为vba中的round有问
' 题。vba中特别同时保留两个round应该是有目的吧，
' 其他函数或操作符一般只有一个
'-----------------------------------------------
If Num = 0 Then
Temp1 = 0
Temp2 = "0"
GoTo ExitFn
End If

With Application.WorksheetFunction

Tempoff = Abs((--Right(Num / 10 ^ (Int(.Log(Abs(Num))) - DIG + 1), 2) = 0.5) _
* ((--Right(Int(Abs(Num) / 10 ^ (Int(.Log(Abs(Num))) - DIG + 1)), 1) _
Mod 2) = 0)) * 10 ^ Int(.Log(Abs(Num)) - DIG + 1)

Temp1 = .Round(Abs(Num), -(Int(.Log(Abs(Num))) - DIG + 1))
Temp1 = Temp1 - Tempoff

Trn = Trn And (10 ^ Int(.Log(Temp1)) = Temp1 And Temp1 > Abs(Num))

If DIG > 14 And Trn Then
Temp2 = "有效位数超过14位不能进位"
GoTo ExitFn
End If

If DIG = 1 And Int(.Log(Abs(Temp1))) = 0 And Not Trn Then
TFM = ""
Else
If Not (DIG = 1 And Int(Temp1) = Temp1 And Not Trn) Then TFM = TFM & "."
TFM = TFM & .Rept("0", DIG + Abs(Trn) - 1)
End If

TFM = "0" & TFM

If Int(.Log(Temp1)) < 0 Then
TFM = TFM & .Rept("0", -Int(.Log(Temp1)))
ElseIf Int(.Log(Temp1)) > 0 Then
TFM = TFM & "E+###"
End If

Temp1 = Temp1 * Sgn(Num)
Temp2 = .Text(Temp1, TFM)
End With

ExitFn:
If TorV Then
Round2 = Temp2
Else
Round2 = Temp1
End If
End Function 我还有一个excel文件，你加我QQ，我传给你，55993721