带通配符的 Excel 数组公式

Question

我有一个数组公式，它引用下拉单元格 ($AG$7) 来确定要评估哪些单元格。这很好用，但是，我需要在下拉列表中添加一个附加项目，即“全部”。

选择此选项后，我希望数组公式使用“*”从数组返回所有实例，但我无法让它工作。

这是我当前使用的公式；

={SUM(IF((tblSkillsMatrix[Role]=[@Role])*(INDIRECT("tblSkillsMatrix["&V$2&"]")=$AG$7),1,0))}

我尝试过使用

={SUM(IF((tblSkillsMatrix[Role]=[@Role])*(INDIRECT("tblSkillsMatrix["&V$2&"]")="*"&$AG$7),1,0))}

和

={SUM(IF((tblSkillsMatrix[Role]=[@Role])*(INDIRECT("tblSkillsMatrix["&V$2&"]")="*"&$AG$7&"*"),1,0))}

但是这些都不起作用。

有人有什么想法吗？

谢谢

Answer 1

显式 = 比较不能使用通配符。 COUTIFS 和 SUMIFS 可以。据我所知，您只想计数(条件和 1 和 0)。

问题是，COUTIFS和SUMIFS不会处理INDIRECT范围。但是，INDIRECT 可以而且应该(因为其不稳定的行为)经常被 INDEX- MATCH 取代。

所以:

=COUNTIFS(tblSkillsMatrix[role],[@role],INDEX(tblSkillsMatrix,,MATCH($V$2,tblSkillsMatrix[#Headers],0)),"*"&$AG$7)

如果 $AG$7 为空，则其计数独立于 $V$2 中命名的列。

顺便说一句:在表(ListObject)中，不需要将其作为数组公式输入。

这并不是 100% 替换您的公式，因为如果 $V$2 为空并且没有给出表格列标题，则该公式不起作用。然后，您的公式将查看所有列，但使用 COUNTIFS 是不可能的，其中每个附加范围必须具有与 criteria_range1 参数相同的列数。因此，如果 $V$2 也可以为空，那么这将不起作用。

如果是这样，那么你可以使用

{=SUM((tblSkillsMatrix[role]=[@role])*(LEFT(INDIRECT("tblSkillsMatrix["&$V$2&"]"),LEN($AG$7))=$AG$7))}

优点:$V$2和$AG$7都可以为空。

缺点:INDIRECT 的行为不稳定，因此即使在 ListObject 表中，此公式也必须是数组公式。必须使用 Ctrl+Shift+Enter 进行确认。