Excelで数値どうしの関係を確認したいときは、XYグラフである散布図が役立ちます。
売上と広告費、気温と販売数、身長と体重のように、横軸と縦軸の両方が数値データである場合、通常の折れ線グラフよりも正確に傾向を表せる方法です。
一方で、X軸とY軸の列を正しく指定しなければ、意図と異なるグラフになることがあります。
この記事では、散布図の作成手順から、X軸・Y軸の設定、見やすい書式設定、近似曲線の活用までを順番に解説します。
XYグラフ作成のポイントは、横軸に使う数値列と縦軸に使う数値列を準備し、散布図を選び、必要に応じてデータ範囲を修正することです。
エクセルでXYグラフを作成する方法【散布図の挿入】
それではまず、散布図を挿入してXYグラフを作成する基本操作について解説していきます。
| A | B | C | |
|---|---|---|---|
| 1 | 広告費 | 売上 | 来店数 |
| 2 | 10 | 120 | 85 |
| 3 | 20 | 155 | 102 |
| 4 | 30 | 188 | 118 |
| 5 | 40 | 230 | 141 |
| 6 | 50 | 255 | 156 |
散布図に適したデータ配置
XYグラフを作る前に、1行目に見出しを置き、2行目以降に数値を入力します。
上の例では、A列の広告費をX軸、B列の売上をY軸として扱います。
X軸とY軸に使う値は、同じ行どうしが対応するように並べることが重要です。
たとえばA2の10とB2の120は、広告費10に対する売上120という1組のデータです。
A列だけを並べ替えたり、途中の行を削除したりすると、対応関係が崩れて正しい分析ができません。
日付をX軸に使う場合もありますが、散布図では日付が内部的に連番の数値として扱われます。
月ごとの推移を均等な間隔で見せたいだけなら折れ線グラフ、時間の間隔そのものも反映したいなら散布図を選ぶとよいでしょう。
散布図では、左側の列をX値、右側の列をY値として用意すると、データ範囲の指定が分かりやすくなります。
挿入タブから散布図を選ぶ操作
A1からB6までの範囲をドラッグして選択します。
続いて、Excel上部の挿入タブを開き、グラフグループにある散布図またはバブルチャートのアイコンを選択しましょう。
表示される種類の中から、最初はマーカーのみの散布図を選ぶのがおすすめです。
マーカーのみの散布図は、個々のデータがどこに位置するかを確認しやすく、直線や曲線を後から追加することもできます。
線付き散布図を最初から選んでもよいですが、データの並び順によっては線が不自然に行き来して見えるかもしれません。
データの関係を観察する目的なら、まず点だけの散布図を選ぶと判断しやすくなります。
グラフが表示されたら、横方向に広告費、縦方向に売上の目盛りが表示されているかを確認してください。
【操作のポイント】範囲選択には必ず見出し行を含めます。系列名に「売上」などの分かりやすい名前が自動表示されます。
グラフの移動と大きさの調整
作成直後のグラフは、選択したセルの近くに重なるように表示されます。
グラフの外枠をクリックすると、周囲に丸いハンドルが表示されます。
中央付近をドラッグすればグラフ全体を移動でき、四隅のハンドルをドラッグすれば縦横比を保ちながらサイズを変更できます。
データ表とグラフを同じシートに置くと元データを修正しやすい反面、印刷時に窮屈になることがあります。
グラフを大きく見せたい場合は、グラフデザインタブのグラフの移動から新しいシートを選択する方法も便利です。
グラフシートに移すと、散布図だけを大きく表示できるため、会議資料や印刷物の作成にも向きます。
ただし、データ入力と比較しながら調整する段階では、同じワークシートに置くほうが作業しやすいでしょう。
グラフを選択した状態でAltキーを押しながら移動すると、セルの境界に合わせて配置しやすくなります。
XYグラフのX軸とY軸の設定
続いては、横軸と縦軸に使うデータを確認し、意図した組み合わせへ修正する方法を確認していきます。
| A | B | C | |
|---|---|---|---|
| 1 | 気温 | アイス売上 | 飲料売上 |
| 2 | 12 | 48 | 65 |
| 3 | 18 | 72 | 77 |
| 4 | 24 | 126 | 94 |
| 5 | 30 | 182 | 118 |
| 6 | 34 | 210 | 135 |
データの選択画面による系列編集
散布図をクリックし、グラフデザインタブからデータの選択をクリックします。
表示されたデータソースの選択画面では、グラフに含まれる系列を確認できます。
散布図では横軸ラベルを別途指定するのではなく、各系列の編集画面で系列Xの値と系列Yの値を設定します。
散布図のX軸はカテゴリ名ではなく、系列ごとに指定した数値の範囲で決まります。
系列を選択して編集をクリックすると、系列名、系列Xの値、系列Yの値という入力欄が表示されます。
気温とアイス売上の関係を作るなら、系列Xの値にA2からA6、系列Yの値にB2からB6を指定します。
セル範囲の先頭と末尾を含め、X値とY値の件数を一致させることが必要です。
【操作のポイント】X値とY値の範囲は、どちらも見出しを除くデータ行だけを指定します。件数が違うと、エラーや意図しない表示の原因になります。
複数のYデータを同じX軸へ追加
気温に対するアイス売上と飲料売上を比較したい場合は、系列を追加します。
データソースの選択画面で追加をクリックし、系列名にはC1、系列Xの値にはA2からA6、系列Yの値にはC2からC6を指定します。
これにより、同じ気温を横軸として、アイス売上と飲料売上の2系列が一つのグラフに表示されます。
系列が増えると、凡例の色とマーカーを見分けやすくする工夫が必要になります。
系列を右クリックしてデータ系列の書式設定を開けば、マーカーの形、色、サイズを個別に変更できます。
比較する系列は多くても三つ程度に抑えると、散布図の読みやすさを保ちやすくなります。
四つ以上の系列を重ねる必要がある場合は、色だけでなく丸、四角、三角のようにマーカーの形も変えましょう。
複数系列を追加しても、各系列のX値は同じである必要はありません。測定したX値が異なるデータ群も、同じ座標上で比較できます。
軸の入れ替えと数値範囲の見直し
X軸とY軸を入れ替えたいときは、グラフを削除しなくても系列の編集画面で範囲を交換できます。
系列Xの値に入っているセル範囲をYの列へ変更し、系列Yの値をXの列へ変更すれば軸が入れ替わります。
折れ線グラフでは行列の切り替えが使われる場面がありますが、散布図ではX値とY値を明示的に編集するほうが確実です。
また、データの一部だけを分析したいときは、対象の行だけを指定します。
空白セルや文字列が混じっていると、点が欠けたり、エラーが出たりする場合があります。
数値に見えても文字列として保存されているデータは、セル左上の警告表示から数値に変換しておきましょう。
軸の向きに迷ったら、原因や条件をX軸、結果や測定値をY軸に置くと、グラフの意味を説明しやすくなります。
【操作のポイント】軸を入れ替えた後は、グラフタイトルと軸ラベルも必ず見直します。データだけ変えて名称を残すと誤解を招きます。
散布図のデザインと軸ラベルの設定
続いては、XYグラフを読みやすくするためのタイトル、軸ラベル、目盛りの設定を確認していきます。
| A | B | C | |
|---|---|---|---|
| 1 | 作業時間 | 処理件数 | 担当者 |
| 2 | 1.5 | 22 | 佐藤 |
| 3 | 2.0 | 31 | 鈴木 |
| 4 | 2.5 | 40 | 田中 |
| 5 | 3.0 | 46 | 高橋 |
| 6 | 3.5 | 51 | 伊藤 |
グラフタイトルと軸ラベルの追加
グラフをクリックすると、右上にプラス記号のグラフ要素ボタンが表示されます。
このボタンをクリックし、グラフタイトルと軸ラベルにチェックを入れましょう。
グラフタイトルには、何を比較しているかが分かる名称を入力します。
たとえば「作業時間と処理件数の関係」と入力すると、グラフを単独で見た人にも内容が伝わります。
横軸タイトルには「作業時間 時間」、縦軸タイトルには「処理件数 件」のように、単位まで含めると親切です。
グラフタイトルは結論ではなく、対象と比較の内容を簡潔に表す言葉にすると客観性を保てます。
タイトルをクリックすれば直接編集でき、文字数が多すぎる場合は改行して整えることも可能です。
【操作のポイント】軸タイトルには数値の単位を入れます。時間、円、個、パーセントなどを省略しないことが大切です。
最小値と最大値による目盛り調整
軸の目盛りを調整するには、変更したい軸を右クリックして軸の書式設定を開きます。
軸のオプションでは、境界値の最小値と最大値、目盛間隔の主単位を変更できます。
たとえば作業時間が1.5から3.5までなら、横軸の最小値を1、最大値を4、主単位を0.5に設定すると読み取りやすくなります。
Excelの自動設定では、最小値がデータの最小値付近から始まり、差が大きく見えすぎることがあります。
特定の差を強調したいだけで軸の開始点を変えると、受け手に誤った印象を与える可能性があります。
比較資料では、軸の範囲を変更した理由を説明できる設定にすることが大切です。
目盛りの設定例は、最小値 0、最大値 60、主単位 10です。処理件数のようなゼロから意味を持つ値では、縦軸を0から始めると比較が分かりやすくなります。
マーカーとグリッド線の見やすさ
散布図の点が小さく見える場合は、データ系列を右クリックしてデータ系列の書式設定を開きます。
マーカーのオプションから組み込みを選択し、サイズを大きくすると、個々のデータを確認しやすくなります。
点が重なるときは、塗りつぶしを半透明にする方法もあります。
また、グラフ要素ボタンから目盛線を調整できます。
主横目盛線だけを薄い色で表示すると、Y軸の値を読み取りやすくなります。
縦横の目盛線を濃く表示しすぎると、点よりも線が目立つ状態になりがちです。
散布図の主役はデータ点であるため、背景の線や枠線は控えめにすると視認性が上がります。
【操作のポイント】印刷前には、白黒で出力しても系列を区別できるか確認します。色だけに頼らず、マーカーの形も変えると安心です。
近似曲線と相関の確認
続いては、散布図に近似曲線を追加し、数値どうしの関係を読み取る方法を確認していきます。
| A | B | |
|---|---|---|
| 1 | 学習時間 | テスト得点 |
| 2 | 1 | 48 |
| 3 | 2 | 55 |
| 4 | 3 | 63 |
| 5 | 4 | 69 |
| 6 | 5 | 78 |
線形近似曲線の追加
データ点をクリックして系列を選択し、右クリックから近似曲線の追加を選びます。
学習時間と得点のように、Xが増えるほどYもほぼ一定の割合で増える関係では、線形近似を選ぶことが一般的です。
グラフ上に傾いた直線が表示され、全体的な増減の方向を視覚的に確認できます。
近似曲線は個別の点を結ぶ線ではなく、データ全体の傾向を表す線です。
点が直線の近くに集まるほど、線形の関係が比較的強いと考えられます。
ただし、近似曲線が上向きでも、Xの増加がYの増加を直接引き起こしているとは限りません。
近似曲線の種類には、線形、指数、対数、多項式、移動平均などがあります。データの意味と分布に合わせて選ぶことが必要です。
数式と決定係数の表示
近似曲線の書式設定で、グラフに数式を表示する、グラフにR二乗値を表示するにチェックを入れると、分析の補助情報を表示できます。
線形近似の数式は、一般にY＝aX＋bの形で表示されます。
Y＝aX＋bにおいて、aは傾き、bは切片です。
傾きが正ならXの増加に伴ってYが増える傾向、負ならXの増加に伴ってYが減る傾向を示します。
たとえばY＝7.2X＋41.0と表示された場合、学習時間が1時間増えるごとに、得点が平均で約7.2点増える傾向を読み取れます。
R二乗値は、近似式がデータのばらつきをどの程度説明できているかを見る目安です。
値が1に近いほど、近似曲線はデータによく当てはまっています。
R二乗値だけで判断せず、外れ値やデータ数、業務上の意味もあわせて確認しましょう。
【操作のポイント】数式を資料に載せる場合は、小数点以下の桁数を調整します。必要以上に細かい数値は、かえって読みにくくなります。
相関係数をワークシートで求める方法
グラフだけでなく、相関係数をセルに表示して数値で確認することもできます。
たとえばD2セルに次の数式を入力します。
=CORREL(A2:A6,B2:B6)
CORREL関数は、二つの数値範囲の相関係数を返す関数です。
1に近い値は強い正の相関、マイナス1に近い値は強い負の相関、0に近い値は線形の相関が弱い状態を示します。
1行目にヘッダーがあるため、数式ではA1やB1を含めず、A2からA6、B2からB6を指定します。
相関係数は関係の強さを示す指標であり、原因と結果を断定する数値ではありません。
外れ値が一つあるだけで数値が大きく変わる場合もあるため、必ず散布図の形を目で確認する習慣を付けましょう。
【操作のポイント】CORREL関数で指定する二つの範囲は、セル数を同じにします。空白や文字列を含むデータは事前に点検します。
XYグラフで起こりやすいエラーと対処
続いては、散布図の作成時に起こりやすい表示の違和感と対処方法を確認していきます。
| A | B | |
|---|---|---|
| 1 | 距離 | 燃料使用量 |
| 2 | 0 | 0 |
| 3 | 50 | 4.2 |
| 4 | 100 | 8.1 |
| 5 | 150 | 12.8 |
| 6 | 200 | 16.5 |
横軸が連番になる場合の確認
散布図を作ったつもりなのに、横軸が1、2、3、4のような連番になる場合があります。
このケースでは、散布図ではなく折れ線グラフを選んでいるか、X値の範囲が正しく設定されていない可能性があります。
折れ線グラフの横軸はカテゴリ軸であり、数値の間隔を反映する散布図の値軸とは仕組みが異なります。
グラフの種類の変更から散布図を選び直し、データの選択で系列Xの値を確認してください。
距離が0、50、100、150のように不均等な間隔でも正確に表示したい場合は、散布図を使います。
日付や時刻の間隔を正しく反映したい場合にも、散布図が有効です。
【操作のポイント】横軸が連番のときは、最初にグラフ種類を確認します。散布図なら横軸の目盛りは実際の数値に応じて配置されます。
データ点が表示されない場合の対処
グラフ枠は表示されるのに点が見えないときは、セルの値が文字列になっていないかを確認します。
数値が左寄せで表示され、セル左上に緑色の三角形が出ている場合、数値が文字列として保存されている可能性があります。
警告アイコンから数値に変換を選ぶか、VALUE関数で数値化してからグラフに使いましょう。
また、フィルターで行が非表示になっている場合や、エラー値が含まれている場合にも、意図どおり表示されないことがあります。
散布図のマーカー色が白になって背景と同化しているケースもあるため、データ系列の書式設定も確認してください。
グラフの問題に見えても、原因は元データの形式や空白セルにあることが少なくありません。
数値かどうかを確認するには、空いているセルに=ISNUMBER(A2)と入力します。TRUEなら数値、FALSEなら文字列など数値以外の値です。
外れ値と欠損値の扱い
ほかの点から大きく離れた外れ値があると、軸の範囲が広がり、通常のデータが固まって見えることがあります。
外れ値を削除する前に、入力ミス、単位の違い、測定条件の違いがないかを確認しましょう。
たとえば距離をメートルで入力した行とキロメートルで入力した行が混在していれば、数値を統一する必要があります。
欠損値を0で埋めると、実際には観測していないデータが原点付近の点として表示されるため注意が必要です。
未測定のデータは空白にするか、別の表に分ける判断も必要になります。
外れ値を除外する場合は、除外理由を記録し、都合のよい結論に合わせて消さないことが大切です。
【操作のポイント】外れ値があるときは、外れ値を含むグラフと除いたグラフを別に作り、比較しながら判断すると安全です。
まとめ エクセルでXYグラフを作成する方法
ExcelでXYグラフを作成するには、まず1行目にヘッダーを置き、X軸とY軸に使う数値を対応する行へ入力します。
そのうえで、対象範囲を選択し、挿入タブから散布図を選択すれば基本のグラフを作成できます。
散布図では、系列Xの値と系列Yの値を個別に確認することが、正確なグラフ作成の基本です。
軸ラベルには項目名と単位を入れ、必要に応じて最小値、最大値、目盛間隔を調整しましょう。
複数系列を比較するときは、色とマーカーの形を使い分けると、グラフの意味が伝わりやすくなります。
さらに近似曲線、数式、R二乗値、CORREL関数を使えば、散布図から数値の関係をより深く確認できます。
ただし、相関が見られても因果関係とは限りません。
元データの単位、欠損値、外れ値を確認し、グラフの形と業務上の背景をあわせて判断することが大切です。
散布図のX軸とY軸を正しく設定して、Excelでの分析や報告に役立てていきましょう。
