FILTER関数でトップテン・トップNフィルターを実現する計算式

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

基本的な考え方

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

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

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

FILTER関数でトップNフィルターを適用する方法

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

数式の構造

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

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

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

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

具体例:降順の式

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

書籍番号書名金額
1001ケーススタディ民法1400
1002交通六法2700
1003法人税法の基礎2700
1004会社法2800
1005民法総則2700
1006判例六法2400
1007基礎法入門2500
1008やさしい経済法2500
1009刑法各論2100
1010現近代民法1400
1011会社法概論2000
1012商法総則1900
1013憲法2500
1014商行為法1600
1015倒産法入門2300
1016破産法1200
1017法社会学1500
1018民事訴訟法学2800
1019法学入門2500
1020労働法の要点2400
1021はやわかり刑法3000
1022講義式憲法3000
1023刑法講義2600
1024会社法1300
1025民法事例集1200
1026六法全書2300
1027労働法実務辞典2700

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

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

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

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

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

具体例:昇順の式

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

=FILTER(A2:C28, RANK.EQ(C2:C28, C2:C28, 1) <= 3)
FILTER関数でトップテン抽出を実行した例:昇順

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

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

コメント