Excelの表から、条件に合う行だけを別の場所へ取り出したい。
FILTER関数を使えばできる、というところまでは分かった。条件がひとつなら動く。
ところが、条件を2つにしたとたん、手が止まります。
カンマで区切るのか。AND関数を使うのか。どちらでもエラーになる。
答えを先に書きます。
条件を両方とも満たしたいときは、掛け算です。どちらか一方でよければ、足し算です。
この記事では、その理由と、そこから先でつまずきやすい場所を、症状ごとに8つ取り上げます。困っているところだけ読んで帰っていただいて大丈夫です。
この記事の前提
FILTER関数は、Microsoft 365のExcel、またはExcel 2021以降で使えます。それより前のバージョン（Excel 2019、2016など）には入っていません。
画面は、A列に氏名、B列に支店、C列に商品、D列に日付、E列に売上が入った一覧表（2行目から23行目）で説明します。
先に結論。条件が2つなら掛け算、どちらかなら足し算
東京支店で、なおかつ売上が10万円以上。この2つを両方とも満たす行だけを出すなら、こう書きます。
=FILTER(A2:E23,(B2:B23="東京")*(E2:E23>=100000),"該当なし")
東京支店、または大阪支店。どちらか一方に当てはまればよいなら、こう書きます。
=FILTER(A2:E23,(B2:B23="東京")+(B2:B23="大阪"),"該当なし")
違いは、条件と条件のあいだに置いた記号だけです。
掛け算（アスタリスク）がAND、足し算（プラス）がOR。
条件はそれぞれ丸括弧で囲みます。ここを囲み忘れると、計算の順番が変わって結果が合わなくなります。
なぜ掛け算で絞り込めるのか
記号を覚えるだけでも書けますが、理由が分かると条件を増やしたときに迷わなくなります。
FILTER関数の2つ目に入れているものは、じつは「条件」ではありません。TRUEとFALSEの一覧です。
試しに、条件の部分だけを空いているセルに入れてみてください。
=B2:B23="東京"
TRUEとFALSEが、行の数だけ縦に並びます。B列が東京になっている行がTRUE、それ以外がFALSEです。
FILTER関数は、この一覧を見て、TRUEの行だけを表示しています。
そしてExcelでは、計算に使うときTRUEは1、FALSEは0として扱われます。
ここまで分かると、掛け算の意味がはっきりします。
| 1つ目の条件 | 2つ目の条件 | 掛け算の結果 |
|---|---|---|
| TRUE（1） | TRUE（1） | 1×1＝1 → 表示される |
| TRUE（1） | FALSE（0） | 1×0＝0 → 表示されない |
| FALSE（0） | TRUE（1） | 0×1＝0 → 表示されない |
| FALSE（0） | FALSE（0） | 0×0＝0 → 表示されない |
掛け算は、どちらか一方でも0があれば0になります。 だから「両方ともTRUEの行だけ」が残ります。
足し算なら逆です。
| 1つ目の条件 | 2つ目の条件 | 足し算の結果 |
|---|---|---|
| TRUE（1） | FALSE（0） | 1＋0＝1 → 表示される |
| FALSE（0） | TRUE（1） | 0＋1＝1 → 表示される |
| FALSE（0） | FALSE（0） | 0＋0＝0 → 表示されない |
FILTER関数は0以外の行を表示するので、どちらかがTRUEなら残ります。
覚え方としては、掛け算はAND、足し算はOR。これだけで大丈夫です。
1. 条件を3つ以上にしたい
理屈が分かっていれば、そのまま増やせます。丸括弧で囲んだ条件を、掛け算でつないでいくだけです。
東京支店で、商品がマウスで、売上が10万円以上。3つとも満たす行なら、こうなります。
=FILTER(A2:E23,(B2:B23="東京")*(C2:C23="マウス")*(E2:E23>=100000),"該当なし")
1×1×1で1。ひとつでも当てはまらなければ、どこかが0になって全体が0になります。条件が4つでも5つでも、考え方は変わりません。
2. ANDとORを組み合わせたい
「東京支店または大阪支店で、なおかつ売上が10万円以上」のように、2種類を混ぜたいこともあります。
このときは、先にまとめたいほうを丸括弧でくくります。
=FILTER(A2:E23,((B2:B23="東京")+(B2:B23="大阪"))*(E2:E23>=100000),"該当なし")
「東京または大阪」の部分を、外側の丸括弧でひとまとめにしてから、売上の条件と掛け算しています。
算数の式と同じで、括弧でくくったところが先に計算されます。ここを囲まないと、東京の行がそのまま出てしまったり、意図と違う結果になります。
括弧が増えて読みにくくなったときは、いちばん内側から順番に見ていくと、どこまでが一組か分かります。
3. 条件を変えるたびに、数式を書き直している
さきほどの数式は、条件を数式の中に直接書きました。この形だと、東京から大阪に変えるたびに数式を開くことになります。
条件をセルに入力して、そこを参照させます。
たとえばH2セルに支店名、H10セルに売上の下限を入れておくなら、こう書きます。
=FILTER(A2:E23,(B2:B23=H2)*(E2:E23>=H10),"該当なし")
H2を「大阪」に変えれば、そのまま大阪支店の結果に切り替わります。数式は触りません。
条件セルにドロップダウンリスト（入力規則）を設定しておくと、支店名を選ぶだけになるので、さらに扱いやすくなります。
4.「田中*」と入れても、出てこない
氏名がフルネームで入っている表から、名字だけで探したいことがあります。
検索欄に「田中」と入れて、次のように書いたとします。
=FILTER(A2:E23,A2:A23=H4,"該当なし")
これでは「田中 美咲」は出てきません。「田中」と「田中 美咲」が完全に同じ文字かどうかを比べているからです。
ここで、後ろにアスタリスクを付けて「田中*」にすればよいのでは、と思われた方もいるかもしれません。
試しても、やはり出てきません。
理由は、イコールを使った比較では、アスタリスクがワイルドカードとして働かないからです。この書き方では、「田中*」というアスタリスク込みの文字列そのものと比べてしまいます。セルに文字どおり「田中*」と入っていない限り、一致しません。
アスタリスクは、どの数式でも使える万能な記号ではありません。ワイルドカードに対応した関数の中で使ったときだけ働きます。
対応している代表的な関数は、次のとおりです。
- COUNTIF、SUMIF、AVERAGEIF
- SEARCH
- XLOOKUP、XMATCH
- VLOOKUP、MATCH
ただし、指定の仕方は関数によって違います。たとえばXLOOKUPでは、一致モードに「2」を指定してはじめてワイルドカードが使えます。
なお、ワイルドカードの意味は次の2つです。
*… 何文字でもよい?… 任意の1文字
5. 名字だけで検索したい（部分一致・「含む」で絞る）
アスタリスクを使わずに、氏名の一部で検索する方法があります。SEARCH関数とISNUMBER関数を組み合わせます。
=FILTER(A2:E23,ISNUMBER(SEARCH(H4,A2:A23)),"該当なし")
長く見えますが、内側から見ると単純です。
まず、SEARCH関数です。
=SEARCH(H4,A2:A23)
SEARCH関数は、H4セルの文字が、それぞれの氏名の何文字目にあるかを返します。「田中 美咲」の中の「田中」は1文字目から始まるので、1が返ります。「マルクス 田中」のような名前なら、5が返ります。
見つからない氏名は、数字ではなくエラーになります。
ただ、FILTER関数が受け取りたいのは、数字やエラーではなくTRUEとFALSEです。そこでISNUMBER関数を外側にかぶせます。
=ISNUMBER(SEARCH(H4,A2:A23))
ISNUMBER関数は、中身が数字ならTRUE、数字でなければFALSEを返します。これで、見つかった行がTRUE、見つからなかった行がFALSEになりました。
あとはFILTER関数が、TRUEの行だけを表示します。検索欄に「田中」と入れるだけで、「田中 美咲」「田中 健太」「田中 裕子」がまとめて出てきます。
SEARCHとFINDの違い
同じように探す関数にFIND関数があります。FIND関数でも同じ形で書けますが、FIND関数はアルファベットの大文字と小文字を区別します。
表記がそろっていない表を検索するなら、区別しないSEARCH関数のほうが扱いやすいです。
6. 検索欄を空にすると、全員出てしまう
部分一致で検索できるようにすると、検索欄を空にしたときに全員分が表示されます。
不具合ではありません。空の文字は、どの氏名にも含まれていると判断されるためです。
条件を外したときに全部出てほしいなら、そのままで問題ありません。何も出したくない場合は、検索欄が空のときだけ別の表示にする、といったひと工夫が要ります。
7. 日付を条件にすると、うまく絞れない
たとえば「2026年9月1日以降」で絞りたいとき、数式の中に日付をそのまま書くと、思ったように動かないことがあります。
数式の中で日付を指定するなら、DATE関数を使います。
=FILTER(A2:E23,D2:D23>=DATE(2026,9,1),"該当なし")
DATE関数は、DATE(年,月,日) の順に、年・月・日を分けて指定します。
日付をセルに入力しておいて、そこを参照させる形でもかまいません。
=FILTER(A2:E23,D2:D23>=H13,"該当なし")
文字で入力した日付をDATEVALUE関数でシリアル値に変換する方法もありますが、文字の日付はパソコンの設定によって解釈が変わることがあります。数式の中で日付を指定するなら、年・月・日を分けて渡せるDATE関数のほうが確実です。
8. 抽出した日付が「46301」のような数字になる
日付を含む範囲を取り出したら、日付が見覚えのない数字になることがあります。
FILTER関数が日付を壊したわけではありません。
Excelは、日付や時刻をもともと「シリアル値」という数字で管理しています。日付は基準の日から何日目かを表す整数、時刻は1日を1とした小数です。お昼の12時は1日の半分なので、0.5になります。
ふだんそれが日付に見えているのは、セルの表示形式が日付になっているからです。
そして、FILTER関数は元の表から値だけを取り出します。セルの色も、罫線も、表示形式も引き継ぎません。 取り出し先のセルが「標準」のままだと、日付は中身のシリアル値のまま表示されます。
直し方はかんたんで、抽出結果の列を選んで、表示形式を「日付」に変えるだけです。時刻なら「時刻」にします。
日付まわりで覚えておくことは、この2つです。
- 条件に日付を直接書くときは、DATE関数
- 抽出結果が数字になったら、表示形式を変える
同じ名前が何度も出る、並べ替えたい
FILTER関数は、ほかの関数と組み合わせると、さらに使いやすくなります。
東京支店の担当者の名前だけを取り出すと、同じ人が何度も出てきます。取引の数だけ行があるので当然ですが、一覧としては読みにくくなります。
重複を1件にまとめるなら、UNIQUE関数を外側にかぶせます。
=UNIQUE(FILTER(A2:A23,B2:B23="東京",""))
内側のFILTER関数で東京支店の担当者を取り出し、外側のUNIQUE関数で重複を取り除いています。支店ごとの担当者一覧や、特定の商品を買った顧客の一覧を作るときに便利です。
売上の高い順に並べたいなら、SORT関数です。
=SORT(FILTER(A2:E23,B2:B23="東京","該当なし"),5,-1)
SORT関数の2つ目に入れている「5」は、並べ替えの基準にする列の番号です。今回取り出している表は、1列目が氏名、2列目が支店、3列目が商品、4列目が日付、5列目が売上なので、売上を基準にするなら5になります。
最後の「-1」は並べる向きです。1なら小さい順、-1なら大きい順です。
関数が重なっても、いちばん内側から順番に見ていけば、何をしているかは追えます。
条件に合うデータが無かったとき
FILTER関数の3つ目に入れている "該当なし" は、条件に合う行がひとつも無かったときに表示する文字です。
=FILTER(A2:E23,B2:B23="横浜","該当なし")
表に無い支店名を指定すると、「該当なし」と表示されます。ここを省くこともできますが、そのときはエラーが表示されます。見た目の分かりやすさのためにも、入れておくことをおすすめします。
つまずきやすいところ
最後に、書いているときに引っかかりやすい点をまとめます。
- 条件のまわりの丸括弧を忘れない。 条件が2つ以上のときは、ひとつずつ丸括弧で囲みます
- ANDとORを混ぜるときは、まとめたいほうを先に括弧でくくる
- 結果が広がる先に、何か入っていると表示されません。 FILTER関数の結果は必要な行数だけ下へ広がります（スピル）。その範囲に別のデータがあるとエラーになるので、出力先は空けておきます
- 見出しは抽出範囲に含めない。 見出しごと取り出すと結果にも見出しが混ざります。結果側の見出しは、あらかじめ用意しておくほうが扱いやすいです
- 数式が長くなったら、内側から。 一番内側の関数だけを別のセルに入れて、何が返っているかを見ると、どこが違うのか分かります
まとめ
FILTER関数の複数条件は、記号ふたつで決まります。
- 両方とも満たしたい（AND）→ 掛け算
- どちらかでよい（OR）→ 足し算
そうなる理由は、FILTER関数が受け取っているのが条件そのものではなく、TRUEとFALSEの一覧で、TRUEは1、FALSEは0として計算されるからです。掛け算なら0がひとつでもあれば0、足し算なら1がひとつでもあれば1。だから掛け算はAND、足し算はORになります。
ここが分かっていれば、条件を3つに増やすのも、ANDとORを混ぜるのも、同じ延長で書けます。
アスタリスクは、イコールの比較では働きません。名字だけで探したいときは、SEARCH関数とISNUMBER関数を組み合わせます。
日付を条件にするときはDATE関数。抽出した日付が数字になったら、表示形式を日付に変える。
実際の画面で順番に確認したい方は、動画でも解説しています。
【Excel】FILTER関数の複数条件｜ANDは掛け算、ORは足し算
https://youtu.be/DCsSaYGqn6g
あわせて読みたい記事です。
FILTER関数は、条件の書き方さえ分かれば、あとは同じ形の繰り返しです。関数の「なぜそう書くのか」を、1本ずつ動画にしています。
▼チャンネル登録はこちら
パソコン教室スタディPCネット練馬校｜仕事で使えるExcel
