【Excel】エクセルでQ-Qプロットを作成する方法は？データを使った正規性の確認手順

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

統計解析ソフトがなくても、Excelの関数、並べ替え、散布図を組み合わせれば、標本データと理論上の正規分布を比較できます。

特に品質管理、アンケート結果、測定値、売上データなどでは、平均値や標準偏差だけで判断せず、データの並び方そのものをグラフで確認することが大切です。

Q-Qプロットでは、点が対角線の周辺にほぼ一直線で並ぶほど、対象のデータは正規分布に近いと考えられます。

この記事では、1行目に見出しがあるサンプルデータを使い、ExcelでQ-Qプロットを作成して正規性を確認する手順を詳しく解説します。

 

エクセルでQ-Qプロットを作成する手順

それではまず、ExcelでQ-Qプロットを完成させる基本手順について解説していきます。

A列	B列	C列	D列
測定値	順位	累積確率	理論分位数
48.2	1	0.025	-1.960
49.1	2	0.075	-1.440
50.0	3	0.125	-1.150

 

元データの準備と昇順への並べ替え

Q-Qプロットを作成する前に、比較したい数値データを1列に整理します。

ここではA1セルに「測定値」という見出しを入力し、A2セルからA21セルまでに20件の測定値が入力されているものとします。

元データが入力順のままでは、理論分位数との対応が取れません。

そのため、A列の見出しを含む表全体を選択し、「データ」タブの「昇順」で並べ替えます。

Q-Qプロットでは最小値から最大値へ順に並べた標本分位数を使うため、昇順への並べ替えが出発点です。

並べ替えを行うときは、「先頭行をデータの見出しとして使用する」設定が有効になっているかも確認しましょう。

見出しまで一緒に並べ替えてしまうと、数式の参照先やグラフ範囲が分かりにくくなることがあります。

元の順番を残したい場合は、A列のデータを別の列または別のシートへコピーしてから並べ替える方法が安心です。

 

順位と累積確率の計算

続いて、並べ替えた各データが全体の何番目に位置するかを示す順位を作成します。

B1セルに「順位」と入力し、B2セルへ次の数式を入力します。

この数式では、2行目なら1、3行目なら2となり、見出しの次の行から連番を作れます。

B2セルの右下にあるフィルハンドルを下へドラッグするか、ダブルクリックしてB21セルまでコピーしましょう。

次にC1セルへ「累積確率」と入力し、C2セルに次の数式を入力します。

この式は、順位をそのまま確率へ置き換えるのではなく、順位から0.5を引いて標本数で割る計算です。

最小値と最大値が0や1の確率にならないため、正規分布の逆関数を使うときにエラーを避けられます。

累積確率は0より大きく1より小さい値にすることが、NORM.S.INV関数を正しく使う重要な条件です。

COUNT関数の範囲には、見出しを含めず、実際の数値が入っているA2からA21を指定します。

データ件数が増減する場合は、COUNT($A$2:$A$1000)のように少し広めの範囲を指定しても構いません。

 

散布図によるQ-Qプロットの完成

最後に、理論分位数と実際の測定値を散布図として表示します。

D1セルに「理論分位数」と入力し、D2セルに次の数式を入れてください。

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

たとえば累積確率が0.5なら理論分位数は0となり、平均付近を表します。

D2セルをD21セルまでオートフィルしたら、D1からD21とA1からA21を参照して散布図を作成します。

操作は、「挿入」タブから「散布図」を選び、「マーカーのみ」の散布図を選択する流れです。

横軸にはD列の理論分位数、縦軸にはA列の測定値を設定します。

横軸と縦軸を逆にしても傾きが変わるだけで比較自体はできますが、理論分位数を横軸に置く形式が一般的です。

グラフタイトルを「Q-Qプロット」に変更し、必要に応じて軸タイトルも表示すると、後から見直しやすくなります。

 

Q-Qプロットに使うサンプルデータと計算式

