1. まえがき
Yahoo!知恵袋にExcelのリストのグループ化で、同じグループのレコードが複数ある場合に、その値を1行の横に並べる方法の質問がありました。
https://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q13319052933
ワークシート関数とモダンExcel（Power Query、Power PivotのメジャーDAX）の方法を紹介します。
2. 入力リスト
テーブル名：テーブル1。元の表です。本記事では、左上のセル(ID)をA2セルとします。
| ID |
氏名 |
担当 |
| 1 |
ONE |
A |
| 1 |
ONE |
B |
| 1 |
ONE |
C |
| 2 |
TWO |
D |
| 2 |
TWO |
E |
| 2 |
TWO |
F |
| 3 |
THREE |
G |
| 3 |
THREE |
H |
| 3 |
THREE |
I |
3. 出力リスト
求める表です。IDと氏名をグループ化し、担当を横に並べます。カンマ区切りで列挙するものと、列を分けるものがあります。
1) 連結
| ID |
氏名 |
担当 |
| 1 |
ONE |
A, B, C |
| 2 |
TWO |
D, E, F |
| 3 |
THREE |
G, H, I |
2) 列分離
| ID |
氏名 |
担当1 |
担当3 |
担当3 |
| 1 |
ONE |
A |
B |
C |
| 2 |
TWO |
D |
E |
F |
| 3 |
THREE |
G |
H |
I |
4. 色々な方法
1) TEXTJOIN関数 連結
Excel2021以降
出力表の左上のセル(ID)をE3とします。E3:G3には項目名、E4:F6にはIDと氏名を入力します。
セルG4に次の式を入れ、G6までコピーします。
=TEXTJOIN( //テキストを結合
",", //区切り文字
TRUE, //空の文字列を無視
FILTER( //第1引数の[担当]列を第2引数の条件でフィルタした配列を返す
テーブル1[担当], //フィルター対象
(テーブル1[ID] = E4) * (テーブル1[氏名] = F4) //[ID]と[氏名]が集計表の項目と同じならTrue
)
)
【注意】
- セルに書く式は、//以降のコメントを省いてください。ワークシート式に//でコメントを書くことはできません。
- 構造化参照を使用しています。セルアドレス参照でもOKです。
2) TOROW関数 列分離
Excel2024以降
列分離は、これが最も簡単です。項目見出し行は個別に入力が必要です。
出力表の左上のセル(ID)をE9とします。E9:I9には項目名、E10:F12にはIDと氏名を入力します。
セルG10に次の式を入れ、G12までコピーします。
=TOROW( //配列を横に展開する
FILTER( //第1引数の[担当]列を第2引数の条件でフィルタした配列を返す
テーブル1[担当], //フィルター対象
(テーブル1[ID] = E10) * (テーブル1[氏名] = F10) //[ID]と[氏名]が集計表の項目と同じならTrue
)
)
3) PIVOTBY関数 連結
Excel 365
連結は、これが最も簡単です。項目見出し行も出力されます。
出力したい範囲の左上のセルに次の式を入れます。
=PIVOTBY(
テーブル1[[#すべて],[ID]:[氏名]], //行グループ
, //列グループ
テーブル1[[#すべて],[担当]], //値
ARRAYTOTEXT, //「,」で区切り文字列を結合した値
3, //入力範囲に項目名を含み、出力に表示する
0 //小計行や総計行を表示しない
)
4) GROUPBY+LAMBDA関数 連結
Excel 365
PIVOTBYから項目名見出し行の出力を省き、LAMBDAで集計関数を定義したものです。
出力したい範囲の左上の1つ下のセルに次の式を入力します。
=GROUPBY(
テーブル1[[ID]:[氏名]], //グループ化対象
テーブル1[担当], //値
LAMBDA(x, TEXTJOIN(",",,x)), //値の集計をLAMBDAで定義。内容は「,」区切りで文字列を連結
0, //入力範囲に項目名を含まず、出力に表示しない
0 //合計を表示しない
)
5) INDEX関数 列分離
Excel2021以前でも使用できる関数です。
出力表の左上のセル(ID)をE21とします。E21:I21には項目名、E22:F24にはIDと氏名を入力します。
セルG22に次の式を入れます。
=IFERROR(
INDEX(
$C:$C, //担当を取り出す
1 / LARGE( // 1を(1/行番号)で割り、行番号を取り出す
INDEX(
($A$3:$A$100 = $E22) * ($B$3:$B$100 = $F22) / ROW($3:$100), //「IDと氏名が合う行は 1/行番号=行番号の逆数、そうでなければ 0」の配列
),
COLUMN($A$2) //担当n列の列順番を取得。LARGEでn番目に大きい1/行番号を返す
)
),
"" // 合う行番号が無ければエラーになるので""を返す
)
行番号を逆数にしている理由は、内側のINDEXで一致する行が無くて0が返された時、外側のINDEXの第2引数の式を1/0とすることでエラーにして、表示を""にするためです。これにより、IF文で0を検出する必要が無くなるので、式が短くなります。一致する行があった時に行番号を取り出せるように、内側のINDEXで一致した行番号を逆数にしています。
この式は難解なので、Excel2021以前なら、Power Queryを使用したほうが分かりやすいです。仕事で他の人が解読しにくい式を使うのは、避けたほうが良いと思います。
6) REPT,CONCAT,TEXTSPILIT 列分離
Excel2021以降
出力表の左上セル(ID)をE28とします。
セルG29に次の式を入れます。E28:I28には項目名、E29:F31にはIDと氏名を入力します。
=IFERROR(
TEXTSPLIT( // 下記配列を","で分離
CONCAT( // 下記配列を結合
REPT( // IDが一致する担当に","を付けた配列
テーブル1[担当]&",",
テーブル1[ID]=E29
)
),
",", // 分離区切り記号
,
TRUE
),
""
)
7) Power Query 連結
Excel2021以前でも使用できます。項目見出し行も出力します。
操作手順
- テーブルを選択し、[データ]-[データの取得v]-[テーブルまたは範囲から]でPower Queryエディタに取込む。
- Ctrlキーを押しながら"ID"と"氏名"列を選択し、[ホーム]-[グループ化]で"新しい列名"に"担当"と入力し[OK]。
数式バーの行カウントの"each Table.RowCount(..."を、文字列結合の"each Text.Combine(..."に書き換えます。
変更前
= Table.Group(変更された型, {"ID", "氏名"}, {{"担当", each Table.RowCount(_), Int64.Type}})
変更後
= Table.Group(変更された型, {"ID", "氏名"}, {{"担当", each Text.Combine([担当],","), Text.Type}})
連結コード全体(PQC)
let
ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
変更された型 = Table.TransformColumnTypes(ソース,{{"ID", Int64.Type}, {"氏名", type text}, {"担当", type text}}),
// グループ化された行 = Table.Group(変更された型, {"ID", "氏名"}, {{"担当", each Table.RowCount(_), Int64.Type}}) のeach Table.RowCount()部分を次のように書き変える
グループ化された行 = Table.Group(変更された型, {"ID", "氏名"}, {{"担当", each Text.Combine([担当],","), Text.Type}}) // 行のカウントに替えて、","区切りの値の結合を行う
in
グループ化された行
Power Query Mの数式を直接修正するので、一見難しそうですが、行のカウント関数を文字列の結合関数に置き換えているだけですので、理解しやすいと思います。
8) Power Query 列分離
Excel2021以前でも使用できます。
2つのコードを示します。Aの方法は、数式バーでの式の修正が不要です。Bの方法は、数式バーでの式の修正が必要です。
項目見出し行も出力します。
操作手順A
- テーブルを選択し、[データ]-[データの取得v]-[テーブルまたは範囲から]でPower Queryエディタに取込む。
- Ctrlキーを押しながら"ID"と"氏名"列を選択し、[ホーム]-[グループ化]で"新しい列名"に"TEMP"を入力し、"操作"に[すべての行]を選び[OK]。
- [列の追加]-[カスタム列]で次のように入力。
新しい列名：カスタム
カスタム列の式： = Table.AddIndexColumn([TEMP], "インデックス", 1)
- "TEMP"列を右クリックし[削除]。
- "カスタム"列の右の矢印アイコンをクリックし、"担当"と"インデックス"以外のチェックを外し[OK]。
- "インデックス"列を選択し、[変換]-[書式v]-[プレフィックスの追加]で"値"に"担当"と入力し[OK]。
- "インデックス"列を選択し、[変換]-[列のピボット]。
- Ctrlキーを押しながら"担当n"列を選択し、[変換]-[データ型v]-[テキスト]。
- [ホーム]-[閉じて読み込むv]-[閉じて次に読み込む]で"テーブル"を選択し[OK]。
列分離コード全体(PQD)
let //このコードは長いですが、処理内容は難しくなく、連番付きの項目名も自動生成する。
ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
変更された型 = Table.TransformColumnTypes(ソース,{{"ID", Int64.Type}, {"氏名", type text}, {"担当", type text}}),
グループ化された行 = Table.Group(変更された型, {"ID", "氏名"}, {{"TEMP", each _, type table [ID=nullable number, 氏名=nullable text, 担当=nullable text]}}), //グループ毎のTableを格納した列(TEMP)を作成
追加されたカスタム = Table.AddColumn(グループ化された行, "カスタム", each Table.AddIndexColumn([TEMP], "インデックス", 1)), //TEMP列に格納されたグループ毎のTableに連番列(インデックス)を追加したTableを格納した列(カスタム)を追加
削除された列 = Table.RemoveColumns(追加されたカスタム,{"TEMP"}), //TEMP列を削除
#"展開された カスタム" = Table.ExpandTableColumn(削除された列, "カスタム", {"担当", "インデックス"}, {"担当", "インデックス"}), //連番(インデックス)と連番に対応する値項目(担当)を展開
追加されたプレフィックス = Table.TransformColumns(#"展開された カスタム", {{"インデックス", each "担当" & Text.From(_, "ja-JP"), type text}}), //連番を項目名にするので、連番(インデックス)の前に項目名(担当)を追加
ピボットされた列 = Table.Pivot(追加されたプレフィックス, List.Distinct(追加されたプレフィックス[インデックス]), "インデックス", "担当"), //連番付きの項目名列(インデックス)を横にピボット展開
変更された型1 = Table.TransformColumnTypes(ピボットされた列,{{"担当1", type text}, {"担当2", type text}, {"担当3", type text}})
in
変更された型1
ステップは多いですが、グループ毎のテーブルにインデックスを付けて、それを展開し、その列をピボットしているだけです。
参考
【Power Query】グループごとに連番をつける方法
https://blog.truestar.co.jp/microsoft/power-query/20210707/45919/
上記コードはクリック操作で完成するようにしていますが、「グループ化された行」のステップのコードを詳細エディタで直接書くと、次の操作手順BのようにTEMP列の作成を省略することができ、2ステップ削減できます。
操作手順B
- テーブルを選択し、[データ]-[データの取得v]-[テーブルまたは範囲から]でPower Queryエディタに取込む。
- Ctrlキーを押しながら"ID"と"氏名"列を選択し、[ホーム]-[グループ化]で"新しい列名"に"カウント"を入力し、"操作"に[すべての行]を選び[OK]。
数式バーの式のeach以降の部分を次のように書き換える。
変更前
= Table.Group(変更された型, {"ID", "氏名"}, {{"カウント", each _, type table [ID=nullable number, 氏名=nullable text, 担当=nullable text]}})
変更後
= Table.Group(変更された型, {"ID", "氏名"}, {{"カウント", each Table.AddIndexColumn(_, "インデックス", 1, 1), type table}})
変更前の式はカウント列にグループ化した全行のテーブル""を格納していますが、変更後の式は""にインデックス列を加えています。
- "カウント"列の右の矢印アイコンをクリックし、"担当"と"インデックス"以外のチェックを外し[OK]。
- "インデックス"列を選択し、[変換]-[書式v]-[プレフィックスの追加]で"値"に"担当"と入力し[OK]。
- "インデックス"列を選択し、[変換]-[列のピボット]。
- Ctrlキーを押しながら"担当n"列を選択し、[変換]-[データ型v]-[テキスト]。
- [ホーム]-[閉じて読み込むv]-[閉じて次に読み込む]で"テーブル"を選択し[OK]。
列分離コード全体(PQD2)
let //このコードは長いですが、処理内容は難しくなく、連番付きの項目名も自動生成する。
ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
変更された型 = Table.TransformColumnTypes(ソース,{{"ID", Int64.Type}, {"氏名", type text}, {"担当", type text}}),
グループ化された行 = Table.Group(変更された型, {"ID", "氏名"}, {{"カウント", each Table.AddIndexColumn(_, "インデックス", 1, 1), type table}}), //グループ毎のTableにインデックスを追加（数式バーか詳細エディタで記述します。）
#"展開された カウント" = Table.ExpandTableColumn(グループ化された行, "カウント", {"担当", "インデックス"}, {"担当", "インデックス"}), //連番(インデックス)と連番に対応する値項目(担当)を展開
追加されたプレフィックス = Table.TransformColumns(#"展開された カウント", {{"インデックス", each "担当" & Text.From(_, "ja-JP"), type text}}), //連番を項目名にするので、連番(インデックス)の前に項目名(担当)を追加
ピボットされた列 = Table.Pivot(追加されたプレフィックス, List.Distinct(追加されたプレフィックス[インデックス]), "インデックス", "担当"), //連番付きの項目名列(インデックス)を横にピボット展開
変更された型1 = Table.TransformColumnTypes(ピボットされた列,{{"担当1", type text}, {"担当2", type text}, {"担当3", type text}})
in
変更された型1
参考
Power Query：縦方向のデータを横並びで出力する : なんでもエクセル : 2022/11/02
https://nandemo-xl.hatenablog.com/entry/2022/11/02/150925
9) メジャー 連結
ピボットテーブルのフィルタコンテキストでフィルタされた'PPC'テーブルに対し、1行ずつ[担当]列の値を抽出し、","でその値を連結します。
連結限定ですが、Power Queryより分かりやすいと思います。
操作手順
- テーブルを選択し[データ]-[データの取得v]-[テーブルまたは範囲から]でPower Queryエディタに取込む。
- [ホーム]-[閉じて読み込むv]-[閉じて次に読み込む]で"ピボットテーブル"と"このデータをデータモデルに追加する"を選択し[OK]。
取得コード全体（PPC）
let //本例はPower Queryに取込んでいますが、テーブルを直接データモデルに追加すれば、クエリは不要です。
ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
変更された型 = Table.TransformColumnTypes(ソース,{{"ID", Int64.Type}, {"氏名", type text}, {"担当", type text}})
in
変更された型
- "ピボットテーブルのフィールド"の'PPC'テーブルを右クリックし、[メジャーの追加]で名前を"M担当"と入力し、次のように式を入力する。
メジャー
M担当: = CONCATENATEX('PPC', 'PPC'[担当], ",")
メジャーで担当を連結します。
- "ピボットテーブルのフィールド"で次のように項目をドラッグします。
行：[ID]、[氏名]、Σ値：[M担当]
5. サンプル
ダウンロード - 250822yokokahen.xlsx
1) Power Queryエディタの表示方法
- [データ]-[クエリと接続]で右に"クエリと接続"を表示。表示されない時は、もう一度操作する。
- 適当なクエリを右クリックし、[編集]でPower Query エディタが開く。
- 右の適用したステップの各ステップをクリックし、各ステップでどのように表が加工されるのかを確認する。
- 各ステップの右の歯車アイコンで追加した列の式や設定などの確認をする。
- [ホーム]-[詳細エディタ]で全体のPower Query Mのコードが表示される。各ステップの数式バーでもコードを確認できる。
2) データの更新方法
- 元データを修正したらファイルを保存。
- [データ]-[すべて更新v]-[すべて更新]。
3) メジャーDAX式の表示方法
- ピボットテーブルを選択し、右に"ピボットテーブルのフィールド"を表示。
- [fx …]を右クリックし[メジャーの編集]で式を表示。
6. 関連記事
Excel Power Query Power Pivotで項目数が変化する集計 : 当ブログ : 2025/02/27
https://bigarrow.tea-nifty.com/blog/2025/02/post-599235.html
最近のコメント