Googleスプレッドシートで同じ数式を何十行にもコピーしていて、行が増えるたびにコピー漏れや参照ズレが起きて困った経験はありませんか。
今回はそんな悩みを一発で解決してくれるARRAYFORMULA関数について、基本の使い方から「入れたのに反映されない」時の原因・対処法まで解説します。
ARRAYFORMULAって結局何ができるの？
リュージ、ARRAYFORMULAってよく聞くけど、普通に数式コピーするのと何が違うの？
一番の違いは「1つの数式で複数行・複数列をまとめて処理できる」ってこと。普通はD2に数式入れてD3、D4…とコピーしていくけど、ARRAYFORMULAならD2に1つ入れるだけで、あとは自動で下まで反映されるよ。
行を後から追加した時はどうなるの？
そこがポイント。「A2:A」みたいに列全体を指定しておけば、行を追加してもコピー不要で自動計算される。だから「ARRAYFORMULA＝列を伸ばす数式」って覚えるとイメージしやすいよ。
Excelでも使えるやつ？
実はARRAYFORMULAはGoogleスプレッドシート専用。Excelにはないけど、Microsoft 365やExcel 2021以降だと「動的配列」という似た仕組みで近いことができるよ。
ARRAYFORMULAは、複数のセル範囲に対して1つの数式でまとめて処理を行う関数である。「A2:A」のように範囲を列全体で指定しておくことで、行を追加しても自動で計算が反映されるようになり、数式のコピー漏れや参照ズレを防げる。Googleスプレッドシート専用の関数でExcelでは使用できないが、Microsoft 365やExcel 2021以降では動的配列という近い機能で代替できる。
実際に書いてみる（単価×個数の例）
実際どう書くの？例えばB列に単価、C列に個数があって、D列に金額を出したい時。
D2セルに =ARRAYFORMULA(IF(B2:B=””, “”, B2:B*C2:C)) って入れるだけ。B列が空白の行は空欄のまま、値がある行だけ自動で金額が計算されるよ。
なんでIFで空白チェックしてるの？そのまま掛け算じゃダメなの？
IFなしだと、データがない行にまで「0」が表示されちゃうんだよね。空白行を空欄のまま保つために、IFで「空白なら空欄、そうでなければ計算」という条件を先に挟んでるんだ。
税込価格みたいな、もう少し複雑な計算でも同じ考え方でいいの？
うん、同じ骨組みでOK。=ARRAYFORMULA(IF(B2:B=””, “”, ROUND(B2:B*1.1,0))) みたいに、ROUNDを足せば端数を四捨五入した税込価格が一括で出せる。「空白チェック→計算」の型さえ覚えれば応用が効くよ。
ARRAYFORMULAの基本形は「=ARRAYFORMULA(IF(範囲="", "", 計算内容)))」という型で覚えると応用が効く。IFで空白チェックを先に挟むことで、データのない行に不要な0や記号が表示されるのを防げる。単価×個数の金額計算や税込価格の計算など、実務でよく使う場面はこの型の組み合わせでほとんど対応できる。
「反映されない」「エラーになる」時の原因と対処法
僕、この前ARRAYFORMULA入れたのに1行目にしか反映されなかったんだけど、あれ何？
それ、ものすごくよくあるつまずきポイント。よくある原因は2つあって、1つは「B2:C2」みたいに単一セルの範囲しか指定していないパターン。これだとARRAYFORMULAをつけても展開する範囲がないから、1行しか計算されないんだよ。
じゃあどう直せばいいの？
数式の中の範囲を「B2」じゃなくて「B2:B」のように、列全体か十分な行数の範囲に直すこと。数式内のすべてのセル参照を範囲指定に揃えるのがコツだよ。
もう1つの原因は何？
「配列の結果が別の配列の結果と重なっています」というエラー。これは、ARRAYFORMULAが展開しようとする範囲に、すでに別の値や数式が入っているのが原因。展開先のセルを空にしてから入れ直すと直るよ。
あと、TODAY関数とか日付系と組み合わせたら変な結果になったこともあった。
TODAYやNOWのような「引数を取らない関数」は、範囲を渡しても配列としてうまく展開されないことがある。こういう関数を使う場合は、無理にARRAYFORMULAでまとめず、1セルだけの計算に留めるのが安全だよ。
ARRAYFORMULAが1行しか反映されない主な原因は、数式内のセル参照が単一セル（B2など）のままになっていることである。すべての参照を「B2:B」のような範囲指定に揃えることで解消できる。また「配列の結果が別の配列の結果と重なっています」というエラーは、展開先の範囲に既存の値や数式が残っていることが原因であり、出力範囲を空にすることで解決する。TODAYやNOWのような引数を取らない関数は配列展開と相性が悪いため、無理に組み合わせない方が安全である。
IF・VLOOKUPと組み合わせた実務パターン
他の関数と組み合わせた実務パターンも知りたい！合否判定とかできる？
できるよ。=ARRAYFORMULA(IF(A2:A=””, “”, IF(A2:A>=60,”合格”,”不合格”))) みたいに、IFを二重にすれば「点数が60点以上なら合格」って判定を全行一括でできるよ。
VLOOKUPと組み合わせるとどうなるの？
商品IDから商品名を自動表示する、みたいなマスタ参照が一括でできる。=ARRAYFORMULA(IF(A9:A=””,””,IFERROR(VLOOKUP(A9:A,$A$2:$C$5,2,FALSE),””))) こんな形。IDを入力するだけで、下まで自動的に商品名が反映されるよ。
IFERRORって何のためについてるの？
IDが見つからない時、VLOOKUPは「#N/A」というエラーを返すんだけど、それがずらっと並ぶと見た目が悪い。IFERRORで囲むことで、見つからない場合は空欄にできるんだ。
ARRAYFORMULAはIF関数と組み合わせることで合否判定などの一括処理ができ、VLOOKUPと組み合わせることでマスタ参照を全行自動化できる。VLOOKUPと組み合わせる際はIFERRORで囲むことで、該当データがない場合の#N/Aエラー表示を空欄に変換でき、シートの見た目を崩さずに運用できる。
まとめ：ARRAYFORMULAの使いどころ早見表
| 使いたい場面 | 数式の型 | 注意点 |
|---|---|---|
| 単価×個数の一括計算 | =ARRAYFORMULA(IF(B2:B=””,””,B2:B*C2:C)) | 出力列は事前に空にする |
| 税込価格の一括計算 | =ARRAYFORMULA(IF(B2:B=””,””,ROUND(B2:B*1.1,0))) | ROUNDで端数処理を忘れない |
| 合否などの一括判定 | =ARRAYFORMULA(IF(A2:A=””,””,IF(A2:A>=60,”合格”,”不合格”))) | 条件式はIFを二重にする |
| VLOOKUPでのマスタ参照 | =ARRAYFORMULA(IF(A9:A=””,””,IFERROR(VLOOKUP(…),””))) | IFERRORで#N/Aを防ぐ |
| 反映されない時 | 参照をすべて「A2:A」形式の範囲に統一 | 単一セル参照が混ざっていないか確認 |
「空白チェック→計算」の型と、範囲指定さえ揃えれば怖くないってことだね。
そうだね。最初は単価計算みたいなシンプルな例で慣れて、IFやVLOOKUPとの組み合わせに進んでいけば、コピー漏れに悩まされる表作りから卒業できるよ。
ARRAYFORMULAは「空白チェックをIFで先に挟み、範囲はすべて列全体で統一する」という基本の型を押さえれば、単価計算から合否判定、マスタ参照まで幅広く応用できる。反映されない・エラーになるといったつまずきの多くは、参照範囲の指定ミスか出力先セルの重複が原因であり、原因を切り分けて対処すれば安定して運用できる。
