FILTER関数を使って「○○で始まる」または「○○で終わる」データを抽出する

ExcelのFILTER関数は、特定の条件に一致するデータを抽出するための非常に便利なツールです。しかし、”○○で始まる”または”○○で終わる”といった前方一致/後方一致の条件を設定することは直接的にはできません。そこで、この記事では、LEFT関数RIGHT関数LEN関数と組み合わせて、部分一致の条件を満たすデータを抽出する方法を紹介します。

サンプルテーブル

A1:D5に以下の表があると仮定します。

年齢検索文字列
佐藤一郎50
岡山二郎30
山田三郎40
岸川四郎20

セル参照を使った前方一致/後方一致抽出

前方一致

まずはセル参照を使って”○○で始まる”データを抽出する方法を見てみましょう。ここではD列の文字で始まるデータを抽出する予定です。以下の計算式を使います:

=FILTER(A2:A5,LEFT(A2:A5,LEN(B2))=B2)

これにより、A列のデータのうち、D2セル(”山”)で始まるデータが抽出されます。式の意味をみていきましょう。


FILTER関数は非常に便利で、指定した条件に合致するレコードを簡単に抽出することができます。

今回の計算式FILTER(A2:C5,LEFT(A2:A5,LEN(D2))=D2)は、以下のような構造となっています。

  1. FILTER(配列, 含む)
    • FILTER関数は、第一引数に指定した検索対象範囲から、第二引数の条件式に合致するレコードを抽出します。
    • 今回はA2:C5の範囲を「配列(第一引数)」としています。
  2. LEFT(A2:A5,LEN(D2))
    • LEFT関数は、指定した文字列の左端から指定した文字数だけの文字を抽出します。
    • LEN関数は、指定した文字の文字数を返します。
    • LEN(D2)は、D2セルに入力されている文字列の文字数を返します。したがって、LEFT(A2:A5,LEN(D2))はA2:A5の各セルから、D2セルの文字数だけの文字を左端から抽出する操作を行います。
  3. LEFT(A2:A5,LEN(D2))=D2
    • この式は、2で抽出した文字がD2セルの文字列と一致するかを確認します。結果として真(TRUE)または偽(FALSE)の配列を返します。
    • そしてこの式が親のFILTER関数の「含む(第二引数)」となっています。

以上の手順により、A2:C5の範囲から、A列の先頭文字がD2セルの文字列と一致するレコードを抽出することができます。


後方一致

同様に、”○○で終わる”データを抽出するには以下の計算式を使います:

=FILTER(A2:C5,RIGHT(A2:A5,LEN(D2))=D2)

これにより、A列のデータのうち、D2セル(”山”)で終わるデータが抽出されます。式の意味をみていきましょう。


今回の計算式FILTER(A2:C5,RIGHT(A2:A5,LEN(D2))=D2)は、以下のような構造となっています。

  1. FILTER(検索対象範囲, 条件式)
    • FILTER関数は、第一引数に指定した検索対象範囲から、第二引数の条件式に合致するレコードを抽出します。
    • 今回はA2:C5の範囲を検索対象としています。
  2. RIGHT(A2:A5,LEN(D2))
    • RIGHT関数は、指定した文字列の右端から指定した文字数だけの文字を抽出します。
    • LEN関数は、指定した文字の文字数を返します。
    • LEN(D2)は、D2セルに入力されている文字列の文字数を返します。したがって、RIGHT(A2:A5,LEN(D2))はA2:A5の各セルから、D2セルの文字数だけの文字を右端から抽出する操作を行います。
  3. RIGHT(A2:A5,LEN(D2))=D2
    • この式は、2で抽出した文字がD2セルの文字列と一致するかを確認します。結果として真(TRUE)または偽(FALSE)の配列を返します。

以上の手順により、A2:C5の範囲から、A列の終端文字がD2セルの文字列と一致するレコードを抽出することができます。

まとめ

FILTER関数は、特定の条件に一致するデータを抽出する強力なツールですが、部分一致の条件を直接設定することはできません。しかし、この記事で紹介したように、LEFT関数RIGHT関数LEN関数と組み合わせることで、”○○で始まる”または”○○で終わる”といった前方一致/後方一致の条件を満たすデータを抽出することが可能になります。これらの関数をうまく活用して、データ分析の幅を広げてみてください。

参考ページ:部分一致検索

“○○を含む”、と検索文字列を中央にしたい場合には別の設定が必要になります。その設定方法については以下のページを参照してください。

FILTER関数で部分一致検索を実現するためのSEARCH関数の活用方法(~を含む)
Excel FILTER関数で部分一致検索を実現するためのSEARCH関数の活用方法(~を含む) ExcelのFILTER関数は、指定した条件に基づいてデータをフィルタリングする非常に便利な関数です。しかし、FILTER関数単体では部分一致...

コメント