ビッグデータ分析はとりあえずExcelでやれる(前編)の続きです。
前回は平均と分散、標準偏差、正規分布というキーワードを学びました。
そして平均は一種類の値に対する分析にしか過ぎないと締めくくっています。
今回は値が二種類ある場合の統計分析を行います。
前回の標準偏差と正規分布の理解が浅いと身に着けることが出来ないです。
なお、後編は数式の説明はしません。
前回以上に考え方とExcelの使い方だけ身に着けることを目的にします。
一次回帰分析
今回は以下の例で説明します。
とあるWebシステムのアクセス数とサーバのCPU使用率のデータです。
システムっぽくて例が解り難かったら、
同時アクセス数⇒ 店舗の一日の来場者数
サーバCPU使用率⇒店舗の一日の売上
と読み替えても構いません。なんでも良いんです。
| 同時アクセス数 | サーバCPU使用率[%] |
| 551 | 14 |
| 987 | 27 |
| 1428 | 33 |
| 1868 | 47 |
| 2025 | 42 |
| 2424 | 57 |
| 2560 | 51 |
| 2544 | 53 |
| 3117 | 60 |
同時アクセス数が増えるほどCPU使用率が増加していそうですね。
CPU使用率が100%になればこれ以上アクセスできない状態に陥りますので、そこがこのシステムの性能の限界となります。
さて、CPU使用率が100%となる同時アクセス数はいくつでしょうか。(先ほどの売上と来場者数の置き換えで言えば、売上100万円を達成するための来場者数と読み替えてください)
こういった分析を行う際に使用するのが一次回帰分析になります。
Excelでのやり方はめっちゃ簡単です。
その後、線の部分をクリックすると近似曲線の書式設定が表示されますので、以下の操作を行います。
これだけです。
今回の例では、
y = 0.0179 x + 7.8632
R^2 = 0.9426
という数式と値、グラフの直線が得られました。
横軸xが同時アクセス数、縦軸yがCPU使用率でしたので、計算すると、
同時アクセス数 x = 5147
の時にCPUが100%に至ります。
めでたし、めでたし…、ではありません。
標準偏差というものがある事は前編で学習した通りです。
今回のケースで言えば標準偏差を示すことは、誤差を示すことになります。
同時アクセス数が5147に至る前にCPUが100%に至る可能性もあります。
その幅がどの程度かを示さなければいけません。
これもExcelで簡単に計算できるのですが、なぜか近似曲線の書式設定の機能では表示できません。
ついでにR二乗が何なのかも説明します。
Excel分析ツール
Excelの分析ツールで標準偏差を計算します。
ファイル＞オプション＞アドインより、設定をクリックすると以下のウィンドウが表示されますので、「分析ツール」にチェックを入れてOKをします。
これを選択すると、Excelのメニューの「データ」タブに「データ分析」という項目が表示されるようになります。
これはExcel自体の設定なので、次回以降は不要です。
この「データ分析」クリックします。
すると、以下のサブウィンドウが表示されますので、「回帰分析」を選択してOKしてください。
以下の回帰分析サブウィンドウが表示されます。
いくつか機能はあるんですが、
「入力Y範囲」※今回はCPU
「入力X範囲」※今回は同時実行数
「出力オプション」新規ワークシート
にしてOKをしてください。
すると、新規シートに以下の結果がえられます。
色々数字があるので見るべきポイントを3つに絞りました。
重決定R2
先ほどのシートに記述されていた、R二乗と同じ値です。
R2は決定係数と呼ばれ、
「本当に直線で表現できる(=統計学的には「一次に回帰している」と言います)かの指標」となります。
1が最大で、一般的には0.7以上あれば信頼できる分析と言えます。
0.5以下ですと直線で表現して良いものではありませんので、残念ながら意味のない分析と判断されます。
有意F
こちらも用途としては重決定R2に近いです。
こちらは係数が0である可能性 = 一次に回帰していない可能性 = 直線で全く表現できない現象であった可能性
を示しています。
こちらは重決定R2のような直線っぽさを示すのではなく、分析そのものが無意味である確率となります。
今回は限りなく0に近い値ですので、大丈夫そうです。
係数、標準偏差
欲しかったやつです。
一次回帰の結果である傾きと切片にも当然それぞれ標準偏差(=誤差)があります。
つまり今回の直線の式についても前回同様に標準偏差を加えたうえで表現するのが本来正しいです。
y = (0.0179 ± 0.0017)x + (7.8632 ± 3.4990)
先ほどの単純計算では、
同時アクセス数 x = 5147
の時にCPUが100%に至りましたが、
・下限
100 % = (0.0179 – 0.0017)x + (7.8632 – 3.4990)
x = 5903
・上限
100 % = (0.0179 + 0.0017)x + (7.8632 + 3.4990)
x = 4522
となります。つまり、
CPU使用率が100%に至る同時アクセス数が、4522 ~ 5903の間である確率は68.7％です。
この確率を示さなければ「お前の予測あてにならないじゃないか」という事になりかねません。
Excelのグラフツールでの式の挿入で済ますのではなく、一時回帰分析で標準偏差も表すことをオススメします。
重回帰分析
ビッグデータ分析という単語が流行り出した時に一緒に流行った分析手法です。
正直言って用途は限られると思っています。
一次回帰分析は「値が二つある場合」であり、同時アクセス数が増えるほどCPU使用率が増加する、以下の式で表せるものだという”予測”に基づいた分析でした。
y(CPU使用率) = ay(同時実行数) + b
しかし、CPU使用率の変化に起因する値は本当に同時アクセス数のみでしょうか？
もしかしたら、室温も関係あるかもしれませんし、そもそも動かしている機械のCPU性能によって大きく変わるはずです。
y = a1x1 + a2x2 + a3x3 + … + b
x1:同時アクセス数
x2:室温
x3:CPU性能
これらの関係性を全部分析するにはCPU使用率、同時アクセス数、室温、CPU性能を同時に測定して、また条件を変えて測定して・・・、を繰り返してデータを収集する必要があります。
流石に現実的ではありませんので、別の例で説明します。
以下はとある商品の新聞広告と店頭プロモーションに掛けた費用と販売個数です。
| 新聞広告 | 店頭プロモ | 販売個数 |
| 1000 | 500 | 12000 |
| 500 | 1000 | 12000 |
| 500 | 1500 | 13000 |
| 800 | 800 | 11800 |
| 1200 | 400 | 14000 |
| 700 | 1000 | 11100 |
| 1500 | 500 | 15500 |
| 1300 | 700 | 16000 |
| 1400 | 600 | 16000 |
| 1300 | 500 | 13000 |
知りたいのは新聞広告と店頭プロモーションのどちらに費用を掛けた方が販売個数増加に期待できるかです。
Excelの使い方は一緒です。分析ツールで回帰分析を選択します。
今回はXの値が複数ありますが、そこは範囲指定してあげれば良いだけです。
3個以上ある場合も同じです。
すると、以下の結果が得られます。
見方は一次回帰分析と同じですので、細かい説明は省きます。
X値1が新聞広告で、X値2が店頭プロモです(指定範囲の左から順番付け)
つまり式は、
販売個数 = (6.93 ± 14.7) * 新聞広告 + (4.23 ± 16.2) * 店頭プロモ + (3195 ± 2608)
となりますので、新聞広告に費用を掛けた方が店頭プロモーションより費用より販売個数増加に期待できそうです。
重回帰分析はアンケート結果の分析に使える
サービスや製品の満足度アンケートを答える時に、まず総合点を答えさせる質問が良くあります。
これは各質問項目と総合点を重回帰分析し、質問項目に対する満足度を分析するためのものです。
なんでも良いのですが、何かの家電に対するアンケートだとします。
総合点、デザイン、機能性、サポート保証などの評点を利用者につけさせて重回帰分析すれば、提供側はどこに力を入れるべきかを判断できます。
提供側はデザインに力を入れていたものの、実際はデザインはどうでも良くて、機能性を重視しているという結果が得られるかもしれません。
終わりに
Excelでちょちょいとやってデータサイエンティスト(笑)を自称するというふざけたテーマでスタートしましたが、それでも出来るのと出来ないのでは大きな違いがあると思っています。
バズワードについても前編でけなしましたが、流行っているからには理由があります。
はぁ、また新しい言葉か。とは思うでしょうが、そういう時は一旦仕事から離れて冷静にバズワードについて学んだ方が良いです。
コメント