続いては、実際に入力できるサンプルデータと数式の考え方を確認していきます。

行	A列 測定値	B列 順位	C列 累積確率
2	48.2	1	0.025
3	49.1	2	0.075
4	50.0	3	0.125

 

測定値データの入力例

たとえば、部品の長さを測定した結果として、48.2、49.1、50.0、50.3、50.7、51.0のような数値が得られたとします。

これらのデータをA列へ貼り付け、昇順に並べ替えることで、Q-Qプロット用の標本分位数になります。

測定値は整数でも小数でも扱えますが、単位は同じものに統一してください。

ミリメートルとセンチメートル、円と千円のように単位が混在すると、分布の比較以前にデータの意味が崩れます。

Q-Qプロットは値の単位そのものより、値が小さい順から大きい順へどう並ぶかを見るグラフです。

そのため、測定対象が同じ条件で取得されたデータかどうかを確認することも欠かせません。

異なる製造ロット、異なる担当者、異なる期間のデータを無条件に混ぜると、本来は正規性があっても複数の山を持つ分布に見える場合があります。

 

順位補正に0.5を使う理由

累積確率の式で使った「順位から0.5を引く」という処理には、両端のデータを無理なく理論分布へ対応させる役割があります。

20件の最小値に対して単純に1÷20を使うと0.05になり、最大値は20÷20で1になります。

しかし、正規分布の逆関数に確率1を渡すと、理論上は無限大となり、Excelでは計算できません。

そこで、順位を中央寄りに補正するプロット位置を用います。

この方法では、最小値も最大値も0と1の間に収まります。

Q-Qプロットの目的は厳密な唯一の計算式を競うことではなく、標本と理論分布を一貫した基準で比較することです。

統計の文献やソフトによっては、(i-0.375)/(n+0.25)など別のプロット位置を用いる場合もあります。

ただし、通常のExcel作業では、(i-0.5)/nを使えば十分に分かりやすく、実務上の正規性確認にも使いやすいでしょう。

 

NORM.S.INV関数と標準化の関係

NORM.S.INV関数は、平均0、標準偏差1の標準正規分布に対応する値を返します。

返される値はz値とも呼ばれ、平均から標準偏差何個分だけ離れているかを示す尺度です。

累積確率が0.975の場合、理論分位数はおおむね1.96になります。

これは標準正規分布において、平均から約1.96標準偏差上側に位置する点です。

実際の測定値は48や50などの単位を持つ一方、D列は標準化された理論上の尺度になります。

この二つを散布図で比較することで、実データの増え方が正規分布の増え方と似ているかを確かめられます。

データを自分で標準化してから作図したい場合は、平均と標準偏差を使ってz値へ変換する方法もあります。

ただし、通常のQ-Qプロットでは、元データを縦軸、NORM.S.INV関数で得た理論分位数を横軸に置けば問題ありません。

 

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

続いては、Q-Qプロットを読み取りやすくする散布図の設定と基準線を確認していきます。

設定項目	推奨内容	確認できること
グラフ種類	散布図 マーカーのみ	分位数の対応
横軸	理論分位数	正規分布との比較
縦軸	測定値	実データの位置

 

散布図で横軸と縦軸を指定する方法

Q-Qプロットを作成するときは、D列の理論分位数を横軸、A列の測定値を縦軸として指定します。

最初にD2からD21を選択し、Ctrlキーを押しながらA2からA21を選択する方法もあります。

選択が難しい場合は、空の散布図を挿入してから「データの選択」で系列を編集すると確実です。

系列の編集画面では、「系列Xの値」にD2:D21、「系列Yの値」にA2:A21を指定します。

見出しセルを範囲に含めると、文字列を含んだ系列になり意図しない表示になることがあるため、数値部分だけを選びます。

散布図が表示されたら、各点が左下から右上へ向かって増える形になるかを確認しましょう。

データの単位が大きくても、グラフの見た目を整えるために無理に標準化する必要はありません。

