エクセルのグラフで売上や作業時間の推移を確認するとき、数値の折れ線や棒だけでは、どの程度が目標なのか、平均より高いのかを直感的に判断しにくいことがあります。
そこで役立つのが、目標値を示す基準線と、データ全体の傾向を示す平均線です。
グラフに補助系列を追加すれば、元データを大きく変更せずに見やすい横線や平均の目安を表示できます。
基準線は目標値や許容値を示す横線です。
平均線は全データの平均値を計算して示す横線です。
どちらも補助データを追加し、グラフの系列として表示する方法が基本になります。
この記事では、エクセルのグラフに基準線・平均線を引く方法を、サンプルデータと数式を使いながら詳しく解説していきます。
エクセルのグラフに基準線・平均線を引く基本手順
| 月 | 売上 | 基準線 | 平均線 |
|---|---|---|---|
| 4月 | 120 | 150 | 135 |
| 5月 | 140 | 150 | 135 |
| 6月 | 145 | 150 | 135 |
それではまず、基準線と平均線をグラフへ追加する基本の考え方について解説していきます。
補助列による線用データの作成
基準線や平均線を表示する最も確実な方法は、元の表に線を描くための補助列を作成する方法です。
たとえば、A列に月、B列に売上があり、1行目が見出しのケースを考えます。
C列に基準線、D列に平均線という見出しを追加しましょう。
基準線が150の場合は、C2から最終行まで同じ150を入力します。
グラフでは同じ数値が各月に並ぶため、横方向へまっすぐな線として表示されます。
基準線の補助列には、目標売上、予算、上限値、下限値、合格ラインなどを入力できます。
データの各行に同じ値を配置することが、横線を表示する重要なポイントです。
途中で目標を変更したくなった場合も、補助列の値を変更すればグラフへ反映されます。
目標値を別セルに入力し、絶対参照で参照する作り方にしておくと、管理しやすくなります。
平均値の数式による算出
平均線には、AVERAGE関数を使う方法が便利です。
売上データがB2からB13に入力されている場合、D2には次の数式を入力します。
=AVERAGE($B$2:$B$13)
この数式は、B2からB13までの売上を合計し、データ数で割った平均値を返します。
$記号を付けた絶対参照にすることで、数式を下方向へコピーしても計算範囲がずれません。
D2の右下にあるフィルハンドルを下へドラッグし、最終行までオートフィルしてください。
各行には同じ平均値が表示されるため、平均線用の系列が完成します。
空白セルや文字列が混ざる表では、AVERAGE関数が数値だけを対象にすることも確認しておきましょう。
グラフ系列への追加
補助列を作成したら、売上列と基準線列、平均線列を含めて表全体を選択し、グラフを挿入します。
すでに売上グラフを作成済みなら、グラフを右クリックしてデータの選択を開き、系列の追加から補助列を指定する方法でも構いません。
基準線と平均線が棒として表示されたときは、グラフの種類の変更で組み合わせグラフを選びます。
売上は集合縦棒、基準線と平均線は折れ線に設定すると、数値の比較がしやすい構成になります。
補助列をグラフの系列として追加することが、エクセルで自由な基準線を作る基本です。
【操作のポイント】基準線と平均線は、元データと同じ行数だけ数値を用意してからグラフへ追加します。
目標値を示す基準線の追加方法
| 月 | 実績 | 目標 |
|---|---|---|
| 7月 | 82 | 90 |
| 8月 | 96 | 90 |
| 9月 | 88 | 90 |
続いては、目標や許容範囲をわかりやすく示す基準線の追加方法を確認していきます。
固定値を入力する基準線
毎月の目標が常に90件のように固定されている場合は、補助列に90を入力するだけで基準線を作成できます。
入力の手間を減らすには、C2に90を入力してから、セル右下のフィルハンドルを最終行まで引き下げましょう。
同じ値が並んだ列を折れ線として表示すると、グラフ内に目標達成の判断基準が現れます。
固定の基準線は、月間目標、予算額、許容温度、在庫の最低ラインなどに向いています。
基準値を一目で確認できるため、会議資料や報告書にも活用しやすい方法です。
線の色は実績データと明確に分けると、読み手が数値の意味を誤解しにくくなります。
参照セルを使う基準線
目標値を変更する可能性がある場合は、表の外側に目標値入力セルを用意する方法がおすすめです。
たとえばF2に目標値90を入力し、C2には次の数式を入力します。
=$F$2
C2を最終行までオートフィルすると、すべての行がF2を参照します。
F2の数値を100に変更すれば、補助列とグラフの基準線も自動で100へ移動します。
基準値を一か所で管理できる設計は、定期レポートを更新するときに特に便利です。
参照先のセルには、目標値であることがわかる見出しを付けておくと、他の人がファイルを使う場合も安心です。
棒グラフと折れ線の組み合わせ
実績を棒グラフで表示している場合、基準線も棒のままでは区別がつきにくいことがあります。
そのときは、基準線の系列だけを選択し、グラフの種類の変更から折れ線へ変更しましょう。
基準線を右クリックし、データ系列の書式設定で線の太さや破線を変更すると、実績との差がより明確になります。
目標線には赤やオレンジ、平均線には青やグレーなど、役割ごとに色を分けると効果的です。
ただし色だけに頼らず、系列名も「目標」「基準値」とわかりやすく設定することが大切です。
【操作のポイント】棒グラフの基準線は、組み合わせグラフで折れ線へ変更すると視認性が高まります。
AVERAGE関数を使った平均線の作成
| 月 | 受注数 | 平均線 |
|---|---|---|
| 1月 | 32 | 35 |
| 2月 | 38 | 35 |
| 3月 | 35 | 35 |
続いては、AVERAGE関数で算出した値を使い、平均線を作成する手順を確認していきます。
平均値を各行へ表示する数式
平均線は、対象データの平均値をすべての行に繰り返し表示して作成します。
受注数がB2からB13にある場合、平均線の先頭セルC2へ次の数式を入力します。
=AVERAGE($B$2:$B$13)
AVERAGE関数は、指定範囲内の数値の平均を求める関数です。
たとえば合計が420でデータ数が12なら、計算結果は35になります。
平均線の数式では、範囲を固定するためにB2とB13の両方へ$記号を付けます。
相対参照のままコピーすると、C3ではB3からB14の平均へずれてしまい、横一直線の平均線になりません。
オートフィルによる平均線の展開
C2に数式を入力したら、セル右下に表示される小さな四角へマウスポインターを合わせます。
ポインターが黒い十字に変わったら、売上データの最終行までドラッグしてください。
オートフィル後の各セルには同じ平均値が表示され、折れ線グラフでは水平な平均線になります。
データ件数が増える表では、テーブル機能を使うと新しい行へ数式が自動反映されやすくなります。
平均線を折れ線として整える書式設定
平均線用の列をグラフへ追加したら、平均線の系列をクリックして選択します。
グラフの種類の変更から折れ線を選び、必要に応じてマーカーをなしに設定しましょう。
平均線は細すぎない線幅にすると、実績の棒や折れ線に埋もれにくくなります。
平均線には落ち着いた青やグレーを使うと、目標線との役割を分けやすくなります。
平均値の意味を示すデータラベルや凡例を残しておくと、初めて見る人にも伝わります。
月別のデータで平均線を使う場合は、対象期間が変わると平均値も変わる点に注意が必要です。
前年との比較では、今年分だけの平均なのか、複数年を含む平均なのかを明記すると誤解を防げます。
【操作のポイント】AVERAGE関数の範囲は絶対参照にし、先頭セルの数式をオートフィルでコピーします。
移動平均と目標線を使い分けるグラフ設定
| 週 | アクセス数 | 目標値 |
|---|---|---|
| 第1週 | 820 | 900 |
| 第2週 | 980 | 900 |
| 第3週 | 870 | 900 |
続いては、単純な平均線、移動平均、目標線の使い分けを確認していきます。
全期間の平均線
全期間の平均線は、対象範囲全体の水準を一つの数値で把握したいときに適しています。
月別売上が平均を上回った月と下回った月を確認したい場合、AVERAGE関数で作る横線が有効です。
ただし、期首と期末で市場環境が大きく変わるデータでは、全体平均だけでは変化の流れをつかみにくいかもしれません。
全体の基準を示したい場合に、固定の平均線を選びましょう。
近似曲線の移動平均
日別や週別の数値のように上下動が大きい場合は、グラフの近似曲線にある移動平均が役立ちます。
データ系列を選択して右クリックし、近似曲線の追加から移動平均を選ぶと、一定期間の平均をなめらかな線で表示できます。
期間を3にすると、連続する3点を使った傾向が表示されます。
移動平均は時点ごとに平均が変化するため、固定の平均線とは異なる意味を持ちます。
固定平均線は全期間を一つの平均値で比較する方法です。
移動平均は直近の変化をならし、増加傾向や減少傾向を確認する方法です。
傾向を見たいなら移動平均、達成水準を見たいなら固定平均線という考え方がわかりやすいでしょう。
二次軸を使う場面
売上金額と達成率のように、単位や数値の桁が大きく異なる系列を同じグラフへ表示する場合があります。
この場合、基準線や平均線の値が小さく見えすぎることがあるため、二次軸の利用を検討します。
ただし二次軸は、左右で目盛りが異なるため、読み手が値を誤解するおそれもあります。
基準線が実績と同じ単位なら、まずは同じ縦軸で表示する方法を優先しましょう。
【操作のポイント】数値のばらつきをならすなら移動平均、目標との比較なら固定の基準線を選びます。
見やすい基準線・平均線に整える書式設定
| 系列 | おすすめの表示 | 役割 |
|---|---|---|
| 実績 | 棒または実線 | 実際の数値 |
| 目標線 | 赤系の破線 | 達成基準 |
| 平均線 | 青系の実線 | 全体傾向 |
続いては、基準線と平均線を読み取りやすくする書式設定を確認していきます。
線の色と種類
基準線と平均線を同じ色にすると、何を表す線なのかが伝わりにくくなります。
目標や上限の線には注意を促す赤系、平均線には比較的落ち着いた青系やグレー系を使うと区別しやすくなります。
目標線を破線にする設定も、実績線との見分けに効果があります。
線の太さは、グラフ内で見失わない程度に設定しつつ、実績データを隠さないバランスが必要です。
凡例とデータラベル
グラフに複数の補助線がある場合、凡例には「基準線」「平均線」「目標」など、意味が伝わる系列名を表示します。
補助列の見出しがそのまま凡例名になるため、表の1行目をわかりやすく入力することが大切です。
必要に応じて、線の右端へデータラベルを付けると、グラフを見る人が数値をすぐ把握できます。
凡例だけで意味が伝わりにくい場合は、グラフタイトルに対象期間や単位を補足します。
たとえば月別売上と目標値、平均売上のように、比較内容を簡潔に示すと効果的です。
縦軸の最小値と最大値
基準線がグラフの上端や下端に近すぎると、線の存在が目立たないことがあります。
縦軸を右クリックして軸の書式設定を開き、最小値と最大値を適切に設定しましょう。
たとえば実績が80から120、基準線が100なら、縦軸を0から200にするよりも、60から140程度に設定したほうが差を読み取りやすくなります。
一方で、途中を省略したような印象を与える場合もあるため、報告先によっては縦軸の開始値を0にする判断も必要です。
【操作のポイント】色、破線、凡例、軸の範囲を整えると、補助線の意味が直感的に伝わります。
まとめ エクセルの平均線・基準線をグラフに引く方法
| 確認項目 | 設定内容 |
|---|---|
| 基準線 | 同じ目標値を補助列へ入力 |
| 平均線 | AVERAGE関数を絶対参照でコピー |
| グラフ | 補助列を折れ線の系列として追加 |
エクセルのグラフに基準線・平均線を引くには、線用の補助列を作り、グラフの系列として追加する方法が基本です。
目標値を示す場合は同じ数値を各行へ入力し、平均値を示す場合はAVERAGE関数で求めた結果を各行へコピーします。
平均線の数式は絶対参照にすることで、オートフィル後も計算範囲がずれません。
棒グラフへ基準線を追加するときは、組み合わせグラフで補助系列を折れ線へ変更すると見やすくなります。
さらに、目標線と平均線の色、線種、凡例を整えれば、実績との関係を短時間で把握できるグラフになります。
目標達成の確認、売上分析、品質管理、業務量の比較などに活用し、読み手へ伝わる資料作りにつなげていきましょう。
