Excelの散布図は、2つの数値の関係をXY座標上に点として並べ、売上と広告費、気温と来店者数、勉強時間と得点のようなデータの傾向を見つけるためのグラフです。
折れ線グラフとは異なり、横軸と縦軸のどちらも数値として扱えるため、数値同士の相関関係を正確に確認したい場面で役立ちます。
この記事では、1行目に見出しがある表を例に、散布図の作成、XY座標の設定、近似曲線、相関係数の表示までを順番に解説します。
散布図を作るときは、横軸に置く説明側の数値と、縦軸に置く結果側の数値を同じ行に対応させることが基本です。
たとえば広告費をA列、売上をB列に並べると、各行が1つの観測データとして散布図に表示されます。
操作後に点の位置が意図と違う場合でも、データ範囲や系列を修正すれば整えられます。
エクセルで散布図を作成する方法
それではまず、Excelで散布図を作成する基本操作について解説していきます。
| 広告費 | 売上 |
|---|---|
| 10 | 120 |
| 15 | 145 |
| 20 | 171 |
| 25 | 188 |
| 30 | 220 |
この表ではA列の広告費をX値、B列の売上をY値として扱います。
1行目の見出しを含めて、途切れない長方形の範囲を選択することが、最初の失敗を避けるポイントです。
データ表の準備
散布図にする前に、比較したい2項目を隣り合う列に配置しましょう。
行ごとの対応が崩れると、広告費10に対する売上120という関係が正しく描画されません。
空白行や文字列が途中に混在すると、Excelが系列を認識しにくくなることがあります。
数値は半角数字で入力し、単位はセルの表示形式で整えるか、見出しに記載すると見やすくなります。
広告費が千円単位なら、見出しを広告費 千円のようにしておくと、グラフを見た人にも尺度が伝わります。
散布図向けの表は、A列に横軸の値、B列に縦軸の値を置く形がもっとも分かりやすい構成です。
日付を横軸にしたい場合は、日付がExcel上で文字列ではなく日付データとして認識されているかも確認しましょう。
散布図の挿入
A1からB6をドラッグして選択したら、リボンの挿入タブを開きます。
グラフグループにある散布図またはバブルチャートのアイコンを選び、散布図をクリックします。
最初は、マーカーのみが表示される散布図を選ぶと、数値同士の関係を素直に読み取れます。
選択が完了すると、ワークシート上に横軸が広告費、縦軸が売上のグラフが挿入されます。
点が右上へまとまっていれば、広告費が増えるほど売上も増える傾向を視覚的に確認できます。
グラフの配置と大きさの調整
挿入されたグラフは外枠をドラッグすると移動でき、四隅のハンドルをドラッグすると大きさを変えられます。
横軸と縦軸の数値が読める程度に広げると、点の密集や外れ値が判断しやすくなります。
グラフタイトルをクリックして、広告費と売上の関係のような内容に変更しておくと、資料へ貼り付けた後も意味が伝わります。
印刷や報告書に使う場合は、表とグラフを近くに配置すると元データとの照合も容易です。
【操作のポイント】散布図はデータの入力順ではなく、X値とY値の組み合わせで点が配置されます。
XY座標の設定と系列の修正
続いては、横軸と縦軸に使う値を確認し、XY座標を意図どおりに設定する方法を解説していきます。
| A列 X値 | B列 Y値 |
|---|---|
| 10 | 120 |
| 15 | 145 |
| 20 | 171 |
散布図では、左側の列が自動的にX値、右側の列がY値になるのが基本です。
ただし列の順番が違う場合や、離れた列を使う場合には、系列の編集画面で指定し直せます。
データの選択画面
グラフをクリックし、グラフのデザインタブからデータの選択をクリックします。
表示された画面には系列一覧があり、通常は系列1として1つのデータ系列が登録されています。
この系列を選んで編集をクリックすると、系列名、系列Xの値、系列Yの値を個別に確認できます。
表の見出しが正しく含まれていれば、系列名には売上などの項目名が自動で入ります。
複数の売上データを比較する場合は、追加をクリックして別の系列を登録することも可能です。
X値とY値の指定
系列の編集画面で、系列Xの値には広告費のセル範囲であるA2からA6を指定します。
系列Yの値には売上のセル範囲であるB2からB6を指定します。
X値とY値は必ず同じ件数にそろえる必要があり、片方だけ5件、もう片方が4件という指定は正しく表示されません。
離れた場所にあるデータを使う場合でも、系列Xの値と系列Yの値にそれぞれの範囲を入力すれば散布図を作成できます。
系列Xの値がA2:A6、系列Yの値がB2:B6なら、10と120、15と145のように同じ行の数値が1点として結び付けられます。
軸の範囲と表示単位
横軸または縦軸を右クリックして軸の書式設定を選ぶと、最小値、最大値、目盛間隔を変更できます。
たとえば広告費が10から30の範囲なのに横軸が0から100まで広がっている場合は、最小値を10、最大値を30に近づけると点の差が読み取りやすくなります。
数値の範囲を極端に狭めると変化が大きく見えやすいため、資料では軸の設定も確認しましょう。
軸ラベルに広告費 千円、売上 万円などの単位を補うと、読者が数値を誤解しにくくなります。
【操作のポイント】横軸と縦軸が逆になったと感じたら、グラフの種類を変えるのではなく系列Xの値と系列Yの値を見直します。
相関を確認する近似曲線と相関係数
続いては、散布図から傾向を読み取り、近似曲線と相関係数を表示する方法を確認していきます。
| 広告費 | 売上 | 傾向 |
|---|---|---|
| 10 | 120 | 右上がり |
| 20 | 171 | 右上がり |
| 30 | 220 | 右上がり |
点が右上がりに並ぶ場合は正の相関、右下がりに並ぶ場合は負の相関、ばらばらなら相関が弱い可能性があります。
ただし、相関があることだけでは、片方がもう片方の原因であるとは判断できません。
近似曲線の追加
散布図の点をクリックして選択し、右クリックメニューから近似曲線の追加を選択します。
多くのケースでは線形近似を選ぶと、データ全体の平均的な増減を1本の直線で表せます。
近似曲線は個々の点を結ぶ線ではなく、全体の傾きを示すための線です。
季節性や急な変化があるデータでは、移動平均や多項式近似が適することもありますが、まずは線形で確認すると理解しやすいでしょう。
近似曲線の傾きが正ならX値の増加に伴ってY値が増える傾向、負なら減る傾向を示します。
数式と決定係数の表示
近似曲線の書式設定では、グラフに数式を表示する、およびグラフにR二乗値を表示するにチェックを入れられます。
表示された数式がy＝3.2x＋88のような形なら、広告費が1増えたときに売上が平均で約3.2増えるという読み方ができます。
R二乗値は近似直線がデータのばらつきをどの程度説明しているかを見る指標です。
R二乗値が1に近いほど、直線的な関係に近いと考えられます。
ただし、少ないデータだけで高い値が出た場合は、判断を急がずデータ件数や外れ値も確認しましょう。
CORREL関数による相関係数
相関係数そのものをセルに求めるには、CORREL関数を使います。
=CORREL(A2:A6,B2:B6)
この数式はA2からA6の広告費と、B2からB6の売上の相関係数を返します。
相関係数はマイナス1から1までの値で、1に近いほど強い正の相関、マイナス1に近いほど強い負の相関、0に近いほど線形の相関が弱い状態です。
散布図で見た印象とCORREL関数の数値を合わせて確認すると、判断の根拠が明確になります。
【操作のポイント】近似曲線は傾向の可視化、CORREL関数は相関の数値化に向いています。
散布図の見やすさを高める書式設定
続いては、作成した散布図を資料で使いやすくする書式設定を確認していきます。
| 設定項目 | 整える内容 |
|---|---|
| グラフタイトル | 比較対象を明記 |
| 軸ラベル | 項目名と単位を記載 |
| マーカー | 色と大きさを調整 |
見た目を整える目的は装飾ではなく、データの意味を誤解なく伝えることです。
グラフタイトルと軸ラベル
グラフ右上のプラス記号から軸ラベルにチェックを入れると、横軸と縦軸の説明を追加できます。
横軸には広告費 千円、縦軸には売上 万円のように、項目名と単位を具体的に入力しましょう。
タイトルは単に散布図とするより、広告費と売上の関係のように内容を示す表現が適しています。
これにより、グラフだけが別の資料へ転記されても意味を理解しやすくなります。
マーカーと目盛線
データ系列を右クリックしてデータ系列の書式設定を開くと、マーカーの色、枠線、サイズを変更できます。
複数系列がある場合は、青と橙のように区別しやすい色を選ぶと比較しやすくなります。
目盛線は多すぎると点が埋もれるため、主要な目盛線だけを薄い色で残すのがおすすめです。
マーカーを必要以上に大きくしないことも、点が重なるデータでは重要です。
データラベルと凡例
特定の点だけを説明したい場合は、その点を右クリックしてデータラベルの追加を選びます。
すべての点にラベルを付けると、データ件数が多い散布図では文字が重なりやすくなります。
外れ値や注目すべき事例だけにラベルを付けると、読み手の視線を自然に導けます。
系列が1つだけなら凡例は省略してもよく、複数系列なら名称が内容に合っているか確認します。
【操作のポイント】タイトル、軸ラベル、単位を先に整えると、グラフの読み間違いを大きく減らせます。
散布図で起こりやすい問題と対処
続いては、散布図を作成したときによくある表示上の問題と対処を解説していきます。
| 症状 | 確認する箇所 |
|---|---|
| 点が表示されない | 数値形式と範囲 |
| 軸が逆 | X値とY値 |
| 点が重なる | マーカーと軸範囲 |
点が表示されない場合
散布図を挿入しても点が見えないときは、対象セルが数値ではなく文字列になっている可能性があります。
セルの左上に緑の三角形が出ている場合は、数値が文字列として保存されていることがあります。
エラー表示から数値に変換するか、VALUE関数で数値化してから散布図を作成しましょう。
空白セルやエラー値が含まれていないかも確認が必要です。
横軸と縦軸が想定と違う場合
折れ線グラフでは横軸が項目軸になりやすいため、数値の間隔を正確に反映したい場合は散布図を選びます。
散布図でも軸が逆なら、データの選択から系列を編集し、X値とY値の参照範囲を入れ替えます。
列を並べ替えなくても、系列の編集だけで修正できるため、元表を壊さずに済みます。
修正後は最小の値と最大の値が軸に反映されているかを確認しましょう。
外れ値と重なったデータ
他の点から大きく離れた点は、入力ミスではなく重要な外れ値である可能性があります。
すぐに削除せず、データの発生日、単位、入力桁を確認して原因を調べましょう。
同じ座標に複数のデータが重なる場合は、マーカーを少し小さくするか、透明度を設定すると密度が分かりやすくなります。
外れ値を除外したグラフを作る場合は、除外条件を資料内に明記することが大切です。
【操作のポイント】見えない点は書式より先に、数値形式、参照範囲、空白セルを確認します。
まとめ エクセルで相関とXY座標を表示する散布図の作成方法
Excelの散布図は、横軸と縦軸のどちらにも数値を使い、2つの項目の関係を確認できるグラフです。
まずは1行目に見出しを置いた表を作り、A列をX値、B列をY値として範囲選択し、挿入タブから散布図を選びましょう。
点の位置がおかしいときは、系列Xの値と系列Yの値を確認することが解決への近道です。
近似曲線を追加すれば全体の傾向を見やすくでき、CORREL関数を使えば相関係数を数値で確認できます。
=CORREL(A2:A6,B2:B6)のように入力すると、対応する2列の相関係数を求められます。
軸ラベル、単位、グラフタイトルも整えれば、作成した散布図は分析用だけでなく報告資料にも活用できます。
相関だけで原因を断定せず、元データの件数、外れ値、業務上の背景も合わせて読み解いていきましょう。
