 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
 |
|
前項の配列数式で紹介したSUMのほか、MAXやMIN、LARGE、SMALL、AVERAGEといった一般的な関数の多くは、セル範囲の代わりに配列を扱うことができます。
誤解しないでほしいのですが、これらの関数で配列を扱うために、配列数式にする必要があるわけではありません。
前項の「=SUM(COUNTIF(A1:A10,C1:C3))」で配列数式にしなければならなかったのは、中にあるCOUNTIF関数の検索条件に指定したセル範囲を、Excelに配列と見なさせるためでした。
SUMやMAX自体は、そのままでも配列を扱うことができます。
たとえば、「=SUM({1,2,"a"})」のように引数を配列定数で指定することができ、この場合は配列数式にする必要はありません。
「=SUM(1,2,"a")」でも同じでは？と思われるかもしれませんが、このように単独の引数として文字列を指定した場合は#VALUE!エラーになってしまいます。配列として指定すれば、セル範囲を指定した場合と同様、文字列などは無視されます。また、「{1,2,"a"}」という配列全体が1つの引数として扱われるので、この中に30個（Excel 2003以前の場合）という引数の限界を超える値を指定することも可能です。
そのまま配列が扱えるとはいっても、SUMなどの場合、カッコの中で行われている計算まで面倒を見てくれるわけではありません。COUNTIF関数の検索条件を配列化するためには、やはり配列数式にする必要がありました。
一方、Excelには、自らのカッコの中のセル範囲を配列として処理してくれる関数もあります。ここではその代表格として、SUMPRODUCTについて紹介することにします。
たとえば、前述の式を次のように書き直すと、配列数式にする必要はなくなります。
=SUMPRODUCT(COUNTIF(A1:A10,C1:C3))
ExcelヘルプのSUMPRODUCT関数の項を読むと、「引数として指定した配列の対応する要素間の積を返し、さらにその和を返す」とありますが、この例のように1つの配列だけを引数に指定することもできるわけです。
これ以外でも、SUMPRODUCTの実際の使われ方をよく検証すると、「引数は1つだけ」というケースが多いことに気づくはずです。つまり、SUMPRODUCTを使うメリットは、「配列数式にしなくても引数内のセル範囲を配列として扱える」という点にあります。
SUMPRODUCTの用法として特に多いのは、複数の条件に基づく集計です。
たとえば、下図のような表で、「居住地」が「東京」、「性別」が「男」である購入者の数、およびその「購入額」の合計を求めたいとします。
条件が1つだけならCOUNTIFとSUMIFで十分ですが、複数の条件を組み合わせて、となるとそうはいきません。このような場合によく使われている式は、次のようなものです。
購入者数：=SUMPRODUCT((B4:B13="東京")*(C4:C13="男")) 購入額計：=SUMPRODUCT((B4:B13="東京")*(C4:C13="男")*D4:D13)
この数式では、まず、B4からB13までの10個のセルの値がそれぞれ「東京」と等しいかどうかを調べています。この戻り値は「TRUE」または「FALSE」の論理値で、10行×1列の配列となります。
また、C4からC13までの10個のセルについても、それぞれその値が「男」と等しいかどうかを調べます。この戻り値も10行×1列の論理値の配列です。
Excelの場合、TRUEは1、FALSEは0と等価ですが、通常、SUMやSUMPRODUCTなどの関数では論理値は無視され、合計すべき値には含まれません。
SUMPRODUCTの場合、「=SUMPRODUCT((B4:B13="東京"),(C4:C13="男"))」のように複数の配列をそれぞれ引数としてカンマで区切って並べるだけでも乗算が行われるのではないかという気もしますが、この方法ではやはり論理値は計算されません。
「(B4:B13="東京")*(C4:C13="男")」のように乗算の演算子「*」を用い、明示的に演算操作を行うことによって、はじめて数値に変換され、合計すべき値と見なされるようになります。
この例の場合、2つの配列の同じ行の要素同士が乗算され、ともにTRUEの行のみ1に、どちらか一方でもFALSEの場合は0になります。それをSUMPRODUCTで合計したものが、すなわち「東京」で「男」の件数、ということになります。
個々の条件をそれぞれSUMPRODUCTの1つの引数として指定したい場合は、「=SUMPRODUCT((B4:B13="東京")*1,(C4:C13="男")*1)」のように個別に乗算して、論理値の配列をそれぞれ強制的に数値（1または0）の配列に変換するという方法もあります。この方法は、SUMPRODUCT関数を［関数の引数］ダイアログボックスから入力したい場合などにわかりやすいかもしれません。
購入額を求める数式では、B列とC列を乗算した結果の配列に、さらに購入額を乗算しています。つまり、B列とC列の乗算結果が1だった行だけ購入額が返され、そうでない行は0が返されます。
これをSUMPRODUCTで合計することで、2つの条件を満たす行のみの購入額の合計が求められます。
ちなみにこちらの場合は、「=SUMPRODUCT((B4:B13="東京")*(C4:C13="男"),D4:D13)」のように、もともと数値である購入額のセル範囲についてはカンマで区切って指定することもできます。