Excel 順位に該当する数値を取得(LARGE関数、SMALL関数)

2020年5月12日

LARGE関数とSMALL関数は順位を指定し

範囲内に該当する数値を取得する関数でRANK関数の逆の働きをします。

関数説明
LARGE順位を降順で取得
SMALL順位を昇順で取得

この記事では、その仕様と使い方を紹介します。

仕様

=LARGE関数(配列,順位)
=SMALL関数(配列,順位)

<配列>
順位を求めるための値の一覧のセル範囲を指定します。

<順位>
取得する順位を指定します。
引数省略時の値説明
配列省略不可順位を求めるための値の一覧のセル範囲を指定
順位省略不可値を取得する順位を指定。
各引数の詳細

利用例(関数/引数別 結果の違い)

B列に数値の一覧、CとD列にRANK関数の結果、

F列に順位、GとH列にLARGEとSMALL関数の結果を表示しています。

下記がそれぞれ対になっていることがわかります。

RANK関数(降順)⇔LARGE関数

RANK関数(昇順)⇔SMALL関数

利用例(関数/引数別 結果の違い)
LARGE関数とSMALL関数の使い方と結果例を紹介

スピルを利用する方法とメリット

Office365やExcel2019以降ではスピルという形式で記述可能です。

これは複数セルに数式をコピーする場合に便利な機能です。

スピルの利用には通常、一つの値しか指定しない引数2も範囲で指定します。

そしてスピルであれば配列の絶対参照($)が不要です。

例ではLARGE関数ですが、SMALL関数でも同じです。

スピルでLARGE関数を記述する例(C3セル)
スピルでLARGE関数を記述する例(C3セル)

数式を入力するのは最初の一つのセルですが、

引数で指定した高さ分、数式がセルのコピーなしで自動拡大されます。

C3に指定したLARGE関数がC12まで自動拡大
C3に指定したLARGE関数がC12まで自動拡大

スピルを利用することで今回の例では、

C3セルに入力するだけでよいため(C4以降にコピーペーストが不要)

以下のメリットがあります。

  • 入力の手間が少ない(数式のセルが多いほど効果が大きい)
  • 数式を編集した時、コピー忘れのリスクがない(最初のセルを更新すると全セルに反映)
  • 絶対参照が不要
  • 途中に行を追加した場合、数式のコピー&ペーストが不要。行削除でも壊れにくい。

関連記事

関数の仕様と使い方の記事一覧