ARRAYFORMULAで列に数式を適用する

通常、すべての行で計算したい場合は、数式を1つ書いて下方向にドラッグします。これでも動作しますが、新しいデータが追加されるたびに数式を拡張する必要があり、コピーした数式が何百個も残ってしまいます。

Google Sheetsには、よりよい方法があります。それがARRAYFORMULAです。1つのセルに1つの数式を置くだけで、列全体を一度に計算し、新しい行にも自動的に対応します。

ARRAYFORMULAを使うと、単一セルではなく範囲全体に数式を適用するようSheetsに指示できます。結果はスピルして、入力範囲の行数に合わせて下方向に広がります。

=A2*B2を100回繰り返す代わりに、A2:AとB2:Bを参照する数式を1つ書けば、答えが自動的に入力されます。

A列に数量、B列に価格が入っているとします。行ごとの合計を計算するには、この1つの数式をC2に入力します。

Aの各値と対応するBの値を下の行まで順番に掛け合わせるため、C3以降を操作する必要はありません。

A2:A100ではなくA2:Aとなっている点に注目してください。この終端を省略した形式は、2行目から下のすべての行を意味するため、新しく追加したデータも自動的に含まれます。

これがARRAYFORMULAの本当の力です。シートを将来のデータにも対応させられます。新しい注文を追加すれば、追加の作業なしで合計が表示されます。

終端を省略した範囲には、データの下にある何千もの空行も含まれるため、数式はそれらの行まで計算しようとします。その結果、0や意図しない値が表示されることがあります。

計算を、行が空かどうかを確認するIFで包みます。Aが空白なら空文字列を返し、それ以外の場合は計算を実行します。

便利な方法として、配列数式の中に列ヘッダーを含めると、列全体を1つのセルで管理できます。この数式をC1に入力します。

最初の部分でヘッダー文字列を出力し、残りの部分で各データ行を計算します。;によってそれらを縦方向に積み重ね、1つのスピル列にします。

ARRAYFORMULAは計算専用ではありません。テキスト関数にも使えます。姓と名からフルネームの列を作成するには、2つの範囲を連結します。

Aの各名とBの各姓を、間にスペースを入れて行ごとに結合し、すべての行を一度に処理します。

配列を自分で処理できるため、ARRAYFORMULAを必要としない関数もあります。SUMIF、FILTER、UNIQUE、QUERYは、すでに範囲全体で動作します。

ARRAYFORMULAを主に使うのは、通常は単一セルで動作する処理（*、&、LEFTなど）をすべての行で実行したい場合です。

LEFT、UPPER、TRIMなどの関数は、通常は1つのセルを処理します。ARRAYFORMULAの中では、範囲全体を処理します。

これにより、A列のメールアドレスをすべて一度に大文字へ変換できます。ARRAYFORMULAで包まなければ、最初のセルだけが変換されます。

関数名を毎回入力する必要はありません。Google Sheetsで範囲を参照する数式を入力し、Ctrl + Shift + Enter（MacではCmd + Shift + Enter）を押します。

Sheetsが数式を自動的にARRAYFORMULAで包んでくれます。ドラッグしてコピーした数式を、1つのスピルする数式にすばやく変換できます。

要素ごとの演算では、範囲の高さを同じにする必要があります。A2:AとB2:B50を混在させると、エラーや位置のずれた結果が生じることがあります。

両方を終端を省略した範囲（A2:AとB2:B）にするか、同じサイズの固定範囲にしてください。範囲をそろえることで、行ごとの出力位置もそろいます。

ARRAYFORMULAの知識を確認しましょう。

1つの数式で列全体を計算する方法を学びました。

• ARRAYFORMULAは範囲全体に対して演算を適用します
• A2:Aのように終端を指定しない範囲は、新しい行を自動的に含めます
• IF(A2:A = "", "", ...)で囲むと、空白をスキップできます
• 数学演算、テキストの結合、UPPERなどの関数で使用できます
• Ctrl/CmdとShiftとEnterを同時に押すと、ラッパーを自動的に追加できます

1つのセルで、常に更新される列を作成できます。