見出し画像

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!)を起こします。
コメントを書きたかったら、他の列もしくは、ヘッダーの上に行を挿入して使いましょう。

ポイント：ROW(A3:A=3)と書き換え

あと、ArrayFormulaはExcelとの互換性がありません。
Excel形式でダウンロードすると諸々変換され、使い回しはできるものの、修正が難しくなります。。。

さいごに

ArrayFormulaを使いこなすのは簡単じゃありませんが、効果は大きいです。Excelの配列数式よりは扱いやすいので（比較しても意味無いけど）、興味を持たれたら、ぜひ、試してください。

良きGoogleスプレッドシート・ライフを！

この記事が気に入ったらサポートをしてみませんか？