【Excel】予実対比グラフを見やすくする方法|超過・未達を色分け表示

予実対比のグラフを作ると、

  • 実績
  • 予算

を並べた棒グラフになることがよくあります。

しかし、そのままでは

どの月が超過なのか

どの月が未達なのか


を瞬時に読み取ることができません。

そこで今回は、

予算に対して

  • 超過は青
  • 未達は赤

で表示し、

予算値はマーカーだけで表現することで、

一目で状況を判断できる予実対比グラフを作成します。

ダッシュボードや月次レポートでも使えるテクニックです。

この記事でできること

✅ 予実対比グラフを見やすくする

✅ 超過・未達を色で表現する

✅ 予算をマーカーで表示する

✅ ダッシュボード向けのグラフを作る

ポイント

今回のポイントは表示を超過の場合(実績-予算がプラス)と未達の場合(実績ー予算がマイナス)で分けることです。

折れ線グラフの線を透明にしてマーカー(予算)だけ表示するのも隠れたポイントです

折れ線グラフ内の表示内容・変換

超過の場合 ⇒予算額をベース表示

実績が予算を上回っている場合は、下のグラフの表示(色が濃い部分)は「予算額」の表示です

未達の場合 ⇒実績額をベース表示

実績が予算を下回っている場合は、下のグラフの表示(色が濃い部分)は「実績」の表示になり、赤の部分が「差額|予算-実績」になります

折れ線グラフ

予算額を示す折れ線グラフの線は非表示(透明)にします

データの作成

グラフの表示は見た目は紺色と「薄い青」か「赤」かの2つだけですが、4つの項目をデータの元とします

ちなみに、基本、超過、未達にはIF関数を入れます

但し、画像上で一番左にある項目の「予算」には数式を入れません。

基本

実績が予算を上回っていたら「予算のセル」、そうでない場合は「実績のセル」を表示できるようにIF関数を入力します

超過

実績が予算を上回っていたら「超過額」を、そうでなければ空欄を表示するようにします

未達

実績が予算を下回っていたら「未達額」を、そうでなければ空欄を表示するようにします

グラフ作成

上で作成したデータを基にして、積上げ棒グラフを作成すると下の画像の状態になります

次に、この一番下/青の箇所はグラフ種類を変更します

次に開いた画面で「折れ線グラフ」にします

ここで、折れ線グラフ線は「線なし」にします

マーカーについては、下の画像のように種類とサイズを調整します

後は、グラフの色を調整して終了です

画像に alt 属性が指定されていません。ファイル名: image-1.png

<まとめ>

今回は、積上げ棒グラフを工夫して予実対比を見やすくする方法を解説しました

今回紹介したグラフは「グラフのシンプル」さを追及した一つの形であり、他にも調整方法があるので、好みに合わせて工夫していくと良いと思います

大事なのはシンプルに表現することです

シンプルさを追及することは忘れないようにしましょう!


にほんブログ村

コメントを残す

メールアドレスが公開されることはありません。 ※ が付いている欄は必須項目です