Excelで全列参照（A:A や 1:1）を使うと、必ず遅くなる。そう思われがちですが、実際は少し違います。重くなりやすいのは、全列参照そのものよりも、SUMPRODUCT や配列数式、広い厳密一致検索のように「空白を含む大量セルまで評価しやすい式」と組み合わさったときです。逆に、SUM や SUMIF、SUMIFS のような一部の組み込み関数は、未使用セルを調べないため、全列参照でも相対的に効率的です。まずはテーブル化、次に参照範囲の縮小、最後に重い式の置き換え。この順で見直すと、Excelの再計算の遅さは改善しやすくなります。(Microsoft Learn)
この記事では、Excelで全列参照が再計算を遅くする理由を、仕様差も含めて整理します。あわせて、どの式が危険か、どの改善策が自分のブックに向くか、どこでつまずきやすいかまで、実務でそのまま使える形でまとめます。
Excelの全列参照が再計算を遅くする本当の理由
Excelは、依存関係の情報と計算チェーンをもとに、変更があった式だけをなるべく再計算する仕組みです。ただし、セルを変更するたびに依存関係の追跡と計算チェーンの更新は行われます。全列参照が多く、しかもその式が広い範囲の評価を必要とする場合は、F9 を押したときだけでなく、入力中のもたつきとしても現れやすくなります。(Microsoft Learn)
A:A は「今使っている行だけ」を意味しません。.xlsx の1列は 1,048,576 行あるため、全列参照を配列計算や広い検索に使うと、空白行まで含んだ巨大な対象を抱えやすくなります。昔の 65,536 行時代の感覚で作られたテンプレートが、今のExcelで急に重く見えるのはこの差が大きいからです。同じ話は 1:1 のような行全体参照でも起こります。(Microsoft Learn)
Microsoftの仕様を踏まえた実務上の目安は、次のとおりです。(Microsoft Learn)
| 式のパターン | 全列参照との相性 | 重くなりやすい理由 | 最初に試す改善 |
|---|---|---|---|
=SUM(A:A) | 比較的よい | 未使用セルを見ないため致命傷になりにくい | テーブル化、または必要範囲に絞る |
=SUMIFS(C:C,A:A,F1) | まずまず | 比較的効率的だが、範囲が広いほど無駄は残る | 構造化参照へ置換 |
=SUMPRODUCT((A:A=F1)*(B:B)) | 悪い | 列内の全セルを計算しやすい | SUMIFS / COUNTIFS へ置換 |
配列数式で A:A を使う | 悪い | 空白を含む参照全体を評価しやすい | 補助列、テーブル参照 |
=VLOOKUP(E2,$A:$H,5,FALSE) | 要注意 | 厳密一致は走査範囲が広いほど不利 | 範囲縮小、MATCH + INDEX 再利用 |
OFFSET / INDIRECT を含む名前定義 | 要注意 | 揮発性で再計算頻度が増えやすい | テーブル、または INDEX ベースへ見直し |
「全列参照は絶対NG」ではない
Microsoftの資料でも、SUM、SUMIF、SUMIFS のような範囲を扱う関数は、未使用セルを調べないため全列参照でも相対的に効率的とされています。一方で、SUMPRODUCT のような配列計算系は列全体を評価しやすく、Microsoft Supportでも全列参照は推奨しないと明記されています。つまり、同じ A:A でも、関数の種類で重さはかなり変わります。(Microsoft Learn)
古い情報だけを信じて「全列参照は全部ダメ」と決めつけるのも正確ではありません。Microsoft Learnには、Office 365 version 1809 以降では同じテーブル範囲に対する VLOOKUP / HLOOKUP / MATCH の厳密一致検索が大きく改善したこと、さらに全列参照を含む特定の VLOOKUP 問題は Office 2016/365 version 1708 以降で修正されたことが記載されています。ただし、厳密一致検索は今でも走査するセル数に比例して時間が増えやすいため、広すぎる範囲を放置するメリットはほとんどありません。(Microsoft Learn)
改善策はこの順番で進めると失敗しにくい
元データをExcelテーブルに変える
元データ範囲を選択して Ctrl + T でExcelテーブルに変えると、構造化参照が使えるようになります。構造化参照はデータの増減に合わせて自動で拡張・縮小し、全列参照や動的範囲よりパフォーマンス上の不利が少ない方法としてMicrosoftが推奨しています。テーブル内の数式もデータに合わせて広がるため、「行が増えるたびに式を直すから全列参照にしていた」という運用をやめやすいのも利点です。(Microsoft Learn)
変更前
=SUMIFS($G:$G,$B:$B,$J2,$C:$C,$K2)
変更後
=SUMIFS(tbl売上[金額],tbl売上[担当],$J2,tbl売上[月],$K2)
テーブル化は、速度だけでなく保守性にも効きます。1枚のシートに複数の表があるときも、A:A のような曖昧な参照より、tbl売上[金額] のような構造化参照のほうが事故が起きにくいです。(Microsoft Learn)
固定範囲にするなら「実データ + 少しの余白」に絞る
厳密一致の検索は、走査するセル数が増えるほど不利です。Microsoftも、厳密一致検索ではスキャンする範囲を最小化し、テーブルや構造化参照、動的範囲を使うよう勧めています。(Microsoft Learn)
たとえば実データが3万行なら、いきなり A:A にするのではなく、まずは A$2:A$50000 のように上限を管理します。将来増えるかも で A$2:A$1000000 にすると、全列参照とほとんど変わらない負担を抱えがちです。
変更前
=VLOOKUP(E2,$A:$H,5,FALSE)
変更後
=VLOOKUP(E2,$A$2:$H$50000,5,FALSE)
SUMPRODUCTや配列数式は、IFS系か補助列へ分解する
Microsoft Learn では、SUMIFS、COUNTIFS、AVERAGEIFS は配列数式よりかなり速く、可能なら SUMPRODUCT より優先すべきと案内されています。配列数式の高速化では、式の中に詰め込んだ条件や参照を補助列へ切り出すのも有効です。(Microsoft Learn)
変更前
=SUMPRODUCT(($A$2:$A$50000=H2)*($B$2:$B$50000=I2)*$C$2:$C$50000)
変更後
=SUMIFS($C$2:$C$50000,$A$2:$A$50000,H2,$B$2:$B$50000,I2)
条件が多いときは、まず TRUE/FALSE を返す補助列を作り、その列を SUMIF や COUNTIF で集計すると、スマート再計算が働きやすくなります。なお、IFS系は条件を左から順に評価するため、絞り込み効果の高い条件を先に置くほうが効率的です。(Microsoft Learn)
複数列を返す検索は、MATCHを1回だけ使う
同じキーで複数列を返すのに VLOOKUP を何本も並べると、同じ範囲を何度も厳密一致で走査しがちです。Microsoft Learn でも、厳密一致の MATCH を1回保存し、その結果を複数の INDEX で再利用する方法が勧められています。(Microsoft Learn)
Z2
=MATCH($E2,$A$2:$A$50000,0)
AA2
=IFERROR(INDEX($B$2:$B$50000,$Z2),"")
AB2
=IFERROR(INDEX($C$2:$C$50000,$Z2),"")
この形にすると、検索の「見つける処理」を1回で済ませられるため、同じ行から複数項目を返す帳票で効きやすくなります。
在庫表や単価表のようにデータを並べ替えられ、近似一致でよい業務なら、ソート済みデータの近似一致は範囲の長さの影響を受けにくく高速です。ただし、社員番号や受注番号のように完全一致が前提の列には向きません。(Microsoft Learn)
動的範囲が必要でも、OFFSETの多用は避ける
行数が増減する表では、名前定義で動的範囲を作りたくなります。ただし Microsoft Learn では、OFFSET は揮発性関数なので、一般に INDEX ベースの動的範囲のほうが望ましいとされています。また、動的範囲の中で使う COUNTA は多くの行を調べるため、使いすぎると逆に遅くなります。(Microsoft Learn)
実務では、まずテーブル化で済まないかを先に考えるのが安全です。動的範囲は便利ですが、式の読みやすさと保守性でテーブルに負けやすいからです。
手動計算は「応急処置」として使う
入力中の待ち時間を減らしたいときは、計算方法を手動にすると、再計算のタイミングを自分で制御できます。ただし、この設定は開いているすべてのブックに影響するため、根本対策ではなく一時しのぎとして使うのが基本です。(Microsoft サポート)
手動計算でよく使うショートカットは、次のとおりです。(Microsoft サポート)
| 操作 | ショートカット |
|---|---|
| 変更された式と依存式を再計算 | F9 |
| アクティブシートだけ再計算 | Shift + F9 |
| すべての数式を再計算 | Ctrl + Alt + F9 |
| 依存関係を再構築して全再計算 | Ctrl + Shift + Alt + F9 |
改善してもまだ遅いときのチェックポイント
Ctrl + Endを押して、実データよりはるか右下へ飛ぶなら、使用範囲が不必要に広がっている可能性があります。Microsoft Learn でも、使用範囲が実態より広いとパフォーマンスやファイルサイズの問題につながると案内しています。(Microsoft Learn)- 名前定義を見直します。全列参照はセル上の式ではなく、Name Manager の定義に隠れていることがよくあります。Microsoftのトラブルシュートでも、名前定義に全列参照や不要なリンクが残っていないか確認するよう案内されています。(Microsoft Learn)
- 条件付き書式や書式設定の範囲も見直します。行全体・列全体への過剰な書式設定や壊れた条件付き書式は、ブックの肥大化とパフォーマンス低下の原因になります。(Microsoft Learn)
INDIRECTやOFFSETが混ざっていないか確認します。OFFSETは揮発性、INDIRECTは揮発性かつシングルスレッド計算なので、全列参照と組み合わさると重さが増幅しやすいです。(Microsoft Learn)
迷ったときの選び方
実務では、次の判断で十分なことがほとんどです。
| 状況 | 最初に選ぶ改善策 | 向いている理由 | 注意点 |
|---|---|---|---|
| 毎月行が増える管理表 | テーブル化 | 参照が自動追従し、全列参照をやめやすい | 構造化参照の書き方に慣れる必要がある |
| 条件付き集計が多い | SUMIFS / COUNTIFS | SUMPRODUCT より軽くしやすい | 条件順を見直す |
| 同じキーで複数列を返す | MATCH 1回 + INDEX 複数回 | 同じ検索を繰り返さない | 補助セルや補助列が増える |
| 古いテンプレを大きく変えられない | 固定上限つき範囲 | 既存式を壊しにくい | 上限の見直しを忘れない |
| とにかく入力待ちを減らしたい | 手動計算 | すぐ効く | 計算漏れのまま保存しやすい |
まずやるべき順番
A:A、1:1、SUMPRODUCT(、OFFSET(、INDIRECT(を検索し、まず重い式を洗い出す。- 元データの表をテーブル化し、構造化参照へ置き換える。
SUMPRODUCTと配列数式をSUMIFS/COUNTIFS/ 補助列へ置き換える。- それでも遅ければ、Name Manager、
Ctrl + End、条件付き書式、手動計算の順に確認する。
Excelの全列参照が再計算を遅くする最大の理由は、「列全体を参照していること」そのものではなく、「その式が空白を含む大量セルまで評価してしまうこと」にあります。SUM 系は比較的耐えますが、SUMPRODUCT、配列数式、広すぎる厳密一致検索は要注意です。まずはテーブル化、次に範囲の縮小、最後に式の分解。この3段階で見直せば、体感速度も保守性も同時に改善しやすくなります。(Microsoft Learn)
最初の1時間でやるなら、いちばん行数が多いシートだけでもテーブル化し、SUMPRODUCT と全列参照を1本置き換えてみてください。改善が出れば、そのブックのボトルネックはかなり絞れます。
コメント