【Excel】エクセルでQ-Qプロットを作成する方法（散布図）

Excelでデータが正規分布に近いかを確認したいときは、Q-Qプロットを作成すると分布の形を視覚的に判断できます。

Q-Qプロットは、実際のデータを小さい順に並べた値と、正規分布から期待される理論値を散布図にしたグラフです。

点が右上がりの直線付近に並べば、データは正規分布に近いと考えられます。

一方で曲線状に並んだり、一部の点だけが大きく離れたりする場合は、歪みや外れ値を含んでいる可能性があります。

サンプルとして、A列に測定値が入力されており、1行目には見出しがある状態を想定して解説します。

それでは、ExcelでQ-Qプロットを作成する手順を確認していきましょう。

 

ExcelでQ-Qプロットを作成する方法【正規分位点と散布図】

A	B	C	D	E
測定値	昇順の実測値	順位	累積確率	正規分位点
52	41	1	0.071	-1.465
41	45	2	0.214	-0.792
58	52	3	0.357	-0.366

それではまず、実測値と正規分位点を用意して散布図を作る方法について解説していきます。

 

Q-Qプロットに必要な列の準備

Q-Qプロットでは、元のデータだけではなく、並べ替え後の実測値と正規分布の理論値を比較します。

そのため、元データがA2からA11に入力されている場合は、B列からE列を計算用の列として利用します。

B1には昇順の実測値、C1には順位、D1には累積確率、E1には正規分位点と入力しておくと、後から見直すときも分かりやすくなります。

Q-Qプロットの横軸と縦軸に使用する列を明確に分けることが、作図ミスを防ぐ基本です。

データ数が10件なら、計算対象の最終行は11行目になります。

空白セルや文字列が混在していると、並べ替えや関数の結果に影響することがあります。

事前に数値データだけを抽出しておくと、計算が安定します。

 

昇順データと正規分位点の計算

B2には、元データから小さい順に値を取り出す数式を入力します。

この数式は、A2からA11の範囲で小さい方から何番目の値かを返します。

ROW(A1)は最初に1を返すため、B2には最小値が表示されます。

B2の数式をB11までオートフィルすると、実測値が昇順に並びます。

C2には順位として1を入力し、C3にはC2+1を入力してC11までコピーする方法でも構いません。

数式だけで順位を作るなら、C2に次の式を入力します。

C2を下方向へコピーすると、1、2、3のように連続した順位が作成されます。

正規分位点を出す前に、実測値と順位が同じ行に対応しているかを確認しましょう。

 

散布図の挿入と軸の指定

E列に正規分位点、B列に昇順の実測値が準備できたら、散布図を挿入します。

まずE1からE11を選択し、Ctrlキーを押したままB1からB11を選択します。

その後、挿入タブから散布図を選び、マーカーのみの散布図をクリックします。

Q-Qプロットでは、折れ線グラフではなく散布図を使うことが重要です。

Excelが自動判定した軸が意図どおりでない場合は、グラフを右クリックしてデータの選択を開きます。

系列の編集画面で、系列Xの値にE2からE11、系列Yの値にB2からB11を設定します。

横軸は標準正規分布の分位点、縦軸は実測値という対応になります。

 

データの並べ替えと順位の計算

元データ	昇順の実測値	順位
58	41	1
45	45	2
41	52	3

続いては、Q-Qプロットの土台となるデータの並べ替えと順位の扱いを確認していきます。

 

SORT関数による昇順データの作成

Microsoft 365やExcel 2021以降では、SORT関数を使うと昇順データを簡単に作成できます。

この数式をB2に入力すると、A2からA11までの数値が小さい順にスピル表示されます。

第2引数の1は並べ替えに使う列を示し、第3引数の1は昇順を意味します。

元データを並べ替えずにQ-Qプロット用の列だけを作れるため、元表を保持したまま分析したい場合に便利な方法です。

ただし、スピル範囲の下に値が入っているとエラーになります。

B2より下のセルを空けてから数式を入力しましょう。

古いExcelではSORT関数が使えないため、SMALL関数またはデータタブの並べ替え機能を使います。

 

同じ値が含まれる場合の考え方

測定値には、同じ数値が複数含まれることがあります。

たとえば整数に丸めたアンケート得点や、単位が粗い測定データでは、重複値が生じやすいでしょう。

