- 表のデータを1行おきに合計したい
- SUM関数を使う方法では時間がかかる
- 項目名がないからSUMIF関数が使えない…
そんなあなたのために、「SUMPROCUCT関数を使って1行おきに合計する方法」についてご説明します。
前回の記事では、下記の表で1行おきに合計を出す方法として「SUMIF関数」をご紹介しました。
「数量」だけを合計、「売上」だけを合計といったように、指定した条件だけを合計する方法です。
表のデータを1行おきに合計したい SUM関数を使う方法では時間がかかる… そんなあなたのために、「SUMIF関数を使って1行おきに合計する方法」についてご説明します。 合計したいデータの項目名を利用[…]
しかし、下図のように「数量」や「売上」などの条件となる項目名のようなものがなく、「上段」と「下段」それぞれの合計をだしたい場合もありますよね。
今回は、条件となる項目名がないためSUMIF関数は使えません。
さてどうしましょうか…。
結論
正直なところ、今からご説明する内容は初心者の方にとっては少し難しいと思います。
順を追ってわかりやすくご説明していこうと思いますが、そんな時間ないよーとか、先に答え教えてーという方は、まずこちらをどうぞ。
SUMPRODUCT関数を使用します。
- 奇数行の合計
=SUMPRODUCT((範囲)*(MOD(ROW(範囲),2)=1))
- 偶数行の合計
=SUMPRODUCT((範囲)*(MOD(ROW(範囲),2)=0))
コピペしてどうぞお使いください♪
上記の式を理解したい！SUMPRODUCT関数ってどんな関数なのか知りたい！という素晴らしい意識の持ち主の方…しばしこれからお付き合いください。
SUMPRODUCT関数とは
SUMPRODUCT関数は、範囲の同じ行どうしを掛け算して合計を出す関数です。
「サムプロダクト」と読みます。
どんな風に使うかを説明しますね。
下記の表で合計金額を出したい場合。
- 「単価」と「数量」を掛け算して各商品の「金額」を出す
- SUM関数を使って「金額」の合計を出す
上記の2ステップで出すことが多いですよね。
SUMPRODUCT関数を使えば「金額」欄が不要となり、1ステップで出すことができるのです。
- SUMPRODUCT関数で「単価」と「数量」の範囲の同じ行どうしを掛け算して合計
これを活用して1行おきの合計を出します。
といっても、一体これをどう活用すれば1行おきの合計が出せるのか…。
1行おきの合計＝奇数行の合計、偶数行の合計
「1行おき」というのをどう考えるかが一つ目のポイントです。
そこで、合計対象となるセル番地の「行番号」に注目します。
上段（黄）の合計を出したい場合
黄の行番号はセルB3、B5、B7…と「奇数」であるため、奇数行のデータの合計を出したいということになります。
下段（白）の合計を出したい場合
白の行番号はセルB4、B6、B8…と「偶数」なので、偶数行のデータの合計を出したいということになりますね。
1行おきに合計を出すということは、
- 奇数行の合計
- 偶数行の合計
それぞれを出すということになるわけです。
1 と 0 を掛け算
次に、どのようにして奇数行の合計、偶数行の合計を出すのかを説明します。
奇数行の合計
奇数行の合計＝奇数行のデータに「１」、偶数行のデータに「０」をそれぞれ掛け算した合計
です。
「１」を掛けたらそのままのデータ、「０」を掛けたら０となります。
こういうことです↓
偶数行の合計
偶数行の合計＝偶数行のデータに「１」、奇数行のデータに「０」をそれぞれ掛け算した合計
です。
同じく、こういうことですね↓
ここまでOKですか？
奇数行と偶数行
奇数行なのか偶数行なのかは、行番号を2で割った余りで判断します。
- 行番号を2で割った余りが１＝奇数行
- 行番号を2で割った余りが０＝偶数行
奇数行
- 奇数行＝行番号を2で割った余りが１
これを式で表すと→ MOD(ROW(セル番地),2)=1
偶数行
- 偶数行＝行番号を2で割った余りが0
これを式で表すと→ MOD(ROW(セル番地),2)=0
行番号を出すROW関数、割り算したあまりを出すMOD関数を使います。
奇数行なら１、偶数行なら０とするには
- MOD(ROW(セル番地),2)=1
この結果は
- 奇数行ならTrue＝１
- 偶数行ならFalse＝０
となりますね。
偶数行なら１、奇数行なら０とするには
- MOD(ROW(セル番地),2)=0
この結果は
- 偶数行ならTrue＝１
- 奇数行ならFalse＝０
となりますね。
TrueとFalseを 数値で表すと、True＝１、False＝０ です。
いよいよSUMPRODUCT関数でまとめます。
SUMPRODUCT関数で１行おきに合計する
SUMPRODUCT関数は、範囲の同じ行どうしを掛け算して合計を出す関数でしたね。
今回の場合は、（各行のデータ）×（１または０）と掛け合わせた結果の合計をだします。
では、上段合計（奇数行の合計）からいきましょう。
セルB16に以下の式を入力します。（手入力が良いです）
=SUMPRODUCT((B3:B14)*(MOD(ROW(B3:B14),2)=1))
続いて、下段合計（偶数行の合計）は、セルB17に以下の式を入力します。
=SUMPRODUCT((B3:B14)*(MOD(ROW(B3:B14),2)=0))
長くなりましたが、これで完成です。
True＝１、False＝０は、Excelではよく使う手法ですので慣れておくと良いですよ。
これが使えれば、あなたもExcel上級者です♡ ばんざーい＼(^o^)／
＼１世代前の型落ち品を特別特価にてご提供！／
別表のデータを1行おきに参照（リンク）させたい オートフィルを使ってみたけどうまくいかない… そんなあなたのために、「1行おきにセル参照する方法」についてご説明します。 先頭に「’」を付け文字列とし[…]
1行おきに空白の行を入れたい たくさん行があるから1行ずつ挿入していくのは大変… そんなあなたのために、「効率良く1行おきに空白行を挿入する方法」についてご説明します。 下図のように、1行ずつ空白行を挿入したい…そ[…]
表のデータを1行おきに合計したい SUM関数を使う方法では時間がかかる… そんなあなたのために、「SUMIF関数を使って1行おきに合計する方法」についてご説明します。 合計したいデータの項目名を利用[…]