Excel FILTER関数の使い方｜複数条件の抽出・空結果・スピル対策

※本記事にはPRを含む場合があります。

こんにちは。『脱会社員』脱サラを応援し続けるブログ、運営者の『なかびー』です。

「担当者を変えるだけで、その人の作業一覧を表示したい」「未着手の明細だけを別の場所へ並べたい」。こうした場面ではExcelのFILTER関数を使えます。条件に合う複数の行を、元表から別の場所へまとめて返す関数です。合計額や件数を一つ出すのではなく、該当する明細そのものを確認したいときに向いています。

基本は「=FILTER(取り出す範囲,条件,該当なしの表示)」です。一つのセルへ式を入れると、必要な行数と列数へ結果が広がります。この動きをスピルと呼びます。結果の置き場所に別の値がある場合や、条件の範囲がずれている場合は、式だけを直しても期待どおりの一覧になりません。

この記事では八行の架空の作業一覧を使い、一条件の抽出、AND・OR条件、該当なし、スピル障害、更新後の照合まで順に進めます。Microsoft公式資料は2026年10月4日に確認しました。主にMicrosoft 365、Excel 2024・2021などFILTER対応環境を想定し、Excel 2016・2019で同じ関数が使えるとは考えないでください。期待する抽出IDは別計算で検算していますが、Office実機や全端末での動作試験は未実施です。画像は説明用に生成したもので、操作画面の写真ではありません。

FILTERで取り出す明細と条件を決め、対応環境を確認する

表を絞って見る操作と、別の一覧を返す式を分ける

元表の見出しにあるフィルターで行を絞ると、その表の中で条件に合わない行を隠して確認できます。FILTER関数では、元表とは別の場所へ該当する行の値を返せます。元表を残しながら、担当別の確認欄や条件切替式の一覧を作りたい場合に、この違いが役立ちます。

例えば依頼元から「青木さんの仕事を見せて」と頼まれたとき、元表の担当列を手で絞るだけで済むこともあります。一方、毎回同じ確認用シートへ一覧を出したいなら、条件セルとFILTERの式を用意する方法があります。どちらが高度かではなく、一覧をどこで誰が見るかに合わせて選びましょう。

フィルター操作の基本はMicrosoft公式のオートフィルターの説明で確認できます。この記事では、画面上のフィルター設定そのものではなく、元の値を条件式で判定して別のセルへ返す仕組みを扱います。抽出後の結果へ直接入力して原表を更新する機能でもありません。

八行の作業表と出力先を用意する

新しい練習シートのA1:F1へ「作業ID・担当・状態・期限・優先度・工数」と入力し、A2:F9に下の八行を入力します。工数は学習用の数値で、実際の作業時間の予測ではありません。期限はExcelが日付として扱える値で入力してください。氏名は説明上の仮名です。

※表は横にスクロールできます。

J1へ「担当条件」、J2へ青木、K1へ「状態条件」、K2へ未着手、L1へ「期限条件」、L2へ2026/10/8を入れます。出力先はH5とし、H4:M4にはA1:F1と同じ六つの見出しを置きます。H5:M30程度を練習用に空け、ほかの表やメモを置かないようにしてください。条件のJ2・K2・L2は出力領域より上にあるため、結果の拡大と重なりません。

担当が青木の明細はT201・T203・T205・T208の四行です。そのうち未着手はT201・T208の二行。答えとなるIDを先にメモしておくと、表示された行数だけでは気付けない取り違えを検査できます。並びは元表と同じ順番で確認し、後の章で必要な場合だけ並べ替えを加えます。

FILTERが使える環境を、渡す相手も含めて確認する

FILTERは新しい動的配列関数の一つなので、すべてのExcelに同じように存在するわけではありません。作成者の環境で動いていても、受け取る人が古い版を使っていれば、同じファイルを編集・再計算できない可能性があります。ファイルを作る前に、相手が閲覧するだけなのか、条件を変えて使うのかを確認しましょう。

関数の正式な適用先はMicrosoft公式のFILTER関数で確認できます。アプリの名前だけでなく、製品の版と更新状況も見ます。式を入力して関数名が認識されない場合、すぐに綴りの問題だけと決めつけず、対応環境かどうかを確かめてください。

