Excel。事務職のデータ分析その17。平均線付勝敗スパークラインを作ってみよう【Spark line】

Excel。事務職のデータ分析その17。平均線付勝敗スパークラインを作ってみよう

＜スパークライン＞

Excel2007から登場した、『スパークライン』ですが、数多くの研修先などでよく、
「これ便利そうなんだけど、ただ折れ線グラフが表示されるだけで…」
ということを耳にします。

そこで今回は、スパークラインに少しアイディアを加えるだけで、
平均以上なのか以下なのかがわかる、
【基準線付勝敗スパークライン】を紹介していきます。

なお、今回の基準線は、平均値を使いますので、
【平均線付勝敗スパークライン】ということになるのかもしれませんね。

では、次の表があります。

上半期の売上表で、平均値を算出している表ですね。

各店舗の状況をH列のスパークラインにまずは、表示してみると。

H2:H6を範囲選択して挿入タブのスパークラインにある、
”折れ線”をクリックすると、
スパークラインの作成ダイアログボックスが表示されます。

データ範囲に、B2:G6と入力をして、OKボタンをクリックしましょう。

折れ線のスパークラインが挿入されましたね。

テキストに掲載されているスパークラインの説明だとこれで、
終わるわけなのですが、これをどうやって、現場で使ったらいいのかが、
わかりにくいわけですね。

たとえば、スパークラインの種類を”縦棒”に変更しても

“勝敗”にスパークラインの種類を変えても、
だからどうしたらいいの？という感じですね。

そこで、せっかく平均値があるので、
これを基準として、平均以上か以下がわかるようなスパークラインにしていきます。

表を次のように変更します。

B8の数式は、=$B2-B$7
平均値との差を算出しています。

これを、オートフィルで、G12まで数式をコピーしています。

スパークラインのデータ範囲を変更します。

スパークラインツールのデータの編集をクリックして、
スパークラインの編集ダイアログボックスを表示します。

データ範囲を、B8:G12に変更して、OKボタンをクリックします。

軸を表示しますので、設定を変更していきますので、
スパークラインツールのデザインタブにある、軸をクリックします。

軸の表示と縦軸の最小値のオプションと最大値のオプションの両方とも、
”すべてのスパークラインで同じ値”にチェックをつけましょう。

スパークラインはこのようになっていますね。

最後に、マイナスの値の色を変更しますので、
表示の”負のポイント”にチェックを入れると、負のデータの色が変わります。

もし変わらない場合には、マーカーの色を変えてあげるといいでしょう。

さて、8行目から12行目までのデータは不要なので、
表示形式を変更して、見えなくしちゃいましょう。

A8:G12を範囲選択して、セルの書式設定ダイアログボックスを表示しましょう。

表示形式のユーザー定義を,;;;（セミコロン×3）としてOKボタンをクリックします。

これで、完成しました。

なお、スパークラインの種類を縦棒に変えてもいいですね。

このように、少しアイディアをプラスして、
現場でのツールとして使ってみてはいかがでしょうか？

Excel。Sparklines。スパークラインってどんな時に使うの？

Excel。スパークラインってどんな時に使うの？

＜スパークライン＞

Excel2010からスパークラインという機能が追加されまして、そのスパークライン。
作り方はわかるのですが、どういう時に使うの？
というご質問をよくいただきますので、
今回はその一つのケースとして、スパークラインの作り方となんで、
スパークラインなのか？ということについて、書いてみたいと思います。

まずは、下記のようなデータ。表があります。

このデータを使って、商品ごとの各月の売上推移を見てみたいと思ったとします。

すると、このデータを使って、折れ線グラフにすると、
商品ごとの各月の売上推移がわかるわけですね。

例えば、デラックスチョコレートをマーカー付き折れ線グラフにしてみると、

このようなマーカー付折れ線グラフになりますので、2月が一番売れているんだよね。
6月が一番落ち込んでいるんだよね。とわかるわけですね。

ところが、今回は、デラックスチョコレートというワンアイテムでしたので、
折れ線グラフも一つだったので、傾向はわかりやすかったのですが、
今回のアイテムを全部まとめて動向や傾向を確認したいとした場合、
どのようになるでしょうか？

A1:M23までを範囲選択して、挿入タブの折れ線グラフから、
マーカー付折れ線グラフをクリックしましょう。

すると、次のようなマーカー付折れ線グラフが完成しましたが、
これでは、全くわからないので、修正していきます。

行と列を切り替えますので、デザインタブの行/列の切り替えボタンをクリックしましょう。

グラフのデータが、行と列が切り替わりましたので、横軸が月になりました。

このグラフからですと、生クリームロールが一番人気があって、
他はその他大勢って感じしか読み取れませんよね。プロットされていますが、ぐちゃぐちゃ。

折れ線グラフは、時系列で経過した推移を見ることが出来るグラフではありますが、
このようにアイテム件数が多い場合、全く役に立ちません。

そこで、最初作成しました、デラックスチョコレートのように、
ワンアイテムごとにグラフを作ってもいいかもしれませんが、このアイテム数、
しかも、縦軸の値がアイテムごとによって変わってしまうので、統一した見方が出来ませんから、
すべてのグラフの縦軸も統一した数値に変えていく必要が生じてしまいます。

そこで、スパークラインの折れ線グラフが登場するわけです。

O2をクリックして、挿入タブのスパークラインの折れ線をクリックしましょう。

そうすると、スパークラインの作成ダイアログボックスが表示されますので、

データ範囲には、B2:M2と入力してOKボタンをクリックしましょう。
スパークラインが挿入されますので、
オートフィルハンドルを使ってスパークラインをコピーしましょう。

これで、スパークラインの折れ線が出来ました。このようにすると、
アイテムごとに動向や傾向を見ることが出来ます。

このようにアイテム数が多い時などに、スパークラインを使ってみるといいかもしれませんね。