【Excel】SORTBY関数とFILTER関数を組み合わせる!Excel抽出結果を自動で並べ替える技

Excelで特定の条件に合うデータを抽出した際、その結果を自動で並べ替えたい場面があります。しかし、FILTER関数で抽出しただけでは、元のデータの並び順のまま表示されます。

この課題を解決するため、SORTBY関数とFILTER関数を組み合わせる方法があります。この組み合わせにより、抽出条件を満たすデータが、指定した列を基準に自動で並べ替えられて表示されます。

この記事では、SORTBY関数とFILTER関数を連携させる具体的な手順と、その応用方法を解説します。読めば、複雑なデータ集計作業を効率化するための強力なテクニックを習得できます。

FILTER関数とSORTBY関数の基本機能

FILTER関数とSORTBY関数は、どちらも動的配列関数です。これらの関数を単独で使うだけでも、データの抽出や並べ替えが可能になります。

FILTER関数は、指定した範囲から条件に合う行や列を抽出します。例えば、売上データから特定月の売上だけを取り出す際に役立ちます。一方、SORTBY関数は、ある配列(データ範囲)を、別の配列の値に基づいて並べ替えます。例えば、氏名のリストを年齢順に並べ替えるといった用途に適しています。

FILTER関数とSORTBY関数を組み合わせるメリット

これらの関数を組み合わせることで、データ抽出と並べ替えを一度の操作で完結できます。通常、FILTER関数で抽出した後に、手動で並べ替えを行うか、別の関数で再度処理する必要があります。

しかし、組み合わせれば、抽出条件を満たすデータが、指定した列を基準に自動で並べ替えられて表示されるようになります。これにより、集計作業の効率が大幅に向上し、手作業によるミスも削減できます。

FILTER関数とSORTBY関数を組み合わせる手順

ここでは、具体的なデータを使って、FILTER関数とSORTBY関数を組み合わせる手順を解説します。例として、商品リストから特定カテゴリの商品を抽出し、価格の安い順に並べ替えるケースを考えます。

準備するデータ

まず、以下の様な商品データを用意します。A列に商品ID、B列に商品名、C列にカテゴリ、D列に価格が入っているとします。

データ範囲: A1:D10

• A列: 商品ID
• B列: 商品名
• C列: カテゴリ
• D列: 価格

抽出と並べ替えの数式入力

抽出結果を表示したいセル(例えばF1セル)に、以下の数式を入力します。

数式例:

=SORTBY(FILTER(A1:D10,C1:C10="雑貨"),D1:D10,1)

1. 数式の解説
   この数式は、まずFILTER(A1:D10,C1:C10="雑貨")の部分で、C列(カテゴリ)が「雑貨」である行をA1:D10の範囲から抽出します。
2. 次に、抽出結果を並べ替える
   次に、抽出された結果全体を、SORTBY関数で並べ替えます。D1:D10(価格列)を基準に、1(昇順)で並べ替えることを指定しています。
3. 結果の表示
   これにより、カテゴリが「雑貨」の商品が、価格の安い順に自動で並べ替えられて表示されます。

数式の要素分解

数式をより詳細に理解するために、各要素を分解して説明します。

FILTER関数の引数

FILTER(配列, 含む, [空の場合])

• 配列: 抽出したいデータ範囲を指定します。例ではA1:D10です。
• 含む: 条件を指定する範囲と条件式です。例ではC1:C10="雑貨"で、C列が「雑貨」である行を指定しています。
• 空の場合: 条件に一致するデータがない場合に表示する値を指定します。省略可能です。

SORTBY関数の引数

SORTBY(配列, 並べ替える配列1, [並べ替え順序1], [並べ替える配列2], [並べ替え順序2], ...)

• 配列: 並べ替えたい対象の配列です。ここではFILTER関数で抽出された結果が自動的に渡されます。
• 並べ替える配列1: 基準となる配列を指定します。例ではD1:D10(価格列)です。
• 並べ替え順序1: 並べ替えの順序を指定します。1は昇順(小さい順)、-1は降順(大きい順)です。例では1なので昇順です。

応用例: 複数列での並べ替え

さらに、複数の列を基準にして並べ替えることも可能です。例えば、カテゴリが「雑貨」の商品を、まず価格の安い順に並べ、次に同価格の場合は商品名(B列)の五十音順に並べたい場合を考えます。

