公式中命令的动态替换

Question

有没有办法将 Excel 命令引用为变量。例如：

=Average(A1:A5)
=Max(A1:A5)

我可以指向内容为“平均值”与“最大值”的单元格并在公式中使用它吗？例如，将下表放在单元格 A1:D5 中。

1   Red     Max      Orange
2   Green   Min      Red
3   Blue    Average  Blue
4   Black       
5   Orange      

= MAX(A1.A5) 

成为（C1、C2 或 C3 单元格内容代表 MAX、MIN、AVERAGE）

=C1(A1.A5)

Answer 1

这里有两个按要求执行的 UDF。

Option Explicit
Public Function nojoy(s As String, r As Range) As String
    Select Case s
        Case "Min"
            nojoy = Application.WorksheetFunction.Min(r)
        Case "Max"
            nojoy = Application.WorksheetFunction.Max(r)
        Case "Average"
            nojoy = Application.WorksheetFunction.Average(r)
    End Select
End Function

Public Function nojoyLookup(s As String, r As Range, r1) As String
Dim s1 As String
    Select Case s
        Case "Min"
            s1 = Application.WorksheetFunction.Min(r)
        Case "Max"
            s1 = Application.WorksheetFunction.Max(r)
        Case "Average"
            s1 = Application.WorksheetFunction.Average(r)
    End Select
    nojoyLookup = Application.WorksheetFunction.Index(r1, Application.WorksheetFunction.Match(Val(s1), r, 0))
End Function