☰

多列比較傳回最常見的值

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 編輯器。
  • 確保您的專案在「專案資源管理器」視窗中突出顯示。
  • 然後，從頂部選單中選擇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 元素數組，其中第二個元素是項目的計數
    • 根據您的 Excel 版本，您可以選擇使用動態數組功能或索引函數傳回此值。

相關內容