Excelでのクロス結合

ExcelのLAMBDA関数でクロス結合を定義する

PowerQueryのGUIではクロス結合はサポートされておりませんが、公式のドキュメントで手順が公開されています。

Microsoft公式-クロス結合

この方法を使えば面倒なことをせずにクロス結合ができてしまうのですが、何らかの制約や要望によりVBAはもちろんのこと、PowerQueryも使わずにクロス結合したいときがあるかもしれません。ないかもしれません。…そんな訳で、ほとんど趣味レベルではありますがさくっとLAMBDAで書いてみました。

=LAMBDA(range1, range2,
    LET(
        rows1, ROWS(range1),
        cols1, COLUMNS(range1),
        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(range1, arraymodel1, SEQUENCE(1, cols1)),
        arraymodel2, INT((rowindex - 1) / rows1) + 1,
        array2, INDEX(range2, arraymodel2, SEQUENCE(1, cols2)),
        return, IF(colindex <= cols1, array1, INDEX(array2, rowindex, colindex - cols1)),
        return
        )
      )

以下のようにクロス結合したい二つの範囲を引数に入れることでクロス結合をしてくれます。

Markdownを初めて使ったけど、慣れたら書きやすいんですかねこれ。

新関数を使ったもっとシンプルな方法(2022/9/22追記)

はけた先生が2022年8月に実装された新関数を使う方法を紹介されていました。短くて素敵です。

このすべてのパターンの組み合わせを意味する単語、色々とあるみたいです。DAXを解説する洋書を読んでいてCartesian product なる単語が出てきて一瞬なにを指しているかわからなかったのですが何のことはなく、クロス結合でしたとさ。下記の表現が全て同じものを指しているって、初見ではわからないですよね…。

  • 直積（direct product）
  • デカルト積（Cartesian product）
  • クロス結合(クロスジョイン、Cross join)