Excel。前回よりも数値がいい件数を求めるのに簡単な方法はないの？
＜SUMPRODUCT関数＞
やりたいことは簡単そうなのに、意外と手間暇がかかったりする帳票というが現場にはあったりします。
例えば、次の表。
B列の1回目の得点とC列の2回目の得点を比べて、2回目の得点のほうが高い人は何人いるのか算出したいとします。
考え方として、B列とC列を比べて、C列のほうが高ければ、「○」と表示させて、その「○」の数を数えるという方法が、思いつきます。
この場合、D2の数式は、
=IF(B2<C2,”○”,””)
とすれば、いいわけです。
そのあとに、COUNTIF関数をつかって「○」を数えれば算出できます。
注意しないといえないのは、COUNTA関数では、算出結果の空白「””」も数える対象になってしまうので、COUNTIF関数を使う必要があります。
F1にそのCOUNTIF関数の数式をつくるとしたら、
=COUNTIF(D2:D11,"○")
と設定します。
COUNTIF関数をつかって件数を数えてもいいのですが、件数が知りたいわけなので、「○」ではなくて、「1」にすれば、SUM関数で合算値を算出するほうが、もっと楽になるわけですね。
=IF(B2<C2,1,””)
このように、判定結果を数値にして、SUM関数で合算することで件数を求めるという方法も楽なのですが、もっと楽に算出する方法があるのです。
それができるのが「SUMPRODUCT関数」です。
このSUMPRODUCT関数を使えば、D列のような一時的に算出させる列やセルを用意しないで、一発で算出することができます。
まずは、F1にSUMPRODUCT関数をつかった数式を設定してみましょう。
=SUMPRODUCT((B2:B11<C2:C11)*1)
確認してみましょう。
先程と同じように、算出されたことが確認できました。
数式について、説明していきます。
SUMPRODUCT関数は、SUM関数+PRODUCT関数の二つの関数が一つになった関数で、SUM関数は、和算。
PRODUCT関数は、乗算の関数です。
これを踏まえたうえで、
引数の(B2:B11<C2:C11)を説明してきます。
これは、B2<C2～B11<C11まで成立しているか否かを算出させています。
実際にD列をつかって、算出してみます。
条件が満たされていれば「TRUE」。そうでなければ「FALSE」が算出されます。
この「TRUE」「FALSE」に「*1」すると、数値に変換することができます。
なぜ、「1」と「0」なのかというと、Excelでは、TRUEが「1」。
FALSEが「0」と設定されているからです。
この結果を合計したのが、F1の値というわけです。
SUMPRODUCT関数は、メジャーというほどの関数ではないかもしれませんが、ちょっと知っていると、意外と使える関数なのかもしれませんね。
今回のSUMPRODUCT関数のように、意外と知っていると便利という関数が、それぞれの現場であるかと思いますので、探して、試してみるといいかもしれませんね。
