エクセルの折れ線グラフで売上や進捗を見える化するとき、目標値や平均値を示す基準線があると、数値の良し悪しを一目で把握しやすくなります。
元データに基準線用の列を追加してグラフに系列として登録すれば、横一直線の目標線や平均線を自然に表示できます。
基準線はグラフの見た目を整えるだけでなく、判断基準を共有するための重要な要素です。
基準線を引く基本の流れは、基準となる数値を決め、同じ値を各行に入力し、折れ線グラフのデータ系列へ追加することです。
この記事では、目標線、平均線、上限値や下限値などをエクセルの折れ線グラフへ追加する方法を、サンプルデータと数式を使って詳しく解説します。
エクセルの折れ線グラフに目標線を追加する方法
それではまず、折れ線グラフへ一定の目標値を基準線として表示する方法について解説していきます。
| 月 | 売上 | 目標線 |
|---|---|---|
| 4月 | 82 | 100 |
| 5月 | 95 | 100 |
| 6月 | 108 | 100 |
| 7月 | 101 | 100 |
基準線用データ列の作成
折れ線グラフに目標線を表示するには、まず元データの右側に目標線専用の列を作成します。
サンプルではA列を月、B列を売上、C列を目標線として使用します。
グラフの基準線は図形として描くのではなく、データ系列として追加する方法が最も安定します。
C2セルに目標値である100を入力し、C5まで同じ100を入力してください。
基準線の数値がすべての月で同一になるため、グラフ上では横一直線になります。
目標値を途中で変える可能性がある場合は、別のセルに目標値を入力して参照する設計にすると、更新作業を減らせます。
例えばE1セルに100を入力し、C2セルへE1を参照する数式を設定する方法です。
= $E$1
絶対参照にすると、下方向へオートフィルしても参照先がE1セルのまま固定されます。
【操作のポイント】基準線の列は売上データと同じ行数まで用意し、空白行を残さないことが大切です。
折れ線グラフへの系列追加
次に、A1からC5までの範囲を選択して、挿入タブから折れ線または面グラフを選び、2D折れ線をクリックします。
すでに売上だけのグラフを作成している場合は、グラフをクリックしてからグラフのデザインタブを開き、データの選択を使います。
データソースの選択画面で追加をクリックし、系列名にC1、系列値にC2からC5を指定しましょう。
横軸ラベルにはA2からA5の月を指定すると、売上と目標線が同じ時間軸で比較できます。
系列として追加したC列は、売上と同じグラフ内に表示されます。
目標線が見えないときは、系列値の範囲に見出しセルを含めていないか、売上と目標線で行数が一致しているかを確認してください。
【操作のポイント】系列の追加はグラフ上で右クリックし、データの選択から行うと対象を間違えにくくなります。
目標線の色と線種の設定
目標線をクリックして選択し、右クリックからデータ系列の書式設定を開きます。
塗りつぶしと線の項目で線を選択し、実線、破線、色、幅を設定できます。
売上の折れ線と区別するため、目標線には赤色やオレンジ色、破線を使うと見やすくなります。
重要なのは目標線を目立たせすぎず、実績の変化を読み取れる状態を保つことです。
売上を青の実線、目標を赤の破線にすると、実績と目標という役割が視覚的に伝わります。
必要に応じてマーカーをなしにすると、各月の100という点が連続している印象を抑えられます。
【操作のポイント】基準線には破線を使うと、実績線との取り違えを防ぎやすくなります。
平均線を数式で表示する方法
続いては、データの平均値を自動計算し、平均線として折れ線グラフへ表示する方法を確認していきます。
| 月 | 売上 | 平均線 |
|---|---|---|
| 4月 | 82 | 96.5 |
| 5月 | 95 | 96.5 |
| 6月 | 108 | 96.5 |
| 7月 | 101 | 96.5 |
AVERAGE関数による平均値の計算
売上実績の平均値を求める場合、C2セルにAVERAGE関数を入力します。
=AVERAGE($B$2:$B$5)
この数式はB2セルからB5セルまでに入力された売上の平均を計算します。
先頭行に見出しがある表では、AVERAGE関数の範囲をB2から指定する点に注意しましょう。
ドル記号を付けた絶対参照により、数式をC3からC5へコピーしても、計算対象の範囲がずれません。
オートフィルのハンドルをC2セル右下から下へドラッグすれば、すべての行に同一の平均値を表示できます。
数値の表示形式は小数点以下を減らすと、グラフの凡例やデータラベルがすっきりします。
【操作のポイント】AVERAGE関数の対象範囲に空白や文字列が混ざる場合は、実際に平均へ含めたい値を確認しましょう。
平均線用系列のグラフ登録
平均線の列を作成したら、目標線と同様に折れ線グラフのデータ系列へ追加します。
A列、B列、C列をまとめて選択してグラフを作れば、売上と平均線が同時に表示されます。
既存グラフへ追加する場合は、データの選択画面からC列の数値だけを平均線の系列値として指定します。
平均線は売上の上下を判断する基準となるため、月ごとの増減だけでは見えない傾向を把握できます。
平均より上の月が続けば好調、下の月が続けば改善の検討が必要というように、グラフを会議資料にも活用できます。
平均線は固定の目標を表す線ではなく、対象データの変化に合わせて動く比較基準です。
【操作のポイント】平均線の凡例名は平均線のままにし、見る人が目標値と混同しないようにします。
期間別平均と移動平均の使い分け
全期間の平均は全体傾向を見るのに向いていますが、期間が長いと直近の変化が埋もれることがあります。
直近3か月の動きを見たい場合は、移動平均線を利用する方法もあります。
グラフの実績系列を右クリックし、近似曲線の追加から移動平均を選択すると、指定した期間の移動平均を表示できます。
全体平均は基準の高さを比較し、移動平均は変化の流れを読むために使うという違いがあります。
固定目標、全期間平均、移動平均を同時に入れると情報量が増えすぎる場合もあります。
資料の目的に合わせて、表示する基準線を絞り込みましょう。
【操作のポイント】月次の変動が大きいデータでは、3か月または6か月の移動平均を使うと傾向を読み取りやすくなります。
上限線と下限線による許容範囲の表示
続いては、上限値と下限値を追加し、許容範囲を折れ線グラフ上で管理する方法を確認していきます。
| 月 | 実績 | 上限線 | 下限線 |
|---|---|---|---|
| 4月 | 82 | 110 | 85 |
| 5月 | 95 | 110 | 85 |
| 6月 | 108 | 110 | 85 |
許容値データの準備
上限線と下限線を表示する場合は、実績列の右に上限値と下限値の列を追加します。
たとえばC列に110、D列に85を各データ行へ入力すると、上側と下側に横線を作成できます。
品質管理や予算管理では、目標達成だけでなく許容範囲を外れていないかを確認する視点が重要です。
= $F$1
上限値をF1セルに置く場合は、C2セルへこの数式を入力して下方向へコピーします。
下限値も同じ要領で、別セルを絶対参照する数式にすると変更へ対応しやすくなります。
【操作のポイント】上限線と下限線は同じ単位のデータに使い、割合と金額を同一軸で比較しないようにします。
Excel画面での系列と書式の設定
グラフのデザインタブにあるデータの選択をクリックし、上限線と下限線を個別の系列として追加してください。
上限線は警戒を示すオレンジ、下限線は注意を示すグレーや赤の破線にすると役割を伝えやすくなります。
実績線より細い線幅に設定すると、基準線を保ちながら実績データを主役にできます。
【操作のポイント】上限線と下限線の凡例は短くし、グラフを縮小しても読める名称に整えます。
条件付き書式との併用
グラフに基準線を表示するだけでなく、表側にも条件付き書式を設定すると、異常値を見つけやすくなります。
実績データの範囲を選択し、ホームタブの条件付き書式からセルの強調表示ルールを選びます。
たとえば85未満を赤、110より大きい値をオレンジに設定すると、グラフと表で同じ判断基準を共有できます。
グラフは傾向の把握、条件付き書式は具体的な数値の確認に向いています。
どちらか一方だけでは見落としが起こることもあるため、報告資料では併用が効果的です。
【操作のポイント】条件付き書式のしきい値とグラフの上限線、下限線は必ず同じ数値へそろえます。
基準線を見やすくするグラフ書式
続いては、追加した基準線を読みやすく整えるための表示設定を確認していきます。
| 項目 | 実績線 | 基準線 |
|---|---|---|
| 線の種類 | 実線 | 破線 |
| 線の太さ | やや太い | 細め |
縦軸の最小値と最大値
基準線を入れた後にグラフが見づらい場合は、縦軸の範囲を調整します。
縦軸を右クリックして軸の書式設定を開き、境界値の最小値と最大値を設定してください。
縦軸をゼロから始めるかどうかで、実績の変化に対する印象は大きく変わります。
売上や数量の比較ではゼロ始まりが分かりやすい一方、細かな増減を分析する場合はデータ近くまで最小値を上げることもあります。
【操作のポイント】軸の範囲を狭める場合は、資料を読む人が誤解しないよう目盛りを確認します。
凡例とデータラベルの配置
凡例は売上、目標線、平均線などの意味を示すため、削除せずに見やすい位置へ配置します。
グラフ右側に置くと横幅を使いますが、線と名称の対応が分かりやすくなります。
目標線の最後の点だけにデータラベルを表示し、目標100のような数値を示す方法も便利です。
すべての点にラベルを付けるより、判断に必要な値だけを表示する方がグラフは読みやすくなります。
【操作のポイント】凡例名は系列名のセルと連動するため、表の見出しを変更するとグラフ側にも反映されます。
複数基準線の整理
目標線、平均線、上限線、下限線を一つの折れ線グラフへ追加すると、線が多くなります。
色を増やしすぎると重要な実績が埋もれるため、基準線はグレー系や破線を中心に整理しましょう。
特に重要な目標線だけを赤やオレンジで強調し、補助的な平均線は薄い青やグレーにする方法があります。
実績線を最も濃く、目標線を次に目立たせ、補助線を控えめにする順序が基本です。
【操作のポイント】基準線が三本以上になる場合は、グラフを分ける選択も検討しましょう。
基準線が表示されない場合の確認項目
続いては、基準線が表示されない、斜めになる、意図しない位置に出る場合の確認項目を解説していきます。
| 症状 | 主な原因 |
|---|---|
| 基準線が出ない | 系列値の範囲指定漏れ |
| 線が途中で切れる | 空白セルや文字列の混在 |
| 線が斜めになる | 各行の基準値が一致していない |
系列値と元データ範囲
基準線が表示されないときは、まずデータの選択画面を開き、系列値の範囲を確認します。
系列名には見出しセル、系列値には数値だけのセル範囲を指定するのが基本です。
系列値に見出し文字や空白セルが含まれると、グラフが想定どおりに描画されないことがあります。
売上がB2からB13までなら、基準線も同じくC2からC13までにそろえます。
【操作のポイント】元データを追加した後は、グラフの系列範囲が新しい最終行まで広がっているか確認します。
数値形式と空白セル
基準線のセルに100と入力していても、文字列として保存されていると正常に扱われない場合があります。
セルを選択して警告マークが出ていないかを確認し、数値に変換してください。
また、途中に空白セルがあると、グラフの設定によっては線が途切れます。
空白セルの表示方法は、データの選択画面にある非表示および空白セルから調整できます。
【操作のポイント】基準線にはゼロではなく空白を入力した場合、線がゼロまで落ちたように見える設定がないか確認します。
第2軸の混在
実績と基準線の単位が異なる場合、第2軸を使うことがあります。
ただし売上と売上目標のように同じ単位で比較する基準線は、原則として同じ主軸へ表示します。
第2軸へ誤って設定すると、数値が合っていても基準線の高さがずれて見えます。
系列を右クリックし、データ系列の書式設定から第2軸の指定を外すと修正できます。
【操作のポイント】同じ単位を比べる実績線と目標線は、必ず同じ縦軸の目盛りで比較します。
まとめ エクセルの折れ線グラフに基準線を引く方法
エクセルの折れ線グラフへ基準線を引くには、目標線や平均線の数値を元データの列として用意し、グラフの系列へ追加します。
固定目標は同じ数値を各行へ入力し、平均線はAVERAGE関数を絶対参照でコピーする方法が基本です。
上限線と下限線を追加すれば、単なる実績比較だけでなく、許容範囲から外れていないかも判断できます。
基準線の色や破線、縦軸、凡例を整えることで、報告書や会議資料でも伝わるグラフになります。
表示されない場合は、系列値の範囲、空白セル、数値形式、第2軸の設定を順番に確認しましょう。
目標、平均、上限、下限のうち必要な基準だけを選び、実績の変化が最も読み取りやすい折れ線グラフを作成してみてください。
