vba - 用于多个条件的 Excel VBA 嵌套案例函数

Question

我有一个 sql 查询并想在 Excel VBA 中复制。我在使用多个 case 语句时遇到问题

示例列

column(a) - segment_nbr

column(b) - ltv 

segment_nbr ltv 

1   2.1526521   
4   3.01348283  
1   1.49385324  
1   1.84731871  
1   1.29541322  
1   0.55659018  
2   2.33690417  
1   1.34068404  
2   1.54078719  
1   0.74087837  
3   1.93278303  
1   1.38347042  
4   1.64194326  

我想构建一个函数来复制以下嵌套 if/case 公式的示例:

=if(and($A1=1,$B1<=0.9),100.01,IF(and($A1=1,$B1<=2.0),201.01,IF(and($A1=1,$B1<=3.0),-23.26,IF(and($A1=2,$B1<=0.9),-99.98,IF(and($A1=3,$B1<=1.3),199.98, IF(and($A1=4,$B1<=0.44),-32.43,IF(and($A1=4,$B1<=1.6),160.9,"" )))

我尝试了以下但不工作:它没有采用 segment_nb 参数。

关于如何纠正它的任何想法？

Function ltv_w(segment_nbr, ltv )

Select Case ltv 
    Case Is <= 0.9 And segment_nbr = 1
        ltv_w = 100.01
    Case Is <= 2.0 And segment_nbr = 1
        ltv_w = 201.01
    Case Is <= 3.0 And segment_nbr = 1
        ltv_w = -23.26
    Case Is <= 0.9 And segment_nbr = 2
        ltv_w = -99.98
    Case Is <= 1.3 And segment_nbr = 3
        ltv_w = 199.98
    Case Is <= 0.44 And segment_nbr = 4
        ltv_w = -32.43
    Case Is <= 1.6 And segment_nbr = 4
        ltv_w = 160.9

End Select

End Function

Answer 1

首先 - 改变检查的顺序。
查看 segment_nbr在第一个案例级别，然后在第二个级别检查 ltv .

Function ltv_w(segment_nbr, ltv)

    Select Case segment_nbr
        Case 1
            Select Case ltv
                Case Is <= 0.9: ltv_w = 100.01
                Case Is <= 2#:  ltv_w = 201.01
                Case Is <= 3#:  ltv_w = -23.26
            End Select

        Case 2
        Case 3
        Case 4
    End Select
End Function

小心 Case Is <=序列。

再一次... 永远不要比较 Doubles 是否相等 .
所以像 <= 3# 这样的条件需要转换成 Not > 3# .
.