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

列のピボット解除

Power Queryの[列のピボット解除]という機能に初めて遭遇した時、感動したのを覚えています。

例えば、部署別の販売実績表など、いわゆる「クロス集計表」(図表1)は「人の目には優しい」ですが、実は「データとして扱いにくい形式」なのです。

図表1 横持ちデータといわれる、クロス集計表

コンピュータでデータを扱うには、クロス集計表のような「横持ちデータ」の形式ではなく、シンプルな「縦持ちデータ」と呼ばれる“リスト形式”にする必要があります(図表2)。

図表2[列のピボット解除]でリスト形式に変更

Power Queryが登場する以前、「横持ち」データを「縦持ち」データに変換するためマクロを使うなど、苦労してきた黒歴史があります。

しかし、Power Queryの[列のピボット解除](図表2)という機能を使えば、「横持ち」データを「縦持ち」データに変換するのは一瞬です。

これまでの苦労が何だったのかというほど簡単にデータを変換できてしまいます。

Power Queryエディターの[変換]タブの[任意の列]グループの[列のピボット解除]をクリックし若干のステップを踏めば良いだけです。

基本的なステップ

Excelで図表1のクロス集計表を選択後、[データ]タブから[データの取得と変換]グループの中の[テーブルまたは範囲から]をクリック、するとPower Queryエディターの画面になりデータが読み込まれます(図表3)。

500図表3 横持ちデータをPower Querエディタに読み込み

読み込まれたデータのうち、「総計」と書かれた行列は各データの合計値であるため、リスト形式では不要です。

そこで[ホーム]タブの[列の削除]と[行の削除]を用いて「総計」の行列を削除します。

そして、「日付」と書かれた左側の「ABC123」 のボタンをクリック(またはその「日付」列で右クリックし、「型の変更」をクリック)、「日付」をクリックすれば「年月日」で表示できるようになります(図表4)。

図表4 行と列の「総計」を削除し、「日付」列を日付形式に変更

なお、この「型の変更」を正しく設定しないと、この先のデータ分析が正しく行われないので留意が必要です。

あとは「A」「B」「C」と記載された3つの列を選択後、[変換]タブの[列のピボット解除]をクリックすれば、リスト形式に変換できます(図表2)。

縦持ちデータと呼ばれるリスト形式のデータになれば、ピボットテーブルで自由にデータ分析できるようになります。

第2回でご紹介しました[データのインポート]で[ピボットテーブルレポート]を選択し、Power QueryエディターによるここまでのデータをExcelに読み込みます。

そのうえで、後述するPower Pivotのリレーションシップにより複数のテーブルを用いたデータ分析を行えば、これまでにない新たな洞察を得ることにも繋げられます。

マクロを使うよりも簡単にデータ変換できてしまう、こんなところが、モダンExcelの魅力の一つです。

[列のピボット解除]の他にも、

様々なETL(抽出、変換、読込)機能を有するPower Query

を上手に活用しデータ分析の前提となるデータの整理をすることが、今後ますます必要になってくると思います。

「横持ち」データを「縦持ち」データに変換する

基本的な流れは、動画をチェック!

今回のポイント

・コンピュータでデータを扱うには、クロス集計表のような「横持ちデータ」の形式ではなく、シンプルな「縦持ちデータ」と呼ばれる“リスト形式”にする必要がある。

・横持ちデータを縦持ちデータに変換するには、Power Queryの[列のピボット解除]を使えばよい。

・「型の変更」を正しく設定しないとデータ分析が正しく行われないので留意が必要。

【ワンポイント】 クエリ

図表2③の左側に「クエリ」、右側に「クエリの設定」がそれぞれありますが、ここでいう“クエリ”とはPower Queryエディターに読み込んだデータのことを指していると考えればよいでしょう。元々の語源はQuestion(質問)です。

モダンExcel研究所

フォローお待ちしてます!

1件のピンバック

コメントは現在停止中です。

error: Content is protected !!