如何在Excel中基于多个条件对单元格求平均？

在Excel中，我们大多数人可能都熟悉COUNTIF和SUMIF函数，它们可以帮助我们根据条件对值进行计数或求和。但是，您是否曾经尝试过基于Excel中的一个或多个条件来计算平均值？

使用averageif函数基于一个条件对单元进行平均

假设我具有以下数据范围，A列是产品列表，B列是订单，现在，我将对产品为KTE的订单单元进行平均。

doc-avergae-with-criteria-1

请在您需要的单元格中输入以下公式： =AVERAGEIF(A2:A19,D2,B2:B19)（A2：A19 是包含条件的数据， B2：B19 指您想要平均的范围， D2 是您要根据其取平均值的标准，然后按输入获得结果的关键，请参见屏幕截图：

doc-avergae-with-criteria-2

使用Averageifs函数基于多个条件对单元进行平均

如果您要使用多个条件来计算平均值，则 平均值 功能可能会帮助您。

Averageifs语法如下：

AVERAGEIFS（平均范围，标准范围1，标准1，标准范围2，标准2…）

平均范围：是平均单元格范围；
Criteria_range1, 条件范围2，…是评估相关标准的范围；
准则1, 条件2，…是您所基于的条件。

以上面的数据为例，现在，我要平均KTE的订单和大于500的订单。请这样做：

