SMALL関数の使い方|配列の中で何番目に小さい数値か求める

Excel(エクセル)のSMALL関数の使い方
Excel関数

Excel(エクセル)のSMALL関数の使い方|配列の中で何番目に小さい数値か求める

使用関数:SMALL、LARGE、IF、COUNTIF

SMALL(スモール)関数とは?

SMALLは「小さい」を意味する英単語で、SMALL関数は指定された配列の範囲で小さい方からの順番を求めます。昇順」で位置を判定します。SMALLと反対に大きい方からの順番を求めるのはLARGE関数で、この2つの関数はセットで覚えた方がいいでしょう。

配列の中の数値を降順で求めるLARGE関数の使い方を分かりやすく解説しています。

https://www.tschoolbank.com/excel/function-large/

SMALL関数の引数と書式

「数式」タブ「関数ライブラリ」→「その他の関数」→「統計」→ SMALLをクリックします。
「関数ライブラリ」→「その他の関数」→「統計」→ SMALLをクリック
SMALL関数の引数は「配列」と「順位」です。

書式の構成はこうなります。
SMALL関数の書式の構成

SMALL関数の基本的な使い方

SMALL関数の引数ダイアログボックスで昇順での順位を指定して値を取り出してみましょう。

ゲームの得点表からブービー賞の点数を取得します。
SMALL関数で昇順での順位を指定して値を取得
※受賞者のセルにはあらかじめ=IFERROR(INDEX(A2:A14,MATCH(D2,B2:B14,0)),””)を入力してあり、得点が入力されると自動で氏名が表示されます。

第一引数「配列」

SMALL関数の引数ダイアログを開き、「配列」に得点データの列範囲をドラッグで指定します。
SMALL関数の引数ダイアログの「配列」に得点データの列範囲をドラッグで指定
※SMALL関数は数値データを対象にする関数なので、「配列」内に空白、文字列、論理値が含まれていた場合は無視されます。

第二引数「順位」

配列の中で小さい方から何番目のセルを抽出するかを数値で指定します。「1」と指定した場合は最大値が返り、「2」と指定すれば小さい方から2番目のデータが返ります。

この例ではブービー賞の「2」を入力します。
SMALL関数の引数「順位」に「2」を入力

ブービー賞の得点セルに配列の中の小さい方から2番目の値が表示されました。
SMALL関数で配列の中の小さい方から2番目の値が表示された

SMALL関数に重複を除く条件を指定して小さい方から3番目までを取得

数値データの小さい方から○番目までをを取得したいという時、重複する値があると、最悪、同じ数値が並んでしまうようなことになります。
SMALL関数で重複データのある範囲から下位3番目までを取得

重複のセルをCOUNTIF関数で抽出

重複データを抽出するのには範囲から特定の条件に一致するセルをカウントするCOUNTIF関数を使います。「重複」という列項目を増やし、その最初のセルにCOUNTIFの数式を入れます。
引数「範囲」に数値データの一番上のセルを「$C$2:C2」というように指定します。
「C2セルからC2セルまで」ということですが、ポイントは最初のセル番地を絶対参照にすることです。
そうすることで、オートフィルコピーした時に一セルずつ検索する範囲が増えていきます。

引数「検索条件」にも数値データの一番上のセルを指定、これは可変させる必要があるため相対参照のままでおきます。
重複データを抽出するCOUNTIF関数の範囲と検索条件を指定

数式は「=COUNTIF($C$2:C2,C2)」となります。これをデータの最終行までオートフィルコピーします。
重複をチェックするCOUNTIF関数の数式をデータの最終行までオートフィルコピー

重複するデータがあるのが「C5」セルなら、C2セルからC5セルまでが「範囲」で、C5の入力値が検索条件になり、「2」がカウントされます。
重複データが入った行でのCOUNTIFの引数の構成

IF関数で重複を除く条件を指定し、SMALL関数で下から3番目までを取得

IF関数で重複を除外する条件式を組み立てます。

論理式」にはCOUNTIFで求めた「重複」列のセル範囲を絶対参照で入力し、「=1」を付け加えます。「もし、「重複」列のセルが1だったら、」という条件です。

真の場合」にはLARGE関数で検索する範囲、点数のデータが入ったセル範囲をドラッグで指定します。「「重複」列のセルが1だったら、この範囲を検索します」という指定です。

偽の場合」にはLARGE関数が無視する空白を示す「””」を入力します。「「重複」列のセルが1でなかったら、空白を返します」という指定です。
IF関数で重複を除外する条件をダイアログで指定

IF関数の数式は「IF(($D$2:$D$11)=1,$C$2:$C$11,“”)

このIF関数の条件式をSMALL関数の「配列」にネストし、「順位」に「1」をセル参照で指定します。
IF関数の条件式をSMALL関数の「配列」にネストし、「順位」に「1」をセル参照で指定

重複データを除外して小さい方から3番目までの数値が取得できました。
重複データを除外して小さい方から3番目までの数値が取得できた

最終的な数式は「=SMALL(IF(($D$2:$D$11)=1,$C$2:$C$11,””),F2)」となります。

小さい方から3番目までの数値に条件付き書式で色を付ける

分かりやすくするためにSMALL関数で求めた下位3セルに色をつけたいと思います。これは条件付き書式で実装します。

条件でセルに色を付けたい範囲を選択して、「ホーム」→「スタイル」グループ→「条件付き書式」をクリックして、プルダウンメニューから「新しいルールを選択します。
「ホーム」→「スタイル」グループ→「条件付き書式」→「新しいルール」を選択

新しい書式のルール」ダイアログが開いたら、ルールの種類で「数式を使用して、書式設定するセルを設定」を選択し、画面中央の「次の数式を満たす場合に値を書式設定」のテキストボックスにSMALL関数の数式を「=C2<=SMALL($C$2:$C$11,3)」と入力します。「配列内のセルが、配列を検索して取得した小さい方から3番目の数値以下だったら」という条件の数式で、これを満たせばセルに書式設定した色が付くということです。

画面最下部の書式」ボタンをクリックして、背景の塗り潰しやフォントの色、サイズなど好きな書式を設定してから「OK」。
SMALL関数の数式を条件にした条件付き書式の新しい書式のルールを設定
小さい数値から3番目までのセルに色が付きました。
SMALL関数で指定した条件付き書式によって小さい数値から3番目までのセルに色が付いた

SMALL関数のまとめ
  • SMALL関数は指定された配列の範囲で昇順(小さい方から)の順番を求める関数です
  • SMALL関数とは反対に、降順(大きい方から)の順番を求めるのはLARGE関数です
  • SMALL関数で小さい方から○番目の数値データを取得する時、重複を除くにはCOUNTIF、IF関数と組み合わせます
  • SMALL関数で小さい方から○番目のセルに色を付けたければ、条件付き書式に数式を指定します
タイトルとURLをコピーしました