FILTER関数で「～を含まない」検索を実現する処理(除外検索)(1・単体)
ExcelのFILTER関数は、指定した条件に基づいてデータをフィルタリングする非常に便利な関数です。しかし、FILTER関数単体では「～を含まない」のような除外検索を行うことはできません。そこで、この記事ではSEARCH関数とISNUMBER関数などを組み合わせて「～を含まない」レコードを抽出する方法を紹介します。
サンプルテーブルの作成
以下のテーブルを例にします。
| 名前 | 性別 | 年齢 |
|---|---|---|
| 山田太郎 | 男 | 34 |
| 佐々木花子 | 女 | 25 |
| 山田涼子 | 女 | 28 |
| 鈴木健一 | 男 | 42 |
| 伊藤薫 | 女 | 35 |
このテーブルから、「山田」という文字列を「含まない」含む名前の行だけを抽出したいとします。
FILTER関数とSEARCH関数を組み合わせて「～を含まない」検索：実例
計算式の確認
FILTER関数単体では「～を含まない」検索が直接できないため、SEARCH関数を使って「～を含まない」検索を実現します。上記のA2:D6の表においてA列に「山田を含まない」レコードを抽出するには以下の計算式を使用します。
=FILTER(A2:C6, NOT(ISNUMBER(SEARCH("山田", A2:A6))), "レコードなし")
この計算式の各部分は以下のように動作します：
- A2:C6：この範囲は、FILTER関数がデータを抽出する元のテーブルを指定します。この例では、名前、性別、年齢を含む5行のデータが対象です。タイトル行・見出しは含めません。1行目は範囲に登録していません。
- NOT(ISNUMBER(SEARCH(“山田”, A2:A6)))：この条件式は、「山田」という文字列を含まない行を抽出するための条件を定義しています。
SEARCH("山田", A2:A6)は、A2からA6の各セル内で「山田」という文字列が存在する位置を検索します。文字列が見つかると、その位置（数値）を返し、見つからない場合はエラーを返します。- ISNUMBER関数は、SEARCH関数の結果が数値（つまり、文字列が見つかった場合）かどうかをチェックします。
- NOT関数により、ISNUMBER関数の結果が偽（FALSE）になる行、つまり「山田」を含まない行が真（TRUE）として評価され、抽出の条件となります。
- “レコードなし”：このテキストは、指定した条件に一致するデータが一つも見つからなかった場合に、FILTER関数によって返されます。
結果として、この式は「山田」を名前に含まない全てのレコードをテーブルから抽出し、名前、性別、年齢の全ての情報を保持した状態で表示します。条件に一致するレコードがない場合は、「レコードなし」というメッセージが表示されます。
実行結果
上記の計算式を実行すると、以下のような結果が得られます。
| 佐々木花子 | 女 | 25 |
| 鈴木健一 | 男 | 42 |
| 伊藤薫 | 女 | 35 |
この結果からわかるように、「山田」という文字列を含まない名前の行だけが抽出されました。
計算式のスクリーンショット
大本の計算式はFILTER関数です。引数「含む」に「NOT(～)」関数を挿入しています。
NOT関数の引数には「ISNUMBER(～)」を挿入しています。～が数値を返しているかを調べます。
ISNUMBER関数の引数いは「SEARCH(～)」を挿入しています。指定単語が含まれていれば数値を返します。
最深部のSEARCH関数です。
FILTER関数とSEARCH関数を組み合わせて「～を含まない」検索：要点整理
ExcelのFILTER関数を使用した「～を含まない」データの抽出方法についての詳細な説明です。
=FILTER(抽出対象範囲全体, NOT(ISNUMBER(SEARCH(検索文字列, 検索対象フィールド))), "空の場合の値")
この式の各部分は以下のように機能します：
- 抽出対象範囲全体：これは、FILTER関数がデータを抽出する範囲です。例えば、A2:C10など、特定の表やデータの範囲を指定します。タイトル行・見出しは含めません。
- NOT(ISNUMBER(SEARCH(検索文字列, 検索対象フィールド)))：この部分が、データを抽出する条件を定義しています。
SEARCH(検索文字列, 検索対象フィールド)は、検索対象フィールド内(検索列)で検索文字列が見つかった場合、その位置（数値）を返します。見つからない場合はエラーを返します。- ISNUMBER関数は、SEARCH関数からの戻り値が数値（つまり検索文字列が見つかった場合）であるかどうかを評価します。
- NOT関数は、その評価結果を反転させます。つまり、検索文字列が見つからなかった場合に真（TRUE）を返します。
- “空の場合の値”：これは、指定した条件に一致するデータが一つも見つからなかった場合に、FILTER関数が返す値です。通常、エラーメッセージや空のテキスト（””）などが指定されます。
結果として、この式は検索文字列を含まないレコードのみを抽出対象範囲から選んで返します。これにより、特定のキーワードを除外したデータ分析が可能になります。
次の記事：「～を含まない」で指定する検索単語を複数にする
参考：FILTER関数で「～を含む」検索
【FILTER関数で「～を含む」検索】をするならば以下のページを参照してください。
コメント