🙆

直積 (CROSS JOIN) をスプレッドシートの名前付き関数で

2023/09/10に公開

Excel

Google Sheets

crossjoin

tech

はじめに

直積の関数自体はググればたくさん出てくるけど、複数列に対応してなかったり、名前付き関数化してるものはあまり見かけなかったので作ってみた。

直積とは

たとえば下記の2つの表があるとき

メニュー
牛めし
チーズ牛めし
ビビン丼
ネギ塩豚焼肉丼

サイズ
並盛
大盛
特盛

すべての組み合わせパターンを出したものを直積（CROSS JOIN）と言う

メニュー	サイズ
牛めし	並盛
牛めし	大盛
牛めし	特盛
チーズ牛めし	並盛
チーズ牛めし	大盛
チーズ牛めし	特盛
ビビン丼	並盛
ビビン丼	大盛
ビビン丼	特盛
ネギ塩豚焼肉丼	並盛
ネギ塩豚焼肉丼	大盛
ネギ塩豚焼肉丼	特盛

名前付き関数作成手順

「データ」 → 「名前付き関数」 → 「新しい関数を追加」を選択
下記の内容を入力
- 関数名: CROSS_JOIN
- 関数の説明: 指定した範囲の直積を生成。空白は無視します。
- 引数のプレースホルダ: array
- 数式の定義: 以下をコピペ
```
=BYROW(REDUCE({""},BYCOL(array,LAMBDA(col,JOIN(CHAR(9),col))),LAMBDA(pre,cur,ARRAYFORMULA(FLATTEN(TOCOL(pre,3)&CHAR(9)&TOROW(SPLIT(cur,CHAR(9)),3))))),LAMBDA(row, SPLIT(row, CHAR(9))))
```
補足情報を入力
- 引数の説明: 直積を生成したい範囲または配列です。空白は無視します。
- 引数の例: A2:C5

サンプル

名前付き関数はインポートできるので、↑のシートからCROSS_JOINをインポートしてもヨシ！

関数の説明

完　全　図　解

=BYROW(
  REDUCE(
    {""}, // REDUCEの初期値として空文字の配列を用意（最初のpre）
    BYCOL(array, LAMBDA(col, JOIN(CHAR(9), col))), // 列ごとにまとめる（この1列1列がcurとなる）
    LAMBDA(
      pre,
      cur,
      // FLATTENで1列にフラット化して次のpreとして受け継ぐ
      ARRAYFORMULA(FLATTEN(
        // 列と行をARRAYFORMULAでぶつけることで、preとcurの全パターンを網羅（これが肝）
        TOCOL(pre, 3) & CHAR(9) & TOROW(SPLIT(cur, CHAR(9)), 3)
      ))
    )
  ),
  // 最後に1行ずつバラして完成
  LAMBDA(row, SPLIT(row, CHAR(9)))
)

おわりに

スプレッドシート最高！

はじめに

直積とは

名前付き関数作成手順

サンプル

関数の説明

おわりに

Discussion