Excelを使っていると1行（1列）置きに計算をしたいときがあります。
今回は合計とカウントの方法をメモに残します。
【1行/1列置きに合計】
[B14]=SUMPRODUCT((B4:B12)*(MOD(ROW(B4:B12),2)=MOD(ROW(B4),2)))
使用する関数はSUMPRODUCTです。この関数には２つの使い方があります。
１つは範囲を２つ指定してそれぞれの積と和をえるというもの。電卓でのメモリ計算のように単価と個数の組みで積を計算し、それぞれ合計を算出します。
もう１つは、範囲指定と条件式を組み合わせたもの。条件式でTRUEは1、FALSEは0の扱いですので、これを範囲の順に適用し、また範囲の数値と組み合わせて計算します。
では、B14の計算式を例に見てみましょう。
SUMPRODUCTの範囲指定は順に数値が取り出され、1行毎に計算結果が出て、それらは和の値となります。
SUMPRODUCTの中には２つの式があります。1つ目は「(B4:B12)」の部分で表の数値をそのまま取り出しています。
２つ目は条件式の部分「(MOD(ROW(B4:B12),2)=MOD(ROW*B4),2))」です。条件式の結果は最初の行が1、次が0になり、あとはそれを繰り返します。（この詳細は後ほど）
２つの式は「*」で結ばれていますので、B4の数値と1の積、B5の数値と0の積と続いて、B12までの範囲で積を計算したらそれらの合計が算出されます。
これで1行毎に奇数行だけが合計として表示されます。
条件式の仕組みを見てみましょう。
「ROW(B4:B12)」は順に行番号を算出します。ROW(B4)は4です。1行毎に結果が欲しいので、MOD関数を使って行番号を2で割った余りを調べます。余りは0になりました。次の行は余りが1、次は0と交互に余りが変わります。
これらを条件式のもう一方と比較します。もう片方は「MOD(ROW(B4),2)」です。常に範囲の最初のセルと比較しますので、その行を起点に1行置きに同じ余りとなるのです。
これにより、B4ではTRUE、B5ではFALSE、B6ではTRUEと繰り返され、TRUEの時だけセルの「数値*1」が実行され、B4 B6 B8 B10 B12の合計が最後に計算されるという仕組みです。
B14の式はB15にそのままコピーして使えます。
【1行/1列置きにカウント】
[B14]=SUMPRODUCT((MOD(ROW(B4:B12),2)=MOD(ROW(B4),2))*(B4:B12="○"))
カウントをする場合も条件式は前述の合計の場合と同じです。
今回は２つの条件式を掛けています。
今回もB14を例に見てみましょう。
1つ目の条件式は行毎に1と0を算出しています。
2つ目の条件式は「(B4:B12="○")」ですので、セルに○が入っている場合にTRUEとなります。
これにより2つの条件式の積が算出されますので、B4は1、B6は1、B8は0、B10は1、B12は0となり、その和は3となります。
2つ目の条件式を変えると様々なパターンに対応できます。
「(B4:12<>"")」では、空欄でない場合にカウントしますので○以外の文字が混ざっていてもカウントします。
「(B4:12>0)」では、0より大きな数値が入っているセルのみ算出します。
【SUMPRODUCT関数】
条件式のない場合の使い方もメモしておきます。
「=SUMPRODUCT(B4:B16,C4:C6)」と２つの範囲を指定します。
答えは「B4*C4+B5*C5+B6*C6」の値となります。