【Excel】エクセルでQ-Qプロットを作成する方法（散布図・正規性・近似曲線・回帰式・決定係数）

Excelでデータの正規性を確認したいとき、Q-Qプロットは分布の形を視覚的に確かめられる便利なグラフです。

平均との差やヒストグラムだけでは気付きにくい外れ値、裾のゆがみ、左右非対称の傾向も、理論分位数と標本分位数を比較することで判断しやすくなります。

ExcelにはQ-Qプロット専用のグラフボタンがないため、データの並べ替え、累積確率、正規分布の逆関数、散布図の設定という順番で作成することが重要です。

ここでは、1行目に見出しがある売上データを例に、ExcelでQ-Qプロットを作成し、近似曲線、回帰式、決定係数まで表示する方法を解説します。

 

ExcelでQ-Qプロットを作成する手順

それではまず、Q-Qプロットを完成させるための全体の流れについて解説していきます。

A列	B列	C列	D列
測定値	並べ替え値	累積確率	理論分位数
48	42	0.05	-1.645
55	48	0.15	-1.036
42	55	0.25	-0.674

Q-Qプロットでは、元データを小さい順に並べた数値と、その順位に対応する標準正規分布の値を組み合わせます。

散布図にする前に計算用の列を準備しておくと、データ数が増えても処理を再利用できます。

 

作業列の準備

まず、A1セルに測定値という見出しを入力し、A2セル以降へ確認したい数値を入力しましょう。

たとえばA2からA21に20件の測定結果があり、1行目はヘッダーとして扱う前提です。

隣のB1セルには並べ替え値、C1セルには累積確率、D1セルには理論分位数と入力します。

元データを直接並べ替えると元の順序が失われるため、Q-Qプロット専用の計算列を別に用意する方法が安全です。

操作前には、A列の値がすべて同じ単位であるかも確認しましょう。

売上額、時間、重量など異なる単位の値を同じ列に入れると、正規性の確認自体に意味を持たせにくくなります。

 

順位とデータ数の把握

続いて、データ数と順位がQ-Qプロットで果たす役割を確認していきます。

20個のデータなら、小さい値から順に1番目、2番目、3番目と順位を対応させます。

順位そのものを表に表示しなくても作成できますが、E1セルに順位、E2セルに1、E3セルに2と入力して下へ連続データを入力すると確認しやすくなります。

順位は累積確率を求めるための基礎です。

一般的には、順位をi、データ数をnとしたとき、累積確率を(i－0.5)／nで求めます。

データ数が少ない場合でも、順位とデータ数を正しく対応させることがQ-Qプロットの土台になります。

 

散布図までの作成順序

続いては、実際にグラフへ進むまでの順序を確認していきます。

最初にB列へ昇順の測定値を作り、次にC列へ累積確率、D列へ理論分位数を入力します。

その後、D列を横軸、B列を縦軸として散布図を挿入する流れです。

グラフを先に挿入してから計算範囲を追加することもできますが、列が完成してから選択した方が系列指定のミスを防げます。

横軸と縦軸を逆にしても直線性の確認はできますが、理論分位数を横軸に置く形が一般的です。

作成後は線形近似曲線を追加し、回帰式とR二乗値を表示すると、目視だけでは分かりにくい直線への近さも補助的に確認できます。

 

並べ替え値と順位の数式

続いては、標本分位数を作るための並べ替え値と順位の数式を確認していきます。

セル	入力内容	意味
B2	=SMALL($A$2:$A$21,ROWS($B$2:B2))	小さい順の値
E2	=ROWS($E$2:E2)	現在の順位

並べ替え値は、元データをコピーして並べ替える方法でも作れますが、関数を使うと元データの変更に追従できます。

 

SMALL関数による昇順データ

それではまず、SMALL関数を使って昇順データを作る方法について解説していきます。

B2セルへ次の数式を入力します。

SMALL関数の第1引数は元データの範囲で、$A$2:$A$21のように絶対参照にします。

第2引数は何番目に小さい値を取り出すかを指定する部分です。

ROWS($B$2:B2)はB2セルでは1となり、B3セルへコピーするとROWS($B$2:B3)となって2になります。

つまり、下方向へオートフィルするだけで、1番目、2番目、3番目の小さい値を順番に取得できます。

数式の先頭セルを作った後は、フィルハンドルを下へドラッグして最終データ行までコピーしましょう。

同じ数値が複数ある場合も、SMALL関数は重複値を含めて順位どおりに返します。

 

ROWS関数による連番

続いては、順位を見える形で管理するROWS関数について確認していきます。

