问Excel按优先规则集分组

EN

您可以将“规则集”转换为属性(IsCool、IsNerdy、HasChild等)的所有可能组合，方法是将“忽略”处理为0(零)或1(单位)。

因此，问题规则集中的第一个规则将被两个规则所取代。

IsCool ¦ IsNerdy ¦ HasChild¦ Group 1 ¦ 1 ¦ 0 ¦ A 1 ¦ 1 ¦ 1 ¦ A

虽然在这三个属性中只有8种可能性，但这种方法可能导致8条以上的规则。例如，在问题的规则集中，当规则集中的“忽略”以这种方式扩展时，具有(1,1,1)给出的数据元组(IsCool，IsNerdy，HasChild)的人将匹配A组和B组。为了消除这种模糊，还需要应用优先级:A组的匹配优先级高于B，因此查找表将(1,1,1，1，A)作为一行，但不包括(1,1,1，B)。

有了更大的规则集，涉及更多属性，从规则表构建所需的VLOOKUP表的任务就不会没有问题，特别是如果需要一种非手动方法，并且只需要使用Excel，而不是将Excel与VBA结合使用。

替代方法不涉及VBA，它将规则集作为数据处理如下。

将上述表示法正式化后，涉及n个属性的规则可以表示为

(r[1],r[2],...,r[n],G)

其中r[i] (i=1,...,n)可以取0、1或"ignore"的值，而G代表组(在问题的例子中是A、B、C或D中的一个)。

数据的实例可以类似地表示为

(d[1],d[2],...,d[n])

其中d[i] (i=1,...,n)取0或1的值(而不是"ignore")

如果下列情况下，则匹配规则：

 r[i] = "ignore" OR "d[i] = r[i]" for each i from 1 to n

有一种很明显的方法在Excel中实现这一点

=AND(OR(r[1]="ignore",d[1]=r[1]),OR(r[2]="ignore",d[2]=r[2]),...,OR(r[n]="ignore",d[n]=r[n]))

当然，这里使用相关的单元格引用来代替上面所示的d[i]和r[i]占位符，并且在AND中嵌套适当数量的OR来替换...速记。

上面的伪公式的值为TRUE或FALSE，前者表示数据实例与规则匹配，后者表示不匹配。

然而，这并不是故事的结束，因为这些规则仍然需要按优先级顺序应用。

因此，进一步扩展表示法，假设规则按优先级顺序列出(规则1比规则2具有更高的优先级，规则3具有更高的优先级，等等)

如果单元格Ck保存了将k‘’th规则应用于数据实例的结果，那么修改上面的伪公式，使Ck现在有了公式。

=IF(OR(C[1],...,C[k-1]),FALSE,AND(...))

确保k‘the规则只能在没有匹配之前(因此是更高优先级)规则的情况下才能匹配。(在这里，IF的第三部分是前面提到的AND公式。)

下面的屏幕抓取显示了问题示例的实际操作方法。

​

蓝色的细胞是公式。第二个表中的TRUE/FALSE值实现了上面讨论的伪公式。例如，单元格F13显示了将规则1应用于数据实例(0,0,0)的结果，并有以下公式

=AND(OR($C$5="Ignore",$C$5=$C13),OR($D$5="Ignore",$D$5=$D13),OR($E$5="Ignore",$E$5=$E13))

注:不，如果需要围绕这个公式，因为没有比规则1更高优先级的规则。

单元格I13的公式显示了将规则4应用于同一个数据实例的结果，并需要考虑规则1、2和3所赋予的更高优先级。

=IF(OR($F13:H13),FALSE,AND(OR($C$8="Ignore",$C$8=$C13),OR($D$8="Ignore",$D$8=$D13),OR($E$8="Ignore",$E$8=$E13)))

细胞G13和H13中的公式类似于I13 (左作为练习)的公式。

通过设计，每行最多可以有一个真值，如果规则集是正确的，则应该有一个这样的值。第二个表的最后一列中的公式对规则集进行了归纳，并简单地从第一个表的最后一列中选择了对应于哪个规则显示为真的相关值。

单元格J13中的公式是

=INDEX(F$5:F$8,SUMPRODUCT(1*(F13:I13),F$12:I$12))

范围F13:J13中的公式只是简单地将表的行复制到单元格F14:J20中。

您可以通过在数据中创建一个键(例如，Joe = "111“、Charliese = "101”等)来做到这一点，然后它只是一个针对您的规则集的vlookup，其中包含所有键的可能组合。