今回は○番目に大きい・小さいデータを表示するSMALL/LARGE関数について紹介します。
有名な関数で１番大きな数値を出力するMAX関数、１番小さな数値を出力するMIN関数
などがありますが今回は表示する順を任意の順位で変えれる関数です。
MAX・MIN関数ではできない表示が可能になるので是非参考にしてみてください。
それでは見ていきましょう。
■関数の仕組み
=LARGE（順位を求めたい範囲,上から表示したい順位)
=SMALL（順位を求めたい範囲,下から表示したい順位)
■○番目に大きなデータを表示する
まずは○番目に大きなデータを表示する方法について見ていきましょう。
下図の見本を元に説明していきましょう。
AさんからJさんの点数が入力されています。
さらに表示順位を「F2」に入力して、その条件に当てはまる得点を
「Ｆ6」に出力する仕組みになってます。
では関数の仕組みを見てみましょう。
=LARGE（順位を求めたい範囲,上から表示したい順位)
順位を求めたい範囲は全員の得点の「C3：C12」になります。
表示したい順位は「F2」に打ち込むので「F2」になります。
関数に当てはめるとこのようになります。
=LARGE(C3:C12,F2)
では実際に打ち込んでみましょう。
うまく機能していますね。
表示順位を2で打ち込んで「2位」を表示しています。
２番目は98なので「98」が出力される仕組みです。
ちなみに表示順位を変えると「条件の得点」も追従して変わってくれます。
■○番目に小さなデータを表示する
次は○番目に小さなデータを表示する方法について見ていきましょう。
先ほどの条件とは真逆な条件ですね。
同じく下図の見本を元に説明していきましょう。
では関数の仕組みを見てみましょう。
=SMALL（順位を求めたい範囲,上から表示したい順位)
順位を求めたい範囲は全員の得点の「C3：C12」になります。
表示したい順位は「F2」に打ち込むので「F2」になります。
関数に当てはめるとこのようになります。
=SMALL(C3:C12,F2)
では実際に打ち込んでみましょう。
こちらもうまく機能していますね。
２番目の順位は「6」なので「6」が出力されました。
LARGE関数同様に表示順位の「F6」を打ち換えると
点数も追従して変わってくれます。
■よくあるエラーと対処法
ではLARGE関数・SMALL関数を使用する中でよくあるエラーについて見ていきましょう。
・表示できない順位が入力されている
こちらは参照しているデータ数に対して表示する順位の数値が大きすぎることが挙げられます。
先ほど使用していたシートを参考にしてみましょう。
対象データは「C3」から「C12」の「１０個のデータ」です。
では見本で表示順位の「Ｆ2」を「100」に打ち換えてみましょう。
つまり「100番目に小さいデータ」を出力する関数になります。
結果はこのようになります。
エラーになりました。
10個しかないデータに100番目の数値は存在しませんから
エラーになってしまいます。
こちらはLARGE関数・SMALL関数ともにエラーになるので
データ数より多い表示順位を入力しないようにしてください。
・参照先にエラーが存在する
では再度、見本を見てみましょう。
今までと関数の仕組みや参照範囲は変わりませんが
参照範囲のDさんの得点がエラーになっています。
この状態で計算した結果はこちらです。
結果もエラーになってしまいました。
こちらの解決方法は参照範囲のエラーを解決するしかありません。
IFERROR関数などを使用してエラーにならない方法を見つけてください。
・文字列になっている
こちらは実際にエラーにはなりませんが
算出結果がおかしくなってしまいます。
下図をご覧ください。
条件では１番小さいデータを表示するようにしていますが
結果は「6」になっています。
本来は一番値の小さな「2」が表示されるはずですが「2」の左上に緑の印がありますね。
通常はデータは「標準」や「数値」で入力していると思います。
しかし緑の印がついている「2」を見てみましょう。
「文字列」になってます。
つまり「2」は数値でなく「文字」として認識されているため
算出時にスルーされたことになります。
解決方法は「標準」または「数値」に戻し、入力しなおせば解決します。
■公式の説明
わかりやすいように説明したため公式と使用する語句が異なりますが
マイクロソフト公式の説明については下記のリンクを参照してください。☟
LARGE 関数 – Office サポート (microsoft.com)
SMALL 関数 – Office サポート (microsoft.com)
■まとめ
いかがだったでしょうか。
最大値、最小値ではなく抽出したい順を選択できるのはとても便利ですよね。
ぜひ、MAX・MIN関数と使い分けて使用してみてください。
それでは次回の記事でお会いしましょう。