ヘルプの森~Excel・Access・Office全般ヘルプデスクサイト

No.005

アイコン 0を抜いた最小値を調べるには 《MIN・SMALL・COUNTIF・IF》

Excel2003/2007/2010/2013

0以上の数値がある場合に、MIN関数(関数の分類: 統計)で最小値を求めると0が返ります。

MIN関数

今回は、0を抜いた最小値を求める方法です。負の数はないとします。
方法は主に次の2つです。

  • 順位で求める方法
  • 配列数式を利用した方法

では、順番にご案内していきましょう。

<順位で求める>

IF関数(関数の分類: 論理)、MIN関数(関数の分類: 統計)、SMALL関数(関数の分類: 統計)、COUNTIF関数(関数の分類: 統計)の4つの関数を使います。

早速ですが、次の式で求めることができます。

  =IF(MIN(A1:A5)=0,SMALL(A1:A5,COUNTIF(A1:A5,0)+1)
    ,MIN(A1:A5))

0抜きの最小値

では、式を部分的に説明していきます。

まず、最初のIF関数では最小値が0のときと、最小値が0でないときの分岐を行っています。
最小値が0でなければその最小値が答えです。(IF関数の[偽の場合])

最小値が0である場合(IF関数の[真の場合])は、順位に対応する値を求めることができるSMALL関数を利用して、0の次に小さい値の順位から求めます。

ただここで、0が複数個ある可能性も考える必要があります。
その場合、0の次に小さい値の順位は、0の個数+1の順位となります。

  COUNTIF(A1:A5,0)+1

COUNTIF関数を使い0の個数を求め、+1しています。

0の次に小さい値の順位がわかったら、SMALL関数で範囲の中の順位に該当する値を求めます。

  SMALL(A1:A5,COUNTIF(A1:A5,0)+1)

SMALL関数

<配列数式を利用>

次に、配列数式を利用した方法をみてみましょう。

MIN関数にIF関数をネストして配列として求めます。数式は次の式です。

  {=MIN(IF(A1:A5=0,"",A1:A5))}

配列数式

式の前後に中かっこ{ }がついています。
これは配列(範囲)をまとめて処理して求める、配列数式のしるしです。
中かっこは、最後「Ctrl」+「Shift」+「Enter」で確定したあとで付きますので、それ以外の数式を普通に設定します。

では、手順をご案内します。

答えを求めるセルでMIN関数をたて、引数にIF関数をネストします。
普通IF関数では1つのセルを評価しますが、今回は複数セル(配列)を指定し、まとめて処理させます。

セルA1:A5のそれぞれのセルが0であれば(真の場合は)空白にする、そうでなければ(偽の場合は)そのままそのセルの値を返すようにしています。

配列数式

このIF関数を指定するとき、[関数の引数]ダイアログ ボックスを使用しているならば、引数のボックスの右の表示を確認してください。
特に赤いしるしのところが、IF関数で返される配列です。

以上で数式は完成、配列数式として求めますので、「Ctrl」+「Shift」+「Enter」で確定します。(配列として確定)

つまり、IF関数で0は空白に置き換えられた配列が返り、MIN関数で処理しています。
MIN関数は空白は無視しますので、0を抜いた最小値が求められています。

ヘルプmemo

<SMALL関数 (統計関数)>

データの中から指定した順位番目に小さい値を返します。
 数式: =SMALL(配列, 順位)
 引数:
  配列... 対象データの配列またはセル参照を指定
  順位... 抽出する値の小さい方から数えた順位を数値で指定
 ヒント:
  同じ数値が複数あった場合は同じ順位となり、次の順位は複数分とびます。
  引数[配列]に文字列があった場合は無視されます。

ヘルプmemo

<配列数式とは>

配列とは、複数のデータを1つのまとまった単位として扱うことをいいます。
セル参照の代わりに配列を用いることができ、式を「Ctrl」+「Shift」+「Enter」を押して確定すると、数式全体が{ }で囲まれた状態になります。
このような数式のことを配列数式といいます。
IF関数をからめて特によく使われます。

関連ヘルプ

重複データを調べる 《COUNTIF》
規則性がない空白セルを除いた行に連番をふるには 《IF・COUNTA・COUNTIFS》
複数条件を満たすデータの件数を求めるには 《COUNTIF・COUNTIFS・SUMPRODUCT》
条件を満たすセルの個数を数える 《COUNTIF》
カウントする関数が何をカウントするか 《COUNT・COUNTA・COUNTBLANK》
ページのトップへ戻る