自動採番で=ROW()-1は卒業しよう〜ARRAYFORMULAのススメ〜 #GoogleSpreadSheet

はじめに

自動採番する時、1セルずつ丁寧に=ROW()-1を指定していて、正直なところ１セルずつコピーして使うの疲れてきた方、次のステップへ進んでみたくないですか？

今回は主にARRAYFORMULA関数と配列を利用して楽にかつ計算処理を抑えて表示速度を向上させる方法を記事に書いていきます。

この記事で得られるもの

ARRAYFORMULA関数の使い方
表示部の計算高速化の一部手法を身につけられる
拡張性がありメンテナンスしやすい資料が作れるようになる

対象者

GoogleSpreadSheet(Googleスプレッドシート)中級者への道に進みたい人
自動採番(No列)で =ROW()-n関数のみ使っている人
関数の計算を高速化させたい人

ARRAYFORMULA関数の基礎

ARRAYFORMULA関数の特徴としては、以下の2つがあります。

1. 配列を複数行や複数列に渡って表示する
2. 引数に指定した関数で配列を使うことができる

特徴1：配列を複数行や複数列に渡って表示する

例えば、A1からA10のデータを他の列にそのまま表示したい場合。
素直に表示したいセルに=A1:A10を指定したいところですが、これだとA1からA10までの全ての情報が表示されずにA1のセルしか表示されません。

そこで登場するのがARRAYFORMULA関数です！
=ARRAYFORMULA(A1:A10)のように表示したい範囲A1:A10をARRAYFORMULA関数に渡すことで複数行に渡り表示することができます。

この特性を利用すると数式は1つ、表示は複数という状況を作り出すことができます。

例えばA1:A10000に数式が定義されている場合、任意のセルを編集すると再計算が行われ、A1〜A10000のセルを1つ１つ丁寧に計算していくため処理が重くなる事態が発生します。
それが後ほど説明する特徴２を組み合わせることで、今まで各セルで行なっていた関数の計算を1セルで完結することが可能となるので、計算処理を抑えられ結果表示結果を高速化することができるという訳です。

特徴2: 引数に指定した関数で配列を使うことができる

偶数と奇数判定を行うIF文を書くときは=IF(MOD(A1, 2)=0, "偶数", "奇数")のようにMOD関数の引数は特定のセルを指定する前提で動作するような作りになっています。

しかし、ARRAYFORMULA関数を用いることでセル単体でしか使用できなかった関数に範囲指定できるようになります。上記の偶数・奇数判定の数式だと=ARRAYFORMULA(IF(MOD(A1:A10, 2)=0, "偶数", "奇数"))という書き方になりますね。

つまりは単体セルを指定する関数に配列を使えるようになる！ということですね。
凄い便利じゃないか。と思うかもしれないですが逆にMAX関数やMIN関数、SUM関数のように配列の結果を集計する関数については挙動が同じになるので注意が必要になります。

範囲選択の基礎

ARRAYFORMULA関数の実践に入る前に配列の取得方法についておさらいです。

SUMIF関数やVLOOKUP関数を利用するときにA1:A100やA:Bのように指定することで特定範囲の配列を取得できます。これは基本中の基本なので知ってるかと思います。

Question

では、A10:Aと指定すると結果はどうなるのでしょうか。

1. エラーが発生する。
2. A10セルから表示されているA列の最大行までが範囲となる。
3. A1からA10までが範囲となる。

Answer

答えは2のA10セルから表示されているA列の最大行までが範囲となる。です。
「今後データが増えるかもしれないから表示されている最大行は1000行だけどA1:A4000まで指定しておこう」という書き方を時々見ますが、A1:Aと記述することでこの問題は解決できます。
この方法を利用すると拡張性もあり、メンテナンスもしやすいですね。

ARRAYFORMULA関数の実践

次からはよく利用するだろうと思われる項目をピックアップして説明していきます。
ここまで配列という表現をしましたが、行列という表現をしていきます。
実は配列の計算というより、正確にいうと行列の計算になるためです。

