跳到主要内容

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

作者:晓阳 最后修改时间:2020-05-19

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

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

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


箭头蓝色右气泡 使用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…)

  • 平均范围:是平均单元格范围;
  • 条件_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 人工智能助手:基于以下内容彻底改变数据分析: 智能执行   |  生成代码  |  创建自定义公式  |  分析数据并生成图表  |  调用 Kutools 函数...
热门特色: 查找、突出显示或识别重复项   |  删除空白行   |  合并列或单元格而不丢失数据   |   不使用公式进行四舍五入 ...
超级查询: 多条件VLookup    多值VLookup  |   跨多个工作表的 VLookup   |   模糊查询 ....
高级下拉列表: 快速创建下拉列表   |  依赖下拉列表   |  多选下拉列表 ....
列管理器: 添加特定数量的列  |  移动列  |  切换隐藏列的可见性状态  |  比较范围和列 ...
特色功能: 网格焦点   |  设计图   |   大方程式酒吧    工作簿和工作表管理器   |  资源库 (自动文本)   |  日期选择器   |  合并工作表   |  加密/解密单元格    按列表发送电子邮件   |  超级筛选   |   特殊过滤器 (过滤粗体/斜体/删除线...)...
前 15 个工具集12 文本 工具 (添加文本, 删除字符,...)   |   50+ 图表 类型 (甘特图,...)   |   40+ 实用 公式 (根据生日计算年龄,...)   |   19 插入 工具 (插入二维码, 从路径插入图片,...)   |   12 转化 工具 (小写金额转大写, 货币兑换,...)   |   7 合并与拆分 工具 (高级组合行, 分裂细胞,...)   |   ... 和更多

使用 Kutools for Excel 增强您的 Excel 技能,体验前所未有的效率。 Kutools for Excel 提供了 300 多种高级功能来提高生产力并节省时间。  单击此处获取您最需要的功能...

描述


Office Tab 为 Office 带来选项卡式界面,让您的工作更加轻松

  • 在Word,Excel,PowerPoint中启用选项卡式编辑和阅读,发布者,Access,Visio和Project。
  • 在同一窗口的新选项卡中而不是在新窗口中打开并创建多个文档。
  • 每天将您的工作效率提高50%,并减少数百次鼠标单击!
Comments (16)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Hi, I'm trying to create an average only if the row meets criteria in other columns. I need to only include the number in column J if that row has a "B" in column F and a "M" in column E. I've worked out the formula if it's only one condition:

=AVERAGEIF(F2:F114, "B", J2:J114)

But I can't work out how to get it to only average numbers in column J that meet both conditions?
This comment was minimized by the moderator on the site
Hello, Sally
To calculate the average based on multiple criteria, you can apply the AVERAGEIFS function, please use the below formula:
=AVERAGEIFS(J2:J114, F2:F114, "B", E2:E114, "M" )

Please have a try, hope it can help you!
This comment was minimized by the moderator on the site
Thanks so much, this worked perfectly!
This comment was minimized by the moderator on the site
What formula would I use if I wanted to get my averages for a month but not count the days that are zero (0)? I am keeping track of my monthly electric usage but need to get an average even before the end of the months. Any ideas or help?
This comment was minimized by the moderator on the site
Hello, TFkidd
To solve your problem, please apply the below formula:
=AVERAGE(IF(B2:B31<>0,B2:B31))
Please try, hope it can help you!
This comment was minimized by the moderator on the site
Hi there
I am trying to calculate percentage of people from across 4 regions (entered in column b)
and percentage by ethnicity (entered in column D)
I can calculate the average age and percentage of ages, but I can't seem to find a formula to calculate the regions and ethnicity. Any suggestions would be appreciated, thanks Tracy
This comment was minimized by the moderator on the site
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")
This comment was minimized by the moderator on the site
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?
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
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.
This comment was minimized by the moderator on the site
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?
This comment was minimized by the moderator on the site
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!
This comment was minimized by the moderator on the site
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.
This comment was minimized by the moderator on the site
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!
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations