Excel 或 SPSS 中所有可能的列组合的交叉表

Question

为了进行研究，我在 Excel 中拥有以下数据集（为便于说明已简化）：

Article   | Boolean1    Boolean2    Boolean3    Boolean4|
--------------------------------------------------------|
1         | Yes         No          Yes         No      |
2         | No          Yes         No          Yes     |
3         | Yes         No          No          Yes     |
4         | Yes         Yes         No          Yes     |
5         | Yes         No          No          No      |

我想计算布尔值的每个可能组合的 Jaccard 指数，因此首先制作一个如下所示的大型交叉表工作表：

             | Boolean2 No   Boolean2 Yes | Boolean3 No   Boolean3 Yes |
-----------------------------------------------------------------------|
Boolean1 No  |          0               1 |           1              0 |
Boolean1 Yes |          3               1 |           3              1 |
-----------------------------------------------------------------------|
Boolean4 No  |          2               0 |           1              3 |
Boolean4 Yes |          1               2 |           1              0 |

但是，我想要这样一个表中的所有组合，所以在这种情况下是 boolean1-boolean2、boolean1-boolean3、boolean1-boolean4、boolean2-boolean3、boolean2-boolean4 和 boolean3-boolean4（1-1、2-2 等。我可以自己过滤掉）。

现在，可以通过在 SPSS 中使用“交叉表”来对所有组合进行操作，或者在 Excel 中为每种可能的组合制作一个数据透视表；但是，由于我有 144 篇文章和 29 个变量（全部为布尔值），因此将其复制到单独的工作表中会非常耗时。由于数据透视表会“堆叠”变量，因此将所有变量放在数据透视表中的相同行/列中也是没有意义的。最接近我想要的答案是这个答案；然而，它在 Excel 2016 上对我来说不起作用。

有没有什么方法可以更有效地创建包含所有可能交叉表的巨型表格，而不是手动复制到 Excel 中的新工作表或现有工作表，或者在 SPSS 等其他工具中？

Answer 1

我会用 来做这件事COUNTIFS。

首先，为表格标题行和列创建标题，这相当简单，特别是如果变量按示例编号（Boolean1、Boolean2、...），因为您只使用自动填充。否则，您必须进行一些字符串操作/提取才能获得漂亮的表格：

             |   Boolean2        Boolean2 |    Boolean3       Boolean3 |
             |         No             Yes |         No             Yes |
-----------------------------------------------------------------------|
Boolean1 No  |          X                 |                            |
Boolean1 Yes |                            |                            |
-----------------------------------------------------------------------|
Boolean4 No  |                            |                            |
Boolean4 Yes |                            |                            |

您还必须将数据集定义为带有标题行的 Excel 表（Ctrl-L）（我使用 Table1 作为默认名称）。

在 X（假设是单元格 C3）中输入公式：
=COUNTIFS(INDIRECT("Table1["& C$1 &"]");C$2;(INDIRECT("Table1["& $A3 &"]"));$B3)
当然，您可以自动填充整个区域。

指Table1[Boolean2]的是数据集中的该列（INDIRECT只是避免了您必须对列引用进行硬编码，并且可以使用单元格中的值C1）。因此，总体公式计算Table1[Boolean2]为“否”和Table1[Boolean1]也为“否”的实例（使用中的两个条件COUNTIFS）。

输出将完全对称，因此根据您想要计算 Jaccard 指数的方式，您只需取对角线下方的输出（丢弃重复的 (N,M) 和 (M,N) 以及平凡的 (N,N)）。如果您想要立即获得干净的输出，当然可以添加一个附加项，IF以便仅当行中的变量索引大于列中的变量索引时才计算数字。您可能也可以在相同的公式中执行此操作，但为了可见性，我只需添加一个带有变量索引号（从 1 到 29）的额外行和列，然后对其进行测试。