Excel。事務職のデータ分析その17。平均線付勝敗スパークラインを作ってみよう
＜スパークライン＞
Excel2007から登場した、『スパークライン』ですが、数多くの研修先などでよく、「これ便利そうなんだけど、ただ折れ線グラフが表示されるだけで…」
ということを耳にします。
そこで今回は、スパークラインに少しアイディアを加えるだけで、
平均以上なのか以下なのかがわかる、
【基準線付勝敗スパークライン】を紹介していきます。
なお、今回の基準線は、平均値を使いますので、
【平均線付勝敗スパークライン】ということになるのかもしれませんね。
では、次の表があります。
上半期の売上表で、平均値を算出している表ですね。
各店舗の状況をH列のスパークラインにまずは、表示してみると。
H2:H6を範囲選択して挿入タブのスパークラインにある、
”折れ線”をクリックすると、
スパークラインの作成ダイアログボックスが表示されます。
データ範囲に、B2:G6と入力をして、OKボタンをクリックしましょう。
折れ線のスパークラインが挿入されましたね。
テキストに掲載されているスパークラインの説明だとこれで、
終わるわけなのですが、これをどうやって、現場で使ったらいいのかが、
わかりにくいわけですね。
たとえば、スパークラインの種類を”縦棒”に変更しても
“勝敗”にスパークラインの種類を変えても、
だからどうしたらいいの？という感じですね。
これを基準として、平均以上か以下がわかるようなスパークラインにしていきます。
表を次のように変更します。
B8の数式は、=$B2-B$7
平均値との差を算出しています。
これを、オートフィルで、G12まで数式をコピーしています。
スパークラインのデータ範囲を変更します。
スパークラインツールのデータの編集をクリックして、
スパークラインの編集ダイアログボックスを表示します。
データ範囲を、B8:G12に変更して、OKボタンをクリックします。
軸を表示しますので、設定を変更していきますので、
スパークラインツールのデザインタブにある、軸をクリックします。
軸の表示と縦軸の最小値のオプションと最大値のオプションの両方とも、
”すべてのスパークラインで同じ値”にチェックをつけましょう。
スパークラインはこのようになっていますね。
最後に、マイナスの値の色を変更しますので、
表示の”負のポイント”にチェックを入れると、負のデータの色が変わります。
もし変わらない場合には、マーカーの色を変えてあげるといいでしょう。
さて、8行目から12行目までのデータは不要なので、
表示形式を変更して、見えなくしちゃいましょう。
A8:G12を範囲選択して、セルの書式設定ダイアログボックスを表示しましょう。
表示形式のユーザー定義を,;;;（セミコロン×3）としてOKボタンをクリックします。
これで、完成しました。
なお、スパークラインの種類を縦棒に変えてもいいですね。
このように、少しアイディアをプラスして、
現場でのツールとして使ってみてはいかがでしょうか？
