このページは解答編です。
↓ネ
↓タ
↓バ
↓レ
↓防
↓止
答えと解説
設問はこちらでした。
P-031: レシート明細データ（receipt.csv）に対し、店舗コード（store_cd）ごとに売上金額（amount）の標準偏差を計算し、降順で5件表示せよ。
※不偏標準偏差（与えられたデータが母集団の一部である標準偏差、n-1で割る）をまず出力しましょう。余裕があれば母集団の標準偏差（与えられたデータを集合全体とした標準偏差、nで割る）にもチャレンジしてください
解答ワークフローは以下のようになります。
標準偏差（Standard Deviation）について
今回は標準偏差を出す問題ですが、標準偏差について考えてみたいと思います。
標準偏差とは、分散と同様データのばらつきを表す指標です。標準偏差を２乗すると分散になります。
標準偏差を求めるための数式はこちらです。
標準偏差は、シグマの結果に対してn（レコード数）で割ったのちに平方根を取っています、不偏標準偏差の場合はn-1で割るところが異なります。
なお、母集団の標準偏差は今計算しているデータに対しての標準偏差ですが、不偏標準偏差は何かしら元となる集団、つまり母集団から一部抜き取ったデータ（サンプリングされたデータ）と考えた時の母集団の標準偏差となります。
ということで、今回のデータは特に母集団などがあるわけではないので、通常の標準偏差を取ればよい、ということになります（100本ノックの解答も通常の標準偏差となっています）。なお、母集団の標準偏差、不偏標準偏差についてはいろいろと言葉が統一されていないため、以下のように表に表してみましょう。
| 割る場合の分母 | Excelの関数 | 名称 |
|---|---|---|
| n | STDEVP, STDEVPA, STDEV.P | 母集団の標準偏差 |
| n-1 | STDEV, STEVA, STDEV.S | 不偏標準偏差 |
とはいえ、なかなかそこまでわかりにくいので、今回は両方の標準偏差を出してみましょう。
実際にワークフローを作っていきましょう
まず、型の変更からになります。今回は売上金額（amount）を計算していくので、数値型（Int16）にしておきます。不要な項目も削除します。
不偏標準偏差（n-1で割る）
不偏標準偏差（n-1で割る）はAlteryxでは簡単に計算することができます。集計ツールを使うだけです。
以下のような設定となります。store_cdでグループ化し、amountの「標準偏差」を選択します。
あとはamount（標準偏差の結果です）で降順にソートし、先頭から５件取得すれば完了です。
実際のワークフローは以下のようになります。
母集団の標準偏差（nで割る）
次に、母集団の標準偏差を出力してみましょう。なぜかAlteryxには母集団の標準偏差を一発で出す機能がありません。ちなみに、３０問目のワークフローを少し改造するだけで計算できます。これは、分散の平方根を取るだけだからです。
３０問目との差分で計算する
３０問目との違いは、最後の計算式で平方根を取るというところです。つまり、元の定義の式について、分散と標準偏差で比較してみましょう。
最後に平方根を取っているのが標準偏差です。つまり、以下の赤色の部分が差分です。
分散の最終導出式：
[Sum_xi-x]/[Count]標準偏差の最終導出式：
SQRT([Sum_xi-x]/[Count])ということで、以下の赤い部分のフォーミュラツールのみが差分となります。
設定は以下の通り。
定義から母集団の標準偏差を計算する
今度は、元の定義に対して１からワークフロー化したいと思います。
まず、平均を計算しましょう。集計ツールで各店舗コードごとにグループ化し、平均を取ります。
得られた平均値は、計算に用いるために元のデータに結合する必要があります。以下のように結合ツールで結合します。
結合ツールの設定は以下の通りです。キー列はstore_cdとし、不要な項目も削除します。
これで準備は整いましたので計算を進めていきましょう。
まず、定義の計算式のうち、赤い部分を計算していきましょう。
これは、各amountの値[amount]と平均値[Avg_amount]を引き算し、二乗すればよいです。二乗はPOW関数で可能です。つまり以下の計算式となります。
POW([amount]-[Avg_amount], 2)なお、POW関数の前側は計算をする値で、後ろの数字はべき乗の乗数を記載します。今回は二乗なので２となります。
フォーミュラツールには以下のようにセットします。フィールド名は「xi-x」、データ型はDoubleとしています。
さて、ここから以下の赤い部分、シグマを計算していきます。シグマは基本的に合計しろ、ということなので、今回の場合は1からNまで、つまり先ほど求めた「xi-x」のすべての合計を行うということなので、集計ツールで合計を求めるだけです。また、ここでnも求めておきましょう。nはレコード数なので集計ツールでカウントを取ればオッケーです。つまり、集計ツールは以下のように設定します。
ここまで求められれば、あとは単純な割り算を行い、平方根を計算します。
SQRT([Sum_xi-x]/[Count])フォーミュラツールは以下のように設定しています。
あとは、amountの昇順で並べ替えて先頭から５レコード抽出すれば完了です。実際のワークフローは以下の通りです。
まとめ
今回も、集計の問題でしたが、単なる集計に終わりませんでした。３０問目は分散でしたが、今回は標準偏差でした。こちらも同様の問題があり、Alteryxのデフォルト機能では不偏標準偏差（n-1で割る）しか計算できないため、母集団の標準偏差（nで割る）を計算したいときは、ワークフローをしっかり組む必要があります。
なお、Excelで考えると、STDEV.Pが母集団の標準偏差（nで割る）、STDEV.Sが不偏標準偏差（n-1で割る）です。
解答ワークフローダウンロード
※2022/09/08更新
コメント