「列のピボット」を使ったクロス集計表の作成

• 

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

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

前回の連載では、“複数の列”に配置されているデータを「属性」と「値」の2列に再構成する「列のピボット解除」の使い方を紹介した。この逆の処理を行うコマンドが「列のピボット」となる。このコマンドを使うと、「リスト形式の表」から「クロス集計表」を作成することが可能となる。その操作手順と注意点などを紹介していこう。

以下の図は、あるレストラン・チェーンにおける日々の売上を店舗ごとに記録した記録したデータ表だ。「リスト形式の表」になっているが、内容的には“前回の連載で紹介した例”と同じデータになる。

• 

  取得元のデータ表

このデータ表を「Power Query エディター」に取得すると、以下の図のようになる。このデータ表を「クロス集計表」に変換する場合を例に操作手順を解説していこう。

• 

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

まずは“区分用のデータ”が記録されている列を選択する。今回の例の場合、「店舗」の列を選択すればよい。続いて、「変換」タブにある「列のピボット」をクリックする。

• 

  区分用の列を選択し、「列のピボット」をクリック

このような設定画面が表示されるので、“複数の列”に配置転換するデータ（列）を選択する。今回の例の場合、「売上金額」の列を選択して「OK」ボタンをクリックすればよい。

• 

  “各列の値”として配置転換する列の指定

「店舗」の列にあったデータが“列名”になり、それぞれの「日付」に対応する「売上金額」を配置転換したデータ表が作成される。

• 

  クロス集計に変換されたデータ表

以上で「クロス集計表」の作成は完了だ。前回の連載と見比べながら処理内容を追っていくと、「列のピボット」と「列のピボット解除」がそれぞれ逆の処理を行うコマンドであることを理解できるだろう。

少し複雑な「列のピボット」

続いては、もう少し複雑なデータ表から「クロス集計表」を作成するときの操作手順を紹介していこう。以下の図は、あるハンバーガー店の日々の売上データを「提供方法」や「分類」に分けて記録したものだ。

この場合、“区分”となる列は「提供方法」と「分類」の2つ、“値”となる列も「数量」と「売上」の2つが存在することになる。

• 

  リスト形式のデータ表

このように“区分”や“値”となる列が複数ある場合は、不要な列を削除してから「列のピボット」を実行するのが基本だ。ここでは「提供方法」で区分した「売上」のクロス集計表を作成してみよう。

まずは、不要な列を削除する。今回の例では「分類」と「数量」の列を使用しないので、これらの列を削除する。

• 

  不要な列の削除

続いて、“区分用のデータ”が記録されている「提供方法」の列を選択し、「変換」タブにある「列のピボット」をクリックする。

• 

  区分用の列を選択し、「列のピボット」をクリック

あとは、配置転換するデータ（列）に「売上」を指定し、「OK」ボタンをクリックするだけ。

• 

  “各列の値”として配置転換する列の指定

これで「提供方法」で区分した「売上」のクロス集計表を作成できる。

• 

  クロス集計に変換されたデータ表

もうひとつ例を紹介しておこう。今度は「分類」で区分した「数量」のクロス集計表を作成してみよう。この場合は「提供方法」と「売上」の列が不要になるので、これらの列を削除する。

• 

  不要な列の削除

不要な列を削除できたら「分類」の列を選択し、「変換」タブにある「列のピボット」をクリックする。配置転換するデータ（列）には「数量」を指定すればよい。

• 

  区分用の列を選択し、「列のピボット」をクリック

• 

  “各列の値”として配置転換する列の指定

上記のように操作を進めていくと、「分類」で区分した「数量」のクロス集計表を作成できる。これで各分類の商品が「いくつ売れているか？」を確認できるようになる。

• 

  クロス集計に変換されたデータ表

このように「クロス集計表」を作成するときは、不要な列を削除してから「列のピボット」を実行するのが基本となる。

参考までに、不要な列を残した状態のまま「列のピボット」を実行した例も紹介しておこう。以下の図は、「提供方法」の列で区分し、値に「売上」を指定して「列のピボット」を実行した例だ。

• 

  不要な列を削除しなかった場合

「分類」と「数量」の列は削除していないため、これらの列は変換後のデータ表にもそのまま残ることになる。その結果、各日のデータは変換前と同じ「7行ずつ」になり、期待していたようなクロス集計表にはなってくれない。

