～
ad. 価格範囲を指定して商品を探せます
Excelで検索語を含む行を抽出して表示する方法
Excelでは、特定の文字列を含む行だけを別の場所へ表示したいことがあります。
Excel 365・Excel 2021以降で利用できるFILTER関数を使えば、検索語に一致する行だけを自動で抽出できます。
この記事では、検索語に部分一致した行を一覧表示する方法を解説します。
データの例
次のようなデータがあるとします。
| A | B | C | |
|---|---|---|---|
| 1 | りんご | みかん | ぶどう |
| 2 | いちご | なし | すいか |
| 3 | さくらんぼ | メロン | バナナ |
| 4 | もも | キウイ | りんご |
| 5 | りんご |
A5セルに検索語（例：りんご）を入力し、その文字を含む行だけをA6セル以降へ表示します。
FILTER関数で抽出する方法
手順
- A5セルに検索したい文字列を入力します。
- A6セルに次の数式を入力します。
=FILTER(A1:C4,BYROW(A1:C4,LAMBDA(r,SUMPRODUCT(--ISNUMBER(SEARCH(A5,r)))>0)),"該当なし")Enterキーを押すと、検索語を含む行だけが自動で表示されます。
数式の解説
FILTER：条件に一致する行だけを抽出します。BYROW：各行ごとに条件を判定します。LAMBDA：行ごとの判定処理を定義します。SEARCH(A5,r)：検索語が行内の各セルに含まれるか調べます（部分一致）。ISNUMBER：検索語が見つかったセルをTRUEに変換します。SUMPRODUCT(...)：その行に検索語が1つ以上あればTRUEを返します。
実行結果
A5セルに「りんご」と入力すると、次のように表示されます。
| A | B | C | |
|---|---|---|---|
| 6 | りんご | みかん | ぶどう |
| 7 | もも | キウイ | りんご |
検索語を変更すると、抽出結果も自動的に更新されます。
検索語が見つからない場合
FILTER関数は一致するデータがないと#CALC!エラーになります。
第3引数に表示する文字列を指定すると、エラーではなく任意のメッセージを表示できます。
=FILTER(A1:C4,BYROW(A1:C4,LAMBDA(r,SUMPRODUCT(--ISNUMBER(SEARCH(A5,r)))>0)),"該当なし")また、他のエラーもまとめて処理したい場合は、IFERROR関数を組み合わせる方法もあります。
=IFERROR(FILTER(A1:C4,BYROW(A1:C4,LAMBDA(r,SUMPRODUCT(--ISNUMBER(SEARCH(A5,r)))>0))),"該当なし")注意点
- FILTER関数はExcel 365・Excel 2021以降で利用できます。
- SEARCH関数は部分一致で検索し、大文字・小文字は区別しません。
- 抽出結果はスピル機能で表示されるため、表示先にデータがあると#SPILL!エラーになります。
まとめ
- 検索語を含む行はFILTER関数で簡単に抽出できる。
- SEARCH関数を組み合わせることで部分一致検索が可能。
- BYROW・LAMBDAを使うことで、行単位で検索条件を判定できる。
- 一致するデータがない場合は、第3引数またはIFERRORを利用すると見やすい。
使用した関数について
FILTER関数で条件に一致する行のデータを求める方法と複数条件や代用方法についてわかりやすく解説
FILTER関数についてFILTERの概要条件に一致する行のデータを求めるExcel関数=FILTER( 範囲 , 条件 , 一致しない場合 )概要 条件に一致する行を取り出す対応バージョン：365 2021 Office365限定です スピル配列として出力されます。 LOOKUPやVLOOKUPは値を取り出したが、F...
programming-cafe.com
BYROW関数とLAMBDA関数で行単位処理を適用する方法についてわかりやすく解説
BYROW関数についてBYROW関数の概要行単位で処理を適用するExcel関数=BYROW(配列, LAMBDA)概要 BYROW関数は、指定した配列の各行にカスタム処理を適用し、その結果を返します。LAMBDA関数と組み合わせて利用することで、柔軟な処理が可能です。 行ごとに個別の計算を適用したい場合に便利です。 L...
programming-cafe.com
IFERROR関数でエラー時の値を指定する方法とVLOOKUPとの組み合わせ方についてわかりやすく解説
IFERROR関数についてIFERRORの概要エラー時の値を指定Excel関数=IFERROR( 値, エラー時の値 )概要 指定した値がエラーの場合に、代替の値を返します。エラーでない場合はその値を返します。 #DIV/0! や #VALUE! などのエラーを処理できます。 数式のエラーによる表示崩れを防ぐために役立...
programming-cafe.com
LAMBDA関数でカスタム関数を使用する方法をわかりやすく解説
LAMBDA関数についてLAMBDAの概要カスタム関数の作成Excel関数=LAMBDA(引数1, 引数2, ..., 処理)概要 LAMBDA関数は、Excelでカスタム関数を作成するための関数です。定義した引数を用いて処理を記述し、関数のように利用できます。 LAMBDA関数を用いると、ワークシート上で関数を定義で...
programming-cafe.com
SEARCH関数で指定文字の位置を求める方法についてわかりやすく解説
SEARCH関数とSEARCHB関数についてSEARCHの概要セル内の指定文字の位置を求めるExcel関数=SEARCH( 文字列 , 対象 )=SEARCHB( 文字列 , 対象 )概要 セルの指定した文字の位置を求める 文字列は1文字である必要はない SEARCHは大文字と小文字の区別をしないが、FINDは区別する...
programming-cafe.com
SUMPRODUCT関数でセルの乗算値の合計を求める方法と割算の合計方法についてわかりやすく解説
SUMPRODUCT関数についてSUMPRODUCTの概要セルの乗算値の合計を求めるExcel関数/数学=SUMPRODUCT( 数値1 , 数値2 , 数値3 ,,, )概要 セルの乗算数の合計値を求める カンマ区切りで乗することができる 行単位で乗じて、合計してゆく。各数値ごとに乗じているわけではない 値が入ってい...
programming-cafe.com