全ver 365 2021～更新日: 2026-04-23

数式の速度UPテクニック集：テーブル・LET・セル参照コスト・FILTER・スピル検索・事前IF・最終行カウント

前回の記事「検索関数の速度とメモリ効率」では、VLOOKUP・INDEX+MATCH・XLOOKUPの関数間の速度差を掘り下げました。今回はそこからさらに一歩進んで、「関数の選び方」ではなく「数式の書き方・構造の工夫」で速度を上げるテクニックを集中的に解説します。

テーブル参照、LET関数による中間結果の再利用、作業セル vs セル内再計算のコスト比較、FILTER関数による一括抽出、スピル検索によるファイルサイズ圧縮、そして事前IF空白チェックと最終行カウントによるスピル最適化まで、 数式を1文字も変えずに速くなる方法と、数式の構造を見直して劇的に速くなる方法の両面をカバーします。

🔴 この記事の前提：完全一致検索での話です
速度比較はすべて完全一致検索（VLOOKUP の FALSE / XLOOKUP のデフォルト）を前提にしています。バイナリ検索（ソート済みデータの二分探索）を使える場合は、そちらの方が桁違いに速くなります。バイナリ検索については「検索関数の速度とメモリ効率」の【5】〜【7】を参照してください。

【1】テーブル（構造化参照）― 「何もしなくても速くなる」最強の土台

Excelの「テーブル」機能（Ctrl+T）でデータをテーブル化し、数式で構造化参照（例：テーブル1[社員ID]）を使うだけで、 列全体参照（A:A）や範囲指定（A2:A100000）より劇的に速くなることがベンチマークで確認されています。

Ablebits.com ベンチマーク（50万行メイン×500行マスタ）― 参照方法別の速度比較

関数	範囲指定 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倍速

なぜテーブル参照は速いのか ― 3つの理由

① 実データ範囲だけを自動参照する：列全体参照（A:A）は104万行をスキャン対象にしますが、テーブルの構造化参照は「テーブルに含まれる行」だけを自動的に参照します。データが500行なら500行分だけがスキャン対象になります。これは範囲指定よりもさらに正確で、余分な空白行を一切含みません。

② キャッシュ効率が最大化される：構造化参照は全数式で自動的に「完全に同一の範囲」を指すため、Excelのキャッシュ共有が100%効きます。範囲指定では人為的なミスで A2:A500 と A2:A501 のようなズレが発生しがちですが、テーブル参照ではこの問題が原理的に起こりません。

③ 行の追加・削除に自動追従する：データ行を追加してもテーブル範囲が自動拡張されるため、数式を修正する必要がありません。これは速度面だけでなく保守性の面でも大きなメリットです。

❌ 列全体参照

=VLOOKUP(A2, マスタ!A:E, 3, FALSE)

104万行×5列をスキャン対象に見えるが、実際にはUsedRangeの末尾まで。
ただしUsedRangeが肥大化していると無駄な走査が発生する。

✅ テーブル参照

=VLOOKUP(A2, マスタテーブル, 3, FALSE)

テーブルの実データ範囲（500行×5列）だけを参照。
行の追加にも自動追従。キャッシュ共有も完璧。

📝 列全体参照（A:A）は本当に104万行スキャンするのか？
VLOOKUP・MATCH・XLOOKUPなどの標準検索関数は、列全体参照（A:A）を指定してもUsedRangeの末尾で走査を停止します。データが500行しかなければ、残りの103万行分の空白セルは走査されません。

ただし以下のケースではUsedRangeが肥大化し、実質的に広範囲を走査することになります：
・最終行付近にデータや書式が残っている（例：誰かが1048576行目にセル書式を設定した）
・大量の行を削除した後にファイルを保存していない（UsedRangeが縮まない）

また、SUMPRODUCTやレガシーCSE配列数式（Ctrl+Shift+Enter）はUsedRangeに関係なく列全体を走査するため、列全体参照は避けるべきです。テーブル参照ならUsedRangeに依存せず常にデータ行のみを対象にするため、最も安全です。

