如何使excel显示一定数量的有效数字? [英] How to get excel to display a certain number of significant figures?

问题描述

我正在使用excel，并且希望将数值显示为一定数量的有效数字.

I am using excel and i want to display a value to a certain number of significant figures.

我尝试使用以下方程式

=ROUND(value,sigfigs-1-INT(LOG10(ABS(value))))

将值替换为我正在使用的数字，并将sigfigs替换为我想要的有效数字.

with value replaced by the number I am using and sigfigs replaced with the number of significant figures I want.

该公式有时有效，但其他时候却无效.

This formula works sometimes, but other times it doesn't.

例如，值18.036将更改为18，其中有2位有效数字.解决此问题的方法是将源格式更改为保留小数点后一位.但这可能会引入一个额外的数字.例如，如果结果为182，然后将小数位更改为182.0，那么现在我将有4个信号图，而不是3个.

For instance, the value 18.036, will change to 18, which has 2 significant figures. The way around this is to change the source formatting to retain 1 decimal place. But that can introduce an extra significant figure. For instance, if the result was 182 and then the decimal place made it change to 182.0, now I would have 4 sig figs instead of 3.

我如何获得Excel来为我设置信号图的数量，这样我就不必手动找出数字了?

How do I get excel to set the number of sig figs for me so I don't have to figure it out manually?

推荐答案

公式(A2包含值，B2 sigfigs)

The formula (A2 contains the value and B2 sigfigs)

=ROUND(A2/10^(INT(LOG10(A2))+1),B2)*10^(INT(LOG10(A2))+1)

可能会给您您想要的号码，例如C2.但是，如果最后一位为零，则不会以常规格式显示.然后，您必须通过VBA应用特定于该组合的数字格式(值，sigfigs).以下应该工作.您必须传递三个参数(val,sigd,trg)，trg是要格式化的目标单元格，您已经在其中有了所需的数字.

may give you the number you want, say, in C2. But if the last digit is zero, then it will not be shown with a General format. You have then to apply a number format specific for that combination (value,sigfigs), and that is via VBA. The following should work. You have to pass three parameters (val,sigd,trg), trg is the target cell to format, where you already have the number you want.

Sub fmt(val As Range, sigd As Range, trg As Range)
    Dim fmtstr As String, fmtstrfrac As String
    Dim nint As Integer, nfrac As Integer
    nint = Int(Log(val) / Log(10)) + 1
    nfrac = sigd - nint
    If (sigd - nint) > 0 Then
      'fmtstrfrac = "." & WorksheetFunction.Rept("0", nfrac)
      fmtstrfrac = "." & String(nfrac, "0")
    Else
      fmtstrfrac = ""
    End If
    'fmtstr = WorksheetFunction.Rept("0", nint) & fmtstrfrac
    fmtstr = String(nint, "0") & fmtstrfrac
    trg.NumberFormat = fmtstr
End Sub

如果您不介意使用字符串而不是数字，则可以将格式字符串(例如D2)作为

If you don't mind having a string instead of a number, then you can get the format string (in, say, D2) as

=REPT("0",INT(LOG10(A2))+1)&IF(B2-(INT(LOG10(A2))+1)>0,"."&REPT("0",B2-(INT(LOG10(A2))+1)),"")

(这将复制VBA代码)，然后使用(例如，在E2中)

(this replicates the VBA code) and then use (in, say, E2)

=TEXT(C2,D2).

其中单元格C2仍具有上述公式.您可以将单元格E2用于可视化，如果需要，可以将C2中获得的数字用于其他数学运算.

where cell C2 still has the formula above. You may use cell E2 for visualization purposes, and the number obtained in C2 for other math, if needed.

这篇关于如何使excel显示一定数量的有效数字?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！