【Excel 2021執念のハック】BYROW / LAMBDAなしで「行ごと集計・ソート・リセット連番」を一発スピルさせる魔改造数式集

セール中

2026年9月9日 12:42

はじめに：あの「#NAME?」の絶望から、すべては始まった

Excel 2021がリリースされ、「スピル関数」の存在を知ったとき、私は身体が震えるほどの衝撃を受けました。

それまでの実務では、データの増減に対応するために、あらかじめ多めの行に数式をコピーしておくしかありませんでした。当然、ファイルの動作は重く、鈍くなる一方。それでも突然データが予想以上に増えれば慌てて数式をドラッグし、一部だけコピーを忘れて計算がズレる……そんな弊害だらけの毎日に、ようやく終止符が打たれる。

「データのサイズに応じて、1つの数式が自動で伸び縮みする。これでもう、数式のコピー漏れに怯える日々は終わった。Excelライフは安泰だ」

そう確信したのも束の間、私は最初の壁にぶち当たりました。
行ごとの「横の足し算」をさせようとスピル範囲に SUM をかけた瞬間、すべての行が合算された巨大な数字が出現したのです。

慌ててネットの海を捜索すると、解決策が見つかりました。
「行ごとの集計をするには、最新の BYROW 関数と LAMBDA 関数を組み合わせれば一発です！」

胸を撫でおろし、その通りに数式を入力した私を待っていたのは、画面を埋め尽くす非情なエラーでした。

「 #NAME ? #NAME ? #NAME ? #NAME ? …… 」

「なんだと……？」
頭が真っ白になりました。さらに調査を続けた結果、冷酷な事実を知ることになります。
それらの関数は、当時のMicrosoft 365（サブスクリプション版）でしか使えない、Excel 2021には未実装の関数だったのです。

激しい失望が襲いました。データの自動展開という未来の技術を目の前にぶら下げられながら、「横の足し算や、横の最小値すら一発で出せない」という、あまりにも不条理な縛りプレイ。

幸い、横のSUM（合計）に関しては、ネット上に MMULT 関数を使った代替策を見つけることができ、なんとか命拾いをしました。しかし、本当に恐ろしいのはここからでした。

「行ごとの最小値（MIN）」は、完全にアウトだったのです。

どう検索しても、どの質問広場を覗いても、出てくる回答は「365に移行してください」「VBA（マクロ）を使ってください」「おとなしく数式を下にドラッグしてください」ばかり。

「スピルを使って、1セルで完結させたいんだ。マクロ禁止の環境でも動く数式が欲しいんだ……！」

ネットも、AIも、誰も答えを持っていない。頼れるのは自分しかいない。
Excel 2021を手にして終わるはずだった戦いは、終わっていなかった。むしろ、孤独で長い戦いが、そのとき幕を開けたのでした。

──それから数年の試行錯誤を経て、私はついに、Excel 2021の限界を突破する数式ロジックを生み出しました。

本書は、Microsoft 365への移行を許されない環境で、同じ絶望を抱えながら戦うごく少数の人々に捧げる、「BYROWもLAMBDAも使わない、執念のスピル数式バイブル」です。

小手調べ：Excel 2021で「行ごとの和」をスピルさせる

まずは小手調べとして、私がExcel 2021の縛り環境の中で最初に突破口を開いた「行ごとの和（合計）」の数式を公開します。

上の図でG3以下に横の和をスピルさせたい場合、通常、365環境であれば =BYROW(B3:F9, LAMBDA(r, SUM(r))) と書くところですが、2021ではこれを使うと #NAME ? エラーの洗礼を受けます。かといって単純に =SUM(B3:F9) とすると、指定した範囲のすべての数字が合算された1つの巨大な値になってしまい、行ごとの集計になりません。

これを2021のスピルで解決するための数式が、こちらです。

excel

=LET(x,B3:F9,
    C,COLUMNS(x),
    Kekka,MMULT(x,SEQUENCE(C,,,0)),
Kekka)

この数式の仕組み

この数式は、行列の掛け算を行う MMULT 関数と、連番を生成する SEQUENCE 関数を組み合わせています。
集計したい範囲（5列分）に対して、SEQUENCE 関数を使って「1が縦に5つ並んだ配列（1; 1; 1; 1; 1）」を作り、それらを掛け合わせることで、「各行の値を1倍して足し算する」という処理を擬似的にスピルさせています。もし、行が増える可能性がある場合は次のようにcountでデータ行数を取得して、xに代入する範囲を行によって可変にします。（※データは連続で入力されていることを前提とします。以下同じ）

