交叉分析表

第七章 交叉分析表
交叉分析表
市場調查或民意調查，常利用交叉分析表來以探討兩個類別變數間之 關聯性（如：地區別與某政策之贊成與否、性別與偏好政黨、教育程 度與使用品牌、品牌與購買原因、所得與是否有數位相機、……）。
於Excel中，交叉分析表係利用『樞紐分析表』或『假設狀況分析/資 料表』來建立。不過，還是以前者較為簡單。所以，我們就僅介紹 『樞紐分析表』。
7
7. 於『Σ值』方塊，單按『加總-性別』項右側之向下箭頭，續選「值欄位設定 (N)…」，轉入『值欄位設定』對話方塊
8
8. 於『摘要值欄位方式(S)』處將其改為「項目個數」，以求算出現次數（即記 錄數）；續於上方『自訂名稱(C)』處，將原內容改為『人數』
9
9. 按
鈕，L3儲存格處之樞紐分析表已改為人數
已有一空白的樞紐分析表，且右側也有一個『樞紐分析表欄位清單』窗格
6
6. 於右側『樞紐分析表欄位清單』窗格上方之『選擇要新增到報表的欄位：』 處，以拖曳之方式，將『□ 居住地區』拉到『欄標籤』方塊；將『□ 政黨傾 向』項拉到『列標籤』方塊；將『□ 性別』拉到『Σ值』方塊，可求得初始 樞紐分析表，表內所求算之統計量，其預設值為求算選取欄位（性別）之加 總。因為，係要求人數（即項目個數），故將那一個欄位拖曳到『Σ值』方塊， 均不影響本例之求算動作。
位』處，以滑鼠拖曳『性別』欄位。將其拉到『報表篩選』方塊內， 即可完成加入篩選依據之設定，獲致新的樞紐分析表
24
以「性別」為篩選依據之樞紐分析表，會於上方加有一下拉式選擇表
目前其上顯示「（全部）」，故樞紐分析表內，所顯示者為全部資料的交叉表 分析結果。欲查閱不同性別資料時，可單按右側之向下箭頭，將顯示各性別之 內容以供選擇：
43
由於其欄位與項目之配對組合可依任何順序排列，故公式也可輸入成： =GETPIVOTDATA(“人數”,$L$3,“居住地區”,“北區”,“政黨傾向”,“民 進黨”) 但這種公式，所有內容均為常數，不適合抄給別的儲存格。故將其改為 =GETPIVOTDATA("人數",$L$3,"政黨傾向",$T5,"居住地區",U$4) 並抄給U5:X10，即可取得各地區不同政黨傾向之觀察值：
10
10. 利用捲動軸按鈕，將表格調往螢幕左上角，以節省畫面空間。點按M3儲存格， 將其『欄標籤』字串改為『居住地區』；點按L4儲存格，將其『列標籤』字 串改為『政黨傾向』，於M4:P4輸入各數字所對應之居住地區，於L5:L10輸 入各數字所對應之政黨名稱，以利閱讀 輸入時，得逐格輸入，無法一次複製多格內容。
選妥後，按
鈕，樞紐分析表內容將轉為僅顯示該性別之內容而已。
如，僅過濾出「1」（男性）之資料而已：
25
馬上練習
為範例Ch07.xlsx『加入地區別篩選』工作表中之交叉表，加入以『居 住地區』當篩選依據，僅顯示居住地區為『南區』（3）之結果，並加 以解釋。
26
變更樞紐分析表的版面配置
欲變更樞紐分析表的版面配置，如：將樞紐分析表資料進行轉軸（移 轉欄列方向）或移動資料項位置，均可以直接拖放欄位方式，來更改 樞紐分析表資料的版面配置。當重新組織樞紐分析表的資料時，它會 自動重新計算，且不影響來源資料。
4
3. 於上半部，選「選取表格或範圍(S)」，其內所顯示者恰為薪資資料之範圍 （Excel會自動判斷正確範圍，若有不適，仍可自行輸入或重選正確之範圍）
4. 於下半部，選「已經存在的工作表(E)」項，續選按L3儲存格。表欲將樞紐分 析表安排於目前工作表之L3處
5
5. 按
鈕，續利用捲動軸，轉到可以看見L3儲存格之位置，可發現
23
加入篩選依據
樞紐分析表內，尚允許加入分頁項目（如：性別），作為交叉表的上 一層分組依據，以便查閱不同性別，各地區的政黨傾向。
假定，欲續於前文之樞紐分析表內再加入『性別』作為分頁依據。其 處理步驟為：（詳範例Ch07.xlsx『加入篩選依據』工作表）
1. 以滑鼠單按樞紐分析表內任一儲存格 2. 於右側『樞紐分析表欄位清單』窗格上方之『選擇要新增到報表的欄
11
按
或
之向下箭頭，可就如下式之選單：
12
選擇要保留或取消那一類別之資料，續按
鈕，樞紐分析表可立
即改成最新之交叉結果：（假定，取消政黨傾向為「無」之類別）
13
馬上練習
針對範例Ch07.xlsx之『政黨傾向與性別』工作表，求政黨傾向交叉性 別之樞紐分析表。
14
馬上練習
針對範例Ch07.xlsx『品牌交叉偏好原因』工作表，求品牌交叉偏好原 因之樞紐分析表。
36
馬上練習
依範例Ch07.xlsx『性別交叉所得』工作表資料，將所得分為 『~40000』、『40001~50000』與『50001~』三組，建立性別交 叉所得之筆數與縱向百分比：
37
僅取部分資料區間分組
不過，問卷上常有答了A題，才可續答B題之情況。如：有使用手機者 才須要問其平均月費。因此，其資料就得略加整理才能進行後續之交 叉分析。
1. 以滑鼠單按樞紐分析表內任一儲存格
16
2. 於右側『樞紐分析表欄位清單』窗格上方之『選擇要新增到報表的欄位』處， 以滑鼠拖曳『□ 性別』欄位。將其拉到右下方之『Σ值』方塊內，『人數』 項目之下。所增加之內容，目前係加於原各欄之右側
17
3. 以滑鼠拖曳『欄標籤』方塊內『Σ值』項目，將其拉到『列標籤』方塊內， 『政黨傾向』項目之下
如範例Ch07.xlsx『手機平均月費』工作表內，B欄若為2，即表示未使 用手機，當然不必將其手機月費納入樞紐分析表：
38
1. 停於B欄，切換到『資料』索引標籤，按『排序與篩選』群組之 『從最 小到最大排序』鈕，使該欄資料為1者，排於前面。如此，可將有手機者集中 在一起
2. 假定，要將手機月費分為~200、201~400、401~600、601~等四組。於E2
31
1. 以滑鼠單按L6之『人數』標題 2. 將滑鼠移往『人數』標題之上緣框邊，指標將由空心十字轉為四向箭頭，按
住滑鼠拖曳，拖到『%』標題之下，再鬆開滑鼠，將可獲致已移妥資料項目 的新樞紐分析表
32
區間分組
無論是文字、日期或數字，於樞紐分析表中，均是將不重複出現之內 容視為一個類別，去求算交叉表之相關統計數字。當碰上重複性較低 之日期或數字，很可能每一個數值均是唯一，而產生幾乎無法縮減其 類別之情況。
2
建立樞紐分析表
茲以範例Ch07.xlsx『政黨傾向與地區別』工作表為例，進行說明建立 交叉分析表之過程，該表有1000筆受訪者之資料，各欄內之代碼意義 請參見表內文字說明。
3
பைடு நூலகம்
1. 以滑鼠單按問卷資料之任一儲存格 2. 切換到『插入』索引標籤，按『表格』群組『樞紐分析表』鈕，
轉入『建立樞紐分析表』對話方塊
42
茲以範例Ch07.xlsx『GETPIVOTDATA』工作表政黨傾向交叉地區別之資料為 例，若要以下表取得居住於『北區』政黨傾向『民進黨』之觀察値（人數）， 可使用 =GETPIVOTDATA("人數",$L$3,"政黨傾向","民進黨","居住地區","北區") 表示要於L3這個樞紐分析表中，取得『人數』列（第6、第9、…第21）之內 容，其對應位置為：『政黨傾向』為「民進黨」、『居住地區』為「北區」。 取得此一公式甚為簡單，於U5先輸入=號再點選M6即可：
34
將F5:F9時間分組與G4:H4性別之數字改為字串，將更易判讀樞紐分析表之內 容：
當然，也可以前文之技巧，一次即求出人數及縱向百分比：（詳範例 Ch07.xlsx『性別交叉運動時間-分組加上%』工作表）
35
馬上練習
依範例Ch07.xlsx『年齡分組交叉性別』工作表之性別與年齡資料，將 年齡分為『~35』與『36~』兩組，建立性別交叉年齡之筆數與縱向 百分比：
欄位名稱為用雙引號括起來的文字串或以文字型態存放之儲存格內容， 用來指明要在樞紐分析表取得何種標籤文字下所對應之資料。如："人 數"、"%"。
樞紐分析表可以是表中的一個儲存格或是儲存格範圍，或是樞紐分析 表上方的一個標籤。
欄位1,項目1,…為1到126組成對的欄位名稱與項目名稱，用以標明要 擷取的資料，其配對組合可依任何順序排列。
輸入
=IF(C2<=200,1,IF(C2<=400,2,IF(C2<=600,3,4)))
並將其抄給E2:E120，即抄給有使用手機者而已：
39
3. 點按A1:E120之任一儲存格，切換到『插入』索引標籤，按『表格』群 組『樞紐分析表』鈕，轉入『建立樞紐分析表』對話方塊，重選 A1:E120當來源範圍（此範圍即為有手機者之資料）
18
4. 按右下方『Σ值』方塊內，『加總-性別』項右側之向下箭頭，續選「值欄位 設定(N)…」，轉入『值欄位設定』對話方塊，於『摘要值欄位方式(S)』處將 其改為「項目個數」，以求算出現次數，另於『自訂名稱(C)』處，將原內容 『加總-性別』改為『%』
19
5. 切換到『值的顯示方式』標籤
20
6. 按『值的顯示方式(A)』下方之向下箭號，選取使用「總欄數的百分比」
15
加入百分比
就算求得前述之樞紐分析表，我們也不太會分析。得再加入百分比資 料，才方便解釋。於表中可加入三種百分比： 總百分比 各儲存格之數字除以總樣本數 欄百分比 各欄內之儲存格數字除以該欄總樣本數 列百分比 各列內之儲存格數字除以該列總樣本數
假定，欲於原建立之『政黨傾向交叉居住地區』交叉表內，加入欄百 分比。其操作步驟為：（詳範例Ch07.xlsx『加入欄百分比』工作表）
40
4. 往後之操作步驟，即同於前文各節。假定，將其安排為性別交叉手機月費分 組資料，並求其欄百分比
41
取得樞紐分析表內容GETPIVOTDATA()
由於要進行卡方分析時，得同使用到『觀察值範圍』與『期望值範 圍』，故得先介紹如何以GETPIVOTDATA()函數取得『觀察值範圍』。
GETPIVOTDATA()函數可用來取得樞紐分析表之內容，其語法為： GETPIVOTDATA(data_field,pivot_table,field1,item1,field2,ite m2,...) GETPIVOTDATA(欄位名稱,樞紐分析表,欄位1,項目1,…)
27
欄列位置互換
欲將樞紐分析表進行轉軸（移轉欄列方向），其處理步驟為：（詳範 例Ch07.xlsx『欄列位置互換』工作表）
1. 以滑鼠單按樞紐分析表內任一儲存格
28
2. 於右側『樞紐分析表欄位清單』窗格，以滑鼠拖曳方式，將『欄標籤』與 『列標籤』之內容互換位置，欄/列內容即已互換：（目前L5:L8之欄標籤內 容為各地區別，只是L4之標題仍為"政黨傾向"；M4:R4之列標籤內容為各政 黨別，只是M3之標題仍為"居住地區"）
如，範例Ch07.xlsx『運動時間未分組』工作表，性別交叉每次運動時
間之結果，有很多種時間係獨立存在產生一列內容，由於組數太多，
於資料分析時並無多大作用：
33
較理想之方式為將毎次運動時間分組，以縮減其組數。假定，要將毎次運動時 間分為0~30、31~60、61~90、91~120與121~五組。可於資料表尾部，新 增一『時間分組』欄以 =IF(C2<=30,1,IF(C2<=60,2,IF(C2<=90,3,IF(C2<=120,4,5)))) 之運算式，將其分為六組。重建一次樞紐分析表，即可得到經縮減組數後之交 叉表：（詳範例Ch07.xlsx『性別交叉運動時間-分組』工作表）
29
3. 將L4之標題改為"居住地區"，M3之標題改為"政黨傾向"，使標題與其內容相 符
30
移動樞紐分析表資料項目
移動樞紐分析表資料項目位置，當然可於『樞紐分析表欄位清單』窗 格之『Σ值』方塊，以拖曳方式進行移動位置。但也可以於樞紐分析表 上，直接以滑鼠進行拖曳！
於範例Ch07.xlsx『移動資料項目』工作表之樞紐分析表，若欲將『人 數』與『%』兩資料項，進行互換位置：
7. 按
鈕，獲致含人數及縱向百分比之交叉分析表
21
馬上練習
針對範例Ch07.xlsx『政黨傾向與性別之欄百分比』工作表，求政黨傾 向交叉性別之樞紐分析表，同時顯示人數及其縱向之欄百分比，並解 釋其結果。
22
馬上練習
針對範例Ch07.xlsx『品牌交叉偏好原因之欄百分比』工作表，求品牌 交叉偏好原因之樞紐分析表。於表中同時顯示人數及其縱向之欄百分 比，並解釋其結果。