エクセルの散布図では、基準値を示す横線や縦線、目標範囲を示す補助線を追加すると、データの位置関係をひと目で判断しやすくなります。
たとえば売上と広告費、身長と体重、温度と測定値のような数値データを散布図にした場合、目標値や上限値を線で表示できれば、外れ値や改善対象を素早く確認できます。
散布図に線を引く基本は、線用の補助データを表に追加し、そのデータを新しい系列としてグラフへ登録する方法です。
散布図の基準線は、図形の直線を重ねるよりも、系列として追加するほうがデータ変更後も位置がずれにくいという特徴があります。
この記事では、横方向の基準線、縦方向の補助線、複数の目標線を散布図へ追加する手順を、サンプルデータと数式を交えながら解説します。
エクセルの散布図に横方向の基準線を追加する方法
それではまず、散布図に一定の数値を示す横方向の基準線を追加する方法について解説していきます。
| A | B | C | D |
|---|---|---|---|
| 月 | 広告費 | 売上 | 売上目標線 |
| 1月 | 12 | 68 | 80 |
| 2月 | 18 | 85 | 80 |
| 3月 | 25 | 76 | 80 |
散布図の横線は、すべての行に同じY値を持つ補助系列を用意することで作成できます。
売上目標が80で固定なら、補助列へ80を繰り返して入力するだけで横線の元データになります。
横線用の補助列の作成
散布図に基準線を入れるには、まず元データの右側に補助列を作成します。
上記の例では、D1セルに売上目標線と入力し、D2セルからD4セルに80を入力します。
D2セルに入力する数式は =80 です。
目標値を別のセルで管理したい場合は、たとえばF1セルに目標値、F2セルに80を入力し、D2セルへ =$F$2 と入力します。
その後、D2セル右下のフィルハンドルをD4セルまでドラッグすると、同じ目標値を各行へコピーできます。
絶対参照のドル記号を付けると、オートフィル後も参照先のF2セルが変化しません。
月ごとに異なる目標値を設定する場合は、D列に月別の目標値を直接入力します。
一方で、水平な基準線として表示したい場合は、D列のすべての値を同じ数値にそろえることが大切です。
補助列の値が途中で空白になると、グラフ上の線が意図しない形で切れる場合があります。
【操作のポイント】横線のY値は同じ数値で統一し、基準値を別セルで管理する場合は絶対参照を利用します。
散布図への新しい系列の追加
続いて、作成済みの散布図へ横線用データを系列として追加していきます。
散布図をクリックし、グラフのデザインタブからデータの選択をクリックします。
表示された画面で追加を選び、系列名にはD1セル、系列Xの値には広告費の範囲であるB2からB4、系列Yの値にはD2からD4を指定します。
散布図では、X値とY値を別々に指定する必要があるため、横線のX値には元データと同じ横軸用数値を指定しましょう。
系列Xの値にB2からB4を入れることで、広告費の最小値から最大値までに対応した横線が表示されます。
データ数が多い表では、範囲を必要な最終行まで広げて指定してください。
追加後にOKをクリックすると、散布図に新しい点または線の系列が加わります。
この段階ではマーカーだけが表示されることもありますが、次の書式設定で線として整えられます。
【操作のポイント】横線用系列のX値には、元の散布図で使うX軸データと同じ範囲を指定します。
マーカーを消して基準線に整える設定
新しく追加した系列をクリックし、右クリックメニューからデータ系列の書式設定を選択します。
塗りつぶしと線の項目で線を実線に変更し、線の色と太さを設定します。
基準線であることを分かりやすくするには、元データの点より目立ちすぎない赤色、オレンジ色、またはグレー系の色が便利です。
次にマーカーの項目を開き、マーカーのオプションでなしを選択します。
マーカーを非表示にすると、補助系列の点が消え、横方向へ伸びる基準線だけを見せられます。
線の種類を破線にすると、実績データと区別しやすい表現になります。
売上目標のように重要度が高い線は、太さを少し上げ、凡例名を売上目標にしておくと読み手にも伝わります。
基準線は実線または破線にし、マーカーはなしに設定すると、散布図本来のデータ点を邪魔しにくくなります。
【操作のポイント】基準線の系列はマーカーなしに設定し、色と破線の使い分けで実測値との違いを明確にします。
エクセルの散布図に縦方向の補助線を追加する方法
続いては、特定のX値を示す縦方向の補助線を散布図へ入れる方法を確認していきます。
| A | B | C | D | E |
|---|---|---|---|---|
| 月 | 広告費 | 売上 | 広告費基準X | 縦線用Y |
| 1月 | 12 | 68 | 20 | 50 |
| 2月 | 18 | 85 | 20 | 100 |
縦線は、同じX値を2行に用意し、そのX値に対してY軸の下端と上端になる値を設定して作成します。
横線と縦線では、固定する値が異なることを押さえると、補助データの作り方を迷いません。
縦線の始点と終点になる数値の設定
縦方向の基準線を広告費20の位置へ表示したい場合、D2セルとD3セルには20を入力します。
次にE2セルにはグラフのY軸下端となる値、E3セルには上端となる値を入力します。
今回の売上データが50から100程度の範囲にあるなら、E2セルには50、E3セルには100を設定できます。
D2セルとD3セルには =20 を入力し、E2セルには =50、E3セルには =100 を入力します。
縦線の上下端は、表示中のY軸の最小値と最大値に合わせると、グラフ領域を縦に貫く線になります。
Y軸の目盛り範囲を後から変更したときは、縦線用Yの値も合わせて調整する必要があります。
Y軸を自動設定にしている場合は、散布図を確認してから少し余裕のある上下端を入力すると安全です。
たとえば最小値が45、最大値が110なら、縦線のY値を45と110に設定します。
【操作のポイント】縦線は同一のX値を2つ用意し、Y値にはグラフの下端と上端に対応する数値を入力します。
縦線データの系列登録
散布図を選択し、グラフのデザインからデータの選択を開きます。
追加をクリックしたら、系列名には広告費基準線など分かりやすい名称を指定します。
系列Xの値にはD2からD3、系列Yの値にはE2からE3を設定します。
横線のときと違い、今回のX値は20と20のように同じ数値です。
2点のX座標が一致し、Y座標だけが異なるため、Excelはその2点を結ぶ縦線として描画します。
設定後、グラフに追加された系列が線ではなく点だけに見える場合は、グラフの種類を確認します。
散布図の種類は、直線とマーカーを表示する散布図、または直線を表示する散布図を選択しましょう。
平滑線付き散布図を使用すると意図しない曲線になることがあるため、基準線には直線タイプが向いています。
【操作のポイント】縦線系列はX値を同じ数値で2回指定し、散布図の直線タイプで表示します。
縦線の書式とラベル表示
縦線を選択してデータ系列の書式設定を開き、線を実線または破線に変更します。
広告費の上限や基準額を表す線なら、横方向の売上目標線とは別の色を使うと判断しやすくなります。
たとえば売上目標を赤の破線、広告費基準を青の破線にすると、縦横の意味を見分けやすくなります。
凡例を見なくても意味が伝わるようにしたい場合は、縦線系列の上端にデータラベルを追加する方法があります。
データラベルを右クリックしてデータラベルの書式設定を開き、値ではなく系列名を表示する設定にすると、広告費基準といった名称を示せます。
縦線のラベルは上端に置くと、散布図のデータ点や軸ラベルと重なりにくくなります。
ラベルが重なる場合は、ドラッグして少し横へ移動させることもできます。
【操作のポイント】横線と縦線は色や破線を変え、必要なら系列名をデータラベルとして上端に表示します。
散布図の基準線と補助線を数式で管理する方法
続いては、基準値を変更したときに散布図の線も自動で更新されるよう、補助データを数式で管理する方法を確認していきます。
| F | G | H |
|---|---|---|
| 基準項目 | 設定値 | 用途 |
| 売上目標 | 80 | 横基準線 |
| 広告費基準 | 20 | 縦補助線 |
基準値を補助データへ直接入力すると、目標変更のたびに複数セルを修正する必要があります。
そこで、設定値を1か所にまとめ、補助列から参照する形にすると管理が簡単です。
絶対参照による横基準線の自動更新
売上目標をG2セルで管理する場合、横線用のD2セルには =$G$2 と入力します。
数式を入力したD2セルを選択し、右下のフィルハンドルを最終行まで下へドラッグします。
オートフィル後のD3セルやD4セルにも =$G$2 が入るため、どの行でも同じ目標値が表示されます。
G2セルの80を90へ変更すると、散布図に追加した横基準線も自動的に90の位置へ移動します。
横基準線用の数式は = $G$2 のように、列と行の両方を固定する絶対参照が基本です。
相対参照で =G2 と入力してコピーすると、D3ではG3、D4ではG4を参照してしまいます。
設定値を1セルに集約したいケースでは、必ず絶対参照を使いましょう。
なお、グラフが数式の変更を反映しないように見える場合は、数式タブの計算方法が自動になっているか確認します。
【操作のポイント】横基準線用の補助列は絶対参照で統一し、設定値セルだけを修正できる状態にします。
軸の最小値と最大値を使う縦補助線
縦補助線では、同じX値を2回使いながら、Y値にはグラフの上下端を設定します。
たとえば広告費基準をG3セルに置いた場合、縦線用のX値セルには =$G$3 を入力します。
縦線用Yの下端と上端は、グラフのY軸で決めた最小値と最大値を参照する構成にすると便利です。
たとえばG5セルに50、G6セルに100を入力し、縦線用Yには =$G$5 と =$G$6 を指定します。
軸の境界値と補助線の端点をそろえると、縦線がプロットエリアの端まで自然に届きます。
Y軸の目盛りを固定するには、縦軸を右クリックし、軸の書式設定から境界値の最小値と最大値を入力します。
このとき、補助データに入力した上下端と軸設定の境界値を同じにしてください。
目盛りを自動設定に戻す可能性がある場合は、少し広めのY値を指定して、線が途中で短く見えないようにする方法もあります。
【操作のポイント】縦補助線の端点は、Y軸の最小値と最大値に合わせて設定すると見栄えが安定します。
IF関数を使った条件付き補助線
データのうち特定条件を満たす部分だけに補助線を表示したい場合は、IF関数を活用できます。
たとえば広告費が20以上のときだけ目標売上80を表示したい場合、D2セルへ =IF(B2>=20,$G$2,NA()) と入力します。
=IF(B2>=20,$G$2,NA()) は、B2が20以上ならG2の目標値を返し、それ以外ではグラフに表示しないエラー値を返す数式です。
NA関数は #N/A エラーを返し、通常はグラフ上でその点を描画しません。
空白セルではなくNA関数を使うと、不要な区間へ線がつながる現象を防ぎやすくなります。
数式を最終行までコピーすると、条件を満たした範囲にだけ補助線を表示できます。
売上が目標を超えた月だけを別色で示す系列や、許容範囲の外にある測定値だけを強調する系列にも応用可能です。
【操作のポイント】条件付きの線にはIF関数とNA関数を組み合わせ、表示したい範囲だけに値を返します。
散布図の複数基準線と許容範囲の表示
続いては、上限値と下限値、目標値など複数の線を散布図へ表示する考え方を確認していきます。
| A | B | C | D | E |
|---|---|---|---|---|
| 測定回 | X値 | 測定値 | 上限線 | 下限線 |
| 1 | 10 | 72 | 90 | 60 |
| 2 | 20 | 88 | 90 | 60 |
複数の基準線を使うと、単に目標を確認するだけでなく、許容範囲内かどうかを視覚的に判断できます。
上限線と下限線の補助列
上限値と下限値が一定なら、横線用の補助列を2つ作成します。
上限線の列には90、下限線の列には60を最終行まで入力します。
設定値を別セルに置く場合は、上限線に =$H$2、下限線に =$H$3 のような絶対参照を使えます。
上限と下限を同時に表示すると、点が許容範囲を外れたかどうかを色分けなしでも把握しやすくなります。
線の色は、上限と下限を同じ色の破線にそろえると、ひとつの許容帯として認識されやすくなります。
目標値を中央に置く場合は、目標線だけ色を変えると役割を区別できます。
【操作のポイント】上限線と下限線は別系列として追加し、同系色の破線で許容範囲を表現します。
許容範囲外のデータ点の強調
散布図の中で規格外や注意対象の点だけを目立たせたい場合は、通常系列とは別に強調用系列を作ります。
測定値がC2セル、上限がH2セル、下限がH3セルなら、強調用のY値には =IF(OR(C2>$H$2,C2<$H$3),C2,NA()) と入力できます。
この数式は測定値が上限超過または下限未満のときだけ元の値を返し、範囲内ならグラフに描画しない値を返します。
強調用系列のX値には元データのX値を指定し、マーカーだけを赤色で大きく設定します。
通常の点を消す必要はなく、同じ座標へ赤いマーカーを重ねることで異常値だけを強調できます。
この方法は品質管理の測定結果、目標未達の店舗、予算超過の案件などに活用できます。
【操作のポイント】範囲外判定にはIF関数とOR関数を使い、該当する点だけを別系列で重ねます。
凡例と線種による見やすさの調整
基準線が増えると、散布図は情報量が多くなる一方で、何を表す線なのか分かりにくくなることがあります。
系列名は系列1や系列2のままにせず、目標売上、上限、下限、広告費基準のように具体的な名称へ変更しましょう。
凡例はグラフの右側または下側へ配置し、データ点と重ならない位置を選びます。
実績はマーカー、基準値は破線、注意値は太い線というように表示ルールを統一すると、グラフの読み取り速度が上がります。
補助線の数が多い場合は、不要な系列を一時的に非表示にすることも有効です。
グラフフィルターのボタンを使えば、元データを削除せずに系列の表示と非表示を切り替えられます。
【操作のポイント】複数線では系列名、線種、色の役割を統一し、凡例で意味を明確にします。
散布図で線が表示されない場合の確認項目
続いては、基準線や補助線を追加したのに散布図へ正しく表示されない場合の確認項目を解説していきます。
| 確認箇所 | 主な原因 | 対処 |
|---|---|---|
| 系列のX値 | 範囲の指定漏れ | X値とY値の個数をそろえる |
| グラフの種類 | マーカーのみ表示 | 直線付き散布図に変更する |
| 軸の範囲 | 線が表示領域外 | 境界値を見直す |
補助データが正しくても、系列設定や軸の範囲によっては線が見えないことがあります。
X値とY値の個数の一致
散布図の系列では、X値とY値が一対一で対応します。
横線用のX値が3セルなのにY値が2セル、または縦線用のX値が2セルなのにY値が3セルといった状態では、意図した線を作れません。
データの選択画面で指定するX値とY値は、必ず同じ個数のセル範囲にそろえます。
横線なら元データと同じ件数、縦線なら通常は2件のX値とY値を用意します。
表の途中に空白行がある場合も、意図しない切れ目や表示不良につながることがあります。
【操作のポイント】散布図の補助系列では、X値とY値のセル数を必ず一致させます。
散布図の種類と線の設定
追加した系列が点だけに見える場合は、グラフの種類がマーカーのみの散布図になっている可能性があります。
グラフを選択し、グラフのデザインからグラフの種類の変更を開きます。
散布図の中から直線とマーカーを表示する形式、または直線を表示する形式を選択します。
系列単位で設定できる場合は、基準線の系列だけを直線付き散布図に変更することも可能です。
補助線は直線、実績値はマーカー付きという組み合わせにすると、情報の役割が分かれます。
さらにデータ系列の書式設定で線なしが選ばれていないか、透明度が高すぎないかも確認しましょう。
【操作のポイント】線が見えないときは、散布図の種類とデータ系列の線設定を順番に確認します。
軸範囲と第2軸の確認
基準線の値がY軸の表示範囲より上または下にあると、線はグラフ内に表示されません。
たとえばY軸の最大値が100なのに目標線を120に設定した場合、目標線は見えない状態になります。
縦軸を右クリックして軸の書式設定を開き、境界値の最小値と最大値を確認してください。
また、意図せず第2軸に系列が設定されていると、主軸の目盛りとは異なる位置に描画されることがあります。
補助線と実績データは、原則として同じ主軸へ配置すると比較しやすくなります。
第2軸を使う必要がない場合は、データ系列の書式設定から主軸を選択します。
【操作のポイント】基準線の値が軸範囲内にあるか、主軸と第2軸のどちらに描かれているかを確認します。
散布図の基準線・補助線を業務で活用する場面
続いては、散布図の線を実務でどのように活用できるかを確認していきます。
| 活用場面 | 横基準線 | 縦補助線 |
|---|---|---|
| 売上分析 | 目標売上 | 広告費の基準額 |
| 品質管理 | 規格上限と規格下限 | 工程条件の基準値 |
| 人事評価 | 目標成果 | 必要な活動量 |
散布図は2つの数値の関係を見るグラフですが、基準線を追加すると行動の判断材料として使いやすくなります。
売上と広告費の目標管理
広告費をX軸、売上をY軸にした散布図では、横線に売上目標、縦線に広告費の上限を設定できます。
右上の領域に点が集まる場合、広告費はかかっているものの売上目標を達成している状態です。
左上にある点は、比較的少ない広告費で目標売上を達成した効率の良い施策かもしれません。
右下にある点は、広告費が基準を超えているのに売上が目標未達という確認対象になります。
縦線と横線でグラフを4つの領域に分けると、データの優先順位を直感的に整理できます。
【操作のポイント】売上目標と広告費基準を交差させ、施策ごとの優先順位を散布図上で判断します。
品質管理における規格値の可視化
製品の寸法、重量、強度、温度などの測定値を散布図で管理する場合、上限規格と下限規格を横線で表示できます。
測定順やロット番号をX軸にし、測定値をY軸にすると、規格外の測定結果がどの時点で発生したかを追いやすくなります。
工程条件の変更時点を縦線で示せば、変更前後でデータのばらつきがどう変わったかも確認できます。
上限と下限の間に点があっても、線へ近づく傾向が続く場合は早めの確認が必要です。
規格線は合否判定だけでなく、値の偏りや工程変化を早く発見するための視覚的な目安になります。
【操作のポイント】品質管理では規格上限と下限を横線で示し、工程変更の位置を縦線で記録します。
会議資料での説明力の向上
会議資料では、数表だけでは重要な数値の位置関係を伝えにくいことがあります。
散布図へ目標線や許容範囲を追加すると、どのデータが基準を満たしているかを短時間で共有できます。
ただし、線を増やしすぎると見にくくなるため、資料の目的に必要な基準だけを残しましょう。
色数も実績、目標、注意の3種類程度に抑えると、印刷時やプロジェクター投影時にも読み取りやすくなります。
グラフタイトルには、売上と広告費の関係、測定値と規格範囲のように、何を比較しているかを具体的に記載します。
【操作のポイント】会議用の散布図では、判断に必要な線だけを残し、色と凡例を簡潔に整えます。
まとめ エクセルの散布図に基準線・補助線を引く方法
エクセルの散布図に線を引くには、基準線や補助線のためのデータを表へ追加し、新しい系列としてグラフに登録します。
横方向の基準線は、X値に元データのX軸範囲を指定し、Y値には同じ基準値を並べることで作成できます。
縦方向の補助線は、同じX値を2つ用意し、Y値にグラフの最小値と最大値を設定する方法が基本です。
設定値を別セルで管理し、絶対参照の数式で補助列へ反映すれば、目標変更後も散布図の線が自動更新されます。
上限線、下限線、目標線を組み合わせると、許容範囲や注意すべきデータ点を分かりやすく示せます。
線が表示されない場合は、X値とY値のセル数、散布図の種類、軸の範囲、主軸か第2軸かを順番に確認しましょう。
散布図の基準線と補助線を活用し、数値の比較や業務上の判断をより分かりやすく進めていきましょう。
