- ホーム >
- FMVサポート >
- Q&A・パソコン活用情報をコーナーから探す >
- エクセル関数塾 >
- X番目に大きい値はどれ？LARGE ⁄ SMALL関数で指定した順位の値を求めよう
第35回X番目に大きい値はどれ？LARGE ⁄ SMALL関数で指定した順位の値を求めよう
塾長！いつも出張に当社FJトラベルをご利用いただき、
ありがとうございます。
当社の中でも、塾長のおかげで関数を使える社員が多くなって、業務の効率化が進んでおります。
さて、以前教えていただいたMAX関数やMIN関数は最大値や最小値を求めるには便利なのですが、2番目とか3番目とか……X番目の値を求めたいようなときに少々困っています。塾長のお知恵を拝借できませんでしょうか？
わしが、エクセル関数塾塾長！関 数之進（せきかずのしん）である！
いつもチケットの手配をありがとう！
どれどれ、表を見せてごらん？なるほど、投票数の上位や下位のX番目までの数字を求めたいのじゃな？このような表なら、「LARGE関数」や「SMALL関数」を使って、上位•下位のX番目の値まで求められるぞ。その技を極意書を使って説明しよう！
関数塾 極意書
LARGE関数 ⁄ SMALL関数でX番目の数を求める
LARGE（ラージ）関数とSMALL（スモール）関数を利用すると、セル範囲の中で「X番目の数」を求めることができます。LARGE関数は、数の多いほうから数えてX番目、SMALLは少ないほうから数えてX番目の値を表示できます。RANK関数との違いは「セル範囲の中の順位」を求めますが、LARGE関数やSMALL関数はX番目（順位）に相当する「値」を求めることができます。
ここでは、LARGE関数とSMALL関数の基本的な書式と機能について紹介します。
LARGE関数とSMALL関数の書式
LARGE関数は次のように入力します。
=LARGE（配列,順位）
「配列」にはセルの範囲を指定し、その中で何番目の大きさの値を求めたいかを「順位」に指定します。
同じように、SMALL関数は
=SMALL（配列,順位）
「配列」にはセルの範囲を指定し、その中で何番目の小さな値を求めたいかを「順位」に指定します。
今回の例では、E列に投票数の順位が入力されています。その順位のセルを利用して、投票数が多い順で投票数を求めます。
注: セルE4の値を変更すると、セルE5以降は+1ずつ加算される式が入力されています。
LARGE関数を入力する、セルF4をアクティブにし、［数式］タブの［関数の挿入］をクリックします。
［関数の分類］で「統計」を選択し、［関数名］で「LARGE」を選択して、［OK］ボタンをクリックします。
［配列］に「$C$4:$C$23」、［順位］に「E4」を入力し、関数の引数ダイアログボックスの［OK］ボタンをクリックします。
セルF4の式をセルF8までコピーします。
セルE4の値を「10」に変更してみると、自動的にE列の連番が変更され、F列のLARGE関数の値も上位10番目から15番目の値に変更されます。
同様に、セルF13に次の式を入力し、値の小さいものから3番目までの値を求めます。
=SMALL($C$4:$C$23,E13)
ステップアップ：条件付き書式と組み合わせて上位、下位のセルに書式を付ける
LARGE関数やSMALL関数は条件付き書式にも利用できます。例えば、一覧の並び順は変更したくないけれども、値として「上位3番目までの値のセルを、背景に網掛けをしたい」とか、「下位5番目までの文字を赤で表示したい」など、さまざまな場面で活用できます。
次の例では、クラスの成績表の一覧から上位5人の背景に色付きの網掛けにしたいので、条件付き書式の書式ルールに「LARGE」関数を利用して、書式を設定してみましょう。
書式を設定するセルC4からC18を範囲選択します。
［ホーム］タブの［条件付き書式］をクリックし、［新しいルール］をクリックします。
［数式を使用して、書式設定するセルを決定］クリックし、次のように式を入力し、［書式］をクリックします。
=C4>=LARGE($C$4:$C$18,5)
［塗りつぶし］タブをクリックして、色を選択後、［OK］ボタンをクリックします。
新しい書式ルールのメニューの［OK］ボタンをクリックします。
上位5名の点数の背景に色が設定されました。
LARGE関数とSMALL関数は、数字の一覧の中から上位や下位X番目の数を求めることができる関数です。データ数の多い一覧から最大値や最小値だけでなく、X番目の数が求められます。また、条件付き書式とあわせるとX番目の数のセルに書式が付くので、一覧から必要なデータを探しやすくなります。
問題
次のような会員数の順位の位置を作成してみましょう。
次の表は、会員数の一覧です。会員数の順位が上位3番目までのサークルの人数を調べましょう。
サンプルをご利用になる場合は、パソコンにて本ページをご覧ください。
問題に必要なエクセルのファイルをダウンロードしてください。
ダウンロードするには、下記のファイル名を右クリックして、メニューから「対象をファイルに保存」を選択してください。
本講座では、ファイルの保存先を《ドキュメント》としています。
ダウンロード方法がわからない場合は、下記ページをご参照ください。
