Как заставить Excel возвращать наиболее часто встречающееся значение в одной ячейке?

Question

У меня есть одна ячейка, содержащая длинный список чисел, разделенных запятой и пробелом.

Некоторые числа повторяются, и мне хочется узнать, какое число повторяется чаще всего.

Функция MODE не работает и, по-видимому, предназначена для работы в диапазоне, где каждое значение находится в своей ячейке.

Пример того, что мне нужно

Cell with numbers
123, 456, 456

Most frequently repeated value
 456

Answer 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Затем возвращается 2в INDEX.

Answer 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