はじめまして、BIKKAです。
データ分析に興味をもって今のチームに移ってきて早3ヶ月、楽しく過ごしています。
自己紹介も早々に。
GoogleスプレッドシートのArrayFormula関数、とっても便利ですよね。
私個人としては、スプレッドシートを使い始めて感動したことTop3に入ります。
…しかし最近、「範囲を広げてArrayFormula関数でくくる」というだけでは実現できないことが！
それは複数列・1行ずつの合計値を計算すること。
実は、ArrayFormula関数内でSUM関数を使っても行毎の和を出すことはできないのです。
どうして…どっちもよく使う関数なのに…。
そこで今回は、スプレッドシートで1セルへの入力だけで行毎の和を出す方法を調べてみました。
細かい話はいいから早く結論を！という人はまとめだけ見てね！
ちなみに、ArrayFormula関数の使い方の詳細は以下の記事をぜひ参考に。
どうしてArrayFormula関数内でSUM関数が使えないの？
同じ行のB列からE列の値を合計してF列に表示するケースを考えてみます。
SUM関数でB列からE列を指定し、ArrayFormula関数でくくれば一発では！？と思っていろいろ組んではみるものの、F2にB2からE11全範囲の合計値が返ってくるばかり。
SUM関数はカッコ内に指定したセルの中で取れる最大の範囲を合計してしまいます（今回の場合、上3式いずれもB2からE11）。
ゆえにArrayFormula関数とSUM関数を単純に組み合わせるだけでは行ごとの合計を出すことはできないのです。困った。
【コピペでOK】ArrayFormula関数で行ごとに合計値を出す方法
でも全行に数式をコピペするのは避けたい。そんなとき有効な、ArrayFormula関数を使った3つの数式を紹介します。
１．「+」で各列の値を足す
力技ですが、分かりやすいし一番手っ取り早いのではないかと思います。
以下、数式の解説です。
上記のように、列ごと足しあわせてArrayFormula関数でくくると、B2+C2+D2+E2、B3+C3+D3+E3、…と行ごとに足し算が実行され、F列に結果が表示されます。
２．SUMIF関数を使う
全列を「+」で足すのは、列が増えると大変だしもっとスマートに書きたい…。という場合はこちら。
SUM関数の代わりにSUMIF関数を使えば特定のセルの値だけを足すことができます。
ただ正直、IF関数の中で何が起きているのかよくわからないです…。
SUMIF関数内の2つ目の引数でROW関数を使って条件を指定、3つ目の引数で全範囲を合計範囲として指定しているので、行ごとにB列からE列の同じ行にあるセルだけを抜き出しているようなのですが。
３．行列積を使って各行の和を求める
こちらもトリッキーですが、範囲指定は一番簡単な方法です。
指定するのは合計する値が入っている全範囲だけ。
指定した範囲に空白のセルがあるとエラーが返ってくるので、予め「0」と入力しておくなどの方法で対処してください。
空白セルを無視したい（※2019/07/01追記）
コメントいただいたので確認したところ、以下の関数なら空白セルがあっても実行可能でした！！
N関数で空白のセルを「0」で埋める処理が加わっています。
この式では、B2からE11を行列として扱い（行列①）、行列①とTRANSPOSE関数を使って生成した行列をMMULT関数で掛け合わせた結果がF列に返されます。
TRANSPOSE関数を使って作られるのは
- 1列
- 行列①の列数と同じ数の行をもつ
- 全成分が1である
という3つの性質をもつ行列です（下図参照）。
列ごとの和を求めたい（※2020/02/04追記）
↓のように書けば複数行・1列ずつの合計値も計算可能でした。sawawaさんコメントありがとうございます！
サンプルの表の場合、各教科の全メンバーの点数の合計を出すことができます。
まとめ
今回ご紹介したArrayFormula関数×SUM関数の代替方法をまとめます。
=ArrayFormula(1列目+2列目+3列目+4列目+...)
２．SUMIF関数を使う
=ArrayFormula(SUMIF(IF(COLUMN(1番上の行),ROW(1番左の列)),ROW(1番左の列),全範囲))
３．行列積を使って各行の和を求める
=MMULT(全範囲,TRANSPOSE(ARRAYFORMULA(COLUMN(全範囲)^0)))
※空白セルがある場合は以下ご利用ください。
=ArrayFormula(MMULT(N(全範囲),N(TRANSPOSE(COLUMN(全範囲)^0))))
この中に1つでもお役に立つものがあれば幸いです…が、「どれもちょっと複雑だな」というのが素直な感想。
たかだか行毎の和を求めるのに、ここまで複雑なことをする必要があるのかどうかは疑問です。
身も蓋もない話になってしまいますが、全行にSUM関数をコピペするというのも一つの手のような気がします。
＼\一緒に『明日のはたらくを創る』仲間を募集中です!! /／
919.jp