もし、今後ARRAYFORMULA関数を利用する機会が増えると使い方を調べるのではなく行列の計算の仕方を調べた方が抱えている課題が解消しやすくなると思います。

自動採番

数式： =ARRAYFORMULA(ROW(A1:A)-1)

いままで説明した内容の復習ですね！
A1セルから表示されている最大行までの範囲に行番号-1の数値を反映する。という意味になります。一度定義してしまえば=ROW()-1を毎回コピーする手間も省くことができますね！！！

なんだか楽しくなってきましたね！（私だけ？）

数値計算

例：３点見積もり法での工数計算 公式→ (悲観値 + 工数 * 4 + 楽観値) / 6
数式： =ARRAYFORMULA((C2:C + B2:B * 4 + D2:D)/6)

３点見積もり法での計算も１セルだけで計算すればよくなるので楽になりますね。
でも、ちょっと待ってください。範囲選択同士を計算するってどういうこと？と疑問に思う方もいますね。

ここで登場するのが行列の計算です。
と言っても今回は4行1列だけなので計算は簡単になりますね。

\begin{pmatrix} 
\begin{pmatrix} 20  + 10 * 4 + 7 * \frac{1}{6} \end{pmatrix} \\
\begin{pmatrix} 6  + 3 * 4 + 1 * \frac{1}{6} \end{pmatrix} \\
\begin{pmatrix} 10  + 3 * 4 + 2 * \frac{1}{6} \end{pmatrix} \\
\begin{pmatrix} 25  + 15 * 4 + 12 * \frac{1}{6} \end{pmatrix} \\
\begin{pmatrix} 25  + 15 * 4 + 12 * \frac{1}{6} \end{pmatrix} \\
\end{pmatrix}
=
\begin{pmatrix} 
11.16666... \\ 
3.16666... \\
4 \\
16.16666... \\
16.16666...
\end{pmatrix}

この行列の計算結果がそのままセルに反映される形となります。
今後、A1:Bなどの指定でn行2列の場合の計算などをしたい場合に「行列」で計算するという仕組みを理解するだけでも次のステップを踏めるのではないでしょうか。

IF文のAND計算

次は先ほどの３点見積もり計算でB列、C列、D列に値が入っていない場合は計算せずに空白として出力するような条件を追加するパターンについてです。

数式：

=ARRAYFORMULA(IF(
  ISBLANK(C2:C) * ISBLANK(B2:B) * ISBLANK(D2:D), 
  "",
  (C2:C + B2:B * 4 + D2:D)/6)
)

AND計算は同じく行列での計算を行います。関数のANDは利用できない点に注意です。
条件を*で計算することでANDが実現可能になります。

ISBLANK関数は引数の値をチェックして空白ならTRUE、それ以外ならFLASEを返します。
プログラミングの世界ではTRUE, FLASEはそれぞれ1と0で表せることを利用して計算を行います。

ISBLANK(C2:C)のISBLANKの計算結果の行列を数値に変換すると以下のようになります。

\begin{pmatrix}
作業名A: FLASE \\
作業名B: FLASE \\
作業名C: FLASE \\
作業名D: FLASE \\
作業名E: FLASE \\
作業名F: TRUE \\
.\\.\\.\\
作業名N: TRUE
\end{pmatrix}
=
\begin{pmatrix}
0 \\
0 \\
0 \\
0 \\
0 \\
1 \\
.\\.\\.\\
1
\end{pmatrix}

同様にISBLANK(B2:B)とISBLANK(D2:D)を数値に変換して計算してみます。
計算する数式： ISBLANK(C2:C) * ISBLANK(B2:B) * ISBLANK(D2:D)

