【エクセル パワークエリ】 列が多段のマトリクス形式の表をリスト形式に変換する方法【Excel PowerQuery】【ピボット解除】
Excelで縦横に展開されてたクロス集計表と呼ばれたりするピボット形式の表は、見る分にはいいのですが、ピボットテーブル等で集計するための元データとしては、このままでは使えません。
項目がすべて縦に並ぶ、いわゆるリスト形式に変換する必要があります。
手作業でこういった変換をやるのは非常に大変です。
マクロを使えば自動化はできますが、そもそもマクロ自体、慣れていない人にとっては敷居が高いです。
そこで、今回はこういった表を、PowerQueryをつかい、マウス操作だけでフォーマットを変換する方法について解説します。
以下の動画でも同じ内容を解説しています。
記事をご購入いただくと、記事の最下部から動画の解説で実際に使用しているExcelファイルをダウンロードすることができます。
=== ストアカで開催中のオンライン講座 ===
===============================
やりたいこと
Excelで、列項目がセル結合されて多階層になっているクロス集計形式の表があったとき
すべての項目が縦に並ぶ、リスト形式に表を変換したい。
手順1：表をテーブルにする
対象となるクロス集計表をテーブルに設定します。
その際、『先頭行をテーブルの見出しとして使用する』のチェックは外しておきます。
手順2：テーブルをクエリに取得
テーブルにしたクロス集計表をクエリに取り込みます。
手順3：表の行列を入れ替える
『変換』タブの『入れ替え』で、テーブルの行列を入れ替える。
手順4：1行目をヘッダーにする
『変換』タブの『1行目をヘッダーとして使用』で、データ行の1行目をヘッダーにする。
手順5：フィルで空欄を埋める
行項目になっている列のうち、nullがある列（元々のクロス集計表のヘッダーでセル結合されていた部分。今回の場合はColumn1～2）を選択して、『変換』タブの『フィル』＞『下』をつかって空欄を埋める。
手順6：ピボット解除
元々のクロス集計表の列項目だった部分（今回の場合はColumn1～3）を選択して、『変換』タブの『列のピボット解除』＞『その他の列のピボット解除』。
手順7：フィールド名の設定
正しいフィールド名を設定する。
（加えて、必要であればデータ型の変換等も行い、データを整える）
手順8：テーブルとしてシートに読み込む
※注
この時点でフォーマットの変換は完了しているので、クエリで作成したデータテーブルをシートに読み込む必要がない場合は、手順８は行わなくても構いません。
『ホーム』タブの『閉じて読み込む』で、PowerQueryエディターを閉じる。
『クエリと接続』のサイドバーに先ほど作成したクエリが表示されるので、右クリックして『読み込み先』をクリック。
データのインポートで読み込み方法を『テーブル』、データを返す先を『新規ワークシート』を選択してOK。
これで、表のフォーマットの変換が完了しました。
最後の手順８では、クエリでフォーマットを変換したデータを、シートにテーブルとして読み込む手順を解説しましたが、フォーマット変換後のデータをクエリでそのまま利用する場合や、ピボットテーブルとしてテーブルとして読み込みたい場合など、ケースによってやり方は変えてください。
さいごに
PowerQueryをつかうと、手作業でやるには非常に大変な表フォーマットの変換を、何回かのマウスクリックだけで簡単に行うことができます。
マクロで変換を自動化するものいいですが、せっかく簡単な操作で可能になる方法があるので、ぜひこちらの方法を試してみてください。
PowerQueryには、他にもデータを集計しやすくクリーニングする機能がいっぱいありますので、今までExcel業務でPowerQueryをつかったことがなかった方も、ぜひPowerQueryにチャレンジしてみてください。
おしまい。
サンプルファイルダウンロード
動画で使用しているファイルはこちらからダウンロードできます。
よければサポートしていただけると嬉しいです😀 有用な記事や動画を制作していけるように頑張ります❗️