Excel。手早く一行おきで合計値を算出したいけど、どうしたらいいの。
＜SUMPRODUCT+MOD+ROW関数＞
帳票をExcelにそのまま移行した場合、算出する方法は簡単でも、それをどのように数式として表現したらいいのか、難しくなることがあります。
例えば次の表。
B列の売上高の合計を一行おきに算出したいというのが、目的です。
なんで一行おきなのかというと、2行1組になっていて、1行目が2022年で2行目が2023年になっているというわけです。
年のフィールド（列）があれば、SUMIF関数がつかえるのですが、条件につかえる列がないので、つかえません。
SUM関数で、一行おきに、範囲選択するしかないのでしょうか。
それでは、ミスも発生する可能性が上がってしまうし、何よりも面倒です。
そこで、SUMPRODUCT関数をつかうことで、手早く算出することができます。
F1に次の数式を設定します。
=SUMPRODUCT((MOD(ROW($B$2:$B$7),2)=0)*$B$2:$B$7)
F2には、次の数式を設定します。
=SUMPRODUCT((MOD(ROW($B$2:$B$7),2)=1)*$B$2:$B$7)
この数式で、算出することができるわけですが、少し複雑な数式なので、説明をします。
SUMPRODUCT関数は、「積の結果」を合計する関数です。
余計な計算列を作らなくて一発で、算出したい時に使うと便利な関数です。
SUMPRODUCT関数の引数から確認していきます。
C1に、
=MOD(ROW(C2:C7),2)=0
と引数のところを抽出してみました。スピル機能で、C7まで算出されています。
ROW関数は、行番号を算出する関数です。C2の行番号は「2」です。
MOD関数は、除算した余りを算出しますので、ROW関数で算出した結果を2で除算した余りを算出しています。
C2は、2÷2なので、余り「0（ゼロ）」です。
その結果が「0」と等しいならば「TRUE」と算出され、等しくなければ「FALSE」と算出される仕組みになっています。
算出結果をみると、TRUEとFALSEが一行おきになっていることがわかります。
これで、一行おきという条件に対応することができるというわけです。
また、Excelは、「TRUE」が1で「FALSE」が0と設定されています。
PRODUCTは掛け算を意味しますので、売上高にFALSE=0を掛ければ「0」になるので、TRUEのところだけをSUMするので、一行おきに合計値を算出できます。
いままで複雑で、いくつか計算列を経由して算出していた数値も、今回使用した、SUMPRODUCT関数をつかうと、コンパクトになるかもしれませんので、つかえないか検討してみるのもいいかもしれませんね。
