- Home »
- エクセル関数の技 »
- 関数を使って上位5位・下位5位のデータを取り出す
更新:2025/11/16;作成:2025/11/15
関数を使って上位5位・下位5位のデータを取り出す topへ
- 表題は「関数を使って上位5位・下位5位のデータを取り出す」としていますが、指定した順位のデータを関数で取り出してみたいと思います。
- SORT関数やTAKE関数といった便利な関数が使えるようになったので、ここでは、RANK系の関数は使用せず、データを昇順や降順に並べ替えて取り出すという方法です。
- SORT関数,FILTER関数は、Excel for Microsoft365、Excel2021以降のバージョンで使用できます。
- TAKE関数はExcel for Microsoft365、Excel2024で使用することができます。
トリム参照やTRIMRANGE関数はExcel for Microsoft365で利用することができます。
ここで使用したデータ topへ
- ここで使用したデータです。コピーしてお試しください。
なお、E列には順位を =RANK.EQ(D3:D22,D3:D22) で計算しています。
データを抽出したときに何番目のデータであるかがわかるよう結果を表示していますが、データの抽出には利用していません。
|
B |
C |
D |
E |
| 2 |
No |
名前 |
得点 |
順位 |
| 3 |
1 |
矢鍋 日出夫 |
56 |
14 |
| 4 |
2 |
中元 英寿 |
80 |
6 |
| 5 |
3 |
松山 利香 |
99 |
1 |
| 6 |
4 |
大江 政勝 |
43 |
18 |
| 7 |
5 |
羽田 弘吉 |
59 |
13 |
| 8 |
6 |
飯尾 雅文 |
48 |
16 |
| 9 |
7 |
井上 達也 |
68 |
11 |
| 10 |
8 |
勝谷 隆一 |
98 |
2 |
| 11 |
9 |
鶴岡 和男 |
63 |
12 |
| 12 |
10 |
西崎 敬次 |
75 |
8 |
| 13 |
11 |
堀川 勝之 |
93 |
3 |
| 14 |
12 |
盛田 江利子 |
50 |
15 |
| 15 |
13 |
田路 覚 |
75 |
8 |
| 16 |
14 |
宮田 吉隆 |
39 |
19 |
| 17 |
15 |
西村 正深 |
79 |
7 |
| 18 |
16 |
福嶋 東 |
74 |
10 |
| 19 |
17 |
西野 一夫 |
84 |
5 |
| 20 |
18 |
秋山 善和 |
44 |
17 |
| 21 |
19 |
土江 克志 |
35 |
20 |
| 22 |
20 |
礒山 博治 |
92 |
4 |
- 下の方に、TAKE関数を使った方法を書いていますが、FILTER関数を使う方法が簡単な気がしましたので書いておきます。
SORT関数,FILTER関数は、Excel for Microsoft365、Excel2021以降のバージョンで使用できます。
上位5位までのデータを取り出す
- G2セルは ”上位" と "下位" が入力規則で選択できるようにしています
- H2セルは 1,2,3,4,5,6・・・,9,10 が入力規則で選択できるようになっています。
表示形式で 0" 位" としています。
- 上位5位の得点は >=LARGE(D3:D22,H2) で求めることができます。
FILTER関数の条件を D3:D22>=LARGE(D3:D22,H2) として5位以上のデータを取り出すことができます。
FILTER(B3:E22,D3:D22>=LARGE(D3:D22,H2),"-")
取り出したデータをSORT関数を使って、得点の列(3列目)を並べ替えのインデックスとして、降順に並べ替えます。
=SORT(FILTER(B3:E22,D3:D22>=LARGE(D3:D22,H2),"-"),3,-1)
下位5位までのデータを取り出す
- FILTER関数の条件を D3:D22<=SMALL(D3:D22,H2) として下位5位以下のデータを取り出すことができます。
数式は =SORT(FILTER(B3:E22,D3:D22<=SMALL(D3:D22,H2),"-"),3,-1) としました。
上位と下位の数式をまとめる
- G2セルの値が「上位」と「下位」によって、FILTER関数の条件を切り替えるようにしました。
IF(G2="上位",D3:D22>=LARGE(D3:D22,H2),D3:D22<=SMALL(D3:D22,H2))
- G5セルの数式は
=SORT(FILTER(B3:E22,
IF(G2="上位",D3:D22>=LARGE(D3:D22,H2),D3:D22<=SMALL(D3:D22,H2)),
"-"),3,-1)
としています。
- なお、D20セルの値を 84 に変更して、同順位が複数ある場合の例を示しています。
- TAKE関数はExcel for Microsoft365、Excel2024で使用することができます。
- SORT関数は、Excel for Microsoft365、Excel2021以降のバージョンで使用できます。
- 上位5位のデータを取り出したいと思います。
まず、データをSORT関数で得点の降順に（大きい順）に並べ替えるには、SORT(B3:E22,3,-1) とします。
並べ替えたデータの上から5個を取り出すと、TAKE(降順のデータ,5)、上位5個のデータとなります。
上位/下位をG2セルで選択して、取り出すデータ数をH2セルで指定することにすると、
SORT関数の引数の並べ替えの順序を IF(G2="上位",-1,1) とG2の値で-1と1に切り替えます。
G5セルは =TAKE(SORT(B3:E22,3,IF(G2="上位",-1,1)),H2) とすることができます。
- 上位5位までのデータを抽出した例
- 上位5位までのデータを抽出した例
- 下位のデータを取り出したとき。上の結果は一番下の順位からデータが並んでいます。J列の順位が20〜16となっています。
下位5位のデータから並べたいときは、降順に並べたデータの下から5個のデータを取り出してみました。
TAKE関数で引数の行数を負の値にすると、下から取り出すことができます。
=TAKE(SORT(B3:E22,3,-1),IF(G2="上位",H2,-1*H2))
とすることができます。
J列の順位が16〜20となっています。
同順位が複数あるとき
- D20セルの値を84に変更して、5位のデータが2個あった場合を作成しました。
上記の数式では6個目のデータが取り出せません。
COUNTIF(D3:D22,">="&LARGE(D3:D22,H2))) として、LARGE関数で5位のデータより大きい値が何個あるかを計算します。
G5セルの数式は
=TAKE(
SORT(B3:E22,3,IF(G2="上位",-1,1)),
COUNTIF(D3:D22,">="&LARGE(D3:D22,H2)))
と、しました。
- D18セルの値を39に変更して複数データを作成しました。
下位に複数のデータがある場合は、SMALL関数でデータ数を数えてみました。
=TAKE(
SORT(B3:E22,3,IF(G2="上位",-1,1)),
IF(G2="上位",COUNTIF(D3:D22,">="&LARGE(D3:D22,H2)),
COUNTIF(D3:D22,"<="&SMALL(D3:D22,H2))))
トリム参照を利用してみた topへ
- Excel for Microsoft365、Excel2024では便利な関数や機能が追加されています。
これからは簡単な数式で計算ができるようになります。
- TAKE関数はExcel for Microsoft365、Excel2024で使用することができます。
トリム参照やTRIMRANGE関数はExcel for Microsoft365で利用することができます。
- トリム参照を利用して、データの増減に対応できるようにしてみました。
トリム参照でタイトル行を無視するためにデータ範囲を DROP(データ,1) としています。
G5セルの数式は
=TAKE(
DROP(SORT(B.:.E,3,IF(G2="上位",-1,1)),1),
IF(G2="上位",COUNTIF(DROP(D.:.D,1),">="&LARGE(DROP(D.:.D,1),H2)),
COUNTIF(DROP(D.:.D,1),"<="&SMALL(DROP(D.:.D,1),H2))))
としています。
- データを増やしても同じ数式で対処できました。
スポンサードリンク
Home »
エクセル関数の技 » 関数を使って上位5位・下位5位のデータを取り出す
PageViewCounter
Since2006/2/27