✅ テーブル化は「最もコストの低い高速化」
数式を一切変えなくても、データをテーブル化して構造化参照に切り替えるだけで、 列全体参照比で5〜10倍、範囲指定比でも2〜3倍の高速化が期待できます。数式の最適化に取り組む前に、まずデータのテーブル化を検討してください。

【2】LET関数 ― 同じ計算を1回だけ実行して再利用する

LET関数（Excel 2021以降）は、数式内で変数を定義し、その結果を複数回参照できる関数です。 同じ部分式が数式内に複数回登場する場合、LETで1回だけ計算して使い回すことで、計算コストを削減できます。

LET関数の基本構文

=LET(
  変数名1, 計算式1,
  変数名2, 計算式2,
  最終的な結果の式
)

変数名に計算結果を格納し、以降の式で何度でも参照可能。各変数は1回だけ計算される。

❌ LETなし ― MATCHが2回実行される

=IF(MATCH(A2,D:D,0)>0, INDEX(E:E,MATCH(A2,D:D,0)), "未登録")

MATCH(A2,D:D,0) が2回書かれている → 2回とも独立して計算される。
10万行のD列を2回スキャンすることになる。

✅ LETあり ― MATCHは1回だけ

=LET(pos, MATCH(A2,D:D,0), IF(pos>0, INDEX(E:E,pos), "未登録"))

MATCHの結果を pos に格納し、IF と INDEX の両方で再利用。
10万行のスキャンは1回だけ。

LETが特に効果を発揮するケース

✅ SORT / FILTER の結果を複数回使う

SORT/FILTER の計算結果は配列全体のソートやフィルタリングなので重い処理です。 LETで1回だけ実行してCHOOSECOLSで列を取り出せば、同じSORT処理が何度も走ることを防げます。

✅ 複雑な条件式を複数箇所で使う

例えば IF(AND(B2>100, C2="A"), ...) のような条件を数式内の複数箇所で参照する場合、 LETで条件結果をTRUE/FALSEの変数に格納すれば、条件評価が1回で済みます。

✅ 可読性の向上

速度だけでなく、長大なネスト数式が変数名で分解されるため、保守性・デバッグ効率も大幅に向上します。

💡 LET関数自体のオーバーヘッドはほぼゼロ
Reddit（r/excel）やMicrosoft Tech Communityでの検証によると、 LET関数自体が追加するオーバーヘッドは計測不能なレベルです。「LETで包んだから遅くなる」ということはなく、重複計算を避けた分だけ純粋に速くなると考えてよいでしょう。ただし、LETの中で定義した変数を1回しか使わない場合は速度改善の意味がないため、その場合は可読性向上のための利用と割り切ってください。

【3】セル参照コスト vs セル内再計算 ― 「作業セルに保存」が常に最速とは限らない

Microsoft公式ドキュメントでは「重複する計算は作業セル（ヘルパー列）に分離して再利用せよ」と推奨されています。しかし実際には、セル参照自体にもコストがあるため、データ規模によって最適解が変わります。

🔴 この節で最も伝えたいこと：「セル参照はタダではない」
Excelの計算エンジンは、セルを参照するたびに依存関係の追跡・値の読み取り・型変換などの処理を行います。数式内で同じ計算を2回実行するコストと、作業セルに結果を保存して参照するコストのどちらが大きいかは、 「計算の重さ」と「データ行数」のバランスで決まります。

ケーススタディ：MATCH結果を作業セルに保存すべきか？

典型的なパターンとして、同じマスタから3列を取得するINDEX+MATCHを考えます。

方法A：作業セルにMATCH結果を保存

H2：=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個の数式

方法B：数式内でMATCHを毎回実行

I2：=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の計算（10万行スキャン）が 1回で済む効果が大きい	10万行スキャンを 3回×1万行 = 3万回実行	方法A（作業セル保存）の方が速い

