せっかくなので、Twitterであった問題についてやってみましょう。
とは言え、ただ解決するだけではもったいないので、今回のデータを題材に「列をピボット」と「行列の入れ替え」の違いについて見ていきたいと思います。
今回やりたいこと
まずはこの記事でやりたいことです。
こんな表があったとします。
で、この表を加工しようにもどうにも加工しにくい。原因は項目が縦に展開されているにもかかわらず、3列目より向こうが一意の値しか持っていないことです。
つまり、このようなデータベース形式にしてあると、他の同じデータとも結合して分析しやすいわけです。
なので今回作りたい表はこんな感じ。
ちょっと見にくいですが、項目列と残高列を横展開し、3列目以降はそのままとするような表を作ってみたいと思います。
実際の操作方法
データの取得
まずはデータの取得です。データタブ「データの取得と変換」メニューから取得します。
エクセルファイルであれば「テーブルまたは範囲から」を選択します。
するとこうなり、準備は完了です。
列のピボット
パワークエリエディターより、「列のピボット」を選択します。
ピボット対象の列が「項目」となっていることを確認し、値列には「残高」列を、値の集計関数は「合計」を選択します。
ちなみに初期設定では自動的に集計関数は合計が選ばれるので、値の集計関数は不要かと言われれば不要と答えます。
そうすればこんな形に、項目列が横に展開されました。
並び替え
今の状態ですと並びが「流動性預金、・・・項目１、項目２、・・・」
となってますので、これを並び替えます。
ここでは流動性預金等の項目をテーブル末尾にもっていきたいと思います。
まず、流動性預金列他移動したい列を、並べたい順番で選択肢、変換タブの「移動」「末尾に移動」を選択します。
これでこのように並び替えもできました。
ここまでできれば、あとはExcelへの張り付け、データモデルへの追加など用途に合わせて閉じて完了です。
「列のピボット」まとめ
結局、パワークエリでやってたことはこんな感じです。
これを下の図のように変換し、行項目はすべて一意しかなかったので1行データになったというわけです。
ちなみにパワークエリのピボットでは、列と値を選択するだけで行は選択しない設計になっていること、また列項目も1列しか選べないので、その点が通常のピボットテーブルとは異なる点です。
ピボットテーブルで同じことが出来るのか
ここまではパワークエリ「列のピボット」を使ったのですが、通常のピボットテーブルでもできるのか、です。
だいたいご想像の通りですが、通常のピボットテーブルでもできます。
ピボットテーブルの各項目の選択はこんな感じ。
このように、列に「項目」列を、行に「流動性預金」列他を、値に「残高」（合計）を選択します。
後はピボットテーブル「デザイン」タブより小計・総計をしないを選択して完了です。
このようにピボットっテーブルでもできました。
ただしこの方法では、行項目と列項目を任意で並び替えることはできないので、その点自由にできるパワークエリとは違います。
以上で、どちらの方法でもできることが分かりました。
では次に、ピボットと似ているようで似ていない、行列入れ替えについても見ておきましょう。
行列入替とピボットの違い
ここまでピボットすればどうなるかと言うことを見てきたので、ここらで行列入替と何が違うかについて見ておきましょう。
行列入替は読んで字のごとく行と列を入れ替えるだけです。
今回の表を入れ替えるとこんな感じです。
タテとヨコが入れ替わってるのが分かると思います。
一方でピボットテーブルは項目を横展開すると1行だけになってましたね。
データベースに対して、いろんな切り口で集計するのがピボット、ただ見た目を変換するのが行列の入れ替え、と整理することができます。
このように、ピボットもしくはピボット解除を使うと簡単に行列入替ができたりしそうなものですけど、実際は両者違う変換方法だということです。
最後に
パワークエリやエクセルのピボット変換を使えばこんなことができるんだよということを、さらには行列入替との違いについて書きました。
これもどちらも良く使う機能ですので、ぜひともマスターしていただきたい機能の一つです。
今回も、最後までお読みいただきありがとうございました。
