Google SheetsのARRAYFORMULA完全ガイド【財務モデル】

三表連動モデルや、タブが連携したシンジケートローン向けDCFモデルでは、こうした信頼性は「あれば便利」という話ではありません。取締役会報告資料の3枚目でモデルが崩壊するか、監査に耐えられるかの差です。

ARRAYFORMULAが実際に何をするのか

通常の数式をB2に入力すると、評価されるのはB2だけです。B2:B5001にコピーすれば、5,000個の独立したセルが生まれますが、誰かが847行目を気づかずに編集した瞬間、そのセルは誤差の発生源になります。

ARRAYFORMULAは1つの数式をラップし、指定した範囲全体に展開します。結果は「1つの数式、1つの真実、更新すべきセルが1つ」です。

// 通常のアプローチ - 5,000セル、5,000の障害ポイント
B2: =IF(A2="売上", C2*前提条件!$B$4, 0)
... B5001まで貼り付け

// ARRAYFORMULA - セル1つで列全体をカバー
B2: =ARRAYFORMULA(IF(A2:A="売上", C2:C*前提条件!$B$4, 0))

A2:Aのように末尾を開放した範囲を指定すると、下に追加された新しい行も自動的にカバーされます。月次決算のたびに行数が増えるGLフィードや月次エクスポートを扱う際には、これが重要です。

マルチタブモデルでのクロスタブARRAYFORMULA

本格的なモデルでARRAYFORMULAが真価を発揮するのは、クロスタブ参照です。たとえば、収益性分析タブでSKU別の貢献利益を表示しながら、損益計算書（P&L）タブと前提条件タブを同時に参照するケースです。

// 収益性分析!D2 - 商品ライン別粗利益を列全体で1数式
=ARRAYFORMULA(
  SUMIFS('損益計算書'!E:E, '損益計算書'!B:B, '収益性分析'!A2:A, '損益計算書'!C:C, ">=" & 前提条件!$B$3)
  - SUMIFS('損益計算書'!F:F, '損益計算書'!B:B, '収益性分析'!A2:A, '損益計算書'!C:C, ">=" & 前提条件!$B$3)
)

この数式は、P&Lタブから売上と売上原価（COGS）を取得し、商品ラインと基準日付で絞り込んで、粗利益の列全体を1つの数式で返します。翌四半期にP&Lタブに12個の新しいSKUが追加されても、数式が自動的に取り込みます。

採用ペース別のキャッシュランウェイ感応度分析にも、同じパターンが使えます。

// 人員計画!G2 - 採用シナリオ別の累積バーン
=ARRAYFORMULA(
  MMULT(
    シナリオ!$C$2:$E$13,                      // 12ヶ月 × 3シナリオ
    TRANSPOSE(前提条件!$D$5:$D$7)             // 役割別の諸経費込みコスト
  ) + SUMIF('固定費'!A:A, "間接費", '固定費'!C:C)
)

対応している関数・していない関数

すべての関数がARRAYFORMULAに対応しているわけではありません。最初にVLOOKUPをARRAYFORMULAでラップしようとして2時間を無駄にした経験がある方には、以下の表が役立つはずです。

Googleの公式ドキュメント「Google スプレッドシートの関数リスト」（2026年7月時点、英語版：Google Sheets function list）および「配列数式を使用する」（Use array formulas、Google Workspace Learning Center 収録）によると、「配列を返す」と説明されている関数はすでに配列コンテキストで動作しているため、ARRAYFORMULAのラップは不要であり、受け付けません。

関数	ARRAYFORMULA対応	備考
`IF`	✅ 対応	最も基本的なユースケース
`SUMIFS`	✅ 対応	集計値の配列を返す
`IFERROR`	✅ 対応	範囲全体をラップ可能
`TEXT`, `VALUE`, `LEN`	✅ 対応	標準的なテキスト・数値関数
`VLOOKUP`	⚠️ 部分対応	動作するが末尾行を取りこぼすことあり。INDEX/MATCHを推奨
`INDEX/MATCH`	✅ 対応	配列検索では最優先の選択肢
`UNIQUE`	❌ 非対応	元から配列関数のためネストするとエラー
`FILTER`	❌ 非対応	同上
`SORT`	❌ 非対応	同上
`QUERY`	❌ 非対応	非互換

大規模データでのARRAYFORMULAパフォーマンス

Google Sheetsのセル上限は1,000万セルです。5万行のGL明細タブに20列分のARRAYFORMULA分類列があっても上限には余裕がありますが、実際のボトルネックは再計算時間です。

実務での経験では、2〜3つのクロスタブ参照を含む5万行のARRAYFORMULAは、シート全体の再計算で3〜8秒かかります。同じ5万行を個別数式でコピーした場合は45〜90秒かかり、場合によってはタブがクラッシュします。

パフォーマンスを高める構造上のポイントは以下の通りです。