⚡ なぜ逆転が起こるのか
セル参照のコストはデータ規模に依存しない「固定コスト」です（依存関係の解決・値の読み取りは一定時間）。一方、MATCH関数の計算コストはマスタの行数に比例する「変動コスト」です。

マスタが小さいとき → MATCHの計算コストは小さいので、3回実行してもセル参照1回分のコストと大差ない。むしろ作業セルの参照・依存関係追跡のオーバーヘッドが目立つ。
マスタが大きいとき → MATCHの1回あたりの計算コストが大きくなるため、3回実行を1回に減らす効果が圧倒的に勝る。

✅ 実務での判断基準
・マスタが1万行以上、かつ同じMATCH結果を3回以上使う → 作業セルに保存が有利
・マスタが数千行以下、かつ使い回しが2回程度 → セル内で再計算しても問題なし（むしろシートがすっきりする）
・LET関数が使える環境（2021以降）なら → LETで変数化するのが最善。作業セルも不要、再計算も1回で済む

迷ったらLET関数を使うのが最もバランスの良い選択です。作業セルを消費せず、再計算を防ぎ、可読性も高い。

Microsoft公式ドキュメントの推奨：「中間結果を行・列に分離せよ」

Microsoft公式のパフォーマンスガイド（「Excel performance: Improving calculation performance」）では、 「重複する計算を作業セルに分離して1回だけ計算し、他の数式で参照する」ことが第一のルールとして明記されています。

この推奨は正しいのですが、小規模データでは「作業セル分離のコスト > 重複計算のコスト」になるケースがあることは公式ドキュメントには書かれていません。公式の推奨は主に大規模データ（数万行以上）を想定していると理解してください。

📝 公式ドキュメントの例：期首からの累計SUM
B1=SUM($A$1:$A1) を2,000行にコピーすると、のべ200万セルの参照が発生し80ミリ秒。 C1=A1, C2=C1+A2 と前行の結果を加算するだけなら、のべ3,999セルの参照で3.7ミリ秒。 約22倍の高速化。これが「中間結果を活用する」効果の典型例です。
ただし、この例は「SUM範囲が行ごとに異なる = キャッシュが効かない」パターンであるため効果が大きいのであって、すべてのケースで22倍になるわけではありません。

【4】FILTER関数で一括抽出 ― 同じ条件のVLOOKUPを何百行もコピーしない

FILTER関数（Excel 2021以降）は、条件に一致するすべての行をまとめて一括抽出する関数です。「特定の部署の社員一覧を取得する」「特定の月の売上データを抜き出す」といった用途では、 VLOOKUPやXLOOKUPを何百行もコピーするよりも、FILTER関数を1つ置くだけで済む場合があります。

VLOOKUPの繰り返し vs FILTER一発 ― 速度とファイルサイズの比較

❌ 従来：同じ条件のXLOOKUPを何百行もコピー

「営業部」の社員を50件取得するために：

B2：=XLOOKUP(条件, ...)（1件目）
B3：=XLOOKUP(条件, ...)（2件目）
︙
B51：=XLOOKUP(条件, ...)（50件目）

50個の独立した数式。各数式が独立してマスタ全体をスキャン。
ファイルに50個分の数式データが保存される。

✅ FILTER関数で1発抽出

=FILTER(マスタ!A:D, マスタ!C:C="営業部", "該当なし")

数式は1個だけ。マスタを1回スキャンして条件に合う全行をスピルで出力。
ファイルに保存されるのは1個の数式だけ。

⚡ FILTERが速い理由
・マスタのスキャンが1回で済む（50個のXLOOKUPは各々がスキャンする）
・結果は1つの配列としてメモリに展開されるため、セルごとの依存関係追跡のオーバーヘッドがない
・Redditの検証（r/excel）では、FILTERはXLOOKUPに比べて約30%高速という結果も報告されている

ただし、FILTERは「条件に合う全行」を返すのに対し、VLOOKUPは「最初の1件」を返すという違いがあります。用途が異なるため、「同じ条件で複数件を取得する」場面でFILTERが特に有利です。

