S051｜Excel関数による部分一致の条件を満たすデータを抽出｜FILTER、IFERROR、FIND、IF、COUNTIF、ROW、SMALL、INDEX

「埼玉県を含む」など、部分一致の条件を満たすデータを抽出するなら、Excel2021/365ではFILTER関数の引数［含む］にIFERROR関数、Excel2019/2016ではIF関数の引数［論理式］にCOUNTIF関数で条件式を入力して抽出する数式を作成しましょう。

例題１｜

1. データを求めるセル（A6セル）を選択し、「=FILTER(」と入力する。
2. ［配列］…抽出する表のセル範囲を選択する。
3. ［含む］…IFERROR関数で「住所」内からA3セルの「東京都」を含む住所を検索し、あれば表内の位置を、なければ「0」を返す数式を入力する。
4. ［空の場合］…省略して、「Enter」キーで数式を確定する。

【数式】=FILTER(A10:C12,IFERROR(FIND(A3,B10:B12),0)>=1)

数式の解説

「=FILTER(A10:C12,IFERROR(FIND(A3,B10:B12),0)>=1)」の数式は、A3セルの「埼玉県」を含む住所を表の「住所」から検索し、ある場合の位置を条件にデータを抽出する。結果、「埼玉県」を含むデータだけが抽出される。

Excelデータダウンロード

以下のリンクを右クリックし、Excelデータをダウンロードください
Excel-sample1.xlsx  

例題２｜

1. D列のセルを選択し、「=IF(」と入力する。
2. COUNTIF関数で、「住所」の一部がA3セルの「埼玉県」である場合の条件式、条件を満たす場合に表示する「ROW(A1)」、満たさない場合に表示する空白［””］を入力したら、「Enter」キーで数式を確定する。
3. 数式を必要な分だけ複写して、INDEX関数の引数［列番号］を抽出する列番号にそれぞれ変更する。
4. 抽出する表の左上のセル（A6セル）を選択し、IFERROR関数、INDEX関数、SMALL関数、ROW関数を使って数式を作成して抽出する。

【数式】=IFERROR(INDEX($A$10:$C$12,SMALL($D$10:$D$12,ROW(A1)),1),””)

数式の解説

「COUNTIF(B10,“*”&$A$3&“*”)」の数式は、A3セルの「埼玉県」を含む「住所」の個数を求める。

IF関数の引数［論理式］に組み合わせることで、B10セルの「住所」の中にA3セルの「埼玉県」を含む場合は表内の番目を求め、違う場合は空白［””］を求める。数式をコピーすることで、条件「埼玉県」を含むデータの行にだけ表内の番目が付けられる。

D列に作成した表内の番目を使い、IFERROR関数、INDEX関数、SMALL関数、ROW関数を使って数式を作成して抽出することで、「埼玉県」を含むデータが抽出される。

Excelデータダウンロード

以下のリンクを右クリックし、Excelデータをダウンロードください
Excel-sample2.xlsx  

応用１｜

Excelデータダウンロード

以下のリンクを右クリックし、Excelデータをダウンロードください
Excel-sample3.xlsx