有没有一种方法可以缩短嵌套的 If..然后..否则声明?

Question

我正在尝试创建自定义Excel函数来减少部门中琐碎的脑力任务。

该函数有效，但冻结 32 位 Excel 超过一分钟。作为参考，平均工作负载为>10,000 行输入。

Function Function_Name(Input)
If Val(Input) = 0 Then
Function_Name = Input
ElseIf Val(Input) = 1 Then Function_Name = Input
ElseIf Val(Input) >= 1110 And Val(Input) <= 1999 Then Function_Name = "1110"
ElseIf Val(Input) >= 3100 And Val(Input) <= 3199 Then Function_Name = "3100"
'Similar ElseIf statements go on for 40+ lines......'
ElseIf Val(Input) >= 5700 And Val(Input) <= 5799 Then Function_Name = "5710"
'Note that they do not always return the lower limit of Val(Input), but sometimes may'
End If
End Function

该功能提供准确的输出，但处理时间较长。我想知道是否有办法改进后端逻辑以降低 Excel 崩溃的风险并简化处理。

Answer 1

废弃整个函数，添加一个新工作表(如果必须隐藏它(，在 A1 中键入"阈值"，在 A2 中键入"值"，然后开始填充查找表(Ctrl+T(使其成为实际表，然后命名它，例如refTable(：

Threshold   Value
0   =NA()
1100   1100
1200   1200
1300   1300
...   ...

现在，无论您在哪里使用该函数(其中 B2 是您当前的"输入"(：

=IFERROR(VLOOKUP(refTable[[Threshold]:[Value]], B2, TRUE), B2)

TRUE final 参数使函数匹配第 1 列中不大于查找值的最大值，因此 4256 将与 4200 阈值匹配，低于 4300。

这假设查找值不能用简单的数学计算(即 5710 结果不是拼写错误(。否则，有一个简单而性能更好的替代方案：

=IF(B2>=1100,INT(B2/100)*100,B2)

如果输入大于或等于 1100，则将此输入的整数值除以 100，再乘以 100(可能也有一种方法可以使用舍入函数(;否则只需返回输入。这应该几乎立即计算。