Excel関数の組み合わせについてお探しですね。
広告
ExcelのFILTER関数とSORT関数を組み合わせて、抽出と並べ替えを一発でやる方法
Excelで売上表や案件リストを管理していると、「条件に合うデータだけ抜き出して、さらに金額順や日付順に並べたい」という場面、ありますよね。
手作業でフィルターかけて並べ替えれば確かにできるんですが、毎回同じ操作を繰り返すのは面倒だし、うっかり元データを並べ替えてしまうと後で困ります。
そんなときに便利なのが、**FILTER関数とSORT関数を組み合わせる**方法です。
この2つを使えば、条件に合うデータを抽出して、自動で並べ替えた状態で表示できます。
元データはそのままで、見やすいレポートだけを別の場所に作れるので、とても実用的です。
この記事では、基本的な使い方から実務で役立つ応用例、エラーが出たときの対処法まで、初心者の方にも分かりやすく解説していきます。
FILTER関数とSORT関数を組み合わせると何がいいの?
**FILTER関数**は、指定した条件に合うデータだけを別の場所に抽出できる関数です。
たとえば「営業部のデータだけ表示する」といった使い方ができます。
一方、**SORT関数**は、指定した範囲を昇順(小さい順)または降順(大きい順)に並べ替えて表示する関数です。
「売上金額の高い順に並べる」といったことができます。
この2つを組み合わせると、**「営業部のデータだけを抽出して、売上金額の高い順に並べる」**といった動きを、たった1つの数式で実現できるんです。
しかも、元データに新しい行を追加したり、条件を変えたりするだけで、表示内容が自動で更新されます。
従来のやり方だと、まずフィルターで条件を指定して、次に並べ替えを実行して…と手順が多く、データが更新されるたびに同じ操作を繰り返す必要がありました。
FILTER関数とSORT関数を使えば、**元データを触らずに、見たい形のレポートを自動生成**できるので、日次レポートや売上ランキング、在庫確認表など、定期的に確認する資料ほど効果を実感しやすいです。
ちなみに、FILTER関数とSORT関数は**Microsoft 365やExcel 2021以降**で使える「動的配列関数」です。
動的配列というのは、1つのセルに入力した数式の結果が、複数の行や列に自動的に広がって表示される仕組みのこと。
この広がる動きを「スピル」と呼びます。
スピルのおかげで、コピーやオートフィルをしなくても一覧表が作れるのですが、出力先に既存のデータがあるとエラーになってしまうので、**レポートを表示する場所はあらかじめ空けておく**ことが大切です。
基本的な書き方と組み合わせ式の作り方
まず、FILTER関数とSORT関数の役割を分けて考えると分かりやすいです。
– **FILTER関数**:「どの行を表示するか」を決める
– **SORT関数**:「どの順番で表示するか」を決める
基本的な形はこんな感じです。
“`
=SORT(FILTER(抽出範囲, 条件), 並べ替え列番号, 並べ替え順序)
“`
– **並べ替え列番号**:抽出範囲の中で何列目を基準にするかを指定します。
– **並べ替え順序**:昇順なら「1」、降順なら「-1」を指定します。
具体例で見てみましょう
たとえば、A列からF列に「日付、部署、担当者、顧客名、ステータス、売上金額」が入っている売上表があるとします。
営業部のデータだけを抽出して、売上金額の高い順に並べたい場合、こんな数式になります。
“`
=SORT(FILTER(A2:F100, B2:B100=”営業部”), 6, -1)
“`
この式では、FILTER関数がB列(部署名)を見て「営業部」だけを抽出し、その結果をSORT関数が6列目(売上金額)を基準に降順(大きい順)で並べ替えています。
数式を入力するのは、レポートを表示したい左上のセルだけ。
結果は下と右に自動的に広がって表示されます。
条件をセル参照にするともっと便利
条件を直接書くのではなく、**セルを参照する**ようにすると、さらに使いやすくなります。
たとえば、I2セルに部署名を入力して、その部署の売上データを表示したい場合はこうします。
“`
=SORT(FILTER(A2:F100, B2:B100=I2), 6, -1)
“`
こうしておけば、I2セルを「営業部」から「マーケティング部」に変更するだけで、表示されるデータも自動で切り替わります。
関数を編集しなくても条件を変えられるので、Excel初心者の人にも使いやすいシートになります。
複数の条件で絞り込むには?
複数の条件で抽出したい場合は、**条件式を掛け算でつなぐ**とAND条件(すべての条件を満たす)になります。
たとえば、「部署が営業部」で、かつ「ステータスが受注済み」のデータだけを売上順に表示するなら、こうなります。
“`
=SORT(FILTER(A2:F100, (B2:B100=I2)*(E2:E100=”受注済み”)), 6, -1)
“`
日付範囲を条件に加えることもできます。
開始日をK2、終了日をL2に入力しておけば、期間指定付きのレポートも作れます。
条件セルをいくつか用意しておくと、同じ元データからいろんな切り口の一覧を素早く確認できて便利です。
実務で使える動的レポートの作り方
売上ランキングを自動で作る
売上管理でよく使うのが、条件に合う売上データを抽出して、金額の大きい順に並べるランキング形式のレポートです。
部署別、担当者別、商品カテゴリ別などの条件セルを上部に用意しておけば、会議前に手作業で表を並べ替える必要がありません。
たとえば、部署を選ぶセル、期間を指定するセル、ステータスを選ぶセルを配置して、その下にFILTER関数とSORT関数の結果を表示すれば、簡易的なダッシュボードとして使えます。
元データを更新するだけで最新のランキングが表示されるので、報告資料を作る時間をぐっと短縮できます。
在庫不足商品を優先順に表示する
在庫管理でも、FILTER関数とSORT関数の組み合わせは役立ちます。
たとえば、在庫数が発注点を下回っている商品だけを抽出して、在庫数の少ない順に並べれば、優先的にチェックすべき商品がすぐに分かります。
商品一覧がA2:E100にあって、D列が在庫数、E列が発注点の場合、こんな数式になります。
“`
=SORT(FILTER(A2:E100, D2:D100<=E2:E100), 4, 1)
```
このレポートは、在庫切れリスクの高い商品を上位に表示するので、確認漏れを防ぐのに向いています。
期限が近いタスクを自動で並べる
タスク管理や案件管理では、未完了の項目だけを抽出して、期限の近い順に並べる使い方が便利です。
A列からE列に「期限、担当者、案件名、ステータス、優先度」がある場合、こうすれば完了以外の案件を期限が近い順に表示できます。
“`
=SORT(FILTER(A2:E100, D2:D100<>“完了”), 1, 1)
“`
期限切れのタスクを目立たせたい場合は、条件付き書式を組み合わせると見やすくなります。
関数で一覧を自動生成して、書式で注意点を強調する構成にすると、単なる抽出表ではなく、判断に使えるレポートになります。
上位N件だけを表示する
上位10件だけを表示したい場合は、Microsoft 365で使える**TAKE関数**を組み合わせる方法もあります。
たとえば、営業部の売上上位10件だけを表示するなら、こう書けます。
“`
=TAKE(SORT(FILTER(A2:F100, B2:B100=I2), 6, -1), 10)
“`
Excel 2021などでTAKE関数が使えない環境では、INDEX関数やSEQUENCE関数を使う方法もありますが、まずはFILTER関数とSORT関数の基本形をしっかり使えるようにすることが大切です。
レポートの目的が「全件確認」なのか「上位だけ確認」なのかを決めてから数式を作ると、見やすい表になります。
エラーが出たときの対処法と、壊れにくいレポート運用のコツ
「該当データなし」のエラーを分かりやすく表示する
FILTER関数とSORT関数を使うときによくあるのが、**抽出結果がない場合のエラー**です。
FILTER関数は、条件に一致するデータがないと「#CALC!」などのエラーを返すことがあります。
レポートとして使う場合、エラー表示のままだと見る人が「壊れてる?」と誤解しやすいので、**IFERROR関数でメッセージを表示する**と親切です。
“`
=IFERROR(SORT(FILTER(A2:F100, B2:B100=I2), 6, -1), “該当データはありません”)
“`
こうしておけば、条件に合う行がない場合でも分かりやすく表示できます。
スピル範囲をちゃんと確保する
もう1つ注意したいのが、**スピル範囲の確保**です。
動的配列関数は、結果を表示するために複数のセルを使います。
その範囲に文字や数式、結合セルがあると「#SPILL!」エラーになってしまいます。
レポート用の出力エリアには、あらかじめ余裕を持った空白範囲を用意して、手入力のメモや別の計算式を置かないようにしましょう。
特に、抽出件数が日によって変わる売上データや問い合わせ一覧では、想定より多くの行が表示されることもあるので、**下方向に十分なスペースを確保する**ことが大切です。
データ範囲をテーブルにすると管理が楽
データ範囲の指定方法にも工夫が必要です。
`A2:F100` のように固定範囲で指定すると、101行目以降に追加したデータがレポートに反映されません。
実務では、**元データをExcelの「テーブル」に変換して、構造化参照を使う**方法がおすすめです。
テーブル名を「売上表」にしておけば、こんな風に書けます。
“`
=SORT(FILTER(売上表, 売上表[部署]=I2), 6, -1)
“`
テーブルは行を追加すると範囲が自動で広がるので、関数側の参照範囲を手で直す手間が減ります。
列番号のずれに注意
SORT関数の列番号は、元のシート上の列番号ではなく、**指定した抽出範囲の中での相対的な列番号**である点にも注意してください。
たとえば、`C2:F100` を抽出範囲にした場合、その中の1列目はC列、4列目はF列になります。
元データの列を追加・削除したときに並べ替え基準がずれることもあるので、レポートを長く使うなら、**列構成をむやみに変更しない運用ルールを決めておく**と安心です。
複数の基準で並べ替えたい場合は、SORT関数ではなく**SORTBY関数**を検討すると、基準列を直接指定できて管理しやすくなります。
まとめ
Excel FILTER関数とSORT関数を組み合わせると、条件抽出と並べ替えを同時に自動化できて、元データを守りながら見やすい動的レポートを作れます。
ポイントは、**FILTER関数で必要な行を絞り込んで、SORT関数でレポートとして見たい順番に整える**こと。
さらに、条件セル、Excelテーブル、IFERROR関数、十分なスピル範囲を組み合わせれば、日々の更新にも強い実務向けの仕組みになります。
手作業のフィルターや並べ替えを減らしたい方は、まず1つの売上表や案件一覧から試してみてください。
きっと効果を実感できるはずです。
広告
