我有一个包含学生及其所修课程的大型数据集。每个学生都修了 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()就会更容易理解一些：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)
这是计算行数（学生）的标准方法两个都列包含1（即该学生参加了两个课程）。
封装IF()功能只是为了确保对角线单元格是空白的。