如何使用Excel製作列聯表

列聯表（contingency table）是社會統計分析很常使用到的一種表格，用來檢視2個名義尺度變項資料（或稱為類別變項）之間的關係，顯示一個變項在另一個變項的各個類別的分布情況。當製作了列聯表之後，通常會進一步執行卡方獨立性檢定、費雪精準檢定、McNemar 檢定或概似比檢定等統計分析，探討2個變項間是否有關聯。

若有統計分析軟體如 SPSS 或 SAS，製作列聯表可說是輕而易舉，但若沒有專業的統計分析軟體，也不必心灰意冷，微軟 Excel 可達到相同的目的。雖然利用 Excel 來製作列聯表的步驟比較麻煩一點，但在沒有任何其他分析軟體的情況下，Excel 是個相當實用的工具。

下面內容將介紹利用 Excel 製作列聯表的2種方法，第1種是透過 Excel 內建的樞紐分析表（pivot table）功能，第2種是運用 COUNTIFS 函數手動自製表格。第2種方法須瞭解儲存格參照的意思和簡易數學運算的方法，若不清楚或不熟悉這方面的內容，可以參考如何使用Excel進行數學計算【基礎篇】以及如何使用Excel進行數學計算【進階篇】。

運用樞紐分析表製作列聯表
1. 僅顯示次數的列聯表
2. 顯示次數和百分比的列聯表
運用 COUNTIFS 函數製作列聯表

運用樞紐分析表製作列聯表

這裡使用〈卡方獨立性檢定的假設檢定〉裡民眾生理性別和政黨支持傾向的資料，共有500位受訪者，第1個變項為受訪者的生理性別（SEX），類別為生理女性和生理男性，第2個變項為受訪者的政黨支持傾向（PARTY），類別為政黨Ａ、政黨Ｂ和政黨Ｃ。因為2個變項皆為名義尺度的變項，所以可透過列聯表來觀察這2個變項之間的關係。

這裡使用 Office 2021 版本，但您可以使用自己持有的 Excel 版本來操作。下面先示範利用樞紐分析表製作基本列聯表的方法，也就是僅顯示各種情況的發生次數，再示範增加百分比至各種情況的操作方法。

➀ 僅顯示次數的列聯表

在 Excel 工作表頁面，除了生理性別和政黨支持傾向這2個變項外，還要有1個受訪者編號的變項 ID，資料輸入完成後點選功能表的插入 » 表格 » 樞紐分析表，帶出「建立樞紐分析表」視窗。

Excel menu of pivot table to create contingency table

在「建立樞紐分析表」視窗裡，先選擇資料來源。因為這裡使用工作表裡的資料，所以勾選選取表格或範圍(S)並在表格／範圍(T)長框裡輸入資料所在的儲存格範圍，您也可以點選長框旁的 ↥ 符號，再用滑鼠從工作表裡圈選資料範圍。此外，須選擇放置樞紐分析表的位置，可以是新工作表或目前的工作表，若選擇已經存在的工作表(E)，要在位置(L)長框裡輸入儲存格的位置，也可利用長框旁的 ↥ 符號從工作表裡圈選儲存格。

Excel dialog box of pivot table to select data range

完成「建立樞紐分析表」視窗裡的選擇後，按下視窗下方的確定，此時工作表的最右側會出現「樞紐分析表欄位」窗格，接下來要利用這個窗格來製作位在儲存格E2的列聯表。

pivot table field pane to create contingency table

在「樞紐分析表欄位」窗格裡，用滑鼠將 ID 拖曳到下方的值方框、SEX 到列方框、PARTY 到欄方框。接著，點選值方框裡加總 – ID欄位最右側的 ▼ 符號，從下拉選單中選擇值欄位設定(N)。在「值欄位設定」視窗裡，先從摘要值欄位方式(S)的選項裡點選計數（顯示各種情況的發生次數、欄合計、列合計等數值），然後在自訂名稱(C)長框裡輸入想使用的欄位名稱，完成後按下視窗下方的確定。

pivot table fields pane to create contingency table

經過上面的步驟後，儲存格E2會出現如下的列聯表，可以清楚地看到生理女性和生理男性傾向於支持政黨Ａ、政黨Ｂ和政黨Ｃ的人數。

original layout of contingency table created by Excel pivot table

如果想讓「欄標籤」和「列標籤」變成欄的變項名稱和列的變項名稱，可以先圈選整個表格，再點選功能表的設計 » 版面配置 » 報表版面配置，從下拉選單中選擇以列表方式顯示(T)，就可顯示變項的名稱 PARTY 和 SEX 而不是「欄標籤」和「列標籤」。

changing pivot table layout to tabular form

光靠次數可能很難看出2個變項之間的關係，加上百分比會讓資料更容易理解。利用「樞紐分析表欄位」可以新增列百分比或欄百分比，下面示範新增列百分比的方法。

➁ 顯示次數和百分比的列聯表

