请会编写excel表格的程序的大神给写一段程序算法，内容为不同等级的钱数对应不同的比例计算方法，附图

发布网友发布时间：2022-05-18 03:10

共2个回答

热心网友时间：2023-10-08 17:09

我帮你编写了一个自定义函数，可以直接当普通公式使用。

生成结果：(更新了错误)

Function Commission(Sales)
    Const Pct1 = 0.55
    Const Pct2 = 0.6
    Const Pct3 = 0.65
    Const Pct4 = 0.7
    Const Pct5 = 0.75
    Const Pct6 = 0.8
    Const Pct7 = 0.85
    Const Pct8 = 0.88
    Const Pct9 = 0.9
    Select Case Sales
        Case 0 To 10000: Commission = Sales * Pct1
        Case 10000.01 To 30000: Commission = Sales * Pct2
        Case 30000.01 To 50000: Commission = Sales * Pct3
        Case 50000.01 To 80000: Commission = Sales * Pct4
        Case 80000.01 To 150000: Commission = Sales * Pct5
        Case 150000.01 To 250000: Commission = Sales * Pct6
        Case 250000.01 To 400000: Commission = Sales * Pct7
        Case 400000.01 To 600000: Commission = Sales * Pct8
        Case Is > 600000: Commission = Sales * Pct9
    End Select
End Function

热心网友时间：2023-10-08 17:10

如果金额在A2，参考公式：
=A2*LOOKUP(A2,{0,10001,30001,50001,80001,150001,200001,400001,600001,99999999999999},{0.55,0.6,0.65,0.7,0.75,0.8,0.85,.88,0.9})追问

对是对，但是算不出答案？由D算E，G、H忽略