Google Sheets約5分で読めます

スプレッドシートのARRAYFORMULA|行を足しても数式を入れない

スプレッドシートのARRAYFORMULAを表したイメージ
記事の内容を表したイメージ(AI生成)

新しい行を追加するたびに数式をコピーしなければならない、という手間に困っている方に向けた記事です。GoogleスプレッドシートのARRAYFORMULAを使うと、1行目に数式を一度だけ書けば、データを追加した行にも自動で結果が広がります。この記事では、仕組みの理解から実際の書き方、よくある失敗の対処まで、具体的な手順で説明します。

ARRAYFORMULAはGoogleスプレッドシート固有の関数です(Excelには同名の関数はなく、動作が異なります)。列全体に対して一括で計算を適用できるため、「行を足すたびに数式を下にコピーする」作業をなくせます。ただし、すべての関数と組み合わせられるわけではなく、使える場面を見極めることが重要です。


ARRAYFORMULAが解決する問題

通常の数式は、書いたセルだけに結果を返します。たとえば、C2セルに=A2*B2と書いた場合、C3以降にも同じ計算を適用するには、C3、C4……と数式を貼り付けていく必要があります。

行数が増えるたびにこの作業を繰り返すと、コピーし忘れによる計算漏れが起きやすくなります。また、途中の行だけ数式が違う状態になっていても気づきにくく、集計結果にずれが生じる原因になります。

ARRAYFORMULAを使うと、C2セルに一つの数式を書くだけで、A列・B列にデータが入っているすべての行に計算結果が表示されます。新しい行にデータを入力すると、C列の結果もその行まで自動で伸びます。


基本の書き方と動作の確認

ARRAYFORMULAの基本構文は次のとおりです。

=ARRAYFORMULA(数式または範囲を使った計算)

列全体を対象にするときは、行番号を省略した列指定(例:A:A)を使います。ただし、1行目が見出しの場合は見出し行を除いた範囲(例:A2:A)を指定します。A2:Aは「A2から下すべて」を意味し、データが増えても自動的に追いかけます。

架空の例です。 申込管理表で、A列に「単価」、B列に「個数」が入っている場合を考えます。

表は横にスクロールできます

行A列(単価)B列(個数)C列(金額)
1単価個数金額
25003?
38002?
420010?

C2セルに次の数式を一つ書きます。

=ARRAYFORMULA(A2:A * B2:B)

これだけで、C2・C3・C4……にそれぞれ1500・1600・2000が表示されます。5行目以降にデータを追加すると、C列にも計算結果が自動で現れます。

なお、画面の表示やメニュー名は版や設定により異なる場合があります。


空白行に「0」や「FALSE」が表示されるときの対処

ARRAYFORMULAでよく困るのが、データのない行にまで「0」や空文字が表示されてしまう問題です。A2:A * B2:Bのように列全体を指定すると、A列・B列が空でも計算が走り、空のセルを0として扱います。

これを防ぐには、IFを組み合わせてデータが空の行をスキップします。

=ARRAYFORMULA(IF(A2:A="", "", A2:A * B2:B))

この数式は「A列が空白なら何も表示しない、そうでなければ計算する」という意味です。B列が空のケースも考えられる場合は、ANDを使って両方をチェックします。

=ARRAYFORMULA(IF((A2:A="")+(B2:B=""), "", A2:A * B2:B))

+はORの代わりに使えます。A列またはB列のどちらかが空であれば空白を返します。

迷いやすい例外として、A列に数値ゼロ(0)が入る場合があります。A2:A=""はゼロを空白と区別できるため、この書き方で問題ありません。ただし、A列に数値ではなくスペースが混入しているケースでは空白判定がうまく働かないことがあります。入力ルールを事前に整えておくことが大切です。


TEXT・CONCATENATE・IFSなど他の関数との組み合わせ

ARRAYFORMULAは四則演算だけでなく、TEXT、LEN、LEFT、SUBSTITUTE、IFERRORなど多くの関数と組み合わせられます。

たとえば、D列に日付が入っていて、「年月」だけを取り出したE列を作りたい場合は次のように書きます。

=ARRAYFORMULA(IF(D2:D="", "", TEXT(D2:D, "yyyy年mm月")))

一方、`VLOOKUP`や`SUMIF`など範囲を返さない関数は、単純に`ARRAYFORMULA`で囲んでも期待どおりに動かない場合があります。VLOOKUPを配列で使うには書き方が変わります(詳細は公式ヘルプで確認してください)。SUMIFは集計関数のため、そもそも行ごとの展開には向きません。

IFSは条件分岐に使えますが、ARRAYFORMULAと組み合わせる場合はすべての条件と戻り値が配列に対応している必要があります。うまく動かないときは、IFをネストした書き方に切り替えると安定することがあります。


既存の数式から切り替えるときの注意点

すでにC列に行ごとの数式が入っている状態からARRAYFORMULAに切り替える場合は、次の手順で進めます。

  1. 作業前にファイルをコピーして元の状態を残します(ファイルメニューから「コピーを作成」)。
  2. ARRAYFORMULAを書く予定のセル(たとえばC2)以外の、同列にある数式をすべて削除します。
  3. C2セルにARRAYFORMULAの数式を書きます。
  4. C2の数式が、以前の各行の結果と一致しているか数行分を目視で確認します。

手順2をすっ飛ばしてARRAYFORMULAを書くと、「配列の結果が既存のセルに上書きできない」というエラーが出ます。ARRAYFORMULAは結果を下のセルに自動展開しようとするため、その範囲にすでに何かが入っていると止まります。

また、ARRAYFORMULAの結果が展開されたセル(C3以降)を手動で編集しようとすると、エラーになるか上書きでARRAYFORMULAが壊れます。列の途中だけ別の値を入れたい場合は、ARRAYFORMULAの設計を見直すか、その行だけ別の列を使う構成に変えることを検討してください。


使わない方がよい場面

ARRAYFORMULAは便利ですが、向かない場面もあります。

  • セルごとに異なる計算ロジックが必要な場合:行によって条件が複雑に変わるときは、数式が読みにくくなりミスを招きます。
  • 手動で一部の行だけ値を上書きしたい場合:展開されたセルは編集できないため、運用ルールと合わなくなります。
  • 他の担当者が数式に慣れていない場合:ARRAYFORMULAを知らない人が中間の行に値を入れて壊してしまうリスクがあります。引き継ぎのときに説明書きを残すか、シートを保護する設定を検討してください。

また、個人情報を含む名簿などを外部サービスや生成AIに貼り付けて数式を直してもらう場合は、データを渡す前に確認が必要です。テスト用に架空データに置き換えてから相談することをお勧めします。


次の行動

まず、手元のスプレッドシートでコピーを作ってから、行ごとに書かれている数式のうち一列だけをARRAYFORMULAに置き換えてみてください。結果が元の数式と一致するか確認し、新しい行を1行追加してC列に自動で結果が出ることを確かめます。うまく動いたら、同じ要領で他の列にも広げていきましょう。

参考資料

この記事の本文はAI(Claude)が執筆しました。画面の表示や操作名は、アプリの版や設定によって異なります。実際の操作の前に元のファイルを複製し、公式のヘルプで最新の情報を確認してください。

記事の誤り・ファイルの不具合を知らせる · 修正記録

記事一覧へ