「列のピボット解除」でクロス集計表をリスト形式に変換

• 

  表の形式を変換、「列のピボット解除」の使い方

「列のピボット解除」の使い方

前回の連載でデータ表の「行と列を入れ替える方法」を紹介したが、表全体ではなく、表の一部分についてのみ構成を変更したいケースもあるだろう。このような場合に活用できるのが「列のピボット解除」だ。

ピボット（pivot）とは、直訳すると「旋回軸」や「中心」、「方向転換」という意味の単語になる。Excelにおいては“データの配置転換”という意味合いでピボットという用語が使用されている。

言葉で説明しても理解しにくいと思うので、実例を示しながら詳しく解説していこう。以下の図は、あるレストラン・チェーンにおける日々の売上を店舗別に集計した表だ。見方によっては“行”と“列”の両方に見出しがある「クロス集計表」と捉えることもできるだろう。

• 

  取得元のデータ表

このデータ表を「Power Query エディター」に取得し、各列のデータ型を指定すると、以下の図のようになる。

• 

  「Power Query エディター」に取得したデータ表

このままの状態でデータ分析を進めていくことも可能であるが、より柔軟にデータを扱えるように「リスト形式の表」（テーブル形式の表）に変換したいケースもあるだろう。ここでは、各店舗を「列名」ではなく「データ」として扱う場合を例に、その手順を紹介していこう。まずは、「新宿店」～「渋谷店」の列をまとめて選択する。

• 

  リスト形式に配置転換する列を選択

続いて、「変換」タブにある「列のピボット解除」の▼をクリックし、「列のピボット解除」を指定する。

• 

  「列のピボット解除」を実行

すると、以前は列名だった「新宿店」、「池袋店」、「渋谷店」がデータとして扱われるようになり、それぞれの「売上金額」を1列に並べた形にデータ表が整形される。

• 

  リスト形式に変換されたデータ表

あとは、「属性」や「値」と記されている列名を適当な名前に変更するだけ。これで「クロス集計表」を「リスト形式の表」に変換できる。

• 

  列名の変更

このように、“複数の列”に区分されていたデータを「属性」と「値」の2列に再構成する作業が「列のピボット解除」となる。言葉で説明すると複雑に感じるが、先ほどの例をよく見れば、その仕組みを理解できるはずだ。

「クロス集計表」は、その名が示すように“集計結果”をわかりやすく示したい場合に適した表形式となる。対して「リスト形式の表」は、データをさまざまな角度から分析するときに適した表形式となる。

データベースに慣れていない方は、「クロス集計表のほうが見やすい」と感じるかもしれない。しかし、この表には欠点がある。先ほど示した例の場合、「売上金額」の数値が3列にまたがって配置されていたため、全店舗の「売上金額」を集計するには3つの列の合計を求める必要がある。同様に、「売上金額」のトップ10を求める場合なども3つの列を対象に処理を進めていかなければならない。

一方、「リスト形式の表」では「売上金額」が1つの列に集約されているため、全店舗を対象にした「売上金額」の合計を算出したり、トップ10を求めたりする作業などを容易に行える。「店舗」の列にフィルターを適用することで、店舗別にデータを分析していくことも可能だ。

このように「さまざまな角度からデータを分析する」という点においては、「リスト形式の表」のほうが何かと都合がよい。パワークエリは“データベース用のツール”として設計されているため、そのデータ表も「リスト形式の表」にしておいた方がスムーズに作業を進められることが多い。念のため、頭に入れておくとよいだろう。

「その他の列のピボット解除」の使い方

「列のピボット解除」に話を戻して解説を進めていこう。このコマンドには「その他の列のピボット解除」という選択肢も用意されている。こちらは“選択した列”以外を対象にピボット解除する機能となる。

たとえば、「日付」の列を選択した状態で「列のピボット解除」→「その他の列のピボット解除」を実行すると……、

• 

  「その他の列のピボット解除」を実行

「日付」以外の列、すなわち「新宿店」～「渋谷店」の列をピボット解除することができる。つまり、先ほど紹介した例と同じ結果になる。

• 

  リスト形式に変換されたデータ表

列数が多くて“列の選択”に手間取るときは、この操作方法でピボット解除を実行するとよいだろう。

