☰

比较多列（超过 2 列）

tag-icon • tag-icon microsoft-excel worksheet-function
比较多列（超过 2 列）

好的，假设我有一张包含 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))}

但编辑会变得越来越繁琐......

相关内容