我有大量學生及其所修課程的資料集。每個學生都選修了大約 80 門課程中的 12 到 18 門。使用 Excel (2013)，我想知道對於任何給定的一對課程，有多少學生同時參加了這兩門課程。我設想一個表格，其中行和列都包含 80 個班級，然後對於每個交叉點，我都會看到有多少學生選擇了該組合。
數據以 Excel 文件形式到達，每個班級每個學生一行：
Student Class
Smith E101
Jones E101
Parker E101
Brown E102
Green E102
Smith E201
Jones E202
Parker E201
Brown E202
Green E203
...
預期產出：
E101 E102 E201 E202 E203 ...
E101 0 2 1 0
E102 0 0 1 1
E201 2 0 0 0
E202 1 1 0 0
E203 0 1 0 0
...
（顯然我只需要上面的對角線一半，因為另一半鏡像它。）
我使用資料透視表將資料放入表中，其中學生為行，所有可能的班級為列，顯示學生參加給定班級的位置為 1。
E101 E102 E201 E202 E203 ...
Smith 1 1
Jones 1 1
Parker 1 1
Brown 1 1
Green 1 1
...
但隨後我陷入瞭如何在盡可能少的手動幹預下繼續實現我想要的輸出的問題。
誰能建議一種方法來實現我在 Excel 中需要的輸出？我已經進行了相當廣泛的搜索，但沒有找到任何東西。
或者我應該尋找其他軟體？
答案1
在 Excel 中，使用在資料透視表上執行的公式可以非常簡單地完成此操作。
兩張桌子這樣設置
輸入以下公式J2，然後按 ctrl-enter/copy-paste/fill-down&right/auto-fill 到表格的其餘儲存格：
=
IF(
J$1=$I2,
"",
COUNTIFS(
INDEX($A$1:$F$6,ROW($A$1:$F$6),MATCH(J$1,$A$1:$F$1,0)):INDEX($A$1:$F$6,1+ROWS($A$1:$F$6)-ROW($A$1:$F$6),MATCH(J$1,$A$1:$F$1,0)),
1,
INDEX($A$1:$F$6,ROW($A$1:$F$6),MATCH($I2,$A$1:$F$1,0)):INDEX($A$1:$F$6,1+ROWS($A$1:$F$6)-ROW($A$1:$F$6),MATCH($I2,$A$1:$F$1,0)),
1
)
)
解釋：
此函數的第一個參數COUNTIFS()是與輸出表的列標題相對應的資料透視表的動態產生的列。如果我們查看中間評估步驟（對於 cell L2），會更容易理解一點：
INDEX($A$1:$F$6,ROW($A$1:$F$6),MATCH(L$1,$A$1:$F$1,0)):INDEX($A$1:$F$6,1+ROWS($A$1:$F$6)-ROW($A$1:$F$6),MATCH(L$1,$A$1:$F$1,0))
→ INDEX($A$1:$F$6,1,MATCH("E201",$A$1:$F$1,0)):INDEX($A$1:$F$6,6,MATCH("E201",$A$1:$F$1,0))
→ INDEX($A$1:$F$6,1,4):INDEX($A$1:$F$6,6,4)
→$D$1:$D$6
（請注意，每個參數的第二個參數INDEX()分別只是資料透視表的完全動態開始行和結束行。）
對於函數的第三個參數也是如此COUNTIFS()，但是這次資料透視表的動態產生的列對應於排輸出表的標題。對於單元格，L2其計算結果為$B$1:$B$6。
因此COUNTIFS()中的函數L2變為
COUNTIFS($D$1:$D$6,1,$B$1:$B$6,1)
這是計算行數（學生）的標準方法，其中兩個都列包含 a 1（即該學生同時註冊了兩個班級）。
封裝IF()功能只是為了確保對角線單元格為空白。