E2セルに=ROWS($E$2:E2)と入力し、E21セルまでオートフィルします。

この式は行数を数えるため、途中に行を挿入しても連番の考え方を維持しやすい特徴があります。

単純に1と2を入力して連続データで伸ばす方法もありますが、数式で順位を作ると、どの行が何番目の値なのかをセル上で検証しやすくなります。

データ数を確認するセルとして、F1セルにデータ数、F2セルに=COUNT($A$2:$A$21)を入力しておく方法も便利です。

COUNT関数は数値が入力されたセルだけを数えるため、空白を含む広い範囲を指定する場合にも役立ちます。

 

並べ替え機能を使う場合

続いては、Excelの並べ替え機能で標本分位数を作る場合を確認していきます。

数式を使わずに行うなら、元データ列をコピーして別の列へ貼り付け、その列を昇順で並べ替えます。

コピーした範囲内のセルを選択し、データタブの昇順ボタンを選ぶと、小さい値から大きい値へ並びます。

見出しを含めて選択した場合は、先頭行を見出しとして認識する設定になっているかを確認してください。

見出しまで並べ替わってしまうと、その後の数式やグラフ系列が分かりにくくなります。

ただし、貼り付けた値を並べ替える方法は、A列の元データが更新されてもB列へ自動反映されません。

毎月の測定結果など更新頻度が高い表では、SMALL関数を使う方法が扱いやすいでしょう。

 

理論分位数と正規分布の計算

続いては、累積確率から標準正規分布の理論分位数を求める方法を確認していきます。

順位	累積確率	理論分位数
1	0.025	-1.960
10	0.475	-0.063
20	0.975	1.960

理論分位数は、標準正規分布に従うと仮定したときに、各順位へ対応する位置を数値で表したものです。

 

累積確率の求め方

それではまず、累積確率を計算する式について解説していきます。

データ数がF2セルにあり、順位がE2セルにある場合、C2セルに次の数式を入力します。

この式の0.5は、順位の中央に確率を割り当てるための補正です。

たとえば20件の最小値は、(1－0.5)／20で0.025となります。

最大値は(20－0.5)／20で0.975となり、確率が0または1にならないため、逆関数を安全に計算できます。

C2セルを下へオートフィルすると、順位ごとの確率が均等な間隔で作成されます。

数式内の$F$2はデータ数を固定するための絶対参照です。

 

NORM.S.INV関数による分位数

続いては、NORM.S.INV関数を使う理論分位数の計算について確認していきます。

D2セルへ次の数式を入力します。

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

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

このD列がQ-Qプロットの横軸になる理論分位数です。

D2セルを最終行までコピーすると、負の値から正の値までなめらかに増加する数列ができます。

古いExcelではNORM.S.INV関数が使えず、NORMSINV関数が表示される場合があります。

利用しているバージョンに応じて関数候補を確認し、返される値が同じ考え方の標準正規分布であることを確かめましょう。

 

標準偏差と平均を使う場合

続いては、標準正規分布ではなく、元データの平均と標準偏差を反映した理論値を作る場合を確認していきます。

Q-Qプロットでは標準正規分布の値を横軸にする方法が分かりやすいですが、元データと同じ尺度の期待値を計算することもできます。

平均をG2セル、標準偏差をH2セルに求めた場合、理論値は次のように計算できます。

NORM.INV関数は平均と標準偏差を指定した正規分布の分位数を返します。

ただし、この値を縦軸の実測値と比べる場合は、傾きが1に近いかという見方になり、標準正規分位数を横軸にする図とは読み方が少し変わります。

基本的な正規性確認では、D列にNORM.S.INVを入れる標準化された方法から始めると理解しやすいでしょう。

 

散布図と近似曲線の設定

続いては、理論分位数と並べ替え値から散布図を作り、近似曲線を設定する方法を確認していきます。

横軸	縦軸	グラフ種類
D2:D21	B2:B21	散布図 マーカーのみ

散布図では、横軸と縦軸の数値が一組ずつ対応します。

折れ線グラフでは横軸がカテゴリとして扱われるため、Q-Qプロットには散布図を選びます。

 

散布図の挿入

それではまず、Q-Qプロット用の散布図を挿入する操作について解説していきます。

D1:D21の理論分位数列を選択した後、Ctrlキーを押しながらB1:B21の並べ替え値列を選択します。

次に挿入タブを開き、グラフグループから散布図を選び、マーカーのみの散布図を選択しましょう。

グラフが挿入されたら、横軸が理論分位数、縦軸が並べ替え値になっているかを確認します。

