excel数字格式-可变的十进制数字

Question

我试图用以下规则设置一个特殊的单元格号格式：

• 显示百分比
• 最多显示3位数字(十进制+整数部分)

所以我可以显示100%、99.3%或1.27%，但不能显示100.9%、100.27%或99.27%。

当然，我可以有负数(-27.3%)，这不影响我的规则。

我尝试使用单元格形成选项，但没有成功：

[<1]0.00%;[<10]0.0%;0%

因为excel (2010)似乎不支持两个以上的单元格形成条件(所以我不能将它扩展到管理负数.)

我想怎么做就怎么做？

谢谢

Answer 1

在功能区的Home选项卡下，选择条件格式：

​

然后选择“新规则”或“管理规则”(对于后者，您可以选择“新规则”，但也可以对所有当前规则进行概述)

然后选择，使用一个公式来确定格式化的单元格，并输入引用单元格本身的公式(在我的示例中，选择了单元格A1，取$ out以允许将其应用于其他单元格本身！)：

​

​

现在点击格式..。并选择所需的数字格式为百分比，并按您的需要选择小数位数。

对于所有想要区分的情况，请重复这一步骤。

由于值可能为负值，所以我总是使用ABS()来测试单元格绝对值的规则。

注意，您可以让所有规则适用于一个双边限制值范围(在我的示例中，最小值为0.1，最大值为1(分别为10%和100% )。或者，您只能确定最小或最大值，并对所涉及的所有规则的右端的Stof If True复选框进行勾选。

Answer 2

通过在VBA中实现一个函数，我有一个相当原始的解决方案：

Function formatStr(myVal As Double) As String
' the smaller the number, the more digits are to be shown
Dim retStr      As String
Dim absVal      As Double

    absVal = Abs(myVal)

    If absVal >= 100 Then
        retStr = "0"
    ElseIf absVal >= 10 Then retStr = "0.0"
    ElseIf absVal >= 1 Then retStr = "0.00"
    Else ' number is between -1 and 1
        retStr = ".000"
    End If

    formatStr = retStr
End Function

然后，可以在这样的VBA语句中使用...which：

   myVal = 0.5555: ActiveSheet.Cells(27, 4).Value = Format(myVal, formatStr(myVal))

如果要在工作表单元格中使用该功能，则需要另一个小函数。

  Function FormatAlignDigits(myVal As Double) As Double
     FormatAlignDigits = Format(myVal, formatStr(myVal))
  End Function

您可以在需要结果的单元格中输入=FormatAlignDigits(B27)。B27是本例中具有源数据的单元格。

我的测试结果：

100.3 => 100

100.5 => 101

10.53 => 10.5

10.55 => 10.6

1.553 => 1.55

1.555 => 1.56

0.5553 => 0.555

0.5555 => 0.556

-0.5555 => -0.556

-0.5553 => -0.555

-1.555 => -1.56

-1.553 => -1.55

-10.55 => -10.6

-10.53 => -10.5

-100.5 => -101

-100.3 => -100

我确信会有更好的解决方案，例如，函数“formatStr”的参数可以告诉数字的个数减少到0(这里: power = 2；如果值为>= 100)。对我来说，这个原始的东西运作得很好。

Answer 3

我最近解决了这个问题，但配置了多达6个小数点。请求如下：

我们希望以与用户输入的方式相同的方式显示一个数字，但不允许超过6个小数：

• 1个-> 1
• 1,1 ->，1
• 1,12 -> 1,12
• ..。
• 1 123456 -> 1 123456
• 1,1234567 -> 1,123457 (从这一点开始)

这可以使用条件格式和从整数开始的每小数点一条规则来解决。整数是一个没有小数的数字，所以是X MOD 1 = 0。我们可以应用这个逻辑，把这个数乘以10^N，就是我们想要的小数N。此外，当我们检测到单元格值中的小数时，我们希望停止应用规则，因此请确保您标记了"Stop if True“标志，并且规则的顺序是从限制性最强的(整数)到限制较小的(表示允许的最大小数的)。

总之，您必须配置以下方式(在我为B列所做的示例中)：

• 整数：=MOD(B2;1)=0
• 一小数点：=MOD(B2*10;1)=0
• 两个小数：=MOD(B2*100;1)=0
• ..。
• 六个小数点：=MOD(B2*1000000;1)=0

​

​

​

​

还将单元格的默认格式设置为具有支持的最大小数的数字：

​

​

最后给出了Excel的工作结果：

​

​