我的 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 行的平均值。