\begin{pmatrix}
作業名A: 0 \\
作業名B: 0 \\
作業名C: 0 \\
作業名D: 0 \\
作業名E: 1 \\
作業名F: 1 \\
.\\.\\.\\
作業名N: 1
\end{pmatrix}
*
\begin{pmatrix} 0 \\ 0 \\ 0 \\ 0 \\ 0 \\ 1 \\ .\\ .\\ .\\ 1 \\\end{pmatrix}
*
\begin{pmatrix} 0 \\ 0 \\ 0 \\ 0 \\ 1 \\ 1 \\ .\\ .\\ .\\ 1 \\\end{pmatrix}
=
\begin{pmatrix} 0(FALSE) \\ 0(FALSE) \\ 0(FALSE) \\ 0(FALSE) \\ 0(FALSE) \\ 1(TRUE) \\ .\\ .\\ .\\ 1(TRUE) \\\end{pmatrix}
=
\begin{pmatrix}
作業名A: データあり \\
作業名B: データあり \\
作業名C: データあり \\
作業名D: データあり \\
作業名E: データあり \\
作業名F: データなし \\
.\\.\\.\\
作業名N: データなし
\end{pmatrix}

上記の表現をすると判定の流れが見えるので分かりやすいですね。
通常の考え方は行列ですが、判定での計算は0と1の計算はビット計算になるのがポイントになります。
行列のAND計算は全て*で連結させる！！！
これは覚えておきましょう。

IF文のOR計算

AND計算が分かったら次はORについでです。

数式：

=ARRAYFORMULA(IF(
  ISBLANK(C2:C) + ISBLANK(B2:B) + ISBLANK(D2:D), 
  "",
  (C2:C + B2:B * 4 + D2:D)/6)
)

OR計算も同じく行列での計算を行います。関数のORは利用できない点に注意です。
条件を+で計算することでORが実現可能になります。同じくビット計算になります。

ANDと同じ容量で計算をしてみましょう。

\begin{pmatrix}
作業名A: 0 \\
作業名B: 0 \\
作業名C: 0 \\
作業名D: 0 \\
作業名E: 1 \\
作業名F: 1 \\
.\\.\\.\\
作業名N: 1
\end{pmatrix}
+
\begin{pmatrix} 0 \\ 0 \\ 0 \\ 0 \\ 0 \\ 1 \\ .\\ .\\ .\\ 1 \\\end{pmatrix}
+
\begin{pmatrix} 0 \\ 0 \\ 0 \\ 0 \\ 1 \\ 1 \\ .\\ .\\ .\\ 1 \\\end{pmatrix}
=
\begin{pmatrix} 0(FALSE) \\ 0(FALSE) \\ 0(FALSE) \\ 0(FALSE) \\ 1(TRUE) \\ 1(TRUE) \\ .\\ .\\ .\\ 1(TRUE) \\\end{pmatrix}
=
\begin{pmatrix}
作業名A: データあり \\
作業名B: データあり \\
作業名C: データあり \\
作業名D: データあり \\
作業名E: データなし \\
作業名F: データなし \\
.\\.\\.\\
作業名N: データなし
\end{pmatrix}

行列のOR計算は全て+で連結させる！！！
これは覚えておきましょう。

ちょっとした注意点

ExcelとGoogleSpreadsheetの内部ロジックが異なるためExcelでの配列計算が必ずしも早くなるとは限らないので注意が必要です。
行や列を移動させると自動入力される数値が確定されてしまうため、ARRAYFORMULA数式でエラー#Ref!が発生します。回避方法としては行や列の移動はせずに追加してデータを編集させるようにする、または移動した後に自動入力されるセルをクリアすると解消するので検討してみてください。
配列を利用するため知識がない人が保守すると大変にパターンもあるので複雑すぎる数式は使わないようにする工夫も大切です。

まとめ

ARRAYFORMULA便利！
数式が複雑だとメンテナンスし辛くなるため使い分けが大切

全てを理解しなくてもいいので、
自動採番の=ROW()-1だけでも卒業できれば幸いです！

自動採番で=ROW()-1は卒業しよう 〜ARRAYFORMULAのススメ〜