エクセルで3列おきの合計・SUMPRODUCT関数、MOD関数、COLUMN関数

2024-07-21

横方向の集計で、合計する項目が飛んでいる場合の集計方法です。

3列おきの例で説明します。

サンプル

項目1、項目２、項目３が横方向にあり、最終列にそれぞれの合計を表示したい。

関数の組み合わせで簡単に集計できます。

項目1の計算式（セルG2）に入力式の例です。

集計範囲「$A2:$F2」に「＄」を入れているのは、横方向に計算式をコピーしたとき、範囲がずれないためです。

「＄」を入れないで計算式を横方向へコピーした例。

範囲がずれます。

項目１：セルG2

=SUMPRODUCT((MOD(COLUMN(A2:F2),3)=1)*(A2:F2))

項目2：セルG2

=SUMPRODUCT((MOD(COLUMN(A2:F2),3)=2)*(A2:F2))

項目3：セルI2

=SUMPRODUCT((MOD(COLUMN(A2:F2),3)=0)*(A2:F2))

違う個所は、「=SUMPRODUCT((MOD(COLUMN(A2:F2),3)=」の後の数字です。

計算式で使うのは、SUMPRODUCT関数、MOD関数、COLUMN関数です。

この関数は、割った数のあまりを返します。

MOD(数値, 除数)

わかりやすいように3行目に列番号を入力しました。セルA4にMOD関数を入力し、横方向へコピーしました。

1列目「1」を3で割ったあまりは「1」です。2列目：2/3のあまりは「2」、3列目：3/3のあまり「0」、4列目以降も同じく計算すると、

※この例の場合はデータが1列目から始まっているので上記の計算結果です。

COLUMN関数は、列番号を返します。上記のMOD関数で列番号の数値を指定した個所を、COLUMN関数に置き換えます。

SUMPRODUCT関数で条件にあったデータの合計を返せます。SUMPRODUCT関数はこの例以外の使い方がありますが、ここでは省略します。

SUMPRODUCT関数、MOD関数、COLUMN関数を組み合わせて、3列おきの合計を返します。

=SUMPRODUCT((MOD(COLUMN(A2:F2),3)=1)*(A2:F2))

SUMPRODUCT関数の後の、「(MOD(COLUMN(A2:F2),3)=1)」で1列目を指定し、その後の式「(A2:F2)」で集計範囲を指定します。