「グループ化」で平均を求めるときの注意点

「グループ化」で平均したときの失敗例

「グループ化」コマンドを使って、各データの「平均」を集計したい場合もあるだろう。このとき、「集計方法に“平均”を選択すればよい」と安易に考えてしまうと、的外れな集計結果を算出してしまう恐れがある。ということで、今回は「平均」を求めるときの注意点を紹介していこう。

• 

  「グループ化」で正しく平均する方法

今回も、前回と同じデータ表を使って具体的な例を紹介していく。以下の図は、あるハンバーガー店の売上データを「Power Query エディター」に取得したものだ。それぞれのデータは、各日に販売した「数量」と「売上」が提供方法（店内飲食／テイクアウト）や分類（バーガー類／サイドメニューなど）に分けて記録されている。

• 

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

このデータ表をもとに「分類」で区分した「数量」の平均を求めてみよう。「変換」タブにある「グループ化」をクリックし、以下の図のように設定して「OK」ボタンをクリックする。

• 

  「グループ化」の設定内容

すると、「分類」で区分した「数量」の平均を求めることができる。この結果を見ると、“バーガー類”は平均で60.2個、“サイドメニュー”は平均で28.5個、……という結果が得られたことになる。

• 

  自動集計された平均

では、これらの「平均」は何を示しているのだろうか？　「1日あたりの販売数」と考える方もいるかもしれないが、それは大きな間違いだ。もういちどデータをよく見てみよう。

以下の図は、グループ化する前のデータ表をExcelに出力し、“バーガー類”のデータだけをフィルターで抽出したものだ。「数量」のデータは1日に2件ずつ、全部で10件ある。そして、これら10件のデータを平均すると60.2個という値になる。この数値は、先ほどの集計結果と一致している。

• 

  テーブルの「集計行」で平均を求めた場合

とはいえ、10件のデータを合計して10で割る、というのは何か違う気がしないだろうか？　売上データは5日分しかないので、“各日の数量”を合計して5で割る、というのが正しい平均の算出方法になるはずだ。上図に示した例の場合、「店内飲食」と「テイクアウト」について「数量」の平均を求めていることになり、あまり意味のない集計結果になってしまう。

求めたい数値が「1日あたり平均で何個売れているか？」であった場合、最初に“各日の数量”を算出して、それら5件のデータを平均しなければならないはずだ。第31回の連載でも似たような話をしているが、「平均」を求めるときは、その結果が「何を平均したものなのか？」をよく確認しておく必要がある。

1日当たりの平均を「グループ化」で求めるには？

では、「分類」で区分した「1日あたりの数量」を求めたいときは、どのように処理すればよいのだろうか。「グループ化」を使って処理する手順を紹介していこう。

まずは、「日付」→「分類」で区分した「各日の数量」を求める。この処理にも「グループ化」コマンドを利用する。「グループ化」をクリックして「詳細設定」を選択する。続いて、区分用の列に「日付」を指定し、「グループの追加」ボタンをクリックする。

• 

  「グループ化」の設定内容（1）

区分用の列を追加できるようになるので、ここに「分類」の列を指定する。これで「日付」→「分類」という区分を指定できたことになる。

• 

  「グループ化」の設定内容（2）

次は、集計方法の指定だ。「各日の数量」は、「数量」の列を「合計」すると求められる。なお、新しい列名には「数量の合計」という名前を指定した。

• 

  「グループ化」の設定内容（3）

「OK」ボタンをクリックすると、以下の図のような集計表が作成される。「日付」→「分類」で区分して、それぞれの「数量」を合計した表が作成されているのを確認できるだろう。この数値が「各日の数量」となる。

• 

  自動集計された「数量」の合計

念のため、グループ化する前のデータ表も掲載しておこう。4月1日の“バーガー類”は、“店内飲食”が75個、“テイクアウト”が34個という値になっている。つまり、4月1日に販売された“バーガー類”の合計は75＋34＝109となる。

• 

  「グループ化」する前のデータ表

これと同じ数値が、先ほどの集計表に表示されている。“サイトメニュー”などの他の区分も同様だ。

これで準備作業は完了。この集計結果をもとに、1日あたりの「数量の平均」を求めていこう。こちらの処理にも「グループ化」を使用する。

• 

  もういちど「グループ化」を実行

今度は区分を階層化しなくてもよいので、設定方法は「基本」のままでよい。区分用の列には「分類」を指定する。

• 

  「区分用の列」の指定

続いて、「数量の合計」の列を「平均」するように集計方法を指定する。なお、新しい列名には「1日あたりの数量」という名前を指定した。

• 

  集計方法の指定

「OK」ボタンをクリックすると、以下の図のような集計表が作成される。これが「分類」で区分した「1日あたりの数量」となる。

• 

  自動集計された平均（1日あたり）

このように「平均」を求める集計表を作成する際に、「グループ化」を2回実行しなければならないケースもある。1回目の「グループ化」は、数値データを区分別に合計する処理（準備作業）。そして、2回目の「グループ化」で平均を算出する、という流れになる。

少し複雑ではあるが、順を追って考えていけば、その理屈を理解できるはずだ。こういったことを考慮せずに、単純に「平均」で自動集計してしまうと、間違った集計結果になってしまう危険性がある。注意しておこう。

集計する項目の追加

参考までに、先ほどの集計表に「売上の平均」を追加するときの操作手順も紹介しておこう。この場合は、それぞれの「グループ化」に集計方法を追加してあげる必要がある。

まずは、1回目の「グループ化」（準備作業）のステップにある「歯車」のアイコンをクリックする。

• 

  「グループ化」の設定画面の呼び出し

「グループ化」の設定画面が表示されるので、「集計の追加」ボタンをクリックする。

• 

  「集計の追加」をクリック

あとは、集計方法に「売上」の「合計」を指定するだけ。こちらの列名には、「売上の合計」という名前を指定した。

• 

  追加した集計方法

「OK」ボタンをクリックすると、準備段階の集計表に「売上の合計」の列が追加されるのを確認できる。

• 

  自動集計された「数量」と「売上」の合計

続いては、それぞれの数値を平均する「グループ化」の設定を変更していこう。2回目の「グループ化」のステップにある「歯車」のアイコンをクリックする。

• 

  「グループ化」の設定画面の呼び出し

「グループ化」の設定画面が表示されるので、設定方法を「詳細設定」に変更し、「集計の追加」ボタンをクリックする。続いて、「売上の合計」を「平均」するように集計方法を指定する。こちらの列名には「1日あたりの売上」という名前を指定した。

• 

  追加した集計方法

「OK」ボタンをクリックすると、集計表に「1日あたりの売上」の列が追加される。これで「分類」で区分した1日あたりの「数量」と「売上」の平均を求めることができる。「閉じて読み込む」をクリックして、この集計表をExcelに出力すると……、

• 

  自動集計された平均（1日あたり）

以下の図のような結果を得られる。これなら意味のある集計表といえるだろう。たとえば、“バーガー類”は1日あたり120.4個売れており、その平均売上は106,308円になる、と分析できる。同様に、“サイドメニュー”は1日あたり57個売れており、その平均売上は26,132円という数値になる。

• 

  Excelに出力したデータ表

前回と今回の連載で紹介した「グループ化」は、手軽に使える便利な機能といえるが、その計算が「どのように行われているか？」には気を配っておく必要がある。特に「平均」を求める場合は注意が必要だ。

間違った結果が算出されていることに気付かないまま分析作業を進めると、大きなトラブルに発展してしまう恐れもある。そういったミスを犯さないためにも、この連載が参考になれば幸いである。