数式例:

=SORTBY(FILTER(A1:D10,C1:C10="雑貨"),D1:D10,1,B1:B10,1)

この数式では、D1:D10(価格)で昇順に並べた後、価格が同じ場合はB1:B10(商品名)で昇順(五十音順)に並べ替えます。

よくある質問とトラブルシューティング

この組み合わせでは、いくつかの注意点や、予期せぬ結果になる場合があります。ここでは、よくある質問とその解決策を解説します。

#CALC!エラーが表示される

このエラーは、数式が計算できない場合に表示されます。主な原因は、FILTER関数で条件に一致するデータが一つも見つからなかった場合です。

解決策:

1. FILTER関数の「空の場合」引数を活用する
   FILTER関数の3番目の引数に、データが見つからなかった場合に表示したい値を指定します。例えば、"該当なし"と表示したい場合は、数式を以下のように変更します。
   =SORTBY(FILTER(A1:D10,C1:C10="雑貨","該当なし"),D1:D10,1)
   ただし、この場合、SORTBY関数が「該当なし」という文字列を並べ替えようとして別のエラー(例: #VALUE!)を返す可能性があります。
2. IFERROR関数でエラーを捕捉する
   数式全体をIFERROR関数で囲むことで、エラーが発生した場合に任意の値を表示できます。
   =IFERROR(SORTBY(FILTER(A1:D10,C1:C10="雑貨"),D1:D10,1),"該当なし")

並べ替え順序が意図通りにならない

並べ替え順序が意図通りにならない場合、SORTBY関数の順序引数(1または-1)を確認してください。

確認点:

• 昇順 (1): 数値は小さい方から大きい方へ、文字列は五十音順やアルファベット順に並びます。
• 降順 (-1): 数値は大きい方から小さい方へ、文字列は逆順に並びます。

特に、数値と文字列が混在する列を基準に並べ替える場合、Excelの解釈によっては予期せぬ順序になることがあります。その場合は、並べ替えたい列を一時的に数値のみ、あるいは文字列のみに変換してから試してください。

抽出結果が更新されない

数式を入力しても、元のデータが変更された際に抽出結果が自動更新されない場合、Excelの計算方法が手動になっている可能性があります。

確認・設定方法:

1. Excelのオプション設定を確認する
   「ファイル」タブ → 「オプション」 → 「数式」を選択します。
   「計算方法の設定」セクションで、「ブックの計算」が「自動」になっているか確認してください。もし「手動」になっていたら、「自動」に変更して「OK」をクリックします。
2. 手動計算の場合の更新方法
   「数式」タブの「計算オプション」で「今すぐ計算」をクリックするか、F9キーを押すことで手動計算を実行できます。

FILTER関数とSORTBY関数が使えない場合の代替策

FILTER関数やSORTBY関数は、Microsoft 365のExcelやExcel 2021以降で利用可能な動的配列関数です。これらの関数が利用できない古いバージョンのExcelを使用している場合、代替策が必要です。

代替策1: VLOOKUP関数と並べ替え機能の組み合わせ

まず、FILTER関数と同様の抽出をVLOOKUP関数などで行い、その結果をコピー&ペーストして値化します。その後、Excelの並べ替え機能を使って手動で並べ替えます。この方法は、最新の関数が使えない環境では一般的ですが、データ更新のたびに手動での再操作が必要です。

代替策2: Power Queryの使用

Power Query(Excel 2016以降では「データの取得と変換」機能として統合)を使用すると、より高度な抽出と並べ替えを自動化できます。Power Queryエディター上で、フィルター処理と並べ替え処理を設定し、その結果をExcelシートに読み込むことで、データ更新時にワンクリックで処理を再実行できます。

Power Queryは、大量のデータを扱う場合や、複雑なデータ変換が必要な場合に特に強力なツールです。

まとめ

SORTBY関数とFILTER関数を組み合わせることで、Excelでのデータ抽出と並べ替え作業を大幅に効率化できます。条件に合うデータを抽出するだけでなく、指定した基準で自動的に並べ替えて表示できるため、集計や分析のスピードが向上します。

今回解説した数式を応用することで、より複雑な条件での抽出や、複数列での高度な並べ替えも実現可能です。ぜひ、ご自身の業務に合わせて活用してみてください。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】