« Excel Power Queryで複数の表を結合する方法(まとめ) | トップページ | Excelで該当する数値をカンマ区切りで連結、連続する数値は始まりと終わりをハイフンでつなぐ »

Excel Power Queryで表をリスト形式に整形する方法(まとめ)

1. 前置き

ネットや書籍などに、Excel Power Queryで表をリスト形式(データベース形式)に整形する方法が解説されていますが、表の形によりその方法で整形できないことがあります。
そこで、整形のメモを整理してみました。なお、当方は素人なので、ここで紹介するものよりもっと良い方法があると思います。古いメモ程、素人度が高いです。使用される場合は、検算を行い自己責任でお願いいたします。

2. 整形方法

1ファイル、1シートの色々な表の整形方法です。ChatGPTを利用して作成したものが多いです。ChatGPTには、次のような質問をしています。

ChatGPTなどへの質問内容

  1. データを簡略化した整形前と整形後のテキストの表を入れる。項目数は簡略化しないほうが良い場合もある。表はカンマ区切りでもTAB区切りでも良い。
  2. Excel、Power Queryであることを書く。
  3. クリック操作で作るのか、Power Query Mのコードの回答を求めるのかを書く。ChatGPTなどのLLMは、コードの回答の方が得意です。
  4. ある程度の整形方法を指示する。例えば、似たような事例とか、途中まで自分で作成した方法を記載する。

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      

↓

Pivot Table
番号 名前 入居日 退去日 部屋番 当月日数
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. 参考情報

5. 余談

  • ChatGPTなどのLLMの答えは、最適解ではないことがあります。

|

« Excel Power Queryで複数の表を結合する方法(まとめ) | トップページ | Excelで該当する数値をカンマ区切りで連結、連続する数値は始まりと終わりをハイフンでつなぐ »

コメント

コメントを書く



(ウェブ上には掲載しません)




« Excel Power Queryで複数の表を結合する方法(まとめ) | トップページ | Excelで該当する数値をカンマ区切りで連結、連続する数値は始まりと終わりをハイフンでつなぐ »