縦軸の単位を残しておくほうが、どの測定値の周辺で曲がりや外れがあるかを把握しやすい場合もあります。

 

近似直線を基準にする考え方

点がどの程度一直線に近いかを判断するには、散布図へ線形近似曲線を追加すると便利です。

グラフ上のデータ点をクリックし、右クリックして「近似曲線の追加」を選択します。

近似曲線のオプションで「線形」を選べば、データ全体に最も合う直線が表示されます。

この直線は、データ点が正規分布に近い場合に期待される傾きと位置を表す目安になります。

個々の点が近似直線の近くにばらついていれば、少なくとも視覚上は正規性に大きな問題がない可能性があります。

一方で、両端だけが直線から大きく離れる場合は、裾が重い、裾が軽い、外れ値があるといった特徴を疑います。

決定係数R2を表示することもできますが、R2が高いだけで正規性を断定することはできません。

Q-Qプロットでは、直線との距離だけでなく、点のずれ方に規則性があるかを読むことが重要です。

 

グラフタイトルと軸タイトルの整備

作成したグラフを報告資料や会議資料で使う場合は、何を比較しているグラフなのかを明確にします。

グラフタイトルには「測定値のQ-Qプロット」など、対象のデータ名を含めるとよいでしょう。

横軸タイトルは「理論正規分位数」、縦軸タイトルは「測定値 mm」のように、尺度と単位を記載します。

この設定により、グラフだけを切り出して共有した場合でも内容を誤解しにくくなります。

軸のタイトルは分析の再現性にも関わるため、変数名だけで済ませず、何を表す値かを言葉で示すことが大切です。

マーカーの色は、基準線と見分けやすい青や緑にし、近似曲線は灰色や黒にすると読みやすくなります。

グラフに不要な凡例が表示されている場合は削除しても構いません。

 

Q-Qプロットによる正規性の確認方法

続いては、作成したQ-Qプロットから正規性を確認する見方について解説していきます。

点の並び方	考えられる特徴	次の確認
ほぼ直線	正規分布に近い	外れ値と標本数
S字型	裾の厚さが異なる	ヒストグラム
片側だけ曲がる	歪みや上限下限	データの発生条件

 

直線に近い配置の読み取り

Q-Qプロットのもっとも基本的な見方は、点が全体として直線状に並んでいるかを見ることです。

中央付近だけでなく、低い値の側と高い値の側も含めて、近似直線の周辺に点が分布していれば、正規分布との整合性は比較的高いといえます。

実際の標本では、すべての点が完全な直線になることはほとんどありません。

標本数が少ない場合は、偶然のばらつきによって点が多少上下するのが自然です。

重要なのは一つの点の小さなずれではなく、全体に一方向の曲がりや系統的なパターンがないかです。

たとえば20個の点のうち1個だけが少し離れていても、入力ミスや一時的な測定誤差を含めて確認すべき対象であり、直ちに分布全体が非正規だとは限りません。

平均、中央値、最頻値が近いか、ヒストグラムが一つの山に見えるかも合わせて見れば、より納得感のある判断になります。

 

S字型と裾の違いの見分け方

点が直線に沿わず、なめらかなS字型に曲がる場合は、データの裾の形が正規分布と異なる可能性があります。

両端の点が直線より大きく外へ開くような形では、極端な値が正規分布より出やすい、いわゆる裾の重い分布が考えられます。

反対に、両端が内側へ入るような形では、極端な値が少ない裾の軽い分布かもしれません。

ただし、端の数点はデータ数の影響を受けやすいため、少数のデータだけで裾の性質を断定することは避けましょう。

S字型のずれを見つけたら、ヒストグラムと箱ひげ図も作成し、外れ値や複数の集団が混じっていないかを確認します。

工程データでS字型が見られるときは、異なる機械、異なる作業条件、異なる材料が混在している場合もあります。

統計的な変換を考える前に、データの取得過程を振り返ることが問題解決につながるでしょう。

 

片側の曲がりと外れ値の確認

