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件を抽出することができます。降順では最大値から順に、昇順では最小値から順に抽出することができます。この方法は動的に機能し、データの変化に対応できるため、効率的なデータ分析が可能です。

コメント

PAGE TOP
タイトルとURLをコピーしました