スマホ用は別頁
散布図とクロス集計

このページの解説に使用するExcelのバージョンは,Excel2007およびExcel Online(https://www.office.com/)です.Excel Onlineは,無料なのに多くのことができますが,データ,プログラムともWeb上にあるため,作業はサーバとの交信になり,やや遅いかなという感じです.無料だから文句は言えない.

1. 主な内容

 このページでは,主に次のような内容を扱います.
(1) Microsoft Excelを使って【表1.1】のような量的データから【図1.1】のような散布図を作る方法
-【表1.1】-
身長体重
163.0 56.0
161.1 54.0
162.5 53.0
164.0 59.0
164.4 66.2
165.3 60.2
165.6 62.0
165.8 59.4
163.0 60.0
166.8 64.3

-【図1.1】-

(2) 【表1.2】のような質的データから【表1.3】のようなクロス集計表を作る方法
-【表1.2】-
卒業中入学高
A中X高
B中Y高
B中Z高
C中Y高
C中Z高
A中X高
B中Y高
C中Z高
B中Y高
A中X高
-【表1.3】-
個数入学高
卒業中X高Y高Z高総計
A中33
B中314
C中123
総計34310

 2変数の関係を分析するための前提として,上記のグラフの描き方を確かめておきます.
(3) 質的データと量的データの組合せ,複数系列散布図(層別散布図)などについても確かめておきます.

2. 散布図

-【表2.1】-
氏名身長体重
平清盛163.0 56.0
織田信長161.1 54.0
徳川家康162.5 53.0
武田信玄164.0 59.0
上杉謙信164.4 66.2
吉備真備165.3 60.2
豊臣秀吉165.6 62.0
藤原道長165.8 59.4
西郷隆盛163.0 60.0
足利義満166.8 64.3
(1) Excelのワークシートに右のようなデータがあるとき,マウスドラッグにより黄色で示した範囲を選択しておきます.
• 右の例で言えば氏名欄に相当する列ラベルがあっても,それを含めないようにするのが第1のコツです.
• また,身長,体重の相当する列ラベルを含めるのが第2のコツです.
(2) Excel画面上端のメニューをたどって,次のように進みます.
 挿入→散布図(一番単純な点々だけのもの)
(凡例,グラフタイトル,マーカーの色形などは好みに応じて変更します)
 これにより図2.1のような散布図ができます.

-【図2.1】-
詳しくは後に述べるが,図2.1のように身長が増えると体重も増えるという関係(この図では右上がりの傾向で点が並んでいる)を「正の相関関係」という.

-【図2.2】-
図2.2のように右下がりの傾向で点が並んでいる場合は「負の相関関係」という.
 このように,散布図を描くと,横軸にとられた変数と縦軸にとられた変数の関係が,視覚的に分かるので,散布図を作っておくことは2変数の関係を分析する上で不可欠な作業です.

3. 複数系列散布図(層別散布図)

 与えられたデータの全部を1つの散布図にしても規則性が分かりにくい場合に,いくつかの系列(層)に分けると,規則性が分かることがある.このような場合に,1個の散布図の中で系列(層)ごとに記号を変えて図示したものを複数系列散布図(層別散布図)という.
-【表3.1】-

身長
x
男子
y1
女子
y2
平清盛162.0 53.5
織田信長156.7 52.3
徳川家康159.2 58.0
武田信玄164.0 60.2
上杉謙信164.4 56.0
小野小町152.1 45.0
紫式部152.3 42.3
清少納言157.6 43.5
卑弥呼156.3 41.2
樋口一葉150.1 42.3

 複数系列散布図を作るには,左の表のように,第1の変数xは共通の列に書き,第2の変数y1およびy2を別の列に書きます.
-【図3.1】-

-【表3.2】-
身長
x
男子
y1
女子
y2
平清盛162.0 53.5
小野小町152.1 45.0
織田信長156.7 52.3
徳川家康159.2 58.0
紫式部152.3 42.3
清少納言157.6 43.5
武田信玄164.0 60.2
卑弥呼156.3 41.2
上杉謙信164.4 56.0
樋口一葉150.1 42.3


 複数系列散布図のデータとして重要なことは,「第1の成分が同じ列にあること」「第2の成分(y座標)がどの列に書かれているかということ」です.
 次の-【表3.2】-から作られる複数系列散布図は,-【表3.1】-から作られる複数系列散布図と全く同じで,(x, y1)および(x, y2)を座標とする点が描かれるということです.

 さらに踏み込んで言えば,
-【表3.3】-の紫式部,織田信長の欄のように共通のx座標(国語)以外にy1(数学)もy2(英語)も書き込まれているデータがあれば,
-【表3.3】-
氏名国語数学英語
紫式部62 55 59
徳川家康65 65
織田信長70 80 75
平清盛75 75
小野小町82 65

-【図3.2】-

共通のx座標(国語)を横の座標として,y1(数学)を縦の座標とする点(x, y1)y2(英語)を縦の座標とする点(x, y2)が2つ並んで表示されます.
-【表3.4】-
体重
氏名身長1組2組3組
平清盛164 42
織田信長164 57
徳川家康151 40
武田信玄164 54
上杉謙信166 59
樋口一葉148 33
卑弥呼157 34
清少納言153 49
紫式部149 47
小野小町148 36
葛飾北斎160 52
松尾芭蕉168 53
井原西鶴171 60
俵屋宗達169 67
【問題3.1】
 右の表3.4--架空データ--(画面上でドラッグ→コピー・貼り付けすることによりExcel上に写せます)のデータを複数系列散布図にして表示してください.
 なお,グラフ中に氏名を表示しなくてもよい.
(解答例)

-【図3.3】-

4. データラベル

-【表4.1】-
氏名国語英語
紫式部62 55
徳川家康65 65
織田信長70 80
平清盛75 75
小野小町82 65
 散布図において,単にマーカーの点だけが表示されてるだけでは,それぞれのマーカーが誰の得点なのか,1つずつ表の数字を確かめなければ分かりにくく不便だと感じることがあります.
 表4.1から作成した散布図において,右図のように各マーカーにデータラベルを付ける方法

-【図4.1】-
Excel2007以前で,そのままデータラベルとして使えるものは,
(1)系列名:表4.1で言えば「英語」・・・これをラベルに貼り付けても皆同じラベルの「英語」になるから,まったく有難くない.なお,表4.1で国語は系列名ではない.複数系列がある場合でも,どの系列にも共通のX座標なので,系列とは見なされていない.
(2) X値:表4.1の紫式部のデータで言えば,62という国語の得点(数値)がこれに該当する.
(3) Y値:表4.1の紫式部のデータで言えば,55という英語の得点(数値)がこれに該当する.
⇒ (2)(3)は単独で,もしくは両方同時に使うことができ,(2)(3)を両方とも指定した場合,前の図4.1のようになる.
新しいバージョンのExcel(2010〜)では,
(4) ラベルのオプションとして「セルの値」も指定できるようになったので,表4.1で言えば,黄色の背景色の氏名の欄が指定できる.(Excel2007以前およびExcel onlineではこの指定はできない.)
 次の図4.2は,生徒30人の国語(横軸),数学(縦軸)の散布図に氏名のデータラベルを付けたものです・・・もちろん,氏名は冗談です.親しみやすい名前を選んだもの・・・

-【図4.2】-
 このデータラベル付き散布図から,教訓が1つ言えます.すなわち,
「データラベルがあると便利であるが」
「データラベルが重なるくらいデータ数が多いときには,かえって見にくくなる」
⇒ 画面の大きさにもよるが,データ数が1桁のオーダーならデータラベルがあると見やすい.データ数が30とか40以上と多くなると,ラベルが重なって煩わしくなる.
※Excel2007以前のバージョンでデータラベルを付ける方法の1つとして,公開されているマクロを利用する方法があります.これを利用するのが1つの選択肢です.ただし,以下に紹介するのは,データ数が1桁以下の場合で,手作業で加工できる程度の場合です.

[A] データ数が1桁のオーダーの場合に,Excel2007もしくはExcel Onlineでできるデータラベルの付け方
 前出の表4.1のデータ(5名の生徒の国語と英語の得点)において,データラベルとして使いたい氏名欄を(共通のX座標)国語を外して,英語の欄に縦横を入れ替えて列ラベル(系列名)となるように貼り付ける.ここで重要なことは,系列名をラベルに取り込むのだから,どのデータも同じ系列にならないように,表4.2のように,英語の得点を対角線に沿って並べることです.
-【表4.2】-
平清盛小野小町織田信長徳川家康紫式部
7575
8265
7080
6570
6255
 この形にしておくと,各系列のデータラベルの内容として「系列名」を選べば「氏名」が表示されます.
 ただし,デフォルトですべての系列が異なる色と形のマーカーで表示されますので,1種類にしたいときはマーカーのオプションを個別に変更することになります.(データ数が1桁で比較的手作業でできる範囲だから可能な話です)
[B] データ数が1桁のオーダーの場合に,Excel2007でできる(Excel Onlineではできない)データラベルの付け方
-【表1.8】-
氏名国語英語
紫式部62 55
徳川家康65 65
織田信長70 80
平清盛75 75
小野小町82 65

 右の表1.8のようなデータがあるときに,次の図1.5のような散布図を作り,氏名欄をデータラベルにする方法です.
 初めに,国語,英語という列ラベルを含む数値データの2列を選択して,挿入→散布図とします.

-【図1.5】-
 レイアウト→データラベル→その他のデータラベルオプション→ラベルの内容をX値またはY値にしておきます.(この段階でラベルが数値として表示されます)
 個々のラベルをクリック(ポイント)すると,その値だけ選択された状態になります→画面上の方にある数式欄に=と書き込んでから目的の氏名をクリックします.(本当はこれでできているのですが,次の操作に移ったときに画面上の表示が変わります)
 次のラベルとクリック(ポイント)→→画面上の方にある数式欄に=と書き込んでから目的の氏名をクリックします.(これの繰り返しです)
 どの氏名をクリックするかは,X値(国語の得点)とY値(英語の得点)を見て考えます.
 このような手間のかかる作業ができるのも,データ数が少ない場合です.

5. クロス集計表

【表5.1】のような質的変数2つ(卒業中と入学高)から【表5.2】のようなクロス集計表を作るには
-【表5.1】-
卒業中入学高
A中X高
B中Y高
B中Z高
C中Y高
C中Z高
A中X高
B中Y高
C中Z高
B中Y高
A中X高
-【表5.2】-
個数入学高
卒業中X高Y高Z高総計
A中33
B中314
C中123
総計34310



-【図5.1】-
 Excelで表5.1の範囲を選択してから,挿入→ピボットテーブル→「テーブルまたは範囲を選択」は済んでいるから,その下の「既存のワークシート」を選んで,右へ下へ向かってデータのない場所を選ぶ→OK
画面右側にピボットテーブルのフィールドリストという作業用のウィンドウが出るので,右図のように3回ドラッグする.
 ■意外に重要■
チェックボックスにチェックを入れるという操作を行うと,思わぬ結果になり易いので,右図のようにチェックボックスよりも右側の空白部分からドラッグするとよい.(結果としてチェックが勝手に入る)
 完成形の【表5.2】を想定すると,「卒業中」が行ラベル(左端のラベル)になるのだから左側に持って行くのは分かり易い.同様にして,「入学高」が列ラベル(上端のラベル)になるのだから上に持って行くのは分かり易い.
 分かりにくいのは,3回目の操作で,白lと書かれた欄に連れて行く物は,「卒業中」でも「入学高」でもよい・・・これにより数字が入る.(「卒業中」から引っ張っていった場合と「入学高」から引っ張っていった場合の違いは,左上端のタイトルが「データの個数 / 卒業中」と表示されるか「データの個数 / 入学高」と表示されるかという表示の違いのみで,数字の値はどちらでやっても同じになる.)
性別態度
支持する
支持しない
支持しない
支持しない
支持する
支持する
支持しない
支持する
支持する
どちらとも言えない
【問題5.1】
 右の表-内閣支持率のつもり-(画面上でドラッグ→コピー・貼り付けすることによりExcel上に写せます)からピボットテーブルを使って性別・態度別のクロス集計表を作ってください.
(解答例)
データの個数
/ 性別
態度
性別どちらとも言えない支持しない支持する総計
1225
2215
総計34310

6. ピボットテーブルのグループ化

 はじめに,「質的変数」「量的変数」の組合せと「散布図」「ピボットテーブル」の利用について,まとめておきます.
 次の表から分かるように,散布図が利用できるのは,量的変数量的変数の組合せだけです.これに対して,ピボットテーブルは,量的変数量的変数質的変数量的変数質的変数質的変数のどの組み合わせについても利用できます.
-【表6.1】-
変数の組散布図ピボットテーブル
量的変数量的変数
質的変数量的変数×
質的変数質的変数×
 この表のうちで,◎の部分については既に説明しているので,〇の部分,すなわち,少なくとも一方が量的変数であるときにピボットテーブルを適用する場合を説明する.
 例えば,量的変数量的変数の組合せの場合,散布図が利用できるから,ピボットテーブルは要らないのでないかと思われるかもしれませんが,次の図のようにデータ数が多い場合,散布図ではマーカーが重なって詳しいことが分からなくなります.

-【図6.1】-
これを次の表のようなクロス集計表に書き換えると,分布状況だけでなく,正確な分布の個数も分かるようになります.

-【表6.2】-
個数
60-6565-7070-7575-8080-8585-90総計
85-90535821
80-8511529
75-80211219251170
70-7552131691560
65-70333312
60-6531138
総計13735454436180

 最終的には,表6.2のようなクロス集計表になるが,途中経過には注意すべき点が幾つもある.
-【表6.3】-
61.968 63.298
61.747 80.549
61.994 64.535
63.052 73.883
62.904 68.557
・・・・・・
63.827 64.417
(1) 1から10までの整数というような整数の場合は,100個あっても10種類の値しか登場しないのに対して,実数(小数)の比較では,原則として「2つの実数が等しい」ということは起こらない.
 だから,右の表6.3のような実数(小数)[画面表示の都合で小数第3位まで表示してある]の組が,例えば100組あれば,原則として,xもyも100種類の異なる値になると想定しなければならない.そのため,表6.3のデータからピボットテーブルを使ってクロス集計表を作成すると,1時的とはいえ100×100通りの表になる.
 集計する立場から言えば,そのような無駄に詳しい表を望んでいるわけではないので,表6.2のように,xについても,yについても適当な幅で「グループ分け」して結果を集計する.
-【表6.4】-
合計 / x
60.8 62.2 62.6 62.8 63.0 63.4 ・・・ 63.8
61.0 61.0・・・
61.2 ・・・
61.4 ・・・
62.2 ・・・
62.4 ・・・
62.9 62.9・・・
64.3 ・・・
64.9 ・・・
65.0 ・・・
・・・ ・・・ ・・・ ・・・ ・・・ ・・・ ・・・ ・・・ ・・・
65.4 ・・・
 行を表6.2のようにグループ分けするには,行ラベル(表6.4で卵色の背景色で示した箇所)を右クリックして,グループ化→先頭の値60,末尾の値90,単位[区間の幅のこと]5→OKとする.
 次に,列を表6.2のようにグループ分けするには,列ラベル(表6.4で水色の背景色で示した箇所)を右クリックして,グループ化→先頭の値60,末尾の値90,単位[区間の幅のこと]5→OKとする.
(2) この例のように,量的変数−量的変数の組をピボットテーブルで集計すると,表6.4の左上端(桃色の背景色で示してある)の表示が,初期設定で「合計/・・・」となっていて,表の中の値も61.0, 62.9, ...などと小数値になる.
 ここで行っているクロス集計の目的から言えば,値の合計は不要で「データの個数」を求めたいのだから,左上端の「合計/・・・」となっている欄を右クリック→データの集計方法→データの個数を選ぶ.
(3) このクロス集計表の重要なねらいは,図6.1の散布図では分かりにくいことを数値的にはっきりさせることだから,図6.1と同じ形で行ラベル(Xの階級分け)も降順にした方がよい.このためには,xの欄(卵色の欄の内の上端のもの)をクリック→降順を選ぶとよい.
(4) 表6.2の行ラベル,列ラベルを見ると,例えば60−65, 65−70 のように区間の境目となっている値は,両方に登場します.このとき,Excelの分析ツール→ヒストグラムを使った場合のような,そのソフト独特の(アメリカ文化独特の?)癖のついた分け方になっていないかどうか,一度は調べておく方がよい.
 具体的には,スポーツの出場資格で「アンダー18」というような X≦18 を自然な分け方としている文化では,60−65, 65−70の意味を60<X≦65, 65<X≦70 としている可能性かあるので,境目となる値X=65.000がどちらに分類されているかを調べておくほうがよい.(コウモリ軍団のように両方に入るということはあり得ないので,60<X≦65型か60≦X<65型かを見る.日本国内向けのレポートでは,60≦X<65型が自然な分け方でしょう)
 筆者の簡単なテストでは,60≦X<65型(以上未満型)になっているので,そのまま使えばよいようです.

-【表6.5】-
性別身長
151.2
152.2
156.9
160.5
160.6
160.8
162.5
163.1
163.6
164
166.7
166.7
167.7
168.6
169.8
169.9
171
175.6
176.1
177.7

 表6.5のような質的変数量的変数の組からも,ピボットテーブルを用いて,表6.6のようなクロス集計表を作ることができます.
 量的変数の方は,そのままでグループ化できます.(階級幅を指定して分類できます)
 質的変数も多数ある場合はグループ化できますが,例えば,滋賀,京都,大阪,兵庫,奈良,和歌山を1つのグループにするというようなことは,自動ではできないので,画面上でShiftキーなどを使いながら指定します.

-【表6.6】-
データの個数 / 身長性別
身長総計
150-15522
155-16011
160-165437
165-170426
170-17511
175-18033
総計11920

-【表6.7】-
縦(cm)横(cm)
9.0 22.3
11.8 19.9
8.1 21.7
7.9 21.8
13.3 22.7
6.6 23.5
11.9 22.5
8.3 22.7
14.2 17.3
7.7 18.2
【問題6.1】
 右の表6.7(画面上でドラッグ→コピー・貼り付けすることによりExcel上に写せます)からピボットテーブルを使って縦横5(cm)ずつの階級幅に分類したクロス集計表を作ってください.
(解答例)
-【表6.8】-
データの個数 / 縦
15-2020-25総計
5-10156
10-15224
総計3710
確率統計のメニューに戻る 高校数学のメニューに戻る