Excel LAMBDA関数でセル範囲を副問い合わせ表をLIKEやBETWEENでJOINするには？

はじめに

データウェアハウス構築でデータソースにワイルドカードや範囲指定の変換テーブルかませてファクトテーブルを作る(https://qiita.com/madilloar/items/56743b78e03212c239c7)では、二つのテーブルのJOINにLIKE演算子やBETWEEN演算子を使って、オリジナルのデータソースのレコードにラベルを付与するようなSQLを作りました。

PowerQueryでテーブルと副問い合わせ表をLIKEやBETWEENでJOINするには？(https://qiita.com/madilloar/items/015e75de35e0a7535ba0)では、PowerQuerを使ってテーブルと副問い合わせ表をLIKEやBETWEENでJOINしてみました。

今度は、Excel LAMBDA関数でセル範囲を副問い合わせ表をLIKEやBETWEENでJOINしてみます(命令型プログラミングにどっぷりなので、関数型プログラミングが難しい。。。)。

戦略

今回の戦略は、SQL、特にSELECT文の挙動を再現するイメージとPowerQueryのM言語でやったことを再現する戦略です。

特に重要というか悩んだのは、SQLの単体テストケースって、どうやって考えるの？(https://qiita.com/madilloar/items/df9c579caae3aa380b52)で書いた、「FROM句の挙動1」です。
SELECT文のFROM句の挙動は、FROM句に登場する全てのテーブルでクロス結合表を作ることなのですが、Excelの関数にクロス結合表を作る関数はないので、自作の関数を作る、つまりLAMBDA関数を作ることになります。
PowerQueryだと次のようにAddColumnでテーブル全体を列として追加できるので簡単なのですが、これをLAMBDA関数でどうやるかがわからなかったです。Excel VBAを組めばできますが、今回はマクロ禁止の縛りなので難しい。。。

let
    MainTable     = Excel.CurrentWorkbook(){[Name="T_注文"]}[Content],
    SubTable      = Excel.CurrentWorkbook(){[Name="T_分析用ラベル付与"]}[Content],

    // 1:CROSS JOINをM言語で再現する。
    // メインテーブルとサブテーブルの結合キー列でLIKEやBETWEENによるあいまいな結合が使えないので、いったんCROSS JOINで大きな表を作る。
    CrossJoined   = Table.AddColumn(MainTable, "Sub", each SubTable),

インプットとアウトプットのイメージ

インプットとなる2つのテーブルのイメージ

最終的に欲しい表

メインの注文テーブルに分析用ラベルの列を付与した表を作りたい。

クロス結合表を作る

keiriman210529さんのブログ「日々の備忘録」にズバリ欲しいLAMBDA関数がありました。感謝です。勝手ながら参考にさせていただきました。ありがとうございます。
ExcelのLAMBDA関数でクロス結合を定義する(https://keiriman210529.hatenablog.com/entry/2022/09/01/010504)

オリジナルの関数では、return変数の定義がreturn, IF(colindex <= cols1, array1, INDEX(array2, rowindex, colindex - cols1)),となってるところを修正してます。
array1とarray2は行数が同じになるので、HSTACKでセル範囲を横にくっつけられると判断したからです。
Excel 関数だとステップ実行できないので、命令型プログラミングどっぷりの自分には難解で理解が難しかったのですが、PowerQueryで各行の変数定義の結果を見ていくのと同じやり方で、LET関数の各行での変数数の値を一つずつ見ていくこと、便宜上ステップ実行することで理解が進みました。

次のようなメインテーブルとサブテーブルの全組み合わせの表を作る関数、CROSS_JOIN関数を作ります。

CROSS_JOIN関数

引数1:メインテーブルの配列。
引数2:サブテーブルの配列。
戻り値:メインテーブルとサブテーブルをクロス結合した結果の配列。
機能説明:
メインテーブルとサブテーブルをクロス結合した結果の配列を求める。
次の関数の各行の挙動については、別記事でステップ実行しながら理解したことを投稿しますので、ここでは特に説明は無しです。

=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
  )
)

CROSS_JOIN関数の利用

=CROSS_JOIN($A$3:$E$9,$G$3:$L$13)

メインテーブルとサブテーブルをあいまい結合した配列を作る

クロス結合表は、メインテーブルとサブテーブルの全ての組み合わせから作られているというのがポイントです。全ての組み合わせなので、とある1行に着目して、その中のメインテーブルの列とサブテーブルの列であいまい結合するかイコールにするか判定すれば、結果的にSQLのJOIN ON句で書いている、もしくはWHERE句で書いていることと同じになります。
あいまい結合するならLIKEやBETWEENでフィルタすればよいです。完全結合するならイコールで結合すればよいです。

クロス結合表を次のようにフィルタするMY_FILTER関数を作ります。

MY_FILTER関数

引数:メインテーブルとサブテーブルから成るクロス結合した結果の配列
戻り値:メインテーブルとサブテーブルで指定した列であいまい結合してフィルタされた結果の配列
機能説明:
引数のクロス結合配列をFILTER関数で絞り込みます。
FILTER関数の第2引数は絞り込みに用いる関数を書きますが、ここにメインテーブルとサブテーブルのJOINに使う列を指定して自作のLIKE関数やBETWEEN関数を使って絞り込みます。クロス結合表のどの列とどの列であいまい結合するかは、INDEX関数の2列と7列のように、コテコテの固定値を指定しているのでイケてないですが、結合条件は要件で変わる部分なので、無名関数として渡すのもありかなと思ったので、固定値でも良しと思っています。各列のフィルタ条件を"*"でつないでいますが、これは算術乗算ではなく、論理積のANDです。

=LAMBDA(range,
  LET(
    return, FILTER(range,
                  (LIKE(INDEX(range, , 2), INDEX(range, , 7)))
                * (LIKE(INDEX(range, , 3), INDEX(range, , 8)))
                * (BETWEEN(INDEX(range, , 4), INDEX(range, , 9), INDEX(range, , 10))),
                "ERROR"
            ),
    return
  )
)

MY_FILTER関数の利用

=MY_FILTER(CROSS_JOIN($A$3:$E$9,$G$3:$L$13))

あいまい結合に利用するヘルパー関数を作る

文字列のワイルドカード一致判定のLIKE関数と数値の範囲判定のBETWEEN関数を作ります。

LIKE関数

引数1:SQLのLIKE演算子の左辺。ワイルドカード文字は指定できません。
引数2:SQLのLIKE演算子の右辺。ワイルドカード文字を指定できます。
引数3:オプション引数。デフォルトのワイルドカード文字は""。
戻り値:左辺と右辺をあいまい結合して一致したらTRUE、一致しない場合はFALSE。
機能説明:
左辺と右辺をあいまい結合して一致したらTRUE、一致しない場合はFALSEを返します。
左辺の先頭もしくは末尾に""がある場合、意図した文字にヒットしないため、ワイルドカード文字を変えたいときに第三引数を指定します。例えば"%"に変えるとか。

=LAMBDA(left, right, [arg_wild_card_char],
  LET(
     wild_card, IF(arg_wild_card_char = "", "*", arg_wild_card_char),
     return, IF(LEFT(right, 1) = wild_card,
               IFERROR(FIND(RIGHT(right, LEN(right) - 1), left), 0) > 0,
               IF(RIGHT(right, 1) = wild_card,
                 IFERROR(FIND(LEFT(right, LEN(right) - 1), left), 0) = 1,
                   IF(left = right, TRUE, FALSE)
                 )
             ),
     return
  )
)

BETWEEN関数

引数1:範囲判定対象の値。SQLのBETWEEN演算子の左辺。
引数2:範囲判定の開始値。SQLのBETWEEN演算子の第二引数。
引数3:範囲判定の終了値。SQLのBETWEEN演算子の第三引数。
戻り値:範囲判定の開始値＜＝範囲判定対象の値＜＝範囲判定の終了値の時、TRUEを返す。そうでないならFALSEを返す。

=LAMBDA(value, from, to,
  LET(
    step1, (from <= value) * (value <= to),
    return, IF(step1 <> 0, TRUE, FALSE),
    return
  )
)

プライマリキー毎にGROUP BYして優先順位のMIN値(最優先)の小さな表を作る

メインテーブルとサブテーブルをあいまい結合した結果の配列からメインテーブルのプライマリキー毎にグルーピングして優先順位の最小値(最優先)を求めます。こんな表を作ります。

GROUP_MIN関数

引数1:配列。メインテーブルとサブテーブルをあいまい結合した結果の配列を想定。
引数2:グルーピングするキーの列番号。メインテーブルのプライマリキーを想定。
引数3:グルーピングするキー毎に最小値を求める列番号。サブテーブルの優先順位列を想定。
戻り値:プライマリキー毎の優先順位の最小値の配列。
機能説明:
グルーピングするキーが1列、集計対象(この場合は最小値を求める対象)も1列しか対応できていない。
最小値を求めるのにExcelのMINFS関数を利用しようとしたのですが、MINFS関数はセル範囲を引数に渡すことを前提に作られていて使えないです。関数が返す動的な配列だと#CALC!となってしまいます。ですので、MIN関数とIF関数を組み合わせます。

IF関数は動的配列を引数に取れます。criteria_range1とmin_rangeはともに同一行数×1列の一次元配列です。同一行数の配列をIF関数に渡しているので、ループの回数は同一です。関数型プログラミングなのでループというのはあくまで方便ということで。
MAP関数がcategoriesから要素を一つ取り出し、これがcですが、これをcriteria_range1の配列の一つ一つとイコールか調べます。
IF(criteria_range1=c, min_range, ""))のcriteria_range1=cの部分。左辺が配列で右辺がスカラ変数です。
IF関数がTRUEつまり、イコールだったら、その配列と同じ添え字のmin_rangeから要素を取り出しています。IF(criteria_range1=c, min_range, ""))の, min_rangeの部分。FALSEの場合は""の空文字。