また、「店内飲食」と「テイクアウト」の列には、noneのデータが随所に配置されるようになる。これは「数量」の列が残っていることが原因だ。3月1日の「サイドメニュー」を例に見ていこう（上図の2～3行目）。ここには、以下に示した4個の数値データが記録されている。

・店内飲食 …………… 数量：54、売上：24,330
・テイクアウト ……… 数量：13、売上：6,530

一方、これらのデータを格納するセルは、2行×3列の6個もある。“セルの数”に対して“データの数”が足りないため、“該当データなし”の部分がnullになる、という理屈だ。少し複雑ではあるが、順を追って確認していけば、一部のセルがnullになってしまう理屈を理解できるだろう。

なお、このようなデータ表は数値を読み取りづらくなるだけで、作成する意味が見当たらない。何度も述べているように、クロス集計表を作成するときは、不要な列を削除してから「列のピボット」を実行するのが基本、と覚えておこう。

「列のピボット」の詳細設定オプション

続いては、「列のピボット」の詳細設定オプションについて解説していこう。通常、「列のピボット」は、同じ区分になるデータ（値）を「合計」して配置転換する仕組みになっている。この集計方法を「平均」や「最大値」などに変更できる機能が詳細設定オプションとなる。

具体的な例で見ていこう。以下の図は、「分類」の列で区分して「列のピボット」を実行する場合の例だ。不要な列は、あらかじめ削除してある。

• 

  「列のピボット」の実行

「列のピボット」の設定画面が表示されたら、値に「数量」を指定する。続いて、「詳細設定オプション」をクリックすると、集計方法を変更できるようになる。ここでは例として、「平均」を選択した場合の処理結果を見ていこう。

• 

  詳細設定オプションで指定できる集計方法

「OK」ボタンをクリックすると、以下の図のような「クロス集計表」が作成される。各セルには、それぞれの区分に該当するデータを「平均」した数値が表示される。

• 

  変換されたデータ表

このように集計方法を「平均」などに変更した「クロス集計表」を作成することも可能である。ただし、「何を平均しているのか？」をよく見極める必要がある。変換前のデータ表と見比べながら検証していこう。

• 

  「列のピボット」を実行する前のデータ表

3月1日の「バーガー類」の数量は、「店内飲食」が100個、「テイクアウト」が46個となっていた。これを合計して2で割ると、（100＋46）÷2＝73になる。この数値が「平均」として「クロス集計表」に表示されている訳だ。

とはいえ、「店内飲食」と「テイクアウト」の数量を平均することに何の意味があるだろうか？　おそらく、適切な理由を説明できないだろう。よって、今回の例では意味（意図）を見いだせないクロス集計表を作成したことになる。

平均を求めるのであれば、「1日あたり“バーガー類”は何個売れているか？」を調べる、といった分析を行うのが一般的であろう。この場合、「列のピボット」で平均を求めるのではなく、普通に“各日付の合計”を求めてから平均値を算出する必要がある。

以下の図は、「分類」の列で区分し、値に「数量」を指定したクロス集計表をExcelに出力した例だ。「列のピボット」の詳細設定オプションは指定していないため、同じ区分になるデータ（値）を「合計」した値が各セルに表示されている。

• 

  「分類」の列で区分したクロス集計表を作成

以降の作業は、パワークエリではなく、Excelで進めていく。“1日あたりの平均”を算出するときは、「テーブル デザイン」タブにある「集計行」をONにし、テーブルの下に集計行を追加すればよい。続いて、集計行の各セルをクリックし、集計方法に「平均」を選択する。

• 

  平均の算出

同様の操作を各列で行い、小数点以下を1桁まで表示するように指定すると、以下の図のような結果が得られる。この結果を見ると、「1日あたり“バーガー類”は平均121.1個売れている」ということを確認できる。

• 

  1日あたりの平均値

このように、各データ（各行）を集計するときは、Excelに出力してから作業を進めていくのが基本だ。パワークエリで強引に“各行の平均”を求める方法もなくはないが、それよりも普通にExcelで作業したほうが効率的である。

テーブルの「集計行」を利用するか、もしくは「構造化参照」を使って数式や関数を入力していれば、「すべて更新」にも対応できる。これについては第4回の連載で詳しく解説しているので、よく分からない方は、この機会に一読しておくとよいだろう。