LET関数 深掘り②:SORT / SORTBY の重い処理を変数化して高速化

💡 やりたいこと

SORT / SORTBY / FILTER などのスピル関数は、大量データに対して実行するとそれなりに重い処理です。同じソート結果を複数の場所で使いたい場合、数式内で何度も SORTBY(...) を書くと計算が倍々に膨れ上がります。LETで1回だけソートし、変数として使い回すテクニック ── そしてそれでも限界がある場合に「作業シートに出すべき」という判断基準を解説します。

"よくあるダメな例":同じソートを複数回

売上データを金額降順にソートし、上位3件の「商品名」と「金額」を横並びで表示したいケース。

❌ 6セルで同じSORTBYが6回実行される
D2(1位の商品名): =INDEX(SORTBY(B2:B1000, C2:C1000, -1), 1) E2(1位の金額): =INDEX(SORTBY(C2:C1000, C2:C1000, -1), 1) D3(2位の商品名): =INDEX(SORTBY(B2:B1000, C2:C1000, -1), 2) E3(2位の金額): =INDEX(SORTBY(C2:C1000, C2:C1000, -1), 2) ... D4, E4 も同様(計6セルで SORTBY が6回)

同じ範囲のソート式が6セルにあります。処理時間は環境とデータに左右されるため、同じブックで再計算時間を比較してください。

LET で1回のソート結果を使い回す

1つのセルに LET + SORTBY を書き、結果をスピルさせる方法です。

=LET( データ, SORTBY(A2:C1000, C2:C1000, -1), 上位3, INDEX(データ, SEQUENCE(3), SEQUENCE(1,3)), 上位3 )

SORTBYは 1回だけ 実行。変数「データ」にソート済み配列が入り、INDEX + SEQUENCE で上位3行×3列を切り出してスピル表示します。

⚡ 効果
ソート式を6セルから1つのスピル式へまとめます。改善量はExcelの版、データ、他の数式やPC性能で変わるため、実測して判断します。

応用:FILTER → SORT を LET で1回にまとめる

「特定カテゴリだけを抽出してからソートする」というパイプライン処理も、LETで変数をつなげると綺麗に書けます。

=LET( カテゴリ, "食品", 条件, A2:A1000=カテゴリ, 件数, SUM(--条件), IF(件数=0, "該当なし", LET( 抽出, FILTER(A2:D1000, 条件), 並替, SORTBY(抽出, INDEX(抽出,,4), -1), INDEX(並替, SEQUENCE(MIN(5,件数)), SEQUENCE(1,COLUMNS(並替))) ) ) )

先に件数を数え、0件なら並べ替えず「該当なし」を表示します。1~4件なら存在する行だけ、5件以上なら上位5件を返します。INDEXとSEQUENCEを使うためExcel 2021でも利用できます。並べ替えるD列は数値、A列はカテゴリ文字列を前提にしています。入力範囲にエラー値がある場合は、元データを確認してください。

🔧 デバッグのコツ
途中経過を確認したいときは、内側のLETの最後のINDEX式を「抽出」または「並替」に差し替えます。0件の分岐を残したまま各段階の結果を確認できます。

それでも「作業シートに出すべき」ケース

LETは万能ではありません。以下のような場合は、素直に作業シートに SORTBY / FILTER の結果をスピルさせ、他のシートからそれを参照するほうが圧倒的に有利です。

ケース①:ソート結果を複数のセル・シートから参照する

LETの変数はそのセルの中でしか使えません。ダッシュボードのグラフ、ピボット的な集計表、帳票シートなど複数の場所から同じソート結果を使うなら、作業シートに1回だけスピルさせてそこを参照する構成がベストです。

元データ → 作業シート(SORTBY 1回) → グラフ / 集計表 / 帳票

ケース②:再計算の待ち時間が長い大規模処理

一律の行数で関数の限界は決まりません。実測した待ち時間が業務に支障を出す場合や、更新時だけ整形すればよい場合は、Power Queryで事前にソート・フィルターを済ませる構成も比較します。

元データ → 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件なら並べ替えを実行しません。

=LET( ソートキー列, IF(G1="金額", 4, 3), 条件, A2:A1000=G2, 件数, SUM(--条件), IF(G2="", "カテゴリを入力", IF(件数=0, "該当なし", LET( データ, FILTER(A2:D1000, 条件), 並替, SORTBY(データ, INDEX(データ,,ソートキー列), -1), INDEX(並替, SEQUENCE(MIN(10,件数)), SEQUENCE(1,COLUMNS(並替))) ) ) ) )

セルG1に「金額」と入力すればD列で、それ以外ならC列でソート。G2でカテゴリを切り替えられるインタラクティブなダッシュボードを関数だけで構築できます。

旧バージョンでの代替法

LET・SORTBY・FILTERが使えない買い切り版Excel 2019以前では、作業シートにLARGE / SMALL + INDEX / MATCHでランキングを構築するか、Power Queryでソート済みテーブルを出力します。Windows版Excel 2016以降にはPower Queryが標準搭載されていますが、Mac版や古い版では機能差があります。