如何处理 IIF 或 Switch 除以零给出 #ERROR?

本文介绍了如何处理 IIF 或 Switch 除以零给出 #ERROR?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我尝试使用 IIF 和 Switch 案例，但是当出现除以零错误时，我无法处理 N/A 案例.例如:

I tried using IIF and Switch case, but I am unable to handle N/A case when there would be a divide by zero error. For example:

=switch(
    ReportItems!Textbox54.Value = 0, "N/A",
    ReportItems!Textbox54.Value <> 0, ((ReportItems!Textbox56.Value) / (ReportItems!Textbox54.Value)))

我不知道这个:

ReportItems!Textbox54.Value <> 0, ((ReportItems!Textbox56.Value) / (ReportItems!Textbox54.Value)

但是如果添加这个条件我会得到一个错误?

But if add this condition I am getting an error?

我使用的是 SSRS 2008 R2.

I am using SSRS 2008 R2.

输出:

19.47%
13.85%
#错误

推荐答案

IIF 函数的问题在于它是一个函数，而不是一个语言结构.这意味着它在将参数传递给函数之前评估两个参数.因此，如果您有除以零错误，这将被评估并导致 #ERROR 条件，即使看起来由于 IIF 的布尔条件而不应执行该代码 语句.

The problem with the IIF function is that it is a function not a language construct. This means that it evaluates both parameters before passing the parameters to the function. Consequently, if you have a divide by zero error, this will get evaluated and cause an #ERROR condition even when it looks like that code shouldn't be executed due to boolean condition of the IIF statement.

此问题有两种解决方法:

There are two workarounds for this problem:

绕过 IIF

基本上进行两次 IIF 函数调用，您不会得到除以零错误的结果:

Basically make two IIF function calls where you won't get divide by zero errors:

=IIF(ReportItems!Textbox54.Value <> 0, 
    ReportItems!Textbox56.Value / IIF(ReportItems!Textbox54.Value = 0, 1, ReportItems!Textbox54.Value),
    "N/A")

因此，如果 ReportItems!Textbox54.Value 为零，则除以 1，将结果丢弃并使用 N/A.

So where ReportItems!Textbox54.Value is zero, divide by 1 instead, throw that result away and use N/A.

自定义代码

在自定义代码中创建一个安全的除零函数，您可以在其中使用真实的语言结构.

Create a safe divide by zero function in custom code where you can use real language constructs.

Public Function SafeDivide(ByVal Numerator As Decimal, ByVal Denominator As Decimal) As Decimal
    If Denominator = 0 Then
       Return 0
    End If
    Return (Numerator / Denominator)
End Function

然后在您的报告中使用它来代替 IIF 或 SWITCH:

and then use this in your report for the Value expression instead of IIF or SWITCH:

=Code.SafeDivide(ReportItems!Textbox56.Value, ReportItems!Textbox54.Value)

并使用格式字符串将零显示为N/A":

and use a Format string to display zeroes as "N/A":

#,##0.00;-#,##0.00;N/A

这篇关于如何处理 IIF 或 Switch 除以零给出 #ERROR?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

IIf