是否有办法在一个单元格中做5种不同的数字格式或编写VBA脚本以产生基于值的数字格式?

Question

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

数字>=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中为它编写宏的方法，那将是伟大的!谢谢你。

Answer 1

像这样处理修饰符:

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")