Excel（エクセル）のSMALL関数の使い方｜配列の中で何番目に小さい数値か求める
使用関数：SMALL、LARGE、IF、COUNTIF
SMALL（スモール）関数とは？
SMALLは「小さい」を意味する英単語で、SMALL関数は指定された配列の範囲で小さい方からの順番を求めます。「昇順」で位置を判定します。SMALLと反対に大きい方からの順番を求めるのはLARGE関数で、この2つの関数はセットで覚えた方がいいでしょう。
配列の中の数値を降順で求めるLARGE関数の使い方を分かりやすく解説しています。
SMALL関数の引数と書式
「数式」タブ「関数ライブラリ」→「その他の関数」→「統計」→ SMALLをクリックします。
SMALL関数の引数は「配列」と「順位」です。
書式の構成はこうなります。
SMALL関数の基本的な使い方
SMALL関数の引数ダイアログボックスで昇順での順位を指定して値を取り出してみましょう。
ゲームの得点表からブービー賞の点数を取得します。
※受賞者のセルにはあらかじめ=IFERROR(INDEX(A2:A14,MATCH(D2,B2:B14,0)),””)を入力してあり、得点が入力されると自動で氏名が表示されます。
第一引数「配列」
SMALL関数の引数ダイアログを開き、「配列」に得点データの列範囲をドラッグで指定します。
※SMALL関数は数値データを対象にする関数なので、「配列」内に空白、文字列、論理値が含まれていた場合は無視されます。
第二引数「順位」
配列の中で小さい方から何番目のセルを抽出するかを数値で指定します。「1」と指定した場合は最大値が返り、「2」と指定すれば小さい方から2番目のデータが返ります。
この例ではブービー賞の「2」を入力します。
ブービー賞の得点セルに配列の中の小さい方から２番目の値が表示されました。
SMALL関数に重複を除く条件を指定して小さい方から3番目までを取得
数値データの小さい方から○番目までをを取得したいという時、重複する値があると、最悪、同じ数値が並んでしまうようなことになります。
重複のセルをCOUNTIF関数で抽出
重複データを抽出するのには範囲から特定の条件に一致するセルをカウントするCOUNTIF関数を使います。「重複」という列項目を増やし、その最初のセルにCOUNTIFの数式を入れます。
引数「範囲」に数値データの一番上のセルを「$C$2:C2」というように指定します。
「C2セルからC2セルまで」ということですが、ポイントは最初のセル番地を絶対参照にすることです。
そうすることで、オートフィルコピーした時に一セルずつ検索する範囲が増えていきます。
引数「検索条件」にも数値データの一番上のセルを指定、これは可変させる必要があるため相対参照のままでおきます。
数式は「=COUNTIF($C$2:C2,C2)」となります。これをデータの最終行までオートフィルコピーします。
重複するデータがあるのが「C5」セルなら、C2セルからC5セルまでが「範囲」で、C5の入力値が検索条件になり、「2」がカウントされます。
IF関数で重複を除く条件を指定し、SMALL関数で下から３番目までを取得
IF関数で重複を除外する条件式を組み立てます。
「論理式」にはCOUNTIFで求めた「重複」列のセル範囲を絶対参照で入力し、「=1」を付け加えます。「もし、「重複」列のセルが1だったら、」という条件です。
「真の場合」にはLARGE関数で検索する範囲、点数のデータが入ったセル範囲をドラッグで指定します。「「重複」列のセルが1だったら、この範囲を検索します」という指定です。
「偽の場合」にはLARGE関数が無視する空白を示す「””」を入力します。「「重複」列のセルが1でなかったら、空白を返します」という指定です。
IF関数の数式は「IF(($D$2:$D$11)=1,$C$2:$C$11,“”)」
このIF関数の条件式をSMALL関数の「配列」にネストし、「順位」に「1」をセル参照で指定します。
重複データを除外して小さい方から３番目までの数値が取得できました。
最終的な数式は「=SMALL(IF(($D$2:$D$11)=1,$C$2:$C$11,””),F2)」となります。
小さい方から３番目までの数値に条件付き書式で色を付ける
分かりやすくするためにSMALL関数で求めた下位３セルに色をつけたいと思います。これは条件付き書式で実装します。
条件でセルに色を付けたい範囲を選択して、「ホーム」→「スタイル」グループ→「条件付き書式」をクリックして、プルダウンメニューから「新しいルール」を選択します。
「新しい書式のルール」ダイアログが開いたら、ルールの種類で「数式を使用して、書式設定するセルを設定」を選択し、画面中央の「次の数式を満たす場合に値を書式設定」のテキストボックスにSMALL関数の数式を「=C2<=SMALL($C$2:$C$11,3)」と入力します。「配列内のセルが、配列を検索して取得した小さい方から３番目の数値以下だったら」という条件の数式で、これを満たせばセルに書式設定した色が付くということです。
画面最下部の「書式」ボタンをクリックして、背景の塗り潰しやフォントの色、サイズなど好きな書式を設定してから「OK」。
小さい数値から３番目までのセルに色が付きました。
- SMALL関数は指定された配列の範囲で昇順（小さい方から）の順番を求める関数です
- SMALL関数とは反対に、降順（大きい方から）の順番を求めるのはLARGE関数です
- SMALL関数で小さい方から○番目の数値データを取得する時、重複を除くにはCOUNTIF、IF関数と組み合わせます
- SMALL関数で小さい方から○番目のセルに色を付けたければ、条件付き書式に数式を指定します