Excel 函数通过先排除所有零,然后排除最小值和最大值来计算平均值

Excel 函数通过先排除所有零,然后排除最小值和最大值来计算平均值

我的 Excel 电子表格中有大约 300 个值(其中许多为零),我想通过排除零、最大值和零后的最小值来计算平均值。什么是合适的公式?

提前致谢。

编辑:我迄今为止尝试过的

假设我的值在 A1:A300 我执行以下操作:

=AVERAGEIFS(A1:A100, A1:A100, "<>0", A1:A100, "<>SMALL(A1:A100, COUNTIF(A1: A100,0)+1)", A1:A100, "<>LARGE(A1: A100, COUNTIF(A1: A100))")

它成功地丢弃了零,但没有丢弃最大值或最小值。

答案1

我假设结果应该打印到一个单元格中,而不使用任何其他单元格来打印中间结果。这可能只适用于数组公式、VBA 代码或数据库查询(答案维姆布泰克)。

数组公式

数组公式指南和示例创建数组公式了解详情。

一个解决方案

在我的例子中,要平均的值位于网格单元格 A1 到 A11 中。

这个公式解决了这个问题:

=AVERAGE(IF((A1:A11=MAX(A1:A11))+(A1:A11=MIN(IF($A$1:$A$11=0,"",$A$1:$A$11)))+(A1:A11=0),"",A1:A11))

插入后,您需要输入++ CTRL(不只是,参见SHIFTENTERENTER创建数组公式)。

该解决方案背后的想法

函数AVERAGE(除 then 之外AVERAGEA)计算所有包含数字的单元格的平均值。包含字符串或布尔/逻辑的单元格将被忽略。我们要做的是:将这些单元格设置为“”(空字符串),等于 0、等于最小单元格值(不包括 0)或等于最大单元格值。基本上,我们使用这个公式:

=AVERAGE(IF(_TODO_,"",A1:A11))

现在我们需要替换去做通过一些内容(上述三种情况)测试这三种情况:

A1:A11=MAX(A1:A11)
A1:A11=MIN(IF($A$1:$A$11=0,"",$A$1:$A$11))
A1:A11=0

第二个测试有点棘手:我们想从计算最小值中排除“0”(零)。该MIN函数忽略所有非数字值(例如空字符串“”)。将IF($A$1:$A$11=0,"",$A$1:$A$11)所有单元格设置为等于零的“”。所有剩余单元格保留其原始值。

现在我们把这三个测试结合起来。方法是将这三个测试放在括号中,并用 组合起来+,这代表一个逻辑OR

(A1:A11=MAX(A1:A11))+(A1:A11=MIN(IF($A$1:$A$11=0,"",$A$1:$A$11)))+(A1:A11=0)

现在,将此组合测试填入顶部的公式中,替换_TODO_

=AVERAGE(IF((A1:A11=MAX(A1:A11))+(A1:A11=MIN(IF($A$1:$A$11=0,"",$A$1:$A$11)))+(A1:A11=0),"",A1:A11))

另一种解决方案

基于用户3396592的方法也可以写成:

=AVERAGEIFS(A1:A11,A1:A11,"<>0",A1:A11,"<>"&MAX(A1:A11), A1:A11,"<>"&MIN(IF($A$1:$A$11=0,"",$A$1:$A$11)))

答案2

希望以下公式能够满足您的要求,

=QUERY(A:A,"选择平均值(A),其中 A > 0")

举个例子,如果 300 行中有 100 行是零,这将跳过这 100 行并计算剩余 200 行的平均值。

相关内容