今回は、ExcelのFILTER関数を使って、抽出条件に合うデータを扱いやすくする方法を紹介します。
FILTER関数でできることを整理する
Excelで一覧表から必要な行だけを取り出したいとき、フィルター機能を使う方法があります。画面上で条件を指定して表示を絞るには便利ですが、別の場所に抽出結果を表示したい場合や、条件を変えながら結果を確認したい場合は、FILTER関数が役立ちます。
FILTER関数は、指定した範囲から条件に合う行や列を返す関数です。元の表を残したまま、別のセルに抽出結果を表示できます。条件用のセルを用意しておけば、入力値を変えるだけで結果も更新されます。検索用の簡単な画面をシート上に作りたいときにも向いています。
FILTER関数を使う場面は、次のように整理できます。
- 担当者や部署で一覧を絞り込みたい
- 状態が未対応の行だけを別表に出したい
- 日付や金額などの条件で対象を取り出したい
- 元データを変更せずに確認用の表を作りたい
FILTER関数は、元の表と抽出結果を分けて管理できることが利点です。手動フィルターと違い、条件セルや数式の形で抽出ルールを残せるため、後から見直しやすくなります。
基本の書き方を押さえる
FILTER関数の基本形は、抽出したい範囲、条件、該当なしの場合の表示を指定する形です。たとえば、A列からD列までの一覧表から、C列が指定した担当者名と一致する行だけを取り出す、といった使い方ができます。
考え方としては、「どの範囲を返したいか」と「どの行を残したいか」を分けて指定します。返したい範囲には、抽出結果として表示したい列全体を指定します。条件には、同じ行数を持つ列を使い、真か偽で判定できる式を入れます。
基本を組み立てるときは、次の順番で確認すると分かりやすくなります。
- 元データの範囲を決める
- 抽出結果に表示したい列を決める
- 条件に使う列を決める
- 条件に一致しない場合の表示を決める
該当する行がないときの表示を指定しておくと、エラー表示の代わりに「該当なし」などの文字を出せます。確認用のシートでは、エラーをそのまま出すよりも、見た人が状況を理解しやすくなります。
条件セルを使って抽出しやすくする
FILTER関数を使うときは、条件を数式の中に直接書くより、条件入力用のセルを用意する方が使いやすくなります。たとえば、担当者名を入力するセル、部署名を選ぶセル、状態を指定するセルを作っておけば、数式を直さずに抽出結果を変えられます。
条件セルを使う場合は、見出しや入力欄を分かりやすく配置します。抽出結果の上に条件欄を置くと、何を基準に表示しているかが読み取りやすくなります。入力ミスを減らしたい場合は、データの入力規則で候補を選べるようにする方法もあります。
条件セルを設計するときのポイントは次の通りです。
- 条件入力欄と抽出結果の位置を近づける
- 条件の見出しを短く分かりやすくする
- 選択肢が決まっている項目はプルダウンにする
- 空欄のときにどう扱うかを決めておく
空欄を「すべて表示」として扱うか、「条件なし」としてエラーを避けるかは、使い方に合わせて決めます。利用者が数式を触らなくても抽出できる状態にしておくと、日常の確認作業で使いやすくなります。
複数条件は掛け算と足し算で考える
FILTER関数では、複数の条件を組み合わせることもできます。複数条件を扱うときは、すべての条件に合う場合と、どれか一つに合う場合を分けて考えます。Excelの数式では、条件同士を掛け算でつなぐと「すべて満たす」、足し算でつなぐと「いずれかを満たす」という形にできます。
たとえば、部署が営業部で、状態が未対応の行だけを取り出す場合は、部署条件と状態条件を掛け合わせます。部署が営業部または総務部の行を取り出す場合は、条件を足し合わせます。条件が増えるほど数式が長くなるため、条件セルの配置と見出しを整えることが大切です。
複数条件を作るときは、次の点を確認します。
- AND条件なのかOR条件なのかを先に決める
- 条件に使う範囲の行数をそろえる
- 条件ごとに括弧を付けて読みやすくする
- 条件セルの空欄を許可するか決める
複数条件では、数式の正しさだけでなく、後から読める形にすることが重要です。条件が複雑になる場合は、補助列で判定を作り、その結果をFILTER関数で使う方法も検討できます。
抽出結果の表示範囲を確保する
FILTER関数の結果は、入力したセルから周囲のセルへ広がって表示されます。この仕組みはスピルと呼ばれます。抽出結果の行数が増えると、下方向へ自動で広がります。便利な一方で、表示先に既存の文字や数式があると結果が表示できず、エラーになることがあります。
抽出結果を置く場所には、十分な空白を用意します。表の下にメモや別の表を置くと、抽出件数が増えたときにぶつかることがあります。結果が増減する前提で、周囲の配置を考えることが大切です。
表示範囲を整えるときは、次の点を確認します。
- 抽出結果の下や右に不要な入力がないか
- 見出し行と結果の開始位置が分かれているか
- 結果が増えても別表と重ならないか
- 印刷範囲を固定しすぎていないか
抽出結果を表として見せたい場合は、見出し行を別に作り、結果部分だけをFILTER関数で表示すると見やすくなります。書式は結果が広がる範囲にも届くよう、少し余裕を持って設定しておくと扱いやすくなります。
元データの列構成を安定させる
FILTER関数は、参照している範囲の構成に影響を受けます。元データの列を削除したり、途中に列を追加したりすると、抽出結果の内容が変わることがあります。業務で使うシートでは、元データの見出しや列の役割を安定させることが大切です。
元データをテーブル化しておくと、データが増えたときに範囲が広がりやすくなります。テーブル名や列名を使った数式にすると、範囲の意味も読み取りやすくなります。行が追加される一覧表では、通常のセル範囲よりもテーブル参照の方が管理しやすい場面があります。
元データを整えるポイントは次の通りです。
- 見出し名を分かりやすく付ける
- 同じ列に異なる種類の値を混ぜない
- 空白行を表の途中に入れない
- 後から追加する列の位置を決めておく
FILTER関数は、元データが整っているほど安定します。抽出式だけを工夫するよりも、元の表を入力しやすく、崩れにくい形にしておくことが近道です。
まとめ
ExcelのFILTER関数を使うと、元データを残したまま条件に合う行を別の場所へ表示できます。条件セルを用意すれば、数式を触らずに抽出内容を切り替えられます。
複数条件ではAND条件とOR条件を分けて考え、表示先には結果が広がる余白を用意します。元データをテーブル化し、列構成を安定させると、日常の更新にも対応しやすくなります。FILTER関数は、抽出条件と元データの整え方を合わせて考えることで使いやすくなる機能です。