除了人數之外，若還想瞭解不同的生理性別傾向於支持各個政黨的百分比，也可利用「樞紐分析表欄位」來進行操作。再一次把 ID 拖曳至值方框，此時在欄方框裡會多出一個∑值欄位，這是即將新增的百分比在列聯表裡的位置。由於我想讓百分比顯示在列欄位（也就是各個生理性別的下方），所以點選∑值欄位最右側的 ▼ 符號，從下拉選單中選擇移到列標籤，這個∑值欄位即會移動到列方框。

moving column labels to row labels in Excel pivot table pane

接下來要修改新增到值方框裡的欄位數值設定，點選加總 – ID欄位最右側的 ▼ 符號，從下拉選單中選擇值欄位設定(N)。在「值欄位設定」視窗裡，從摘要值方式標籤下方的摘要值欄位方式(S)選項中點選計數，再從值的顯示方式標籤下方的值的顯示方式(A)下拉選單中點選列總和百分比，最後在自訂名稱(C)長框裡輸入想顯示出來的欄位名稱。

adding value in Excel pivot table field pane

經過上面的步驟後，原本位在儲存格E2的列聯表會變成下面的樣子。從增加了列百分比的列聯表可以看到，傾向於支持政黨Ａ的生理女性占了48%而生理男性占了36%，傾向於支持政黨Ｃ的生理女性占了46%但生理男性占了60%。

contingency table with count and column percentage

若想增加欄百分比，只要在「值欄位設定」視窗的值的顯示方式(A)下拉選單裡點選欄總和百分比即可。此外，若想調整數值顯示的格式，可點選數值格式(N)來進行修改。

例如上面顯示次數和百分比的列聯表，如果想讓百分比只顯示到小數點後第1位，可點選「值欄位設定」視窗下方的數值格式(N)，在「設定儲存格格式」視窗的類別(C)選項裡選擇百分比，然後在小數位數(D)方格裡把預設值2改成1。設定完成後按下視窗下方的確定，列聯表裡列百分比的小數點後位數即會變成1位。

changing value format of percentage to one decimal point

從上面的介紹可以發現利用 Excel 的樞紐分析表功能製作出來的列聯表幾乎無異於統計分析軟體如 SPSS 輸出的列聯表，雖然操作上比較麻煩一點，特別是想同時顯示計數和百分比的時候，但在沒有統計分析軟體的情況下，Excel 的樞紐分析表功能是一個不錯的選擇。

運用 COUNTIFS 函數製作列聯表

如果不想利用內建的樞紐分析表功能來製作列聯表，也可以手動製作，過程須使用到 COUNTIFS 函數和簡易的數學運算。這裡同樣使用上面的民眾生理性別和政黨支持傾向的資料，示範在 Excel 工作表裡自製2個變項列聯表的方法。

在帶有分析資料的工作表的空白儲存格處列出2個變項位在列聯表裡的列標籤和欄標籤，以及列合計、欄合計、計數和百分比（%）等想呈現出來的數值名稱，如下圖。為了計算出資料裡不同生理性別的民眾傾向於支持各個政黨的人數，可以利用 COUNTIFS 函數。

counting cell numbers using COUNTIFS function

COUNTIFS 函數適用在帶有數個變項的資料上，並計算出符合2個以上條件時的儲存格次數。這個函數的語法為 COUNTIFS(criteria_range1, criteria, [criteria_range2, criteria2]…)，括號裡的引數指第1個條件的資料範圍和條件、第2個條件的資料範圍和條件，最多可以要求127個條件。例如要計算資料裡生理女性傾向於支持政黨Ａ的人數，可以在上圖的儲存格G3裡輸入下面的語法：

=COUNTIFS($B$2:$B$501, “生理女性”, $C$2:$C$501, “政黨Ａ”)

上面語法裡的 $B$2:$B$501 指儲存格B2到B501的資料範圍，加上「$」符號代表固定這個資料範圍，如果要複製這個語法至其他儲存格的時候，資料範圍不會變更，$C$2:$C$501 也是相同的意思。上面整個語法要求計算出儲存格B2到B501裡名稱為「生理女性」且儲存格C2到C501裡名稱為「政黨Ａ」的人數，語法輸入完成後按下 Enter 會傳回數值120。

運用相同的方法，計算出其他5種情況的人數，輸出結果如下圖。由於計算「生理女性傾向於支持政黨Ａ」人數的語法裡已經固定了資料範圍，所以可以複製儲存格G3的語法再貼上至儲存格H3、I3、G5、H5和I5，然後修改語法裡的條件（例如政黨Ａ變成政黨Ｂ）即可。

copy COUNTIFS function and paste to other cells

利用 COUNTIFS 函數取得了6種情況的人數後，接著就可利用這6個數值來計算列合計、欄合計和百分比。例如要計算生理女性的人數，可以在下圖儲存格J3裡輸入 =G3+H3+I3 或 =SUM(G3:I3)，完成後按下 Enter 會傳回數值250。關於利用 Excel 進行數學運算的方法，可以參考如何使用Excel進行數學計算【基礎篇】以及如何使用Excel進行數學計算【進階篇】。