非対応環境の相手が閲覧だけをするなら、抽出結果を値として保存した資料やPDFを別途渡す方法があります。ただし、それは保存した時点の一覧で、条件や元表を変えても自動更新しません。再計算が必要なら、相手の環境で使える手段へ設計を合わせ、動くかを試す必要があります。

一行が何を表すかと、取り出す列を決める

FILTERは、指定した範囲にある値を条件で返します。元表が一行一作業なら作業一覧になり、一行一更新履歴なら同じ作業IDが何度も出る履歴一覧になります。関数を使っただけで最新行だけになるわけではありません。何の明細を取り出しているかを見出しや説明へ残してください。

今回の範囲A2:F9には六列すべてを含めます。担当と期限だけを見たい場合でも、最初の練習ではIDを残すと原表との照合がしやすくなります。IDを消して似た作業の説明だけを並べると、同じ内容が二つ出たときに、同一行の二重表示なのか別の記録なのかを追いにくくなります。

実際の名簿や顧客管理表では、抽出先へ必要な列だけを渡すことも重要です。ただし、非表示の列やシートに元データが残っているブックをそのまま共有すると、表示していない情報まで相手へ渡る可能性があります。抽出条件の正しさと、共有してよい範囲の確認は別の作業として扱いましょう。

一条件のFILTERを作り、スピルする範囲を確認する

三つの引数を分けて入力する

H5へ次の式を入れてEnterで確定します。A2:F9は返したい表全体、B2:B9=J2は担当を比べる条件、「該当なし」は一致する行がないときの表示です。J2が青木なら、四行六列の結果がH5:M8へ広がる想定です。H4:M4の見出しは、この式の外側へ手で用意しています。

=FILTER(A2:F9,B2:B9=J2,"該当なし")

ここでB2:B9=J2は、八行それぞれに対して青木かどうかを調べる比較です。青木の行はTRUE、それ以外はFALSEとして扱える結果になり、その判定に従ってA2:F9の同じ行が返ります。「B列だけを表示する」という指定ではなく、B列で行を選び、A:Fの値を表示する指定です。

結果の先頭IDがT201、続いてT203・T205・T208となっているか確かめます。期限の日付が番号のように表示された場合は、出力された期限列Kの表示形式も確認してください。FILTERは元のセルの見た目を丸ごと複製する目的の関数ではないため、値が正しいかと表示が読みやすいかを分けて点検します。

一つの数式を、結果の行数分コピーしない

FILTERの数式は左上のH5に一つ置きます。H6、H7へ同じ式をコピーすると、複数の式がそれぞれ一覧を広げようとして結果が重なり、エラーの原因になります。行ごとに一つずつ値を返す従来の数式と同じ感覚で、下方向へ埋めないことが大切です。

スピルされた範囲の途中のセルは、その場所だけを独立した結果として書き換えるためのセルではありません。式を変更する場合はH5へ戻ります。抽出された状態が間違っていたら、出力先のC列相当の値を直接直すのではなく、元表の該当IDを探して状態を確認してください。

動的配列の編集方法はMicrosoft公式のスピル配列の動作に説明があります。ここで理解したいのは、「一つの式が一覧全体を管理する」という責任の置き場所です。式のセル、条件のセル、元データのセルを見分けられる配置にしておきましょう。

範囲の高さと行の対応をそろえる

返す範囲A2:F9は八行、条件に使うB2:B9も八行です。列数は返す側が六列、条件側が一列でも構いませんが、今回のように行を選ぶ式では、各行に対する判定が同じ順で対応する必要があります。条件をB2:B8にすると一行少なくなり、範囲をそろえるという前提が崩れます。

また、B3:B10は八行ですが、A2:F9とは一行ずれています。同じ長さだから正しいとは限りません。T201の行をT202の担当で判定するようなずれがあると、式が何らかの結果を返していても、抽出IDが誤る可能性があります。見出しの行を片方だけ含めないことにも注意してください。

練習時は、返す範囲と条件範囲の開始行・終了行を声に出して確認すると分かりやすくなります。元表の列を個別に並べ替えることも避けましょう。担当列だけを並べ替えると、IDと担当の関係そのものが壊れ、どんな関数を使っても元の正しい一覧を返せなくなります。

