トップ > 関数 > 動的配列・モダン > FILTER

「田中の売上だけ」の表を、数式で抜き出す

FILTER(フィルター)関数

「田中さんの分だけ抜き出して」と言われるたびに、フィルターをかけてコピーして、別の表を作る。この手作業は、条件が変わるたびにやり直しです。FILTER は、条件に合う行を丸ごと抜き出して、別の場所に自動で並べる関数です。条件のセルを書き換えるだけで、抽出結果が切り替わります。

✅ これができます:担当者別・商品別の小表が、条件を変えるだけで自動で作り直されるようになります

完成形(実行前 → 実行後)

実行前
実行前
実行後
実行後

数式を入れる前(左)は抽出欄が空欄。入れた後(右)は、田中の行だけが4行抜き出されます。F1 の名前を変えれば、その人の表に切り替わります

これをコピペすれば動きます

田中の行だけを抜き出す数式(F2 に入れる。結果は右と下へ自動で広がる)

=FILTER($A$2:$D$11,$B$2:$B$11=$F$1)

金額の条件も足した版(田中の 10,000円以上の行だけ)

=FILTER($A$2:$D$11,($B$2:$B$11=$F$1)*($D$2:$D$11>=10000))

変えるのはここだけ

変える場所を示した図
数式の「変える場所」を番号で示した図。番号は下の表と対応しています
番号変える場所意味書き方・例
1抜き出す表の範囲
$A$2:$D$11
結果として並べたい表全体の範囲一部の列だけでよい場合は、その列だけの範囲にもできる(例:日付と金額だけなら $A$2:$A$11 と $D$2:$D$11 は離せないため、まずは表全体が基本)
2判定する列
$B$2:$B$11
条件と照らし合わせる列。ここでは担当者の列商品で絞るなら商品名の列に変える。行数は①と必ずそろえる
3条件の値が入ったセル
$F$1
絞り込みたい担当者名が入っているセル。セル参照にしておくと、名前を変えるだけで抽出先が切り替わる直接 "田中" と書くこともできるが、実務ではセル参照のほうが断然楽

組み込み手順

  1. サンプル表を用意します。A〜D列に売上リスト(日付/担当者/商品名/金額)を並べます
  2. F1 に絞り込みたい担当者名(田中)を書きます
  3. F2 に1つ目の数式を貼り付けます。該当する行が、右と下へ自動で広がって並びます
  4. 条件を2つにしたいときは、2つ目の数式のように(条件)*(条件)の形で足します(* は「かつ」の意味)
  5. 自分の表で使うときは、「変えるのはここだけ」の①〜③を自分の表に差し替えます

このページのサンプル表は、A列に日付、B列に担当者、C列に商品名、D列に金額が入った売上リスト(10件)です。 F1 に「田中」と書くと、田中の4行(128,000/8,500/32,800/3,500)が抜き出されます。

Excelとスプレッドシートの違い

Microsoft 365 と Excel 2021 以降で使える関数です。Excel 2019 以前では #NAME? エラーになります。結果の広がる先に値があると #SPILL! エラーになります。

古い Excel 向けの代替:オートフィルターで絞ってコピーする方法が従来のやり方です(自動更新はされません)。

Googleスプレッドシートでも FILTER が使えます。書き方はほぼ同じですが、複数条件の書き方は FILTER(範囲, 条件1, 条件2) のように「カンマで条件を並べる」形が基本です(Excel の * 方式も動きます)。

カスタマイズ例

該当する行が無いときに「該当なし」と表示したい

FILTER の3番目の引数に書きます。=FILTER($A$2:$D$11,$B$2:$B$11=$F$1,"該当なし") の形です。これが無いと、0件のときに #CALC! エラーになります。

抜き出した表を、金額順に並べたい

SORT で囲みます。=SORT(FILTER($A$2:$D$11,$B$2:$B$11=$F$1),4,-1) とすると、金額の大きい順に並んだ抽出表になります(次の SORT のページ参照)。

「または」の条件にしたい

条件同士を + でつなぐと「どちらか」の意味になります。例:($B$2:$B$11="田中")+($B$2:$B$11="佐藤")。ただし両方に当てはまる行は重複して出る場合があるため、使い方に注意してください。

つまずきどころ

関連する関数・レシピ

次はこれ:SORT/SORTBY(並び順を数式で変える)
抽出できたら次は並べ替えです。元の表はそのままで、並べた結果だけを別に作ります

よくある質問

Q. 手作業のフィルターと何が違う?

A. 手作業のフィルターは「その場で絞って見る」操作です。FILTER 関数は、抽出結果を別の表として自動で保ち続けます。元データが変われば、抽出先も自動で変わります。

Q. 抽出した結果を、値として固定したい

A. 抽出範囲をコピーして「値として貼り付け」をすると、その時点の結果で固定できます。

Q. Mac でも使える?

A. Mac版 Excel(Microsoft 365/2021以降)と、ブラウザ版の Googleスプレッドシートで動作します。

PR(広告)

もっと体系的に学びたい人へ
独学で体系化するなら、講座で一気に学ぶのが近道です。

Udemy:Excelショートカット講座を見る Udemy:Googleスプレッドシート入門講座を見る