☰

Сравнение нескольких столбцов для возврата наиболее распространенного значения

tag-icon • tag-icon microsoft-excel
Сравнение нескольких столбцов для возврата наиболее распространенного значения

У меня есть 6 столбцов строк, и я ищу самую распространенную строку среди всех 6 столбцов.

Любая помощь в этом вопросе будет высоко оценена.

колонка1 колонка2 колонка3 колонка4 колонка5 колонка6
Яблоко Апельсин Банан киви Брауни Брокколи
Апельсин Банан киви Брауни Брокколи
Банан киви Брауни Брокколи
киви Брауни Брокколи
Брауни Брокколи
Брокколи

Результатом будет Брокколи. Если бы столбец 1, строка 6 Брокколи не были там, то результатом был бы Брауни/Брокколи.

колонка1 колонка2 колонка3 колонка4 колонка5 колонка6
Маранта Артишок Руккола Спаржа Бамбуковые побеги Бобы
Свекла Морковь Сельдерей Брауни Брокколи
Банан киви Морковь Чеснок
Шоколадная крошка Хлеб Сыр
Сок Поп
Чипсы

Результатом будет морковь.

решение1

Подойдет следующее:

=LET(Source,A2:F7,
     ShortList,UNIQUE(FILTERXML("<Outer><Inner>"&SUBSTITUTE(TEXTJOIN(",",TRUE,Source),",","</Inner><Inner>")&"</Inner></Outer>","/Outer/Inner")),
     Occurrences,COUNTIF(Source,ShortList),

 TEXTJOIN("/",TRUE,SORT(IF(Occurrences=MAX(Occurrences),ShortList,""))))

Он использует TEXTJOIN()для объединения всего списка, теряя пробелы. Затем FILTERXML()трюк , чтобы превратить его в HTML и разбить на массив, который Excel распознает как таковой. UNIQUE()затем получает список отдельных экземпляров каждого присутствующего значения.

Затем COUNTIF()используется для получения количества для каждого уникального элемента, MAX()получает наибольшее значение из списка этих количеств и IF()сравнивает количество каждого уникального элемента с максимальным значением, чтобы найти квалифицированные результаты. SORT()Располагает эти квалифицированные результаты в алфавитном порядке.

Наконец, TEXTJOIN()берет квалифицированные результаты и форматирует их для желаемой выходной строки.

(Если сортировка нежелательна (или не нужна), просто отредактируйте эту функцию. Я предположил, что она будет желательной (решив, что строка «Брауни/Брокколи» не является непреложной, а просто быстро демонстрирует желаемый результат), и решил, что вставить ее и отредактировать будет яснее, чем не вставлять ее, просто сказать «Тогда отсортируйте» и предоставить это вам для самостоятельной работы.)

Организовано LET()для удобства и логики. «Удобство» в том, что диапазон для работы появляется в непосредственном начале и только там, поэтому его легко редактировать. Больше ничего не меняется в настоящей версии, поэтому она переходит к промежуточным вычислениям Имена в представлении снизу вверх (возможно, лучше описать как «изнутри наружу», чтобы описать их приоритет в формуле). И, наконец, результирующая рабочая формула.

решение2

Вы также можете сделать это с помощью пользовательской функции, написанной на VBA.

Ввести этот UDF легко:

Чтобы ввести эту пользовательскую функцию (UDF),

  • <alt-F11>открывает редактор Visual Basic.
  • Убедитесь, что ваш проект выделен в окне Project Explorer.
  • Затем в верхнем меню выберитеInsert => Module
  • вставьте код ниже в открывшееся окно.

Чтобы использовать эту пользовательскую функцию (UDF), введите формулу, например, =mostFrequent(A1:F6)в ячейку.

Option Explicit
Function mostFrequent(r As Range) As Variant()
    Dim arr As Variant, dict As Object
    Dim v
    Dim result(1)
    
'read range into vba array for faster processing
arr = r
Set dict = CreateObject("Scripting.Dictionary")
    dict.CompareMode = TextCompare
    
'read into dictionary and get the count of each item
For Each v In arr
    If Len(v) > 0 Then
        If Not dict.Exists(v) Then
            dict.Add Key:=v, Item:=1
        Else
            dict(v) = dict(v) + 1
        End If
    End If
Next v

'find max count
For Each v In dict.Keys
    If dict(v) > result(1) Then
        result(0) = v
        result(1) = dict(v)
    End If
Next v

'return most frequent string and it's count
mostFrequent = result
    
End Function

введите описание изображения здесь

Алгоритм

  • Считать диапазон в массив VBA для максимально быстрой обработки
  • Введите каждую строку в словарь, где
    • Ключ = строка
    • Значение = количество строк
  • Верните строку с наибольшим количеством
  • В приведенном ниже коде мы фактически возвращаем массив из 2 элементов, где второй элемент — это количество элементов.
    • При желании вы можете вернуть это значение, используя функцию динамического массива или функцию индекса (в зависимости от версии Excel).

Связанный контент