条件を変えて件数が増減することを試す

J2を佐藤へ変えると、T202・T206の二行が返り、森へ変えるとT204・T207の二行になります。青木へ戻すと四行です。出力先の下へ余計なデータを手入力せず、件数が変わっても式が管理する範囲に収まるかを確認します。結果が少なくなった場所に前の値が残っていないことも見てください。

担当に存在しない「田中」を指定すれば、H5には該当なしと表示されます。これは検索した範囲に条件へ合う行がなかったという結果です。条件セルへ余分な空白があるため一致しなかった場合も同じ表示になるので、該当なしという文字を見ただけで業務上の作業が本当にないと断定しないようにします。

※表は横にスクロールできます。

AND・OR・日付条件を組み合わせ、抽出IDで検算する

AND条件は各比較を括弧で囲んで掛ける

担当がJ2で、かつ状態がK2の明細を返す場合は、二つの比較を掛け合わせます。J2を青木、K2を未着手にした状態で、H5の式を次の形へ置き換えます。比較ごとに括弧を付け、担当の判定と状態の判定を分けて読めるようにします。

=FILTER(A2:F9,(B2:B9=J2)*(C2:C9=K2),"該当なし")

二つともTRUEの行が残るため、IDはT201・T208の二つです。青木でも完了のT203や対応中のT205は外れます。未着手でも別担当のT204・T206も対象外です。抽出した二行だけを確認するのではなく、外れるべき代表行も見ると、ANDの意図が正しいか確かめられます。

条件を増やすときも、先に文章へ戻します。「青木で未着手、さらに優先度が高い」なら比較を一つ追加する設計になりますが、本例では元からその二行の優先度が高いため、結果は変わりません。条件を追加したのに行数が変わらないことが正常な場合もあるので、行数だけで式の有効性を判断しないようにします。

OR条件は足し算でも、一行を重複して返さない

担当が青木、または優先度が高い行を返す場合は、比較を足し合わせます。次の式はJ2に青木を入れた状態で試します。担当条件に合う四行に、優先度が高い森のT204が加わり、T201・T203・T204・T205・T208の五行が返る想定です。

=FILTER(A2:F9,(B2:B9=J2)+(E2:E9="高"),"該当なし")

T201とT208は両方の条件に合うため、比較の足し算は2になります。FILTERではその行を含める判定として働き、同じ元行が二度並ぶわけではありません。二つの抽出一覧を別々に作って縦へ追加する方法とは、重なった行の扱いが異なります。結果のIDを数えて、二重表示がないことを確かめてください。

ただし、元表に同じ内容の行が二つある場合は、FILTERが勝手に一つへまとめることはありません。元の二行がどちらも条件に合えば、二行とも返ります。ORの重なりで一つの行を二度返さないことと、元データの重複を取り除くことは別です。重複が必要な履歴なのか、誤登録なのかは、原表側で調べる必要があります。

期限条件は日付の実体と完了の除外を確認する

L2には期限の基準として2026/10/8を入れています。期限がこの日までで、状態が完了ではない行を取り出すなら、期限の比較と状態の比較を掛け合わせます。今回は期限に時刻を含めない練習表なので、以下という条件をそのまま使います。

=FILTER(A2:F9,(D2:D9<=L2)*(C2:C9<>"完了"),"該当なし")

対象はT201・T202・T205・T206の四行です。期限が10月5日でも完了のT203は外れ、10月8日のT205は含まれます。基準日を10月7日へ変更するとT205が外れるため、境界の日付も試せます。実務で期限へ時刻を含む場合は、「その日の0時まで」か「その日の終わりまで」かを別途定めてください。

日付らしい表示でも、文字列として入っている値は比較の前提が異なります。空欄や日付未確定の作業を抽出対象へどう含めるかも、仕事のルールです。今回の八行には期限空欄がないため、空欄を含む台帳へそのまま適用して完成とはしないでください。未定を別に管理するなど、例外の行を先に確認する工程を加えましょう。

比較結果を別列に出すと、条件の誤りを追える

式が複雑で分かりにくい場合は、出力範囲と重ならないO列を検査用に使います。O1へ「担当一致」、O2へ=B2=$J$2と入力してO9までコピーすると、行ごとのTRUE・FALSEを見られます。P列には状態一致を出すなど、比較を分けて確認すると、どちらの条件で外れたかが分かります。