選択した列の順番によって系列が意図どおりにならない場合は、グラフのデータの選択から修正できます。

横軸の値にB列が入っていると、Q-Qプロットとしての比較ができません。

 

系列の編集

続いては、散布図の横軸と縦軸を正しく指定する操作を確認していきます。

グラフを右クリックし、データの選択を開きます。

系列を選択して編集を押すと、系列Xの値と系列Yの値を指定する画面が表示されます。

系列Xの値には$D$2:$D$21、系列Yの値には$B$2:$B$21を指定します。

見出しのある1行目は数値範囲に含めず、実際のデータが始まる2行目から最終行までを設定することが大切です。

グラフタイトルはQ-Qプロット、横軸タイトルは理論分位数、縦軸タイトルは並べ替え値などにすると、他の人が見ても内容を把握しやすくなります。

 

線形近似曲線の追加

続いては、近似曲線、回帰式、決定係数をグラフへ表示する方法を確認していきます。

散布図上の点をクリックしてデータ系列を選択し、右クリックから近似曲線の追加を選びます。

近似曲線のオプションでは線形を選択します。

同じ設定画面でグラフに数式を表示する、グラフにR二乗値を表示するという項目にチェックを入れましょう。

表示される回帰式は、縦軸の値をy、横軸の理論分位数をxとした一次式です。

R二乗値は決定係数とも呼ばれ、直線モデルでどの程度説明できるかの目安になります。

ただし、決定係数だけで正規性を確定することはできません。

 

Q-Qプロットと正規性の読み方

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

点の並び方	考えられる傾向
ほぼ直線	正規分布に近い形
両端が大きく曲がる	裾の厚さや外れ値の影響
S字型	正規分布との差、ゆがみ

Q-Qプロットは検定結果そのものではなく、データの形を探索するための図です。

 

直線に近い場合

それではまず、点が直線に近い場合の見方について解説していきます。

プロットした点が近似曲線の周辺へまんべんなく並んでいれば、標本分位数と正規分布の理論分位数の対応が比較的良好です。

このとき、中央部だけでなく、左右の端でも大きく外れていないかを確認することが重要です。

回帰式の傾きはデータのおおよその広がりに関係し、切片はデータの中心位置に関係します。

元データの平均が50付近なら、横軸が0付近の位置で縦軸が50付近になる形を想像できます。

直線が右上がりであることは自然ですが、完全に一直線になる必要はありません。

 

外れ値と裾の確認

続いては、外れ値や裾の違いを確認する方法について解説していきます。

一部の点だけが直線から大きく離れている場合、その行の測定値を元表へ戻って確認します。

入力ミス、単位の混在、特殊な条件での測定などが原因かもしれません。

両端の点が規則的に直線から曲がるなら、単なる入力ミスではなく、データの裾が正規分布と異なる可能性があります。

極端な値を安易に削除するのではなく、発生理由を確認してから分析対象を判断しましょう。

たとえば処理時間のように0未満にならないデータでは、右側へ長い裾が伸びることがあります。

 

決定係数の扱い

続いては、回帰式と決定係数を判断材料として使う際の注意点を確認していきます。

R二乗値が1に近いほど、プロットは線形近似曲線で説明されやすい状態です。

そのため、R二乗値が高いと直線性の目安にはなります。

一方で、決定係数が高いことだけを根拠に正規性が証明されたと考えることはできません。

データ数が少ない場合や、特定の形に偏った場合は、数値だけでは見逃す特徴が残るためです。

Q-Qプロットの形、ヒストグラム、平均値と中央値の差、業務上の発生メカニズムを合わせて判断すると、分析の精度が上がります。

 

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

ExcelでQ-Qプロットを作成するには、元データを昇順へ並べ、順位に応じた累積確率を計算し、NORM.S.INV関数で理論分位数を求めます。

その後、理論分位数を横軸、並べ替え値を縦軸にして散布図を挿入すれば、正規性を確認するためのQ-Qプロットが完成します。

B列の並べ替え値、C列の累積確率、D列の理論分位数という計算列を分けると、数式とグラフの対応を確認しやすくなります。

累積確率は=(順位－0.5)／データ数、理論分位数は=NORM.S.INV(累積確率)という形で求めるのが基本です。

散布図へ線形近似曲線を追加し、回帰式と決定係数を表示すると、直線への近さを補助的に確認できます。

ただし、正規性は決定係数だけで判断せず、点の並び、両端の曲がり、外れ値、データの性質を総合して読み取りましょう。

テンプレートとして計算列とグラフを保存しておけば、次回は元データを差し替えるだけで同じ手順を活用できます。