☰

複数列を比較して最も一般的な値を返す

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()適格な結果を取得し、目的の出力文字列に合わせてフォーマットします。

(ソートが望ましくない (または必要でない) 場合は、その機能を編集して削除します。私はそれが望ましいと想定し (「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要素の配列を返します。2番目の要素はアイテムの数です。
    • オプションで、Excel のバージョンに応じて、動的配列機能または Index 関数を使用してこれを返すことができます。

関連情報