ExcelのFILTER関数でトップNフィルターを実現する方法

基本的な考え方

Excelのデータ分析では、特定の条件に基づいてデータを抽出することがよくあります。

データの上位N件や下位N件といったトップテン系の抽出を実行したいする場合、FILTER関数とRANK.EQ関数を組み合わせることで効率的に実現できます。本記事では、降順および昇順でトップNフィルターを適用する方法を詳しく解説します。

FILTER関数は、特定の条件を満たす行を抽出するために使用されます。一方、RANK.EQ関数は、特定の値の順位を計算するために使用されます。これらの関数を組み合わせることで、データの上位N件や下位N件を抽出することが可能になります。

FILTER関数でトップNフィルターを適用するには、RANK.EQ関数を使用して、キー列の値の順位を計算します。次に、その順位が指定された抽出数以下である行をFILTER関数で抽出します。

=FILTER(配列, RANK.EQ(キー列, キー列, 昇順降順) <= 抽出数)

この数式には、以下の3つの主要部分があります。引数の説明には英語版を使用しています。

FILTER関数：
- FILTER(array, include, [if_empty])
- array：抽出元のデータ範囲。例：A2:C28
- include：各行を抽出するかどうかを決定する条件。この場合、RANK.EQ関数の結果を使用。
- [if_empty]：フィルタ結果が空の場合に返す値（省略可）。ここでは割愛。
RANK.EQ関数：
- RANK.EQ(number, ref, [order])
- number：順位を計算する対象の値。この場合、キー列の値（例：C2:C28）。単一の値ではなく配列を指定して配列数式としている
- ref：参照範囲。この範囲内でnumberの順位が計算されます（例：C2:C28）。numberと同じ範囲を指定する。
- [order]：0（降順）または1（昇順）を指定。省略すると0（降順）の扱い。
抽出数：
- 抽出する上位の項目数。例：3

それでは具体例を見てみましょう。

以下のデータ表(A1:C28)を用いて、C列の金額が高い上位5件のレコードを抽出する例を示します。ただし5位が4つあるので、実際には8件が抽出されます。

上記のデータから、C列の金額が高い上位5件のレコードを抽出するための数式は以下の通りです。「昇順降順」には「0」を指定しているので値が高いレコードが抽出されます。

=FILTER(A2:C28, RANK.EQ(C2:C28, C2:C28, 0) <= 5)

この数式の実行結果は以下の通りです。5位である「2700」が4つあるので8件表示されました。

FILTER関数でトップテン抽出を実行した例：降順

今度はC列の金額が安い上位3件のレコードを抽出する例を示します。数式は以下の通りです。「昇順降順」には「1」を指定しているので値が小さいレコードが抽出されます。

=FILTER(A2:C28, RANK.EQ(C2:C28, C2:C28, 1) <= 3)

FILTER関数でトップテン抽出を実行した例：昇順

ExcelのFILTER関数とRANK.EQ関数を組み合わせることで、特定の条件に基づいてデータの上位N件や下位N件を抽出することができます。降順では最大値から順に、昇順では最小値から順に抽出することができます。この方法は動的に機能し、データの変化に対応できるため、効率的なデータ分析が可能です。