时间戳

首页 > 解决方案 > 有没有办法在一个单元格中执行 5 种不同的数字格式或编写 VBA 脚本以根据值生成数字格式？

excel - 有没有办法在一个单元格中执行 5 种不同的数字格式或编写 VBA 脚本以根据值生成数字格式？

问题描述

我生成的数据必须具有特定的数字格式，这取决于数字的大小。

数字 >=100 不应该有任何小数位，>=10 应该有一个小数位（即使它是零），>=1 应该有两个小数位（即使最后一个是零），0.999-0.001应该有三个小数位（即使最后一个是零），并且 0.0009-0.0001 应该有四个小数位，同样不管最后一个小数位的数字是多少。

所以本质上我需要一个代码或方法来查看一个数字并给我那些特定的小数位。我知道我可以单独格式化它们，但我们正在谈论我要格式化的成千上万的数字。我试过使用 round 函数，但它不会给我尾随零。

这是我目前在电子表格上使用的函数，它适用于除尾随零之外的所有内容：

=IF(VLOOKUP($A$21,'Copied Data'!$A$15:$FL$140,23)>'Copied Data'!U$7,IF('Copied Data'!W16<0.001,ROUND('Copied Data'!W16,4),IF('Copied Data'!W16>=0.001,ROUND('Copied Data'!W16,3),IF('Copied Data'!W16>0.999,ROUND('Copied Data'!W16,2),IF('Copied Data'!W16>9.999,ROUND('Copied Data'!W16,1),IF('Copied Data'!W16>99.999,ROUND('Copied Data'!W16,0)," "))))),"<"&IF('Copied Data'!U$7<0.001,ROUND('Copied Data'!U$7,4),IF('Copied Data'!U$7>=0.0001,ROUND('Copied Data'!U$7,3),IF('Copied Data'!U$7>0.999,ROUND('Copied Data'!U$7,2),IF('Copied Data'!U$7>9.999,ROUND('Copied Data'!U$7,1),IF('Copied Data'!U$7>99.999,ROUND('Copied Data'!U$7,0)," "))))))

如果有更好的方法来做到这一点，或者在 VBA 中为它编写宏，那就太好了！谢谢你。

标签： excelvbadecimalnumber-formatting

解决方案

像这样的东西将处理修饰符：

Dim rng As Range, c As Range, op, v

Set rng = ActiveSheet.Range("A1:A100") 'for example, or = Selection

For Each c In rng.Cells
    v = Trim(c.Value)
    If Len(v) > 0 Then
        'if the value has a modifier, remove it
        op = ""
        If v Like ">*" Or v Like "<*" Then
            op = Left(v, 1)
            v = Trim(Right(v, Len(v) - 1))
        End If
    
        If IsNumeric(v) Then
            v = CDbl(v)
            If v > 0.0001 And v <= 0.00099 Then
                c.Value = v
                c.NumberFormat = op & "[format1]" 'include any modifier in the format
            ElseIf v > 0.001 And v <= 0.999 Then
                c.Value = v
                c.NumberFormat = op & "[format2]"
            Else
                'you should probably include a general format so all
                ' cells which might have modifiers get the same treatment
            End If
            'add other cases as needed
        End If
    End If

Next c

其中 [format1] 等是您从宏录制中获得的格式

请注意，此后任何修饰符都不再是单元格值的一部分 - 它们仅以单元格格式存在。这意味着您可以将单元格作为数字使用，但取决于您最终需要做什么，它可能合适也可能不合适。

如果您只想更改单元格值的数字部分的格式，那么您可以执行以下操作：

c.Value = Application.Text(v, op & "0.0000")

推荐阅读