「選択した列のみをピボット解除」とは？

そのほか、「列のピボット解除」には「選択した列のみをピボット解除」という選択肢も用意されている。

• 

  「列のピボット解除」に用意されている選択肢

こちらは、“選択した列”を対象にピボット解除する機能となる。最初に紹介した例と同じ処理に見えるが、いったい何が違うのだろうか？　実際に試してみよう。

「新宿店」～「渋谷店」の列を選択し、「列のピボット解除」→「選択した列のみをピボット解除」を実行する。

• 

  「選択した列のみをピボット解除」を実行

選択した列が「属性」と「値」の2列に再構成される。ここまでの処理結果は、最初に示した例と同じだ。

• 

  リスト形式に変換されたデータ表

両者に違いが表れるのは、加工したデータ表をExcelに出力した後だ。列名を「店舗」と「売上金額」に変更してからExcelに出力する。続いて、各列に適当な表示形式を指定すると、以下の図のような結果が得られる。

• 

  Excelに出力したデータ表

ここでデータの取得元となるワークシートに「横浜店」の列を追加したとしよう。

• 

  取得元のデータ表に「横浜店」を追加

その後、「すべて更新」をクリックすると、「横浜店」のデータを追加した状態でクエリが再実行される。その処理結果は以下の図のとおり。ピボット解除されているのは「新宿店」、「池袋店」、「渋谷店」の3列だけで、「横浜店」は各データを3行ずつ複製した形で“列”のまま残っている。

• 

  「選択した列のみをピボット解除」を指定した場合

一方、「列のピボット解除」→「列のピボット解除」を選択して処理を指定した場合は、「横浜店」の列もピボット解除の対象になる。

• 

  「列のピボット解除」を指定した場合

つまり、両者に差は、取得元のデータ表に“列の増加”があった場合に生じることになる。ポイントとなるのは“選択した列のみ”というワードだ。

「選択した列のみをピボット解除」を指定した場合は、「新宿店」、「池袋店」、「渋谷店」の3列だけをピボット解除することになる。他の列は、ピボット解除の対象にならない。一方、「列のピボット解除」を選択した場合は、“新しく追加した列”もピボット解除の対象になる。

些細な事ではあるが、このような違いがあることも覚えておくと役に立つだろう。なお、列を追加する予定がないのであれば、どちらを選択しても結果は同じなので、特に気にする必要はない。

「合計」などの列が含まれる場合は？

最後に、取得元のデータ表に「合計」などの列が含まれていた場合について補足しておこう。ここでは、以下の図に示したデータ表を例に対処方法を紹介していく。

• 

  取得元のデータ表

このワークシートを「Power Query エディター」に取得し、「新宿店」～「渋谷店」の列を選択した状態で「列のピボット解除」を実行する。

• 

  「列のピボット解除」を実行

すると、以下の図のような結果が得られる。「合計」の列はピボット解除の対象にしていないため、各データを3行ずつ複製した形で“列”のまま残ることになる。

• 

  ピボット解除されたデータ表

このデータ表の各行を“1件のレコード”として見た場合、「合計」の値は特に意味をなさない数値になってしまう。このままでは混乱の原因になるので、「合計」の列は削除しておくのが基本だ。

• 

  不適切な列の削除

もしくは、「合計」の列までピボット解除してしまう、という考え方もある。この場合は「日付」の列を選択し、「その他の列のピボット解除」を実行すればよい。

• 

  「日付」以外の列をピボット解除

すると、以下の図のような結果を得ることができる。この場合、各行（各レコード）の意味を何とか理解することが可能だ。

• 

  ピボット解除されたデータ表

ただし、決して好ましいデータ表とはいえない。というのも、「売上金額」（値）の列を合計すると、その数値は“本当の合計の2倍”になってしまうからだ。これではトラブルの原因になりかねない。よって、特に理由がない限り、このような形式のデータ表は作成しないのが基本だ。

パワークエリでデータを処理するときは、「合計」などの行を含まない“素のデータ”だけを扱うのが基本である。よって、最初の時点で「合計」の列を削除しておくのが最も基本的な対処方法といえる。目的に応じて状況は変化するが、“基本的な考え方”として覚えておく必要があるだろう。