日々の仕事でデスクワークを行っているとデータの集計やグラフを作成し、レポートや資料を作成するといった業務を行った方は多いのではないでしょうか？こうした「データ集計・分析」を行うにあたっては、Excelを使って数値データの集計やグラフ化への利用を行うことが多いですが、使用しようとしているデータの集計表やデータ構成も様々な形式があり、
・見た目を意識して、データの利活用やグラフ化がしづらい。
・行ごとに同じようなデータが羅列されていて、どんな傾向があるかわからない。
といったように、一言で「データ」と知っても様々な形やその集約を行った集計表があり、受け手や利用者には扱いづらい、理解しづらいことがあります。
このようなときに、「データクレンジング」という手法によりデータを扱いやすい構造に変換して、データ集計や分析、レポート作成に利活用することができます。
その際、PowerQueryでは、このデータクレンジングを「ピボット」と「ピボットの解除」という機能を利用することで、ノーコードでかつ簡単に処理することができます。
今回はPowerQueryでデータクレンジングを行うための「ピボット、ピボットの解除」について解説していきます。
データクレンジングとピボット、ピボットの解除とは？
日々業務を行っていると様々な形の集計表を目にすることがあるかもしれませんが、それぞれの集計表の作り手が異なると集計表自体の作りや構成も異なってきます。
こうした違いにより、別途データを集計する際やグラフを作成する場合、必要なデータを転記しなおして別の集計表を作ったり、グラフ作成用の集計表ができたりとムダな工程や作成物ができてしまうことになります。
こうしたムダな工程を避けるため、データ分析や集計を行う際に欠かせない作業のひとつが「データクレンジング」という手法です。
データクレンジングは収集したデータを「使える形」に整える手法のことで、
・不要なデータの削除
・フォーマットの統一
・欠損値の処理
・データの形を整える
といった作業もデータクレンジングのうちに含まれます。
データを使える形に整えることは、最終的に「データ分析の質」にもつながります。
こうしたデータクレンジングの手法の中でも、PowerQueryにて「データの形を整える」ための処理が、「ピボット」と「ピボットの解除」の2つの機能です。この2つの機能を駆使することでデータ構造を縦横に変換でき、様々な分析や処理が可能になります。
ピボットとは？
ピボットとは、下図のように行方向に並んだデータを列に展開することです。Excelの「ピボットテーブル」と似ていますが、PowerQueryで行うピボットはデータ構造自体を変換する処理です。
データ構造自体を変換する処理のため、処理後のデータを使ってピボットテーブルで集計、グラフ化することも可能です。
ただし、データ構造がピボットテーブルでの集計に適した形式であるかはチェックが必要です。
ピボットの解除とは？
ピボット解除は、下図のように列方向に展開されたデータを行方向へと変換する処理です。これにより、データベースに適した「縦長形式」になります。
一見すると、ワークシート上での行列の入れ替えを伴うコピペ作業に似ていますが、ピボットでは列に展開するカテゴリと行のクロス集計まで一括して行うことができること。ピボットの解除では様々な形の集計表を統一された縦長形式のデータに変換し、データ分析に利用することができるといった単純なコピペ作業とは大きな違いがあります。
また、PowerQueryでのデータ加工となるため読み込み先のデータ構成が変わらなければ半自動的にデータクレンジングから更新までのクリック操作で行うことができます。
PowerQueryの概要やデータ取込や更新内容については下記ページにて詳細を記載しているので、こちらも併せてご覧ください。
【概要】業務効率化への一歩！ Excel自動化機能「Power Query」とは？
【初心者でもできる】業務自動化への第一歩！ Power Queryでのデータ取得と更新方法
ピボット、ピボットの解除のメリット・デメリット
PowerQueryのピボット、ピボットの解除により変換されたデータには特徴があり、それぞれの機能にはどのようなメリットやデメリットがあるのかを押さえておくことは重要なポイントです。
ピボット、ピボットの解除それぞれのメリット・デメリットについて下記で押さえていきましょう。
ピボットのメリット
ピボットの処理には下図のように大きく3つのメリットがあります。
●表の視認性向上
ピボットにより変換されたデータはクロス集計のような表形式（横長形式）のデータとなるため、縦長形式のように行数（レコード数）が相当数存在するデータテーブル型とは異なり、変換された表データの視認性が上がり、表データの把握がしゃすくなります。
●レポート作成に適する
表の視認性も向上することに加え、集計対象のカテゴリごとにクロス集計の形でデータ集計・更新が可能となるため、ピボット機能で作成したデータをレポート・資料作成に活用することに向いています。
●数値比較がしやすい
クロス集計と同様、行・列でそれぞれ集計対象のカテゴリごとに数値集計ができるため、ピボットにより変換された表データにて数値の比較や傾向を把握すること、簡易的に分析を行うことができます。
ピボットのデメリット
ピボットの処理には下図のように大きく3つのデメリットがあります。
●列数が多くなる
列に展開するデータの内容にもよりますが、月日のように日々列数が増加するデータを列数に展開した場合、更新のたびに横長に長くなる表となってしまうため、逆に視認性が損なわれることにつながりかねないです。
月日の場合、日のデータを月にグループ化するなどカテゴリに応じてグループ化することで列数の簡略化につなげることも可能です。列に展開するデータに関してはどんどん横長に列数が増加する場合はデータ構成の検討が必要となります。
●データベースに適さない
ピボット処理で変換した横長形式のデータはクロス集計としての形式や視認性を高める一方で、データベースへのデータ取込や管理に適した構造ではないため、データ管理やより高度な分析や可視化への流用には不向きです。
●BIツールでのグラフ化に不向き
ピボット処理で変換した横長形式のデータはデータベースだけでなく、PowerBIやtableauといったBIツールへの取込にも不向きです。そのため、BIツールを使ったダッシュボード開発やデータ分析には適した機能とは言いづらいです。
「ピボット」機能によるデータクレンジングは集計表として使う横長形式としてのデータ構造であるため、前提として後工程で他システムやソフトにデータを取込、分析や集計をするというよりはピボットで変換された表形式で完結することを目的としたデータクレンジング処理に適した機能と言えます。
ピボットの解除のメリット
ピボットの解除には下図のように3つのメリットがあります。
●データベースに適している
ピボットの解除で処理した「縦長形式」のデータ構造はデータベースソフトへの取込や管理に適した構成であるため、集計表のデータや平仄が異なる集計表のデータをクレンジングしてデータベースへ取込、活用することへ最適な手法です。
●データ加工、可視化が容易
「縦長形式」のデータ構造はSQLやPowerQueryでのデータ加工、ピボットテーブル、PowerPivot集計やグラフ作成に適しており、ピボットの解除で処理したデータを二次利用して加工・集計するのに適した形式に処理することができます。
●分析に最適
「縦長形式」のデータ構造は加工や集計だけでなく、SQLやDAX、統計処理へ利用しやすいデータ構造でもあるため、ピボットの解除で処理したデータを用いて分析を行いやすくなります。
ピボットの解除のデメリット
ピボットの解除のデメリットは大きく下図の2つが挙げられます。
●一目でデータ把握がしづらい
ピボットで処理したクロス集計のような横長形式とは異なり、ピボットの解除で処理した縦長形式のデータ構造では、列数が少ない代わりに一目でデータの構成や数値の傾向を判断することが難しいです。そのため、ピボットの解除で処理したデータを別途集計する必要があります。
●レポートや印刷には不向き
縦長形式のデータ構造の場合、行数（レコード数）が相当数発生することや、カテゴリごとに集計されたデータではなくローデータに近い形となるため、ピボットの解除で処理したデータをピボット処理したデータと同様にレポートや資料、また印刷して使用するといった活用には不向きな処理となります。
ピボットの解除では、「データベースへの利用」や「加工、集計、分析がしやすくなる」といったように処理後のデータ構造のみで利用するわけではなく、ピボットの解除で処理したデータを二次利用することに適しています。
そのため、ピボットの解除によって作成されたデータをそのまま使うわけではなく、「別システムやソフトへの連携」、「PowerPivotやDAXでの集計、グラフ化」といった二次利用を前提で使用することが望ましいです。
ピボット、ピボットの解除の使い方
ここまで、PowerQueryでのピボット、ピボットの解除についての概要やメリット・デメリットを記載してきましたが、実際に使う場合にはどのように行えばいいのでしょうか？
ピボット、ピボットの解除それぞれの使い方について説明していきます。
ピボットの使い方
ピボットでは行方向に並んだデータを列に展開する手法でした。
今回は下記のようなデータをピボット処理する流れを説明していきます。
| 商品 | 月 | 売上 |
| A | 1月 | 100 |
| A | 2月 | 200 |
| B | 1月 | 150 |
| B | 2月 | 180 |
データとして、「商品」、「月」、「売上」の3つの項目に対して各行に対象のデータが配列した形式となります。
このデータを月を列に展開し、各商品（A、B・・・）の売上をクロス集計の形にデータ構造自体を変換することがピボットの処理となります。
このピボット処理を行うにあたり、
①：データ取込
②：ピボットの処理選択
③：データ整形
の3つのステップを進めて、ピボット処理を行います。
①：データ取込
このステップではデータ取込を行いつつ、PowerQueryエディターで「ピボット」の処理を行う手前までの工程となります。
前提として、「商品」、「月」、「売上」の3つの列名を持つ集計表をPowerQueryエディターで読み込みを行い、PowerQueryエディターで加工・整形を行える状態にしていきます。
実際の作業としては①～③の内容となります。
① 読み込んだデータのうち、ピボット処理で「列名に持ってくるカラム」を選択する。（今回は月のカラムが対象となる。）
② PowerQueryエディターの変換タブを選択。
③ 「列のピボット」をクリック。
②：ピボットの処理選択
PowerQueryエディターにて「列のピボット」をクリックすると、下記のようなピボット処理の画面が立ち上がります。
ピボット処理の画面では①～③の操作を行います。
① 選択した列名にある名前を使用して新しい列を作成します。という文面が表示されているか確認しましょう。（別の列名が表示されている場合は再度列を選択し直して「列のピボット」をクリックします。）
② ピボット処理をする際に集計するデータの列名を選択します。
③ 詳細設定オプションをクリックして、値の集計関数が目的とする集計方法かをチェックします。
※列名で数値データ選択した場合、デフォルトでは合計となりますが、平均値や値の個数を集計したい場合はプルダウンから集計したい関数を選択します。
③：データ整形
ピボット処理後、データ処理が行われて目的とする「クロス集計」のデータ構造へ変換されます。
実施にこの工程では処理を行うことは少ないですが、ピボット処理の結果が目的としているデータ構造となっているかをチェックすることが重要になってきます。
実際の操作は下記のような流れとなります。
① 選択した列のデータが列名に反映され、各列の集計値が選択した集計関数で処理された数値が反映されます。
② ホームタブの「閉じて読み込む」にて、加工した処理を保存します。
※単純に閉じて読み込むをクリックし、新規ワークシートへ加工したデータを反映させてもいいですが、読み込み形式を変更したい場合は「閉じて次に 読み込む」を選択しましょう。
ピボットの解除の使い方
ピボットの解除は列方向に展開されたデータを行方向へと変換する手法でした。
今回は下記のようなデータをピボットの解除で縦長形式のデータ構造に変換していきます。
| 商品 | 1月 | 2月 |
| A | 100 | 200 |
| B | 150 | 180 |
データとしては、「商品」、「1月」、「2月」・・・のように月が列に並ぶ形の構造となり、商品と月のクロス集計表のような形式となっています。
このデータを商品、月、売上の列構成にすることが「ピボットの解除」の処理目的となります。
ピボットの解除を行うにあたり、
① データ取込
② ピボットの解除処理、データ整形
のステップを踏んで、処理を行っていきます。
① データ取込
このステップではデータ取込を行いつつ、PowerQueryエディターで「ピボット」の処理を行う手前までの工程となります。
前提として、「商品」、「1月」、「2月」の3つの列名を持つ集計表をPowerQueryエディターで読み込みを行い、PowerQueryエディターで加工・整形を行える状態とします。
実際の作業としては①～③の内容となります。
① 読み込んだデータのうち、ピボットの解除で「行に持ってくるカラム」を選択する。（今回は１月、2月のカラムが対象となる。）
② PowerQueryエディターの変換タブを選択。
③ 「列のピボット解除」をクリックし、列のピボット解除をクリック
※「その他の列をピボット解除」の場合、選択された以外の列が行に変換される。
②：ピボットの解除処理、データ整形
ピボットの解除はピボットとは異なり、列のピボット解除を実行すると処理画面が立ち上がることなく、データ構造の変換が実行されます。
実際に行う工程は少ないですが、データ構造を大きく変換しているため、処理内容としては大掛かりな変更処理を行っているため、目的とするデータ構造になっているかをチェックしていくことがこの工程では重要になります。
実際の作業としては下記①、②となります。
① ピボットの解除により、列名が「属性」の列へ、列内のレコード値が「値」の列に変換されていることを確認する。
※ピボットの解除を行った場合、「属性」と「値」の列が必ず作成される。この列名はPowerQueryにて修正可能なため、用途に応じて列名を変更するとベター。
② ホームタブの「閉じて読み込む」にて、加工した処理を保存します。
※単純に閉じて読み込むをクリックし、新規ワークシートへ加工したデータを反映させてもいいですが、読み込み形式を変更したい場合は「閉じて次に読み込む」を選択しましょう。
PowerQueryでのデータの取込やエディターでのデータ加工後の保存方法については以下の記事で詳細に説明しておりますので、ご参照いただければと思います。
【初心者でもできる】業務自動化への第一歩！ Power Queryでのデータ取得と更新方法
ピボット、ピボットの解除はどのように活用できるか？
ここまでPowerQueryでの「ピボット」、「ピボットの解除」の機能についてメリット・デメリットや使い方について紹介してきましたが、実際に仕事やExcel作業でどのように活用することができるのでしょうか？
「ピボット」、「ピボットの解除」それぞれの活用例について触れていきます。
ピボットの活用例
二次利用を主とせず、ピボットにてデータ変換された横長形式のデータ構造で集計値の把握や分析を行うことを主とする利用。
例：商品別売上やWebサイトへのアクセス数といった集計表としての利用・更新。
PowerQueryで集計表を作成することで、読み込み元データが更新されても、データ更新のみで集計表も更新されるため、効率的な集計が可能となります。
ピボットの解除の活用例
一次利用を主とせず、ピボットの解除にて変換された縦長形式の構造データを用いて、二次利用として集計・可視化・分析を行うことを主とする利用。
例：アンケートデータの集計・グラフ作成や分析。売上時系列データの集計、推移可視化。
PowerQueryで縦長形式の構造データを作成することで、読み込み先のデータが更新されても、データ更新にて最新データとして利用できます。
「ピボット」、「ピボットの解除」ともに
・集計表として活用するか。
・一次データとして加工して二次利用するか。
それぞれの活用方法が異なってきます。
実際に私も仕事で「ピボット」、「ピボットの解除」を使ったデータクレンジングを行うことはありますが、実務で使う頻度が高かったのは「ピボットの解除」でした。
仕事の特性上、数値データの集計だけでなくその分析やグラフ作成といった作業を行うことが多かったですが、ピボットで作成した集計表をそのままレポートや資料に使用するということはあまりなく、受け手にわかりやすい可視化を行うとなると、やはりピボットの解除でデータを縦長形式の一次データへ変換し、二次利用する頻度が多かったためです。
ですが、「データクレンジング」として、また利用用途が多岐に渡る面で「ピボットの解除」を使うことが実務では多い印象です。
企業や部署によって集計表の作りや見せ方は様々なため、「ピボット」機能では補え切れない側面もあるかと思います。
一方で、縦長形式のデータ構造はデータテーブルの構成であり、データベースやBIツールと他システム・ソフトへの連携もしやすいため活用範囲が広がること。また、様々な集計表のデータをデータテーブルの構成に変換し、別途利用するには「ピボットの解除」は必須の機能とも言えます。
PowerQueryでのピボット、ピボットの解除を学ぶためには？ Udemyを利用しよう！
これまで解説してきた「ピボット」「ピボットの解除」の概念や操作方法は、文章や画像である程度理解できますが、「実際の業務データにどう使うのか？」という具体的な活用例までは独学ではなかなか難しいこともあるのではないかと思います。
また、独学で学ぼうとしても、
・Power Queryについて、学習している時間がない。
・書籍を読んでも、なかなか身に着けることができない。
といった方もいらっしゃるのではないでしょうか？
私自身も時間がない中でどうやって学習すればよいか悩みました。
本で学習しようにも、読む時間や学んだことを定着化させることに難しさを感じていました。
そんな中、学習を行う上で、非常に役立ったのが、
というサイトでの学習コンテンツでした。
Udemyでの動画を活用することで、
効率的にPowerQueryやその先の集計（Power PivotやDAX）を学習できた。
学んだスキルを定着させ、日々の仕事に活かすことができている。
大きく2つのメリットが自身にとってありました。
Udemyについては、過去にまとめた記事がありますのでそちらもご参照ください。
特にUdemyを利用して、
・ハンズオン形式で手を動かしながら学ぶことができる。
・動画のため、再生速度を調節しながら時短できる（1.5倍速～2倍速で視聴していました。）
・ポイントを絞って、繰り返し視聴で学習できる。
といった良かった点がありました。
Udemyではよくセールをしているため、セール期間中であれば90%以上割引（1動画1,200円～1,800円程度）で購入できます。
特に、私が学習していてオススメの動画は下記3つでした。
①Microsoft Excel – Power Pivot, Power Query, DAX 入門講座
≪こんな方にオススメ！≫
Power Queryでの加工からPower PivotやDAXを使った集計・分析までをざっくりと学んでみたい方向け。
②【続】Microsoft – Excel Power Pivot 入門講座 ～Power Query Editor編～
≪こんな方にオススメ！≫
Power Queryエディターでのデータ加工/整形方法を集中的に学びたい方向け。
③Excel＆ Power BI両方で使えるPower Query 完全ガイド 初級者から上級者まで対応
≪こんな方にオススメ！≫
Power Queryの概要は理解しており、さらに発展的に学びたい方向け。
PowerQueryを学習してみたい、仕事に活かしてみたいと思った方は是非、利用・活用してみてください！
≪書籍はこちら≫
Excelパワークエリ データ収集・整形を自由自在にする本まとめ
今回はPowerQueryでの「ピボット」、「ピボットの解除」について解説してきました。
この「ピボット」、「ピボットの解除」の機能はデータクレンジングを効率的に行い、データ集計や分析といった活用の幅を広げるために大きく役立つ機能となります。
それぞれの機能として、
ピボット：縦→横へデータ構造を変換。クロス集計やレポート作成に最適。
ピボットの解除：横→縦へデータ構造を変換。分析やBIツール向けに最適。
といったように、目的に応じて使用することでデータの利活用を効率的に行うことが可能となります。
こうしたピボット、ピボットの解除を使ったデータクレンジングのスキルを身に着けることでExcelでの集計や分析作業の幅が広がることや業務を効率的に進めることもできるようになります。
今回の記事から、
・PowerQueryを学んでみよう！
・日々の仕事に活用してみよう！
と思えてもらえますと幸いです。
コメント