=LAMBDA(range, category_col_num, value_col_num,
  LET(
    categories, UNIQUE(INDEX(range, 0, category_col_num)),
    min_range, BYROW(range, LAMBDA(row, INDEX(row, 0, value_col_num))),
    criteria_range1, BYROW(range, LAMBDA(row, INDEX(row, 0, category_col_num))),
    min_values, MAP(categories, LAMBDA(c, MIN(IF(criteria_range1=c, min_range, "")))),
    combin, HSTACK(categories, min_values),
    return, combin,
    return
  )
)

GROUP_MIN関数の利用

=GROUP_BY_MIN(MY_FILTER(CROSS_JOIN($A$3:$E$9,$G$3:$L$13)),1 , 6)

あいまい結合した表とプライマリキー毎優先順位表をINNER JOINするためのキー列(LOOKUP KEY列)を作る

この後でクロス結合表と優先順位表をINNER JOINしてフィルタリングしますが、そのためのLOOKUP KEYを作っています。こんな感じで左端にLOOKUP KEYをつけます。

ADD_LOOKUP_KEY

引数1:LOOKUP KEYをつけたい配列。
引数2:LOOKUP KEYの1つ目の列番号。配列のプライマリキーを想定。
引数3:LOOKUP KEYの2つ目の列番号。配列の優先順位を想定。
戻り値:プライマリキーと優先順位をカンマ区切りした文字列の配列。

