Googleスプレッドシートの配列数式は便利
タイトルのまんまですけど、Googleスプレッドシートの配列数式 ArrayFormula を最近使い始めました。
存在は知りつつ、手を出さずにいましたが、単なる食わず嫌いでした。
使ってみたら便利で、職場のチームで使用例を共有したら、そこそこ良いフィードバックを貰ったので紹介します。
●スプレッドシートありがちな使い方と課題
本題に入る前に、スプレッドシート関数の一般的な使い方から。
何かしらのセルを読み取って変換する関数を設定し、下の列にコピーします。
コピーしても不具合が出ないように
「絶対参照・相対参照(＝$有無)を賢く使いましょう！」
というアドバイスがありますが、＄を活用しても扱うデータ（＝対象行数）が増えたら、
都度、関数を下に下にとコピーする必要があります。そして、コピーした際に関数が壊れる、あるいは行数が増えた際に既存関数を誤って書き換えてしまった。なんてミスはありがちです。皆さんも、一度ならず、何度も経験されてますよね？
●配列数式、ArrayFormulaの特徴
コピーの手間やミスを抑制するのにArrayFormulaは有効です。
ArrayFormulaを使って関数設定すると、同じ列の下は記述しないで勝手に処理します。
そのため、扱うデータ（＝対象行数）が増えても、関数コピーは不要です。また、特定セル設定のみで（上の例ではB1）、間違って設定を更新するリスクが減ります（上の例ではB2以下のセル）。
●ArrayFormulaの活用例
概要説明だけではイマイチ伝わらないので、文字の長さを返すLEN関数を例に説明します。A列に文字数の異なるデータを準備します。
B1セルに=LEN(A1)を設定すると、
文字列の長さ（つまり文字数）として1が表示されます。
この条件をArrayFormulaで括ります。
綴りに自信がない場合は、セル編集で
Win: Shift + Ctrl + Enter
Mac: Shift+ ⌘ + Enter
をすると数式に対して、ARRAYFORMULA( 〜〜 )が追加できます。
「あれ？ B1セルに=LEN(A1) の時と変わらないじゃん」
と突っ込みを受けそうですが、ArrayFormulaを使うときは、
開始セルから終了セル
A1 → A1:A6 と書きます。
さらに終了セルを指定せず、
開始セルから無限セル
とセル範囲設定が肝です。対象セルがA1以下全てにする場合
A1 → A1:A
と書きます。
:（コロンの）右側はアルファベットのみで数字不要
がポイントです。書いてみましょう。
B2以下のセルにA列の値を読み取り長さ（＝文字数）が表示されます。
カーソルをB6セルに合わせると、
関数は設定されてなく、文字数のみ表示されます。言い換えるとB2以下のセルは、データの上書きや削除ができません。
配列は数を決めずに、足りなくなったら増やして全て取り込むという調整力があり、ArrayよりもArrangementと個人的に捉えてます。
●ArrayFormulaの活用Tips
概要を説明しましたが、A列は6行目までしかデータがないのに、 A1:Aと設定しているので、B列は7行目以降は、長さ0(ゼロ)と勝手に表示するのはイケてません。
かといって、A1:A6と書き、データが増えるたびに数値を書き換えるのも…。そこで、IF文を活用します。
対象セルが空白だったら、空白を表示
対象セルに値があれば、関数で処理
との条件を使います。スプレッドシートでは、空白は、="" で処理でき、A1セルを対象に考えると、
A1セルが空白だったら、空白を表示
A1セルに値があれば、LEN関数で処理
は、
=IF(A1="","", LEN(A1))と記述します。配列数式を使うと、
=ARRAYFORMULA(IF(A1:A="","",ARRAYFORMULA(LEN(A1:A))))
とデータが無い7行目以降がすっきりします。ところで実務を考えると、
とヘッダーを記述するのが一般的です。上述の例では、B2セルに
=ARRAYFORMULA(IF(A2:A="","",ARRAYFORMULA(LEN(A2:A))))を設定すると、上書きされるリスクがあるので、さらに
1行目ならラベル表示 （ row()=1なら ）
1行目以外は関数で処理
という条件を加え、
=ARRAYFORMULA(IF(row(A1:A)=1,"文字数",ARRAYFORMULA(IF(A1:A="","",ARRAYFORMULA(LEN(A1:A))))))
とIFを二重で使い、記述式は長くなりましたが、誤って書き換えてしまうリスクは減らせます。もちろん、ラベルを書き換えるのはちょっと面倒臭いけど、TRUE値の"〜〜〜"を書き換えるだけです。
●ArrayFormula注意点
ArrayFormulaは開始セルから無限セルという範囲設定のため、
邪魔するデータがあるとエラー(= #REF!)を起こします。
コメントを書きたかったら、他の列もしくは、ヘッダーの上に行を挿入して使いましょう。
あと、ArrayFormulaはExcelとの互換性がありません。
Excel形式でダウンロードすると諸々変換され、使い回しはできるものの、修正が難しくなります。。。
さいごに
ArrayFormulaを使いこなすのは簡単じゃありませんが、効果は大きいです。Excelの配列数式よりは扱いやすいので（比較しても意味無いけど）、興味を持たれたら、ぜひ、試してください。
良きGoogleスプレッドシート・ライフを！