office交流網--QQ交流群號

Access培訓群:792054000         Excel免費交流群群:686050929          Outlook交流群:221378704    

Word交流群:218156588             PPT交流群:324131555

access excel vba穫取數組中的衆數

2020-04-02 08:00:00
tmtony8
原創
4001
在數據查詢中,我們有時需要找齣一組數據中齣現次數最多的數字。這箇也是我們常説的衆數瞭。

如在數組{2,3,2,5,8,2,16,17} ,衆數卽是 2瞭

這箇我們如果用SQL語句,是很容易求齣來的。用count 統計,用order by 排序一下就齣來結果瞭。


但是在VBA中,如何求齣數組中的衆數呢?下麵給齣具體的函數

Function gf_mode(a)
    Dim b As Integer
    Dim c() As Double
    Dim f() As Double
    Dim i As Integer
    Dim j As Integer
    Dim k As Integer
    Dim x() As Double
    k = UBound(a)
    ReDim c(k), f(k)
    For i = 0 To k - 1
        If f(i) = 0 Then
            c(i) = 1
            For j = i + 1 To k
                If a(j) = a(i) Then
                    c(i) = c(i) + 1
                    f(j) = 1
                End If
            Next
        End If
    Next
    If f(i) = 0 Then c(i) = 1
    b = 1
    For i = 0 To k
        If c(i) > b Then b = c(i)
    Next
'    若所有數據都是衆數,則沒有衆數
    For i = 0 To k
        If c(i) <> b And c(i) <> 0 Then Exit For
    Next
    If i = k + 1 Then
        ReDim x(0)
        x(0) = "沒有衆數"
        gf_mode = x
        Exit Function
    End If
'    找齣所有衆數
    j = 0
    For i = 0 To k
        If c(i) = b Then
            ReDim Preserve x(j)
            x(j) = a(i)
            j = j + 1
        End If
    Next
    gf_mode = x
End Function

在窗體或者直接調用函數,截圖如下

' 調用求衆數的函數
Public Sub acc()
    Dim arrtxt() As Double

    arrtxt = gf_mode(Array(2,3,2,5,8,2,16,17))
'    顯示所有衆數
    For i = 0 To UBound(arrtxt)
        MsgBox "衆數是:" & "" & arrtxt(i)
    Next
End Sub

    分享