Excel如何为函数创建引用变量以实现自定义Tax计税函数

需求背景 #

我需要实现自定义函数内动态引用变量的功能，具体场景为已有的个税计算器，目前可通过SUMPRODUCT函数（搭配累计表时VLOOKUP也可得到正确结果）得到正确计算值。

当前使用的SUMPRODUCT公式如下：
=SUMPRODUCT(A4:A10-A3:A9,E1-C4:C10,N(E1>C4:C10))

我希望将公式中的E1替换为变量x，最终可以通过=Tax(E1)得到和原SUMPRODUCT公式一致的计算结果，但不知道如何将x设置为可引用的变量。
目前使用=LET(x, E1, SUMPRODUCT(A4:A10-A3:A9,x-C4:C10,N(x>C4:C10)))已经可以得到正确值，在此基础上我希望进一步简化公式，实现可以动态传入任意单元格参数，即支持输入=Tax(任意单元格)调用。

最终实现方案（VBA自定义函数） #

后续我研究了SUMPRODUCT函数的运行逻辑，检索了大量VBA相关资料，最终编写了可实现该需求的VBA自定义函数，代码如下供有需要的用户参考：

Dim Rate1 As Single
Dim Rate2 As Single
Dim Rate3 As Single
Dim Rate4 As Single
Dim Rate5 As Single
Dim Rate6 As Single
Dim Rate7 As Single

Dim Over1 As Single
Dim Over2 As Single
Dim Over3 As Single
Dim Over4 As Single
Dim Over5 As Single
Dim Over6 As Single
Dim Over7 As Single

Dim Amount1 As Single
Dim Amount2 As Single
Dim Amount3 As Single
Dim Amount4 As Single
Dim Amount5 As Single
Dim Amount6 As Single
Dim Amount7 As Single

Dim Tax1 As Single
Dim Tax2 As Single
Dim Tax3 As Single
Dim Tax4 As Single
Dim Tax5 As Single
Dim Tax6 As Single
Dim Tax7 As Single

Dim Final As Single

Function Tax(Value As Single)
    Rate1 = 0.1
    Rate2 = 0.02
    Rate3 = 0.1
    Rate4 = 0.02
    Rate5 = 0.08
    Rate6 = 0.03
    Rate7 = 0.02
    
    Over1 = 0
    Over2 = 9951
    Over3 = 40526
    Over4 = 86376
    Over5 = 164926
    Over6 = 209426
    Over7 = 523601
    
    Amount1 = Value - Over1
    If Amount1 > 0 Then
        Tax1 = Amount1 * Rate1
        Final = Tax1
        Amount2 = Value - Over2
    Else
        Amount2 = 0
    End If
    If Amount2 > 0 Then
        Tax2 = Amount2 * Rate2
        Final = Final + Tax2
        Amount3 = Value - Over3
    Else
        Amount3 = 0
    End If
    If Amount3 > 0 Then
        Tax3 = Amount3 * Rate3
        Final = Final + Tax3
        Amount4 = Value - Over4
    Else
        Amount4 = 0
    End If
    If Amount4 > 0 Then
        Tax4 = Amount4 * Rate4
        Final = Final + Tax4
        Amount5 = Value - Over5
    Else
        Amount5 = 0
    End If
    If Amount5 > 0 Then
        Tax5 = Amount5 * Rate5
        Final = Final + Tax5
        Amount6 = Value - Over6
    Else
        Amount6 = 0
    End If
    If Amount6 > 0 Then
        Tax6 = Amount6 * Rate6
        Final = Final + Tax6
        Amount7 = Value - Over7
    Else
        Amount7 = 0
    End If

    Tax = Value - Final
End Function

内容的提问来源于stack exchange，提问作者Jacob Hogan