HOME > Q&A > Excel （一般機能）

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

 
(Windows 11 Home : Excel 2021)
クエリでの加工の仕方
投稿日時: 25/09/24 15:10:57
投稿者: toma100

こんにちは、よろしくお願いいたします。
 
以下のように、質問が横に50個ぐらいあります。
名前は下に50名ほどあります。
 
名前    質問1　コメント    　　　質問2　コメント    　　　質問3　コメント
田中    はい    　コメント1    　いいえ　コメント1    　　　はい    　コメント1
飯田    いいえ　コメント2    　はい　コメント2　　　　　　いいえ　コメント2
 
このような２列ごとの表を
パワークエリで以下のように加工したいのですが、ピボット解除をどのようにしたら
可能でしょうか？
 
名前    質問内容    回答    　コメント            
田中    質問1    はい    　コメント1            
飯田    質問1    いいえ　コメント2            
田中    質問2    いいえ　コメント1            
飯田    質問2    はい    　コメント2            
田中    質問3    はい    　コメント1            
飯田    質問3    いいえ　コメント2        
 
データが量が多く、毎月、発生するのでクエリで加工できればと思ったのですが。。
ただ、コードを変更する権限がないため
クエリのメニューだけの操作でなんとか可能でしょうか？
 
    

回答
投稿日時: 25/09/24 17:46:26
投稿者: sk

引用:
名前 質問1　コメント 　　　質問2　コメント 　　　質問3　コメント

これらの[コメント]列の見出しは実際に同じ列名なのでしょうか。
 
もしそうであるならば、その範囲はまだテーブルに変換されていない
（ 1 つのテーブルにおいて同名の列を 2 つ以上設けることはできないので）
ということになるはずですが、テーブルに変換せずにそのままの状態で扱う
必要があるのでしょうか。
投稿日時: 25/09/24 20:45:26
投稿者: toma100

sk様
 
見出しは書き間違いでした、申し訳ございません！
質問のところは2つのセルで1つに結合されているため
テーブるに変換すると
 
名前 質問1　列1 　　　質問2　列2 　　　質問3　列3
 
のようになります、
よろしくお願いいたします。

回答
投稿日時: 25/09/26 08:03:46
投稿者: んなっと

引用:
コードを変更する権限がないため
クエリのメニューだけの操作でなんとか可能でしょうか？

可能かもしれませんが、申し訳ないのですが操作方法の説明が面倒です。
　アンピボット
→0からの[連番0]、1からの[連番1]追加
→マージ
→[連番1]の奇数行削除
→不要な列削除
回答
投稿日時: 25/09/26 08:11:31
投稿者: んなっと

let
    ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
    unp = Table.UnpivotOtherColumns(ソース, {"名前"}, "質問", "回答"),
    idx0 = Table.AddIndexColumn(unp, "連番0", 0, 1, Int64.Type),
    idx1 = Table.AddIndexColumn(idx0, "連番1", 1, 1, Int64.Type),
    mrg = Table.NestedJoin(idx1, {"連番1"}, idx1, {"連番0"}, "マージ", JoinKind.LeftOuter),
    exp = Table.ExpandTableColumn(mrg, "マージ", {"回答"}, {"コメント"}),
    flt = Table.SelectRows(exp, each Number.Mod([連番1], 2)=1),
    grp = Table.Group(flt, {"名前"}, {{"グループ", each _, type table}}),
    grp1 = grp{0}[グループ],
    idx2 = Table.AddIndexColumn(grp1, "連番2", 1, 1, Int64.Type),
    mrg2 = Table.NestedJoin(flt, {"質問"}, idx2, {"質問"}, "del", JoinKind.LeftOuter),
    exp2 = Table.ExpandTableColumn(mrg2, "del", {"連番2"}, {"連番2"}),
回答
投稿日時: 25/09/26 08:12:05
投稿者: んなっと

    srt = Table.Sort(exp2,{{"連番2", Order.Ascending}, {"連番1", Order.Ascending}}),
    del = Table.RemoveColumns(srt,{"連番0", "連番1", "連番2"})
in
    del
 
都合により2つに分けました。詳細エディターです。
回答
投稿日時: 25/09/26 14:32:08
投稿者: んなっと

ちなみに、これでいいのなら...
 
　　　A　　　B　　　 C　　　　　D
1　名前　 質問　　回答　 コメント
2　田中　質問1　　はい　コメント1
3　田中　質問2　いいえ　コメント2
4　田中　質問3　　はい　コメント3
5　飯田　質問1　いいえ　コメント4
6　飯田　質問2　　はい　コメント5
7　飯田　質問3　いいえ　コメント6
 
簡単。手順の解説もできます。
 
let
    ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
    unp = Table.UnpivotOtherColumns(ソース, {"名前"}, "質問", "回答"),
    idx0 = Table.AddIndexColumn(unp, "連番0", 0, 1, Int64.Type),
    idx1 = Table.AddIndexColumn(idx0, "連番1", 1, 1, Int64.Type),
    mrg = Table.NestedJoin(idx1, {"連番1"}, idx1, {"連番0"}, "マージ", JoinKind.LeftOuter),
    exp = Table.ExpandTableColumn(mrg, "マージ", {"回答"}, {"コメント"}),
    flt = Table.SelectRows(exp, each Number.Mod([連番1], 2)=1),
    del = Table.RemoveColumns(flt,{"連番0", "連番1"})