excel

=LET(x,OFFSET(B2,1,,COUNT(B:B),5),
    C,COLUMNS(x),
    Kekka,MMULT(x,SEQUENCE(C,,,0)),
Kekka)

ちなみにOFFSETの起点をB3でなくB2にしているのは、項目名の行より下の生のデータエリアには行の追加を行うことも想定した小さな配慮です。

「なんだ、MMULTを使う方法はネットで見かけたことがあるぞ」と思った方もいるかもしれません。

確かに、この「和」のロジックまでは、ネットの海を深く潜れば先人たちが残した足跡に辿り着くことができます。私もこれを見つけた瞬間は、「これで全ての行ごと集計は解決だ！」と思いました。

――しかし、本当の絶望はここからだったのです。

「和」が解けても、「最小値」と「ソート」の壁は絶対に超えられない

「和のロジックがMMULTで解けるなら、同じ要領で『最小値（MIN）』や『最大値（MAX）』もいけるはずだ」

そう確信して数式を組み替えようとした瞬間、私は凍りつきました。
MMULT はあくまで「掛け算と足し算」を行う関数です。「範囲の中から一番小さい値（または大きい値）を比較して選ぶ」という処理は、逆立ちしても実行できません。

さらに、実務で本当にやりたかった「行ごとのデータを並び替える（ソート）」や、縦データにおける「グループが変わったら1にリセットされる連番（SCAN代替）」、セル内の「文字列を区切り文字でスピル展開する（TEXTSPLIT代替）」といった高度な配列操作。これらは、ネットのどこを検索しても、どんな質問サイトで有識者に泣きついても、「Excel 2021では100%不可能。おとなしく365に課金するか、VBAでマクロを組んでください」という冷たい回答しか返ってきませんでした。

ですが、私は諦めきれませんでした。
「マクロ禁止の社内環境でも動く、1セルだけで完結する美しいスピル数式が絶対に存在するはずだ。この世に斬れぬものはなし。」

そこから数年試行錯誤を重ねた果てに……私はついに、2021環境のままで「最小値」「最大値」「ソート」「累計」「グループ連番」「文字列展開」「ソート結果への合計行ドッキング（VSTACK代替）」をすべて1セルで完結させる『魔改造ロジック』の完全解明に成功しました。

VBAは1行も使っていません。365しか使えない関数も使っていません。純粋なExcel 2021の関数だけで、すべての超難問を力技でねじ伏せています。

何時間も自力で悩んでバグに頭を抱える時間を、缶コーヒー数本分の価格でショートカットしませんか？「2021環境でここまでやってのけた執念の結晶」のナカミを、ここにすべてコピペ可能な状態で公開します。

⚠️ 期間限定の先行価格です
本記事は、現在【先行価格 500円】で公開しています。10月に迫るOffice 365移行シーズン、あるいは一定数の販売に達したタイミングで、予告なく【通常価格 600円】へと値上げいたします。今、この瞬間の投資が、あなたの今後のExcel実務を劇的に、そして永久に進化させます。

365の最新関数に頼らず、Excel 2021という縛り環境のままで完全勝利を収めるための全コードを、以下に格納します。
【有料エリアに収録されている数式一覧（すべてExcel 2021対応・コピペ可能）】

【基本編】ある列の累計列を作る「累計列スピル」
【基本編】グループごとに1にリセットされる連番列をつくる「縦連番スピル」
【基本編】二つの範囲を縦にドッキングさせる「縦結合スピル」（VSTACK関数の代替）
【応用編】行ごとのデータを並び替える「行ごとソートスピル」
【応用編】行ごとの「最小値」「最大値」を1セルで出す「行ごと最大・最小スピル」
【応用編】カンマ区切りの文字列を横にバラす「区切り文字展開スピル」（TEXTSPLIT関数の代替　※横1行に展開するスピルです）
【購入特典】上記の数式がすべて最初から組み込まれている「動作確認済・サンプルExcelファイル（.xlsx）」のダウンロードリンク

（注）「○○関数の代替」はイメージです。細かい引数設定や動きを完全に再現するものではありません。

💻 動作確認環境（安心してご購入いただくために）

対応バージョン： Excel 2021（永続版・デスクトップ版）
非対応（不要な環境）： Microsoft 365（サブスク版） / Excel 2024 以降（※これらは公式の最新関数が使えるため、本記事の技を使う必要がありません）
マクロ（VBA）： 一切使用していません。.xlsx ファイルのまま動きます。