请教求取最大值和缺失的值

wslyy

请教老师们提取最大值和缺失的值写入指定单元格的问题,谢谢!

hcm19522

B2{=IFERROR(SMALL(IF(MMULT(COLUMN(A:J)^0,1-ISERR(FIND(","&COLUMN(A:T)&",",","&A$2:A$11&",")))=0,COLUMN(A:T)),ROW(A1)),"")

C2{=MAX((1-ISERR(FIND(","&COLUMN(A:T)&",",","&A$2:A$11&",")))*COLUMN(A:T))

sj15627581616

参考

wslyy

谢谢两个老师的代码,谢谢!

gwjkkkkk

Sub TEST1()
    Dim ar, br, cr, i&, j&, r&, strJoin$, iMax&
   
    ar = Range("A2", Cells(Rows.Count, "A").End(xlUp)).Value
    strJoin = "," & Join(Application.Transpose(Application.Index(ar, , 1)), ",") & ","
   
    ReDim br(1 To 20, 0)
    For i = 1 To 20
        If InStr(strJoin, "," & i & ",") = False Then
             r = r + 1
             br(r, 0) = i
        End If
    Next i
    Range("C2:C" & Rows.Count).ClearContents
    [C2].Resize(r) = br
   
    For i = 1 To UBound(ar)
        cr = Split(ar(i, 1), ",")
        For j = 0 To UBound(cr)
            If Val(cr(j)) > iMax Then iMax = cr(j)
        Next j
    Next i
    [D2] = iMax
   
    Beep
End Sub

gwjkkkkk

参与一下。。。

ykcbf1100

附件供参考。。。

ykcbf1100

参与一下。。。

1. Sub ykcbf()   '//2024.2.28
   
2.     Dim arr, d
   
3.     Application.ScreenUpdating = False
   
4.     r = Cells(Rows.Count, "a").End(xlUp).Row
   
5.     arr = [a1].Resize(r, 4)
   
6.     st = ""
   
7.     For i = 2 To UBound(arr)
   
8.         s = CStr(arr(i, 1))
   
9.         If InStr(s, "、") Then
   
10.             b = Split(s, "、")
    
11.             For x = 0 To UBound(b)
    
12.                 st = st & "、" & b(x)
    
13.             Next
    
14.         Else
    
15.             st = st & "、" & s
    
16.         End If
    
17.     Next
    
18.     zrr = Split(Mid(st, 2), "、")
    
19.     Max = 1
    
20.     ReDim brr(1 To 20, 0)
    
21.     For i = 0 To UBound(zrr)
    
22.         If Max < Val(zrr(i)) Then Max = Val(zrr(i))
    
23.         If Val(zrr(i)) <> i + 1 Then
    
24.             m = m + 1
    
25.             brr(m, 0) = i + 1
    
26.         End If
    
27.     Next
    
28.     [c2:d1000] = ""
    
29.     [c2].Resize(m) = brr
    
30.     [d2] = Max
    
31.     Application.ScreenUpdating = True
    
32.     MsgBox "OK！"
    
33. End Sub

复制代码

chxw68

参与一下。