Excel

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

2021-05-15

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

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

3列おきの集計表

サンプル

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

3列おきの集計

SUMPRODUCT関数・MOD関数COLUMN関数で簡単集計

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

計算式の例

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

計算式

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

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

計算式のコピー

範囲がずれます。

項目ごとの計算式

項目1:セル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関数の説明

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

MOD(数値, 除数)

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

MOD関数

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

  • 1列目:1
  • 2列目:2
  • 3列目:0

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

COLUMN関数

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

COLUMN関数

SUMPRODUCT関数

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)」で集計範囲を指定します。

-Excel

© 2021 オフィスのQ&A