Excel 1行おきに合計する方法

2020年5月21日

1行おきに合計したい場合、データの少ない表であれば1行づつ開けて選択すればよいですが

表が大きくなってくると作業が大変になり間違いも発生しやすくなります。

この記事では1行おきの計算を効率的に実現する方法を紹介します。

手順

手順は二つあります。

手順説明
中間計算セルを作成基本的にはこちらを推奨します。
合計セルのみで完結(配列数式中間計算セルを作成したくない場合は
こちらの方法をとります。
配列数式はメリットが多い分、
デメリットも多いので注意が必要です。

このような表で売上・コストのみを合計する例で考えます。

サンプル表

中間計算セルを作成

基本的にはこの方法を推奨します。

MOD関数ROW関数を使用し、下記の数式の中間計算セルを作成します。

=MOD(ROW(),2)

下のキャプチャは関数をセルで分けていますが、F列が同じ結果になります。

ROW関数で行数を出し、2を割ることで売上の行が0、コストの行が1になります。

ROW関数とMOD関数の結果

これをSUMIF関数で合計します。

=SUMIF(中間計算セルの範囲,0,合計する数値の範囲)
SUMIF関数で売上だけを合計

売上合計の数式の0を1に変えればコスト合計の式になります。

=SUMIF(中間計算セルの範囲,1,合計する数値の範囲)

この例では全ての数値を合計してから売上合計を引いても正解です。

SUMIF関数でコストだけを合計

合計セルのみで完結(配列数式)

何らかの理由で中間計算セルを作りたくない場合に使用します。

売上合計のセルにSUM関数IF関数MOD関数ROW関数を使用し

下記のように数式を入力します。

=SUM(IF(MOD(ROW(数値の範囲),2)=0,数値の範囲,0))

そして数式を確定させるときには[Ctrl]+[Shift]+[Enter]キーで確定します。

スピルに対応しているバージョンではEnterだけで問題ありません)

配列数式で売上合計を出す例

数式が{}で囲まれていれば成功です。

数値の範囲の各行でROW関数MOD関数の判定が発生し、その結果を合計しています。

同様に0の部分を1に変えればコスト合計になります。

=SUM(IF(MOD(ROW(数値の範囲),2)=1,数値の範囲,0))
配列数式でコスト合計を出す例

配列数式は強力な分のデメリットも多いのでご注意ください。(詳細はこちら

補足(1行おきでない場合の応用)

1行づつの場合はMOD関数で2で割っていますが

他の数字にすれば間隔を変えることが可能です。

3で割れば2行おきに合計が可能ですし比較演算子OR関数などで条件を調整すれば

余りが0と2の時は集計に含め、1の時は除外といった柔軟な設定が可能です。