Q-Qプロットで片側だけが大きく曲がる場合は、分布の歪みを示している可能性があります。

右側の点だけが上方へ急に離れる形なら、高い値の側に長い尾を持つ右裾の分布が疑われます。

売上額、待ち時間、故障までの時間など、値が0未満にならず一部の大きな値が出やすいデータでは、この形が現れやすい傾向があります。

反対に、低い値の側だけが大きく離れていれば、下側の外れ値、下限値への集中、左に歪んだ分布などを検討します。

端から大きく孤立した一点は、外れ値である可能性と、重要な異常を示す値である可能性の両方があります。

安易に削除するのではなく、入力誤り、測定条件、対象の取り違え、実際に発生した異常のいずれなのかを確認してください。

業務上意味のある外れ値なら、除外した分析だけでなく、含めた分析も記録しておくことが望ましいでしょう。

 

正規性確認で注意したい条件と判断基準

続いては、Q-Qプロットだけに頼りすぎないための注意点と判断基準を確認していきます。

確認項目	注意したい内容
データ数	少なすぎると形の判断が不安定
データの独立性	時系列や繰り返し測定では相関を確認
複数集団の混在	条件別に分けて可視化する

 

データ数と視覚判断の限界

Q-Qプロットは直感的に理解しやすい一方で、点の並び方を読む方法であるため、データ数の影響を受けます。

10件未満の小さな標本では、偶然のばらつきと本当の非正規性を区別しにくいことがあります。

反対に、数千件を超える大きな標本では、ごく小さなずれも見えやすくなります。

そのため、完全な正規分布かどうかという二択ではなく、予定している分析に対して正規性の近似が十分かを考える視点が必要です。

平均の比較や回帰分析では、元データそのものではなく、残差の正規性が重要になる場合もあります。

たとえば回帰分析を行うなら、予測値との差である残差を計算し、その残差でQ-Qプロットを作るとモデルの適合を確認しやすくなります。

目的に応じて、何の正規性を確認するべきかを最初に決めておきましょう。

 

ヒストグラムと箱ひげ図の併用

Q-Qプロットだけでは、データがどの値にどれだけ集中しているかを直感的に把握しにくい場合があります。

そこで、同じデータからヒストグラムと箱ひげ図も作成して併用する方法が有効です。

ヒストグラムでは山の数、左右の歪み、値の集中具合を確認できます。

箱ひげ図では、四分位範囲、中央値、外れ値候補を簡潔に確認できます。

Q-Qプロット、ヒストグラム、箱ひげ図の三つを並べると、分布の形と外れ値の両方を多角的に判断できます。

たとえばQ-Qプロットに曲がりがあり、ヒストグラムに二つの山が見えるなら、単なる正規性の問題ではなく、異なるグループが混在している可能性があります。

この場合は、部門、製品、担当者、期間などの属性でデータを分けて再確認することが有効です。

 

統計的検定との使い分け

正規性を数値として検定したい場合は、Shapiro-Wilk検定やKolmogorov-Smirnov検定などが使われます。

ただし、標準的なExcelにはこれらの検定を直接実行する機能が常に用意されているわけではありません。

アドイン、統計ソフト、R、Pythonなどを使う場面もあります。

統計的検定ではp値を確認できますが、p値だけで分布の実務的な問題の大きさが分かるわけではありません。

検定結果とQ-Qプロットを併用すると、統計上の差と実務上気になる形のずれを分けて考えやすくなります。

大標本では小さなずれでも検定が有意になることがあり、小標本では大きなずれを見逃すこともあります。

ExcelでのQ-Qプロットは、まずデータの状態を把握し、追加の検定や変換が必要かを判断するための実用的な入口といえるでしょう。

 

エクセルでQ-Qプロットを作成する際のよくある失敗

続いては、ExcelでQ-Qプロットを作るときに起こりやすい失敗と対処法を確認していきます。

