Excel 计数标准多列范围。无序比较测试

Question

这是我的第一个问题，所以如果有问题请不要杀了我。我在这个网站上找到了很多解决方案，但这次没有。不幸的是我还不能发布图像。这并不容易，但我会尝试。

重点:

我的数据具有以下标题:

Decision_Id Opponent1 Opponent2 Opponent3 Suitor1 Suitor2 Suitor3 Suitor4

Decision_id 是唯一的整数标识符。其余的都是字符串。

每一行代表一个特定的司法判决。每个决策最多可以有 3 个反对者(辩护方)和最多 4 个诉求者(攻击方)。特定一方可以是一个决策中的诉求者，也可以是另一决策中的反对者。

我想要得到什么:

跨表，其中行标题和列标题都是我在表中遇到的不同方。 (没问题，完成。)其中每个单元格显示特定对手(由行标题定义)受到特定追求者(列标题)攻击的不同决策=>所有对角单元格都等于零(一方不能攻击自己)并且表格不对称。

我已经尝试过

应用于第一个单元格并展开:

=COUNTIFS("所有对手的固定范围:$B$2:$D$6","想要的对手值:$A2", "所有追求者的固定范围:$E$2:$H$6", "通缉追求者值(value):B$1")

我有一个错误。我发现标准范围必须具有相同的大小。好的，创建了虚拟空列 => 没有错误，但是，结果显然被低估了。我认为只有当对手和追求者的“数字”相同时，才有匹配。详细信息:对于每一行，excel 测试对手1 和追求者1 的相应值，然后测试对手2 和追求者2，然后对手3 和追求者3...这实际上解释了为什么范围必须具有相同的大小。

那么，我需要什么

对于每一行，要使 Excel 测试所有对手是否达到想要的对手值，测试所有追求者是否达到想要的追求者值。如果至少有一个对手和一个追求者对应，则进行匹配并计算此决定。(即使对手1和追求者3具有想要的值)

备注

我已经编写了一个 VBA 代码来完成这项工作，但它太慢了(整个表大约需要 5 个小时)，我希望对此类不同的表执行相同的操作和/或修改这个表。所以我对“纯Excel”、快速解决方案感兴趣。

非常感谢!

Answer 1

这里的困难部分是将多列范围分成单独的行 - 一种方法是在 COUNTIF 中使用 OFFSET，即这个公式

=SUMPRODUCT(COUNTIF(OFFSET($B$2:$D$6,ROW($B$2:$D$6)-ROW($B$2),0,1),$A2),COUNTIF(偏移量($E$2:$H$6,ROW($E$2:$H$6)-ROW($E$2),0,1),B$1))

假设所有追求者在任何一行上都不同，并且所有对手在任何一行上都不同(尽管如果情况并非如此，可以修改公式)。

您可以将范围扩展到您想要的任何大小 - 尽管每个部分的行数必须相同

...或者这是使用 MMULT 函数的另一种更晦涩的方法

=SUMPRODUCT(MMULT(($B$2:$D$6=$A2)+0,{1;1;1}),MMULT(($E$2:$H$6=B$1)+ 0,{1;1;1;1}))

{1;1;1} 和 {1;1;1;1} 代表每个部分中的列数，因此如果您有 6 和 8 列，则需要相应更改