第3回 速攻! 横持ちデータを縦持ちデータに変換

列のピボット解除
Power Queryの[列のピボット解除]という機能に初めて遭遇した時、感動したのを覚えています。
例えば、部署別の販売実績表など、いわゆる「クロス集計表」(図表1)は「人の目には優しい」ですが、実は「データとして扱いにくい形式」なのです。
コンピュータでデータを扱うには、クロス集計表のような「横持ちデータ」の形式ではなく、シンプルな「縦持ちデータ」と呼ばれる“リスト形式”にする必要があります(図表2)。
Power Queryが登場する以前、「横持ち」データを「縦持ち」データに変換するためマクロを使うなど、苦労してきた黒歴史があります。
しかし、Power Queryの[列のピボット解除](図表2)という機能を使えば、「横持ち」データを「縦持ち」データに変換するのは一瞬です。
これまでの苦労が何だったのかというほど簡単にデータを変換できてしまいます。
Power Queryエディターの[変換]タブの[任意の列]グループの[列のピボット解除]をクリックし若干のステップを踏めば良いだけです。
基本的なステップ
Excelで図表1のクロス集計表を選択後、[データ]タブから[データの取得と変換]グループの中の[テーブルまたは範囲から]をクリック、するとPower Queryエディターの画面になりデータが読み込まれます(図表3)。
読み込まれたデータのうち、「総計」と書かれた行列は各データの合計値であるため、リスト形式では不要です。
そこで[ホーム]タブの[列の削除]と[行の削除]を用いて「総計」の行列を削除します。
そして、「日付」と書かれた左側の「ABC123」 のボタンをクリック(またはその「日付」列で右クリックし、「型の変更」をクリック)、「日付」をクリックすれば「年月日」で表示できるようになります(図表4)。
なお、この「型の変更」を正しく設定しないと、この先のデータ分析が正しく行われないので留意が必要です。
あとは「A」「B」「C」と記載された3つの列を選択後、[変換]タブの[列のピボット解除]をクリックすれば、リスト形式に変換できます(図表2)。
縦持ちデータと呼ばれるリスト形式のデータになれば、ピボットテーブルで自由にデータ分析できるようになります。
第2回でご紹介しました[データのインポート]で[ピボットテーブルレポート]を選択し、Power QueryエディターによるここまでのデータをExcelに読み込みます。
そのうえで、後述するPower Pivotのリレーションシップにより複数のテーブルを用いたデータ分析を行えば、これまでにない新たな洞察を得ることにも繋げられます。
マクロを使うよりも簡単にデータ変換できてしまう、こんなところが、モダンExcelの魅力の一つです。
[列のピボット解除]の他にも、
様々なETL(抽出、変換、読込)機能を有するPower Query
を上手に活用しデータ分析の前提となるデータの整理をすることが、今後ますます必要になってくると思います。

「横持ち」データを「縦持ち」データに変換する
基本的な流れは、動画をチェック!
【ワンポイント】 クエリ
図表2③の左側に「クエリ」、右側に「クエリの設定」がそれぞれありますが、ここでいう“クエリ”とはPower Queryエディターに読み込んだデータのことを指していると考えればよいでしょう。元々の語源はQuestion(質問)です。
「モダンExcel研究所」を楽しむ際の【注意事項】
コンテンツコピー、スクレイピングなど【厳禁】 ☚【検知ツール設置済】☚悪質な場合【法的措置】を講じます
(過負荷によるサーバー障害が生じた経験上、ご協力お願いいたします!)

今回のポイント
・コンピュータでデータを扱うには、クロス集計表のような「横持ちデータ」の形式ではなく、シンプルな「縦持ちデータ」と呼ばれる“リスト形式”にする必要がある。
・横持ちデータを縦持ちデータに変換するには、Power Queryの[列のピボット解除]を使えばよい。
・「型の変更」を正しく設定しないとデータ分析が正しく行われないので留意が必要。

モダンExcel研究所
フォローお待ちしてます!
「モダンExcel」の入門書

1件のピンバック
第6回 コピペで効率化! クエリの複製・参照 ★ モダンExcel研究所
コメントは現在停止中です。