よくある失敗	主な原因	対処法
#NUM!エラー	確率が0または1	順位補正を使う
グラフが不自然	折れ線グラフを選択	散布図に変更
点が対応しない	並べ替え漏れ	測定値を昇順にする

 

見出し行を含めた数式範囲のずれ

Excelでは、1行目に見出しがあることを忘れて数式範囲を指定すると、順位やデータ数がずれることがあります。

今回の例では、数値データはA2から始まるため、COUNT関数もA2からA21を対象にします。

もしCOUNT($A$1:$A$21)としても、A1は文字列なのでCOUNT関数では数えられません。

しかし、AVERAGE、STDEV.S、グラフ範囲などでは、見出しの扱いを統一したほうが後の確認が容易です。

数式をコピーするときは、データ件数を固定する範囲に$記号を付けることも重要です。

COUNT($A$2:$A$21)のような絶対参照にしておけば、下の行へコピーしても標本数の範囲が動きません。

数式バーで各セルをクリックし、意図した参照範囲になっているかを確認する習慣を付けると、作図ミスを減らせます。

 

欠損値と文字列が混在する場合

測定データに空白、ハイフン、未測定、N/Aなどの文字列が含まれる場合は、そのままQ-Qプロット用の列に使わないようにします。

COUNT関数は数値だけを数えますが、並べ替えやグラフの範囲選択では空白や文字列が見た目の混乱につながります。

元データを残したまま処理するなら、別列で数値だけを抽出してから並べ替える方法が安全です。

ExcelのFILTER関数を利用できる環境では、数値の行だけを取り出すこともできます。

この数式は、A2からA100のうち数値と判定されたセルだけを抽出します。

ただし、古いExcelではFILTER関数が使えない場合があるため、その際はフィルター機能や手作業での確認が必要です。

欠損値を0として埋めるかどうかは、統計処理では大きな意味を持ちます。

0が実際の測定値ではないなら、空白を安易に0へ置き換えず、欠損として扱う理由を明確にすることが必要です。

 

データの並べ替え後に列対応を崩す問題

測定値のほかに日付、製品番号、担当者などの関連情報がある場合、A列だけを並べ替えると行の対応関係が崩れます。

元データ表をそのまま使うときは、関連する列を含めた表全体を選択して並べ替えましょう。

Q-Qプロット専用の作業列を別シートに作る方法なら、元の表を保護しながら計算できます。

また、数式で参照している列を並べ替える場合は、数式の結果がどう変わるかを確認してください。

Excelのテーブル機能を使っていると、列名で参照できるため、作業範囲を管理しやすくなります。

Q-Qプロット作成用のデータは、元データのコピーを使い、並べ替え前後の状態を残しておくと検証しやすくなります。

分析結果に説明責任が必要な場面では、元データ、加工後データ、グラフを別々に保存し、作成日や対象期間を記録しておくこともおすすめです。

 

まとめ エクセルでQ-Qプロットを作成する正規性確認の手順

ExcelでQ-Qプロットを作成するには、まず測定値を昇順に並べ替え、順位と累積確率を計算します。

累積確率は、順位をi、データ数をnとしたとき、(i-0.5)/nで求めると、0と1を避けながら理論分位数を計算できます。

次にNORM.S.INV関数で標準正規分布の理論分位数を求め、理論分位数を横軸、実際の測定値を縦軸にした散布図を作成しましょう。

点が近似直線の周辺におおむね並ぶなら、データは正規分布に近いと判断する材料になります。

一方で、S字型の曲がり、片側だけの大きなずれ、孤立した点が見られる場合は、歪み、外れ値、複数グループの混在を確認する必要があります。

Q-Qプロットは正規性を一目で把握できる有用な手法ですが、ヒストグラム、箱ひげ図、データの取得条件も合わせて確認することが重要です。

Excelの関数と散布図だけでも、正規性の確認に必要な基本的なQ-Qプロットを作成できます。

データを正しく整え、数式の参照範囲とグラフの軸を確認しながら、日々の品質管理や統計分析に役立てていきましょう。