☰

Excel のプレフィックスまたはサフィックス

tag-icon • tag-icon microsoft-excel worksheet-function
Excel のプレフィックスまたはサフィックス

Excel にコンマ区切りのリストを含む列があります。

Header
1, 61
61
1, 61, 161
5, 55

このデータを抽出して、各項目の出現回数をカウントし、次の結果を得たいと思います。

Count of Items
1    |    2
5    |    1
55   |    1
61   |    3
161  |    1

私は「*」を使ってcountifを試しましたが、この場合、接頭辞または接尾辞（1,61,161）があるため混乱します。

助けてください！

答え1

オプション1：

カンマ区切りの数字の出現回数だけでなく、テキストの出現回数もカウントする UDF (ユーザー定義関数) を提案したいと思います。

ここに画像の説明を入力してください

使い方：

  • プレスAlt+F11VBエディタを入手するにはコピー＆ペーストこのコードはモジュール。

    Option Explicit

Function CountOccurrence(SearchRange As Range, Phrase As String) As Long

Dim RE As Object, MC As Object
Dim sPat As String
Dim V As Variant
Dim I As Long, J As Long

V = SearchRange

Set RE = CreateObject("vbscript.regexp")
With RE
.Global = True
.MultiLine = True
.ignorecase = True
.Pattern = "(?:^|,\s*)" & Phrase & "(?:\s*,|$)"
End With

For I = 1 To UBound(V, 1)
If RE.test(V(I, 1)) Then J = J + 1
Next I

CountOccurrence = J

End Function

  • 範囲に条件を入力しH18:H26、セルにこの数式を入力しI18て下方向に入力します。

=CountOccurrence($G$18:$G$24,H18)

オプション2:

この数式をセルに入力してI18、下方向に塗りつぶします。

=SUMPRODUCT(--ISNUMBER(FIND(H18,$G$18:$G$24)))

必要に応じてセル参照を調整します。

答え2

  1. まず、数字を別々のセルに分割する必要があります。
    • データを選択してください
    • データタブで「列に分割」を選択します
    • 「区切り」を選択し、次へ
    • 選択したデータは「カンマ」で区切られ、終了
  2. これでCOUNTIFが使えるようになりました。例:
    =COUNTIF($A$2:$C$5,E2)

ここに画像の説明を入力してください

答え3

すでに解決策があるようですが、動的データを処理する VBA 以外のソリューションも紹介します。このソリューションでは、任意の大きな範囲に事前設定できるヘルパー列を使用します。関連付けられているデータがない場合、セルは空白になります。ヘルパー列の一部は削除できます。繰り返しを最小限に抑えるために含まれていますが、ヘルパー列はすべて非表示にできます。

ここに画像の説明を入力してください

データは列 A にあります。列 B は、コンマの数に基づいて各エントリの値の数を決定します。B3 の数式は次のとおりです。

=IF(ISBLANK(A3),"",LEN(A3)-LEN(SUBSTITUTE(A3,",",""))+1)

列 C は列 B からの累積コンポーネント数です。C2 は と入力されます0。C3 の数式は次のとおりです。

=IF(ISBLANK(A3),"",SUM(B3:B$3))

列 B と列 C に、データがある限り多くの行を入力します。必要に応じて、いつでもこれらの列を拡張できます。

列 E は便宜上のものです。解析された値のインデックスを提供します。 をハードコードして1、各行ごとに 1 を加算することができます。特に理由はありませんが、行番号に基づいて計算しました (-2以下の数式の は、値が から始まるように調整するためのものです1)。値の合計数を超えるセルは空白になります。E3 の数式:

=IF(ROW()-2>MAX($C$2:$C$10),"",ROW()-2)

列 F は、式の繰り返しを避けるためのものです。現在のコンポーネントが解析される関連する列 A エントリを取得します。F3 の数式は次のとおりです。

=IFERROR(OFFSET($A$2,MATCH(E3-1,$C$2:$C$10,1),0),"")

列 E のコンポーネント番号と列 C の累積コンポーネント数を比較して、適切なエントリを見つけます。

列 G は解析されたコンポーネント値であり、すべてが 1 つの連続した列にまとめられているため、簡単に操作できます。G3 の数式は次のとおりです。

=IFERROR(TRIM(MID(SUBSTITUTE(F3,",",REPT(" ",LEN(F3))),(E3-INDEX($C$2:$C$10,MATCH(E3-1,$C$2:$C$10,1))-1)*LEN(F3)+1,LEN(F3))),"")

これは、現在の要素番号から最後の「完了した」入力レコードの累積要素数を減算することにより、列 F エントリから解析する要素を決定します。

列 E から G は、予想されるコンポーネント値の数 (少なくともデータ行の数の数倍) をカバーするのに十分な行に伝播する必要があります。範囲 $C$2:$C$10 を参照する上記のすべての数式は、データの全範囲が含まれるように調整する必要があることに注意してください。

解析されたすべての要素が適切な列に表示されたので、それらを集計してカウントを取得する方法はいくつかあります。私はピボット テーブルを使用しました。これにより、一意の値のリストも同時に表示されます。

ピボット テーブルの列 G の事前入力された範囲全体を選択します。そのフィールドを行ウィンドウと値ウィンドウに使用します (集計としてカウントを選択します)。範囲には未使用の行の空白が含まれるため、組み込みフィルターを使用して空白を選択解除します。

データが変更された場合は、ピボット テーブルを更新し、フィルターで新しい要素値が選択されていることを確認します。

関連情報