Q-Qプロットでは、同じ実測値が複数あっても、そのまま昇順に並べて問題ありません。

ただし、同じ高さに点が並ぶため、グラフでは横方向に点が連続して見えることがあります。

重複値が多いこと自体はエラーではなく、データが離散的であることを示す特徴です。

順位は重複値があっても1からデータ数まで連番で付けます。

RANK.EQ関数で同順位を作る方法ではなく、並べ替え後の行番号を順位として使う方が、正規分位点を計算しやすくなります。

 

空白セルとエラー値の除外

Q-Qプロットでは、計算に含めるデータ数を正しく数える必要があります。

範囲内に空白セル、文字列、エラー値があると、COUNT関数と並べ替え結果がずれる場合があります。

数値だけの件数は、たとえば次の数式で確認できます。

COUNT関数は数値セルだけを数えるため、理論分位点の分母に使う件数の確認に向いています。

エラー値がある場合は、原因を修正するか、分析対象外として別の列に数値だけを抽出します。

分母となるデータ数が1件でもずれると、全ての正規分位点が変わります。

 

正規分位点とNORM.S.INV関数

順位	データ数	累積確率	正規分位点
1	10	0.05	-1.645
2	10	0.15	-1.036
3	10	0.25	-0.674

続いては、正規分布の理論値となる正規分位点とNORM.S.INV関数を確認していきます。

 

累積確率を求める計算式

正規分位点を計算する前に、各順位に対応する累積確率を求めます。

データ数をn、順位をiとした場合、累積確率は一般的にiをnで割る方法ではなく、端点を避ける式を使います。

たとえば順位がC2、元データの範囲がA2からA11なら、D2には次の数式を入力します。

最小値でも確率が0にならず、最大値でも確率が1にならないため、NORM.S.INV関数のエラーを避けられます。

NORM.S.INV関数には0より大きく1より小さい確率を渡す必要があります。

D2の数式を最終行までオートフィルしてください。

 

NORM.S.INV関数による理論値の取得

累積確率を計算できたら、E列で標準正規分布の分位点を求めます。

E2には次の数式を入力します。

NORM.S.INV関数は、指定した累積確率に対応する標準正規分布の値を返します。

標準正規分布は平均が0、標準偏差が1の正規分布です。

累積確率が0.5なら結果は0になり、0.5より小さい確率では負の値、0.5より大きい確率では正の値になります。

数式をE11までコピーすると、Q-Qプロットの横軸に使う理論値が完成します。

NORM.S.INVの結果は、元データの単位とは異なる標準化された理論上の位置です。

 

NORM.INV関数との使い分け

NORM.INV関数も正規分布の分位点を計算する関数です。

ただし、NORM.INV関数では累積確率に加えて平均と標準偏差を指定します。

実測値の平均と標準偏差を基準とした理論値を作りたい場合は、次のような数式を利用できます。

この方法では、縦軸と同じ単位を持つ理論値を作れます。

一方で、標準正規分位点を横軸にして散布図を作る方法は、式が分かりやすく、Q-Qプロットの標準的な形として扱いやすいでしょう。

どちらの方法でも、点が直線に近いかという判断の考え方は同じです。

 

散布図の設定と基準線の追加

横軸	縦軸	グラフ種類
正規分位点 E2:E11	昇順の実測値 B2:B11	散布図 マーカーのみ

続いては、Q-Qプロットを見やすい散布図に整え、判断用の基準線を追加する方法を確認していきます。

 

散布図の作成と系列の確認

正規分位点のE2からE11と、昇順の実測値のB2からB11を使って散布図を挿入します。

挿入タブのグラフグループから、散布図またはバブルチャートを選択します。

最初のアイコンである散布図を選ぶと、点だけで構成されたグラフを作成できます。

グラフ内を右クリックしてデータの選択を開けば、系列の範囲を確認できます。

系列Xの値は正規分位点、系列Yの値は実測値に設定します。

誤って元データを縦軸にすると、データの並び順が乱れ、Q-Qプロットとして適切に読めません。

横軸タイトルには正規分位点、縦軸タイトルには実測値と入力しておくと、第三者にも意図が伝わります。

 

近似曲線による直線性の確認

散布図に近似曲線を追加すると、点群の全体的な傾きを確認しやすくなります。

