[[20201114122421]]　『集計の仕方について』（うた）　ページの最後に飛ぶ

『集計の仕方について』（うた）

下記のような元データがあるとして、
注文日、決済方法の次に購入商品が横一列に並びます。
1点購入の人もいれば、複数買っている人もいます。

注文日　決済方法　　　商品１　金額　　　商品２　金額　　　　商品３　金額

2020/11/01　現金　　　　BBB　700　　　
2020/11/01　クレジット　AAA　500　　　　BBB　700
2020/11/02　現金　　　　CCC　200　　　　AAA　500　　　　　BBB 700
　　：　　　　　：　
2020/12/01　クレジット　CCC　200　　　　BBB 700

これを下のように集計したいのですが、
どのようにするのが良いでしょうか。

【2020年11月でフィルタ】
決済方法　　商品　　件数　　金額

　現金　　　AAA　　
　　　　　　BBB
　　　　　　CCC
クレジット　AAA
　　　　　　BBB
　　　　　　CCC

複数商品購入したときに、商品１の列にまとめるとピボットでもいけると思いますが、
なるべく元データを崩さずに集計する方法はありますか。

＜使用 Excel：Excel2010、使用 OS：Windows10 ＞

Power Query を使うと、こんな感じのピボットで出力きました。
※使用環境が限られるのは問題でしょうか。
https://qiita.com/PowerBIxyz/items/52e563037b855a099ca9

 注文年	2020		
 注文月	11

 決済方法      商品    件数     金額
 クレジット    AAA      1        500
               BBB      1        700
 現金          AAA      1        500
               BBB      2       1400
               CCC      1        200

 '------
 let
    ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
    変更された型 = Table.TransformColumnTypes(ソース,{{"注文日", type date}, {"決済方法", type text}, {"商品１", type text}, {"金額", Int64.Type}, {"商品２", type text}, {"金額2", Int64.Type}, {"商品３", type text}, {"金額3", Int64.Type}}),
    ピボット解除された他の列 = Table.UnpivotOtherColumns(変更された型, {"注文日", "決済方法"}, "属性", "値"),
    追加された条件列 = Table.AddColumn(ピボット解除された他の列, "商品", each if Text.StartsWith([属性], "商品") then [値] else null),
    追加された条件列1 = Table.AddColumn(追加された条件列, "金額", each if Text.StartsWith([属性], "金額") then [値] else null),
    下方向へコピー済み = Table.FillDown(追加された条件列1,{"商品"}),
    フィルターされた行 = Table.SelectRows(下方向へコピー済み, each [金額] <> null and [金額] <> ""),
    挿入された年 = Table.AddColumn(フィルターされた行, "注文年", each Date.Year([注文日]), Int64.Type),
    挿入された月 = Table.AddColumn(挿入された年, "注文月", each Date.Month([注文日]), Int64.Type),
    削除された列 = Table.RemoveColumns(挿入された月,{"注文日", "属性", "値"}),
    並べ替えられた列 = Table.ReorderColumns(削除された列,{"注文年", "注文月", "決済方法", "商品", "金額"})
i n
    並べ替えられた列

（マナ） 2020/11/14(土) 15:14

早速ありがとうございます！
スマホで確認しておりますので、お礼だけ先に言わせてください。
Power Query を使用したことがなかったので、今夜ダウンロードしてやってみます！

（うた） 2020/11/14(土) 15:41

最初の型の変更と、最後の列の並べ替えは、無意味でした

 let
    ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
    ピボット解除された他の列 = Table.UnpivotOtherColumns(ソース, {"注文日", "決済方法"}, "属性", "値"),
    追加された条件列 = Table.AddColumn(ピボット解除された他の列, "商品", each if Text.StartsWith([属性], "商品") then [値] else null),
    追加された条件列1 = Table.AddColumn(追加された条件列, "金額", each if Text.StartsWith([属性], "金額") then [値] else null),
    下方向へコピー済み = Table.FillDown(追加された条件列1,{"商品"}),
    フィルターされた行 = Table.SelectRows(下方向へコピー済み, each [金額] <> null and [金額] <> ""),
    挿入された年 = Table.AddColumn(フィルターされた行, "注文年", each Date.Year([注文日]), Int64.Type),
    挿入された月 = Table.AddColumn(挿入された年, "注文月", each Date.Month([注文日]), Int64.Type),
    削除された列 = Table.RemoveColumns(挿入された月,{"注文日", "属性", "値"})
 in
    削除された列

（マナ） 2020/11/14(土) 18:49

とりあえず再現はできましたー！
詳細エディタに、まるまるコピペしただけですが、、、
中身の理解をこれからしたいと思います！
ありがとうございました。
（うた） 2020/11/14(土) 21:44

文字入力変換の問題がでるなら、↓の回避策を。
https://www.travel-and-mylife.com/2019/07/excel-ime-problem.html

（マナ） 2020/11/14(土) 22:10

手探りでしたが、いただいた手順を参考に、実際の元データでもやってみて、
思うようにまとめることができました！

これでデータ数が増えても更新でいけますね。
年、月のフィルターは、パワークエリで挿入しなくても、
ピボットにしてからでも絞れますもんね。

今日初めて知りましたが、パワークエリに感動しています。。
本当にありがとうございます。
（うた） 2020/11/14(土) 22:49

コメント返信：

[ 一覧(最新更新順) ]

[[20201114122421]] 『集計の仕方について』（うた） ページの最後に飛ぶ

[[20201114122421]]　『集計の仕方について』（うた）　ページの最後に飛ぶ