Excel でグラフ作ってますか？
Excel と聞くと拒否反応が出る方も少なくないとは思いますが、実は簡単なことをするだけなら十分強力なツールだったりします。 細かいことをしたいときや繰り返し処理をしたいときは適当なプログラムを書いた方が良いですが、そうでないなんちゃってデータ分析＆可視化なら Excel も意外と使えます。 今日はそんな Excel でいい感じの図を作るための tips をいくつか紹介します。
準備
今回は二系列の大型スーパー A, B の各店舗(A1, A2, A3)と(B1, B2, B3)の 1-4 月の売上データをもとに、系列ごとの各月の売り上げを図示していきたいと思います。
| Month | A1 | A2 | A3 | B1 | B2 | B3 |
|---|---|---|---|---|---|---|
| 1 | 390 | 407 | 393 | 475 | 273 | 63 |
| 2 | 149 | 75 | 364 | 250 | 306 | 423 |
| 3 | 239 | 287 | 483 | 465 | 463 | 384 |
| 4 | 384 | 81 | 378 | 432 | 244 | 332 |
これをもとに、系列ごとの平均と分散をそれぞれ計算します。
※ AVERAGE(), STDEV() でそれぞれ計算できます。
| mean | A | B | std | A | B | |
|---|---|---|---|---|---|---|
| 1 | 397 | 270 | 1 | 7 | 168 | |
| 2 | 196 | 326 | 2 | 123 | 72 | |
| 3 | 336 | 437 | 3 | 106 | 38 | |
| 4 | 281 | 336 | 4 | 141 | 77 |
すると上記のような表が得られると思います。
※いずれの値も整数部分で打ち切っています。
まずは、この状態で愚直に平均値だけを折れ線グラフでプロットすると次のようになります。
※見やすいようにマーカー等を調整しています。
以降、様々な tips を紹介しながら、この図を下図のような状態にまで持っていきます。
エラーバーを表示する
Excel でできて Google Spreadsheets でできないことの最大のポイントはエラーバーだと思います。 Excel で図にエラーバーを追加するにはエラーバーを追加したい系列を選択した状態で
「グラフ要素の追加」＞「誤差範囲」＞「その他のオプション」
から追加できます。
標準では固定値 10%という謎の誤差範囲が指定されているので、ここに先ほど計算した標準偏差を参照するように設定します。
「誤差範囲の書式設定」＞「誤差範囲のオプション」＞「誤差範囲」＞「ユーザー設定」を選択
その後、「値の指定」から「正の誤差の値・負の誤差の値」の両方に、始めに計算した標準偏差の値が入ったセルを設定します。 すると標準偏差がエラーバーとして表示されます。 これを全ての系列に対して行います。
ここまでの結果です。分かりやすいようにエラーバーの見た目も少し弄っています。
エラーバーをずらす
上の図では二つのエラーバーが重なってしまっていて見づらいですね。 これを解決するためにマーカーの位置を少しだけ左右にずらします。
まずは、現状の折れ線グラフを「グラフの種類の変更」から直線とマーカー付きの散布図に変更します。
色々と直したい気はしますが、まずは一旦エラーバーの重なりを無くすことにします。
始めに、横軸方向にずらすために次のような表を作ります。実際には 0.98 月なんてものは存在しないわけですが、ずらすだけなので問題ないです。
| mean | A | B | diff: | A | B | |
|---|---|---|---|---|---|---|
| 1 | 397 | 270 | 0.02 | 0.98 | 1.02 | |
| 2 | 196 | 326 | 1.98 | 2.02 | ||
| 3 | 336 | 437 | 2.98 | 3.02 | ||
| 4 | 281 | 336 | 3.98 | 4.02 |
次に、グラフの横軸に使用されている値をオリジナルの 1, 2, 3, 4 月から上記の表の値に変更します。
一番簡単なのは、グラフで系列データを選択した状態で、下図のように参照されているセルの範囲（紫色）を変更する事だと思います。
※図は diff が 0.05 になっていますが、大きかったので 0.02 に変更しました。
これを両方の系列に適用すると次のようになります。
無事、マーカー位置が微妙にずれてエラーバーが見やすくなりました。
横軸から不要な範囲を消す
さて、皆さんだいぶ前から横軸を 1-4 月で切り取りたくてうずうずしていると思います。 ここではその方法をご説明します。
まずは横軸を選択した状態で、
「軸の書式設定」＞「軸のオプション」＞「境界値」
で最小値を 1.0 、最大値を 4.0 にします。
ついでに 0.5 ヶ月刻みになるのも気持ち悪いので、主単位を 1.0 にします。
さて、どうなったでしょうか。
横軸が 1-4 月になったのはいいですが、端のデータが消えてしまいました。
それぞれ横軸の値が 0.98 と 4.02 なので当然と言えば当然ですね。
これでは困るので「境界値」を最小値 0.9 、最大値 4.1 にしてみます。
いい感じですが、今度は横軸の目盛が 0.9 スタートになって微妙です。
そこで目盛を四捨五入することで、無理矢理表示上整数値に丸め込みます。
そのために横軸を選択した状態で
「軸の書式設定」＞「文字のオプション」＞「表示形式」
でカテゴリを「数値」に設定し、小数点以下の桁数を 0 に設定します。
完成です。 お疲れさまでした。
終わりに
今回ご紹介した方法を適宜組み合わせることで、大抵の図はいい感じに整形できると思います。
皆さんもぜひ簡単なデータ分析には Excel を使用してみてください。
※ステマではないです。