こちら でもさらりと書いているのですが、Power Query エディターを使って左端と上部に見出しのある表をテーブルに変換するときに使う、列のピボット解除についてだけ書きます。
やりたいことは、A の表を B にしたい、です。
このようなテーブルにするために Power Query エディターの [列のピボット解除] を使うのですが、3 つある選択肢のどれを使うとどうなるかをお見せしようと思っています。
列のピボット解除とは
ピボット解除とは、列の見出し部分を「属性」、該当するデータ部分を「値」にした組み合わせを作成する処理です。
この処理をすることで、横方向に並んでいるデータを縦方向に配置できるため、縦横の組み合わせで値を特定している表をテーブルの形に整えることができ、データ集計や分析に適した状態に変換できます。
[商品名] 列が、「商品A」のみの表で考えてみます。
この表では、[プラン1]、[プラン2]、[プラン3] の部分を「属性」と「値」とすることでテーブルにします。
さらに、[プラン4] 以降の列が増えたときにも「属性」と「値」にしたいと考えています。 ←これ大事
現在の 3 つのプランの列、および今後増える (かもしれない)、[商品名] 列以外のすべてを解除したいため、[商品名] 列を選択して [その他の列のピボット解除] を実行すると、
該当する列の見出しが [属性] 列に、数値が [値] 列に配置されます。
3 つの属性があるので、[商品名] 列には「商品A」が 3 つ用意されて 3 行になります。
いま選択している列以外全部！ということで、[その他の列のピボット解除] がわかりやすいですね。
もしも、こんな風に↓ [プラン4] が追加された場合は、
[プラン4] も [商品名] 列以外の列であるため、更新するだけで「属性」と「値」に展開されます。
そして、複数の商品がある場合のイメージはこちら。
ピボット解除の種類
3つの種類の違いを見てみましょう。
[列のピボット解除]
説明によると、「現在、非選択の列以外すべての列を属性 / 値のペアに変換します。」とあります。
ややこしいけれど、選択しているすべての列と、今後増えるかもしれない列をピボット解除する、ということです。
下図の場合、 [プラン1] 列と、[プラン2] 列と、[プラン3] 列を選択して [列のピボット解除] を実行すると下の表の形になり、 [プラン4] が追加されても、処理を行った段階での非選択列は [商品名] 列であることに変わりはないので [プラン4] のデータも「属性」と「値」に変換されます。
[商品名] 列を選択して [その他の列のピボット解除] を実行するときとの違いは、どの列を選択して操作しているのか、ということです。ピボット解除したい列がたくさんあるかどうかで使い分ければよいでしょう。
列が追加された場合
[その他の列のピボット解除]
最初に紹介済みですが念のため。[その他の列のピボット解除] は、「現在選択されている列以外のすべての列を属性 / 値のペアに変換します。」ということで、ピボット解除しない列を選択して処理することで、選択していないすべての列と、今後増えるかもしれない列をピボット解除します。
列が追加された場合
[選択した列のみをピボット解除]
「現在選択されている列のみ、属性 / 値のペアに変換します。」とあるので、いま選択している列のみをピボット解除します。今後追加されるデータはピボット解除されません。たとえば、 [プラン4] 列が追加されても、ピボット解除されないという点に注意しなければいけません。
列が追加された場合
縦横交差の表をテーブルにする
[その他の列のピボット解除] を行うときの実際の操作ステップをご紹介します。
ここでは、表をテーブルに変換して [data] という名前をつけてある縦横交差の表を使います。
事前にテーブルに変換していない場合は、手順1. を実行したときにテーブルに変換されます。
-
Power Query エディターでピボット解除をしてテーブル (縦長の表) に変換したい場所を選択して、リボンの [データ] タブの [データの取得と変換] グループの [テーブルまたは範囲から] をクリックします。
テーブルに変換していないセル範囲を対象とする場合は、表全体を選択して実行してください。
-
Power Query エディターが起動します。
ピボット解除しない列 ([商品名]) を選択して、リボンの [変換] タブの [任意の列] グループの [その他の列のピボット解除] をクリックします。
列は、列名をクリックして選択できます。
-
選択している列以外の列のデータが、[属性] 列と [値] 列に展開されます。
-
列の名前は、列名の部分をダブル クリックして変更できます。
下図では、[属性] を [プラン] に、[値] を [金額] に変更しています。
-
ここでは、テーブルを配置する場所を選びたい (元の表と同じワークシートに配置したい) ので、Power Query エディターのリボンの [ホーム] タブの [閉じる] グループの [閉じて読み込む] をクリックし、[閉じて次に読み込む] をクリックします。
元の表とは異なるワークシートにテーブルを配置したい場合は、[閉じて読み込む] をクリックしてください。
-
Power Query エディターが閉じます。
[データのインポート] ダイアログ ボックスで [テーブル] を選択して [既存のワークシート] を選び、配置する位置のセルをクリックして指定して、[OK] をクリックします。
-
Power Query エディターでピボット解除を行った結果が、テーブルとして配置されます。
-
出力されたテーブルのスタイル (書式) は変更可能です。
ここでは、緑はやめて、見出しが黒いシンプルなスタイルに変更しました。
-
ここでは、元の表に [プラン4] の列と「商品E」の行を追加して更新を実行しています。
[プラン4] のデータもピボット解除されており、「商品E」も含まれていることが確認できます。
実務で扱うデータはここで紹介しているサンプルよりもたくさんの列とレコードがあると思いますが、まずは小さな表を用意して試してみてください。(更新もね)
石田 かのこ
