☰

如何在 Excel 中按给定间隔对数字进行四舍五入

tag-icon • tag-icon microsoft-excel
如何在 Excel 中按给定间隔对数字进行四舍五入

因此，我制作了一个包含工作价格的 Excel 电子表格。价格应以以下数字结尾：12、20、40、60、80 或 00（无小数）。它们应以哪个数字结尾，请参见下面我制作的间隔表：

12 if  15 > x ≥ 6
20 if  30 > x ≥ 15
40 if  50 > x ≥ 30 
60 if  70 > x ≥ 50 
80 if  90 > x ≥ 70 
00 if   9 > x ≥ 90

例子：

x = 10669,56 should be converted to 10660,00
x = 922,56 should be converted to 920,00
x = 7448,73 should be converted to 7440,00

有人知道我需要使用哪些命令以及如何设置吗？

答案1

下面的公式可以解决这个问题：

=TRUNC(A1,-2)  +  XLOOKUP(TRUNC(A1,0)-TRUNC(A1,-2),$I$1:$I$7,$J$1:$J$7,,1)

在开始解释之前，我想指出您在要四舍五入的值方面存在冲突。首先，您的值低至 6（因此包括 6、7 和 8），四舍五入为 12，但最后您想要的值低于 9（因此包括 6、7 和 8），四舍五入为 00。我敢打赌，您打算在末尾使用“低于 6”，其余部分也假设这一点。如果 <9 是正确的，请调整后面查找表中的第一个条目。

我原来的（已删除）答案一直被改成“嗯，实际上，有一种更简单的方法可以完成这一部分，所以用这个吧”，过了一会儿，这变得荒谬了。所以这是新的、改进的版本。

您不需要（或不想）进行任何奇怪而复杂的实际舍入。因此，无需担心ROUND()、MROUND()或INTEGER()以及它们带来的任何奇怪之处。也不用担心奇怪的舍入带带来的困难。

您将获取输入值，去掉小数部分，因为小数部分没有任何用处，也不希望出现在最终结果中。这样做还有一个好处，就是您将分解该值并处理其各个部分。

首先，您需要百位数以上的部分。因此，对于 10669，这意味着 10600 部分，删除最后两位数字。该部分很简单，从未对其进行任何处理……它只是等待第二部分计算出来并添加到其中。

要做到这一点，您可以使用TRUNC()截断值。该函数的“Intellisense”帮助让您看起来像是输入了您想要的原始数字的位数，但这完全是错误的。您实际上输入的是您想要保留的小数位数。如果输入“0”，您将获得一个整数值，该值只是在小数位处截断的原始值。如果输入“2”，您将获得两位小数。如果输入“负二：-2”，您将获得截断的十位和个位数字。这有点误导。您可能会认为这意味着将 10669 截断为 -2 位会得到“106”（截断最后两位数字），但它真正做的是截断它们但用零替换它们。所以你在这里得到了你想要的“10600”。

好了。这样就完成了，现在就等着添加最后两位数字了。为此，您需要一个两列、七行的表格，第一列是舍入规则中的断点，第二列是每个断点的结果。您可以复制并粘贴这个：

抬头 结果
6 00
15 12
三十 20
50 40
70 60
90 80
99 00

您还可以将其加载到命名范围中，这样它就不必出现在工作表上，以防您喜欢这样或没有空间。为此，实际上在某个工作表上输入表格，比如说我使用的 I1:J7，并使用以下函数，不知出于什么原因，似乎没有人知道这个函数：

=ARRAYTOTEXT(I1:J7,1)

其输出为：（ {6,0;15,12;30,20;50,40;70,60;90,80;99,0}复制并粘贴|特殊|值后）您可以将其复制并粘贴到命名范围的“引用”框中。将表加载到任何内容（命名范围或其他内容）的最简单方法，因为它会为您处理逗号和分号。

（顺便说一句，您的问题清楚地表明您没有使用英文版的 Excel，或者更确切地说，您的 Windows 设置（例如小数点分隔符）不同，因此您必须调整我所写的任何内容以适合您的情况。）

那么，回到公式的后半部分。您使用表格进行查找。查找值是截断数字的最后两位数字。获取这两位数字的简单方法是从完整的“截断为 0 位”数字中减去“截断为 -2 位”的值。因此 10669-10600 得出 69。使用它进行查找XLOOKUP()不是因为我不喜欢VLOOKUP()，而是因为它做了一些VLOOKUP()（我的老朋友）做不到的事情：它允许您指定您想要“精确匹配或下一个更高的值”，这意味着查找“69”找不到精确匹配，因此它选择表中的下一个更高的值“70”，并返回 60。

因此，现在您有了公式需要添加的两个数字，并且将它们相加会给出明显四舍五入的结果，但实际上并没有“真正”进行四舍五入。

记住ARRAYTOTEXT()。创建数组字符串比大量输入、确保所有逗号和分号都正确，或使用标准字符串技术构建遵循某种模式的长数组或实际上一步完成困难的操作要容易得多。令人惊讶的是，人们在 SE 问题中发现的人们使用复杂的方法来完成工作的数量！这几乎和有多少人使用令人讨厌的、看起来很可怕的公式来查找列字母名称一样糟糕，而他们只是使用ADDRESS()“1”表示行，使用列号表示列，然后SUBSTITUTE()删除多余的位。

无论如何，就是这样。不需要 VBA。但是，如果您了解 VBA，并且被允许在您的环境中使用它，那么它始终是任何事物的黄金标准，即使是半复杂的事物。

答案2

您可以使用用户定义的自定义函数：

Function PriceRound(price As Double) As Double
    Dim iPrice As Integer
    Dim m100 As Integer
    
    iPrice = CInt(price)
    m100 = iPrice Mod 100
    iPrice = iPrice - m100
    
    If m100 < 6 Then
        m100 = 0
    ElseIf m100 < 15 Then
        m100 = 12
    ElseIf m100 < 30 Then
        m100 = 20
    ElseIf m100 < 50 Then
        m100 = 40
    ElseIf m100 < 70 Then
        m100 = 60
    ElseIf m100 < 90 Then
        m100 = 80
    Else
        m100 = 100
    End If
        
    PriceRound = iPrice + m100
End Function

如果没有自定义函数，您可以使用嵌套的如果函数来遍历所有的情况。这可能是未婚妻在评论中。

为了减少情况数量，您可以首先检查该值是否属于四舍五入为 的倍数的常规范围20。 否则，将其四舍五入为最后一位数字12。

相关内容