テーブルから列を規則的に並べ替える

はじめに

ExcelドカタCH さんのYouTubeチャンネルにて上げられていた下記動画のネタをワークシート関数のみで実装してみた記事です。

元データとなる人名リストは ChatGPT に頼んで作ってもらいました。

元データを格納したテーブルから、5行ごとに改列、4列ごとに改ページした表を作成します。

元データから行・列番号を作成する

元データから以下のようなテーブルを作成します。

元データから0始まりのインデックスを作成します。

[B1]
=MIN(テーブル1[index])

[index]
=IFERROR(VALUE(TEXTBEFORE([@列1], ".")), "")

[0index]
=[@index]-$B$1

5行ごとに改列して、4列ごとに改ページするような数列を計算します。

[D1]
5
[row]
=MOD([@0index], $D$1)

[E1]
4
[col]
=LET(_quo, QUOTIENT([@0index], $D$1), MOD(_quo, $E$1))

[F1]
=$D$1*$E$1
[page]
=QUOTIENT([@0index], $F$1)

page, row, col から key を、元データからよみがなを取り除いて value を作成します。

[key]
=[@page]&","&[@row]&","&[@col]

[value]
=LET(_tb, TEXTBEFORE([@列1], "("), TRIM(_tb))

規則的に並べ替えた表を作成

key を並べた表を作成、それを使ってテーブルから value を取ってくることで、テーブルで計算した規則通りに並べ替えます。

page と row をそれぞれ並べたスピルを作成します。
２つのスピルの全組み合わせ（直積）を作成するため、TOCOL, TOROW を利用して文字列結合を行います。

[J3]
=LET(_uni, UNIQUE(テーブル1[page]), _flt, FILTER(_uni, NOT(ISERROR(_uni))), TOCOL(SORT(_flt)))

[K3]
=LET(_uni, UNIQUE(テーブル1[row]), _flt, FILTER(_uni, NOT(ISERROR(_uni))), TOCOL(SORT(_flt)))

[L3]
=TOCOL(TOCOL(J3#) & "," & TOROW(K3#))

行方向に col を並べたスピルを作成します。
行と列のスピルから文字列結合して key を並べた表を作成します。

[M2]
=LET(_uni, UNIQUE(テーブル1[col]), _flt, FILTER(_uni, NOT(ISERROR(_uni))), TOROW(SORT(_flt)))

[M4]
=L3# & "," & M2#

key のスピルから元テーブルの value をXLOOKUPします。

[R3]
=XLOOKUP(M3#, テーブル1[key], テーブル1[value], "-")

下記のように、改列パラメータを変えたり、途中データを削除しても、問題なく表示されます。