株式会社クイックのWebサービス開発blog

HAPPYなサービスプランナー・エンジニア・デザイナーのブログです。

ArrayFormula関数内でSUM関数を使いたい!│Googleスプレッドシート

はじめまして、BIKKAです。
データ分析に興味をもって今のチームに移ってきて早3ヶ月、楽しく過ごしています。

自己紹介も早々に。
GoogleスプレッドシートのArrayFormula関数、とっても便利ですよね。
私個人としては、スプレッドシートを使い始めて感動したことTop3に入ります。

…しかし最近、「範囲を広げてArrayFormula関数でくくる」というだけでは実現できないことが!
それは複数列・1行ずつの合計値を計算すること。
実は、ArrayFormula関数内でSUM関数を使っても行毎の和を出すことはできないのです。
どうして…どっちもよく使う関数なのに…。

そこで今回は、スプレッドシートで1セルへの入力だけで行毎の和を出す方法を調べてみました。
細かい話はいいから早く結論を!という人はまとめだけ見てね!

ちなみに、ArrayFormula関数の使い方の詳細は以下の記事をぜひ参考に。

aimstogeek.hatenablog.com




どうしてArrayFormula関数内でSUM関数が使えないの?

同じ行のB列からE列の値を合計してF列に表示するケースを考えてみます。

f:id:aimstogeek:20190228102821p:plain
各教科の合計点をF列に表示したい

SUM関数でB列からE列を指定し、ArrayFormula関数でくくれば一発では!?と思っていろいろ組んではみるものの、F2にB2からE11全範囲の合計値が返ってくるばかり。
f:id:aimstogeek:20190228105045p:plain
f:id:aimstogeek:20190228105200p:plain
f:id:aimstogeek:20190228105222p:plain
f:id:aimstogeek:20190228154454p:plain
ArrayFormula関数×SUM関数の結果。撃沈

SUM関数はカッコ内に指定したセルの中で取れる最大の範囲を合計してしまいます(今回の場合、上3式いずれもB2からE11)。
ゆえにArrayFormula関数とSUM関数を単純に組み合わせるだけでは行ごとの合計を出すことはできないのです。困った。

【コピペでOK】ArrayFormula関数で行ごとに合計値を出す方法

でも全行に数式をコピペするのは避けたい。そんなとき有効な、ArrayFormula関数を使った3つの数式を紹介します。

1.「+」で各列の値を足す

力技ですが、分かりやすいし一番手っ取り早いのではないかと思います。
ArrayFormula(B2:B11+C2:C11+D2:D11+E2:E11)
以下、数式の解説です。

=ArrayFormula(1列目+2列目+3列目+4列目+...)


上記のように、列ごと足しあわせてArrayFormula関数でくくると、B2+C2+D2+E2、B3+C3+D3+E3、…と行ごとに足し算が実行され、F列に結果が表示されます。

2.SUMIF関数を使う

全列を「+」で足すのは、列が増えると大変だしもっとスマートに書きたい…。という場合はこちら。
ArrayFormula(SUMIF(IF(COLUMN(B2:E2),ROW(B2:B11)),ROW(B2:B11),B2:E11))

=ArrayFormula(SUMIF(IF(COLUMN(1番上の行),ROW(1番左の列)),ROW(1番左の列),全範囲))


SUM関数の代わりにSUMIF関数を使えば特定のセルの値だけを足すことができます。
ただ正直、IF関数の中で何が起きているのかよくわからないです…。
SUMIF関数内の2つ目の引数でROW関数を使って条件を指定、3つ目の引数で全範囲を合計範囲として指定しているので、行ごとにB列からE列の同じ行にあるセルだけを抜き出しているようなのですが。

3.行列積を使って各行の和を求める

こちらもトリッキーですが、範囲指定は一番簡単な方法です。
MMULT(B2:E11,TRANSPOSE(ARRAYFORMULA(COLUMN(B2:E11)0)))
指定するのは合計する値が入っている全範囲だけ。

=MMULT(全範囲,TRANSPOSE(ARRAYFORMULA(COLUMN(全範囲)^0)))


指定した範囲に空白のセルがあるとエラーが返ってくるので、予め「0」と入力しておくなどの方法で対処してください。

※2019/07/01追記
コメントいただいたので確認したところ、以下の関数なら空白セルがあっても実行可能でした!!
N関数で空白のセルを「0」で埋める処理が加わっています。

=ArrayFormula(MMULT(N(全範囲),N(TRANSPOSE(COLUMN(全範囲)^0))))



この式では、B2からE11を行列として扱い(行列①)、行列①とTRANSPOSE関数を使って生成した行列をMMULT関数で掛け合わせた結果がF列に返されます。
TRANSPOSE関数を使って作られるのは

  • 1列
  • 行列①数と同じ数のをもつ
  • 全成分が1である

という3つの性質をもつ行列です(下図参照)。

f:id:aimstogeek:20190301112237p:plain
行列計算の解説

まとめ

今回ご紹介したArrayFormula関数×SUM関数の代替方法をまとめます。

1.「+」で各列の値を足す
=ArrayFormula(1列目+2列目+3列目+4列目+...)
2.SUMIF関数を使う
=ArrayFormula(SUMIF(IF(COLUMN(1番上の行),ROW(1番左の列)),ROW(1番左の列),全範囲))
3.行列積を使って各行の和を求める
=MMULT(全範囲,TRANSPOSE(ARRAYFORMULA(COLUMN(全範囲)^0)))
※空白セルがある場合は以下ご利用ください。
=ArrayFormula(MMULT(N(全範囲),N(TRANSPOSE(COLUMN(全範囲)^0))))


この中に1つでもお役に立つものがあれば幸いです…が、「どれもちょっと複雑だな」というのが素直な感想。
たかだか行毎の和を求めるのに、ここまで複雑なことをする必要があるのかどうかは疑問です。
身も蓋もない話になってしまいますが、全行にSUM関数をコピペするというのも一つの手のような気がします。



\\一緒に『明日のはたらくを創る』仲間を募集中です!! // 919.jp