ARRAYFORMULAが意図せず広がる・重くなるときの整理

下の行まで数式が勝手に広がって、手入力していたデータを上書きしてしまった。

`#REF!` が大量に出て、どこまでが本来の結果か分からなくなった――

ARRAYFORMULAでよく起きるのはこの3つです。原因と、避け方を順に整理します。

■ ARRAYFORMULAが何をしているか

=ARRAYFORMULA(B2:B100*C2:B100)

通常、`=B2*C2` は1つのセルに書けば1つの結果しか返しません。

ARRAYFORMULAは、この計算を範囲の行数ぶんまとめて配列として計算し、

入力したセルを起点に下や右へ結果を展開する関数です。1つのセルに

書くだけで、100行分の掛け算を1つの数式で済ませられます。

この「展開する」という性質が、そのまま事故の原因になります。

展開先に何かあれば衝突し、範囲の取り方を誤ればずれた場所まで

計算対象になります。

■ 症状1：入力していたセルの値が消える／#REF!になる

ARRAYFORMULAの結果は、入力したセルから下方向・右方向へ

自動的に広がります。このとき、展開先のセルにすでに値が入っていると、

その値は書き換えられず、代わりにARRAYFORMULA全体が `#REF!` になります。

たとえばB2セルに `=ARRAYFORMULA(D2:D100*E2:E100)` と入力したとき、

B5に別の数式や手入力の値が残っていると、B2〜B100全体の結果が

`#REF!` として表示されます。エラーの位置がB5ではなくB2に出るため、

どのセルが衝突しているのか一見して分かりにくいのが厄介な点です。

対処は、展開範囲（この例ならB2:B100）を先に選択して中身を空にしてから

ARRAYFORMULAを入力することです。範囲内に何か1つでも残っていると

全体が止まるため、「一部だけ空ける」ではなく「範囲ごと空にする」

のが基本になります。

■ 症状2：意図した範囲より広く（または狭く）展開される

=ARRAYFORMULA(IF(B2:B<>"", C2:C*D2:D, ""))

B列を `B2:B` のように列全体（の2行目以降）で指定すると、

実データが100行しかなくても、シートの最終行まで計算対象になります。

IF文で空判定をしていても、計算自体はシートの最終行まで毎回走るため、

シートが重くなる原因にもなります。

逆に、範囲を `B2:B100` のように固定してしまうと、101行目以降に

データを追加してもARRAYFORMULAの計算範囲に入らず、

101行目だけ結果が空欄のまま、という状態になります。

行数が変わる前提のデータなら、列全体参照＋IF文での空判定の組み合わせ

より、テーブルとして扱える範囲（元データをテーブル化しておく、

または想定される最大行数まで広めに固定する）にしておくほうが、

「広がりすぎて重い」「増えても反映されない」の両方を避けやすくなります。

■ 症状3：IFERRORと組み合わせたのに一部だけエラーが残る

=ARRAYFORMULA(IFERROR(B2:B100/C2:C100, 0))

IFERRORをARRAYFORMULAの内側に書くと、配列の各要素ごとに

エラー処理が適用されるため、これは正しい書き方です。

一方で、

=IFERROR(ARRAYFORMULA(B2:B100/C2:C100), 0)

のようにARRAYFORMULAの外側にIFERRORを置くと、配列全体としてどこか1つでも

エラーを含んでいた場合、結果全体が丸ごと0に置き換わります。

一部の行だけ正常に計算できていても、その結果ごと消えてしまうため、

「一部のセルだけエラーが直らない」のではなく「一括で全滅する」形の

不具合として現れます。行ごとにエラー処理をしたいなら、IFERRORは

必ずARRAYFORMULAの内側、計算式のすぐ外側に置いてください。

■ ARRAYFORMULAを使うべきでない場面

次のような場合は、そもそもARRAYFORMULAを使わないほうが安全です。

・対象が1行・1セルだけの計算 → 配列にする必要がなく、通常の数式で足ります

・SUMIFS、COUNTIFS、QUERYなど、すでに範囲をそのまま渡せる関数

　→ これらは元々配列的に範囲を扱えるため、ARRAYFORMULAで包む必要がありません

・展開先に手入力のメモや別の数式が混在している列

　→ 新しい行の追加や編集のたびに衝突リスクが生まれます

こうした場面では、範囲を実データぶんに絞った通常の数式を

1行ずつ（または最初の1行だけ書いてオートフィル）入れたほうが、

展開の事故が起きず、後から見た人にも計算範囲が分かりやすくなります。

■ 代わりに範囲を絞る考え方

ARRAYFORMULAを使う場合でも、`B:B` のような列全体参照は

最終手段に留め、まずは実データの想定最大行数（例：B2:B500）で

固定するところから始めるのが安全です。データが増えて範囲が

足りなくなったら、その時点で範囲を広げ直します。

「将来増えるかもしれないから最初から列全体にしておく」という判断は、

一見安全に見えて、シート全体を重くする・展開先の衝突に気づきにくくする

という2つの副作用を持ち込みます。範囲は必要になってから広げる、

という順番のほうが結果的に扱いやすくなります。

■ ARRAYFORMULAを他の展開系関数と重ねたときの負荷

ARRAYFORMULAの中でQUERYやIMPORTRANGEをさらに呼び出す書き方も

よく見かけます。

=ARRAYFORMULA(IFERROR(QUERY(A2:C, "select B where A="&D2:D)))

こうした書き方は、D列の行数ぶんQUERYが繰り返し評価される形に

近くなり、行数が増えるほど再計算の負荷が積み上がります。QUERY

自体がすでに範囲全体をまとめて処理できる関数なので、多くの場合

ARRAYFORMULAで包まずにQUERYの条件式だけで書き直したほうが、

結果は同じでも計算量を抑えられます。ARRAYFORMULAは「本来1セルずつ

しか計算できない数式を配列的に処理したいとき」に使う関数であり、

すでに範囲をまとめて扱える関数の外側にさらに重ねる必要は

基本的にありません。

■ それでも解決しないとき

・`#REF!` の原因になっているセルが、範囲のどこにあるか特定できない

・ARRAYFORMULAを使わない書き方に直したいが、行数が可変で組み方が分からない

・複数のARRAYFORMULAが同じシート内で干渉していて、切り分けられない

このあたりまで来たら、展開範囲を1つずつ検証した方が早いです。

私はスプレッドシートの数式まわりの調査と修正を承っています。

どこで展開が衝突していたかを明らかにしてお返ししています。

――

この記事の執筆には生成AIを活用しています。内容は実際の挙動を確認して書いています。