LARGEとSMALLで上位と下位を求める

MAX は最も大きい値を1つ返し、MIN は最も小さい値を1つ返します。しかし、2番目に大きい値や、3番目に小さい値を求めたい場合はどうでしょうか。

そのための関数がLARGEとSMALLです。範囲から n 番目に大きい値や n 番目に小さい値を取り出せるため、上位 N 件・下位 N 件のレポートの基礎になります。

LARGE(range, k) は k 番目に大きい値を返します。2番目の引数kで位置を指定します。1 は最大値、2 は2番目に大きい値、というように指定します。

したがって、LARGE(A2:A20,1) は MAX(A2:A20) と同じ値になり、LARGE(A2:A20,2) は2位の値を返します。

SMALL(range, k) は、最小値側から LARGE と同じ働きをします。SMALL(A2:A20,1) は MIN(A2:A20) と同じ値になり、SMALL(A2:A20,3) は3番目に小さい値を返します。

SMALL は、成績の低い担当者、最も安い選択肢、またはデータセット内で最も早い日付を抽出する場合に使用します。

上位3つの値を一覧にするには、k に 1、2、3 を指定した LARGE の数式を3つ並べます。より簡潔な方法は、補助番号を参照して数式を下方向にコピーできるようにすることです。

D2 に 1、D3 に 2、D4 に 3 が入力されている場合、次の1つの数式を下方向にコピーするだけで、上位3件のリスト全体を作成できます。

ROW 関数を使うと k を自動生成できるため、補助列は不要です。ROW()-1 は最初の行で 1、次の行で 2、その後も同様の値を返します。

下方向にコピーすると、追加の準備なしで順位付きリストを作成できます。リストの開始位置に合わせてオフセットを調整してください。

最新の Excel や Google Sheets では、SEQUENCE を k 引数に指定することで、1つの数式から上位5件全体をスピルさせられます。SEQUENCE(5) は 1、2、3、4、5 を生成します。

LARGE はその結果として5つの値を一度に返し、自動的に下方向へスピルします。1つの数式で、順位付きリスト全体を作成できます。

ビジネスでよくある質問に、「上位3人の顧客はどれだけ貢献しているか」があります。LARGE を SEQUENCE（または配列定数）とともに SUM で囲むと、上位の値を合計できます。

以下の数式は、範囲内で最も大きい3つの値を加算し、上位3件の合計を1つのセルで求めます。

k が 1 未満の場合、または範囲内の数値の個数を超える場合、LARGE と SMALL は #NUM! エラーを返します。

たとえば、19個しか数値がないリストから30番目に大きい値を求めると失敗します。レポートでは、呼び出しを IFERROR で囲んでこの問題に対処してください。

値だけを知るよりも、誰がその値を達成したかが分かるほうが便利です。LARGE を INDEX および MATCH と組み合わせて、最大値に対応する名前を取得します。

この方法では、最大の売上額を求め、その行を特定し、A列から対応する名前を返します。そのため、レポートには「Top seller: Maria」と表示できます。

他の統計関数と同様に、LARGE と SMALL が数えるのは数値だけです。範囲内のテキストと空白セルはスキップされるため、n 番目に大きい値の判定には影響しません。

そのため、見出し行を含む列を安全に指定できます。ただし、誤ってテキストとして保存された数値には注意してください。これらは無視されるため、LARGE や SMALL が返す値が変わる可能性があります。

B2:B20 に月ごとの売上が入力されています。経営陣は、最も成績の良い月と最も悪い月を3つずつ求めたいと考えています。

• 上位3件：=LARGE($B$2:$B$20,{1,2,3})
• 下位3件：=SMALL($B$2:$B$20,{1,2,3})

それぞれをスピル可能なセルに入力すれば、ダッシュボードに利用できる上位・下位の概要がすぐに得られます。

LARGE と SMALL の理解度を確認しましょう。

これで MAX と MIN の先にある値も取得できるようになりました。

• LARGE(range,k) は k 番目に大きい値を返し、SMALL(range,k) は k 番目に小さい値を返します。
• k = 1 は MAX / MIN と同じです。k を大きくすると、2位以下の値を取得できます。
• SEQUENCE または {1,2,3} のような配列を指定すると、上位 N 件のリストを1つの数式で作成または合計できます。
• INDEX-MATCH と組み合わせて値にラベルを付け、IFERROR で囲んで範囲外の k に対処します。