(Excel)配列数式で複数条件を指定する

(Excel)配列数式を使ってみたで、配列数式のことを書きました。
しかし、上記の場合は 0 より大きい値の最小値を求めたいということで、条件が一つでした。

この条件が複数になった場合について今回書きます。


今回はWindowsのパフォーマンスカウンタで2時間おきに取得した空きメモリの容量で、業務時間(9-18時)中の平均と合計を求めたいということです。

下記が元データになります。この色がついている部分の 空きメモリ の平均と合計が知りたいわけです。


配列数式を使わずにできるのは、作業用の列を用意して、そこに下記のような数式を入れ、平均と合計値がほしい所に普通にAverageとSum関数を使います。
作業用列の数式(D3の例)

=IF( AND(VALUE(MID(A3,12,2))<=18,VALUE(MID(A3,12,2))>=9),B3,"")

さて、これで配列数式を使うと平均を求める場合は下記のような計算式になります。

=AVERAGE(IF((VALUE(MID(A3:A24,12,2))<=18)*(VALUE(MID(A3:A24,12,2))>=9),B3:B24,""))

(VALUE(MID(A3:A24,12,2))<=18) と (VALUE(MID(A3:A24,12,2))>=9) というのが条件ですね。9時以上 かつ 18時以下という条件になります。
さて、二つの条件を指定するときは、AND(またはOR)関数が使えません。
条件式の結果は数値になっています。True=1,False=0ですね。
なので、AND条件としたい場合は二つの条件の論理積を取ります。なので、* を使ってます。
同じようにOR条件としたい場合は二つの条件の論理和(+)を求めればOKです。




ちなみに、IF関数は省略することができます。
例えば合計を求める部分はIF関数を省略して、下記のようになります。

=SUM((VALUE(MID(A3:A24,12,2))<=18)*(VALUE(MID(A3:A24,12,2))>=9)*B3:B24)

しかし、平均を求める部分でIF関数を省略すると、イメージにあるように 151.045.. となっていしまいます。
これは条件を満たせてない値が0として処理されてしまい、平均を求めるときの個数にカウントされてしまっているからです。(本当は10で合計値をわらないといけないのに、21で割ってしまいます)
なのでaverage関数を使うときはIF関数を使った方が安全ですね。


こうしてみると、配列数式かなり使えます。
まぁExcel2007からはSUMIFやAVARAGEIF関数もあるので出番は減るかもしれませんが、OpenOffice Calcでも同じように使えるのでそういう場面で役立つと思います。

参考:
第４回　複数条件の指定方法 非常に分かりやすいです。
Excel：配列数式とは