0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@Phroneris(森の子リスのミーコの大冒険)

表2つからクロス結合（直積）の表を得る5(+2)つの方法【Googleスプレッドシート】

0
Last updated at Posted at 2026-08-22

Google スプレッドシートでクロス結合をしよう！

2 つの表があるとして、その各行全ての組み合わせを列挙した表を得たい。

main00-手本.png

得た。
こういうのをクロス結合（CROSS JOIN）とか直積（デカルト積）とかいうらしい。

Google スプレッドシートでこのクロス結合を実現する方法を模索した結果、なんかいくつも発見できたので、若干の種類分けと共にご紹介して行く。

いずれの方式でも、数式は全セルとか全行頭セルとかに逐一書くのではなく、薄青色で示す出力想定領域の左上 1 セルだけに数式を記述（上図ではカーソルを置いた A9 だけに記述）する事とする。

main01-左上セルにだけ数式.png

二重ループ型

BYROW 方式

表を行毎に処理する BYROW 関数によって、2 つの表を各行で二重ループするという方法。

main-二重ループ-byrow00.png

=WRAPROWS(
  FLATTEN(
    BYROW(
      A3:C4, LAMBDA(
        表ア各行, TOROW(
          BYROW(
            E3:F5, LAMBDA(表イ各行, {表ア各行, 表イ各行})
          )
        )
      )
    )
  ),
  COLUMNS(A3:C4) + COLUMNS(E3:F5)
)

機序

main-二重ループ-byrow01-機序.png

サイズの都合で Qiita 上では小さく表示されてしまうので、別途開いてご覧頂きたい。
もしくは、検証用 Google スプレッドシートの「二重ループ型」シートを直接閲覧しても同じ。

特徴

  • 利点
    • 二重ループという基本構造は素直でわかりやすい
    • 後の章で紹介する他方式と比べると、ループの際に各行の内容を直接渡しているという点で効率が良さそうな気がする
  • 欠点・懸念点
    • 目的のためのコア部分の記述量に対して様々な制約を回避するためだけの追加記述量が多く、素直な方法のつもりが結果的にむしろトリッキーになってしまっている
      • BYROW 関数での各処理には入力 1 行・出力 1 行という鉄の掟があり、これを犯すと「結果は 1 行です。」というエラーになってしまうので、外側の方の LAMBDA 関数（A3:C4, の直後の方）の各結果を 1 行に均すために TOROW 関数が必要
      • それによって列がはみ出すので、全体の整形に WRAPROWS 関数が必要
      • WRAPROWS 関数の入力は 1 次元でなければならない（方向は不問）ので、FLATTEN 関数で 1 次元に均す事が必要
    • 中間的に極端なサイズの配列が生成されるのでちょっと心配
      • 元となる表アの行数と列数を $R_{ア}$ と $C_{ア}$、表イの行数と列数を $R_{イ}$ と $C_{イ}$ とすると、各段階での内部データのサイズは…
        • BYROW & TOROW ⋯ 行数 $= R_{ア}$、列数 $= R_{イ} (C_{ア} + C_{イ})$
        • FLATTEN ⋯ 行数 $= R_{ア} R_{イ} (C_{ア} + C_{イ})$、列数 $= 1$
    • 結果の行数・列数が固定である事に依存している

BYROW 関数の出力は列数に関してなら意外と寛大

BYROW 関数での各処理が「入力 1 行・出力 1 行」と書いたが、実は BYROW 関数のヘルプには次のように書かれている。

すべての行は単一の値にグループ化される必要があります。グループ化された値の配列の結果は返されません。

「単一の値」とあるので、本来なら各出力は 1 行どころか 1 マス（1 行かつ 1 列）でなければならない事になる。
駄目じゃん。

抜粋: 明らかに単一の値ではなく、配列リテラルで複数列にしている
          BYROW(
            E3:F5, LAMBDA(表イ各行, {表ア各行, 表イ各行})
          )

しかし実際には、スプレッドシート関数の傾向として「単一の値が入るべき箇所に配列が入ると、配置が競合しない限りは割とそのまま配列として自動展開してくれる」というありがたい挙動がある。
このため、BYROW 関数の出力は行さえ 1 つずつであれば列は複数あってもエラー等にはならない。

動くならヨシ！！！

行番号利用割り当て型

この型だけ方式が 3 つもある。

まず各方式の数式と個別の特徴を全て示し、その後でこの型共通の特徴や各方式の微妙な差異について触れる。

MAKEARRAY 方式

MAKEARRAY 関数によって、クロス結合の出力先の表を必要な行数でまず確保し、その各行に割り当てる内容を行番号からの整数除算と CHOOSEROWS 関数で都度取得するという方法。

main-割当00-makearray00.png

=MAKEARRAY(
  ROWS(A3:C4) * ROWS(E3:F5), 1, LAMBDA(
    結果行番号, _, {
      CHOOSEROWS(A3:C4, QUOTIENT(結果行番号 - 1, ROWS(E3:F5)) + 1),
      CHOOSEROWS(E3:F5,      MOD(結果行番号 - 1, ROWS(E3:F5)) + 1)
    }
  )
)

機序は後で。→ § 3 つの方式の機序

特徴

  • 利点
    • BYROW 方式と比較して、トリッキーなこじつけ関数が挟まっていないため、結果的に数式のネストが浅く理解しやすい
    • 列方向の自動展開を利用しているため、結果の列数が可変
  • 欠点・懸念点
    • LAMBDA 関数には使いもしない列番号の引数名も指定しなければならない（ここでは _）
    • 行番号を用いる方式の宿命として、ループの度に各表の全く同じ部分を何度も取得し直している点では効率が気になる
    • MAKEARRAY 関数が確保する配列構造に依存しているので、結果の行数は固定

