文章で説明するのが難しいけど、「項目名1・値1・項目名2・値2……」のように、２行１組で縦一列になっているデータを組み替えたい時の話。
１組あたりの項目数が固定の場合は比較的簡単（ただし、Microsoft365なら）。
=LET(
_wc,WRAPCOLS(TOROW(A:A,1),2),
_col,4,VSTACK(TAKE(_wc,1,_col),
WRAPROWS(TAKE(_wc,-1),_col))
)２行折り返しのデータに変換してから２行目を４列折り返して見出しと縦結合すればいい。文章だと伝わりにくいかもしれないけど、つまりは画像のように２段階で組み替えている。
こうしてから（C1に「WRAPCOLS(TOROW(A:A,1),2)」）
こう（C4に「=WRAPROWS(TAKE(C1#,-1),4)」）。
一応クエリでもやってみる。やっていることはマウス操作でできることがほとんど。A列を「データ範囲」と名前定義しているものとして、
let
ソース = Excel.CurrentWorkbook(){[Name="データ範囲"]}[Content],
リスト変換 = ソース[Column1],
上位行の保持 = List.FirstN(リスト変換, each _<>null),
見出しリスト = List.Alternate(上位行の保持, 1, 1, 1),
値リスト = List.Alternate(List.Skip(上位行の保持, 1), 1, 1, 1),
リスト結合 = List.Zip({見出しリスト, 値リスト}),
テーブル変換 = Table.FromRows(リスト結合),
行のグループ化 = Table.Group(
テーブル変換, {"Column1"},
{
"グループ",
each Table.AddIndexColumn(_, "Column3", 1, 1),
type table
}
),
グループ列の展開 = Table.ExpandTableColumn(
行のグループ化, "グループ",
{"Column2", "Column3"}
),
列のピボット = Table.Pivot(
グループ列の展開,
List.Distinct(グループ列の展開[Column1]),
"Column1",
"Column2"
),
列の削除 = Table.RemoveColumns(列のピボット,{"Column3"})
in
列の削除こんな感じ。
ところがこの手のデータは「こういうルール（４項目ひとくくり）になっているはず」が通じないことも多い。読み込んでみたら「備考」みたいな項目の行が、入っていたり入っていなかったりする場合もあるかもしれない。
さっきと違って、２組目にだけ「備考（A17:A18）」が追加されている。こうなると固定では読み込めないので、一工夫必要になる。
まずは数式でやってみる。
=LET(
_wc,WRAPROWS(TOCOL(A:A,1),2),
_set,SCAN(0,TAKE(_wc,,1),LAMBDA(x,y,x+(y=TAKE(_wc,1,1)))),
_hd1,UNIQUE(_set),
_hd2,TRANSPOSE(UNIQUE(TAKE(_wc,,1))),
REDUCE(_hd2,_hd1,LAMBDA(xx,yy,VSTACK(xx,
SCAN("",_hd2,LAMBDA(x,y,
IFERROR(FILTER(TAKE(_wc,,-1),(TAKE(_wc,,1)=y)*(_set=yy)),"")
))
)))
)見やすさ重視で改行は超適当。ただ見ての通り数式でやるととてもややこしくなる。
こういう場合はクエリでやったほうがいいように思う。
let
ソース = Excel.CurrentWorkbook(){[Name="データ範囲2"]}[Content],
リスト変換 = ソース[Column1],
上位行の保持 = List.FirstN(リスト変換, each _<>null),
見出しリスト = List.Alternate(上位行の保持, 1, 1, 1),
値リスト = List.Alternate(List.Skip(上位行の保持, 1), 1, 1, 1),
組リスト = [
組番リスト = List.Accumulate(
見出しリスト,
{0},
(x,y)=> {Number.From(y="ユーザーID") + x{0}} & x
),
リスト反転 = List.Reverse(組番リスト),
先頭行を削除 = List.Skip(リスト反転)
][先頭行を削除],
リスト結合 = List.Zip({見出しリスト, 値リスト, 組リスト}),
テーブル変換 = Table.FromRows(リスト結合),
列のピボット = Table.Pivot(
テーブル変換,
List.Distinct(テーブル変換[Column1]),
"Column1",
"Column2"
),
列の削除 = Table.RemoveColumns(列のピボット, {"Column3"})
in
列の削除めいっぱい丁寧にやってみた。多分これでいいはず。
