求助EXCEL自定义函数大神

B39输入，数组函数，

=SUM(LARGE(MOD(LARGE(IF(COUNTIF(OFFSET(A39,,,-ROW(INDIRECT("1:"&ROW(A39)))),N(OFFSET(A39,-ROW(INDIRECT("1:"&ROW(A39)))+1,)))=1,N(OFFSET(A39,-ROW(INDIRECT("1:"&ROW(A39)))+1,))+(ROW(A39)-ROW(INDIRECT("1:"&ROW(A39))))*10^4),ROW($1:$5)),10^4),ROW($2:$5))*10^(ROW($1:$4)-1))&MAX(MOD(LARGE(IF(COUNTIF(OFFSET(A39,,,-ROW(INDIRECT("1:"&ROW(A39)))),N(OFFSET(A39,-ROW(INDIRECT("1:"&ROW(A39)))+1,)))=1,N(OFFSET(A39,-ROW(INDIRECT("1:"&ROW(A39)))+1,))+(ROW(A39)-ROW(INDIRECT("1:"&ROW(A39))))*10^4),ROW($1:$5)),10^4))

max后面那一段都是求10，

这只是其中一种方法，还有很多方法，公式长度应该都很长

效果如图

叫你去论坛，你不去，VBA更简单

Function largg(ByRef a As Range, ParamArray arr() As Variant)
Dim i As Long, i1 As Long
Dim rng As Range
Dim arr_a
arr_a = a.Value
Dim arr1
arr1 = arr
r = a.Row - 1
For i = 1 To UBound(arr_a, 1)
For i1 = 0 To UBound(arr1)
If arr_a(i, 1) = arr(i1) Then
arr1(i1) = i + r
End If
Next
Next
largg = WorksheetFunction.Median(arr1)
End Function
largg(第一个参数是单元格区域,后面可以传入多个参数代表你要对比的数值)
=largg(G2:G110,3,2,4)

代码如下：

Function largg(ByVal a As Range)

Dim r2%, r3%, r4%, i%

For i = a.Count To 1 Step -1

   If a(i) = 2 And r2 = 0 Then

      r2 = a(i).Row

   ElseIf a(i) = 3 And r3 = 0 Then

      r3 = a(i).Row

   ElseIf a(i) = 4 And r4 = 0 Then

      r4 = a(i).Row

   End If

   If r2 > 0 And r3 > 0 And r4 > 0 Then Exit For

Next

largg = Application.Median(r2, r3, r4)

End Function

既然用代码了，就不要兼用函数了。

vba的逻辑不是你这样的，要进行循环判断