Excel。SUMPRODUCT関数は便利と聞くけど、どんな時につかうの？
＜SUMPRODUCT関数＞
SUMPRODUCT関数は、SUM＝足し算とPRODUCT＝掛け算を組み合わせた関数なのですが、どのような時につかったらいいのかを今回はご紹介します。
次の表をつかって、説明します。
A1:B6の表は生年月日を管理している表です。
この中から、8月生まれの人が何名いるのかを算出している表です。
なお、8月生まれと入力されている、D1には、ユーザー定義の書式を設定してあります。
これは、SUMPRODUCT関数で使用するためです。
さて、どうのようにしたら、8月の誕生日の人を算出したらいいのでしょうか？
考え方としては、誕生月を算出して、その件数を数えれば、8月の誕生日の人が何名いるのかを算出することができます。
もし、SUMPRODUCT関数をつかわないとしたら、2段階方式で算出することになります。
たとえば、MONTH関数をつかって、誕生月を抽出させます。
C2には、
=MONTH(B2)
というMONTH関数をつかって、誕生日から誕生月を算出させています。
C6までオートフィルで数式をコピーしています。
そして、D2の数式は、
=COUNTIF(C2:C6,D1)
条件を指定して件数を求めるには、COUNTIF関数をつかいます。
範囲には、C2:C6
検索条件には、D1
D1の数値を変えることで、別の月の件数を容易に算出することができます。
ここで使用するために、D1には、表示形式を設定したわけです。
8月生まれの人を結果的には算出することができたので、目的は達成することができたわけですが、数式を2つ作らないといけないわけですね。
今回は、算出できる列があったので、よかったのですが、資料によっては、難しい場合もあります。
ところが、SUMPRODUCT関数をつかうと、一発で算出することができます。
SUMPRODUCT関数をつかって作成した数式は、
=SUMPRODUCT((MONTH(B2:B6)=D1)*1)
です。
SUMPRODUCT関数の引数は、配列という形で、範囲を設定していきます。
パッと見た目、「？」になってしまうので、この数式について、解説していきます。
最初の
MONTH(B2:B6)=D1
ですが、この数式だけの結果を算出してみます。
C2には、
=MONTH(B2:B6)=D1
と設定しております。
範囲に、絶対参照が必要なのではと思われるかもしれませんが、Office365の「Office Insider」版には、『スピル』という機能が加わったことにより、絶対参照は不要です。
なお、通常のExcelでは、絶対参照が必要になりますので、数式は、
MONTH($B$2:$B$6)=D1
と設定します。
条件と合致しているならば、「TRUE」を算出し、合致していないと「FALSE」を算出しますが、このままでは、結局件数を数えなければなりません。
そこで、「×1」をこの式に追加します。
「TRUE」や「FALSE」に「×1」すると数値に変更することができます。
ちなみに、Excelでは、TRUEを1。FALSEを0と設定されています。
ここで、算出された結果の合計値が、「２」と算出される仕組みなのが、SUMPRODUCT関数です。