検査欄は練習用コピーで使い、提出版へ残すかどうかは目的で決めます。補助列を隠すだけで機密情報が消えるわけではありませんが、内部の確認用ファイルでは検査の根拠を残す利点があります。一つの長い式を暗記するより、比較の意味を行ごとに説明できることを優先しましょう。

※表は横にスクロールできます。

該当なし・スピル・参照エラーを分けて直す

第三引数は「該当する行がない」ときの表示を決める

FILTERの第三引数は、条件に合う行がない場合の表示です。省略した式で何も該当しないと、空の配列を返せないため#CALC!となることがあります。学習用では「該当なし」と明示すると、入力条件に一致する明細がないことを見分けやすくなります。

第三引数を""にすれば、空の文字列を返す表示にできます。ただし、これは「ゼロ行の通常の表ができた」と単純に考えないようにします。該当なしの表示を別の関数へ渡して件数や列数を調べる場合、その文字列やセル自体を一つの結果として扱うことがあるためです。後続の式も、該当ありと該当なしの両方で試してください。

また、第三引数はあらゆるエラーを隠すための指定ではありません。条件範囲の中に#N/Aなどが混ざり、比較そのものがエラーになる場合は、その原因を調べる必要があります。該当なしと、計算できない状態を同じメッセージへまとめると、データ修正が必要なことを見落としやすくなります。

#SPILL!は、広がる先の値や結合セルを確認する

#SPILL!、日本語表示では#スピル!が出たら、結果が広がる先に障害がないか見ます。H5から四行六列へ出したいのに、J7へメモが入っていれば、式はそこを勝手に上書きできません。空欄に見えても数式が入っていたり、結合セルがあったりする場合も調査対象です。

エラーのセルを選び、意図したスピル範囲や障害セルを確認する操作はMicrosoft公式の#SPILL!の対処に案内されています。不要なメモなら移動しますが、業務データをまとめて削除して空けるのではなく、何が置かれているかを見てから別の安全な出力先を検討してください。

元データをExcelテーブルにすることと、スピルする式の置き場所は分けて考えます。FILTERの出力式はテーブルの外側へ置く設計にし、元テーブルを構造化参照で読む形にすると整理できます。結果が増える下方向だけでなく、返す列数分の右方向にも空きが必要です。シートの端へ近い位置も避けましょう。

範囲の不一致と、条件列のエラーを順に切り分ける

#VALUE!などが出た場合は、返す範囲と条件範囲の高さ、文字列と数値の混在、比較の記号を順に点検します。最初に担当だけの基本式へ戻し、それが動けば状態、期限という順に条件を増やすと、どの条件で問題が出るかを見つけやすくなります。

元表の担当列や期限列に別の数式があり、そこがエラーを返している場合は、FILTERの式より先に元列を直す必要があります。例えば参照先の一覧が壊れて担当名が#N/Aになっているなら、「担当が青木か」という比較も正常にできません。エラーの起点と、それを引き継いで表示している場所を区別してください。

IFERRORで全体を該当なしへ置き換えると、入力ミス、参照切れ、対応版の問題まで見えなくなる可能性があります。提出版の表示を整える前に、内部用の確認欄で異常を検知できるようにしましょう。結果が空だった理由を説明できない状態は、抽出作業の完了とは言いにくいものです。

別ブック参照と非対応版は、元表とは別の原因になる

同じシート内の八行では動いても、別ブックを参照する式では条件が増えます。Microsoftの案内では、ブック間の動的配列のサポートに制限があり、両方のブックが開いている場合を前提とする場面があります。元ブックを閉じて更新すると#REF!になることがあるため、ファイルの開閉を含めた運用試験が必要です。

非対応の版で関数名が認識されない場合も、出力先を空けるだけでは解決しません。版の確認、使える手段への変更、閲覧用の値の共有という別の判断になります。古い形式へ保存するときは、互換性に関する警告の内容を読み、再度開いたファイルで実際に結果を確認してください。

※表は横にスクロールできます。

更新後の抽出一覧を照合し、再利用できる形で渡す

元表をテーブル化し、追加行が含まれるか確かめる

