我有一个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