今回は「列のピボット解除」というコマンドの使い方を紹介していこう。このコマンドは、「クロス集計表」を「リスト形式の表」に変換するときに活用できる。「行と列の入れ替え」に比べると、少しだけ考え方が難しくなるが、データ表を最適な形に整える際に必須となるコマンドなので、この機会に使い方を覚えておくとよい。
「列のピボット解除」の使い方
前回の連載でデータ表の「行と列を入れ替える方法」を紹介したが、表全体ではなく、表の一部分についてのみ構成を変更したいケースもあるだろう。このような場合に活用できるのが「列のピボット解除」だ。
ピボット(pivot)とは、直訳すると「旋回軸」や「中心」、「方向転換」という意味の単語になる。Excelにおいては“データの配置転換”という意味合いでピボットという用語が使用されている。
言葉で説明しても理解しにくいと思うので、実例を示しながら詳しく解説していこう。以下の図は、あるレストラン・チェーンにおける日々の売上を店舗別に集計した表だ。見方によっては“行”と“列”の両方に見出しがある「クロス集計表」と捉えることもできるだろう。
このデータ表を「Power Query エディター」に取得し、各列のデータ型を指定すると、以下の図のようになる。
このままの状態でデータ分析を進めていくことも可能であるが、より柔軟にデータを扱えるように「リスト形式の表」(テーブル形式の表)に変換したいケースもあるだろう。ここでは、各店舗を「列名」ではなく「データ」として扱う場合を例に、その手順を紹介していこう。まずは、「新宿店」~「渋谷店」の列をまとめて選択する。
続いて、「変換」タブにある「列のピボット解除」の▼をクリックし、「列のピボット解除」を指定する。
すると、以前は列名だった「新宿店」、「池袋店」、「渋谷店」がデータとして扱われるようになり、それぞれの「売上金額」を1列に並べた形にデータ表が整形される。
あとは、「属性」や「値」と記されている列名を適当な名前に変更するだけ。これで「クロス集計表」を「リスト形式の表」に変換できる。
このように、“複数の列”に区分されていたデータを「属性」と「値」の2列に再構成する作業が「列のピボット解除」となる。言葉で説明すると複雑に感じるが、先ほどの例をよく見れば、その仕組みを理解できるはずだ。
「クロス集計表」は、その名が示すように“集計結果”をわかりやすく示したい場合に適した表形式となる。対して「リスト形式の表」は、データをさまざまな角度から分析するときに適した表形式となる。
データベースに慣れていない方は、「クロス集計表のほうが見やすい」と感じるかもしれない。しかし、この表には欠点がある。先ほど示した例の場合、「売上金額」の数値が3列にまたがって配置されていたため、全店舗の「売上金額」を集計するには3つの列の合計を求める必要がある。同様に、「売上金額」のトップ10を求める場合なども3つの列を対象に処理を進めていかなければならない。
一方、「リスト形式の表」では「売上金額」が1つの列に集約されているため、全店舗を対象にした「売上金額」の合計を算出したり、トップ10を求めたりする作業などを容易に行える。「店舗」の列にフィルターを適用することで、店舗別にデータを分析していくことも可能だ。
このように「さまざまな角度からデータを分析する」という点においては、「リスト形式の表」のほうが何かと都合がよい。パワークエリは“データベース用のツール”として設計されているため、そのデータ表も「リスト形式の表」にしておいた方がスムーズに作業を進められることが多い。念のため、頭に入れておくとよいだろう。
「その他の列のピボット解除」の使い方
「列のピボット解除」に話を戻して解説を進めていこう。このコマンドには「その他の列のピボット解除」という選択肢も用意されている。こちらは“選択した列”以外を対象にピボット解除する機能となる。
たとえば、「日付」の列を選択した状態で「列のピボット解除」→「その他の列のピボット解除」を実行すると……、
「日付」以外の列、すなわち「新宿店」~「渋谷店」の列をピボット解除することができる。つまり、先ほど紹介した例と同じ結果になる。
列数が多くて“列の選択”に手間取るときは、この操作方法でピボット解除を実行するとよいだろう。
「選択した列のみをピボット解除」とは?
そのほか、「列のピボット解除」には「選択した列のみをピボット解除」という選択肢も用意されている。
こちらは、“選択した列”を対象にピボット解除する機能となる。最初に紹介した例と同じ処理に見えるが、いったい何が違うのだろうか? 実際に試してみよう。
「新宿店」~「渋谷店」の列を選択し、「列のピボット解除」→「選択した列のみをピボット解除」を実行する。
選択した列が「属性」と「値」の2列に再構成される。ここまでの処理結果は、最初に示した例と同じだ。
両者に違いが表れるのは、加工したデータ表をExcelに出力した後だ。列名を「店舗」と「売上金額」に変更してからExcelに出力する。続いて、各列に適当な表示形式を指定すると、以下の図のような結果が得られる。
ここでデータの取得元となるワークシートに「横浜店」の列を追加したとしよう。
その後、「すべて更新」をクリックすると、「横浜店」のデータを追加した状態でクエリが再実行される。その処理結果は以下の図のとおり。ピボット解除されているのは「新宿店」、「池袋店」、「渋谷店」の3列だけで、「横浜店」は各データを3行ずつ複製した形で“列”のまま残っている。
一方、「列のピボット解除」→「列のピボット解除」を選択して処理を指定した場合は、「横浜店」の列もピボット解除の対象になる。
つまり、両者に差は、取得元のデータ表に“列の増加”があった場合に生じることになる。ポイントとなるのは“選択した列のみ”というワードだ。
「選択した列のみをピボット解除」を指定した場合は、「新宿店」、「池袋店」、「渋谷店」の3列だけをピボット解除することになる。他の列は、ピボット解除の対象にならない。一方、「列のピボット解除」を選択した場合は、“新しく追加した列”もピボット解除の対象になる。
些細な事ではあるが、このような違いがあることも覚えておくと役に立つだろう。なお、列を追加する予定がないのであれば、どちらを選択しても結果は同じなので、特に気にする必要はない。
「合計」などの列が含まれる場合は?
最後に、取得元のデータ表に「合計」などの列が含まれていた場合について補足しておこう。ここでは、以下の図に示したデータ表を例に対処方法を紹介していく。
このワークシートを「Power Query エディター」に取得し、「新宿店」~「渋谷店」の列を選択した状態で「列のピボット解除」を実行する。
すると、以下の図のような結果が得られる。「合計」の列はピボット解除の対象にしていないため、各データを3行ずつ複製した形で“列”のまま残ることになる。
このデータ表の各行を“1件のレコード”として見た場合、「合計」の値は特に意味をなさない数値になってしまう。このままでは混乱の原因になるので、「合計」の列は削除しておくのが基本だ。
もしくは、「合計」の列までピボット解除してしまう、という考え方もある。この場合は「日付」の列を選択し、「その他の列のピボット解除」を実行すればよい。
すると、以下の図のような結果を得ることができる。この場合、各行(各レコード)の意味を何とか理解することが可能だ。
ただし、決して好ましいデータ表とはいえない。というのも、「売上金額」(値)の列を合計すると、その数値は“本当の合計の2倍”になってしまうからだ。これではトラブルの原因になりかねない。よって、特に理由がない限り、このような形式のデータ表は作成しないのが基本だ。
パワークエリでデータを処理するときは、「合計」などの行を含まない“素のデータ”だけを扱うのが基本である。よって、最初の時点で「合計」の列を削除しておくのが最も基本的な対処方法といえる。目的に応じて状況は変化するが、“基本的な考え方”として覚えておく必要があるだろう。