我有一行值,想求出前 3 个值的平均值(不包括空白单元格)。示例:
ABCDEFG 12 空白 8 空白 7 9 4
前 3 个非空白单元格的平均值应为 9,但我找不到可以帮我计算的公式。有人可以帮忙吗?
答案1
在A2进入:
=IF(A1="",0,1)
以及B2进入:
=IF(OR(B1="",SUM($A$2:A2)=3),0,1)
并复制。然后在另一个单元格中输入:
=SUMPRODUCT((1:1)*(2:2))/3
答案2
答案3
解决方案 1
强力破解:你想要的平均值可以通过以下方式计算:
=如果(计数($A1:$C1)=3,平均值($A1:$C1), 如果(计数($A1:$D1)=3,平均值($A1:$D1), 如果(计数($A1:$E1)=3,平均值($A1:$E1), 如果(计数($A1:$F1)=3,平均值($A1:$F1), IF(COUNT($A1:$G1)=3, AVERAGE($A1:$G1), "哎呀"))))) (单行版本:) =IF(COUNT($A1:$C1)=3, AVERAGE($A1:$C1), IF(COUNT($A1:$D1)=3, AVERAGE($A1:$D1), IF(COUNT($A1:$E1)=3, AVERAGE($A1:$E1), IF(COUNT($A1:$F1)=3, AVERAGE($A1:$F1), IF(COUNT($A1:$G1)=3, AVERAGE($A1:$G1),"oops")))))
它会查找包含三个数字的行的最短初始子集,然后计算平均值。这不仅在列数较多时会变得很麻烦,而且实际上可能会很快失败,因为(我相信)IF()
嵌套限制相当低。
解决方案 1.9
技巧。
我将其称为“1.9”,因为它尚未完成。表达式
SMALL(IF(ISNUMBER($A1:$G1),COLUMN($A1:$G1),""), {1,2,3})
(用于数组公式)查找第 1 行前三个数字的列号。
IF(ISNUMBER($A1:$G1),COLUMN($A1:$G1),"")
部分计算结果为
IF( {TRUE,FALSE,TRUE,FALSE,TRUE,TRUE,TRUE}, {1,2,3,4,5,6,7}, "" )
简化为
{ 1, "", 3, "", 5, 6, 7 }
并SMALL(…, {1,2,3})
产生上述结果{ 1, 3, 5 }
。这应该是一个简单的说法
=AVERAGE(INDEX($A7:$G7, 1, SMALL(IF(ISNUMBER($A7:$G7),COLUMN($A7:$G7),""),{1,2,3})))
或者
=AVERAGE(OFFSET($A7:$G7, 1, SMALL(IF(ISNUMBER($A7:$G7),COLUMN($A7:$G7),""),{1,2,3})))
但它们返回的值要么是A1
(12),要么是错误 (#DIV/0!
或#REF!
)。我不知道该如何完成它。