Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 11 Home : Excel 2021)
パワークエリでクロス表
投稿日時: 25/01/27 15:04:16
投稿者: toma100

こんにちは、よろしくお願いいたします。
 
パワークエリでは、クロス表をテーブル形式にできますが、
複数のブックでそれぞれクロス表を作成していて
1つのテーブルにまとめるには、
 
パワークエリでそれぞれのクロス表をそれぞれにテーブルにしてから、
これらのテーブルを1つに結合して、
クロス表を作成する
という、段階をしなけれぱならないのでしょうか?
よろしくお願いいたします。

回答
投稿日時: 25/01/27 15:55:46
投稿者: んなっと

具体的な表形式のサンプルがないので、適当に回答します。
 
"E:\office\Excel\ExcelVBA\11" フォルダー内にある2つのブックが以下のようになっていて
 
    A   B   C   D   E
1 クラス 名前 国語 英語 社会
2    1  あ   5      8
3    1  い      2   
4    1  う   6      
5    1  え         7
6    1  お   3   4   
 
 
    A   B   C   D   E
1 クラス 名前 数学 英語 理科
2    2  か   9  10   7
3    2  か   4   2   
4    2  か   3      
5    2  か      0   5
 
 
それらを最初の2列以外の列をピボット解除し、次のように統合したいとします。
 
     A   B   C  D
 1 クラス 名前 属性 値
 2    1  あ 国語  5
 3    1  あ 社会  8
 4    1  い 英語  2
 5    1  う 国語  6
 6    1  え 社会  7
 7    1  お 国語  3
 8    1  お 英語  4
 9    2  か 数学  9
10    2  か 英語 10
11    2  か 理科  7
12    2  か 数学  4
13    2  か 英語  2
14    2  か 数学  3
15    2  か 英語  0
16    2  か 理科  5
 
 
 データ→データの取得→その他のデータベースから
→空のクエリ→PowerQueryエディターが開いたら
→詳細エディター
 
let
    MltExp = (Param as binary) => let
        Src = Excel.Workbook(Param, null, true),
        Sht = Src{0}[Data],
        Prm = Table.PromoteHeaders(Sht, [PromoteAllScalars=true]),
        Unp = Table.UnpivotOtherColumns(Prm, List.FirstN(Table.ColumnNames(Prm),2), "属性", "値")
    in
        Unp,
    ソース = Folder.Files("E:\office\Excel\ExcelVBA\11"),
    Flt = Table.SelectRows(ソース, each [Attributes]?[Hidden]? <> true),
    Add = Table.AddColumn(Flt, "カスタム", each MltExp([Content])),
    Del = Table.SelectColumns(Add, {"カスタム"}),
    Exp = Table.ExpandTableColumn(Del, "カスタム", Table.ColumnNames(Del[カスタム]{0}))
in
    Exp

投稿日時: 25/01/27 16:05:53
投稿者: toma100

んなっと様、いつもありがとうございます!
 
1つのクロス表にしたいのですが、
上で完成したテーブルを
ピポット解除でクロス表にしたらよいでしょうか?

回答
投稿日時: 25/01/27 16:43:24
投稿者: んなっと

let
    MltExp = (Param as binary) => let
        Src = Excel.Workbook(Param, null, true),
        Sht = Src{0}[Data],
        Prm = Table.PromoteHeaders(Sht, [PromoteAllScalars=true]),
        Unp = Table.UnpivotOtherColumns(Prm, List.FirstN(Table.ColumnNames(Prm),2), "属性", "値")
    in
        Unp,
    ソース = Folder.Files("E:\office\Excel\ExcelVBA\11"),
    Flt = Table.SelectRows(ソース, each [Attributes]?[Hidden]? <> true),
    Add = Table.AddColumn(Flt, "カスタム", each MltExp([Content])),
    Del = Table.SelectColumns(Add, {"カスタム"}),
    Exp = Table.ExpandTableColumn(Del, "カスタム", Table.ColumnNames(Del[カスタム]{0})),
    Piv = Table.Pivot(Exp, List.Distinct(Exp[属性]), "属性", "値")
in
    Piv
 
もしも2つのブックの列名がすべて同じなら、クロス表のピボット解除は不要。
 
    A   B   C   D   E
1 クラス 名前 国語 英語 社会
2    1  あ   5      8
3    1  い      2   
4    1  う   6      
5    1  え         7
6    1  お   3   4   
 
    A   B   C   D   E
1 クラス 名前 国語 英語 社会
2    2  か   9  10   7
3    2  き   4   2   
4    2  く   3      
5    2  け      0   5
 
  ↓ 直接統合
 
     A   B   C   D   E
 1 クラス 名前 国語 英語 社会
 2    1  あ   5      8
 3    1  い      2   
 4    1  う   6      
 5    1  え         7
 6    1  お   3   4   
 7    2  か   9  10   7
 8    2  き   4   2   
 9    2  く   3      
10    2  け      0   5
 
 
let
    MltExp = (Param as binary) => let
        Src = Excel.Workbook(Param, null, true),
        Sht = Src{0}[Data],
        Prm = Table.PromoteHeaders(Sht, [PromoteAllScalars=true])
    in
        Prm,
    ソース = Folder.Files("E:\office\Excel\ExcelVBA\11"),
    Flt = Table.SelectRows(ソース, each [Attributes]?[Hidden]? <> true),
    Add = Table.AddColumn(Flt, "カスタム", each MltExp([Content])),
    Del = Table.SelectColumns(Add, {"カスタム"}),
    Exp = Table.ExpandTableColumn(Del, "カスタム", Table.ColumnNames(Del[カスタム]{0}))
in
    Exp

回答
投稿日時: 25/01/27 16:45:56
投稿者: んなっと

引用:
パワークエリでは、クロス表をテーブル形式にできますが

これでは伝わりません。
今後は表形式の具体例を添えてください。

投稿日時: 25/01/27 16:50:51
投稿者: toma100

んなっと様
 
ありがとうございました!
これでできそうです、いつもありがとうございます(..)