FILTERが不利になるケース

FILTERはあらゆる場面でVLOOKUPより速いわけではありません。以下のケースでは注意が必要です。

⚠ 結果の件数が不定

FILTERの結果はスピルで展開されるため、出力先のセルが空いている必要があります。結果件数が可変であるため、レイアウトが固定のシートでは使いにくい場合があります。

⚠ 多数のFILTER数式がある場合

FILTER自体はマスタ全体をスキャンするため、異なる条件のFILTER数式が何十個もある場合は、それぞれがマスタ全体をスキャンします。条件ごとのVLOOKUPと比較して大差がないこともあります。

⚠ 1件だけの完全一致検索

「IDで1件だけ引く」用途ではVLOOKUPやXLOOKUPの方が適切です。 FILTERは「条件に一致する全行」を返すため、1件取得にはオーバースペックです。

✅ FILTERの使いどころまとめ
・同じ条件で複数件の結果を取得したい → FILTER が圧倒的に適切
・IDで1件だけ取得したい → XLOOKUP / INDEX+MATCH の方が適切
・複数条件の組み合わせで抽出したい → FILTER + 論理演算（*や+）が柔軟で高速

【5】スピル検索 ― 1個の数式で15,000行分のVLOOKUPを置き換える

Excel 2021以降では、XLOOKUPやVLOOKUPの検索値に配列（複数セルの範囲）を渡すことで、 1個の数式から結果がスピル（自動展開）されます。これは速度面に加えて、ファイルサイズの劇的な削減という隠れた大きなメリットがあります。

従来方式 vs スピル検索 ― 15,000行のVLOOKUPで比較

❌ 従来：15,000行に数式をコピー

B1：=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の数式データ。

✅ スピル検索：1個の数式で全行を処理

B1：=XLOOKUP(A1:A15000, マスタ!A:A, マスタ!C:C)

数式は1個だけ。結果がB1:B15000にスピルで自動展開。
ファイルに保存されるのは1個の数式データのみ。

ファイルサイズの劇的な削減

Excelのxlsxファイル形式は、内部的にはXMLファイルをZIP圧縮したものです。各セルの数式はXMLとして1つ1つ記録されます。 15,000行の数式をコピーすると、15,000個の <f> タグがXMLに書かれます。

スピル検索では数式が1個だけなので、XMLに記録される <f> タグも1個だけです。スピルで展開された結果セルはキャッシュ値として保存されますが、数式そのもののデータ量は激減します。

ファイルサイズの比較イメージ（15,000行のXLOOKUP）

従来方式：15,000個の数式

数式データ（15,000個の<f>タグ）

値キャッシュ

他

スピル検索：1個の数式

数式（1個）

値キャッシュ

未使用

✅ 実測例
15,000行の XLOOKUP をコピーしたブック → 約3〜5MB
同じ検索をスピル1数式に置き換えたブック → 約500KB〜1MB
ファイルサイズが3〜5分の1に圧縮されるケースが珍しくありません。
ファイルサイズの削減は、ネットワーク越しの共有やメール添付でも実用的なメリットがあります。

スピル検索の速度特性 ― 必ずしも「常に高速」ではない

スピル検索は1個の数式で全行を処理しますが、速度面では「常に個別数式より速い」とは限りません。

スピル検索 vs 個別数式 ― 速度の傾向

観点	スピル検索（1数式）	個別数式（N行コピー）
初回計算	全行を一括計算（並列化の恩恵を受けにくい場合あり）	マルチスレッドで並列計算されやすい
1セル変更時の再計算	⚠ 検索値が1つ変わっても全行が再計算される	✅ 変更されたセルの数式だけが再計算される
ファイルサイズ	✅ 圧倒的に小さい	⚠ 数式数に比例して大きい
数式の数	✅ 1個	⚠ N個
保守性	✅ 修正は1箇所	⚠ N箇所に影響

