SUMPRODUCT関数について
はじめに
「今週末で今年の夏競馬も終了するけど、今年も馬券成績は微妙だったなぁ・・・いくら負けたんだろう？」
って考えたくもないですが、筆者はEXCELで馬券収支を記録しています。ただ、1つの表に纏めて記録していますので、開催場所や距離などで限定した収支を確認する場合はフィルタや並び替えをしたりして、計算しなければならないので、めんどくさくなっていました。
そこで最近はSUMPRODUCT関数という便利な関数を使っています。筆者は普段の仕事でもEXCELを使うことが多いで、たまにSUMPRODUCT関数も使うのですが、この関数は便利な反面、式が少し複雑で忘れてしまいやすいので、敬遠されがちなところがあります。
需要があるかは怪しいですが、SUMPRODUCT関数の使い方をご紹介したいと思います。(筆者も忘れてしまった際は、この記事を見て、使い方を思い出すことにします^^;)
SUMPRODUCT関数とは？
EXCELでのSUMPRODUCT関数を説明文を見てみますと、
SUMPRODUCT(array1, array2, array3,…)
指定された配列に対応する要素の積を合計した結果を返します。
と書いてあります。まあ、敬遠しますね・・・
基本的な使い方は次の二つです。
①複数の条件(数字、単語)に合致したものの個数を数えてくれる。
②複数の条件(数字、単語)に合致したものの 合計値を算出してくれる。
馬券収支を例にしますと、
①は競馬場(小倉など)×馬場(芝,ダート)×距離(1200mなど)で何レース的中したなどの数を数えるケース、②は 競馬場(小倉など)×馬場(芝,ダート)×距離(1200mなど)で いくらプラス(マイナス)になったかを計算するケースに使います。
基本的な使い方
①的中数を数えてみる
まず、戦績のリストを下記の表のように作ります。(誰の戦績かは分かりませんが…)
小倉 芝1200mの着順が1着なレースが2レースありますので、単勝的中数をSUMPRODUCTで算出したいと思います。的中数(F列, 18行目)に的中数を表示できるようにしたいと思います。
まず、F列,18行目を選んで、上のfxの横の覧に関数を入力します。
=SUMPRODUCT((D3:D14=C18)*(G3:G14=D18)*(H3:H14=E18)*(L3:L14=1))
- D3～D14列からC18(小倉)がいくつあるか？ ⇒(D3:D4=C18)
- G3～G14列からD18(芝)がいくつあるか？ ⇒(G3:G14=D18)
- H3～H14列からE18(1200)がいくつあるか？ ⇒(H3:H14=E18)
- L3～L14から1(1着)がいくつあるか？ ⇒(L3:L14=1)
の4条件に合致する個数を計算してくれることになります。この4つの条件の式をSUMPRODUCT関数のカッコ内に掛け合わせるような数式を入力しますと、確かにF列18行目は2になっています。これがSUMPRODUCT関数の1つ目の使い方となります。
②収支の合計を算出する
①と式はほとんど同じです。
=SUMPRODUCT((D3:D14=C18)*(G3:G14=D18)*(H3:H14=E18),N3:N14)
最後だけ*(L3:L14=1)⇒,N3:N14 に代わっています。これはL行の着順はすべての着順を含むので不要であり、代わりにN行の実回収率の合計値を出してくれる、というものになります。つまり、小倉 芝1200mの回収率は140%ということが計算できることになります。
複合的にも使える
実は、これらを組み合わせると、より複雑な使い方ができるようになります。
「直近3年間で新潟1000mで8枠の馬が、馬体重470～500kgで3番人気以内で前半2ハロン22.5以内で走った場合の平均着順」という少し変な条件ですが、この計算をSUMPRODUCT関数で算出したいと思います。データベースはTarget frontier JVからEXCELにコピーして利用します。(実際は馬体重、人気もTarget frontier JVで条件の絞り込みは可能ですが、今回は絞り込まずに行います。)
まず、Target frontier JVで新潟 芝1000mの8枠の馬でレースを検索してEXCELに貼り付けます。不要な列は削除して見やすくすると良いです。ここで関係するデータはP列(走破タイム)、R列(上がり3F)、W列(馬体重)、M列(着順)です。
Q列(前2F)はデータに無いので、(走破タイム)ー(上がり3F)で求めることになります。走破タイムは1分を超えると1000になるので、少し面倒くさくなります。筆者はIF関数とRIGHT関数を使って計算しています。
Q列6行目 =(IF(P6>=1000,600,0)+RIGHT(P6,3))/10-R6
IF関数とＲIGHT関数は難しい関数では無いので、説明を省略しますが、走破タイムを秒数に変換して、上がり3Fを引いて前半2Fを算出することをしています。
次に平均着順を表示したいE列3行目を選び、上のfxの右の空欄に下記の数式を入力します。
=SUMPRODUCT((W6:W185>=470)*(W6:W185<=500)*(L6:L185<=3)*(Q6:Q185<=22.5),M6:M185)/SUMPRODUCT((W6:W185>=470)*(W6:W185<=500)*(L6:L185<=3)*(Q6:Q185<=22.5))
答えは2.4着ということが分かります。(長い式でスミマセン…)
これは基本的な使い方の②÷①で計算しておりまして、
②(赤字)は「馬体重470～500kgで3番人気以内の馬が前半2ハロン22.5以内」の馬の着順の合計値、①(青字)は「馬体重470～500kgで3番人気以内の馬が前半2ハロン22.5以内」 の馬の頭数の数式ですので、着順の合計値を頭数で割ると平均の着順となります。
実は、筆者は騎手の単勝回収率の集計などにも、このSUMPRODUCT関数を使用して算出しています。EXCELを良く使う人にはわりと利用価値の高い関数だと思います。まあ、競馬にEXCELを利用している人では、すでに知っているが人が多く、利用しない人は全然関係無い、というようなものだとは思うんですけどね…
