数式の速度UPテクニック集:テーブル・LET・セル参照コスト・FILTER・スピル検索・事前IF・最終行カウント
前回の記事「検索関数の速度と参照範囲」では、VLOOKUP・INDEX+MATCH・XLOOKUPの関数間の速度差を掘り下げました。 今回はそこからさらに一歩進んで、「関数の選び方」ではなく「数式の書き方・構造の工夫」で速度を上げるテクニックを集中的に解説します。
テーブル参照、LET関数による中間結果の再利用、作業セル vs セル内再計算のコスト比較、FILTER関数による一括抽出、スピル検索によるファイルサイズ圧縮、 そして事前IF空白チェックと最終行カウントによるスピル最適化まで、 数式を大きく変えずに負荷を見直す方法と、数式の構造自体を見直す方法の両面を扱います。
計算速度はデータ量やブック構成によって変わります。掲載する秒数や倍率は、参照元または例示条件での結果です。重い場合は、実際のブックのコピーで変更前後を比較してください。
速度比較はすべて完全一致検索(VLOOKUP の FALSE / XLOOKUP のデフォルト)を前提にしています。 バイナリ検索(ソート済みデータの二分探索)を使える場合は、検索負荷を抑えられることがあります。 バイナリ検索については「検索関数の速度と参照範囲」の【5】〜【7】を参照してください。
【1】テーブル(構造化参照)― 参照範囲を管理しやすくする土台
Excelの「テーブル」機能(Ctrl+T)でデータをテーブル化し、数式で構造化参照(例:テーブル1[社員ID])を使うだけで、
実データ範囲に追従するため、不要な行まで参照する数式より負荷を抑えやすいことがベンチマークで確認されています。固定範囲との速度差はデータ構成によって変わります。
| 関数 | 範囲指定 A2:A500 | 列全体参照 A:A | テーブル参照 テーブル1[列名] | テーブル参照の 高速化率 |
|---|---|---|---|---|
| VLOOKUP | 6.6秒 | 14.2秒 | 2.3秒 | 範囲指定の約2.9倍速 |
| INDEX+MATCH | 8.9秒 | 17.7秒 | 2.6秒 | 範囲指定の約3.4倍速 |
| XLOOKUP | 11.2秒 | 24.1秒 | 3.3秒 | 範囲指定の約3.4倍速 |
出典:Ablebits「VLOOKUP vs INDEX MATCH vs XLOOKUP: which is the fastest」。計測は Microsoft 365 の Excel 32ビット(バージョン2011、当時のベータチャネル)で行われたものです。各行は同じ関数のまま参照方法だけを変えた比較なので、この差は検索関数の違いではなく、テーブル化(参照範囲の違い)による差です。現在のExcelや64ビット版では結果が変わる可能性があります。
① 対象範囲を明示しやすい:構造化参照はテーブル列を指すため、列全体参照より処理対象を把握しやすくなります。固定範囲との速度差は関数とデータ構成で変わります。
② 参照範囲を揃えやすい:A2:A500 と A2:A501 のような手入力のずれを防ぎやすくなります。これを「キャッシュ共有100%」という内部処理の保証には置き換えません。
③ 行の追加・削除に自動追従する:テーブル行の追加に合わせて参照が拡張されるため、数式の保守がしやすくなります。
=VLOOKUP(A2, マスタ!A:E, 3, FALSE)列全体を数式の参照範囲として指定。
関数によって全列参照の扱いが異なるため、限定範囲・テーブル参照と比較する。
=VLOOKUP(A2, マスタテーブル, 3, FALSE)テーブル範囲を参照し、行の追加にも自動追従。
主な利点は範囲管理と保守性。
SUMやSUMIFなどは未使用セルを比較的効率よく扱える一方、配列計算では全列参照の負荷が大きくなる場合があります。完全一致検索では検索範囲を小さくし、テーブル、限定範囲、動的範囲を使うことが推奨されています。
したがって「全列参照は常に遅い」「標準検索関数なら常にUsedRangeまで」と一般化せず、対象関数と実データで計測してください。
データをテーブル化して構造化参照に切り替えることで、 参照したベンチマークでは、列全体参照や通常範囲より短い計算時間になった例があります。 ただし既存の名前定義、外部参照、共有先との互換性を確認してから切り替えてください。
【2】LET関数 ― 同じ計算を1回だけ実行して再利用する
LET関数(Excel 2021以降)は、数式内で変数を定義し、その結果を複数回参照できる関数です。 同じ部分式が数式内に複数回登場する場合、LETで1回だけ計算して使い回すことで、計算コストを削減できます。
変数名に計算結果を格納し、以降の式で何度でも参照可能。各変数は1回だけ計算される。
=IF(ISNUMBER(MATCH(A2,D:D,0)), INDEX(E:E,MATCH(A2,D:D,0)), "未登録")MATCH(A2,D:D,0) が2回書かれている → 2回とも独立して計算される。
同じMATCH式を2か所に記述する構成。
=LET(pos, XMATCH(A2, D:D), IF(ISNUMBER(pos), INDEX(E:E, pos), "未登録"))XMATCHの結果を
pos に格納し、IF と INDEX の両方で再利用。検索結果を変数として再利用する構成(Microsoft 365 / Excel 2021以降)。
MATCH / XMATCH は、見つからないときに 0 ではなく #N/A を返します。IF(MATCH(…)>0, …) のように書くと、未登録のときは #N/A がそのまま表示され、「未登録」の分岐は実行されません。見つかったかどうかは ISNUMBER で判定します。
IF(AND(B2>100, C2="A"), ...) のような条件を数式内の複数箇所で参照する場合、
LETで条件結果をTRUE/FALSEの変数に格納すれば、条件評価が1回で済みます。
Reddit(r/excel)やMicrosoft Tech Communityでの検証によると、 LET関数自体が追加するオーバーヘッドは計測不能なレベルです。 「LETで包んだから遅くなる」ということはなく、重複計算を避けた分だけ純粋に速くなると考えてよいでしょう。 ただし、LETの中で定義した変数を1回しか使わない場合は速度改善の意味がないため、 その場合は可読性向上のための利用と割り切ってください。
【3】セル参照コスト vs セル内再計算 ― 「作業セルに保存」が常に最速とは限らない
Microsoft公式ドキュメントでは「重複する計算は作業セル(ヘルパー列)に分離して再利用せよ」と推奨されています。 しかし実際には、セル参照自体にもコストがあるため、データ規模によって最適解が変わります。
Excelの計算エンジンは、セルを参照するたびに依存関係の追跡・値の読み取り・型変換などの処理を行います。 数式内で同じ計算を2回実行するコストと、作業セルに結果を保存して参照するコストのどちらが大きいかは、 「計算の重さ」と「データ行数」のバランスで決まります。
典型的なパターンとして、同じマスタから3列を取得するINDEX+MATCHを考えます。
=MATCH(A2, マスタ!A:A, 0)I2:
=INDEX(マスタ!B:B, H2)J2:
=INDEX(マスタ!C:C, H2)K2:
=INDEX(マスタ!D:D, H2)MATCHは1回だけ。INDEXはセル参照(H2)で行番号を取得。
数式4個 × N行 = 4N個の数式
=INDEX(マスタ!B:B, MATCH(A2, マスタ!A:A, 0))J2:
=INDEX(マスタ!C:C, MATCH(A2, マスタ!A:A, 0))K2:
=INDEX(マスタ!D:D, MATCH(A2, マスタ!A:A, 0))MATCHが3回実行される。ただし作業セルの参照コストはゼロ。
数式3個 × N行 = 3N個の数式
| データ規模 | 方法A (作業セル+INDEX) | 方法B (MATCH毎回実行) | 判定 |
|---|---|---|---|
| マスタ〜数百行 数式〜数百行 |
作業セルの参照コストが 相対的に大きい |
MATCHの計算が軽いので 3回実行しても問題なし |
方法B(セル内再計算) の方が速い傾向 (推測。実際のブックで計測してください) |
| マスタ〜数千行 数式〜数千行 |
ほぼ同等 | ほぼ同等 | 差が小さい傾向 (推測。実際のブックで計測してください) 可読性で選んでOK |
| マスタ10万行超 数式1万行超 |
MATCH結果を保存し 複数のINDEXで再利用 |
同じMATCH式を 各式の中に繰り返し記述 |
方法A(作業セル保存) の方が速い傾向 (推測。実際のブックで計測してください) |
以下は計測やMicrosoftの公式資料にもとづく説明ではなく、経験則からの推測です。セル参照のコストは、データ規模にあまり左右されない「固定的なコスト」と考えられます。 一方、MATCH関数の計算コストは、マスタの行数が増えるほど大きくなると考えられます。
マスタが小さいとき → MATCHの計算コストは小さいので、3回実行してもセル参照1回分のコストと大差ない。むしろ作業セルの参照・依存関係追跡のオーバーヘッドが目立つ。
マスタが大きいとき → MATCHの1回あたりの計算コストが大きくなるため、3回実行を1回に減らす効果が出やすい。
・大きなマスタで同じMATCH結果を複数回使う → 作業セルに保存する案を比較
・小規模で使い回しが少ない → セル内で計算する案も含めて可読性を優先
・LET関数が使える環境(2021以降)なら → LETで変数化する方法も候補。作業セルを増やさず、重複計算をまとめられる
LETは同じ部分式を数式内で再利用しやすくしますが、ブック全体の再計算を防ぐ機能ではありません。結果一致と再計算時間で選んでください。
Microsoft公式のパフォーマンスガイド(「Excel performance: Improving calculation performance」)では、 「重複する計算を作業セルに分離して1回だけ計算し、他の数式で参照する」ことが第一のルールとして明記されています。
小規模な式では速度差を体感しにくい場合もあります。公式資料の方針を基本にしつつ、作業列の保守性と実測結果を確認してください。
B1=SUM($A$1:$A1) を下へコピーする方式は、行が下がるほど参照セルが増えます。
C1=A1, C2=C1+A2 のように前行の結果を再利用する方式は、各行の計算を小さくできます。これは累計計算の例であり、すべての数式が同じ割合で短縮されるわけではありません。
【4】FILTER関数で一括抽出 ― 同じ条件のVLOOKUPを何百行もコピーしない
FILTER関数(Excel 2021以降)は、条件に一致するすべての行をまとめて一括抽出する関数です。 「特定の部署の社員一覧を取得する」「特定の月の売上データを抜き出す」といった用途では、 VLOOKUPやXLOOKUPを何百行もコピーするよりも、FILTER関数を1つ置くだけで済む場合があります。
B2:
=XLOOKUP(条件, ...)(1件目)B3:
=XLOOKUP(条件, ...)(2件目)︙
B51:
=XLOOKUP(条件, ...)(50件目)50個の独立した数式。 各数式がそれぞれ検索を行う構成。
ファイルに50個分の数式データが保存される。
=FILTER(マスタ!A2:D5000, マスタ!C2:C5000="営業部", "該当なし")(マスタをテーブルにしている場合は
=FILTER(マスタ表, マスタ表[部署]="営業部", "該当なし")。列全体 A:D は指定しません)数式は1個だけ。 条件に合う全行を1つの式からスピルで出力。
ファイルに保存されるのは1個の数式だけ。
FILTERは「条件に合う全行」を返し、VLOOKUPやXLOOKUPは通常「対応する1件」を返します。処理目的が異なるため、速度だけを理由に直接置き換えません。
「同じ条件で複数件を取得する」用途なら、複数の検索式を並べる方法とFILTERを1つ置く方法で、結果・速度・レイアウトを比較できます。
FILTERはあらゆる場面でVLOOKUPより速いわけではありません。以下のケースでは注意が必要です。
・同じ条件で複数件の結果を取得したい → FILTER が適している
・IDで1件だけ取得したい → XLOOKUP / INDEX+MATCH の方が適切
・複数条件の組み合わせで抽出したい → FILTER + 論理演算(*や+)が柔軟(速度はデータ量と条件によって変わります)
【5】スピル検索 ― 1個の数式で15,000行分のVLOOKUPを置き換える
Excel 2021以降では、XLOOKUPやVLOOKUPの検索値に配列(複数セルの範囲)を渡すことで、 1個の数式から結果がスピル(自動展開)されます。 これは速度面に加えて、保存する数式数とファイルサイズを減らせる場合があるというメリットがあります。
=XLOOKUP(A1, マスタ!A:A, マスタ!C:C)B2:
=XLOOKUP(A2, マスタ!A:A, マスタ!C:C)︙
B15000:
=XLOOKUP(A15000, マスタ!A:A, マスタ!C:C)15,000個の独立した数式がファイルに保存される。
数式1つあたり数十バイト × 15,000 = 数百KB〜数MBの数式データ。
=XLOOKUP(A1:A15000, マスタ!A:A, マスタ!C:C)数式は1個だけ。結果がB1:B15000にスピルで自動展開。
ファイルに保存されるのは1個の数式データのみ。
Excelのxlsxファイル形式は、内部的にはXMLファイルをZIP圧縮したものです。各セルの数式はXMLとして1つ1つ記録されます。
15,000行の数式をコピーすると、15,000個の <f> タグがXMLに書かれます。
スピル検索では起点となる数式が少なくなるため、数式データを減らせる場合があります。ただし、計算結果、書式、共有文字列などもファイルサイズに影響するため、数式数だけから最終サイズは決まりません。
15,000行の XLOOKUP をコピーしたブック → 約3〜5MB(例)
同じ検索をスピル1数式に置き換えたブック → 約500KB〜1MB(例)
列数・書式・データの内容などの計測条件を記録していないため、目安の例です。同じ割合を保証するものではありません。対象ブックのコピーで保存前後を比較してください。
スピル検索は1個の数式で全行を処理しますが、速度面では「常に個別数式より速い」とは限りません。
| 観点 | スピル検索(1数式) | 個別数式(N行コピー) |
|---|---|---|
| 初回計算 | 全行を一括計算 (並列化の恩恵を受けにくい場合あり・推測) |
マルチスレッドで 並列計算されやすい(推測) |
| 1セル変更時の 再計算 |
⚠ 検索値が1つ変わっても 全行が再計算される |
✅ 変更されたセルの 数式だけが再計算される |
| ファイルサイズ | ✅ 小さくなる場合がある(計測条件は未記録) | ⚠ 数式数に比例して大きい |
| 数式の数 | ✅ 1個 | ⚠ N個 |
| 保守性 | ✅ 修正は1箇所 | ⚠ N箇所に影響 |
個別数式なら「A5だけ変更 → B5だけ再計算」ですが、 スピル数式は「A5が変更 → B1:B15000全体が再計算」されます。 検索値が頻繁に変わるシートでは、この再計算範囲の広さが速度低下の原因になることがあります。
逆に、「一度データを入力したらあまり変更しない」シート(月次集計レポートなど)では、 再計算頻度が低いためスピル検索のデメリットはほぼなく、ファイルサイズ削減と保守性のメリットだけが残ります。
・データ入力後にあまり変更しない帳票・レポート系のシート
・ファイルサイズを小さくしたい(メール添付・ネットワーク共有)
・数式の保守コストを下げたい(修正は1箇所だけ)
・XLOOKUPのバイナリ検索と組み合わせる場合(元々高速なので再計算コストが小さい)
【6】事前IF空白チェック ― 式の形ごとに効果を確認する
検索値が空白のときにVLOOKUP/XLOOKUPの実行をスキップする「事前IFチェック」は、 個別セル式では不要な検索を省ける場合があります。スピル式では配列全体の依存関係も関わるため、同じ短縮幅になるとは限りません。
各セルに入力してコピーする従来方式では、空白行の計算を省けるため、事前IFで負荷を抑えられる場合があります。
=VLOOKUP(A2, マスタ!D:E, 2, FALSE)A2が空白でもVLOOKUPを評価し、
結果として#N/Aエラーまたは意図しない値を返す。
空白行にも検索式が入っている構成。
=IF(A2="", "", VLOOKUP(A2, マスタ!D:E, 2, FALSE))A2が空白の場合、IFの短絡評価により
空白行では検索結果を求めない形にできる。
空白行が多いブックでは比較候補になる。
=IF(A2="", "", VLOOKUP(...)) は、空白行では検索結果を必要としないことを明示できます。関数・配列・Excel版によって評価方法の詳細は異なる可能性があるため、「計算コストがゼロ」とは断定せず、変更前後を計測します。
スピル式で同じIFチェックを行った場合の効果は、配列サイズ、検索関数、Excelの版で変わるため実測します。
=IF(A1:A15000="", "", XLOOKUP(A1:A15000, マスタ!D:D, マスタ!E:E, "", 0))IF条件とXLOOKUPの両方が配列を扱う構成。
結果は分かりやすい一方、事前IFだけで入力配列の大きさは変わらない。
=XLOOKUP(A1:A15000, マスタ!D:D, マスタ!E:E, "", 0)第4引数
if_not_found に "" を指定。空白検索値がマスタに該当しなければ自動で""を返す。
事前IFが不要なら、式を短くできる。
コミュニティ検証では分岐の両側が評価されたように見える例がありますが、1つの式から「すべての配列IFが常に両分岐を全計算する」とは断定できません。本記事では、入力配列を実データ範囲に絞る案と、事前IFを使う案を実測比較します。
| 方式 | IFの評価 | 空白行のVLOOKUP | 速度効果 |
|---|---|---|---|
| 個別セル数式 + 事前IF | 短絡評価あり | スキップされる | 負荷を抑える候補 |
| スピル式 + 事前IF | 配列式のため要実測 | 入力配列の範囲に依存 | 効果は式と版による |
| スピル式 + if_not_found="" | IF不使用 | 入力配列の範囲に依存 | 式を短くできる |
| スピル式 + 最終行カウント | IF不使用 | 空白行が範囲外 | 入力範囲を絞る候補 |
・個別セル数式(非スピル)を使う場合 →
=IF(A2="","", VLOOKUP(...)) は、空白行が多いほど負荷を抑えやすい。・スピル式を使う場合 → 事前IFだけでは効果が出にくいため、最終行を求めて範囲を絞る方法を検討します。
【7】最終行カウント ― スピル式の参照範囲を絞る
スピル式の負荷を見直す基本は、事前IFだけに頼らず、 入力配列のサイズ自体を最小化することです。 空白行をIFでスキップするのではなく、そもそも空白行を含まない範囲だけを渡します。
=XLOOKUP(A1:A15000, マスタ!D:D, マスタ!E:E, "", 0)データが8,000行でも、数式上の入力配列はA1:A15000。
実データより広い範囲を渡す構成になる。
=XLOOKUP(A1:INDEX(A:A, COUNTA(A:A)), マスタ!D:D, マスタ!E:E, "", 0)COUNTA(A:A) が8,000を返す前提なら、数式上の入力範囲をA1:A8000にできる。途中空白、数式で返した空文字、ヘッダーの有無に注意する。
① 数式が参照する配列を実データに近づけられる。
② 最終行の判定を1か所にまとめ、複数の式で再利用できる。
③ 事前IFが必要かどうかを、データの途中空白と出力要件から判断しやすい。
実際の再計算時間は対象ブックで確認してください。
LETで最終行計算を変数化することで、可読性が高く保守しやすい数式になります。
=LET(
最終行, COUNTA(A:A),
検索値, A1:INDEX(A:A, 最終行),
XLOOKUP(検索値, マスタ!D:D, マスタ!E:E, "", 0)
)COUNTAの計算が1回だけ変数に格納され、INDEX で範囲を動的に構成。
スピル配列のサイズが実データ行に自動調整される。
COUNTA はセルに値があるものをカウントするため、
データの途中に空白セルがあるとカウントがずれます。
途中に空白を含む可能性がある場合は、最終データ行を正確に取得する方法を使います。
=LET(
最終行, MATCH(1E+99, A:A, 1),
検索値, A1:INDEX(A:A, 最終行),
XLOOKUP(検索値, マスタ!D:D, マスタ!E:E, "", 0)
)MATCHの近似一致(型1)を使う方法。
昇順など近似一致の前提を満たす数値列でのみ使用する。
=LET(
最終行, MAX((A:A<>"")*ROW(A:A)),
検索値, A1:INDEX(A:A, 最終行),
XLOOKUP(検索値, マスタ!D:D, マスタ!E:E, "", 0)
)空でないセルのROW番号の最大値を取得。
空でないセルの最大行を求める例。列全体の配列計算になるため、限定範囲でも比較する。
MAX((A:A<>"")*ROW(A:A)) は列全体に対する配列演算で、それ自体にもコストがあります。
データに途中空白がないことが保証されているなら COUNTA の方が軽量です。途中空白がある場合は、テーブル参照や限定範囲を使う方法も比較してください。
| 手法 | 速度効果 | 仕組み | 注意点 |
|---|---|---|---|
| 事前IF | 式・版により要実測 | 空白時の表示条件を式に含める | 入力範囲自体は変わらない |
| if_not_found="" | 式・版により要実測 | 未登録時の表示をXLOOKUP内で指定 | 空白と未登録を区別する要件に注意 |
| 最終行カウント | 比較候補 | 数式上の入力範囲を 実データに近づける |
途中空白ありの場合は COUNTAの代わりにMATCH/MAX |
最終行カウントは、スピル式へ渡す範囲を管理する方法の一つです。COUNTAがデータ構造に合わない場合は、テーブル参照や別の最終行判定を検討します。
個別セル数式の事前IF、スピル式のif_not_found、限定範囲を、同じ結果になる条件で比較してください。
【8】最終まとめ ― どのテクニックをいつ使うか
されていない?
変更前後を対象ブックで計測する
数式内に複数回ある?
重複計算を排除
「複数行」を取得?
VLOOKUPを何百行もコピーしない
大量の行で実行?
最終行・テーブル・限定範囲を比較
多い?
個別セル式で変更前後を比較
複数の式で使う?
LETを使う案も含めて比較
| テクニック | 速度改善効果 | ファイルサイズ | 保守性 | 対応ver | 手間 |
|---|---|---|---|---|---|
| テーブル化 | ✅ 条件により短縮 | 変化なし | ✅ 大幅向上 | 全ver | ✅ Ctrl+Tだけ |
| LET関数 | ✅ 重複分だけ削減 | 変化なし | ✅ 可読性向上 | 2021以降 | 数式書き換え |
| 作業セル分離 | ✅ 大規模データで有効 | ⚠ 列が増える | やや複雑になる | 全ver | 列追加+数式変更 |
| FILTER一括抽出 | ✅ 複数件取得で有効 | ✅ 数式1個で済む | ✅ 修正1箇所 | 2021以降 | ✅ 数式置換だけ |
| スピル検索 | 場合による | 小さくなる場合がある | ✅ 修正1箇所 | 2021以降 | ✅ 数式置換だけ |
| 事前IF(個別セル) | 空白行が多い場合の候補 | 変化なし | 数式がやや長くなる | 全ver | 数式にIF追加 |
| 事前IF(スピル) | 式・版により要実測 | 変化なし | 表示要件によっては有用 | ― | 結果を確認して比較 |
| 最終行カウント(スピル) | 入力範囲を絞る候補 | 小さくなる場合がある | ✅ LET併用で良好 | 2021以降 | 数式書き換え |
・テーブル化:参照範囲の管理と行追加への追従が必要な場合。
・LET/作業セル:同じ中間結果を複数回使う場合。
・FILTER:条件に合う複数行を返す用途の場合。
・スピル検索と範囲管理:同じ式を多数行へ展開する用途の場合。
・事前IF:個別セル式で空白時の検索が不要な場合。
・バイナリ検索:並び順を保証でき、通常検索との結果一致を検証できる場合。