計算・統計の使い方
今回のテーマは「計算・統計の使い方とそれに関連したステップの過去参照」です。
目標として以下のようなテーブルがあった時、
① 商品ごとの売上を計算し、
② 全商品の売上合計に対するそれぞれの商品の占める割合を計算します。
Before
| 商品 | 単価 | 数量 |
|---|---|---|
| 商品Ａ | 500 | 52 |
| 商品Ｂ | 800 | 196 |
| 商品Ｃ | 600 | 435 |
| 商品Ｄ | 1000 | 227 |
| 商品Ｅ | 700 | 150 |
After
| 商品 | 単価 | 数量 | 売上 | 割合 |
|---|---|---|---|---|
| 商品Ａ | 500 | 52 | 26000 | 0.033513792 |
| 商品Ｂ | 800 | 196 | 156800 | 0.202113947 |
| 商品Ｃ | 600 | 435 | 261000 | 0.336426914 |
| 商品Ｄ | 1000 | 227 | 227000 | 0.292601186 |
| 商品Ｅ | 700 | 150 | 105000 | 0.135344161 |
実践
1. テーブルの読み込みと各商品の売上の計算
まず、テーブルの上にカーソルを置き、データメニュー→テーブルまたは範囲からでPower Query Editorを開きます。
次に、列の追加メニューに移動し、Ctrlキーを押しながら単価、数量列をクリックします。
数値→標準→乗算を選択します。
これで単価×数量＝売上が計算されました。
なお、Ctrlキーを押しながら選択した項目の順番がそのまま計算のテーブルが順番になります。今回は乗算なので順番が違っていても影響ありませんが、減算や除算などは要注意です。
ここで少し寄り道をしてカスタム列を追加して以下の計算式で売上を計算してみます。
先ほど作成した乗算とカスタム列で作成した売上がほぼ同じ結果で並んでいます。
違うところと言えば、以下２点です。
① 列名： 「乗算」vs 「売上」
② データ型： 整数型 vs すべて型
その違いの出所を知るために、右側の適用したステップの中から挿入された乗算と追加されたカスタムの数式をそれぞれ比較します。
挿入された乗算
= Table.AddColumn(変更された型, "乗算", each [単価] * [数量], Int64.Type)
追加されたカスタム
= Table.AddColumn(挿入された乗算, "売上", each [単価] * [数量])
どちらも最初にTable.AddColumnという関数を使用しています。次の「変更された型」と「挿入された乗算」は直前のステップを表しているので、ここも無視します。
次に、列名を示す"乗算"と"売上"が異なります。
次に、乗算の方は最後に、Int64.Typeというデータ型を示す言葉があるのに対し、「売上」にはそれがありません。
【まとめ】
標準→乗算で追加：列名は設定できないが、データ型は正しい（整数）
カスタム列で追加：列名は設定できるが、データ型はすべて型
ここまで分かったので、処理をシンプルするために少し工夫します。最後の追加されたカスタムステップを削除し、挿入された乗算ステップの数式を以下のように変更します。
挿入された乗算
= Table.AddColumn(変更された型, "売上", each [単価] * [数量], Int64.Type)
これで上手くいきました。最初にマウスクリックで数式のひな形を作成し、そのあとで数式を一部書き換えることで、効率的でシンプルなクエリを作成することができます。
2. 個々の商品の売上の占める割合を出す
次に、それぞれ売上の占める割合を出すために全商品の売上の合計を出します。そのためには、
① 全商品の売上合計を出し、
② それぞれの商品の売上を全商品の売上合計で割る
という２段階の作業が必要です。
① 全商品の売上合計を出す
変換メニューに移動し（列の追加メニューではありません）、売上列を選択したあとに統計→合計を選択してください。
すると、画面がガラッと変わり、今まで縦横にテーブルが広がっていたのが、数値が一つしか表示されなくなりました。
数字としては売上の合計のようですが、一つの値しか表示されなくなりました。このままでは割合どころの話ではないので元に戻します。
今回は、最後のステップを削除する代わりに、数式バーのfxをクリックして、2つ前のステップ名である挿入された乗算と入力します。
すると、プレビューが二つ前のステップに戻りました。
今度は同じ要領で列の追加メニューでカスタム列を開き、以下のように計算された合計の値を追加します。
すると、各行に先ほど統計→合計で計算した値が並びました。
数式を見ると、eachの後に先ほど統計→合計で作成したステップである計算された合計があります。
= Table.AddColumn(カスタム1, "売上合計", each 計算された合計)
統計を使うときは、このようにいったん全行を一つの値にまとめたあと、前のステップに戻りその値を利用することができます。
② それぞれの商品の売上を全商品の売上合計で割る
それでは最後の仕上げとして、割合を計算します。列の追加メニューでCtrlキーを押しながら、売上、売上合計の順番にクリックし、標準→除算を実行します。
最後に仕上げとして、売上合計の削除、除算記号のリネームをして完成です。
ここまで来たら「閉じて読み込む」を実行してください。
これで出来上がりです。
