Excelで 行を一行おきに合計したい、つまり奇数行または偶数行のみの値を足し合わせたい件
この問題をググってみると sumproduct 関数を使え、ということらしい。 あちこち見たのですが、結局一番わかりやすかったのが オフィシャルページの説明 SUMPRODUCT function です。
このエントリーではではエクセル関数を小文字で書いています。
結論
たとえば B1:B5 の範囲で奇数行の値だけを足し合わせたい場合:
=sumproduct( (B1:B5)*(mod(row(B1:B5),2)=1) )
で計算できる。
なにこれ？どうやら 関数A( リストL * 関数B ) という構造になっている気がする。
実際 sumproduct の説明を読むと * の代わりに別のオペレータ... たとえば + なども使えるとある。
関数A( リストL + 関数B ) とかも計算できるということ。
JavaScript で言えば map して reduce しているだけの話だった。
listL.map( (it)=> it*functionB() ).reduce( (a,b)=>a+b )とかlistL.map( (it)=> it+functionB() ).reduce( (a,b)=>a+b )
ならば、 sum でも同じことができるのか？つまり:
=sum( (B1:B5)*(mod(row(B1:B5),2)=1) )
これがいけるのかと言えば、まったくこれは作動しない。そういう話ではない、らしい。 こういうエクセルの一貫性にかけるところがイライラしかしない。
だが、しかし、sumproduct 関数は便利なので、どのように作動するのか順を追って想像しつつこの関数の理解を深めてみる。
sum
=sum( B1:B5 )
sumproduct の前に基本の sum 関数を見る。
このように、 =sum( 範囲 ) とすることでその範囲にある値を合計してくれる。
sumproduct (1)
=sumproduct( (B1:B5)*1 )
sumpproduct の一番の基本的な使い方。
=sumproduct( 範囲 * 1 ) とすることで、=sum( 範囲 ) と同じ結果が得られる。
この処理で内部で行われているのは、以下のような計算だと思う。
- B1 の値( 1 ) に *1 する→ 1
- B2 の値( 2 ) に *1 する→ 2
- B3 の値( 3 ) に *1 する→3
- B4 の値( 4 ) に *1 する→4
- B5 の値( 5 ) に *1 する→5
- 1 から 5 までの結果を合計する → (1+2+3+4+5=) 15
JavaScript で書けば以下と同じ:
[1,2,3,4,5].map( (it)=> it*1 ).reduce( (a,b)=>a+b )
だったら
[1,2,3,4,5].map( (it)=> it+0 ).reduce( (a,b)=>a+b )しても同じなのだから、つまり Excelでは=sumproduct( (B1:B5)+0 )と書いても同じ結果になる。
sumproduct (2)
=sumproduct( (B1:B5)+2 )
今度はオペレータを + にして 1 を 2 にしてみた。 つまり、B1:B5 の範囲にある値を 順に +2 した上で、それらを合計する。
- B1 の値( 1 ) に +2 する→3
- B2 の値( 2 ) に +2 する→4
- B3 の値( 3 ) に +2 する→5
- B4 の値( 4 ) に +2 する→6
- B5 の値( 5 ) に +2 する→7
- 1 から 5 までの結果を合計する → (3+4+5+6+7=) 25
JavaScript で書けば以下と同じ:
[1,2,3,4,5].map( (it)=> it+2 ).reduce( (a,b)=>a+b )
sumproduct (3) 偶数行の値のみの合計
=sumproduct( (B1:B5)*(mod(row(B1:B5),2)=0) )
オペレータを * に戻して、いよいよ本題の一行おきに足す方法です。
- row は行を評価する関数。引数で与えられたアドレスの行の値を返す関数です。たとえば row(A1) とすれば 1 になります。（一行目だから）
- mod は、２つの引数をとり、最初の引数を後の引数で割ったあまりです。
mod(100,2)とすれば、100 / 2 の計算をして余りが 0 なので、mod(100,2) の値は 0 になります。
つまり、回りくどく説明しているが、後の引数を 2 に固定すれば mod(数値,2) 最初の引数に与えた数が奇数か偶数かを 0,1 で判定する関数です。
さらにこれを:
mod(row(B1),2)=0
とすれば、これは B1 の行数(1) を2で割った余りが 0 かどうか評価しているので結果は false です。
なぜなら mod(row(B1),2) の評価結果は 1 であって 0 ではないから。
Excel は false は 0, true は 1 として扱うようなので:
(B1) * (mod(row(B1),2)=0)
B1(のセルの値は 1) でそれに false(つまり 0 として扱われる) をかけることになるので、結果は 0 です。
続いて B2 は:
(B2) * (mod(row(B2),2)=0)
B2（のセルの値2) でそれに true(つまり 1 として扱われる) をかけるので、結果は 2 です。
あとはこれが範囲指定した分繰り返されることになる:
(B1:B5) * (mod(row(B1:B5),2)=0)
つまり、 奇数行では 0 をかけ、偶数行では 1 をかけるを繰り返す ため、結果的に偶数行だけが足し合わされることなる。
JavaScript で書けば以下と同じ ( javascript は 0-index 方式で excel は 1-index 方式である点に注意 ):
[1,2,3,4,5].map( (it,index)=> it*((index+1)%2==0 ? 1:0) ).reduce( (a,b)=>a+b )
sumproduct (4) 奇数行の値のみの合計
=sumproduct( (B1:B5)*(mod(row(B1:B5),2)=1) )
偶数行の反対の処理です。
偶数行を足し合わせるときは mod(row(B1),2)=0 としていたところを今度は mod(row(B1),2)=1 としているので、結果として奇数行だけが足し合わされる。
だから、奇数行のときは:
(B1:B5) * (mod(row(B1:B5),2)
と書いても同じ結果が得られる。
偶数行と奇数行とで対称性がないコードになるから、逆にわかりづらくなるとは思う。
JavaScript で書けば以下と同じ ( javascript は 0-index 方式で excel は 1-index 方式である点に注意 ):
[1,2,3,4,5].map( (it,index)=> it*((index+1)%2==1 ? 1:0) ).reduce( (a,b)=>a+b )
または
[1,2,3,4,5].map( (it,index)=> it*((index+1)%2==0 ? 0:1) ).reduce( (a,b)=>a+b )
まとめ
sumproduct すごく便利だけど、人に説明するのが大変。 そういう向きには sumproduct を説明するより、参照式を使って、別セルに以下の関数を入れる列をつくるやり方を説明した方がいいかも。
=if(mod(row(B1),2)=0, B1, 0)
=if(mod(row(B2),2)=0, B2, 0)
=if(mod(row(B3),2)=0, B3, 0)
こうやってつくった列を sum を使って合計するという二段構えで。