ARRAYFORMULA関数とSUM関数を組み合わせる2つの裏技

こんにちは、スプレッドシートが大好きなたぬ(@tanuhack)です!

この記事をご覧になっている皆さんは、きっと次のような悩みがあると思います。

あ、あれ、?ARRAYFORMULA関数とSUM関数を組み合わせたいけど、思うようにARRAYFORMULAが動かない…!
arrayformula-sum1
ARRAYFORMULA関数を使って、1行ずつの和を最終行まで実行したかったけど出来なかった例

それもそのハズ。SUMは範囲を集約して、その合計を求める関数なので、ARRAYFORMULA関数と相性が悪いのです。

結論から言うと、ARRAYFORMULA関数とSUM関数を組み合わせる方法はありません。

なので、SUM関数の代わりになるもので代用します。

そこで今回は、SUM関数を使わずに、ARRAYFORMULA関数とSUM関数で実現したいことを実現させる方法を2つ紹介します。

裏技1:算術演算子+を使う

初心に戻ります。SUM関数は足し算を実行する関数でしたね。

つまり、SUM関数で足し算をせず、算術演算子『+』を使って、ゴリゴリに計算します。

arrayformula-sum2
=ArrayFormula(B1:B15+C1:C15+D1:D15+E1:E15+F1:F15)

今回の例では、B列からF列までの和を15行にかけて足算をしているので、そこまで足し算を記述することは大変ではありません。

ですが、もし範囲がB列からF列までじゃなくて、もっと広い場合大変ですよね。(例えば、B列からZ列までなど。)

これでも目的の動きが出来てるからOKという人もいると思いますが、僕はわざわざ、たったこれだけの情報量で記事にしません、笑

次に紹介する裏技が本題です。それではどうぞご覧ください。

裏技2:行列積を計算するMMULT関数を使う

行列積』という言葉ってどこかで聞いたことありませんか?

高校の数学(数C)で習いましたね!

行列積の復習

行列積は別名、内積(ないせき)といいます。

内積は、行ベクトル列ベクトルを掛けて求めることが出来ます。

[aside]補足
ベクトルの成分を横に並べたものを行ベクトルといい、縦に並べたものを列ベクトルと言います。[/aside]

内積を定義出来る条件

行ベクトルの成分数と列ベクトルの成分数が等しい場合、内積を求めることが出来ます。

arrayformula-sum3

内積の大きさ

①m行n列と②n行p列の内積は『m行p列』です。

つまり、①のn列と②のn行が打ち消されて、『m行p列』になります。

arrayformula-sum4

今回は、1行1列の計算結果が欲しいので、行ベクトルに列ベクトルを掛けます。

MMULT関数を使って、行列積を求める

MMULT関数の使い方
=MMULT(行列A,行列B)

STEP1:1行の内積を計算してみる

まず、MMULT関数の動作確認をするために1行だけ計算してみます。

A1セルに内積が表示されていることを確認してください。

arrayformula-sum5

数式
=MMULT(B1:F1,H1:H5)
arrayformula-sum6
行列の計算結果

STEP2:すべての行の内積を計算してみる

arrayformula-sum7

数式
=MMULT(B1:F15,H1:H5)
arrayformula-sum8
行列の計算結果

これでやりたかったことが出来ました。

ですが、H列に列ベクトルを入れて計算しているのが不格好なので、列ベクトルを入れずともARRAYFORMULA関数を使って自動で計算出来るようにしてみましょう。

arrayformula-sum9

ARRAYFORMULA関数とMMULT関数を組み合わせる

STEP1:ARRAYFORMULA関数で、計算する列数の配列を作成する

この際、選択する行はどこでも大丈夫です。

B列からF列、つまり5つの成分数を持つ、行ベクトルをARRAYFORMUA関数で作成したいだけなので。

数式
=ArrayFormula(B1:F1)

arrayformula-sum10

STEP2:配列のすべての成分の値を1にする

次に、ARRAYFORMULA関数で展開された数値をすべて0乗して、成分の値をすべて1にします。

数式
=ArrayFormula(B1:F1^0)

arrayformula-sum11

STEP3:TRANSEPOSE関数で、転置行列を作成する

次に、行ベクトルから列ベクトルに転置したいので、TRANSEPOSE関数を使って行と列を入れ替えます。

数式
=TRANSPOSE(ArrayFormula(B1:F1^0))

arrayformula-sum12

これで、先程の列ベクトルが数式だけで作ることが出来ました。

STEP4:目的の数式に組み込ませる

数式
=MMULT(B1:F15,TRANSPOSE(ArrayFormula(B1:F1^0)))

arrayformula-sum13

さいごに

今回は、SUM関数を使わずに、ARRAYFORMULA関数とSUM関数で実現したいことを実現させる方法を2つ紹介しました。

SUM関数とARRAYFORMULA関数を組み合わせるやり方は、ARRAYFORMULA関数を覚えたてのころに避けて通れないと思います。

これからもARRAYFORMULA関数を使って、表示速度爆速の快適なスプレッドシートライフをお過ごしください!

それでは

スプレッドシートの『ARRAYFORMULA』関数を使って表示速度を高速化させよう!