こんにちは、Excellent仕事術のガッツ鶴岡です
前回までは複数のクエリ（データ）を組み合わせる技術を解説しました
今回からは、Power Query Editor上でデータを変換する技術を解説していきます
ところで、題名にあるPivotはどう意 味かご存知でしょうか？
ちなみに辞書には軸とか、旋回と書いてありました
この回を終了した時には、”Pivot”の意味が納得できるようになっていると思います
＜今回のポイント＞
A．集計の目的とデータの並べ方の関係
下の２つのデータを見比べてください
①は縦に繰り返し並んでいます。一方、②は、①の２つの目の列が横に“旋回”して並んでいます
どちらが集計しやすいと思いますでしょうか？
全体の合計値を出す場合、①は1列を範囲にしてSUM関数を入力すれば算出できます
一方、②は見出し以外の全ての行列を範囲に含めてSUM関数を入力するしかありません
①と②のデータで「アルファベット」と「ひらがな」別、つまり２つの切り口を組み合わせて合計値を集計する場合も考えてみましょう
①はフィルターをかけて、ひらがなの列だけみれば合計値を集計できます
②の場合には、目で列を追いかけながら集計するしかありません
ですので、「データはあらゆる切り口でデータ集計が行えるようにする」、ということであれば、縦方向に繰り返し並べていくべきなのです
B．Power Query上での操作
Power Queryでは、Power Query Editor上で、横に旋回しているデータを縦の並びに切り替え、集計を行い易い形にてデータを出力し直すことができます
その際、旋回させる軸を決めるのと同時に、以下のサンプルデータの合計の行列（Total)を削除します
この合計の値を消して、その後はどのようにに合計値を算出していくかについては後述します
では、実際のサンプルデータを基にして解説をはじめます
１．Power Query Editorを立ち上げる
データタブの「テーブルまたは範囲から」をクリックし、Power Query Editorを立ち上げます
この際、8行目のTotalの範囲が入るように気を付けましょう！
こちらは前述のように、Power Query Editor上で削除を行います
２．合計行列を消去する
Power Query Editorが開いたら次の①②の処理を行い、「 B．Power Query上での操作」で前述したようにTotalの行列を削除します
①フィルターからTotal行を消去する
-フィルターをクリックします
-チェックを外して一覧からTotalを消します
➁Total列を消去する
Total列を指定し、削除をクリックします
３．Pivot解除
ではPivot解除機能により、横に旋回しているデータを縦の並びに切り替えます
①軸を指定します
今回は、横に展開している日付を縦並びにします
ですので、一番左にあるカテゴリー列を左クリックして指定します
その後、右クリックすると可能な処理の一覧がでてきます
➁その他の列のピボット解除
①の一覧から「その他の列のピボット解除」をクリックします
これで、横に展開していたデータが縦並びになりました
４．読込先をピボットテーブルに指定する
Power Query Editorで縦並びにしたデータを「ピボットテーブル」としてエクセルシート上に読込みます
①閉じて次に読みこむを指定
ホームタブにて「閉じて読み込む」の右横▼マークから「閉じて次に読み込む」を指定します
➁ピボットテーブルの読込先を指定
ｰ「既存のワークシート」内の「元のデータの下」A11セルを指定しましょう！
-ピボットテーブルのフィールドを以下のように指定しましょう！
Power Query Editor上でTotalの行列は削除しましたが、ピボットテーブルでTotal（合計）が集計できるようになりました
逆に、もしPower Query Editor上でTotal行列を削除しなかったら、ピボットテーブル上で合計値が本来の値より2倍になってしまいます
＜まとめ＞
このPivot解除を知っておけば、他の方から入手したデータが、横並びになっていなかったとしても、縦の並びに簡単に変えることができます
データを縦の並びに変えておけば、様々な切り口でデータ集計がおこなえるようになりますので、ぜひ有効活用していきましょう！
次回はセルの中のデータを複数列に分解する方法と今回解説したピボット解除を組み合わせた変換処理を行います
にほんブログ村
