クロス集計とは｜Excelピボットの作り方と読み方

この記事でわかること

クロス集計の意味と、単純集計との違い
Excelのピボットテーブルと COUNTIFS でクロス集計表を作る手順
行の％と列の％の読み分け、差が偶然かをχ²検定で確かめる方法

📌 関連知識：項目を分けて考える基本はチェックシートと層別の使い方で解説しています

先月の不良200件を集計すると、寸法不良が84件、きずが80件、異物が36件でした。報告すると、工場長から「それは昼勤と夜勤で同じなのか」と聞かれます。不良モードごとの件数だけでは答えられません。不良の記録には勤務の欄もあるので、2つの項目を組み合わせて数え直すことにしました。

この「2つの項目をかけ合わせて数える」表がクロス集計表です。全体の件数では見えなかった傾向が見えることがあります。この記事では、Excelでの作り方、行と列のどちらの％で読むか、差が偶然かどうかの確かめ方までを例題で整理します。

クロス集計を使う場面
クロス集計とは：単純集計との違い
Excelでクロス集計表を作る
1. 方法1：ピボットテーブル（おすすめ）
2. 方法2：COUNTIFS
行の％と列の％を読み分ける
差が偶然かをχ²検定で確かめる
クロス集計のよくある失敗
よくある質問
まとめ

クロス集計を使う場面

クロス集計は、分類のデータ（勤務・号機・品種・不良モードなど）どうしの関係を見たいときに使います。

不良モードが号機や勤務で違うかを調べるとき
苦情の内容が品種や地域で違うかを調べるとき
アンケートの回答が部署で違うかを調べるとき

寸法や重量のような測定値どうしの関係を見るなら、クロス集計ではなく散布図と相関係数を使います。測定値を号機ごとに比べるなら、平均を比べる t検定や分散分析の出番です。

クロス集計とは：単純集計との違い

単純集計は、1つの項目ごとに個数を数えた表です。クロス集計は、2つの項目を行と列に置いて、組み合わせごとの個数を数えた表です。

単純集計	寸法不良	きず	異物	合計
件数	84	80	36	200

これを勤務で分けたクロス集計表が次の表です（説明のために組み立てた数字です）。

勤務	寸法不良	きず	異物	合計
昼勤	60	36	24	120
夜勤	24	44	12	80
合計	84	80	36	200

単純集計では寸法不良ときずはほぼ同じ件数でしたが、クロス集計にすると、昼勤は寸法不良、夜勤はきずが多いという違いが見えてきました。層別の考え方を2つの項目で表にしたもの、と考えると整理できます。

Excelでクロス集計表を作る

元のデータは、1件を1行にした形でそろえます。A列に勤務、B列に不良モードを入れ、A1・B1は見出しにします。200件ならデータは2〜201行目です。

方法1：ピボットテーブル（おすすめ）

データの中のセルを選び、「挿入」→「ピボットテーブル」を押して、新しいシートに作ります
右のフィールドの一覧から、「勤務」を「行」へ、「不良モード」を「列」へドラッグします
「不良モード」をもう一度「値」へドラッグします。文字の項目なので、集計方法は自動で「データの個数」が選ばれます

これで合計つきの表ができます。値が「合計」と出たら、「値フィールドの設定」の「集計方法」タブで「データの個数」に変えます。元のデータを増やしたときは、ピボットテーブルを右クリックして「更新」を押します。

方法2：COUNTIFS

報告書の決まった形に数字を入れたいときは、関数で数えます。E2 に「昼勤」、F1 に「寸法不良」のように見出しを入れて、F2 に次の式を入れ、右と下へコピーします。

=COUNTIFS($A$2:$A$201,$E2,$B$2:$B$201,F$1)

行の見出しは $E2、列の見出しは F$1 と、固定する位置を変えるのがコツです。表記のゆれ（「キズ」と「きず」など）があると別の項目として数えられるので、先に統一します。行の合計はI列、列の合計は4行目（F4:H4）、総合計はI4に入れて、合計が200件と合うかを確かめます。

行の％と列の％を読み分ける

件数のままでは、昼勤120件と夜勤80件で全体の大きさが違うので比べられません。割合に直します。ここで、どちらの方向の％を取るかで答える問いが変わります。

行の％：「昼勤の不良の内訳は？」

勤務	寸法不良	きず	異物	合計
昼勤	50.0％	30.0％	20.0％	100％
夜勤	30.0％	55.0％	15.0％	100％
全体	42.0％	40.0％	18.0％	100％

夜勤の不良の55.0％がきずで、全体の40.0％より多いことがわかります。ピボットテーブルでは、値を右クリックして「値の表示形式」→「行集計に対する比率」を選びます（「値フィールドの設定」の「計算の種類」タブからも選べます）。関数なら =F2/SUM($F2:$H2) です。

列の％：「きずはどの勤務で多い？」

列ごとの合計を100％にすると、きず80件のうち昼勤が45.0％、夜勤が55.0％です。寸法不良84件では昼勤が71.4％です。ただし、この読み方は昼勤と夜勤の件数の差に影響されます。昼勤は不良の総数が多く、全体の60％を占めます。そのため列の％は昼勤が高めに出やすく、きずの昼勤45.0％は全体の60％より低い、のように全体の比率を基準にして読みます。

原因を探すときは、原因にあたる項目（勤務）を行に置き、行の％で比べるのが基本です。「夜勤だときずの割合が高い」と読めます。

注意：不良率ではない

この表は不良が出たあとの内訳です。夜勤のきずの割合が高くても、夜勤の不良率が高いとは言えません。不良率を比べるなら、勤務ごとの生産数を入れて、良品も含めたクロス集計を作ります。

差が偶然かをχ²検定で確かめる

30％と55％の差は、たまたまのばらつきでも起きる大きさなのか。クロス集計表の差を確かめるのがχ²検定（カイ二乗検定）です。

まず、勤務と不良モードに関係がないとしたときの期待度数を計算します。

\[
\text{期待度数} = \frac{\text{行の合計} \times \text{列の合計}}{\text{総合計}}
\]

昼勤の寸法不良なら $ 120 \times 84 \div 200 = 50.4 $ です。Excelでは、F7 に =$I2*F$4/$I$4 を入れて、右と下へコピーします（期待度数の表は F7:H8）。すべてのマスを計算すると次の表です。

勤務	寸法不良	きず	異物
昼勤	50.4	48.0	21.6
夜勤	33.6	32.0	14.4

実際の度数と期待度数のずれを合計したものがχ²です。

\[
\chi^2 = \sum \frac{(\text{観測} – \text{期待})^2}{\text{期待}} = 12.74
\]

Excelでは、観測の範囲と期待の範囲を渡して =CHISQ.TEST(F2:H3,F7:H8) で p 値が出ます。結果は0.0017です。5％より十分小さいので、勤務によって不良の内訳が違うと判断できます。自由度は (行数−1)×(列数−1)＝(2−1)×(3−1)＝2 です。

検定の考え方と手順はカイ二乗検定（χ²検定）とは｜独立性と適合度の検定をExcelで、自由度の意味は自由度とは｜なぜn−1で割るのか・各検定での求め方で解説しています。期待度数が5未満のマスが全体の2割を超えるときや、1未満のマスがあるときは、χ²検定は当てになりません。項目をまとめるか、データを増やします（例題の最小は14.4で問題ありません）。