ＥｘｃｅｌのＦＩＬＴＥＲ関数で自分の未完了案件を出したい！条件の掛け算とスピル、該当なしの表示から共有前の確認まで一緒に進めよう

この記事には広告・アフィリエイトリンクが含まれます。

案件の表を開くたびに、自分の名前を探して、終わった仕事を飛ばして、残りを別の場所へコピーする。たとえば、そんな確認を毎朝しているなら、取り出す条件を数式へ渡す方法があります。

使うのは、ExcelのFILTER関数です。今回は「担当が自分」「状態が未完了」という二つの条件で、元の一覧から必要な行を別の場所へ表示してみましょう。

一行ずつ式をコピーしなくても、結果の行数に合わせて広がるんです。ああ、だから先に表示先を空けておくのねっ！
便利な部分と、つまずきやすい部分がつながっています。

ただ、数式を入れれば案件の正しさまで判断してくれるわけではありません。表の「未完了」が本当に未完了かは、人が更新するところ。式に任せるのは、決めた条件で行を取り出す作業です。

以下の案件と名前は説明用の架空データです。実際の仕事の一覧を変更する前に、空のシートなどで少ない行から仕組みを見てみてください。関数を使えるExcelの版、表示する場所、条件の意味を順にそろえていきますね。

まず５件だけ。どの列を返し、どの列で選ぶかを分けよう

MicrosoftのFILTER関数の案内では、Excel 2021、Excel 2024、Microsoft 365などが対象に挙げられています。ここでは、その関数を使える環境で進めることを前提にします。古い版へ式を貼り、名前を直せば必ず使える、とは考えず、利用しているExcelの版を先に確認してください。

練習用に、Ａ１〜Ｄ６へ次のような小さい表を作るとします。１行目が見出し、２〜６行目がデータです。表では見やすいように行番号も添えましたが、行番号の欄をＡ列へ入力するのではなく、「案件」をＡ１へ置きます。

Ｈ１には「葵」と入力します。そして、結果を表示したいＪ２へ次の数式を一つ入れる、という設定です。数式内の英字、数字、記号は半角で入力してください。

=FILTER(A2:D6,B2:B6=H1,"該当なし")

最初のA2:D6は、返したいデータの範囲。案件名だけではなく、担当、状態、優先度まで四列を返します。次のB2:B6=H1は、担当の列がＨ１と一致するかを行ごとに調べる条件。最後の「該当なし」は、一件も条件に合わないときに表示する文字です。

この設定なら、担当が葵の案件Ａ・Ｃ・Ｄが対象です。Ｃ列が「進行中」の案件Ｃも含みます。今の式が見ているのは担当だけなので、ここで未完了だけにならなくても、式の失敗ではありません。二つ目の条件は、次の節で足しましょう。

大事なのは、返す範囲と選ぶ範囲の行をそろえること。A2:D6に対して担当をB2:B5までしか見ない式へすると、対象の高さが合いません。見出しを含めるかも統一し、今回は両方とも２行目から６行目です。

結果の見出しを付けたいなら、Ｊ１〜Ｍ１へ「案件・担当・状態・優先度」を別に用意します。今回の式はA2:D6を返すので、元の１行目は含んでいません。まず、どの四列がどこへ出るかを決めると、条件の説明がぐっと追いやすくなります。

抽出の仕組みを本で続けたい場合は、不二桜さんの『ほしいデータを瞬時に「検索」「出力」するExcel活用術』が候補です。出版社の目次に、FILTERでの抽出、複数条件、スピルへの対応が明記されています。以下の案件表は、この本の練習ファイルの転載ではなく、ここでの説明用です。

FILTERによる抽出を扱うExcel活用術の掲載商品を楽天市場で確認する

構文と対応環境はMicrosoftのFILTER関数の説明、本の構成は技術評論社の紹介・目次で確認できます。

「葵、かつ未完了」は掛け算。「または」に替えると何が変わる？

担当だけを選べたら、Ｈ２へ「未完了」と入力します。今度は、葵の案件の中でも、状態が未完了の行だけを取り出す条件です。Ｊ２の数式を次の形へ変えます。

=FILTER(A2:D6,(B2:B6=H1)*(C2:C6=H2),"該当なし")

括弧の中で、担当の一致と状態の一致を、それぞれ調べています。その二つを掛け算の「*」でつなぐと、両方を満たす行が対象になります。今回なら、案件Ａと案件Ｄの二件です。