⚠️ スピル検索の最大の注意点：1セル変更でも全行再計算になる
個別数式なら「A5だけ変更 → B5だけ再計算」ですが、スピル数式は「A5が変更 → B1:B15000全体が再計算」されます。 検索値が頻繁に変わるシートでは、この再計算範囲の広さが速度低下の原因になることがあります。

逆に、「一度データを入力したらあまり変更しない」シート（月次集計レポートなど）では、再計算頻度が低いためスピル検索のデメリットはほぼなく、ファイルサイズ削減と保守性のメリットだけが残ります。

✅ スピル検索が特に有効な場面
・データ入力後にあまり変更しない帳票・レポート系のシート
・ファイルサイズを小さくしたい（メール添付・ネットワーク共有）
・数式の保守コストを下げたい（修正は1箇所だけ）
・XLOOKUPのバイナリ検索と組み合わせる場合（元々高速なので再計算コストが小さい）

【6】事前IF空白チェック ― スピルと個別セルで効果が真逆になる

検索値が空白のときにVLOOKUP/XLOOKUPの実行をスキップする「事前IFチェック」は、 スピル式と個別セル数式で効果がまったく異なります。これはExcel の IF関数の評価方式の違いに起因しています。

個別セル数式（非スピル）：✅ 高速化する

各セルに入力してコピーする従来方式では、事前IFは確実に高速化します。

❌ IFなし ― 空白行でもVLOOKUPが走る

=VLOOKUP(A2, マスタ!D:E, 2, FALSE)

A2が空白でもVLOOKUPがマスタ全体をスキャンし、
結果として#N/Aエラーまたは意図しない値を返す。
15,000行のうち5,000行が空白なら、
5,000回分のVLOOKUPが無駄に実行される。

✅ 事前IFあり ― 空白行はVLOOKUPをスキップ

=IF(A2="", "", VLOOKUP(A2, マスタ!D:E, 2, FALSE))

A2が空白の場合、IFの短絡評価により
VLOOKUPは一切実行されない。
5,000回分のVLOOKUPがまるごとスキップされる。

⚡ なぜ個別セルでは高速化するのか ― IF関数の「短絡評価」
Excel の IF 関数は短絡評価（ショートサーキット）を行います。 =IF(条件, 真の式, 偽の式) において、条件がTRUEなら「真の式」だけが評価され、「偽の式」は一切評価されません。逆も同様です。

これはMicrosoft Tech Community（JKPieterse氏のVBA UDFテスト）や Stack Overflow（=IF(TRUE, "成功", IF(TRUE, 0/0, LOG(-1))) でエラーが発生しないテスト）で確実に検証されています。

つまり =IF(A2="", "", VLOOKUP(...)) でA2が空白なら、 VLOOKUPの数式解析すら行われず、計算コストは実質ゼロです。

スピル式：❌ 高速化しない（むしろ遅くなりうる）

スピル式で同じIFチェックを行っても、高速化効果はありません。

❌ スピルの事前IF ― 効果なし

=IF(A1:A15000="", "", XLOOKUP(A1:A15000, マスタ!D:D, マスタ!E:E, "", 0))

IFの条件が配列のため、短絡評価が働かない。
XLOOKUP は15,000行分すべて先に計算され、
その後IFが各行の結果を振り分けるだけ。
IFの条件チェック分のオーバーヘッドが上乗せされる。

✅ スピルではIFなしの方がよい

=XLOOKUP(A1:A15000, マスタ!D:D, マスタ!E:E, "", 0)

第4引数 if_not_found に "" を指定。
空白検索値がマスタに該当しなければ自動で""を返す。
IFのオーバーヘッドもなく、最もシンプル。

🔴 配列IFには短絡評価がない ― 検証済みの事実
Reddit（u/uhlyeiss, 2024年9月）の検証で、配列の =IF({TRUE,TRUE}, {5,6}, HSTACK({7}, A1)) が循環参照エラーを発生させることが確認されています。条件がすべてTRUEであっても、FALSE側の HSTACK({7}, A1) が評価されていた証拠です。