在空白单元格中输入以下公式： =AVERAGEIFS(B2:B19,A2:A19,E1,B2:B19,">500")( A2：A19 是包含条件1的数据， B2：B19 指您要平均的范围， E1 和 > 500 是条件1和条件2），然后按输入获得所需结果的关键。看截图：

doc-avergae-with-criteria-3

请注意： 如果您有两个以上的条件，则只需要添加条件范围和条件，如下所示： = AVERAGEIFS（C2：C19，A2：A19，F1，B2：B19，F2，C2：C19，“ <500”）（A2：A19 和 F1 是第一个条件范围和条件， B2：B19 和 F2 是第二个条件范围和条件， C2：C19 和 <500 是第三标准范围和标准， C2：C19 指范围你想要取平均值），请参见屏幕截图：

doc-avergae-with-criteria-4

相关文章：

如何平均Excel中的前3个值？

在输入新数字时，如何平均一列的最后5个值？

如何在Excel中每5行或每列平均一次？

最佳办公效率工具

Kutools for Excel解决了您的大多数问题，并使您的生产率提高了80％

重用: 快速插入 复杂的公式，图表 以及您以前使用过的任何东西；加密单元带密码创建邮件列表并发送电子邮件...
超级公式栏（轻松编辑多行文本和公式）；阅读版式（轻松读取和编辑大量单元格）；粘贴到过滤范围...
合并单元格/行/列不会丢失数据；拆分单元格内容；合并重复的行/列...防止细胞重复；比较范围...
选择重复或唯一行; 选择空白行（所有单元格都是空的）；超级查找和模糊查找在许多工作簿中；随机选择...
确切的副本多个单元格，无需更改公式参考；自动创建参考到多张纸；插入项目符号，复选框等...
提取文字，添加文本，按位置删除，删除空间; 创建和打印分页小计；在单元格内容和注释之间转换...
超级滤镜（将过滤方案保存并应用于其他工作表）；高级排序按月/周/日，频率及更多；特殊过滤器用粗体，斜体...
结合工作簿和工作表; 根据关键列合并表；将数据分割成多个工作表; 批量转换xls，xlsx和PDF...
超过300种强大功能。支持Office / Excel 2007-2019和365。支持所有语言。在您的企业或组织中轻松部署。完整功能30天免费试用。 60天退款保证。

阅读更多免费下载... 采购...

Office选项卡为Office带来了选项卡式界面，使您的工作更加轻松

在Word，Excel，PowerPoint中启用选项卡式编辑和阅读，发布者，Access，Visio和Project。
在同一窗口的新选项卡中而不是在新窗口中打开并创建多个文档。
每天将您的工作效率提高50％，并减少数百次鼠标单击！

阅读更多免费下载... 采购...

10 Comments
Login

Sort by Newest
- Best
- Popular
- Newest
- Oldest

You are guest

or post as a guest, but your post won't be published automatically.

Loading comment... The comment will be refreshed after 00:00.

To post as a guest, your comment is unpublished.
Jenin J · 1 years ago
To get an average of data in a column with multiple criteria the following formulae may be used.

Syntax = AVERAGEIFS (Range, Range 1, criteria 1, range 2, criteria 2)

In this case, my requirement is to find the average of the data in a range of numbers, without considering '0 - Zero' & values greater that '3000'.

Total data in the range is 31 nos ,

And the formulae will be

=AVERAGEIFS(G5:G35,G5:G35, "> 3000", G5:G35,"<>0")
- Reply
To post as a guest, your comment is unpublished.
La · 1 years ago
I have 4 variables: 1st: Date of onset, 2nd: Date of termination, 3rd: Date of measurement, 4th: measurement. I want to calculate the average of the 4th variable between each period of onset-termination. How can i do it? I think its a bit tricky. Each date of onset has an counterpart date of termination. The same applies for the 3rd and 4th variable.
How can i make this happen?
- Reply
To post as a guest, your comment is unpublished.
Mark Main · 1 years ago
VBA code to average one or more ranges of nonzero numbers:

Option Explicit
Function avgNonZeros(ParamArray rangeList() As Variant) As Variant
'Returns the average for all nonzeros of rangeList.
'rangeList may be one or multiple ranges.
Dim cell As Range
Dim i As Long
Dim totSum As Long
Dim cnt As Long
DoEvents 'allows calculations prior to performing
avgNonZeros = 0 'default return
For i = LBound(rangeList) To UBound(rangeList)
For Each cell In rangeList(i)
If cell <> 0 Then
totSum = totSum + cell
cnt = cnt + 1
End If
Next cell
Next i
If cnt <> 0 Then avgNonZeros = totSum / cnt
End Function
- Reply
To post as a guest, your comment is unpublished.
Lutfur · 1 years ago
Hi,
I have 200 observations. I need to get an average of 21st to 40th observations. I am struggling to set an averageif formula. Any suggestion will be appreciated.
- Reply
To post as a guest, your comment is unpublished.
Crist · 1 years ago
I have a similar problem like example #3, but the difference is that I need the average between two dates instead of for a single month. Any suggestion?
- Reply
- To post as a guest, your comment is unpublished.
  skyyang · 1 years ago
  
  Hello, Crist,
  
  To solve your problem, please apply the below formula:
  =AVERAGEIFS(C2:C15, A2:A15, F1, B2:B15, ">=" &F2, B2:B15, "<=" &G2, C2:C15, ">300")
  
  Please try, hope it can help you!
  
  Reply
To post as a guest, your comment is unpublished.
pablo · 2 years ago
Hi there,

My is Pablo and I would like to ask you about this situation. I have got a column with several values and some of them are zeros. As they are dB measurements this is the array formula I use to get the average: =10*LOG(AVERAGE(10^(C3:C66/10)))

My problem is that I am trying to get with a formula that does not take in account the zeros.

I have tried the next formula but it seems that does not work for my situation: =10*LOG(AVERAGEif(C3:C66,"<>0",[10^(C3:C66/10)]))

It would be very apprecited if you could give me a hint to solve this problem.

Thank you in advance,

Pablo.
- Reply
To post as a guest, your comment is unpublished.
Stephanie · 3 years ago
I want to take numbers in multiple columns and combine them into one average. I have three columns to average, I want two of the columns to count for 40% each and the remaining column to count for 20%. Is this possible? Thanks!
- Reply
To post as a guest, your comment is unpublished.
ch · 4 years ago
I want to average cells is those cells are between a range.
If A is between range 1 & range 2, average value in B. What formula would I use?
Ex.
A B Range 1 Range 2
$78,093 651,462 55001 60000
$76,968 636,679
$76,946 1,682,295
$73,909 142,277
$73,567 624,257
$71,505 492,828
$67,772 351,316
$63,786 458,169
$63,263 157,665
$58,427 180,498
$56,693 187,028
$56,510 73,339
$56,129 80,832

Thanks for any help.
- Reply
To post as a guest, your comment is unpublished.
Jackie · 4 years ago
7
$3.00
20
$40.00
3
$6.00
0
$-
MY AVERAGE FOR CELL B3,B5,B7,B9 SHOULD BE 10
MY COUNT FOR CELL B3,B5,B7,B9 SHOULD BE 3

How do I get averages for specific cells excluding zero
- Reply

登录到您的帐户

重设密码