LARGE/SMALL関数でX番目に大きい数を求めよう

case

39

LARGE/SMALL関数でX番目に大きい数を求めよう

統計2023年11月7日

対応バージョン

365
web
2021
2019
2016
2013
Web App
2011
2010
2007
Starter

X番目に大きい数はどれ？今回は、LARGE/SMALL関数を使って簡単に表内の順位の数を表示する方法を解説いたします。

LARGE関数とSMALL関数の書式
ステップアップ：条件付き書式と組み合わせて上位、下位のセルに書式を付ける

X番目に大きい数、X番目に小さい数を求める

LARGE（ラージ）関数とSMALL（スモール）関数を利用すると、セル範囲の中で「X番目の数」を求めることができます。
LARGE関数は、数の多いほうから数えてX番目、SMALLは少ないほうから数えてX番目の値を表示できます。RANK関数との違いは「セル範囲の中の順位」を求めますが、LARGE関数やSMALL関数はX番目（順位）に相当する「値」を求めることができます。

ここでは、LARGE関数とSMALL関数の基本的な書式と機能について紹介します。

LARGE関数とSMALL関数の書式

LARGE関数とSMALL関数は次のように入力します。

＝LARGE（配列,順位）

＝SMALL（配列,順位）

LARGE関数、SMALL関数はセル範囲の中から、指定したX番目の値を求める関数です。
LARGE関数、SMALL関数は、［関数の挿入］ボタンをクリックして［関数の分類］で「統計」を選択すると、関数が簡単に入力できます。

今回の例では、E列に投票数の順位が入力されています。その順位のセルを利用して、投票数が多い順で投票数を求めます。
注：セルE4の値を変更すると、セルE5以降は+1ずつ加算される式が入力されています。

セルF4をアクティブにし、［数式］タブの［関数の挿入］をクリックしている画面イメージ — 1LARGE関数を入力する、セルF4をアクティブにし、［数式］タブの［関数の挿入］をクリックします。

［関数の分類］で「統計」を選択し、［関数名］で「LARGE」を選択して、［OK］ボタンをクリックしている画面イメージ — 2［関数の分類］で「統計」を選択し、［関数名］で「LARGE」を選択して、［OK］ボタンをクリックします。

［配列］に「$C$4:$C$23」、［順位］に「E4」を入力し、関数の引数ダイアログボックスの［OK］ボタンをクリックしている画面イメージ — 3［配列］に「$C$4:$C$23」、［順位］に「E4」を入力し、関数の引数ダイアログボックスの［OK］ボタンをクリックします。

セルF4の式をセルF8までコピーしている画面イメージ — 4セルF4の式をセルF8までコピーします。

E4の値を「10」に変更してみると、自動的にE列の連番が変更され、F列のLARGE関数の値も上位10番目から14番目の値に変更されている画面イメージ — 5セルE4の値を「10」に変更してみると、自動的にE列の連番が変更され、F列のLARGE関数の値も上位10番目から14番目の値に変更されます。

セルF13に次の式を入力し、値の小さいものから3番目までの値を求めている画面イメージ — 6同様に、セルF13に次の式を入力し、値の小さいものから3番目までの値を求めます。
=SMALL($C$4:$C$23,E13)

ステップアップ：条件付き書式と組み合わせて上位、下位のセルに書式を付ける

LARGE関数やSMALL関数は条件付き書式にも利用できます。例えば、一覧の並び順は変更したくないけれども、値として「上位3番目までの値のセルを、背景に網掛けをしたい」とか、「下位5番目までの文字を赤で表示したい」など、さまざまな場面で活用できます。

次の例では、クラスの成績表の一覧から上位5人の背景に色付きの網掛けにしたいので、条件付き書式の書式ルールに「LARGE」関数を利用して、書式を設定してみましょう。

C4からC18を範囲選択している画面イメージ — 1書式を設定するセルC4からC18を範囲選択します。

［ホーム］タブの［条件付き書式］をクリックし、［新しいルール］をクリックしている画面イメージ — 2［ホーム］タブの［条件付き書式］をクリックし、［新しいルール］をクリックします。

［数式を使用して、書式設定するセルを決定］クリックし、式を入力し、［書式］をクリックしている画面イメージ — 3［数式を使用して、書式設定するセルを決定］クリックし、次のように式を入力し、［書式］をクリックします。
=C4>=LARGE($C$4:$C$18,5)

［塗りつぶし］タブをクリックして、色を選択後、［OK］ボタンをクリックしている画面イメージ — 4［塗りつぶし］タブをクリックして、色を選択後、［OK］ボタンをクリックします。

新しい書式ルールのメニューの［OK］ボタンをクリックしている画面イメージ — 5新しい書式ルールのメニューの［OK］ボタンをクリックします。

上位5名の点数の背景に色が設定されている画面イメージ — 6上位5名の点数の背景に色が設定されました。

LARGE関数とSMALL関数は、数字の一覧の中から上位や下位X番目の数を求めることができる関数です。データ数の多い一覧から最大値や最小値だけでなく、X番目の数が求められます。また、条件付き書式とあわせるとX番目の数のセルに書式が付くので、一覧から必要なデータを探しやすくなります。