☰

如何在一个 countif 公式中使用多个数组

tag-icon • tag-icon microsoft-excel worksheet-function
如何在一个 countif 公式中使用多个数组

我需要在所有条件都满足的情况下找到表格中单元格的数量。因此我写了以下公式：

=SUM(COUNTIFS(Master_tbl[Erfassungsdatum],">=01.01.2017",Master_tbl[Erfassungsdatum],"<=31.01.2017",Master_tbl[Auftragsart],{"YAEL","YAFD","YALP","YAPL"},Master_tbl[IH-Leistungsart],{"T01","T02","T03","T04","T35"},Master_tbl[P/AP],"AP"))

但结果比实际要小。当我只使用一组数组时，结果是正确的，但是当我使用两组数组时，结果不正确。

有人能帮我找出我的错误吗？

答案1

您最多只能创建 2 个数组，并且其中一个数组需要用;和 分隔,。这样，一个数组就是垂直数组，另一个数组就是水平数组。

=SUM(COUNTIFS(Master_tbl[Erfassungsdatum],">=01.01.2017",Master_tbl[Erfassungsdatum],"<=31.01.2017",Master_tbl[Auftragsart],{"YAEL","YAFD","YALP","YAPL"},Master_tbl[IH-Leistungsart],{"T01";"T02";"T03";"T04";"T35"},Master_tbl[P/AP],"AP"))

答案2

公式中使用的多个数组基本上是通过对齐各个数组并独立使用每个集合来评估公式（即 YAEL+T01、YAFD+T02）。这不是您想要做的。

我确信还有其他方法可以实现您想要做的事情，但我首先想到的是使用 Sumproduct。我不确定您使用 Excel 多久了，但长期以来，Sumproduct 是执行多条件 Countif 的最佳方法。采用上述公式并进行转换，我们得到：

=SUMPRODUCT(--(Master_tbl[Erfassungsdatum]>="01.01.2017"),--(Master_tbl[Erfassungsdatum]<="31.01.2017"),--(ISNUMBER(MATCH(Master_tbl[Auftragsart],{"YAEL","YAFD","YALP","YAPL"},0))),--(ISNUMBER(MATCH(Master_tbl[IH-Leistungsart],{"T01","T02","T03","T04","T35"},0))),--(Master_tbl[P/AP]="AP"))

ISNUMBER(MATCH... 本质上将多维数组（如果您正在执行列 = {数组} 的简单比较）转换为单维，然后可以将其与其他条件比较相结合。

我不完全确定您的 Erfassungsdatum 列的格式是什么，因此这些列的比较假设为字符串值。如果是日期，您需要进行相应调整。

答案3

通过使用纯布尔逻辑，可以轻松控制与 sumproduct 解决方案相同的逻辑。您有 5 个条件。数组 = 条件给出一个 true/False 数组。只有 True * True = True ，每个项目需要 5 个 True 才能获得 True。通过乘以条件数组，true 变为 1，false 变为 0

=Master_tbl[Auftragsart]={"YAEL","YAFD","YALP","YAPL"}_
*Master_tbl[IH-Leistungsart]={"T01","T02","T03","T04","T35"}_
*Master_tbl[P/AP]="AP")_
*(Master_tbl[Erfassungsdatum]>=LowVal)_
*(Master_tbl[Erfassungsdatum]<=HighVal)

仅当所有条件对某项都成立时，结果条件数组才成立（1）。
将其放入 sum 或 sumproduct 中，即可获得所有条件都成立的所有实例的计数。此方法易于控制，只需将条件数组与表中的一列值相乘即可，或者您可以使用 If(条件表 = 1, True, False) 重新建立 1 的 True，并使用此公式突出显示您的表格。

Rolf

相关内容