如何将自定义格式规则中的这些数组公式和公式合并到自定义格式规则中的1个公式中？ [英] How to combine these arrayformula and formula in custom formatting rule in just 1 formula in custom formatting rule?

问题描述

我使用数组公式查找计划中的冲突。在简化的形式中，它看起来如下所示：

=ARRAYFORMULA({"Teacher Conflicts";IF(COUNTIFS($A4:$A;$A4:$A;B4:B;B4:B)>1;"Conflict";)})

其中A列包含时间段，B列包含教师姓名，如

group 1                 Teacher Conflicts
10:00-10:45  Smith      Conflict
11:00-11:45  Black
12:00-12:45  Anderson
...
group 2
10:00-10:45  Smith      Conflict
11:00-11:45  White
12:00-12:45  Hardy
...

现在，为了更方便使用，我还使用了条件格式(整个解决方案是suggestedby MattKing)和公式=len(C4)来突出显示冲突的单元格并隐藏C列。

可以看到，第二个公式非常简单，所以看起来我可以将整个公式转换为自定义格式，如下所示：

=len(IF(COUNTIFS($A4:$A;$A4:$A;B4:B;B4:B)>1;"Conflict";))

(可以简化为=COUNTIFS($A4:$A;$A4:$A;B4:B;B4:B)>1，但我只是复制粘贴单元格位以使其更清楚；这里不是重点)

问题是，它的工作方式不一样！它高亮显示不同的单元格。我猜原因是，在arrayformula和自定义格式公式中，第一个和第二个$A4:$A的含义不同，但我不确定如何更正它们，以及自定义格式公式的确切读取方式是如何不同的。我希望预期的行为足够清楚，所以问题是，我如何修改自定义格式公式才能达到预期效果？

PS这里有一个MVCE。这个公式有点复杂，当同一个人应该同时担任导师和教师时，就会产生交叉冲突；与简单的冲突相比，这实际上给了我们一些关于问题根源的洞察力。如果(在"实验"选项卡中)尝试使用(COUNTIFS($A3:$A;$A3:$A;B3:B;B3:B) + COUNTIFS($A3:$A;$A3:$A;C3:C;B3:B))>1替身自定义格式公式=len(D3)，您将注意到不再突出显示两个冲突。

PPS一些更多的实验给了我一个假设：看起来自定义格式化公式内部countifs中的范围实际上不是B4:B，而是Bn:B，其中n是格式化单元格的行号。不确定如何检查此问题，以及是否有可能修复此问题。还没有。

推荐答案

建议

您可以尝试&；测试下面的自定义公式，您可以将其添加到Conditional format rules设置中。

=countifs($A$4:$A,A4,$B$4:$B,B4) > 1

示例

这篇关于如何将自定义格式规则中的这些数组公式和公式合并到自定义格式规则中的1个公式中？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！