非配列の =IF(TRUE, 5, A1) では循環参照エラーは発生せず、短絡評価が正常に機能しています。

つまり配列（スピル）モードのIFは、TRUEブランチもFALSEブランチも両方とも全要素を計算してから結果を振り分けます。

事前IF空白チェックの効果 ― 比較まとめ

方式	IFの評価	空白行のVLOOKUP	速度効果
個別セル数式 + 事前IF	短絡評価あり	スキップされる	✅ 高速化する
スピル式 + 事前IF	短絡評価なし	全行実行される	❌ 効果なし（やや遅い）
スピル式 + if_not_found=""	IF不使用	全行実行される	IFオーバーヘッドなし
スピル式 + 最終行カウント	IF不使用	空白行が範囲外	✅ 最も高速（次節で解説）

✅ 結論
・個別セル数式（非スピル）を使う場合 → =IF(A2="","", VLOOKUP(...)) は確実に高速化する。空白率が高いほど効果大。
・スピル式を使う場合 → 事前IFは効果なし。代わりに最終行カウントで範囲を絞るのが最善。

【7】最終行カウント ― スピル式の「本当の高速化」はここにある

スピル式の高速化で最も効果的なのは、事前IFではなく、 入力配列のサイズ自体を最小化することです。空白行をIFでスキップするのではなく、そもそも空白行を含まない範囲だけを渡します。

基本形：COUNTAで最終行を取得してスピル範囲を絞る

❌ 固定範囲 ― 空白行も含む

=XLOOKUP(A1:A15000, マスタ!D:D, マスタ!E:E, "", 0)

データが8,000行しかなくても、残り7,000行分の空白に対して
XLOOKUPの検索処理が走る（結果は""だが処理は実行される）。
再計算時も15,000行分が対象になる。

✅ 最終行カウント ― データ行だけを処理

=XLOOKUP(A1:INDEX(A:A, COUNTA(A:A)), マスタ!D:D, マスタ!E:E, "", 0)

COUNTA(A:A) が8,000を返し、A1:A8000 だけが処理対象。
7,000回分の検索がまるごとカットされる。
再計算時も8,000行分だけが対象。

⚡ 高速化の理由はシンプル
① 検索回数の削減：配列サイズが8,000なら8,000回、15,000なら15,000回の検索処理が走る。実データ行に絞れば無駄な検索がなくなる。
② 再計算コストの削減：スピル式は変更があると配列全体を再計算する。配列が小さいほど再計算も速い。
③ 事前IFが完全に不要：空白行がそもそも範囲に含まれないため、IFによる空白チェック自体が不要。IFのオーバーヘッドもゼロ。

LETとの組み合わせ（推奨形）

LETで最終行計算を変数化することで、可読性が高く保守しやすい数式になります。

✅ 推奨：LET + COUNTA + XLOOKUP

=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配列計算のコスト
MAX((A:A<>"")*ROW(A:A)) は列全体に対する配列演算で、それ自体にもコストがあります。データに途中空白がないことが保証されているなら COUNTA の方が軽量です。
途中空白が発生しないようにデータを管理するのがベストプラクティスです。

スピル最適化の3手法 ― 比較まとめ

手法	速度効果	仕組み	注意点
事前IF	❌ 効果なし	配列IFに短絡評価がないため全行の検索が実行される	IFのオーバーヘッド分だけわずかに遅くなる可能性
if_not_found=""	△ 速度は同じ	全行検索は実行されるが IFのオーバーヘッドはない	速度改善にはならないが最もシンプルな記述
最終行カウント	✅ 高速化する	空白行を配列から除外し検索回数自体を減らす	途中空白ありの場合は COUNTAの代わりにMATCH/MAX

✅ スピル式の高速化はこれで決まり
スピル式を使う場合の高速化は「最終行カウントで範囲を絞る」一択です。
事前IFは効果がなく、if_not_foundは速度を変えません。
範囲を絞れば検索回数も再計算範囲もメモリ使用量も減り、すべての面で有利です。

