Excel VBA：修剪一个百分比的小数位数

Below is a snippet of an excel vba code. I'm trying to combine a percentage value and numerical sample size value (both in separate cells) into one cell

下面是excel vba代码的片段。我正在尝试将百分比值和数值样本大小值（均在单独的单元格中）组合到一个单元格中

However, there are many decimals for the % value. Even though I have changed the format to show only 1 decimal value, when I click on the cell, it still shows multiple decimal places. Hence, when I combine the 2 variables, the full length of decimal places is combined with the sample size, which is not what I want.

但是，％值有许多小数。即使我已将格式更改为仅显示1个十进制值，但当我单击该单元格时，它仍会显示多个小数位。因此，当我组合2个变量时，小数位的全长与样本大小相结合，这不是我想要的。

How do I trim off these unnecessary decimal places?

如何修剪这些不必要的小数位？

        For j = Row_SourceStart To Row_SourceEnd

            wbSource.Activate
            wbSource_ACX.Select
            Percentage_value = wbSource_ACX.Range("M" & j).NumberFormat = "0.0%"
            Sample_size = wbSource_ACX.Cells(j, 12)

            wbTarget.Activate
            Set wbTarget_ACX = wbTarget.Worksheets(ArrayWS_Source(0))
            wbTarget_ACX.Select
            wbTarget_ACX.Cells(Row_TargetStart, j + 5) = Percentage_value & " (" & Sample_size & ")"

        Next j

2 个解决方案

#1

The value in the cell is only formated to look like a rounded percentage. Excel stores the actual value as calculated with all decimal places in the background. You see this when you click on the cell and look at the value in the formula bar.

单元格中的值仅形成为圆整百分比。 Excel存储计算的实际值，并在后台显示所有小数位。单击单元格并查看公式栏中的值时，会看到此信息。

You can trim the value to 1 decimal place in VBA either by a number of methods. There is the Round Function in VBA or you can use one of the worksheet functions that also do rounding. Here are a few examples (with the number in the example being written in cell A1 of the active sheet):

您可以通过多种方法将值修剪为VBA中的1位小数。在VBA中有Round函数，或者您可以使用其中一个也进行舍入的工作表函数。以下是一些示例（示例中的数字写在活动工作表的单元格A1中）：

Sub test()
    Debug.Print Round(Range("a1"), 1)
    Debug.Print WorksheetFunction.Round(Range("a1"), 1)
    Debug.Print WorksheetFunction.RoundUp(Range("a1"), 1)
    Debug.Print WorksheetFunction.RoundDown(Range("a1"), 1)
End Sub

#2

In VBA, you can use the round function.

在VBA中，您可以使用round函数。

wbTarget_ACX.Cells(Row_TargetStart, j + 5) = Round(Percentage_value, 1) & " (" & Sample_size & ")"

If your percentage value is already correctly formatted in the sheet, use the text-property of the cell:

如果您的百分比值已在工作表中正确格式化，请使用单元格的text-property：

wbTarget_ACX.Cells(Row_TargetStart, j + 5) = wbSource_ACX.Range("M" & j).text & " (" & Sample_size & ")"

Else calculate the value by your own (and add the %-sign)

否则自己计算值（并添加％-sign）

Percentage_value = wbSource_ACX.Range("M" & j).Value * 100
wbTarget_ACX.Cells(Row_TargetStart, j + 5) = Round(Percentage_value, 1) & "% (" & Sample_size & ")"

BTW: You can (and should) remove all the activate and select commands - they are not neccessary.

顺便说一句：您可以（并且应该）删除所有激活和选择命令 - 它们不是必需的。

#1