=LAMBDA(range, key_col1, key_col2,
  LET(
    result, BYROW(range, LAMBDA(row, INDEX(row, 0, key_col1) & "," & INDEX(row, 0, key_col2))),
    return, HSTACK(result, range),
    return
  )
)

クロス結合表のLOOKUPキーを追加

=ADD_LOOKUP_KEY(MY_FILTER(CROSS_JOIN($A$3:$E$9,$G$3:$L$13)),1,6)

プライマリキー毎優先順位表のLOOKUPキーを追加

=ADD_LOOKUP_KEY(GROUP_BY_MIN(MY_FILTER(CROSS_JOIN($A$3:$E$9,$G$3:$L$13)),1, 6),1,2)

あいまい結合した表とプライマリキー毎優先順位表をLOOKUP KEYでINNER JOIN結合して最終的に欲しい表を作る

いよいよ最後。最終的に欲しい表を作ります。

結果の表を作成する無名関数

引数1:あいまい結合した表
引数2:プライマリーキー毎優先順位表
戻り値:結果の表
変数filterdのところがLOOKUP KEYによるINNER JOINと考えてください。
変数selectedのところは、SQLのSELECT文のカラムを選択しているところと考えてください。selected, LAMBDA(range, のrangeには(sorted, {2,3,4,5,6,12}),の変数sortedが来るので、フィルタしたソート後の配列が来ます。
SEQUENCE関数でrangeを行方向に移動させ、{2,3,4,5,6,12}で欲しいカラムを指定しています。

=LAMBDA(range1, range2,
  LET(
    range1_key, BYROW(range1, LAMBDA(row, INDEX(row, 0, 1))),
    range2_key, BYROW(range2, LAMBDA(row, INDEX(row, 0, 1))),
    filterd, FILTER(range1, ISNUMBER(MATCH(range1_key, range2_key, 0))),
    sorted, SORTBY(filterd,INDEX(filterd, 0,2),1),
    selected, LAMBDA(range, select_cols,
                INDEX(HSTACK(range), SEQUENCE(ROWS(range)), select_cols)
              )(sorted, {2,3,4,5,6,12}),
    return, selected,
    return
  )
)(ADD_LOOKUP_KEY(MY_FILTER(CROSS_JOIN($A$3:$E$9,$G$3:$L$13)),1,6),ADD_LOOKUP_KEY(GROUP_BY_MIN(MY_FILTER(CROSS_JOIN($A$3:$E$9,$G$3:$L$13)),1, 6),1,2))