個別セル数式を使う場合は=IF(A2="","", VLOOKUP(...))の事前IFが有効です。スピルと個別で最適解が異なることを覚えておいてください。

【8】最終まとめ ― どのテクニックをいつ使うか

テクニック選択の判断フロー

データがテーブル化
されていない？

→

まずテーブル化する
数式を変えずに2〜3倍速くなる

↓ テーブル化済み

同じ部分式が
数式内に複数回ある？

→ Yes

LET関数で変数化
重複計算を排除

↓

条件に合う
「複数行」を取得？

→ Yes

FILTER関数で一括抽出
VLOOKUPを何百行もコピーしない

↓

同じ検索を
大量の行で実行？

→ Yes

スピル検索 + 最終行カウント
ファイルサイズ削減＋範囲絞りで高速化

↓ 個別数式を使う場合

検索値に空白行が
多い？

→ Yes

事前IF空白チェック
個別セルのIFは短絡評価で高速化

↓

マスタが10万行超で
MATCH結果を3回以上使う？

→ Yes

作業セルにMATCH結果を保存
大規模データではセル参照コスト
より再計算コストの方が大きい

テクニック総合比較

テクニック	速度改善効果	ファイルサイズ	保守性	対応ver	手間
テーブル化	✅ 2〜10倍速	変化なし	✅ 大幅向上	全ver	✅ Ctrl+Tだけ
LET関数	✅ 重複分だけ削減	変化なし	✅ 可読性向上	2021以降	数式書き換え
作業セル分離	✅ 大規模データで有効	⚠ 列が増える	やや複雑になる	全ver	列追加＋数式変更
FILTER一括抽出	✅ 複数件取得で有効	✅ 数式1個で済む	✅ 修正1箇所	2021以降	✅ 数式置換だけ
スピル検索	場合による	✅ 劇的に小さい	✅ 修正1箇所	2021以降	✅ 数式置換だけ
事前IF（個別セル）	✅ 空白率に比例	変化なし	数式がやや長くなる	全ver	数式にIF追加
事前IF（スピル）	❌ 効果なし	変化なし	× 無駄に複雑	―	やらない方がよい
最終行カウント（スピル）	✅ 空白率に比例	✅ さらに圧縮	✅ LET併用で良好	2021以降	数式書き換え

✅ 優先順位の結論
① テーブル化（Ctrl+T）：数式を変えずに最も簡単に高速化できる。全員がまずやるべき。
② LET関数で重複排除：数式内の同じ計算を1回にまとめる。2021以降で最もバランスの良い最適化。
③ FILTER / スピル検索への置き換え：用途に応じて数式数を劇的に減らし、ファイルサイズも削減。スピル式には最終行カウントを必ず併用。
④ 個別セル数式の事前IF：スピルを使わない場合の空白行スキップに有効。短絡評価で確実に高速化。
⑤ 作業セル分離：大規模データで効果大。ただしLETが使えるならLETの方がシートが汚れない。
⑥ バイナリ検索：ソート可能なデータなら桁違いの高速化（前回記事参照）。

数式の速度UPテクニック集：テーブル・LET・セル参照コスト・FILTER・スピル検索・事前IF・最終行カウント

【1】テーブル（構造化参照）― 「何もしなくても速くなる」最強の土台

【2】LET関数 ― 同じ計算を1回だけ実行して再利用する

【3】セル参照コスト vs セル内再計算 ― 「作業セルに保存」が常に最速とは限らない

【4】FILTER関数で一括抽出 ― 同じ条件のVLOOKUPを何百行もコピーしない

【5】スピル検索 ― 1個の数式で15,000行分のVLOOKUPを置き換える

【6】事前IF空白チェック ― スピルと個別セルで効果が真逆になる

【7】最終行カウント ― スピル式の「本当の高速化」はここにある

【8】最終まとめ ― どのテクニックをいつ使うか

🔗 関連記事

速度・パフォーマンス

関連する関数の詳細解説