【検証】スプレッドシートの高速化にARRAYFORMULAは本当に有効か？

Googleスプレッドシートはデータ量が多くなればなるほど動作速度が遅くなったりしてしまう傾向がありますが、工夫をすることで高速化を実現できる可能性は残されています。

今回の記事では、スプレッドシートを高速化する方法の一つとして紹介されることが多いARRAYFORMULA関数について、本当に高速化の一助となっているかという事の検証を行いたいと思います。

ARRAYFORMULA関数とは？

そもそもARRAYFORMULA関数ってなんだ？という方もいらっしゃると思いますので、簡単に関数の説明をさせていただきます。ARRAYFORMULA関数とは配列関数と呼ばれる関数の1つで、1つの関数で複数セルに計算結果を出力ができる関数の事になります。

ARRAYFORMULA関数を活用することによって、今まで各セルに関数を入れていたところが1つのセルに関数を入れれば同じ計算結果を返してくれるというとても便利そうな関数になります。

例えば・・・

A	B	C
1	2	=A1+B1
1	2	=A2+B2
1	2	=A3+B3

このようなスプレッドシートがあった場合に、ARRAYFORMULA関数を使えば下記のように置き換えることができます。

A	B	C
1	2	=ARRAYFORMULA(A1:A3+B1:B3)
1	2
1	2

上記2つの出力結果はどちらも同じものになります。ARRAYFORMULA関数を利用するとC1に関数を記述するだけでC2とC3にも計算結果が出力されるというようなMicrosoftのExcelにはないスプレッドシート独自の関数になります。

最新版のExcelでは、スピルと呼ばれる関数で似たようなことができるようになったみたいですね。Excelは久しく使っていないので、詳しくはよくわかりませんが、日々進化しているのでしょう。

今回の記事では、ARRAYFORMULA関数って実際のところ高速になるの！？を検証してみたいと思います。

どっちが処理が速いのか検証してみる

通常の関数とARRAYFORMULA関数、どちらが動作速度が早いのかを比較するためにスプレッドシートを2つ用意しました。

ARRAYFORMULA関数を使っていないスプレッドシート

ARRAYFORMULA関数を使っているスプレッドシート

すでに大量のデータが入っているので、ページを開いてからの画面の処理が安定するまではどちらもかなり長いかと思いますが、右上のプログレスバーが全部溜まったら操作をしてみてください。

どちらもA列とB列に数字を入れてC列では同じ行のA列とB列を足し算する関数を入れてあります。行数が371,000もあるので、最初の処理が重いです・・・。

A列とB列は編集可能になっていますので何行目でも良いので、適当な数字を入れてみて下さい。（数字以外を入力しようとすると入力規則が違うとのダイアログが表示されて入力できませんので、1〜371000までの数値を入れてみて下さい）

数字を変更してからC列の値が実際に再計算されるまでどのくらいの時間がかかるのかそれぞれのスプレッドシートで体験してみましょう。ページを開いたときの処理にかなり時間はかかるのですが、一度スプレッドシートが落ち着いたあとに数字を変更してみると処理時間の違いが歴然です。

検証結果

結果はいかがでしたでしょうか？
スプレッドシートの高速化ということで注目されるARRAYFORMULA関数ですが、体感して頂いた通り、超大量のデータが入っているファイルの場合は逆に処理がかなり遅くなります。実際にはデータがそれほど入っていないスプレッドシートの場合でも処理は遅くなっていると思うのですが、体感できない範囲なので気づくことができません。

私なりの遅延理由の考察は次の通りです。ARRAYFORMULA関数のような配列関数と呼ばれる関数は1つの関数で複数行に結果を返します。そのため、1箇所変更するだけでも対象の範囲のすべての計算を再度行います。そのため、時間がかなりかかるものと考えられます。一方、通常の関数の場合は変更された箇所のみ再計算するので、膨大な関数があったとしても変更時の処理が1箇所の再計算で済むため結果として処理速度が早くなるのです。（公式見解ではないので、あくまでも私の考察です）

それほどデータが多くない場合は配列関数は有効な手段だと思いますが、データ量が増える場合はあまり使わないほうが良いと思います。

ただし、配列関数には関数の変更がある場合に1箇所の変更で済むという利点もあります。適材適所で関数を選択できればいいですね。

まとめ

今回は、ARRAYFORMULA関数ってホントに早いの！？という疑問を解消するべく記事にしてみました。盲目的にブログに書いてあることを信じないで、自分で検証してみるのも大事かもしれませんね。インターネットには情報が溢れているからこそ、その情報が正しいかどうかを判断できるようになる必要があると思います。
皆さんのスプレッドシートがより快適なものになりますように。