Excelで数値の分布や個々のデータ位置を見やすくしたいとき、ドットプロットは有効なグラフです。
ただし、Excelにはドットプロットという名前のグラフ種類が常に表示されるわけではなく、目的に合わせて散布図や横棒グラフを使い分ける必要があります。
数値の位置を座標として示すなら散布図、同じ値の件数を積み上げて見せるなら補助列を使った散布図が基本です。
データの並び方、軸の設定、点の追加方法を順に押さえると、比較しやすいドットプロットを作成できます。
この記事では、1行目に見出しがあるサンプルデータを用い、散布図でドットプロットを作る方法から点の見た目の整え方まで解説します。
Excelでドットプロットを散布図として作る方法
| 商品名 | 評価点 | 表示位置 |
|---|---|---|
| A商品 | 72 | 1 |
| B商品 | 85 | 2 |
| C商品 | 78 | 3 |
それではまず、散布図を使って基本のドットプロットを作る手順について解説していきます。
横軸に比較したい数値、縦軸に各データを区別するための連番を置くと、横方向に点が並ぶドットプロットになります。
散布図に必要なデータ列
散布図では、横軸用と縦軸用の数値が必要です。
サンプルではB列の評価点を横軸、C列の表示位置を縦軸に利用します。
商品名のような文字列は散布図の軸には直接使えないため、まずは1、2、3のような連番を補助列として用意します。
C2セルに1、C3セルに2を入力し、2セルを選択してフィルハンドルを下へドラッグすると、連番を作成できます。
データ数が多い場合は、C2セルに次の数式を入力する方法も便利です。
=ROW()-1
この数式は現在の行番号から1を引くため、見出しが1行目にある表では2行目から1、3行目から2と自動で連番になります。
数式を下方向へオートフィルすれば、追加データにも同じルールを適用できます。
散布図の挿入操作
B1からC4までのように、数値データと見出しを含む範囲を選択します。
Excel上部の挿入タブを開き、グラフグループから散布図またはバブルチャートの挿入を選びます。
表示された種類のうち、線がないマーカーのみの散布図をクリックしましょう。
グラフ内に複数の点が表示されれば、基本のドットプロットは完成です。
初期状態で線が表示された場合でも、あとから系列の書式設定で線をなしに変更できます。
散布図は数値の間隔を正確に反映するため、72点と85点の差を視覚的に比べやすい点が特徴です。
縦軸を見やすくする調整
縦方向の連番は、点を重ならないように置くための補助情報です。
縦軸を右クリックして軸の書式設定を開き、最小値を0、最大値をデータ件数に近い値、主単位を1に設定します。
上から順番に商品を表示したいときは、軸を反転する設定を利用すると見やすくなります。
縦軸の数値を読ませる必要がない場合は、ラベルを非表示にしても問題ありません。
その代わりに、各点へ商品名のデータラベルを表示すると、何を表す点なのかを把握しやすくなります。
【操作のポイント】散布図では文字列ではなく数値を軸に指定し、商品名はデータラベルとして表示すると整理しやすくなります。
散布図に点を追加する手順
| 商品名 | 評価点 | 表示位置 |
|---|---|---|
| A商品 | 72 | 1 |
| B商品 | 85 | 2 |
| C商品 | 78 | 3 |
| D商品 | 91 | 4 |
続いては、完成した散布図へ新しいデータ点を追加する操作を確認していきます。
元データ範囲を広げる方法
グラフ作成後にD商品を追加した場合、元の表の下に商品名、評価点、表示位置を入力します。
グラフをクリックし、グラフのデザインタブからデータの選択を開きます。
グラフデータの範囲を、追加した行まで含むように変更してください。
既存の系列を編集してXの値とYの値の参照範囲を同じ行数まで拡張することが重要です。
評価点だけを増やして表示位置を増やさないと、散布図の座標が対応しません。
Excelテーブルとして表を作成しておくと、隣接する行への入力に合わせて参照範囲が自動拡張される場合があります。
系列を新規に追加する方法
既存の点と別の色で、たとえば前年の評価点を表示したい場合は、新しい系列を追加します。
データの選択画面で追加をクリックし、系列名には前年、系列Xの値には前年の評価点列、系列Yの値には表示位置列を指定します。
これにより、同じ商品位置に今年と前年の点を並べて比較できます。
比較対象ごとに系列を分けると、凡例と色分けを使った説明がしやすくなります。
ただし、点が重なると片方が隠れることがあります。
重なりが多いときは、片方の系列のY値に0.15などの小さな差を加える方法が有効です。
=C2+0.15
この補助列を前年用のY値にすれば、横方向の数値比較を保ちながら縦方向だけ少しずらせます。
点を手作業で追加しない理由
散布図上で見えている点をクリックして移動させると、元データとの関係が崩れるおそれがあります。
グラフは表の数値から自動作成される状態に保つことが、更新ミスを防ぐ基本です。
追加する値は必ずセルに入力し、系列の参照範囲を広げる流れを守りましょう。
表の値を修正すればグラフも更新されるため、資料の再利用にも役立ちます。
点が表示されないときは、空白セル、文字列として保存された数値、X値とY値の件数差を確認してください。
【操作のポイント】点の追加はグラフ上ではなく元データ表で行い、X値とY値の範囲を必ずそろえます。
同じ値を積み上げるドットプロットの作成
| 得点 | 出現順 | 縦位置 |
|---|---|---|
| 70 | 1 | 1 |
| 70 | 2 | 2 |
| 80 | 1 | 1 |
続いては、同じ数値を縦に積み上げ、分布を読み取るドットプロットを確認していきます。
同じ得点が複数ある場合、横軸を得点、縦軸をその得点が何回目に現れたかという順位にすると、点の重なりを避けられます。
出現順を数式で求める方法
B2セルには、同じ得点が上から何回目かを数える数式を入力します。
=COUNTIF($A$2:A2,A2)
COUNTIF関数は指定範囲内で条件に一致するセル数を数える関数です。
最初の引数の開始セルを絶対参照にし、終点だけを行ごとに変化させることで、上から現在行までの同じ得点を数えられます。
70が初めて出た行では1、次に70が出た行では2となります。
数式を下へコピーするだけで、同一値の積み上げ位置を自動計算できる点が大きな利点です。
数式をオートフィルする操作
B2セルの数式を確定したら、セル右下にある小さな四角形のフィルハンドルへマウスポインターを合わせます。
ポインターが黒い十字に変わったら、データ最終行までドラッグします。
隣のA列に連続データがある場合は、フィルハンドルをダブルクリックしても最終行まで数式をコピーできます。
数式の結果がすべて1になる場合は、開始セルの$記号や参照範囲を確認しましょう。
A2だけでなく$A$2:A2という範囲指定にすることが、累積的に数えるための要点です。
分布用散布図の挿入
A列の得点をX値、B列の出現順をY値として、マーカーのみの散布図を挿入します。
得点70の場所に点が縦に2つ並び、得点80の場所には1つ表示されます。
この形なら、平均値だけでは見えにくい集中度や偏りを視覚的に確認できます。
横軸の主単位を5や10にすると、大きな数値範囲でも読みやすい目盛りになります。
縦軸は度数を示す補助軸なので、最大値をデータ内の最大出現回数に合わせると余白を抑えられます。
【操作のポイント】同じ数値を重ねずに見せるには、COUNTIF関数で出現順を作り、その結果を散布図のY値に指定します。
散布図の点と軸を設定する方法
| 設定項目 | 推奨例 | 目的 |
|---|---|---|
| マーカーサイズ | 7 | 点の視認性 |
| 横軸最小値 | 60 | 差の把握 |
| 横軸最大値 | 100 | 比較範囲の固定 |
続いては、読みやすいグラフに仕上げるための点と軸の設定について確認していきます。
マーカーの形とサイズの変更
グラフ内の点を一度クリックして系列全体を選択し、右クリックからデータ系列の書式設定を開きます。
マーカーの項目では、円、四角形、ひし形などの種類を選択できます。
一般的なドットプロットでは円形が直感的ですが、複数系列を比較する場合は形も変えると判別しやすくなります。
マーカーサイズは小さすぎると数値の位置が見えにくく、大きすぎると隣接する点を隠します。
通常はサイズ6から9程度を起点に、グラフの大きさとデータ数に合わせて調整しましょう。
枠線を濃い色、塗りつぶしを少し薄い色にすると、重なった点も見分けやすくなるかもしれません。
横軸の最小値と最大値の指定
横軸を右クリックして軸の書式設定を選ぶと、境界値と単位を変更できます。
評価点が60から100の間にある場合、最小値を60、最大値を100、主単位を10に固定すると比較の基準が安定します。
自動設定のままでは、データ更新によって軸の幅が変わり、以前のグラフとの印象が変化することがあります。
複数月や複数部門のドットプロットを並べる場合は、横軸の最小値と最大値を統一します。
軸の範囲を統一すると、点の位置の差を正しく比較できます。
ただし、狭い範囲だけを強調する軸設定では、差が実際以上に大きく見える場合があるため注意が必要です。
データラベルと目盛線の整理
商品名を表示するには、グラフ要素のプラス記号からデータラベルをオンにします。
ラベルを右クリックしてデータラベルの書式設定を開き、セルの値を選べばA列の商品名を表示できます。
値、X値、Y値をすべて表示すると情報量が増えすぎるため、読者に必要な情報だけを残すことが大切です。
主目盛線は薄いグレーにし、補助目盛線は必要な場合だけ表示すると、点が主役のグラフになります。
凡例は系列が1つだけなら削除しても構いません。
【操作のポイント】マーカー、軸、ラベルの設定は、データの比較目的を決めてから最小限に整えると見やすくなります。
ドットプロットが見やすくなるデータ整理
| 項目 | 確認内容 |
|---|---|
| 数値形式 | 文字列ではなく数値か |
| 空白 | 不要な空白セルがないか |
| 分類 | 系列として分ける必要があるか |
続いては、ドットプロットの作成前後に確認したいデータ整理について解説していきます。
文字列の数値を数値へ直す方法
セル内の数値が左寄せになっていたり、緑色の三角形が表示されたりする場合は、数値が文字列として保存されている可能性があります。
文字列の数値は散布図のX値やY値として正しく認識されないことがあります。
警告アイコンが出ているセルでは、アイコンをクリックして数値に変換を選択します。
大量のデータを変換する場合は、1を入力した空白セルをコピーし、対象範囲へ形式を選択して貼り付けで乗算を実行する方法もあります。
散布図を作る前に数値形式をそろえることが、点が消えるトラブルの予防につながります。
空白値とエラー値の扱い
データに空白やエラー値が混在すると、グラフの点が途切れたり、意図しない位置に表示されたりする場合があります。
欠測値をそのまま除外したい場合は、グラフ用の補助表を作り、必要な行だけを参照する方法が安全です。
IFERROR関数を使ってエラーを空白に置き換える場合でも、散布図に含める範囲を確認しましょう。
=IFERROR(B2,””)
この数式はB2セルでエラーが発生したときに空白を返します。
ただし、空白をゼロとして扱う設定になっていると、原点付近に不要な点が現れることがあります。
分類別に系列を分ける考え方
部署、商品カテゴリ、性別などの分類がある場合、1つの系列にすべての点を入れるより、分類ごとに系列を分けると傾向を比較しやすくなります。
系列ごとに色を変え、同じ軸範囲を使用してください。
色だけに依存せず、マーカー形状やラベルも組み合わせると、白黒印刷でも意味が伝わりやすくなります。
分類が多すぎると凡例が複雑になるため、比較目的に関係する分類に絞る工夫も必要です。
【操作のポイント】グラフの見栄えを調整する前に、数値形式、空白、分類の扱いをそろえると修正作業を減らせます。
まとめ エクセルでドットプロットを作る方法（散布図の設定・点の追加）
| 作業 | 要点 |
|---|---|
| 基本作成 | 数値列と連番列で散布図を作成 |
| 点の追加 | 元表へ入力して参照範囲を拡張 |
| 分布表示 | COUNTIFで同値の出現順を計算 |
Excelのドットプロットは、散布図のマーカーのみを利用すると作成できます。
個々のデータを横方向に並べる場合は、X値に比較対象の数値、Y値に連番を指定します。
同じ値が多い分布データでは、COUNTIF関数で出現順を求め、縦方向へ点を積み上げる方法が便利です。
点を追加するときは元データ表を更新し、X値とY値の参照範囲をそろえることを忘れないでください。
さらに、マーカーの大きさ、軸の範囲、データラベルを整えると、数値の違いや分布の特徴が伝わるグラフになります。
目的に合ったデータ整理と散布図の設定を行い、Excelで見やすいドットプロットを活用しましょう。
