☰

取特定条件下 90% 的小持续时间的平均值

tag-icon • tag-icon microsoft-excel worksheet-function
取特定条件下 90% 的小持续时间的平均值

我想从以下条件下的一列中取 90% 最小数字的平均值

  • Group1 = “主要地铁”和“VIP地铁”
  • 组 2 = “访问”
  • 严重性=“中”
  • 包括/排除 = “包括”

结果将为 = 51:22:00

我使用了单独的公式和工作，但我不知道如何将它们结合起来

第一个公式根据我的条件计算所有人的平均值

=AVERAGE(IF((B:B="ACCESS")*(C:C="Medium")*(E:E="Include")*((A:A="Major Metro")+(A:A="VIP Metro")),D:D))

第二个公式取最小数 90% 的平均值

=SUMIF(A:A,"<"&PERCENTILE(D:D,0.9))/COUNTIF(D:D,"<"&PERCENTILE(D:D,0.9))

在此处输入图片描述

如何将这两个公式结合起来或创建新的更好的公式？

答案1

您的第一个公式不会按照您预期的方式发挥作用。

您的IF(...)语句将返回一个混合了 D 值和 FALSE 值的数组，一旦嵌入到 AVERAGE() 函数中就会出错。

如果您有 Excel O365，我会考虑使用 FILTER() 来满足您的各种标准:(alt returns为清楚起见，此处显示如下）：

=average( FILTER(D:D,
(B:B="ACCESS")*
(C:C="Medium")*
(E:E="Include")*
((A:A="Major Metro")+(A:A="VIP Metro"))
)

然后，您只需添加附加条件（就像您已经完成其他条件的方式）即可获得 90 百分位数：

=AVERAGE( FILTER(D:D,
(B:B="ACCESS")*
(C:C="Medium")*
(E:E="Include")*
((A:A="Major Metro")+(A:A="VIP Metro"))*
(D:D <= PERCENTILE.INC( D:D , 90% ))
)

如果没有O365，和做不是拥有 Excel 2021您将无法使用 AVERAGEIF 实现此目的，因为您对 A 列有一个 OR 条件，而 AVERAGEIF 只需要 AND 条件。

因此，如果没有 Excel O365，您将不得不使用 SUMPRODUCT 方法。在此方法中，您将使用两个 SUMPRODUCTS... 一个用于对符合条件的值数组求和，另一个用于对条件的逻辑结果数组求和。第一个数组是满足条件的值，第二个数组是满足条件的每一行的 1，不满足条件的每一行的 0，这将产生计数。有时您很可能会除以零，因此建议使用 IFERROR 或某种零处理。

分子是：

=SUPMPRODUCT( D:D *
(B:B="ACCESS")*
(C:C="Medium")*
(E:E="Include")*
((A:A="Major Metro") + (A:A="VIP Metro"))*
(D:D <= PERCENTILE.INC( D:D , 90% ))
)

D:D *并且，即使没有开头的 ，主宰者也是相同的：

=SUPMPRODUCT(
(B:B="ACCESS")*
(C:C="Medium")*
(E:E="Include")*
((A:A="Major Metro") + (A:A="VIP Metro"))*
(D:D <= PERCENTILE.INC( D:D , 90% ))
)

你最终会得到这个怪物般的公式：

=SUPMPRODUCT( D:D *
(B:B="ACCESS")*
(C:C="Medium")*
(E:E="Include")*
((A:A="Major Metro") + (A:A="VIP Metro"))*
(D:D <= PERCENTILE.INC( D:D , 90% ))
) /
SUPMPRODUCT(
(B:B="ACCESS")*
(C:C="Medium")*
(E:E="Include")*
((A:A="Major Metro") + (A:A="VIP Metro"))*
(D:D <= PERCENTILE.INC( D:D , 90% ))
)

如果你做有 Excel 2021，但不是Excel O365

然后你将使用 SUMPPRODUCT() 方法。但你可以戏剧性地通过将公式封装在 LET() 语句中来简化公式。一旦您学会了使用 LET() 语句的方法，并养成了使用 LET() 语句alt enter使 LET() 更具可读性的习惯，您就再也不会回头了。您无需重复两次条件，而是将它们作为 LET() 语句中的参数。这简化了公式，但非常重要的是，在这种情况下将计算次数减少了一半。每个参数都计算一次并保持持久性（因此可重复使用）之内LET() 的那个实例。整个 SUMPRODUCT() 公式变为：

=LET(
selectedRowArray,
(B:B="ACCESS")*
(C:C="Medium")*
(E:E="Include")*
((A:A="Major Metro") + (A:A="VIP Metro"))*
(D:D <= PERCENTILE.INC( D:D , 90% )),
countOfRows, SUM( selectedRowArray ),
IF ( countOfRows, SUMPRODUCT(D:D, selectedRowArray) / countOfRows, 0 )
)

这也说明了我将如何整合错误检查，以便在零行符合条件时进行捕获。我将除法放在 IF() 语句中，该语句仅在分母不为 0 时执行。任何大于或等于 1 的 countOfRows 值都将被TRUEIF() 解释为。

结束语

  1. 我使用它PERCENTILE.INC()只是因为它是的较新版本PERCENTILE()，并且保持了兼容性。

  2. 使用整列A:A是您可以随意使用的方法，但对于会“持续存在”并长期使用的工作表来说，这绝对不是最佳实践。它充满了复杂性……（a）扫描每列中的一百万行以上的性能损失；（b）意外内容最终出现在列中的某个位置并被默默地包含在结果中的风险。最佳实践是，如果您需要透明地容纳行插入的范围，则尽可能使用 Excel 表构造。（感谢 Jos）

相关内容