開放範囲（A2:A）の多用を避ける。 A2:Aは便利ですが、再計算のたびに列全体を評価します。四半期実績タブのように行数が決まっている場合（例：5,000行）は、A2:A5001と明示的に指定してください。

ARRAYFORMULAのネストを避ける。 外側に1つラップすれば十分です。二重にネストしても冗長なだけで、評価速度が低下します。

補助列を経由した二重ARRAYFORMULAを避ける。 ARRAYFORMULAが出力した列を別のARRAYFORMULAで再度ラップすると、再計算コストが倍増するだけでメリットはありません。なお、ARRAYFORMULAの入力として補助列を使うこと自体は問題ありません。

モデルを壊す典型パターン

ある取締役会報告資料で実際に起きた約2,500万円の誤差は、こうして生まれました。サマリータブのSUMIFSにARRAYFORMULAが使われておらず、明細タブの3行が手作業で上書きされていたのです。数式の参照範囲が、手入力された行の1行前で止まっていました。銀行コベナンツ（財務制限条項）の計算値がずれて初めて発覚しました。

ARRAYFORMULAは手動上書きを完全に防ぐわけではありませんが、問題を表面化させます。列全体を1つのセルが管理している状態で手入力が発生すると、#REF!エラーや数式パターンの破損が目に見える形で現れます。5,000行のコピー数式が静かにずれていくのとは対照的です。

ARRAYFORMULA vs. QUERY vs. ネイティブ配列関数：使い分けの基準

目的によって最適な関数は異なります。

ARRAYFORMULAは、列の各行に計算や分類を適用したいときに最適です。売上カテゴリ分け、人員別の諸経費込みコスト算出、前期比の差異フラグなどが典型例です。

QUERY（Google Sheets専用）は、複数のSUMIFS・COUNTIFSを重ねて書くような集計・フィルタリングに向いています。SQL的な記法でグルーピングを簡潔に書けますが、大きな範囲では処理が遅く、ARRAYFORMULAとの併用はできません。

ネイティブ配列関数（FILTER、UNIQUE、SORT、SEQUENCE）は、それぞれの用途に特化しており高速です。2万行のGL明細からコストセンターのユニークリストを抽出したいなら、UNIQUE('GL明細'!B2:B)がARRAYFORMULAで組む何よりも速いです。

三表連動モデルでの実践的な使い分けをまとめると、行レベルの分類・計算列はARRAYFORMULA、サマリーの検索とユニークリストはネイティブ配列関数、ピボットの代わりとなるアドホック集計はQUERY、というのが基本方針です。

複雑なクロスタブARRAYFORMULAを素早く書きたい場合は、ModelMonkeyのサイドバーに必要な内容を日本語で説明するだけで数式を自動生成できます。12タブ構成のLBOモデルを深夜に組んでいるとき、列のオフセットを頭の中で追いながら数式を手組みする作業から解放されます。

よくある質問

Q. ARRAYFORMULAはExcelでも使えますか？ A. いいえ。ARRAYFORMULAはGoogle Sheets固有の関数です。Excelには類似の機能として動的配列数式（Ctrl+Shift+Enterで確定する旧来の配列入力、またはExcel 365の動的配列）がありますが、構文や挙動が異なります。Google SheetsからExcelにエクスポートした場合、ARRAYFORMULAは機能しなくなるため注意が必要です。

Q. ARRAYFORMULAの途中のセルに手入力で値を上書きするとどうなりますか？ A. ARRAYFORMULAが展開しているセル範囲内に手入力しようとすると、多くの場合エラーが表示されるか、ARRAYFORMULAそのものが壊れます。これは問題が「見えない形で蓄積する」のを防ぐ安全装置として機能します。意図的に一部の行を手入力したい場合は、補助列を用意してIF文で切り替えるのがベストプラクティスです。

Q. ARRAYFORMULA内でIFERRORを使うべき場面はどこですか？ A. 参照先のタブにデータが存在しない行がある場合や、VLOOKUP・INDEX/MATCHで一致しない行が発生しうる場合には、IFERRORでラップすることを推奨します。=ARRAYFORMULA(IFERROR(INDEX/MATCH(...), ""))のように書くと、エラーセルが空欄で表示され、モデル全体の見た目がきれいに保たれます。

Q. Google Sheetsのセル上限（1,000万セル）に近づいたらどうすればいいですか？ A. まず開放範囲（A2:A形式）を明示的な範囲（A2:A50001など）に変換し、不要な書式設定列を削除します。それでも改善しない場合は、データの一部をBigQueryや別のスプレッドシートに切り出し、IMPORTRANGEまたはApps Script経由で参照する構成を検討してください。

Q. ARRAYFORMULAを使う数式とQUERYを混在させてもいいですか？ A. 同一セルへのネストは非互換ですが、別々のセルや列で併用するのは問題ありません。行ごとの計算列にはARRAYFORMULA、サマリー集計にはQUERYと役割を分けて設計するのが現実的なアプローチです。