![比较多列(超过 2 列)](https://linux22.com/image/1356583/%E6%AF%94%E8%BE%83%E5%A4%9A%E5%88%97%EF%BC%88%E8%B6%85%E8%BF%87%202%20%E5%88%97%EF%BC%89.png)
好的,假设我有一张包含 N 列数据的图表。我想要做的是将每一列与其他每一列进行比较,并计算两列中相等的值的数量。我知道我可以轻松比较两列,只需添加第三列并将两列内容匹配的值设置为 1,然后计算第三列中的 1 即可。(有很多人发帖问这个问题,但这比这更复杂。)我需要比较一堆列,但我不想为每对可能的现有数据列创建一个新的比较列。(N 可能会变得非常大,这种方法基本上需要大约 N 2 /2* 个额外的列 - 不可接受。)似乎应该可以在一个公式中计算 A 列和 B 列之间的所有匹配项,然后只需复制该公式即可比较 A:C、A:D、B:C、B:D、C:D 等,无论有多少列。这样只需要 N 2 /2细胞,而不是那么多整列,这样效率更高。有什么帮助吗?
*(技术上是 N*(N-1)/2,但足够接近。)
答案1
这是一个您可以扩展的 3 列示例。
首先,您需要命名所有源列。在列上放置列标题,用作范围名称。如果(例如)您的列数为数百但少于 1000,则放置标签,如 Col001、Col002、Col003 等。输入标签后,按 Ctrl+A 组合键,然后按 Alt+I(插入)、N(名称)、C(创建);选中“Top Row”(上一行)并单击“OK”(确定)。
现在您需要一个矩阵区域来进行计算。对于 n 列,您将需要一个 nxn 块,以及一行和一列用于标签:
在第一个单元格中,键入以下公式,然后按 Shift+Ctrl+Enter 将其作为数组公式输入:
=SUM(如果(INDIRECT(B$1)=INDIRECT($A2),1,0))
(这意味着,对于范围“Col01”中每个等于范围“Col01”中相应单元格的单元格,总和为 1;如果不相等,则总和为 0。)
现在只需将该公式复制到矩阵列的其余部分(不包括您从中复制到粘贴选择的单元格,否则您将收到“您无法更改数组的一部分”的消息)。填满整个列后,将该列(仅计算单元格)复制到其他列以填充矩阵。
沿对角线的单元格将仅具有源列中的总行数(因为,例如,“Col01”始终完全等于“Col01”。沿对角线镜像的单元格将具有相同的值,因为(例如)“Col02”与“Col01”具有与“Col01”与“Col02”相同数量的相同值。它们是多余的,对角线不是特别有用,因此您可以清除它们以使其更具可读性。
添加了更多细节,以回应评论......
在下图中,A7:C16(蓝色单元格)包含源数据。第 6 行中的标签将作为范围名称应用,方法是选择 A6:C16 并选择 Alt+I(插入)、N(名称)、C(创建),然后在“从选择中创建名称”对话框中选中 Top ON 并单击确定。(现在,例如,=SUM(Col01) 与 =SUM(A7:A16) 相同)。
范围 B2:D4 是计数矩阵。选择 B2,键入或粘贴公式,然后使用 Ctrl+Shift+Enter 将其作为数组输入。将 B2 复制到 B3:B4。然后将 B2:B4 复制到 C2:D4(这样有点麻烦,因为它是一个数组公式)。绿色单元格代表您想要实现的计数。对角线始终是最大值,因为(例如)Col01 始终等于 Col01,单元格对单元格。对角线另一侧的其他白色单元格是多余的,是绿色单元格的镜像。现在您可以根据需要对其进行扩展。
INDIRECT 函数的意思是,使用引用单元格中的文本作为范围名称。因此(例如)=SUM(INDIRECT(B$1)) 的含义与 =SUM(Col01) 相同。$ 符号是绝对引用,因此您可以将公式复制并粘贴到整个矩阵中,而不必编辑每一个。B$1 表示,始终使用公式中的第 1 行,即使您将其向下复制也是如此。$A2 表示,始终使用 A 列,即使您将其复制到另一列也是如此。
还有更多细节... :^)
请确保:
- 范围名称应用于平面表中的源数据(即您想要相互比较的列的原始表)
- 数组公式应用于矩阵中将计算匹配计数的每个单元格
我的猜测是:范围名称与平面表中的源数据不匹配。在示例中,“Col01”应“引用”=Sheet1!$A7$16,值应类似于 {“5”;“7”;“5”;“9”;...
一旦正确应用范围名称,矩阵单元格中的数组公式应按如下方式应用:
现在...由于排列倍增速度很快(3 列 --> 3 次比较,4 --> 6、5 --> 10、6 --> 15 等),INDIRECT 确实很方便 - 您可以在 B2 中键入一次公式,然后将其粘贴到所有其他单元格。(如果您收到“无法更改数组的一部分”,请回顾之前的答案内容,您就会明白。)
如果没有间接语句,B2 可能是:
{=SUM(如果(Col01=Col01,1,0))}
但这意味着,对于矩阵中的每个其他单元格,您必须手动将“Col01”更改为“Col02”等等,非常繁琐......
如果没有范围名称,B2 可能是:
{=SUM(如果(A7:A16=A7:A16,1,0))}
但编辑会变得越来越繁琐......