Excelでやってはいけないことの一つが「マトリクス表への直接入力」。これをやる人は必ずといっていいほど同形式の表をシートで分けて量産してしまう。見出しにセル結合を入れちゃう人もいる。そして後でデータをまとめたり集計したりする時に困る。ここまでセット。
悪いことづくめなのにどうにもこのやり方がなくならないのは「入力する時に楽」なんだと思う。縦横の見出しに入力してある値は先に用意してあるから、入力しないでいい。翌月はそのシートを複製すればいいから超ラクチン。そういう理屈。
Power Queryがない時代は、これをやってしまったら組み換えにマクロか長たらしい数式（かピボットテーブルの複数のワークシート範囲指定）が必要だったんだけど、クエリならピボット解除すればあっさり解決するようになった。
結果の１セルに複数の情報を入れ込むのも非推奨行為なんだけど、TEXTSPLIT関数が使える今となってはどっちでもいいのかもしれない。 Microsoft365なら数式一本でもできんことはないし。
数式案（Excel for Microsoft365の場合）
=LET(
_key,"○",
_tbl,テーブル1,
_seqr,SEQUENCE(ROWS(_tbl)),
_seqc,SEQUENCE(,COLUMNS(_tbl)-1,2),
_rng,INDEX(_tbl,_seqr,_seqc),
_hl1,テーブル1[曜日],
_hl2,INDEX(テーブル1[#見出し],1,_seqc),
_c1,FILTER(_hl1,BYROW(_rng,LAMBDA(_a,OR(_a=_key)))),
_c2,BYROW(_rng,LAMBDA(_a,TEXTJOIN(",",,IF(_a=_key,_hl2,"")))),
_c3,FILTER(_c2,_c2<>""),
HSTACK(_c1,_c3)
)
とはいえ、なんというかまだるっこしい。素直にクエリでやったほうがいいと思う。
クエリ案
let
テーブル1 = [
ブック = Excel.CurrentWorkbook(),
ソース = ブック{[Name="テーブル1"]}[Content],
見出し = Table.ColumnNames(ソース),
タイプ = List.Transform(見出し, each {_, type text}),
型の変更 = Table.TransformColumnTypes(ソース, タイプ)
][型の変更],
列のピボット解除 = Table.UnpivotOtherColumns(
テーブル1, {"曜日"}, "属性", "値"
),
行の抽出 = Table.SelectRows(
列のピボット解除, each ([値] = "○")
),
行のグループ化 = Table.Group(
行の抽出, {"曜日"},
{"該当月", each _, type table [属性=text]}
),
該当月列を編集 = Table.TransformColumns(
行のグループ化, {
"該当月", each Text.Combine([属性], ", "), type text
}
)
in
該当月列を編集