Excel LAMBDA関数を手動でステップ実行して理解を深めるには？

Excel LAMBDA関数でセル範囲を副問い合わせ表をLIKEやBETWEENでJOINするには？(https://qiita.com/madilloar/items/d058f5076a0810f5cf73)
で、2つのセル範囲を2つのテーブルとみなして、クロス結合表を作る関数、CROSS_JOIN関数を紹介しました。

=LAMBDA(range,range2,
  LET(
    rows1, ROWS(range),
    cols1, COLUMNS(range),
    rows2, ROWS(range2),
    cols2, COLUMNS(range2),
    rowsn, rows1 * rows2,
    colsn, cols1 + cols2,
    rowindex, SEQUENCE(rowsn, 1),
    colindex, SEQUENCE(1, colsn),
    arraymodel1, MOD(SEQUENCE(rowsn, 1, 0), rows1) + 1,
    array1, INDEX(range, arraymodel1, SEQUENCE(1, cols1)),
    arraymodel2, INT((rowindex - 1) / rows1) + 1,
    array2, INDEX(range2, arraymodel2, SEQUENCE(1, cols2)),
    return, HSTACK(array1,array2),
    return
  )
)($A$2:$B$6,$D$2:$E$4)

$A$2:$B$6と$D$2:$E$4の2つの表を与えるとG2:J16の2つの表の全組み合わせのクロス結合表を作るイメージです。

この関数は、ExcelのLAMBDA関数でクロス結合を定義する(https://keiriman210529.hatenablog.com/entry/2022/09/01/010504)を使っています。

Excel LAMBDA関数にはブレイクポイントを置いてステップ実行できないので、初めはどこから手を付ければよいのかわからず、LAMBDA関数を理解するのは、どうすればよいか途方に暮れてました。

Excelには「数式」リボンの「ワークシート分析」に「数式の検証」というボタンがあるので、これが使えるか？と思ったのですが、これはネストした関数にステップインして結果を見るようなものなので、ちょっと目的と違うので、これも使えない。

で、どうしたのか？ですが、PowerQueryのステップ実行を参考にしました。
PowerQueryでは列のタイプを変更したり、行を抽出したり、何らかの変換ステップを追加すると、その過程で、変数がどんどん追加されていきます。そしてinの次に指定された変数が、そのクエリの最終的な戻り値になります。

PowerQueryのM言語のイメージ

let
 変数1,
 変数2,
 ・・・
 変数n
in
 変数n

これはLAMBDA関数の中でLET関数を使って変数を定義していき、最後の変数がそのLAMBDA関数の戻り値になるのと構造が一緒です。
この構造にならい、LET関数の最後の変数を手動で差し替えることでステップ実行を再現します。

上記の関数の例なら、最後のreturnのところを、

    return, HSTACK(array1,array2),
    return

例えば、rowindexとすれば、

    rowindex, SEQUENCE(rowsn, 1),

の結果が見えるようになります。

では、実際にCROSS_JOIN関数がどういう結果を関数の積み重ねで動いているのか見ていきます。

「rowsn, rows1 * rows2,」は何を返す？

rows1, ROWS(range),とかはセル範囲の行数をrows1に設定しているだけなので、簡単なので端折ります。

=LAMBDA(range,range2,
  LET(
    rows1, ROWS(range),
    cols1, COLUMNS(range),
    rows2, ROWS(range2),
    cols2, COLUMNS(range2),
    rowsn, rows1 * rows2,
    colsn, cols1 + cols2,
    rowindex, SEQUENCE(rowsn, 1),
    colindex, SEQUENCE(1, colsn),
    arraymodel1, MOD(SEQUENCE(rowsn, 1, 0), rows1) + 1,
    array1, INDEX(range, arraymodel1, SEQUENCE(1, cols1)),
    arraymodel2, INT((rowindex - 1) / rows1) + 1,
    array2, INDEX(range2, arraymodel2, SEQUENCE(1, cols2)),
    return, HSTACK(array1,array2),
    rowsn
  )
)($A$2:$B$6,$D$2:$E$4)

rowsn, rows1 * rows2,が何を返すのか見ていきます。LET関数の最後に注目してください。
元の関数では、returnになっていたかと思いますが、rowsnに書き換えています。

    return, HSTACK(array1,array2),
    rowsn

この状態で関数をセルに設定すると、「15」が返ってきます。
これは、CROSS_JOIN関数の引数に「$A$2:$B$6,$D$2:$E$4」を渡しているからで、
「$A$2:$B$6」は「5行2列」なので、rows1は「5」です。
「$D$2:$E$4」は「3行2列」なので、rows2は「3」です。
なので、rowsn, rows1 * rows2,は「5*3」の「15」をrowsn変数に設定しています。

「rowindex, SEQUENCE(rowsn, 1),」は何を返す？

SEQUENCE関数は、SEQUENCE(行数,列数)で、引数の行数、列数で配列を生成し連続した数値の一覧を生成します。
先の例ではrowsnは「15」でしたので、SEQUENCE(rowsn, 1)は、SEQUENCE(15, 1)です。
ですので、15行1列の連番が生成されます。では、LET関数の最後のrowsnとしていたところを、今度はrowindexに変えて実行してす。

    return, HSTACK(array1,array2),
    rowindex

行方向に1から15までの連番が生成されました。

「colindex, SEQUENCE(1, colsn),」は何を返す？

colsnは、colsn, cols1 + cols2,で計算されるので、cols1は2列、cols2も2列なので、colsnは「4」です。
では、LET関数の最後を今度はcolindexに変えて実行します。

列方向に1から4までの連番が生成されました。

「arraymodel1, MOD(SEQUENCE(rowsn, 1, 0), rows1) + 1,」は何を返す？

だんだん難しくなってきました。
まずは、LET関数の最後をrowindexからarraymodel1に変えて結果を見てみます。

1から5の連番を3回繰り返しています。
クロス結合がどういものかですが、テーブル1とテーブル2の全組み合わせを作り出すので、テーブル1の行数をテーブル2の行数分繰り返すことになります。
今回の例ではテーブル1が5行、テーブル2が3行なので、1から5が3回繰り返したいため、このような結果となっています。

この行「arraymodel1, MOD(SEQUENCE(rowsn, 1, 0), rows1) + 1,」の内側の関数のSEQUENCE関数に注目します。

①SEQUENCE(rowsn, 1, 0):
クロス結合表全行(rowsn)=15行、1列の配列を作り、連番を生成しています。

②MOD(①, rowsn1) + 1:
①の配列には1から15の連番が入っています。これをテーブル1の行数(rows1=5)で剰余(MOD関数)を計算すると、この例では5行で割り切れるとゼロになるので、0から4までの数字が作り出されます。欲しい値はこれに+1した値ですので+1しています。

「array1, INDEX(range, arraymodel1, SEQUENCE(1, cols1)),」は何を返す？

もっと難しくなってきました。
まずは、LET関数の最後をarraymodel1からarray1に変えて結果を見てみます。

テーブル1の内容(5行2列)がテーブル2の行数(3行)分繰り返されています。

INDEX関数の第一引数のrange1(この例のテーブル1のこと)からレコードを取得しています。
この時、行方向カーソル移動は、arraymodel1(1から5の連番が3回繰り返されている)の配列の値で移動します。
列方向のカーソル移動は単純でSEQUENCE関数でrange1の1列目からrange1の列数分(この例では2列)繰り返します。命令型プログラミングのイメージだとこんな感じ。

for(int row = 1; row <= arraymodel1; row++){
  for(int col = 1; col <= cols1; col++){
    array1.add(INDEX(range, row, col));
  }
}
return array1;

「arraymodel2, INT((rowindex - 1) / rows1) + 1,」は何を返す？

まずは、LET関数の最後をarray1からarraymodel2に変えて結果を見てみます。

1が5行、2が5行、3が5行という値が返ってきます。これは、テーブル1が5行で、テーブル2が3行なので、
テーブル2の各行をテーブル1の行で繰り返した結果です。
rowindexは1から15の配列です。これをrows1の5行で割って、余りを切り捨て+1することで、11111、22222、33333という連番を生成しています。

おわり

全ての行について解説はしませんでしたが、このように、手動でステップ実行して結果を見ていけば、LAMBDA関数の理解を深められます。