点をクリックして系列を選択し、グラフ要素の追加から近似曲線を選びます。

近似曲線の種類は線形を選択します。

線形近似曲線は、正規分布に近い場合に点群の中心を通る目安となります。

ただし、近似曲線の有無だけで正規性を断定することはできません。

重要なのは、中央部だけではなく両端の点が直線からどの程度離れているかです。

必要に応じて、グラフ要素から数式とR二乗値を表示することもできます。

 

Excel画面での散布図設定

上の画面イメージでは、E列の正規分位点を選択し、挿入タブから散布図を作る流れを示しています。

数式を入力する最初のセルだけを正しく設定し、フィルハンドルを下へドラッグしてオートフィルすると、データ数に応じた理論値をまとめて求められます。

グラフの見た目を整える前に、セルの数式と系列範囲を確認することが大切です。

 

Q-Qプロットの読み方と外れ値の確認

点の並び方	読み取れる傾向
直線付近に並ぶ	正規分布に近い可能性
S字に曲がる	歪みや裾の形の違い
端の点だけ離れる	外れ値の可能性

続いては、完成したQ-Qプロットから正規性、歪み、外れ値を読み取る考え方を確認していきます。

 

直線的な並びと正規性

Q-Qプロットの点が全体として右上がりの直線に沿って並ぶ場合、データは正規分布に近いと判断できます。

完全に一直線になる実測データは少ないため、多少のばらつきは自然な現象です。

特にデータ数が少ないと、中央付近でも点が直線からわずかに離れることがあります。

重要なのは個々の点の小さな差ではなく、点群全体に一貫した曲がりがあるかどうかです。

直線の傾きはデータの標準偏差に関連し、切片は平均値に関連します。

横軸に標準正規分位点を使った場合、傾きそのものを正規性の判定値として使う必要はありません。

点の配列が概ね直線的であるかを確認することが主な目的です。

 

S字型の曲がりと分布の歪み

点群がS字型や逆S字型に曲がるときは、実測データの裾の広がり方が正規分布と異なる可能性があります。

両端が基準線より外側に大きく離れ、中央部が内側に寄る形は、正規分布より裾が重い分布で見られることがあります。

反対に両端が内側に入り、中央部が外側に膨らむ形では、裾が軽い分布の可能性を考えます。

右側だけが大きく上へ曲がる場合は、右に長い裾を持つ右歪みを疑います。

Q-Qプロットは、平均値だけでは把握しにくい分布の歪みを視覚化できる点が強みです。

ただし、曲がりが見えた場合でも、データ数が極端に少ないと偶然のばらつきの可能性があります。

 

外れ値と元データの再確認

大部分の点が直線付近にあるのに、1点または少数の点だけが離れている場合は、外れ値の候補です。

外れ値を見つけたときは、すぐに削除するのではなく、入力ミス、測定条件、単位の違いを確認しましょう。

たとえば本来は52と入力すべきところを520と入力していた場合、Q-Qプロットの端に極端な点として現れます。

一方で、その値が実際に起きた重要な事象を表す場合もあります。

外れ値は単なる邪魔な値ではなく、業務上の異常や新しい発見を示す情報かもしれません。

必要に応じて、外れ値を含むグラフと除外したグラフを分けて比較する方法もあります。

 

まとめ エクセルでQ-Qプロットを作成する方法

手順	行う内容
1	元データを昇順に並べる
2	順位と累積確率を計算する
3	NORM.S.INV関数で正規分位点を求める
4	正規分位点と実測値で散布図を作成する

ExcelでQ-Qプロットを作成するには、実測値を小さい順に並べ、順位から累積確率を計算し、NORM.S.INV関数で正規分位点を求めます。

その後、正規分位点を横軸、昇順の実測値を縦軸にして散布図を作成すれば、正規性を視覚的に確認できます。

点が直線付近に並ぶほど、データは正規分布に近い傾向を持つと考えられます。

曲線状の並びは歪みを、端から大きく離れた点は外れ値を確認するきっかけになります。

COUNT関数で数値データの件数を確認し、累積確率が0や1にならないように計算することも重要です。

Excelの散布図と関数を組み合わせれば、統計ソフトがなくても、データ分布の特徴を実務で分かりやすく確認できるでしょう。