固定範囲A2:F9のままでは、その外へ追加した行が抽出対象に入らないことがあります。継続して使う一覧では、元データをExcelテーブルにし、「作業一覧」などの分かりやすい名前を付ける方法があります。見出しは今回と同じ六項目にそろえ、FILTERはテーブルの外へ置きます。

=FILTER(作業一覧,作業一覧[担当]=J2,"該当なし")

構造化参照による追加への追従は、Microsoft公式のExcelテーブルの参照方法で確認できます。新しいデータを近くへ貼っただけでは、実際にテーブルへ含まれていない場合もあるため、境界とテーブル名を確かめます。結果が増えないときは、条件を変える前に元の範囲へ入ったかを見てください。

試験用コピーで、担当青木のT209をテーブルの末尾へ一行追加します。担当だけの抽出が四行から五行へ増えるか、T209の期限や工数も同じ行の値で返るかを確認します。その行の担当を森へ変えたとき、青木の一覧から消えることも試せます。追加と条件変更を別々に試すと、どこまで追従する設計かが分かります。

並べ替えを加える前に、空結果の動きを試す

FILTERで返った順序を期限順へ変えたい場合は、SORTと組み合わせる方法があります。例えば担当が必ず存在する練習条件で「=SORT(FILTER(A2:F9,B2:B9=J2),4,1)」とすれば、返した六列のうち四列目の期限を昇順に並べる設計になります。列番号の4は元のExcelのD列という名前そのものではなく、返している配列の四列目です。

ただし、この短い式は該当しない担当を指定した場合の表示を整えていません。第三引数に「該当なし」を追加しただけでも、その一セルだけの結果を四列目で並べ替えようとしても、通常の六列表とは形が違います。空結果を先に判定するなど、目的に合う設計を加え、該当ありと該当なしの双方を試してください。この記事の基本完成形は、並べ替えなしのFILTERです。

SORTの構文と動作はMicrosoft公式のSORT関数で確認できます。同じ期限の作業が複数ある場合、必要な順序があるならIDや優先順位を含む設計も考えます。並べ替えを加えた後も、抽出IDの集合が変わらず、順序だけが変わったことを照合するのが基本です。

抽出結果へ書くメモは、IDと結び付けて管理する

抽出した行の右隣へ手作業のメモを付ける運用には注意が必要です。条件を変えたり元表を追加したりすると、同じ行位置に別のIDが来ることがあります。右隣のメモが以前のまま残れば、別の作業へ誤って結び付いて見えるおそれがあります。行番号ではなく、管理IDを基準に対応を持つ設計が必要です。

更新可能な作業メモは、元の作業台帳の該当IDへ記録するか、ID付きの別のメモ一覧へ保存する方法を検討してください。抽出結果は閲覧用と位置付け、編集する場所を明示すると混乱を減らせます。どこを直せば正本が変わるかを、使う人へ伝えることが大切です。

その時点の一覧へコメントを付けて提出する必要があるなら、更新する抽出シートとは別に、日時を付けた固定版を作る選択肢もあります。値として固定したこと、元の一覧を変更してもその資料は変わらないことを明記します。動く一覧と、提出時点を保存した一覧をファイル名や表題で区別しましょう。

最終確認は条件・ID・更新時点の三つで行う

提出前には、条件セル、抽出されたID、元データの更新時点を確認します。青木・未着手と表示しているのに、式が担当だけの抽出へ戻っていれば、見出しと内容が一致しません。条件の表示と実際の式を照合し、先頭・末尾・境界の代表行が期待どおりであることを確かめてください。

※表は横にスクロールできます。

一覧の項目や業務書類のひな形を整える段階では、当サイトのbizoceanのテンプレートを業務へ取り入れる方法も参考になります。ひな形を使う場合も、FILTERの出力場所へ既存のメモや合計欄が重ならないか、追加行が参照へ含まれるかを確認してから運用してください。

FILTERを使うときは、まず一つの条件で正しいIDを返し、その後に複数条件や期限を加える順序がおすすめです。該当なしと計算エラーを分け、結果が広がる空間を確保し、更新後も原表へ戻って照合しましょう。表示された一覧を、誰がどの条件で作ったか説明できる形にすると、担当が変わっても使い続けやすくなります。