案件Ｃは担当が葵ですが、状態は進行中。だから外れます。案件Ｅは未完了ですが、担当が蓮。これも外れます。式を長い文字列として見るより、「どちらが一致しなかった？」と一行ずつ見るほうが、条件の意味をつかめますよ。

ここで、一つ立ち止まりたいところがあります。「進行中」も、仕事としてはまだ終わっていませんよね。でも、この式で選ぶのは、文字として「未完了」と一致する行だけ。「完了していない全部」を自動で解釈しているわけではありません。

自分の表で「未着手」「進行中」「確認待ち」を別々に使っているなら、何を一覧へ出したいのかを先に決めます。今回は仕組みを見るため、状態が「未完了」と書かれた行に絞ります。実務へ移すときは、呼び名と抽出したい範囲をチームでも確認してください。

では、条件を「担当が葵、または状態が未完了」にしたい場合は？
今度は、二つの条件を足し算の「+」でつなぎます。

=FILTER(A2:D6,(B2:B6=H1)+(C2:C6=H2),"該当なし")

この場合は案件Ａ・Ｃ・Ｄ・Ｅが対象です。葵の担当という理由でＣが入り、未完了という理由でＥも入ります。両方を満たす案件ＡとＤが二回ずつ表示されるわけではなく、その行を含める条件として評価されます。

同じ表、同じ二つの言葉でも、つなぎ方で欲しい一覧が変わります。「数式が出た、よし！」の後に、対象の案件名まで見る理由がここにあります。まず５件で正解を目で追える状態にしてから、自分の表へ進めると判断しやすいです。

本で読み進めるなら、複数条件で抽出する項目を目次から探せる一冊が向きます。今回の書籍は、その項目を出版社の案内で確認できるので、関数名だけを一覧で眺める本とは、選ぶ理由が具体的になります。

複数条件の抽出を学ぶ一冊の販売内容を確認する

結果が下へ広がるのがスピル。置く場所を空けるところまでが準備

Ｊ２に式を一つ入れたのに、Ｊ列からＭ列へ、さらに下の行へ結果が出る。この動きを「スピル」と呼びます。今回の返す範囲は四列なので、結果も四列分の場所を使います。

担当だけの条件で三件出るなら、Ｊ２〜Ｍ４。葵かつ未完了の二件なら、Ｊ２〜Ｍ３が結果の場所になります。この例の件数に応じた範囲であって、どんな表でも三行までしか広がらない、という意味ではありません。

だから、式を入れるＪ２だけを空けても足りないんです。右隣と下へ結果を置ける空き場所が必要になります。Ｊ３へ「明日確認」とメモを書いていたら、そこが結果と重なる可能性があります。元の表を避けただけでなく、返ってくる四列と行数も考えて置きましょう。

「#SPILL!」が出たら、いきなり式を別の関数へ替える前に、結果の場所を見ます。Microsoftは、空でないセルが邪魔している場合、エラーの確認から障害物のセルを選び、内容を移すか削除する方法を案内しています。

ただし、共有ファイルの大事なメモを消してよい、という話ではないですよ。何が入っているかを読み、残す必要があるなら別の場所へ移します。表示先をもっと広い空き場所へ変える方法もあります。セルが空に見えるかだけで決めず、実際の内容を確認してください。

結合されたセルも、スピル先にはできません。見出しを大きく見せるために結合した場所へ結果がぶつかっていないかを見ます。表示先は、まず普通のセルが並んだ場所を選ぶと仕組みを追いやすいですね。

もう一つ、Excelの「テーブル」との関係も分けて覚えたいところ。元データをテーブルにして参照することはできますが、スピルする数式そのものは、テーブルの外のセルへ置きます。元の一覧をテーブルにしたから、出力の式もその中へ入れる、とはしません。

結果の途中の一行だけ、手入力で直そうとしても、その場所はスピルした結果です。数式を変えるなら左上のＪ２、元の担当や状態を変えるなら元表の該当セルへ戻ります。表示された場所へ直接打って、元データも更新されたつもりにならないようにします。

あ、直す場所が二つに分かれていたんだね。
数式の条件を変えるのか、案件の状態を更新するのか。そこが決まると、次に触るセルも具体的になります。

スピルの説明もあるExcel活用術を楽天市場で探す

動きと編集位置はMicrosoftの動的配列・スピルの説明、障害物や結合セルはスピルエラーの案内を参照しています。

