ExcelのVLOOKUP関数で部分一致を検索して抽出する方法や、COUNTIF関数で部分一致する文字列の個数をカウントする方法をご紹介します。検索値をセル参照にする方法や、複数セルを抽出する方法についても解説しているので、ぜひ参考にしてみてください。
部分一致を抽出する方法
VLOOKUP関数は範囲内の1列目を検索し、検索値と一致する行を指定した列から返します。
0 個以上の任意の文字列を表すワイルドカードは * です。例えば VLOOKUP(“★*”,A2:A6,1,FALSE) なら、範囲の 1 列目から ★で始まるセルを抽出します。
検索方法には、完全一致を表す FALSE を指定してください。
VLOOKUP関数の構成要素:(検索値, 範囲, 列番号, [検索の型])
部分一致をカウントする方法
COUNTIF関数は、範囲内で条件に一致する要素の個数を返します。
任意の 1 文字を表すワイルドカードは ? です。例えば COUNTIF(A2:A6,”★???”) なら、範囲内から★で始まる合計4文字のセルの個数を返します。
COUNTIF関数の構成要素:(範囲, 条件)
部分一致を複数抽出する方法
部分一致を含むセルを、すべて抽出する方法をご紹介します。VLOOKUPの上位互換であるXLOOKUP関数や、フィルタ処理が可能なFILTER関数にはワイルドカードが使用できません。
VLOOKUP関数で複数セルを抽出する方法
部分一致するをすべて抽出する方法は、以下の通りです。今回はVLOOKUP関数をオートフィルでコピーすることですべてのデータを抽出するので、範囲は固定します。
- COUNTIF関数で、範囲内から部分一致するセルの番号を取得します。
- 取得した番号を、アンパサンド(&) で元データと結合します。
- VLOOKUP関数は結合結果を検索し、検索値と部分一致する行の3列目から値を返します。
数式例:VLOOKUP(“★“&ROW(A1), $A$2:$C$6, 3, FALSE )
ポイントはCOUNTIF関数の範囲を、$C$2:C2 のように開始位置のみ固定することです。これにより数式をコピーしたときに、終点位置のみ1行ずつ下に拡張されます。
例えば COUNTIF($C$2:C2,”*★*”) なら、★を含むセルに当たる毎に 1 が加算されていきます。
COUNTIF関数で取得した番号を元データと結合して、すべてのデータに番号を振ります。
例えば C2&COUNTIF($C$2:C2,”*★*”) なら、★ABC1 です。このとき部分一致するデータには、1、2、3、と一意の番号が振られていく形になります。
VLOOKUP関数の検索値には、ROW関数で行番号を結合します。番号 1 から検索したいので、ROW関数の範囲には 1 行目の全体またはいずれかのセルを指定してください。
例えば検索値が “*★*”&ROW(A1) なら、抽出されるのは★を含む 1 で終わるセルです。
数式をコピーすると行番号も1行ずつ移動するため、*★*1、*★*2、*★*3、を順に抽出できます。
ROW関数の構成要素:([範囲])
エラーを非表示にする方法
IFERROR関数は値がエラーでない場合は そのまま値を、エラーの場合はエラー値を返します。つまり値にVLOOKUP関数を、エラー値に空白を指定すればいいので例のようになります。
例:IFERROR(VLOOKUP(“★“&ROW(A1), $A$2:$C$6, 3, FALSE ),””)
IFERROR関数の構成要素:(値, エラー値)
検索値をセル参照にする方法
検索値をセル参照にする場合は、ワイルドカードとセル番地をアンパサンド(&)で結合します。
例えば★で始まる文字列なら $A$2$&”*”、★を含む文字列なら “*”&$A$2&”*” です。このように、ワイルドカード記号にはそれぞれ二重引用符を付けます。
検索値をセル参照にすることで、部分一致を含む直感的な抽出が可能です。
例の場合はA2セルに任意の値を入力するだけで、その値を含むセルをすべて抽出します。