LET関数 深掘り②:SORT / SORTBY の重い処理を変数化して高速化
💡 やりたいこと
SORT / SORTBY / FILTER などのスピル関数は、大量データに対して実行するとそれなりに重い処理です。同じソート結果を複数の場所で使いたい場合、数式内で何度も SORTBY(...) を書くと計算が倍々に膨れ上がります。LETで1回だけソートし、変数として使い回すテクニック ── そしてそれでも限界がある場合に「作業シートに出すべき」という判断基準を解説します。
"よくあるダメな例":同じソートを複数回
売上データを金額降順にソートし、上位3件の「商品名」と「金額」を横並びで表示したいケース。
❌ 6セルで同じSORTBYが6回実行される同じ範囲のソート式が6セルにあります。処理時間は環境とデータに左右されるため、同じブックで再計算時間を比較してください。
LET で1回のソート結果を使い回す
1つのセルに LET + SORTBY を書き、結果をスピルさせる方法です。
SORTBYは 1回だけ 実行。変数「データ」にソート済み配列が入り、INDEX + SEQUENCE で上位3行×3列を切り出してスピル表示します。
ソート式を6セルから1つのスピル式へまとめます。改善量はExcelの版、データ、他の数式やPC性能で変わるため、実測して判断します。
応用:FILTER → SORT を LET で1回にまとめる
「特定カテゴリだけを抽出してからソートする」というパイプライン処理も、LETで変数をつなげると綺麗に書けます。
先に件数を数え、0件なら並べ替えず「該当なし」を表示します。1~4件なら存在する行だけ、5件以上なら上位5件を返します。INDEXとSEQUENCEを使うためExcel 2021でも利用できます。並べ替えるD列は数値、A列はカテゴリ文字列を前提にしています。入力範囲にエラー値がある場合は、元データを確認してください。
途中経過を確認したいときは、内側のLETの最後のINDEX式を「抽出」または「並替」に差し替えます。0件の分岐を残したまま各段階の結果を確認できます。
それでも「作業シートに出すべき」ケース
LETは万能ではありません。以下のような場合は、素直に作業シートに SORTBY / FILTER の結果をスピルさせ、他のシートからそれを参照するほうが圧倒的に有利です。
ケース①:ソート結果を複数のセル・シートから参照する
LETの変数はそのセルの中でしか使えません。ダッシュボードのグラフ、ピボット的な集計表、帳票シートなど複数の場所から同じソート結果を使うなら、作業シートに1回だけスピルさせてそこを参照する構成がベストです。
ケース②:再計算の待ち時間が長い大規模処理
一律の行数で関数の限界は決まりません。実測した待ち時間が業務に支障を出す場合や、更新時だけ整形すればよい場合は、Power Queryで事前にソート・フィルターを済ませる構成も比較します。
(ソート+フィルター) → 出力テーブル → 関数で参照
ケース③:同じ数式が行ごとに入る場合
A列の各行に =LET(データ, SORTBY(...), ...) と入れると、行数ぶんだけ SORTBY が実行されます。LET は「1セル内の重複」を防ぐだけで、「他のセルとの重複」は防げません。この場合は作業シートに SORTBY を1回だけ書き、各行からはその結果を INDEX / MATCH で引くべきです。
判断基準:LET?作業シート?Power Query?
| 状況 | 最適な方法 | 理由 |
|---|---|---|
| 1つのセル内で同じソート結果を複数回参照 | LET | 変数化で重複評価を排除。最もシンプル |
| 複数セル・複数シートから同じソート結果を参照 | 作業シート | LETの変数はセル外から参照不可。作業シートに1回スピルが正解 |
| 行ごとに同じSORT/FILTERが入る数式 | 作業シート | LETでは行間の重複を防げない |
| 再計算時間が業務に支障を出す処理 | Power Queryも比較 | 更新時だけ加工する構成と、再計算で追従する構成を実測比較 |
| FILTER → SORTBY → INDEXのパイプライン処理 | LET(結果が1箇所なら) | 0件分岐と途中結果を名前で確認できる |
発展:セルの値で並べ替え条件を切り替える
G1で並べ替え列、G2でカテゴリを選ぶ例です。Excel 2021対応の関数だけで構成しています。G2が空欄のときは条件の入力を求め、該当0件なら並べ替えを実行しません。
セルG1に「金額」と入力すればD列で、それ以外ならC列でソート。G2でカテゴリを切り替えられるインタラクティブなダッシュボードを関数だけで構築できます。
旧バージョンでの代替法
LET・SORTBY・FILTERが使えない買い切り版Excel 2019以前では、作業シートにLARGE / SMALL + INDEX / MATCHでランキングを構築するか、Power Queryでソート済みテーブルを出力します。Windows版Excel 2016以降にはPower Queryが標準搭載されていますが、Mac版や古い版では機能差があります。