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つの主要部分があります。引数の説明には英語版を使用しています。
- FILTER関数:
FILTER(array, include, [if_empty])
array
:抽出元のデータ範囲。例:A2:C28include
:各行を抽出するかどうかを決定する条件。この場合、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件が抽出されます。
書籍番号 | 書名 | 金額 |
---|---|---|
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件表示されました。
具体例:昇順の式
今度はC列の金額が安い上位3件のレコードを抽出する例を示します。数式は以下の通りです。「昇順降順」には「1」を指定しているので値が小さいレコードが抽出されます。
=FILTER(A2:C28, RANK.EQ(C2:C28, C2:C28, 1) <= 3)
ExcelのFILTER関数とRANK.EQ関数を組み合わせることで、特定の条件に基づいてデータの上位N件や下位N件を抽出することができます。降順では最大値から順に、昇順では最小値から順に抽出することができます。この方法は動的に機能し、データの変化に対応できるため、効率的なデータ分析が可能です。
コメント