我在 EXCEL 中有一个小表格,用于COUNTIFS
计算数据集中符合四个条件的实例数,用户可以从表格前四个单元格中的数据验证列表中选择这些实例。COUNTIFS
第五列中的活动会显示数字。这没有问题。
我现在想在 4 个经过验证的列表中的每一个中添加“ANY”,以便用户可以通过选择 ANY 选项有效地“忽略”一个或多个条件。因此,极端的例子是四个单元格中的每一个单元格都是 ANY,这将返回数据集中的全部行数,并计算每个条目(这不是很有用,只是为了说明我的目的!)
我正在使用一些辅助单元格(L45:T48)并使用以下数组公式来尝试实现此目的
{=SUM(COUNTIFS(range1, L45:T45, range2, L46:T46, range3, L47:T47, range4, L48:T48))}
辅助单元格仅包含一个 IF 语句:
=IF(C57<>"ANY",C57,{"Scheduled","Discretionary"})
这样,如果用户选择了一个特定的条件,单元格就只会镜像该条件,但如果用户选择“ANY”,那么所有条件的数组都会放入辅助单元格区域,以供数组公式循环。
毫不奇怪,当我在这里发布时,它还没有完全发挥作用!
如果选择了特定标准,它仍然有效,但如果选择了任何标准,那么它似乎只是选择数组中的第一个元素,并且会给出与专门选择该标准相同的返回值。
我希望我错过了一些显而易见的东西,为什么公式似乎没有像预期的那样循环遍历数组元素。当我选择一个条件时,在返回值出现之前有一个微小的延迟,所以它似乎就好像它正在遍历辅助单元格范围中的每个单元格,但没有拾取数据?
非常感谢你的回复,欧文。抱歉,我使用的是支持动态数组的 Excel 365,但 Excel 文件是要分发给一个组,其中一些组使用的是早期版本。如果编辑 OP 不是“回复”的最佳方式,也请原谅。(显然这里是新用户!)
我想无论如何都要试一试,看看至少使用动态数组解决方案是否可行,我将有问题的公式精简到前两个条件范围。奇怪的是,您的建议让我更进一步,但并没有完全成功。因此,现在只需处理两个条件,使用 # 而不是范围(例如 L45:T45),允许一个“ANY”按预期工作。但是,当两者都被选为 ANY 时,它会返回到仅选择每个范围中的第一个条件。目前有 73 行。当条件一(用户名)被选为 ANY 时,当我选择 Scheduled 时,我得到 45,当我选择 Discreteary 时,我得到 28。两者都正确。但是,当我为第二个条件也选择 ANY 时,我得到的不是 73,而是 8 - 这是第一个条件数组中列出的第一个用户进行的预定条目数。
您的解决方案在 SUM 函数中使用了 SUMIF,而我需要 COUNTIF。我假设语法应该转换为两者,但这个假设有效吗?
也许有一种方法可以使用 AND 逻辑语句将四个 COUNTIF 拼接在一起,因为它们似乎在同一个括号内不能很好地发挥作用?!但我也无法做到这一点。
答案1
如果你拥有支持动态数组的最新版本的 Excel,那么它将起作用:
=SUM(SUMIF(F3:F7,C3#,G3:G7))
这里最重要的部分是 C3#,它将收集全部单元格 C3 中公式返回的项目数(如果有人在单元格 I3 中选择“ANY”,则返回两个项目)。
这是返回所选数组的公式(为了可见性):
=IF(I3<>"ANY",I3,{"Scheduled","Discretionary"})
当然,您可以将单元格 C3 中的公式嵌套在 J3 中的公式中:
无论哪种方式,这种方法都应该可以帮助您达到所需的目标。
编辑:
要扩展到多个选择条件,您可以在数据表中添加一列,如果满足选择条件,则返回 TRUE,否则返回 FALSE。然后您只需计算该列为 TRUE 的行数。如果您愿意,您可以返回一些有意义的文本(如“已选择”和“未选择”)。
为了证明这适用于 3 个条件,我使用以下公式创建了一个列:
=AND(OR(E3=$J$3,$J$3="ANY"),OR(F3=$K$3,$K$3="ANY"),OR(G3=$L$3,$L$3="ANY"))
很容易看出,对于每个选择标准,只需在 AND 中添加一个新的 OR 语句。
然后,选定行的数量仅为:
=COUNTIF(H3:H7,TRUE)