SPILLで2つの配列に含まれるすべて行の組み合わせ（FULL OUTER JOIN）

今回はExcelのSPILLの小技。

Problem

Excelシート上に2つの配列（範囲）がある。
2つの配列に含まれるすべての行の組み合わせ、つまりFULL OUTER JOINを行いたい。

例えば配列A（ここではテーブルAとして定義）と配列B（同じくテーブルB）があったとき：

テーブルA

ACol1	ACol2
A1	A1-2
A2	A2-2
A3	A3-2

テーブルB

BCol1	BCol2
B1	B1-2
B2	B2-2

次のような配列（SPILL）を得たいとする：

ACol1	ACol2	BCol1	BCol2
A1	A1-2	B1	B1-2
A2	A2-2	B2	B2-2
A3	A3-2	B1	B1-2
A1	A1-2	B2	B2-2
A2	A2-2	B1	B1-2
A3	A3-2	B2	B2-2

Excelの式で例えば次のようにして期待した結果が得られればよいのだが・・・：

=BYROW(テーブルA,LAMBDA(arow,BYROW(テーブルB,LAMBDA(brow,HSTACK(arow,brow)))))

残念ながらLAMBDA関数から配列を返そうとしているためにエラー!CALCが発生する。

Solution

REDUCE関数を利用する。

まずは列が1つのみの配列同士でやってみる。
例として以下の2つの配列、テーブル「ABC」とテーブル「あいう」の組み合わせを求めてみる：

=DROP(
  LET(
    zero,
    MAKEARRAY(1,2,LAMBDA(a,b,0)),
    lrows,
    ROWS(ABC),
    seq,
    SEQUENCE(ROWS(ABC)*ROWS(あいう),,0),
    REDUCE(
      zero,
      seq,
      LAMBDA(
        acc,
        elm,
        VSTACK(
          acc,
          HSTACK(
            INDEX(ABC,MOD(elm,lrows)+1),
            INDEX(あいう,QUOTIENT(elm,lrows)+1)
          )
        )
      )
    )
  ),
  1
)

計算過程をざっくり説明すると：

• LET関数で計算過程に名前をつけて順番に処理していく
• zeroは1行x2列の配列で要素の値はすべて0。これは後ほどREDUCE関数を利用するときに必要になるダミー配列
• lrowsは左側（JOINの考え方でいえば「外側」）の配列の行数。これも後続の処理で利用
• seqは2つの配列の行数をかけ合わせた数の要素を持つシーケンス
• そしていよいよREDUCE関数の登場。アキュームレータとして先ほど用意したダミー配列を利用。処理対象の配列としてはこれも先ほどのシーケンスを利用
• これらを処理する関数はLAMBDAで定義。シーケンスの要素（0, 1, 2, 3, ...）を利用して2つの配列「ABC」と「あいう」から要素を取り出しHSTACK関数で1行にまとめる（例：{"A", "あ"}）
• それを今度はVSTACK関数で1つの配列（2次元配列）にまとめる（例：{{"A","あ"},{"B","あ"}, ...}
• 最後にまとめあげた配列の先頭にはダミー配列の要素{0, 0}があるのでこれをDROP関数で除去

結果は以下のようなSPILLとなる（1行目は可読性のためヘッダーとして入力したもの）：

続いて、列が複数の配列同士でやってみる。
例として以下の2つの配列、テーブル「ABCPLUS」とテーブル「あいうPLUS」の組み合わせを求めてみる：

=DROP(
  LET(
    zero,
    MAKEARRAY(1,COLUMNS(ABCPLUS)+COLUMNS(あいうPLUS),LAMBDA(a,b,0)),
    lrows,
    ROWS(ABCPLUS),
    seq,
    SEQUENCE(lrows*ROWS(あいうPLUS),,0),
    REDUCE(
      zero,
      seq,
      LAMBDA(
        acc,
        elm,
        VSTACK(
          acc,
          HSTACK(
            CHOOSEROWS(ABCPLUS,MOD(elm,lrows)+1),
            CHOOSEROWS(あいうPLUS,QUOTIENT(elm,lrows)+1)
          )
        )
      )
    )
  ),
  1
)

結果は以下のようなSPILLとなる（1行目はヘッダーとして入力したもの）：