☰

多列比较返回最常见的值

tag-icon • tag-icon microsoft-excel
多列比较返回最常见的值

我有 6 列字符串，我正在寻找这 6 列中最常见的字符串。

任何帮助都将不胜感激。

列1 第2栏 第3栏 栏4 第5栏 第6栏
苹果 橙子 香蕉 猕猴桃 布朗尼 西兰花
橙子 香蕉 猕猴桃 布朗尼 西兰花
香蕉 猕猴桃 布朗尼 西兰花
猕猴桃 布朗尼 西兰花
布朗尼 西兰花
西兰花

结果将是 Broccoli。如果第 1 列、第 6 行没有 Broccoli，则结果将是 Brownie/Broccoli。

列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()获取合格的结果并将其格式化为所需的输出字符串。

（如果不需要排序（或者不需要排序），只需将该功能编辑掉。我认为这是可取的（认为“Brownie/Broccoli”字符串不是不可改变的，只是快速显示所需的结果）并认为将其放入然后编辑掉比不放入更清楚，只是说“然后对其进行排序”并留给你去解决。）

其LET()组织方式简单且合乎逻辑。“简单”是指要处理的范围在一开始就出现，而且只有在那里，因此易于编辑。当前版本中没有其他变化，因此它转到中间计算名称，采用自下而上的呈现方式（也许更好地描述为“从内向外”以描述它们在公式中的优先级）。最后，得到最终的工作公式。

答案2

您还可以使用 VBA 编写的用户定义函数来执行此操作

输入这个 UDF 很容易：

要输入此用户定义函数 (UDF)，

  • <alt-F11>打开 Visual Basic 编辑器。
  • 确保您的项目在“项目资源管理器”窗口中突出显示。
  • 然后，从顶部菜单中选择Insert => Module
  • 将以下代码粘贴到打开的窗口中。

=mostFrequent(A1:F6)要使用此用户定义函数 (UDF)，请输入类似某些单元格的公式。

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 元素数组，其中第二个元素是项目的数量
    • 您可以选择使用动态数组功能或 Index 函数返回此值，具体取决于您的 Excel 版本。

相关内容