in
    del
回答
投稿日時: 25/09/26 22:01:09
投稿者: sk

引用:
名前 質問1　列1 　　　質問2　列2 　　　質問3　列3

・それぞれの[質問n]列と[列n]列は1対1の関係にある（重複も抜けもない）。
 
・対になっている列同士の名前の末尾には同じ添え字（ 1, 2, 3 ... n ）が付加されている。
 
・[名前]列の値は必ずしもユニークではない。
 
というテーブルが存在していると仮定した場合。
 
引用:
ただ、コードを変更する権限がないため
クエリのメニューだけの操作でなんとか可能でしょうか？

 
（操作例）
---------------------------------------------------------------------------------
 
1. そのテーブルのいずれかのセルを選択する。
 
2. [データ]タブ -> [データの取得と変換]グループ -> 「テーブルまたは範囲から」を
   クリックし、Power Query エディターを起動する。
 
3. 自動作成されたクエリ（既定ではそのテーブルと同名）が選択された状態で、
   [列の追加]タブ -> [全般]グループ -> [インデックス列]をクリックする
   （ドロップダウンした場合は、[0 から]または[1 から]をクリックする）。
 
4. [インデックス]列が選択された状態で Ctrl キーをクリックしながら
   [名前]列をクリックし、それら 2 つの列のみを範囲選択する。
 
