Excel Power Queryで表をリスト形式に整形する方法(まとめ)
1. 前置き
ネットや書籍などに、Excel Power Queryで表をリスト形式(データベース形式)に整形する方法が解説されていますが、表の形によりその方法で整形できないことがあります。
そこで、整形のメモを整理してみました。なお、当方は素人なので、ここで紹介するものよりもっと良い方法があると思います。古いメモ程、素人度が高いです。使用される場合は、検算を行い自己責任でお願いいたします。
2. 整形方法
1ファイル、1シートの色々な表の整形方法です。ChatGPTを利用して作成したものが多いです。ChatGPTには、次のような質問をしています。
ChatGPTなどへの質問内容
- データを簡略化した整形前と整形後のテキストの表を入れる。項目数は簡略化しないほうが良い場合もある。表はカンマ区切りでもTAB区切りでも良い。
- Excel、Power Queryであることを書く。
- クリック操作で作るのか、Power Query Mのコードの回答を求めるのかを書く。ChatGPTなどのLLMは、コードの回答の方が得意です。
- ある程度の整形方法を指示する。例えば、似たような事例とか、途中まで自分で作成した方法を記載する。
1回で正解にならないことが多いので、4)の方法を伝えることが大事です。エラーが出た場合は、エラーが出たコードと、エラーメッセージと、エラーの理由と、その回避方法を使えると良いです。(例:aaaというボタンが表示されないので、bbbが使えるか検討してください。)
m) 横に2種類の項目が種類順に繰り返し並んだ表をExcelで整形(Power Query, ワークシート関数):2025/07/19:当ブログ
https://bigarrow.tea-nifty.com/blog/2025/07/post-ccee90.html
1ファイル、1シート、1表、横に同一項目のレコードが並ぶ。Power Query M 詳細エディタでコーディング
| 品番 | 品名 | 店舗NO. | 納期1 | 納期2 | 納期3 | 数量1 | 数量2 | 数量3 | 受信日 |
|---|---|---|---|---|---|---|---|---|---|
| ABCD-123 | ゴミ袋 | ○○○ | 2025/1/1 | 2025/1/4 | 2025/1/20 | 35 | 200 | 1 | 2024/12/1 |
| ABCE-456 | ブラシ | ○○○ | 2025/1/3 | 2025/1/4 | 13 | 20 | 2024/12/1 |
↓
| 品番 | 品名 | 店舗NO. | 納期 | 数量 | 受信日 |
|---|---|---|---|---|---|
| ABCD-123 | ゴミ袋 | ○○○ | 2025/1/1 | 35 | 2024/12/1 |
| ABCD-123 | ゴミ袋 | ○○○ | 2025/1/4 | 200 | 2024/12/1 |
| ABCD-123 | ゴミ袋 | ○○○ | 2025/1/20 | 1 | 2024/12/1 |
| ABCE-456 | ブラシ | ○○○ | 2025/1/3 | 13 | 2024/12/1 |
| ABCE-456 | ブラシ | ○○○ | 2025/1/4 | 2024/12/1 |
n) 月別に独立して横に展開されている項目の表の整形:2025/07/18:当ブログ
https://bigarrow.tea-nifty.com/blog/2025/07/post-485823.html
1ファイル、1シート、複数表(横)、リスト。入替-列結合-入替-ヘッダ化-ピボット解除-整数除算-ピボット
| 25/04 | 25/05 | ||
|---|---|---|---|
| 取引先 | 売上 | 取引先 | 売上 |
| a社 | 1,000,000 | a社 | 500,000 |
| b社 | 100,000 | c社 | 1,000,000 |
| d社 | 500,000 | ||
横方向のつながりはありません。各月の表は独立しています。各月の表の間に1列の空白列が合ってもOKです。
↓
| 年月 | 取引先 | 売上 |
|---|---|---|
| 25/04 | a社 | 1,000,000 |
| 25/04 | b社 | 100,000 |
| 25/05 | a社 | 500,000 |
| 25/05 | C社 | 1,000,000 |
| 25/05 | d社 | 500,000 |
o) 項目が繰り返し横に伸びる、開始日と終了日のデータのみの表をモダンExcelで集計:2025/07/08:当ブログ
https://bigarrow.tea-nifty.com/blog/2025/07/post-258734.html
1ファイル、1シート、1表、横に複数項目のレコードが繰り返し。列結合-転置-ヘッダ化-整数除算-ピボット解除-ピボット
| 番号 | 氏名 | 入居日 | 退去日 | 部屋番 | 入居日 | 退去日 | 部屋番 | 入居日 | 退去日 | 部屋番 |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 山口 | 2025/6/2 | 2025/6/5 | 103 | 2025/6/10 | 2025/6/12 | 105 | 2025/6/30 | 2025/7/10 | 203 |
| 2 | 田中 | 2025/5/3 | 2025/5/5 | 106 | 2025/5/10 | 2025/5/11 | 105 | 2025/6/3 | 2025/6/5 | 103 |
| 10 | 加藤 | 2025/7/1 | 2025/7/2 | 201 | ||||||
| 15 | 広瀬 | 2025/8/3 | 2025/8/8 | 203 | 2025/9/10 | 2025/9/15 | 106 | |||
| 5 | 長澤 | |||||||||
| 23 | 深田 | 2025/4/3 | 2025/4/8 | 101 | 2025/7/12 | 2025/7/15 | 205 |
↓
| 番号 | 名前 | 入居日 | 退去日 | 部屋番 | 当月日数 |
|---|---|---|---|---|---|
| 1 | 山口 | 25/06/30 | 25/07/10 | 203 | 10 |
| 25/07/13 | 25/07/16 | 203 | 4 | ||
| 25/07/18 | 25/07/22 | 203 | 5 | ||
| 山口 集計 | 19 | ||||
| 10 | 加藤 | 25/07/01 | 25/07/02 | 201 | 2 |
| 加藤 集計 | 2 | ||||
| 2 | 田中 | 25/07/03 | 25/07/06 | 205 | 4 |
| 田中 集計 | 4 | ||||
| 23 | 深田 | 25/07/12 | 25/07/15 | 205 | 4 |
| 深田 集計 | 4 | ||||
p) Excel/Power Queryでデータブロックが横に並んでいるデータを縦にまとめる方法:2025/06/14:当ブログ
https://bigarrow.tea-nifty.com/blog/2025/06/post-80cc7d.html
1ファイル、1シート、複数表(横)、リスト。入替-整数除算-ピボット解除-ピボット
| 品番 | 1111 | 品番 | 1112 | |||||||
| 得意先 | 品名 | 数量 | 単価 | 納期 | 得意先 | 品名 | 数量 | 単価 | 納期 | |
| A商社 | りんご | 1 | 100 | 12月1日 | A商社 | もも | 3 | 100 | 11月1日 | |
| B商社 | バナナ | 2 | 200 | 12月23日 | B商社 | バナナ | 4 | 200 | 12月1日 |
横方向のつながりはありません。各品番の表は独立しています。各品番の表は1列の空白列で区切られています。
↓
| 品番 | 得意先 | 品名 | 数量 | 単価 | 納期 |
| 1111 | A商社 | りんご | 1 | 100 | 12月1日 |
| 1111 | B商社 | バナナ | 2 | 200 | 12月23日 |
| 1112 | A商社 | もも | 3 | 100 | 11月1日 |
| 1112 | B商社 | バナナ | 4 | 200 | 12月1日 |
q) Excel Power Queryで1レコードが複数行、複数列に分かれている表の整形方法:2025/02/28:当ブログ
https://bigarrow.tea-nifty.com/blog/2025/02/post-08e375.html
1ファイル、1シート、1表、1レコードが複数行、複数列で、行毎に項目が違う。Power Query M 詳細エディタでコーディング
| 1 | a:あ | b:い | c:う | d:え | h:く | |
| e:お | f:か | g:き | ||||
| 2 | a:け | b:こ | c:さ | d:し | h:た | |
| e:す | f:せ | g:そ | ||||
↓
| No. | a | b | c | d | e | f | g | h |
|---|---|---|---|---|---|---|---|---|
| 1 | あ | い | う | え | お | か | き | く |
| 2 | け | こ | さ | し | す | せ | そ | た |
r) Excel Power Queryで横項目3段のクロス表をリスト形式にする方法:2025/02/17:当ブログ
https://bigarrow.tea-nifty.com/blog/2025/02/post-431da6.html
1ファイル、1シート、1表、クロス表。転置-列結合-転置-ヘッダー化-ピボット解除-列分割-ピボット
| 2025年 | 1F | 2F | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1月 | AM | PM | AM | PM | ||||||||
| 日 | Gr | 品名 | 個数 | Gr | 品名 | 個数 | Gr | 品名 | 個数 | Gr | 品名 | 個数 |
| 1日 | A | りんご | 3 | B | キウイ | 5 | B | 醤油 | 1 | A | 塩 | 2 |
| 2日 | A | みかん | 5 | B | キウイ | 5 | B | ソース | 2 | A | 醤油 | 2 |
| 3日 | ||||||||||||
| 4日 | B | みかん | 11 | A | キウイ | 5 | A | 塩 | 1 | B | ソース | 1 |
↓
| 年月日 | 年 | 月 | 日 | 1段目 | 2段目 | Gr | 品名 | 個数 |
|---|---|---|---|---|---|---|---|---|
| 2025/01/01 | 2025年 | 1月 | 1日 | 1F | AM | A | りんご | 3 |
| 2025/01/01 | 2025年 | 1月 | 1日 | 1F | PM | B | キウイ | 5 |
| 2025/01/01 | 2025年 | 1月 | 1日 | 2F | AM | B | 醤油 | 3 |
| 以下省略 |
s) 1レコードが1シート複数行複数列から成る複数シートを1つのデータベース形式にする:2025/09/26:当ブログ
https://bigarrow.tea-nifty.com/blog/2025/09/post-4beb66.html
1ファイル、複数シート、2行毎に項目と値を構成。列結合-剰余-整数除算-列のピボット-転置-列の結合-列の分離
| 会社情報 | |
|---|---|
| 社名 | 住所 |
| A社 | 東京 |
| 担当者 | 営業日 |
| A営業 | 月-木 |
| 電話 | FAX |
| 0120-000-000 | 0120-100-001 |
| 資本 | 受注 |
| 1,000万 | 100万 |
↓
| 社名 | 住所 | 担当者 | 営業日 | 電話 | FAX | 資本 | 受注 |
|---|---|---|---|---|---|---|---|
| A社 | 日本 | A営業 | 月-木 | 0120-000-000 | 0120-000-001 | 1,000万 | 100万 |
| B社 | 日本 | B営業 | 月-木 | 0120-001-000 | 0120-001-001 | 1,001万 | 101万 |
| C社 | 日本 | C営業 | 月-木 | 0120-002-000 | 0120-002-001 | 1,002万 | 102万 |
t) Power Queryで縦横に複数の子表が並ぶシートをデータベース形式に整形:2025/12/14:当ブログ
https://bigarrow.tea-nifty.com/blog/2025/12/post-de2eb7.html
1ファイル、1シート、縦横複数表。転置-整数除算-グループ化-カスタム列で整数除算削除し転置-展開結合
縦横2個、合計4個の表の例
| 商品場所 | |||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|
| コード | A123456789 | コード | A123456790 | ||||||||
| 品名 | 100001 | 品名 | 100002 | ||||||||
| ロット | 入数 | 箱数 | 合計 | ロット | 入数 | 箱数 | 合計 | ||||
| aswdw | 12 | 3 | 36 | 88888 | 8 | 1 | 8 | ||||
| qwrq | 3 | 4 | 12 | 99999 | 9 | 2 | 18 | ||||
| 333 | 5 | 2 | 10 | 1000 | 10 | 3 | 30 | ||||
| TOTAL | 16 | 104 | TOTAL | 15 | 160 | ||||||
| コード | A123456791 | コード | A123456792 | ||||||||
| 品名 | 100003 | 品名 | 100004 | ||||||||
| ロット | 入数 | 箱数 | 合計 | ロット | 入数 | 箱数 | 合計 | ||||
| 777 | 17 | 1 | 17 | aaa | 10 | 2 | 20 | ||||
| 888 | 18 | 2 | 36 | bbb | 20 | 3 | 60 | ||||
| 999 | 19 | 3 | 57 | ||||||||
| 000 | 20 | 4 | 80 | ||||||||
| TOTAL | 15 | 295 | TOTAL | 20 | 700 |
↓
| コード | 品名 | ロット | 入数 | 箱数 | 合計 |
|---|---|---|---|---|---|
| A123456789 | 100001 | 12 | 3 | 36 | aswdw |
| A123456789 | 100001 | qwrq | 3 | 4 | 12 |
| A123456789 | 100001 | 333 | 5 | 2 | 10 |
| A123456791 | 100003 | 777 | 17 | 1 | 17 |
| A123456791 | 100003 | 888 | 18 | 2 | 36 |
| A123456791 | 100003 | 999 | 19 | 3 | 57 |
| A123456791 | 100003 | 000 | 20 | 4 | 80 |
| A123456790 | 100002 | 88888 | 8 | 1 | 8 |
| A123456790 | 100002 | 99999 | 9 | 2 | 18 |
| A123456790 | 100002 | 1000 | 10 | 3 | 30 |
| A123456792 | 100004 | aaa | 10 | 2 | 20 |
| A123456792 | 100004 | bbb | 20 | 3 | 60 |
u) 表の外に共通項目があり、縦横に複数の項目を持つクロス表をPower Queryでリストに整形:2026/05/07:当ブログ
https://bigarrow.tea-nifty.com/blog/2026/05/post-dced47.html
欄外に共通項目がある多段クロス表。絶対アドレス参照。転置-結合-転置-インデックス-整数除算-ピボット解除-列のピボット。
| Column1 | Column2 | Column3 | Column4 | Column5 | Column6 | Column7 | Column8 | Column9 |
|---|---|---|---|---|---|---|---|---|
| 《今野物産 ピッキングリスト》 | ||||||||
| 受信日:2026/04/28 | 部門:11 | |||||||
| 出荷日:2026/04/30 | 出庫元:0006781 佐川営業所 | |||||||
| 納品日:2026/05/01 | 売上部署:0006781 佐川営 | 検索対象:手入力情報 + EDI情報(本発注) | ||||||
| 品名/規格 | 品目コード | 単位 | 相手先店コード | 160016 | 160014 | 160012 | ||
| 相手先品目コード | 入数 | 自社店コード | S001 | S002 | S003 | |||
| 店名 | 合計 | 東京店 | 大阪店 | 北海道店 | ||||
| りんご/生 | P | 数量 | 100 | 100 | ||||
| B001 | 12345 | 10 | ケース数 | 10 | 10 | |||
| ぶどう/生 | P | 数量 | 70 | 40 | 30 | |||
| B002 | 11111 | 10 | ケース数 | 7 | 4 | 3 |
↓
| 品名/規格 | 相手先品目コード | 品目コード | 相手先店コード | 自社店コード | 店名 | 数量 | ケース数 | 部門 | 受信日 | 出荷日 | 納品日 |
|---|---|---|---|---|---|---|---|---|---|---|---|
| りんご/生 | B001 | 12345 | 160016 | S001 | 東京店 | 100 | 10 | 11 | 2026/04/28 火 | 2026/04/30 木 | 2026/05/01 金 |
| ぶどう/生 | B002 | 11111 | 160012 | S003 | 北海道店 | 30 | 3 | 11 | 2026/04/28 火 | 2026/04/30 木 | 2026/05/01 金 |
| ぶどう/生 | B002 | 11111 | 160014 | S002 | 大阪店 | 40 | 4 | 11 | 2026/04/28 火 | 2026/04/30 木 | 2026/05/01 金 |
3. 整形手順のパターン
クロス表
[転置]-[列結合]-[ヘッダー化]-[ピボット開所]-[列分割]-[ピボット]。
横に同じ項目が繰り返し並ぶ表
[列結合]-[転置]-[ヘッダ化]-[整数除算]-[ピボット解除]-[ピボット]。
整数除算を行うことで項目の組合せの重複が無くなるため、ピボット時に「集計しない」でエラーを無くすことができる。
セル結合で空白セルがある表
[フィル]-[下へ]。[転置]-[フィル]-[下へ]-[転置]。
表の外にある1つの値を全レコードの値にする方法
[列の追加]-[カスタム列の追加]で次のように入力する。
= ソース{0}[Column2] // ソースはテーブル名、{0}はレコード番号(行番号)、[Column2]は列名。
当ブログの過去の記事では、カスタム列を2つ使い方法を紹介してきましたが、このようにフィールドを直接参照する方が簡単です。
4. 参考情報
- ピボット解除の応用技 簡単操作でマトリクス形式の表をデータベース形式に変換【エクセル パワークエリ】【Excel PowerQuery】:ExcelドカタCH 2021/02/28
https://www.youtube.com/watch?v=fWw9ueVkHto - #2 表の変換にマクロなんかいらない【エクセル パワークエリ PowerQuery】1レコード複数行・複数列の表をリスト形式に変換する方法:ExcelドカタCH 2021/06/05
https://www.youtube.com/watch?v=VleTkjBmaCA - ピボット解除応用:多階層クロス集計表のデータ化:鷲尾祥のExcel BIブログ 2019/10/15
https://modernexcel7.hatenablog.com/entry/2019/10/05/232407 - 【エクセル パワークエリ】 列が多段のマトリクス形式の表をリスト形式に変換する方法【Excel PowerQuery】【ピボット解除】 :Excelドカタの書きもの 2020/11/28
https://note.com/excel_dokata/n/neb26fe4b8331 - 【Excel】マウス操作だけのパワークエリからレベルアップ!カスタム関数クエリでワンランク上のデータ整形:Excel ドカタCH 2024/06/15
https://www.youtube.com/watch?v=jDIwpBCVibY - Power Queryで複数の表を結合する方法(まとめ)/ 当ブログ 2025/11/05
https://bigarrow.tea-nifty.com/blog/2025/11/post-341d5f.html - 【PowerQuery for Excel】『目から鱗の凄技』「データマッピング」で、ランダム「セル」を「データ化」して「結合」: たぬき親父の「Excel勉強会」: 2023/08/25
https://www.youtube.com/watch?v=UZXtUUALfIE
5. 余談
- ChatGPTなどのLLMの答えは、最適解ではないことがあります。
| 固定リンク





コメント