Как заставить Excel вернуть самое частое значение в одной ячейке?

Содержание

Вопрос или проблема
Ответ или решение
Способ 1: Использование функции FILTERXML
Способ 2: Использование функции INDEX и MATCH
Способ 3: Пользовательская функция на VBA
Заключение

Вопрос или проблема

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

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

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

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

Ячейка с числами
123, 456, 456

Число, повторяющееся наиболее часто
 456

Если ваши значения — это числа, как вы показываете:

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

запись "<a>"&SUBSTITUTE(A1,",","")&"</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.

Попробуйте эту короткую пользовательскую функцию:

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

Добавляя к ответу Скотта Крейнера, если значения в ячейках — это текст или смесь И включают специальный символ, такой как ‘&’, который функция FILTERXML() не обрабатывает, вы можете использовать TEXTSPLIT вместо этого:

=INDEX(TEXTSPLIT(A1,", "),,MODE.SNGL(MATCH(TEXTSPLIT(A1,", "),TEXTSPLIT(A1,", "),0)))

Функция TEXTSPLIT() делит ячейку чисел/текста на колонный массив этих чисел. Затем функция MATCH() этого массива – к самому себе – найдет первое совпадение и вернет относительные позиции каждого найденного значения в массиве. (Примечание: Если все числа в вашей ячейке одинаковые, функция MATCH() только вернет 1.) Затем MODE.SNGL() возвращает наиболее часто повторяющееся значение, которое является относительной позицией (относительный индекс колонки) в массиве числа, которое повторяется чаще всего. Наконец, функция INDEX() в массиве ищет значение в массиве, соответствующее наиболее часто повторяемой относительной колонной позиции.

Ответ или решение

Для решения задачи нахождения наиболее часто встречающегося значения в одной ячейке Excel, содержащей длинный список чисел, разделённых запятыми и пробелами, можно воспользоваться несколькими методами. Поскольку функция MODE работает только с диапазоном ячеек, нам нужно использовать более подходящие способы обработки данных в одной ячейке.

Способ 1: Использование функции `FILTERXML`

Если у вас есть только числовые значения, то вы можете использовать следующую формулу:

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

Как это работает:

SUBSTITUTE преобразует строку из ячейки A1, заменяя запятые на XML-теги, создавая строку вида <a>123456456</a>.
FILTERXML использует этот XML, чтобы извлечь значения, которые можно обработать.
MODE.SNGL возвращает наиболее часто встречающееся значение из полученного массива.

Способ 2: Использование функции `INDEX` и `MATCH`

Если данные могут содержать текст или специальные символы, вместо FILTERXML лучше использовать функцию TEXTSPLIT (доступна в более новых версиях Excel):

=INDEX(TEXTSPLIT(A1,", "),,MODE.SNGL(MATCH(TEXTSPLIT(A1,", "),TEXTSPLIT(A1,", "),0)))

Разбор формулы:

TEXTSPLIT разделяет строку из ячейки A1 на массив по запятой.
MATCH ищет каждое значение в этом массиве (возвращает массив позиций).
MODE.SNGL находит наиболее частое значение среди этих позиций.
INDEX возвращает соответствующее значение из исходного массива.

Способ 3: Пользовательская функция на VBA

Если вы хотите создать собственное решение, можно воспользоватьсяыне написанной выше пользовательской функцией:

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

Объяснение работы функции:

Функция разбивает строку на массив, используя Split.
Затем она итерирует по массиву, подсчитывая количество повторений каждого значения.
В конце возвращается значение с наибольшим количеством повторов.

Заключение

Выбор метода зависит от ваших конкретных требований и версии Excel. Если вы работаете с числами, используйте FILTERXML. Для текстов и специальных символов предпочтительней будет TEXTSPLIT. Если вам нужен больший контроль и гибкость, то подход с пользовательскими функциями на VBA также является отличным решением. Выбор правильного подхода поможет вам эффективно решать задачи анализа данных в Excel.