数式/関数：数列単位で横方向に並んだ情報を組み替える

何列かで１組になっているデータを縦方向に組み替える。クエリで一度書いたネタなのだが、これを数式でやるとどうなるか考えてみた。

ｎ列一組で横方向に並んだデータを縦方向に組み替える

まずは一応、クエリ版をおさらい。

let
    ソース = Excel.CurrentWorkbook(){[Name="データ範囲"]}[Content],
    ヘッダーへの昇格 = Table.PromoteHeaders(
        ソース, [PromoteAllScalars=true]
    ),
    列のピボット解除 = Table.UnpivotOtherColumns(
        ヘッダーへの昇格, {"番号", "店舗名"}, "属性", "値"
    ),
    テーブル分割 = Table.Split(列のピボット解除, 3),
    リスト編集 = List.Transform(テーブル分割, each
        Table.FromRows(
            {{[番号]{0}, [店舗名]{0}} & [値]},
            type table [
                番号=Int64.Type, 店舗名=text,
                発注番号=Int64.Type, 品名=text, 数量=Int64.Type
            ]
        )
    ),
    テーブル結合 = Table.Combine(リスト編集)
in
    テーブル結合

連番作って整数除算入れて列のピボット入れて、みたいなことをするよりこっちのほうがすっきりする。

これを数式（Microsoft365）でやるとどうなるか。

=LET(
    x,WRAPROWS(TOCOL(C2:N5),3),
    y,INDEX(A2:B5,SEQUENCE(ROWS(A2:B5)*4,,0,1/4)+9^-7+1,{1,2}),
    VSTACK(A1:E1,SORT(FILTER(HSTACK(y,x),INDEX(x,,1)<>""),1))
)

こんな感じ。列数や組数の上限を分けて、変数を日本語で書くと下記のようになる。

=LET(
    列数,3,
    組数,4,
    列1,WRAPROWS(TOCOL(C2:N5),列数),
    列2,INDEX(A2:B5,SEQUENCE(ROWS(A2:B5)*組数,,0,1/組数)+9^-7+1,{1,2}),
    VSTACK(A1:E1,SORT(FILTER(HSTACK(列2,列1),INDEX(列1,,1)<>""),1))
)

「+9^-7」は演算誤差対策。行数が可変になる場合は、「A2:B5」「C2:N5」は取り込んだ範囲から分けたほうがいいと思う。