连续平均特定值 [英] averaging specific values in a row
问题描述
我对excel经验不足,所以这可能是一个基本问题,但我似乎找不到任何答案.
I'm fairly inexperienced with excel so this might be a bit of a basic question but I can't seem to find the answer anywhere.
我有几行,一个是索引"行,其他则包含值.我想获取基于索引的每一行的平均值(每个索引的平均值).
I have several rows, one is an "index" row while the others contain values. I want to get the averages for each row based on the indexes (average for each index).
我一直在查看AVERAGE函数中的IF函数,但似乎无法找出正确的语法,所以我想知道是否有可能?
I've been looking at the IF function inside an AVERAGE function but I can't seem to figure out the proper syntax so I'm wondering if it is even possible?
我试图对单个平均值这样做: = AVERAGE(IF(A3:J3 = 0; A5:M5; 0))
.
I attempted to do it like so for a single average: =AVERAGE(IF(A3:J3=0;A5:M5;0))
.
马上我可以看到几个问题.首先,对于每个正条件,我要说的是使用整个行(我不知道如何将一行映射到另一行).其次,对于每个负条件,我都提供一个0而不是忽略该值.
Straight away I can see a couple of problems. First for each positive conditional I am saying to use the entire row (I don't know how to map one row to another) as a result. Secondly for each negative conditional I provide a 0 rather than ignoring that value.
从我站着的地方看来,我需要做的事似乎不可能...我正确吗?
From where I'm standing it doesn't seem like what I need is possible to do...am I correct?
推荐答案
您可以结合使用 SUMIF
和 COUNTIF
函数来获得平均值:
You can combine SUMIF
and COUNTIF
functions to get the average:
=SUMIF(A1:K1,1,A2:K2) / COUNTIF(A1:K1, 1)
将给出您想要的索引1(在第1行,A到K列)和第2行中的值的平均值.
would give the average you want for index 1 (in row 1, columns A through K), values in row 2.
这篇关于连续平均特定值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!