5. [変換]タブ -> [任意の列]グループ -> [列のピボット解除]をドロップダウンし、
   [その他の列のピボット解除]をクリックする。
   (この時、[インデックス]列および[名前]列以外の全ての列が
   [属性]列と[値]列に変換される）
 
6. [属性]列のみを選択し、[変換]タブ -> [テキストの列]グループ ->
   [列の分割]をドロップダウンし、[数字以外から数字による分割]をクリックする。
   （この時、[属性]列が[属性.1]列と[属性.2]列に分割される）
 
7. [属性.1]列の名前を[項目名]に、[属性.2]の名前を[質問番号]に変更する。
   （列名の変更はその列見出しを左マウスダブルクリックするか、
    列見出しを右マウスクリックしてポップアップメニューを表示し
    [名前の変更]をクリックするか、いずれかの操作によって行なう）
 
8. [クエリの設定]ウィンドウ（右側）より、そのクエリを任意の名前
   （ここでは[ピボット解除クエリ]とする）に変更する。
 
9. [クエリ]ウィンドウ（左側）よりそのクエリを右マウスクリックして
   ポップアップメニューを表示し、[参照]をクリックして
   新しいクエリを作成する。
 
10. 作成されたクエリの[項目名]列のフィルターボタンをクリックし、
    [項目名]列の値が"質問"であるレコードのみを抽出する。
 
11. 上記 7 と同様の操作により、[値]列の名前を[回答]に変更する。
 
12. [クエリの設定]ウィンドウ（右側）より、そのクエリを任意の名前
   （ここでは[回答抽出クエリ]とする）に変更する。
 
13. [クエリ]ウィンドウ（左側）より[ピボット解除クエリ]を選択、
    右マウスクリックしてポップアップメニューを表示し、
    [参照]をクリックして新しいクエリを作成する。
 
14. 作成されたクエリの[項目名]列のフィルターボタンをクリックし、
    [項目名]列の値が"列"であるレコードのみを抽出する。
 
15. 上記 7 と同様の操作により、[値]列の名前を[コメント]に変更する。
 
16. [クエリの設定]ウィンドウ（右側）より、そのクエリを任意の名前
   （ここでは[コメント抽出クエリ]とする）に変更する。
 
17. ここまでの状態で[ホーム]タブ -> [閉じる]グループ -> [閉じて読み込む]を
    ドロップダウンし、[閉じて次に読み込む]をクリックする。
 
18. Excel 上で[データのインポート]ダイアログが表示されたら、
   [接続の作成のみ]を選択して[OK]ボタンをクリックする。
 
19. [クエリと接続]ウィンドウより[回答抽出クエリ]をマウス右クリックして
    ポップアップメニューを表示し、[結合]をクリックする。
 
20. [マージ]ウィンドウが表示されたら、[回答抽出クエリ]と結合するクエリとして
    [コメント抽出クエリ]を選択し、そのプレビューを表示させる。
 
21. [回答抽出クエリ]のプレビュー上の[インデックス]列を左マウスクリックする。
 
22. [コメント抽出クエリ]のプレビュー上の[インデックス]列を左マウスクリックする。
 
23. Ctrl キーを押しながら、[回答抽出クエリ]のプレビュー上の
    [質問番号]列を左マウスクリックする。
 
23. Ctrl キーを押しながら、[コメント抽出クエリ]のプレビュー上の
    [質問番号]列を左マウスクリックする。
 
24. それぞれの列見出しの右側に表示されているキー番号（ 1, 2 ）が
    一致していることを確認し、[OK]ボタンをクリックする。
    （この時、Power Query エディターを起動して新しいクエリが作成される）
 
25. [コメント抽出クエリ]列の展開ボタンをクリックし、
    [元の列名をプリフィックスとして使用します]をオフにした状態で
    [コメント]列のみを展開する。
 
26. [質問番号]列を選択し、[変換]タブ -> [任意の列]グループ -> [データ型]を
    ドロップダウンし、[整数]を選択する。
 
27. [インデックス]列のフィルターボタンをクリックし、[昇順に並べ替え]をクリックする。
 
28. [質問番号]列のフィルターボタンをクリックし、[昇順に並べ替え]をクリックする。
 
29. [インデックス]列を左マウスドラッグして最も左側の列になるようドロップする。
 
30. [クエリの設定]ウィンドウ（右側）より、そのクエリを任意の名前
   （ここでは[マージクエリ]とする）に変更する。
 
---------------------------------------------------------------------------------
 
とりあえず、ここまでの時点において必要とされる形に
データが変換されているかどうかをご確認下さい。
回答
投稿日時: 25/09/27 05:48:22
投稿者: んなっと

もう少し簡単にできました。
 
【元のデータ】
　　　A　　　 B　　　　　C　　　 D　　　　　E　　　 F　　　　　G
1　名前　　猫派　　　　　　　 犬派　　　　　　 前世熊　　　　　　
2　田中　　はい　コメント1　いいえ　コメント2　　はい　コメント3
3　飯田　いいえ　コメント4　　はい　コメント5　いいえ　コメント6
 
【PowerQueryの詳細エディターに】
 
let
    ソース = Excel.CurrentWorkbook(){[Name="テーブル1"]}[Content],
    unp = Table.UnpivotOtherColumns(ソース, {"名前"}, "質問", "回答"),
    idx0 = Table.AddIndexColumn(unp, "連番0", 0, 1, Int64.Type),
    idx1 = Table.AddIndexColumn(idx0, "連番1", 1, 1, Int64.Type),
    mrg = Table.NestedJoin(idx1, {"連番1"}, idx1, {"連番0"}, "マージ", JoinKind.LeftOuter),
    exp = Table.ExpandTableColumn(mrg, "マージ", {"回答"}, {"コメント"}),
    flt = Table.SelectRows(exp, each Number.Mod([連番1], 2)=1),
    del = Table.RemoveColumns(flt,{"連番0", "連番1"}),
    grp = Table.Group(del, {"質問"}, {{"グループ", each _, type table}}),
    del2 = Table.SelectColumns(grp,{"グループ"}),
    exp2 = Table.ExpandTableColumn(del2, "グループ",Table.ColumnNames(del2[グループ]{0}), Table.ColumnNames(del2[グループ]{0}))
in
    exp2
 
【完成図】
 
　　　A　　　 B　　　 C　　　　　D
1　名前　　質問　　回答　 コメント
2　田中　　猫派　　はい　コメント1
3　飯田　　猫派　いいえ　コメント4
4　田中　　犬派　いいえ　コメント2
5　飯田　　犬派　　はい　コメント5
6　田中　前世熊　　はい　コメント3
7　飯田　前世熊　いいえ　コメント6
 
 
【おおまかな流れ】
　[名前]以外の列のピボット解除
→0からの[連番0]、1からの[連番1]追加
→[連番1]に対して[連番0]をマージ
→[連番1]の奇数行削除
→不要な列削除
→[質問]でグループ化
→グループ列の展開
無駄になる可能性があるので、今のところはこれだけ。
投稿日時: 25/09/27 10:17:59
投稿者: toma100

んなっと様、sk様
昨日は体調不良のため、返信遅れて大変申し訳ございません！
 
んなっと様へ
たくさん、ありがとうございます！！
それなのにコードを変更する権限がないので大変申し訳ございません！
自宅ではこちらのコードを入れてやってみます！
操作してみて、こちらにまた連絡させていただきたく思います(._.)
 
sk様へ
操作例を詳しく書いていただいてありがとうございます！
操作してみて、こちらにまた連絡させていただきたく思います(._.)

投稿日時: 25/09/30 08:23:55
投稿者: toma100

 んなっと様へ
 
遅くなって申し訳ございません(._.)/
書いて頂いたコード、自宅のパソコンで貼り付けてみたところ、見事にばっちりできました！
凄すぎます！
会社では、権限がないので、書いていただいた
 
 [名前]以外の列のピボット解除 →0からの[連番0]、1からの[連番1]追加 →[連番1]に対して[連番0]をマージ →[連番1]の奇数行削除 →不要な列削除 →[質問]でグループ化 →グループ列の展開
 
を会社で行い、なんとか試行錯誤して、なんとか希望の形になりました！
本当にありがとうございました(._.)//
 
 
 
sk様へ
遅くなって申し訳ございません(._.)/
詳しく手順を書いて頂き、感謝申し上げます(._.)/
 
手順を追って作成後、試行錯誤して、なんとか希望の形になりました！
本当にありがとうございました(._.)//