Excelでデータの分布が正規分布に近いかを確認したいとき、Q-Qプロットは非常に役立つグラフです。
ヒストグラムでは分かりにくい裾の違いや外れ値の影響も、理論分位点と標本分位点を比較することで視覚的に捉えられます。
統計解析の専門ソフトがなくても、並べ替え、順位、NORM.S.INV関数、散布図を組み合わせればExcelで作成できます。
Q-Qプロットでは、点が直線状に並ぶほどデータの分布は正規分布に近いと判断します。
ただし、完全な直線だけを求めるのではなく、どの部分がどのように離れているかを読むことが大切です。
この記事では、1行目に見出しがある売上時間などの数値データを例に、Q-Qプロットの作成手順、数式、散布図の整え方、グラフの読み方を順に解説します。
小規模なデータの確認から、分析結果を報告書へまとめる場面まで活用できる内容です。
ExcelでQ-Qプロットを作成する手順
それではまず、ExcelでQ-Qプロットを作る基本手順について解説していきます。
| 測定番号 | 測定値 |
|---|---|
| 1 | 48.2 |
| 2 | 51.4 |
| 3 | 53.1 |
| 4 | 55.8 |
| 5 | 60.7 |
元データと補助列の準備
Q-Qプロットでは、比較したい数値を小さい順に並べ、その順位ごとに理論上の正規分布の位置を求めます。
たとえばA列に測定番号、B列に測定値という見出しを置き、B2からB31に30件の値が入力されているものとします。
元データは直接並べ替えても構いませんが、日付や担当者などの対応関係を残したい場合は、別の列に昇順の値を表示するほうが安全です。
ここではD1に標本順位、E1に並べ替えた測定値、F1に累積確率、G1に標準正規分位点という見出しを作ります。
Q-Qプロットの横軸には標準正規分位点、縦軸には並べ替えた実測値を置くと、グラフの意味を整理しやすくなります。
測定値に文字列、空白、エラー値が混ざっていると順位や分位点がずれるため、作成前に数値だけになっているかを確認しましょう。
補助列は元データを壊さずに計算過程を確認するための作業領域です。
データ数が増えた場合にも、数式を下へコピーするだけで同じ構造を利用できます。
昇順データと順位の作成
続いて、標本順位と昇順にした測定値を作成します。
D2には1を入力し、D3には「=D2+1」と入力して最終行までオートフィルします。
E2には「=SMALL($B$2:$B$31,D2)」と入力してください。
SMALL関数は、指定範囲で小さいほうから何番目の数値かを返す関数です。
D2が1なら最小値、D3が2なら2番目に小さい値がE列に表示されます。
昇順データを求める数式
=SMALL($B$2:$B$31,D2)
最初の引数は元データ範囲で、絶対参照にしておくとオートフィル時に範囲が動きません。
同じ測定値が複数ある場合でもSMALL関数は問題なく使えますが、散布図では複数の点が同じ位置に重なることがあります。
これは式の誤りではなく、実データに同順位の値が存在することによる自然な結果です。
順位のセルとSMALL関数の参照範囲を確認してから、E2の数式をE31までコピーしましょう。
【操作のポイント】元データの件数が30件以外なら、$B$2:$B$31を実際の最終行まで変更します。
累積確率と標準正規分位点の計算
次に、各順位が正規分布のどの位置に当たるかを数式で計算します。
データ数をn、順位をiとしたとき、累積確率は一般に「(i-0.5)/n」で求められます。
F2には「=(D2-0.5)/COUNT($B$2:$B$31)」を入力します。
この計算により、最小値と最大値が確率0や1になることを避けられ、NORM.S.INV関数のエラーも防げます。
累積確率の数式
=(D2-0.5)/COUNT($B$2:$B$31)
COUNT関数は数値が入力されたセル数を数えるため、見出し行を含めずにデータ件数を自動取得できます。
G2には「=NORM.S.INV(F2)」を入力します。
NORM.S.INV関数は、指定した累積確率に対応する標準正規分布の値を返します。
F2が0.5に近ければG列は0に近くなり、確率が小さいほど負の値、大きいほど正の値になります。
NORM.S.INV関数で得られる値が、Q-Qプロットの比較基準となる理論分位点です。
F2とG2の数式を最終行までコピーし、空白セルやエラー表示がないことを確認してください。
【操作のポイント】NORM.S.INVに0または1を渡すとエラーになるため、順位そのものを確率として使わないようにします。
散布図によるQ-Qプロットの設定
続いては、計算した理論分位点と実測値を散布図にする方法を確認していきます。
| 標本順位 | 並べ替えた測定値 | 累積確率 | 標準正規分位点 |
|---|---|---|---|
| 1 | 48.2 | 0.017 | -2.13 |
| 2 | 51.4 | 0.050 | -1.64 |
| 3 | 53.1 | 0.083 | -1.38 |
横軸と縦軸のデータ範囲
散布図を作るときは、横軸にG列の標準正規分位点、縦軸にE列の並べ替えた測定値を指定します。
G1:G31とE1:E31を同時に選択しにくい場合は、まず散布図を挿入してからデータの選択画面で系列を編集する方法が確実です。
Excelの挿入タブから散布図を選び、線なしでマーカーのみが表示される種類を選択します。
グラフを右クリックしてデータの選択を開き、系列の編集画面で系列Xの値をG2:G31、系列Yの値をE2:E31に設定しましょう。
折れ線グラフではなく散布図を選ぶことが、数値間隔を正しく反映させる重要な条件です。
折れ線グラフは横軸をカテゴリとして均等に配置するため、標準正規分位点の距離を正しく表せません。
グラフの系列名は「Q-Qプロット」や「測定値」に変更しておくと、複数の図を扱う資料でも意味を見失いにくくなります。
散布図の挿入と系列編集
グラフの初期状態では、Excelが選択範囲を意図と異なる向きに解釈することがあります。
その場合は、グラフデザインタブからデータの選択を開き、系列が1つだけになっているかを確認します。
系列Xの値には理論分位点のセル範囲だけを指定し、列見出しのG1は含めません。
系列Yの値も同様に、並べ替えた実測値であるE2:E31だけを指定します。
系列が複数に分かれてしまったときは、不要な系列を削除し、XとYを1組にした系列を作り直すと整います。
データ点が左下から右上に向かって並べば、設定は概ね正しい状態です。
右下がりになった場合は、標本値の並び順が降順になっていないか、横軸と縦軸を取り違えていないかを見直してください。
【操作のポイント】グラフ内の点をクリックして系列を選択し、数式バーで参照範囲を確認すると設定ミスを探しやすくなります。
軸タイトルと近似直線の表示
グラフの見方を共有しやすくするには、横軸と縦軸にタイトルを付けます。
横軸は「標準正規分位点」、縦軸は「並べ替えた測定値」と入力すると、Q-Qプロットであることが伝わりやすくなります。
データ点を選択して近似曲線の追加を選び、線形を指定すると、点の全体的な傾きを把握できます。
近似直線は正規性を数学的に証明するものではありませんが、点群の曲がりや端部のずれを比較する目安になります。
平均値や標準偏差が異なるデータでも、Q-Qプロットでは直線性に注目して分布形状を比較します。
そのため、近似直線の傾きが1でないこと自体は問題ではありません。
マーカーをやや大きくし、補助線は薄い灰色にすると、離れた点や端部の曲線が読み取りやすくなります。
【操作のポイント】近似直線の式や決定係数は補助情報として扱い、点の並び方そのものを優先して確認します。
数式を使ったQ-Qプロット用データの作成
続いては、数式の仕組みをより詳しく確認していきます。
| D列 | E列 | F列 | G列 |
|---|---|---|---|
| 順位 | 標本分位点 | 累積確率 | 理論分位点 |
| 1 | =SMALL($B$2:$B$31,D2) | =(D2-0.5)/COUNT($B$2:$B$31) | =NORM.S.INV(F2) |
SMALL関数による標本分位点
Q-Qプロットでは、実測値を順位順に対応付けるため、標本分位点を作る必要があります。
ExcelのSMALL関数を使えば、データを別表に複製して並べ替えなくても、順位に応じた値を抽出できます。
「=SMALL($B$2:$B$31,D2)」のD2は、何番目に小さい値を返すかを示す順位です。
この順位を1からデータ数まで連続させることで、実測値は小さい順に整列されます。
Q-Qプロットの標本分位点は、並べ替えた各実測値そのものです。
大きな値から確認したいときはLARGE関数も使えますが、理論分位点も同じ向きに並べなければなりません。
一般的には昇順のSMALL関数を使うと、左下から右上へ伸びる見やすい散布図になります。
【操作のポイント】SMALL関数の範囲には見出しを入れず、数値データだけを選択します。
累積確率の計算方法
順位を正規分布の位置へ変換するには、各データに累積確率を割り当てます。
よく使われる「(i-0.5)/n」は、最小順位でも0にならず、最大順位でも1にならないようにする計算式です。
nが30の場合、1番目の確率は約0.0167、30番目の確率は約0.9833になります。
この考え方はプロッティングポジションと呼ばれ、標本データを理論分布へ対応させるための方法の一つです。
理論分位点の準備式
p=(i-0.5)/n
z=NORM.S.INV(p)
データ数が少ないと端部の点数も少なくなるため、裾の正規性は慎重に評価する必要があります。
反対に件数が多い場合は、わずかなずれも目立つことがあります。
グラフを機械的に合格、不合格と決めるのではなく、分析目的に対して問題となるずれかを考えましょう。
【操作のポイント】COUNT関数を使うと、データ行を追加した場合にも件数を数え直す手間を減らせます。
NORM.S.INV関数の役割
NORM.S.INV関数は、標準正規分布の累積確率からz値を返します。
標準正規分布とは、平均が0、標準偏差が1になるよう標準化された正規分布です。
たとえば累積確率0.5では結果が0となり、中央付近の実測値と対応します。
累積確率0.025では約-1.96、0.975では約1.96となり、分布の両端を表現します。
このz値を横軸に置くことで、実測データの形を正規分布の理論的な形と比較できます。
NORM.INV関数は平均と標準偏差を指定する関数ですが、Q-Qプロットの理論軸としてはNORM.S.INV関数のほうが扱いやすい場面が多いです。
実測値の平均や標準偏差は縦軸側のスケールに表れるため、標準正規分布を基準にしても分布の形状は確認できます。
【操作のポイント】NORM.S.INVの引数は0より大きく1より小さい確率である必要があります。
Q-Qプロットの直線性と外れ値の見方
続いては、完成したQ-Qプロットを読むための視点を確認していきます。
| 点の形 | 読み取れる傾向 |
|---|---|
| おおむね直線 | 正規分布に近い可能性 |
| 両端が大きく曲がる | 裾の厚さや歪みの可能性 |
| 一部の点だけ離れる | 外れ値や入力確認の必要性 |
直線状に並ぶ場合の判断
データ点が近似直線の周辺に無作為に並び、全体として直線に見える場合、正規分布からの大きな逸脱は見えにくい状態です。
完全に一直線になる実データはほとんどないため、多少のばらつきは自然な標本誤差と考えられます。
中央だけでなく両端の点まで大きく外れていないかを確認することが重要です。
正規性を前提とするt検定、分散分析、回帰分析の残差診断では、このような視覚的確認が分析の出発点になります。
ただしQ-Qプロットだけで正規性を断定するものではありません。
データの発生過程、サンプル数、正規性検定、ヒストグラムなども合わせて判断すると安心です。
【操作のポイント】直線からの距離だけでなく、点のずれがランダムか、一定の曲線になっているかを見ます。
S字型と裾の広がり
Q-Qプロットで点群がゆるやかなS字型になる場合、正規分布と比べて裾の厚さが異なる可能性があります。
両端の点が直線から大きく離れ、中央部が比較的近い形では、極端に小さい値や大きい値が予想より出やすい厚い裾を示すことがあります。
逆に両端が内側へ曲がる形では、正規分布より端の値が出にくい薄い裾の可能性があります。
端部の曲がりは、平均付近では見えにくい分布の特徴を表します。
製造測定値、待ち時間、売上金額などは、下限や上限、偶発的な大きな値の影響を受けることがあります。
原因を考えずに外れ値として除外するのではなく、工程変化、入力単位、記録ミス、対象条件の違いを確認することが先です。
Q-Qプロットの端部は少数の観測値に左右されやすい領域です。
データ数が少ない場合は、端の数点だけで強い結論を出さない姿勢が求められます。
【操作のポイント】S字の向きはグラフの軸設定にも影響されるため、理論分位点を横軸、昇順の標本値を縦軸にしたか再確認します。
孤立した点と外れ値候補
ほかの点が直線に沿っているのに、1点または数点だけが大きく離れている場合は、外れ値候補として確認します。
その値が測定ミス、入力ミス、単位の違いによるものなら、原記録を照合して修正できるかもしれません。
一方で実際に発生した重要な事象なら、単に削除するのではなく、なぜその値が生じたかを分析に残すべきです。
外れ値は邪魔な値ではなく、工程異常や顧客行動の変化を示す情報になる場合があります。
外れ値を含めたQ-Qプロットと、確認済みの誤入力を訂正した後のQ-Qプロットを並べると、影響を説明しやすくなります。
報告時には、外れ値の処理基準を事前に定め、恣意的にデータを選ばないことも大切です。
【操作のポイント】孤立点を見つけたら、グラフだけで削除せず、元データの行番号や測定条件まで戻って確認します。
正規性確認で併用する分析方法
続いては、Q-Qプロットと組み合わせて確認したい分析方法を見ていきます。
| 確認方法 | 確認しやすい内容 |
|---|---|
| ヒストグラム | 山の数、左右の偏り、全体の度数分布 |
| 記述統計 | 平均、中央値、標準偏差、歪度 |
| 正規性検定 | 統計量と有意確率による補助判断 |
ヒストグラムとの役割分担
ヒストグラムはデータを階級に分け、値がどの範囲にどれだけ集まっているかを見るグラフです。
山が一つか複数か、左右どちらに長い尾があるかといった特徴を直感的に把握できます。
一方、階級幅の決め方によって見え方が変わるため、ヒストグラムだけでは正規性の判定が安定しないことがあります。
ヒストグラムで全体像を見て、Q-Qプロットで理論分布との差を細かく見る組み合わせが有効です。
Excelではデータを選択して挿入タブから統計グラフのヒストグラムを選ぶことで、基本的な分布図を作成できます。
階級幅を変えても偏りや二峰性が一貫して見えるかを確認すると、偶然の見え方に左右されにくくなります。
【操作のポイント】ヒストグラムの横軸は実測値、Q-Qプロットの横軸は理論分位点であり、同じグラフではないことを意識します。
平均値と中央値の比較
平均値と中央値は、データの中心を示す代表値です。
正規分布に近い対称的な分布では、平均値と中央値は概ね近い値になります。
平均は「=AVERAGE(B2:B31)」、中央値は「=MEDIAN(B2:B31)」で計算できます。
平均値と中央値の確認式
=AVERAGE(B2:B31)
=MEDIAN(B2:B31)
平均が中央値よりかなり大きい場合は、右側に大きな値が伸びる右裾の長い分布を疑うきっかけになります。
ただし、代表値の差だけで分布形状を決めることはできません。
Q-Qプロットの曲がり方やヒストグラムの形と合わせて読み取ることで、数値と視覚の両方から確認できます。
【操作のポイント】平均値と中央値の差は、データの単位や業務上許容できる差の大きさも踏まえて評価します。
検定結果の扱い方
正規性検定は、データが正規分布に従うという仮説を統計的に確認する方法です。
代表的な方法にはShapiro-Wilk検定やKolmogorov-Smirnov検定がありますが、標準的なExcelだけでは直接実行しにくい検定もあります。
検定結果で有意差が出なかったとしても、正規分布であることが証明されたわけではありません。
またデータ数が非常に多いと、実務上ほとんど問題にならない小さなずれでも有意になることがあります。
検定のp値とQ-Qプロットの形を併用し、分析目的に照らして判断する姿勢が重要です。
たとえば予測誤差の検討では、残差に系統的な偏りがないかという実務上の意味も確認する必要があります。
正規性から少し外れていても、サンプルサイズや分析手法の頑健性によっては実用上の結論が変わらない場合もあります。
【操作のポイント】検定結果を単独で結論にせず、グラフ、データの背景、分析の目的を一緒に記録します。
まとめ Q-Qプロットを作成するExcelの方法とグラフの見方
ExcelでQ-Qプロットを作成するには、まず元データを小さい順の標本分位点として並べ、順位から累積確率を計算します。
その累積確率にNORM.S.INV関数を使い、標準正規分位点を作成します。
散布図では横軸に理論分位点、縦軸に並べ替えた実測値を設定することが基本です。
点が概ね直線に沿うなら正規分布に近く、曲線的なずれや孤立点があれば分布の歪みや外れ値を確認します。
SMALL関数、COUNT関数、NORM.S.INV関数を使えば、元データを残したまま再利用しやすいQ-Qプロット用の表を作れます。
判断の精度を高めるには、ヒストグラム、平均値と中央値、必要に応じた正規性検定も併用しましょう。
Q-Qプロットを単なるグラフ作成で終わらせず、点の並び方からデータの特徴を読み取ることが、Excelを使った統計分析の大切な一歩です。
