![見出し画像](https://assets.st-note.com/production/uploads/images/39748526/rectangle_large_type_2_f127bfb98806622d01aa0d5b2b219175.png?width=800)
【エクセル パワークエリ】 列が多段のマトリクス形式の表をリスト形式に変換する方法【Excel PowerQuery】【ピボット解除】
Excelで縦横に展開されてたクロス集計表と呼ばれたりするピボット形式の表は、見る分にはいいのですが、ピボットテーブル等で集計するための元データとしては、このままでは使えません。
項目がすべて縦に並ぶ、いわゆるリスト形式に変換する必要があります。
![画像3](https://assets.st-note.com/production/uploads/images/39749222/picture_pc_505c598f6bf65c4991e8e0ec11871e59.png?width=800)
手作業でこういった変換をやるのは非常に大変です。
マクロを使えば自動化はできますが、そもそもマクロ自体、慣れていない人にとっては敷居が高いです。
そこで、今回はこういった表を、PowerQueryをつかい、マウス操作だけでフォーマットを変換する方法について解説します。
以下の動画でも同じ内容を解説しています。
記事をご購入いただくと、記事の最下部から動画の解説で実際に使用しているExcelファイルをダウンロードすることができます。
=== ストアカで開催中のオンライン講座 ===
![画像23](https://assets.st-note.com/production/uploads/images/46654496/picture_pc_2f2f148c59d39b8d0211c17b13793efd.png)
===============================
やりたいこと
Excelで、列項目がセル結合されて多階層になっているクロス集計形式の表があったとき
![画像1](https://assets.st-note.com/production/uploads/images/39748816/picture_pc_13f9693967574bd232631c928ac177e4.png?width=800)
すべての項目が縦に並ぶ、リスト形式に表を変換したい。
![画像2](https://assets.st-note.com/production/uploads/images/39748891/picture_pc_78137197a725a3552c7dcf862569cf27.png)
手順1:表をテーブルにする
対象となるクロス集計表をテーブルに設定します。
その際、『先頭行をテーブルの見出しとして使用する』のチェックは外しておきます。
![画像4](https://assets.st-note.com/production/uploads/images/39749545/picture_pc_aef61383d0282d19c220edf1e070d687.png?width=800)
![画像5](https://assets.st-note.com/production/uploads/images/39749564/picture_pc_3fccdb016941f11446b18d8c7d79f9e6.png)
![画像6](https://assets.st-note.com/production/uploads/images/39749635/picture_pc_8f9e681ae742aac445c64b1422413de6.png?width=800)
手順2:テーブルをクエリに取得
テーブルにしたクロス集計表をクエリに取り込みます。
![画像7](https://assets.st-note.com/production/uploads/images/39749862/picture_pc_a22d4c55e3323903e699eccc537d8482.png)
![画像8](https://assets.st-note.com/production/uploads/images/39749934/picture_pc_30b121901eb2390d02ce0cc6de626452.png?width=800)
手順3:表の行列を入れ替える
『変換』タブの『入れ替え』で、テーブルの行列を入れ替える。
![画像9](https://assets.st-note.com/production/uploads/images/39750076/picture_pc_4dd16688ae3b83790f8504bde39e07fd.png?width=800)
![画像10](https://assets.st-note.com/production/uploads/images/39750175/picture_pc_e074ace12b9dcab661f81e090eb8d390.png?width=800)
手順4:1行目をヘッダーにする
『変換』タブの『1行目をヘッダーとして使用』で、データ行の1行目をヘッダーにする。
![画像12](https://assets.st-note.com/production/uploads/images/39750534/picture_pc_9983f078e582b5c8d61b3e36a5ff5b1d.png?width=800)
![画像12](https://assets.st-note.com/production/uploads/images/39750465/picture_pc_36fd186d0e78d297601a9d5283dc8afd.png?width=800)
手順5:フィルで空欄を埋める
行項目になっている列のうち、nullがある列(元々のクロス集計表のヘッダーでセル結合されていた部分。今回の場合はColumn1~2)を選択して、『変換』タブの『フィル』>『下』をつかって空欄を埋める。
![画像13](https://assets.st-note.com/production/uploads/images/39750828/picture_pc_fd4241abec34b4ec1304d92cec24ed15.png?width=800)
![画像14](https://assets.st-note.com/production/uploads/images/39750896/picture_pc_90f958558339e524707dc771d2b0cc68.png?width=800)
手順6:ピボット解除
元々のクロス集計表の列項目だった部分(今回の場合はColumn1~3)を選択して、『変換』タブの『列のピボット解除』>『その他の列のピボット解除』。
![画像15](https://assets.st-note.com/production/uploads/images/39751256/picture_pc_3e31273db5c2ee3cf2987ad744faffd9.png?width=800)
![画像16](https://assets.st-note.com/production/uploads/images/39751303/picture_pc_b852f0461d3a34e23d2a3999a5e07bed.png?width=800)
手順7:フィールド名の設定
正しいフィールド名を設定する。
(加えて、必要であればデータ型の変換等も行い、データを整える)
![画像17](https://assets.st-note.com/production/uploads/images/39751367/picture_pc_0a8751d4657b677791cfd3f39984d00c.png?width=800)
![画像18](https://assets.st-note.com/production/uploads/images/39751563/picture_pc_e1804b4b379ae9977b19548e540d847d.png?width=800)
手順8:テーブルとしてシートに読み込む
※注
この時点でフォーマットの変換は完了しているので、クエリで作成したデータテーブルをシートに読み込む必要がない場合は、手順8は行わなくても構いません。
『ホーム』タブの『閉じて読み込む』で、PowerQueryエディターを閉じる。
![画像19](https://assets.st-note.com/production/uploads/images/39751681/picture_pc_99472b03312eaeb2006698cb8280cd6b.png?width=800)
『クエリと接続』のサイドバーに先ほど作成したクエリが表示されるので、右クリックして『読み込み先』をクリック。
![画像20](https://assets.st-note.com/production/uploads/images/39751829/picture_pc_1a9af8d8fb3fedd8ecb8607ea4351f12.png)
データのインポートで読み込み方法を『テーブル』、データを返す先を『新規ワークシート』を選択してOK。
![画像21](https://assets.st-note.com/production/uploads/images/39751983/picture_pc_583e1d3b0b0b01224a1424dc60de2ffb.png)
![画像22](https://assets.st-note.com/production/uploads/images/39752017/picture_pc_fbb5259165f6a85ca90e56906241ccdb.png)
これで、表のフォーマットの変換が完了しました。
最後の手順8では、クエリでフォーマットを変換したデータを、シートにテーブルとして読み込む手順を解説しましたが、フォーマット変換後のデータをクエリでそのまま利用する場合や、ピボットテーブルとしてテーブルとして読み込みたい場合など、ケースによってやり方は変えてください。
さいごに
PowerQueryをつかうと、手作業でやるには非常に大変な表フォーマットの変換を、何回かのマウスクリックだけで簡単に行うことができます。
マクロで変換を自動化するものいいですが、せっかく簡単な操作で可能になる方法があるので、ぜひこちらの方法を試してみてください。
PowerQueryには、他にもデータを集計しやすくクリーニングする機能がいっぱいありますので、今までExcel業務でPowerQueryをつかったことがなかった方も、ぜひPowerQueryにチャレンジしてみてください。
おしまい。
サンプルファイルダウンロード
動画で使用しているファイルはこちらからダウンロードできます。
よければサポートしていただけると嬉しいです😀 有用な記事や動画を制作していけるように頑張ります❗️