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

arrayformula-sum

SPONSORED LINK

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

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

あ、あれ、?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)で習いましたね!

行列積の復習

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

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

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

内積を定義出来る条件

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

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』関数を使って表示速度を高速化させよう!

2017.12.30

SPONSORED LINK

arrayformula-sum

Time Ticketはじめました
実務で即戦力になるためのスプレッドシート講座開講!

記事を読んでも、よく分からなかった人やもっと深く知りたい人はこちらからご連絡ください。

ブログ特典!!

『クーポンコード:9876』とメッセージいただければ、どのレベルのコースでも初級コースと同じ価格で教えます!!

スプレッドシートをより深く学んで、実務で即戦力になりませんか?

ABOUTこの記事を書いてる人

たぬ(谷口健太)

25歳社会人。本職は福岡の通販会社でWebマーケッターやっとります。 プログラミング✕マーケティング=最強説を唱えており、非効率的な世の中に革命を起こそうと本ブログを設立。 ストレングスファインダー:戦略性・最上志向・目標志向・未来志向・個別化

NEW POSTこのブログの最新記事