如何讓 Excel 傳回一個儲存格中出現頻率最高的值？

2024-8-8 • tag-icon

microsoft-excel

如何讓 Excel 傳回一個儲存格中出現頻率最高的值？

我有一個單元格，其中包含一長串數字，由逗號和空格分隔。

有些數字是重複的，我想知道哪個數字重複次數最多。

函數 MODE 不起作用，並且似乎設計為在每個值都位於其自己的單元格中的範圍內工作。

我需要的範例

Cell with numbers
123, 456, 456

Most frequently repeated value
 456

答案1

如果您的值是像您顯示的那樣的數字：

=MODE.SNGL(FILTERXML("<a><b>"&SUBSTITUTE(A1,",","</b><b>")&"</b></a>","//b"))

建立"<a><b>"&SUBSTITUTE(A1,",","</b><b>")&"</b></a>"一個 xml 字串。然後FILTERXML(...,"//b")返回數字數組。然後MODE.SNGL傳回最大的重複項。

如果是文字或混合：

=INDEX(FILTERXML("<a><b>"&SUBSTITUTE(A1,",","</b><b>")&"</b></a>","//b"),MODE.SNGL(MATCH(FILTERXML("<a><b>"&SUBSTITUTE(A1,",","</b><b>")&"</b></a>","//b"),FILTERXML("<a><b>"&SUBSTITUTE(A1,",","</b><b>")&"</b></a>","//b"),0)))

這次我們使用 Match 返回一個數字數組（它與第一個匹配項相匹配，在下面的情況下將是1,2,2），MODE_SNGL然後返回2INDEX。

答案2

嘗試這個簡短的使用者定義函數：

Public Function splitt(s As String) As Variant
    Dim mx As Long, i As Long, j As Long, mxkp As Long
    arr = Split(s, ", ")
    mx = 0
    mxkp = 0
    splitt = arr(0)
    For i = 0 To UBound(arr)
        v = arr(i)
        mx = 0
        For j = 1 To UBound(arr)
            If v = arr(j) Then mx = mx + 1
        Next j
        If mx > mxkp Then
            mxkp = mx
            splitt = v
        End If
    Next i
End Function

相關內容