該当なしとエラーは別。行を足した日にも範囲を見よう

練習表のＨ１を、表にない担当名へ替えたらどうなるでしょう。今回の式は最後に「該当なし」を指定しているので、条件に合う行がなければ、その文字を返す設定です。

これは、全てのエラーを隠す設定ではありません。Microsoftの説明では、条件側にエラーがある場合などはFILTER自体もエラーになります。「該当なし」を書いたから、元表の間違いも全部処理できる、とは考えないでください。

条件に合う行がないときの指定を省くと、空の配列を返せず、#CALC!になる場合があります。初心者向けの一覧では、空白だけを返すより「該当なし」のような言葉を入れると、何も表示されない理由を見分ける手がかりになります。

反対に、文字が少し違っているだけで該当しなくなる場合もあります。担当名や状態に、余分な空白、表記の違いがないかを見る。式の記号を全部書き直す前に、Ｈ１・Ｈ２と元の列の文字を並べて確認しましょう。

次は、案件を７行目へ追加した日です。最初の式のA2:D6は、６行目まで。７行目の新しい仕事は、その範囲に入っていません。「新しい行なのに表示されない」ときは、条件に合うかだけでなく、返す範囲と条件の範囲へその行が含まれるかも見ます。

範囲を伸ばすなら、返すA2:D6だけでなく、担当のB2:B6、状態のC2:C6も同じ終わりへ合わせます。一つだけ７へ変えると、高さが合わなくなる原因になります。最初に行をそろえた理由が、ここでもう一度役立ちます。

増え続ける元表では、Excelのテーブルと構造化参照を使う方法もあります。Microsoftは、テーブルの行の追加や削除に応じて参照が調整されることを説明しています。ただ、今のセル範囲の式へ名前だけを適当に足すのではなく、テーブル名と列名の参照を学んでから移してください。今回は、固定の５件で条件を理解するところまでを先に進めます。

書籍で次へ進むなら、FILTERだけでなく、テーブルや元データの変更・追加への対応が目次にあるかを見ると選びやすくなります。今回の一冊は、そのつながりを出版社の紹介で確かめられます。

行の追加や抽出方法を続けて学ぶ本の掲載を確認する

共有する前に、二件の意味を一行添えよう

葵の未完了が二件出たら、次は見る相手のことを考えます。画面に二行だけ見えても、その人には「担当で絞ったのか」「未完了だけなのか」が分からないことがあります。

たとえば「担当：葵／状態：未完了との一致／元表の２〜６行を対象」と、条件と対象範囲をそばに書く。これは説明用の添え書きです。実際の仕事なら、元表をいつ更新したかも分かるようにすると、古い一覧を現在の状況と取り違える迷いを減らせます。

また、別のブックにある元表を参照する動的配列には制限があります。Microsoftの案内では、両方のブックを開いている場合に対応し、参照元を閉じて更新すると#REF!が返る場合があります。自分のＰＣで表示できたから、送付先でも同じように更新できる、とは決めないでください。

数式が動く一覧を渡したいのか、その時点の内容を渡したいのかも分けます。後者で値として別の資料へ移すなら、その後は元表を変えても同じように連動しません。配る物の用途に合わせ、受け取る人へ更新の扱いを伝えましょう。

抽出して見える件数と、ブックに含まれるデータの範囲も別です。必要な二件が表示されていても、元の一覧が同じブックに残っているなら、共有してよい内容かは別に確認します。FILTERは行を選んで表示する関数で、見せてよい相手を判断する機能ではありません。

今回紹介した本は、紙書籍のＩＳＢＮが９７８４２９７１２６５１３です。確認時の楽天検索には中古の紙書籍や電子書籍の掲載がありました。紙で買いたいなら本の状態、電子版なら形式や対応する読み方を確認し、同じ題名だけで判断しないでください。

『ほしいデータを瞬時に「検索」「出力」するExcel活用術』の販売状態を見る

最初の一歩は、自分の仕事を全部移すことではなく、この５件で「ＡとＤが出る理由」を説明できるところまで。担当と状態を一つずつ見て、掛け算で両方を満たす行へ絞る。それが分かれば、自分の表のどこをそろえるかも見えてきます。

毎朝の一覧を、探し直すところから始めなくていい形へ。まず一つ、どんな条件の仕事を見たいかを決めてみよう。数式は、その答えが決まってから一緒に組み立てていけば大丈夫です！