MAKEARRAY 関数の出力列数も 1 列と指定してこそ寛大

MAKEARRAY 関数の第 2 引数は列数の指定なので、ここを 1 としている今回の数式では出力結果の表がたった 1 列に制限されてしまうように思えるかもしれない。

抜粋
=MAKEARRAY(
  ROWS(A3:C4) * ROWS(E3:F5), 1, LAMBDA(

実際ヘルプにも

インデックスに適用する LAMBDA 関数によって作成される値はすべて、単一の値である必要があります。作成された値の配列の結果は返されません。

と書いてある。

しかしこれは BYROW 関数の「単一の値」のくだりと全く同じで、現実では列数指定を 1 とした上で各返り値を 1 行複数列の配列にするとそのように自動展開してくれる。
ヨシ！！！！！

むしろ列数の指定を複数にしてしまうと、BYROW 関数と違って MAKEARRAY 関数の LAMBDA 関数は各行ではなく各マスに動作するので、データが無意味に重複し、更には結果の配置先が競合してエラーになってしまう。

MAP 方式

SEQUENCE 関数によってクロス結合表の行番号にあたる連番を生成すると共に出力先の表を確保し、MAP 関数でその番号の行にあるべき行データを各表から取得して割り当てるという方法。

main-割当01-map00.png

=MAP(
  SEQUENCE(ROWS(A3:C4) * ROWS(E3:F5), 1, 0), LAMBDA(
    結果行零始番号, {
      CHOOSEROWS(A3:C4, QUOTIENT(結果行零始番号, ROWS(E3:F5)) + 1),
      CHOOSEROWS(E3:F5,      MOD(結果行零始番号, ROWS(E3:F5)) + 1)
    }
  )
)

機序は後で。→ § 3 つの方式の機序

特徴

  • 利点
    • MAKEARRAY 方式とほぼ同じでありながら、あちらにあった余分な引数指定が無い
    • MAKEARRAY 方式と比較して、行番号引数を 0 始まりにできるので数式が少し単純になる
      • このため、変数名を 結果行番号 から 結果行零始番号 に改名している
    • MAKEARRAY 方式と同じく、列方向の自動展開を利用しているため、結果の列数が可変
  • 欠点・懸念点
    • 連番を得るためだけに SEQUENCE 関数で新たな配列を生成する必要があるというのは、余計な処理にも思える
    • 行番号を用いる方式の宿命として、ループの度に各表の全く同じ部分を何度も取得し直している点では効率が気になる
    • SEQUENCE 関数が出力する配列構造に依存しているので、MAKEARRAY 方式と同じく結果の行数は固定

ARRAYFORMULA 方式

MAP 方式と同様の処理を、ARRAYFORMULA 関数の魔法によって LAMBDA 関数に頼らない従来型の数式に押し込める方法。

main-割当02-arrayformula00.png

=ARRAYFORMULA(
  {
    CHOOSEROWS(A3:C4, QUOTIENT(SEQUENCE(ROWS(A3:C4) * ROWS(E3:F5)) - 1, ROWS(E3:F5)) + 1),
    CHOOSEROWS(E3:F5,      MOD(SEQUENCE(ROWS(A3:C4) * ROWS(E3:F5)) - 1, ROWS(E3:F5)) + 1)
  }
)

さすがに LET 関数を使った方が整理がつく。

main-割当02-arrayformula01-let.png

=ARRAYFORMULA(
  LET(
    表イ行数, ROWS(E3:F5),
    結果行零始番号, SEQUENCE(ROWS(A3:C4) * 表イ行数) - 1,
    {
      CHOOSEROWS(A3:C4, QUOTIENT(結果行零始番号, 表イ行数) + 1),
      CHOOSEROWS(E3:F5,      MOD(結果行零始番号, 表イ行数) + 1)
    }
  )
)

機序は後で。→ § 3 つの方式の機序

特徴

  • 利点
    • LAMBDA 関数を用いる諸方式と比較して、処理効率が高いかもしれない
      • → § おまけ 1. 大量のデータは危険
      • 配列系関数は LAMBDA 関数によって柔軟かつ自由にロジックを記述できるのに対し、ARRAYFORMULA 関数はそこまでできない代わりにスプレッドシート内部でのネイティブな最適化によって一括処理のパフォーマンスに優れる、みたいな話を調査の過程で見かけた…気がするが、見付け直せなかった
        • まぁでも一般論ではそうよね
  • 欠点・懸念点
    • LAMBDA 関数を用いる諸方式と比較して、ARRAYFORMULA 関数による数式は宣言的でなく、何がどう処理されるのかを直感的に掌握し辛い
      • 上記の自由度との引き換えで、「ARRAYFORMULA 関数の中で INDEX 関数が使えない！」みたいな驚きが発生しやすい
    • 行番号を用いる方式の宿命として、ループの度に各表の全く同じ部分を何度も取得し直している点では効率が気になる
    • MAP 方式と同じく、連番を得るためだけに SEQUENCE 関数で新たな配列を生成する必要があるというのは余計な処理にも思える
      • 特に LET 関数を用いない場合は 2 回も生成しているのでなお悪い
    • 結果の行数・列数は固定

3 つの方式の機序

main-割当03-機序.png

やはり Qiita 上では表示が小さいので、別途画像を開いてご覧頂くか、検証用 Google スプレッドシートの「行番号利用割り当て型」シートを直接ご覧頂きたい。
シートだと矢印図形の位置がズレている場合があるのはご愛嬌。

整数除算による割り当て元の求め方

クロス結合表のとある行番号を $r_{結}$ とおき、クロス結合表において $r_{結}$ 行目には表アの $r_{ア}$ 行目と表イの $r_{イ}$ 行目を取得・結合して割り当てるとする。

もし行番号が 0 始まりだとすると、これらの値の関係は表イの行数 $R_{イ}$ を用いて、剰余のある整数除算で

r_{結} \div R_{イ} = r_{ア} \cdots r_{イ}

と表せる。

実際には 1 始まりとなる点を補正して実装する。

MAKEARRAY 方式から抜粋
      CHOOSEROWS(A3:C4, QUOTIENT(結果行番号 - 1, ROWS(E3:F5)) + 1),
      CHOOSEROWS(E3:F5,      MOD(結果行番号 - 1, ROWS(E3:F5)) + 1)

$r_{結}$ に限っては SEQUENCE 関数や LET 関数を用いるなら 0 始まりで取得する事もできるが、$r_{ア}$、$r_{イ}$ については 1 始まりとしてしか CHOOSEROWS 関数に渡せない。

MAKEARRAY 方式と MAP 方式では列数が可変

MAKEARRAY 方式と MAP 方式については、それぞれの利点にも書いた通り、列方向の自動展開を利用しているために列数が可変となる。

つまり、もしクロス結合を超える複雑な事をやりたいのであれば、ループの中で状況に応じて列数を増減する事もできる。

main-割当04-列数00-可変.png

一方、ARRAYFORMULA 方式はそのような柔軟性に欠け、同じ方法で列数を変化させようとするとエラーが出まくる。

main-割当04-列数01-固定.png

列数を減らす方は、欠けたセルが個別で #N/A エラーになるだけなのでまだ良い。
列数を増やす処理をした行は、行全体が #REF! エラーになり、

関数 ARRAY_ROW のパラメータ 2 の 行 のサイズが一致しません。1 になっていますが、6 にしてください。

と怒られてしまう。
ひょっとしたら何か解決法はあるのかもしれないが…、ARRAYFORMULA 関数はパワフルな分ちょっとフワッとし過ぎていて、何ができて何ができないのかが個人的に昔から掴み切れていない。

そもそもクロス結合の中でやるべき作業か？ という疑問は大いにあるが（やれるとしても IFS 関数や SWITCH 関数が使えないのは酷い）、まぁもしこういう事をやりたければ ARRAYFORMULA 方式より MAKEARRAY 方式や MAP 方式の方が柔軟性で優れるとは言って良いだろう。

CHOOSEROWS 関数 vs INDEX 関数

範囲から特定の行だけを得る関数として CHOOSEROWS 関数を用いたが、似て非なる存在として INDEX 関数がある。

CHOOSEROWS 関数はセル範囲としてではなく新たな配列として行を切り出すので、ROW 関数等によって参照元の範囲情報を利用しようとすると、そのセルは「引数は範囲にする必要があります。」という #N/A エラーになってしまう。

一方 INDEX 関数なら、あくまで範囲のオフセットとして行を切り出すので、参照元の範囲情報が利用可能となる。

main-割当05-index00-可能.png

個人的には行を得るという目的を明示するために CHOOSEROWS 関数の方を好んで使っているが、CHOOSEROWS と複数形である点からもわかる通りこの関数は複数の行を切り出す機能こそが本領でもある。
毎回明らかに単一の行しか切り出さない今回のような用途においては、状況によって INDEX 関数の方が良い場合もあるだろう。

ただし、ARRAYFORMULA 方式では INDEX 関数が使えない。

main-割当05-index01-不可能.png

列数の件といい、本当に ARRAYFORMULA 関数のブラックボックスぶりを物語っている。

そもそもクロス結合の中でやるべき作業か？？ という疑問…まぁ、うん。

行番号利用集積型

REDUCE 方式

MAP 方式と同様に SEQUENCE 関数で連番を得てその番号毎に行データを取得するが、取得した行を固定的に割り当てるのではなく REDUCE 関数によって動的に集積するという方法。

main-集積-reduce00.png

=REDUCE(
  , SEQUENCE(ROWS(A3:C4), ROWS(E3:F5), 0), LAMBDA(
    結果表, 結果行零始番号, LET(
      結果行, {
        CHOOSEROWS(A3:C4, QUOTIENT(結果行零始番号, ROWS(E3:F5)) + 1),
        CHOOSEROWS(E3:F5,      MOD(結果行零始番号, ROWS(E3:F5)) + 1)
      },
      IF(結果行零始番号 = 0, 結果行, VSTACK(結果表, 結果行))
    )
  )
)

機序

main-集積-reduce01-機序.png

画像を別途開いてご覧頂くか、検証用 Google スプレッドシートの「行番号利用集積型」シートを直接ご覧頂きたい。
矢印図形がズレていてもご愛嬌。

特徴

  • 利点
    • 結果の行数も列数も可変
      • 取得した行を出力用配列の末尾に積み込んでいく方式である事が一因
        • 行番号利用割り当て型は固定長配列へのインデックス指定代入方式、対してこの REDUCE 方式は可変長配列へのプッシュ追加方式とも言えるだろう
      • ただし、増減によって生じた空きセルには専用のエラー処理を要する
    • MAP 方式や ARRAYFORMULA 方式と違って、SEQUENCE 関数が出力する連番配列の構造が結果に影響しない
    • MAP 方式と同じく、行番号引数を 0 始まりにできるので数式が少し単純になる
  • 欠点・懸念点
    • 重畳処理の概念がわからないと難解
    • 初期値の処理が必須
    • 行番号を用いる方式の宿命として、ループの度に各表の全く同じ部分を何度も取得し直している点では効率が気になる
    • MAP 方式や ARRAYFORMULA 方式と同じく、連番を得るためだけに SEQUENCE 関数で新たな配列を生成する必要があるというのは余計な処理にも思える

「連番配列の構造が結果に影響しない」の何が利点なのかというと、まぁ単に数式の文字数を少し減らせるというだけ。

  • MAP / ARRAYFORMULA 方式の連番配列サイズ: 行数 $= R_{ア} R_{イ}$、列数 $= 1$
MAP 方式から抜粋
=MAP(
  SEQUENCE(ROWS(A3:C4) * ROWS(E3:F5), 1, 0), LAMBDA(
  • REDUCE 方式の連番配列サイズ: 行数 $= R_{ア}$、列数 $= R_{イ}$
本方式から抜粋
=REDUCE(
  , SEQUENCE(ROWS(A3:C4), ROWS(E3:F5), 0), LAMBDA(

重畳処理 (reduce) って何？

ご存知ない人のために。

例えばこういうやつ。

JavaScript での例
const getMinAbs = nums => nums.reduce(  // 数値の配列から最小の絶対値を得る
  (acc, crr) => Math.min(acc, Math.abs(crr)),  // acc: 直前までの累積値、crr: 現在値
  Number.POSITIVE_INFINITY  // 最初に acc に渡される累積値の初期値
);
console.log(getMinAbs([3.14, -999, -1.5, 42]));  // 1.5
回 acc crr 処理 結果 備考
1 $+ \infty$ $3.14$ $\min(+ \infty, | 3.14 |)$ $3.14$ 初期値は「結果」に影響しないよう工夫する
2 $3.14$ $-999$ $\min(3.14, | -999 |)$ $3.14$
3 $3.14$ $-1.5$ $\min(3.14, | -1.5 |)$ $1.5$ 「結果」は要素ではなく累積値なので $-1.5$ ではない
4 $1.5$ $42$ $\min(1.5, | 42 |)$ $1.5$ 最終的にこれが返される

言葉で説明すると、

  • 配列の先頭から末尾まで全ての要素に自前の縮小関数を適用する
  • 縮小関数は、直前の自身の結果と現在の要素の値の 2 つを引数に取って 1 つの値に縮小 (reduce) する処理を行い、次の自身に渡す
    • このため、直前の結果はそれまでの全ての要素の累積値 (accumulator) と呼ばれる
    • こうして末尾の要素まで処理し終えると、得られる最後の結果は配列全体を一貫した処理で縮小した累積値となり、これが全体の結果として返される
  • 初回の処理では直前の結果という物が無いので、言語によってはデフォルトでは初回のみ先頭 2 つの要素を縮小関数の引数に取る（従ってループ数は 配列サイズ - 1 になる）事とし、オプションとして累積値の初期値を用意する事でそれを回避できる場合がある
    • JavaScript では上例の Number.POSITIVE_INFINITY の部分がそれで、もしこの初期値を設定しないと、先頭が負数の場合のために縮小関数の中で Math.min(acc, Math.abs(crr)) ではなく Math.min(Math.abs(acc), Math.abs(crr)) と余計な処理を追加しなければならない

という感じ。

"reduce"、つまり「減らす」という名の通り、多くの値の集まりから単一の集計結果にまとめられた値を得るのが本来の用途。
…なのだが、今回は単一の値にまとめるどころか新たな表として行をガンガン追加する使い方をしているので、「"reduce" に元より大きい配列なんて吐かせるな！！ 増やしてんじゃねーよ！！！」と怒られても文句は言えない気がする。

スプレッドシートの REDUCE 関数の初期値を攻略せよ

一般的な重畳処理については上記の通りだが、ここからは今回の数式の REDUCE 関数についてのお話。

どういう試行錯誤を経て最終的な数式に至ったかという軌跡を残しているだけなので、数式の成果物だけを享受すれば十分という人は読み飛ばして OK。

今回、REDUCE 関数の第 1 引数で指定する初期値は空の値とし、縮小関数では初回を判定して初期値を無視するようにした。

REDUCE 方式のクロス結合数式（再掲）
=REDUCE(
  , SEQUENCE(ROWS(A3:C4), ROWS(E3:F5), 0), LAMBDA(
    結果表, 結果行零始番号, LET(
      結果行, {
        CHOOSEROWS(A3:C4, QUOTIENT(結果行零始番号, ROWS(E3:F5)) + 1),
        CHOOSEROWS(E3:F5,      MOD(結果行零始番号, ROWS(E3:F5)) + 1)
      },
      IF(結果行零始番号 = 0, 結果行, VSTACK(結果表, 結果行))
    )
  )
)

Excel やスプレッドシートの関数の記法として、カンマ区切りで引数の場所は確保しつつ、そこに値を書かないようにする事ができる。
正確な呼び方はわからないが、とりあえず本記事ではこれを空の値と呼ぶ事にする。

第 1 引数を空の値にしている
=REDUCE(, 残りの引数)

まず、スプレッドシートの REDUCE 関数は、他言語のそれと違って「初期値を省略する事で初回のみ先頭 2 つを縮小関数の引数に取ってもらう」というような事ができない。

第 1 引数での初期値指定をカンマ区切りごと削って引数の数を減らすと、当然エラーになる。
また、カンマ区切りの構造を残して第 1 引数に空の値を与えたとしても、空の値は数値演算では 0、文字列演算では空文字列 "" として扱われるだけらしく、初期値の省略指定として機能する訳ではない。

main-集積-reduce02-初期値00-空の初期値実験.png

では、初回の行追加の際にその追加行が後の結果全体の 1 行目になってくれるような、つまり初期値の行が初回の行追加によって押し潰されたり無視されたり上書きされたりして勝手に消滅するような、そんな都合の良い初期値という物は無いのだろうか？
残念ながらそれも一切無い。色々試したが全て駄目だった。

main-集積-reduce02-初期値01-都合の良い初期値は無い.png

そういう訳で初期値を与えない方針は諦め、せっかく行番号を利用している事だし、結果行零始番号 = 0 で初回を判定して初回のみ累積値を無視するという形で解決する事に。
つまり初期値はどんな値でも影響無し！ という事で、REDUCE 関数の第 1 引数は単純に文字数が少ない空の値にした。

REDUCE 方式のクロス結合数式（再々掲）
=REDUCE(
  , SEQUENCE(ROWS(A3:C4), ROWS(E3:F5), 0), LAMBDA(
    結果表, 結果行零始番号, LET(
      結果行, {
        CHOOSEROWS(A3:C4, QUOTIENT(結果行零始番号, ROWS(E3:F5)) + 1),
        CHOOSEROWS(E3:F5,      MOD(結果行零始番号, ROWS(E3:F5)) + 1)
      },
      IF(結果行零始番号 = 0, 結果行, VSTACK(結果表, 結果行))
    )
  )
)

他の攻略法の考察

他の方法として、初期値には何らかの特別な値を期待または用意し、等価評価や IS云々 系の関数でそれを検出するという手もある。

今は空の値を初期値としているのだから ISBLANK 関数で検出できるし、初期値に NA 関数等で #N/A 値を用意すれば各処理では ISNA 関数で検出できるし（元案の terashim 氏がこれ）、もっと原始的に初期値を "__INITIAL__" のようにハードコードして IF(結果表 = "__INITIAL__", ... とする事もできる。

main-集積-reduce02-初期値02-他の攻略法.png

行番号ではなく配列の各要素で直接ループするような REDUCE 関数本来の使い方をする場合は、この類の検出法に頼る事になるだろう。
もちろん誤検出には要注意であり、これらの方法では表アの左上が特別な値と一致してしまった場合にその行とのクロス結合が丸ごと無視されてしまうといった事故が起こる。

main-集積-reduce02-初期値03-誤検出.png

また、ここまで初期値の無視を REDUCE 関数の内部でやってきたが、判定処理は初回だけで用済みなのに、それをループの全ての回で行っているのは非効率な気もする。
それよりは REDUCE 関数の外部で、初期値も含んだ処理後の結果全体から 1 行目だけを消す事ができれば、計算量こそ $2n$ にはなってしまうが、スプレッドシート関数によるネイティブ処理を頼れるという点でより良いかもしれない。

QUERY 関数で "offset 1" クエリを与えて全体を 1 行上に詰めるか、FILTER 関数で 1 行目以外真となるようなフィルター配列を与えるとそのようにできる。
なお、OFFSET 関数でもできそうに思うが、引数に配列ではなくセル参照を与えなければならない仕様なので今回はできない。

main-集積-reduce02-初期値04-1行目だけ消す.png

ただ、QUERY 関数では外部のクエリを通す都合か、表の中でエラーが生じた場合に本来の赤マーク付きのエラーではなくそれを文字列化した "#N/A" のような値になってしまうため、視覚的利便性を損なう。
また、FILTER 関数ではフィルター配列の行数が結果全体の行数と厳密に一致しなければならないので、後に触れる行数を増減させるような場合（→ § 行数も列数も可変（ただし #N/A として））にはかなり扱い辛い。

main-集積-reduce02-初期値05-queryとfilterの弱点.png

そのため、本記事では最もシンプルで無難な方法として行番号による検出を採用した。

行数も列数も可変（ただし #N/A として）

MAKEARRAY 及び MAP 方式では、列の自動展開によりアドリブで列数を増減できた。

一方こちらの REDUCE 方式は、行を動的に追加する処理であるため、そして VSTACK 関数が列数の食い違いを許容して縦に積めるために、列数だけでなく行数すらも可変となる。
ただし、食い違いで空いたセルは通常の空白ではなく #N/A エラーになってしまうので、その始末を着けるには更に IFNA 関数で全体を囲う等の工夫が必要となる。

main-集積-reduce03-行数も列数も可変だがNA.png

例によって IFS 関数も SWITCH 関数も使えない。地獄？
いやまぁ、LET 関数が無かったらもっと地獄だっただろうし…。

あと、数値の位取りが消えるのは普通になんで？？

VSTACK 関数 vs 配列リテラル記法

複数の範囲を縦に積むもう 1 つの方法として、配列リテラルの {範囲1; 範囲2} の記法がある。
しかしこの記法は、各範囲の列数が 1 つでも食い違うと全体が #VALUE! エラーになり、エラー文にも「ARRAY_LITERAL の配列リテラルで、1 つ以上の行の値が見つかりませんでした。」と怒られてしまう。

一方の VSTACK 関数は値の足りないセルだけが個別に #N/A エラーになるので、特に列や行の増減をしたい場合には柔軟性の点で上位互換となる。
ちなみに個別のエラー文は

このセルには値がありません。IFERROR 関数内で VSTACK を使用して、#N/A を、選択した値に置き換えることを検討してください。

となるのだが、スプレッドシート的には IFNA 関数よりも IFERROR 関数を推奨したいのだろうか。
エラーの種類は #N/A だとわかり切っているのに。

まぁ何にせよ、行数も列数も可変のおかげで、クロス結合を超える事をやろうとする場合に汎用性が最も高いのは REDUCE 方式、という事には間違い無い。
間違い無いが…、実際にやってみると「そこまでやる？」という気分になる。

=IFNA(REDUCE(, SEQUENCE(ROWS(A3:C4), ROWS(E3:F5), 0), LAMBDA(結果表, 結果行零始番号, LET(結果行, {CHOOSEROWS(A3:C4, QUOTIENT(結果行零始番号, ROWS(E3:F5)) + 1), CHOOSEROWS(E3:F5, MOD(結果行零始番号, ROWS(E3:F5)) + 1)}, 品物, CHOOSECOLS(結果行, 4), 列加工済結果行, IF(品物 = "小物入れ", {結果行, "←新商品"}, IF(品物 = "置き時計", {ARRAY_CONSTRAIN(結果行, 1, 3), "（廃番）"}, 結果行)), 行列加工済結果行, IF(結果行零始番号 = 3, VSTACK("↓新店舗", 列加工済結果行), 列加工済結果行), IF(結果行零始番号 = 0, 行列加工済結果行, VSTACK(結果表, 行列加工済結果行))))), "")

こんなのメンテしたいか？？
そもそもクロス結合の中でやるべき作業か？？？

おまけ 1. 大量のデータは危険

さて、ここまでの表ア・表イはせいぜい 2～3 行という小さなサイズだったが、これを両方とも 1000 行の表として与えたら同じように正常動作するだろうか。

toomany00-source.png

得られるクロス結合表は、行数が 1000 × 1000 で百万行というヤバいサイズになる。
こいつをテストしたい所だが、シートには行数やセル数の上限があるので、さすがにこれをそのまま出力したら上限にぶち当たるのがわかり切っていて良くない。

そこで、UNIQUE 関数を噛ませて重複行を除去してみる。
上図の通り新しい表ア・表イの実際の内容はいずれもほとんど空行なので、クロス結合表から重複行を除去すれば、理論上の結果はたったの 12 行に収まる。
あと、数式の簡略化のため、各方式の処理は名前付き関数 CROSSJOIN_BYROW、CROSSJOIN_MAKEARRAY、CROSSJOIN_MAP、CROSSJOIN_ARRAYFORMULA、CROSSJOIN_REDUCE として定義して用いる。

これなら、ちゃんと処理さえされれば普通に出力できるはず。
ちゃんと処理さえされれば。

toomany01-ideal.png

=UNIQUE(CROSSJOIN_何々(A3:C1002, E3:F1002))

BYROW 方式、MAKEARRAY 方式、MAP 方式の場合

ちゃんと処理され…ないんだな、これが。

toomany02-real00-cell-errors.png

=UNIQUE(CROSSJOIN_BYROW(A3:C1002, E3:F1002))

=UNIQUE(CROSSJOIN_MAKEARRAY(A3:C1002, E3:F1002))

=UNIQUE(CROSSJOIN_MAP(A3:C1002, E3:F1002))

CROSSJOIN_BYROW 関数、CROSSJOIN_MAKEARRAY 関数、CROSSJOIN_MAP 関数のいずれにおいても、数式を入れたセルは長いロード時間の果てに次のような #ERROR! となる。

この数式の計算中に、計算の上限に達しました。

知　っ　て　た

最終結果が十分に小さなサイズに収まるとしても、処理の過程の内部データがあまりにも膨大になるとこのようにエラーになってしまうのであった。

ARRAYFORMULA 方式の場合

ちゃんと処理され…た！！！

toomany02-real01-arrayformula-success.png

=UNIQUE(CROSSJOIN_ARRAYFORMULA(A3:C1002, E3:F1002))

CROSSJOIN_ARRAYFORMULA 関数では、ちゃんと処理できた。嘘だろ。
完全に失敗させるつもりで組んだ百万行テストだったので、この成功には驚かされた。
ネイティブな 最適化って すげー！

しかし当然、じゃあ 1 億行も行けるな！ という話にはできない。
百万行より先のどこに限界があるのかわからないだけでなく、どんな環境でも同じ百万行テストが必ず成功するかどうかすらわからない。
本題は「百万行までならできる」ではなく「組み合わせ爆発をナメるな」である点に留意されたい。

REDUCE 方式の場合

=UNIQUE(CROSSJOIN_REDUCE(A3:C1002, E3:F1002))

toomany02-real02-reduce-page-error.png

CROSSJOIN_REDUCE 関数は最も深刻で、長いロードの果てにページごと落ちる。
ちゃんと処理されないってレベルじゃねーぞ！！！

エラーコードは "Out of Memory" だったり "RESULT_CODE_HUNG" だったりしたが、とにかく何度やってもセルのエラーとかではなく絶対にページごと落ちる。
こうなるとセルをまともに触れなくなるため、この数式で保存されてしまったら修正すら一苦労になる。

実は本記事の着想元となった私的に制作した数式でも、似たような感じで REDUCE 関数に表の組み合わせの配列を出力させたところ、大量のデータの場合にページごと落ちる事例があった。
現状の REDUCE 関数の性能そのものに何か弱みがあるのかもしれない。

処理に失敗するにしても、せめてセルのエラーに留めておいて欲しい所だが…。
動的に配列を拡張できるような自由度の高さに対する代償なのだろうか。
いや、やはり「増やしてんじゃねーよ！！！！！」という事か。ゴメンちゃい。

表 2 つからクロス結合（直積）の表を得るな！

やり方を紹介しておいて何だが…。

クロス結合なんて物は組み合わせ爆発をわざわざ起こしに行くような蛮行であり、スプレッドシート関数の処理能力も有限なので、どの方式にせよ大量のデータを渡す事には十分な警戒を要する。
渡すはず無いじゃんと思いたいが、特に可変長のデータを取り扱うとサイズの事をうっかり失念するのが人間の性。
きちんと全容を掌握できる固定長のデータで使うようにするのが無難だろう。
その上で、安全性を求めるなら ARRAYFORMULA 方式が最善と思われる。

じゃあどこまでなら「大量」じゃないのか、というチキンレースもできるかもしれないが、不毛なのでやらない。
あと GAS は知らない。

おまけ 2. 値だけをクロス結合するならもっと簡単にできる

ここまでは 2 つの表の各行を、列の内容を保ってクロス結合していた。

もし元のデータがどちらも 1 列の値だけしかないなら、ARRAYFORMULA 関数の力でまた別の書き方ができる。
ここでは、1 列の表同士ならではの方法を紹介する。

valueonly00-source.png

その 0. CONCAT 方式

いきなり「もし」に「もし」を重ねるが、もしクロス結合の出力結果表すらも各ペアを区切り文字で結合しただけの 1 列のデータで良いなら、非常に短く済む。

valueonly01-concat00.png

=ARRAYFORMULA(
  TOCOL(A3:A4 & "＼" & TOROW(E3:E5))
)

TOCOL 関数を取り除いてみると、動作のイメージがより掴みやすくなるかもしれない。
※薄緑・薄赤の見出しセルは解説のために設けているだけで、数式では参照利用していない。

valueonly01-concat01-without-outer-tocol.png

なお、このイメージからわかる通り範囲 A3:A4 の箇所は縦 1 列である必要があるので、元がそうでない場合（横 1 行とか）はそこも TOCOL 関数で囲う必要がある。

その 1. CSV 方式

さて、先の CONCAT 方式の各結果を区切り文字で分割すれば、擬似的に配列へ復元できる。
要は CSV のようなやり方。

valueonly02-csv.png

=ARRAYFORMULA(
  SPLIT(
    TOCOL(A3:A4 & CHAR(9) & TOROW(E3:E5)),
    CHAR(9)
  )
)

ただし完全な文字列結合の後の再分割でしかない以上、当然ながら区切り文字と同じ文字が元データにあるとそれだけで破綻するので注意。先の例では ＼ がそう。
せめてもの抵抗で、データとして発生しにくい制御文字（ここでは文字コード 0x09 の水平タブ文字）を CHAR 関数で区切り文字に指定する事で、多少安全にしている。

ただ、例えば文字コード 0x07 のベル文字 \a を用いて =SPLIT(何か文字列、CHAR(7)) のような数式を書いてみたところ、

関数 SPLIT のパラメータ 2 の値は必ず指定してください。

とエラーになってしまった。
どうやら ASCII 制御文字の大部分は SPLIT 関数の第 2 引数に指定できない…というか指定しても空の値として扱われてしまうようで、試した限りで正常に指定できたのは 0x09（水平タブ HT \t）、0x0A（改行 LF \n）、0x0D（復帰 CR \r）、0x7F（抹消 DEL）の 4 種だけだった。

まぁそんな感じで、正直推奨できない。
推奨できないのだが、クロス結合の実現方法をググると本当にこのような文字列結合と再分割に依存する方法ばかり出てくるので、公式ヘルプに載ってしまっている情報の影響力に絶望させられる。

その 2. 並列 TOCOL 方式

そんな絶望の最中に突然、ARRAYFORMULA 関数のパワフルさを活用しつつ文字列結合と再分割を伴わない、全く別の方法が見付かった。

valueonly03-tocol00.png

=ARRAYFORMULA(
  {
    TOCOL(
      IF(SEQUENCE(ROWS(A3:A4), ROWS(C3:C5)), A3:A4)
    ),
    TOCOL(
      IF(SEQUENCE(ROWS(C3:C5), ROWS(A3:A4)), C3:C5),
      FALSE,
      TRUE
    )
  }
)
  • TOCOL 関数 https://support.google.com/docs/answer/13187258
    • 1 列に均す
    • 第 2 引数で空白を無視するかどうか、第 3 引数でスキャン方向を列毎にするかどうかを指定できる
  • split - Repeat each row N times in Google Sheets - Stack Overflow
    https://stackoverflow.com/questions/55481291
    • > 2021/12/16、TheMaster 氏の回答
      • 他の回答が手垢の付いた CSV 方式ばかり提案する中、全く別のアプローチで配列処理のみによる解決法を示し、本記事執筆時点での支持スコアは採用済みの 2019 年の回答（10 点）の倍に迫る（19 点）
      • 残念ながら質問タイトルに反して行ではなく値しか繰り返せない方法だが、応用すればこのおまけでは大活躍できる事に気付いた

まず、ここまでの方式が全て行毎の処理だったのに対し、この方式だけは列毎の処理になっているのが 1 つの特徴だろう。

そして、その各列の処理は次のような工程になっている。
なお、出力の 1 列目が表ア、2 列目が表イを担当しているので、ある列が担当する方の表を自表、そうでない方の表を他表とし、行数をそれぞれ $R_{自}$、$R_{他}$ とする。

  1. SEQUENCE 関数によって、$R_{自}$ 行 $R_{他}$ 列の配列を得る
    • 各セルの値は、偽でなければ（空や 0 や FALSE でなければ）何でも良い
      • そういう意味では各セルが偽でない配列さえ作れれば MAKEARRAY 関数等でも良いと思われるが、単純に SEQUENCE 関数の方が短く書ける
  2. IF 関数（と ARRAYFORMULA 関数）によって、自表の対応する行の値で全ての列を置き換える
  3. TOCOL 関数によって、1 列目ならデフォルトのまま行方向にスキャン、2 列目なら第 3 引数の指定で列方向にスキャンし、1 列に均す

valueonly03-tocol01-機序.png

うーん面白い。
数式が直感的かというとまた別だし、もしかしたら計算量の不利があったりするかもしれないが、個人的には文字列結合を経由するより配列処理だけで完結している点で遥かにスマートに思える。

単にスマートな気分になれるだけという訳ではなく、少なくともエラー値対応の点で実務上も優れている。
元の表にエラー値を持つセルがあった場合、CSV 方式では文字列結合を挟むせいで結果表の行全体がエラーになってしまうのに対し、こちらの並列 TOCOL 方式ではセル単位でエラーが保持される。

valueonly03-tocol02-エラー値.png

ただ、じゃあこれを推奨するかというと…。
元の表の列数が増えたら結局破綻するので、そもそも最初から多列対応の方法で良くない？ と思うが。
それでも、まだまだ発想の転換が眠っているかもしれないと思わせてくれた。

まとめ

各方式と特徴

数式に与える 2 つの表の引数名をそれぞれ 表ア、表イ とする。

  • BYROW 方式
    • 結果の行数や列数は固定（クロス結合より複雑な事をやろうとしてもいじりようが無い）
=WRAPROWS(
  FLATTEN(
    BYROW(
      表ア, LAMBDA(
        表ア各行, TOROW(
          BYROW(
            表イ, LAMBDA(表イ各行, {表ア各行, 表イ各行})
          )
        )
      )
    )
  ),
  COLUMNS(表ア) + COLUMNS(表イ)
)
  • MAKEARRAY 方式
    • 結果の行数は固定、列数は可変（LAMBDA 関数内でいじれる）
    • 引数 _ は未使用
=MAKEARRAY(
  ROWS(表ア) * ROWS(表イ), 1, LAMBDA(
    結果行番号, _, {
      CHOOSEROWS(表ア, QUOTIENT(結果行番号 - 1, ROWS(表イ)) + 1),
      CHOOSEROWS(表イ,      MOD(結果行番号 - 1, ROWS(表イ)) + 1)
    }
  )
)
  • MAP 方式
    • 結果の行数は固定、列数は可変 
=MAP(
  SEQUENCE(ROWS(表ア) * ROWS(表イ), 1, 0), LAMBDA(
    結果行零始番号, {
      CHOOSEROWS(表ア, QUOTIENT(結果行零始番号, ROWS(表イ)) + 1),
      CHOOSEROWS(表イ,      MOD(結果行零始番号, ROWS(表イ)) + 1)
    }
  )
)
  • ARRAYFORMULA 方式
    • 結果の行数や列数は固定
    • 大量のデータに最も強い（恐らく処理効率が高い）
=ARRAYFORMULA(
  LET(
    表イ行数, ROWS(表イ),
    結果行零始番号, SEQUENCE(ROWS(表ア) * 表イ行数) - 1,
    {
      CHOOSEROWS(表ア, QUOTIENT(結果行零始番号, 表イ行数) + 1),
      CHOOSEROWS(表イ,      MOD(結果行零始番号, 表イ行数) + 1)
    }
  )
)
  • REDUCE 方式
    • 元案: terashim 氏 https://terashim.com/posts/table-join-google-sheets/
      • 結構改変してあるので、元の数式を見たい方は元記事で公開されているシートへどうぞ
    • 結果の行数・列数共に可変
    • 大量のデータの場合、セルのエラーというよりページごと落ちる事象あり
=REDUCE(
  , SEQUENCE(ROWS(表ア), ROWS(表イ), 0), LAMBDA(
    結果表, 結果行零始番号, LET(
      結果行, {
        CHOOSEROWS(表ア, QUOTIENT(結果行零始番号, ROWS(表イ)) + 1),
        CHOOSEROWS(表イ,      MOD(結果行零始番号, ROWS(表イ)) + 1)
      },
      IF(通し番号 = 0, 結果各行, VSTACK(結果表, 結果行))
    )
  )
)

=ARRAYFORMULA(
  SPLIT(
    TOCOL(表ア & CHAR(9) & TOROW(表イ)),
    CHAR(9)
  )
)

=ARRAYFORMULA(
  {
    TOCOL(
      IF(SEQUENCE(ROWS(表ア), ROWS(表イ)), 表ア)
    ),
    TOCOL(
      IF(SEQUENCE(ROWS(表イ), ROWS(表ア)), 表イ),
      FALSE,
      TRUE
    )
  }
)

個人的 Tier 表

ランク 方式 ⋯ 講評
S
  • ARRAYFORMULA 方式 ⋯ 安全第一！ 柔軟な編集は他でやるべき
A （対象者無し）
B
  • MAP 方式 ⋯ 下よりマシだが A には値しない
  • REDUCE 方式 ⋯ 危険だが、どうしても曲芸を組み込みたいなら最強
C
  • MAKEARRAY 方式 ⋯ MAP 方式より数式の洗練度が僅差で劣る
D
  • BYROW 方式 ⋯ 自由度が低いのに代わりの強みも無い
  • 並列 TOCOL 方式（1 列同士） ⋯ 1 列同士としては優れるが所詮そこまで
E
  • CSV 方式（1 列同士） ⋯ 他に手が無い場合にしか許されず、他にもうある

共通の注意事項

大量のデータを与えると簡単に組み合わせ爆発を起こして死ぬ。
でも ARRAYFORMULA は強い。

​

おわり

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?