エクセルのグラフでは、売上や実績の推移だけを表示しても、数値が良いのか悪いのかを判断しにくい場合があります。
そこで役立つのが、平均値を示す平均線、目標達成の目安となる目標線、特定の値を読み取りやすくする補助線です。
基準線を追加すると、グラフを見る人が比較すべき水準をすぐに把握できるため、会議資料、営業レポート、予算管理表などの説得力が高まります。
基準線を引く主な方法は、元データに基準値の列を追加してグラフ系列として表示する方法です。
平均線、目標線、上限線、下限線を同じ考え方で作成できます。
この記事では、Excelのグラフに基準線を引く方法を、サンプルデータ、AVERAGE関数、複合グラフ、誤差範囲、図形を使う場合の注意点まで含めて解説します。
見栄えだけで線を足すのではなく、データと連動して自動更新されるグラフを作ることがポイントです。
エクセルのグラフに平均線を追加する方法
それではまず、グラフの元データに平均値の系列を追加し、折れ線として表示する方法について解説していきます。
| 月 | 売上 | 平均線 |
|---|---|---|
| 4月 | 120 | 135 |
| 5月 | 150 | 135 |
| 6月 | 135 | 135 |
| 7月 | 155 | 135 |
| 8月 | 115 | 135 |
この例では、B列の売上の平均である135をC列に並べ、売上は縦棒、平均線は折れ線で表示します。
平均値を計算する数式
平均線を作る前に、基準となる平均値をワークシートで計算します。
サンプルデータで1行目が見出し、売上データがB2からB6にある場合、C2へ次の数式を入力します。
=AVERAGE($B$2:$B$6)
AVERAGE関数は指定した範囲の合計を件数で割り、平均値を返す関数です。
ドル記号を付けた$B$2:$B$6は絶対参照であり、数式を下方向へコピーしても平均を求める範囲が変わりません。
C2の右下にあるフィルハンドルをC6までドラッグするか、コピーして貼り付けると、各行に同じ平均値が表示されます。
毎月の売上が更新されれば平均値も再計算されるため、グラフの基準線も自動で動きます。
グラフ系列への平均線の登録
続いては、売上列と平均線列を含めてグラフを作成する操作を確認していきます。
A1:C6を選択し、挿入タブのグラフから集合縦棒を選びます。
最初は売上と平均線の両方が縦棒で表示されることがありますが、これは正常な状態です。
グラフ内の平均線系列をクリックし、右クリックからグラフの種類の変更を選択します。
平均線だけを折れ線に変更すると、棒グラフの上を横切る基準線になります。
元データの列をグラフに含めるだけで、追加の描画作業なしに基準線を維持できます。
【操作のポイント】平均値を1つのセルだけに置くと線として描けないため、グラフに表示する各カテゴリの行へ同じ平均値を配置します。
平均線の見た目の調整
続いては、平均線を実績の棒と見分けやすくする書式設定を確認していきます。
平均線を選択してデータ系列の書式設定を開き、線の色を赤やオレンジなどに変更します。
線の幅を少し太くし、破線を選ぶと、実績値ではなく比較用の目安であることが伝わりやすくなります。
必要であれば、最後のデータポイントだけにデータラベルを付け、平均135のように表示しましょう。
平均線は強調しすぎず、実績データより一段控えめに見せると、情報の優先順位が整います。
平均線の色は赤系、目標線は緑系、上限や下限は灰色系というように、資料内で色の意味を統一すると読みやすくなります。
目標値を示す横線の作成手順
続いては、予算、KPI、合格ラインなどの固定目標をグラフへ表示する目標線について確認していきます。
| 月 | 実績 | 目標線 |
|---|---|---|
| 4月 | 120 | 140 |
| 5月 | 150 | 140 |
| 6月 | 135 | 140 |
| 7月 | 155 | 140 |
| 8月 | 115 | 140 |
目標値が月ごとに同じなら、平均線と同様に一定の数値を目標線列へ入力します。
固定目標値を入力するセル
目標を直接各行に入力しても構いませんが、後から変更しやすいように別セルへ目標値を置く方法が便利です。
たとえばE1に目標、E2に140と入力し、C2には次の数式を入力します。
=$E$2
C2をC6までコピーすれば、目標値だけをE2で管理できます。
予算会議で目標が変わっても、E2の数値を修正するだけでグラフ全体が更新されます。
年度ごと、部門ごとに目標値が異なる場合も、参照先のセルを変えるだけで同じ仕組みを応用できます。
複合グラフによる目標線表示
続いては、目標線を折れ線へ切り替える複合グラフの設定を確認していきます。
実績と目標線の列を選択した状態でグラフを作成し、グラフのデザインタブからグラフの種類の変更を選びます。
組み合わせを選択し、実績は集合縦棒、目標線は折れ線に設定します。
実績と目標の単位が同じなら、第2軸は通常不要です。
第2軸を不用意に使うと、棒と線の高さの比較が視覚的に正しくなくなる場合があります。
同じ単位の数値は原則として同じ縦軸で比較することが、目標線を正しく読ませる基本です。
【操作のポイント】グラフの種類を変更しても元データは変わりません。実績を棒、目標を線に分けるだけで、目標との差が読み取りやすくなります。
目標達成と未達の読み取り
続いては、目標線を使ってグラフから判断できる内容を確認していきます。
棒が目標線より上なら達成、下なら未達と直感的に判断できます。
ただし、棒の高さだけでは差の大きさが分かりにくいこともあります。
その場合は、D列に実績から目標を引いた差異を作ると効果的です。
=B2-$E$2
差異がプラスなら目標超過、マイナスなら未達です。
グラフは傾向を確認し、セルの数式は正確な差額を確認するという役割分担にすると、分析の精度が上がります。
補助線と誤差範囲を使う表示設定
続いては、特定の点や値を視線で追いやすくする補助線、誤差範囲を利用した基準線の表示について解説していきます。
| 商品 | 販売数 | 基準値 |
|---|---|---|
| 商品A | 82 | 100 |
| 商品B | 116 | 100 |
| 商品C | 97 | 100 |
補助線は単なる装飾ではなく、データラベルや軸目盛りを読まなくても基準位置を判断できるようにする案内線です。
グラフ要素から追加する目盛線
それではまず、縦軸と横軸の目盛線を整える操作について解説していきます。
グラフをクリックし、右上に表示されるグラフ要素のプラス記号から目盛線を選択します。
主要横目盛線は、縦棒グラフの数値を横方向に追う際に有効です。
補助横目盛線まで増やすと細かい数値は読みやすくなりますが、線が多すぎると実績の棒が埋もれます。
目盛線は薄い灰色にし、主役であるデータ系列より目立たせないことが大切です。
目盛線の追加は、グラフを選択しているときだけ表示されるグラフ要素から操作します。
誤差範囲を利用する縦方向の補助線
続いては、折れ線グラフや散布図で特定のデータ点を目立たせる誤差範囲について確認していきます。
グラフ要素から誤差範囲を追加すると、各データ点の上下へ線を表示できます。
標準誤差、パーセンテージ、固定値、ユーザー設定から目的に合う形式を選択します。
たとえば許容差が上下10の場合は、固定値に10を設定すると、各値の上下に幅を持った線を描けます。
誤差範囲は平均線そのものではありません。
測定値のばらつき、許容範囲、予測誤差など、数値に幅があることを示したいときに使います。
目標値の横線と、ばらつきを示す誤差範囲は用途が異なるため、混同しないようにしましょう。
【操作のポイント】誤差範囲の線が多すぎる場合は、グラフの対象データを絞るか、線の色を薄くして情報量を調整します。
基準線に使うセル参照の設計
続いては、基準線を後から管理しやすくするセル参照の設計について確認していきます。
目標、平均、上限、下限を入力するセルをデータ表の近くにまとめると、何を基準にしているかが明確になります。
たとえばE2に目標、E3に上限、E4に下限を置き、それぞれの列で絶対参照します。
=IF($A2<>””,$E$2,NA())
この数式では、A列に月が入力されている行だけ目標値を返し、空白行ではNA関数でグラフに値を描かせません。
将来の入力行まで基準線が不要に伸びる問題を防げるので、実務の一覧表で便利な方法です。
平均線と目標線を見やすくする書式設定
続いては、追加した基準線を読みやすく整える色、ラベル、軸の設定について確認していきます。
| 系列 | 推奨色 | 線の例 |
|---|---|---|
| 実績 | 青または緑 | 棒または実線 |
| 平均 | オレンジ | 破線 |
| 目標 | 赤または緑 | 太めの実線 |
線色と破線による役割の区別
それではまず、複数の線があるグラフで役割を区別する方法について解説していきます。
平均線と目標線が同じ色、同じ太さでは、閲覧者が凡例を何度も見なければなりません。
平均は過去実績の中心を示すためオレンジの破線、目標は到達すべき水準を示すため赤または緑の実線にするなど、意味を視覚的に分けます。
色だけでなく実線と破線の違いも付けると、白黒印刷や色覚の違いがある環境でも判別しやすくなります。
データラベルと凡例の配置
続いては、基準線の数値を過不足なく伝えるラベルの配置を確認していきます。
全てのポイントへ同じ135というラベルを付けると、グラフ内が混み合います。
平均線または目標線の右端のポイントだけにデータラベルを表示し、平均135、目標140のように内容が分かる系列名も併記すると効果的です。
凡例だけに頼らず、線の近くへ短い名称を示すと、資料を単独で見た人にも意図が伝わります。
縦軸の最小値と最大値の調整
続いては、基準線の位置を誤解させない縦軸の設定について確認していきます。
縦軸の最小値を大きく設定しすぎると、小さな差が実際以上に大きく見えることがあります。
反対に最大値が大きすぎると、目標との差が見えにくくなります。
縦棒グラフでは、比較の誤解を避けるため、縦軸の最小値を0にする設定が基本です。
ただし、細かな変化を検討する折れ線グラフでは、目的を注記したうえで最小値を調整することもあります。
【操作のポイント】軸の書式設定で最小値、最大値、主単位を固定すると、月ごとに更新してもグラフの尺度が変わりにくくなります。
グラフの基準線が表示されない場合の確認項目
続いては、平均線や目標線が出ない、斜めになる、実績と重なるといったトラブルの確認項目を解説していきます。
| 症状 | 主な原因 | 確認内容 |
|---|---|---|
| 線が出ない | 系列の範囲漏れ | データの選択 |
| 線が斜め | 参照範囲のずれ | 絶対参照 |
| 高さが合わない | 第2軸の設定 | 軸の尺度 |
グラフのデータ範囲の確認
それではまず、基準線の列がグラフ系列に含まれているか確認していきます。
グラフを右クリックしてデータの選択を開くと、凡例項目に平均線または目標線が表示されます。
表示されない場合は追加を選び、系列名に見出しセル、系列値に基準線の数値範囲を指定します。
グラフを作成した後で列を追加した場合は、自動で系列へ加わらないことがあります。
数式の相対参照による値のずれ
続いては、横線になるはずの基準線が斜めになる場合を確認していきます。
C2に=AVERAGE(B2:B6)と入力して下へコピーすると、C3ではAVERAGE(B3:B7)へ変化します。
このような相対参照では、各行で異なる平均値が計算され、線が横一直線になりません。
範囲を固定した=AVERAGE($B$2:$B$6)へ修正しましょう。
基準線の各セルに同じ数値が入っているかを確認すると、原因をすばやく見つけられます。
第2軸と表示形式の不一致
続いては、目標線が意図しない高さにある場合の軸設定を確認していきます。
実績が円、目標が百分率のように単位が異なるときは、第2軸が必要になることがあります。
一方、両方が売上金額なら第2軸を使わず、同一の縦軸にそろえるのが基本です。
また、セルが文字列として入力されていると、グラフに正しく反映されないこともあります。
数値として認識させるには、セルの表示形式だけでなく、数式バー上の値に不要な文字や先頭のアポストロフィがないか確認します。
【操作のポイント】基準線の数値、系列の種類、縦軸の尺度を順番に確認すると、ほとんどの表示不具合を切り分けられます。
まとめ エクセルのグラフに基準線を引く方法
エクセルのグラフに基準線を引くには、平均値や目標値を元データの列として用意し、グラフでは折れ線系列として表示する方法が基本です。
平均線はAVERAGE関数で計算し、目標線は専用セルを絶対参照するようにすると、データ更新に連動したグラフになります。
実績を縦棒、平均線や目標線を折れ線にした複合グラフなら、達成状況と傾向をひと目で比較できます。
補助線として目盛線や誤差範囲を使う場合は、目的に応じて情報量を調整しましょう。
線色、破線、データラベル、縦軸の設定まで整えることで、単に数値を並べたグラフではなく、判断に役立つレポートへ仕上がります。
基準線が表示されないときは、データ範囲、絶対参照、第2軸の有無を確認してみてください。
