Excel中多列的筛选器的平均值 [英] Average of a filter of multiple columns in Excel

问题描述

我想要一个公式来显示Excel中过滤值的平均值。例如，在列AI中有年份，BI列有月份，列CI中有值。

I would like a formula to display the average of a number of filtered values in Excel. For example, in column A I have the year, in column B I have the month, and in column C I have the values.

所以我想选择2011年， 5月份获得满足这些要求的C列中的值的平均值。我正在考虑一个类似...的功能

So I would like to select the 2011 year, the month of May and get the average of the values in column C that satisfy these requirements. I'm thinking a function like...

=AVERAGE(IF(AND($A$1:$A:$100=2011,$B$1:$B:$100=May),$C$1:$C:$100))

但是我不认为这会奏效，我已经尝试过了。

But I don't think this will work, and I've tried it.

推荐答案

使用AverageIfs函数。例如

Use the AverageIfs function. e.g.

AVERAGEIFS（C1：C100，A1：A100，= 2011，B1：B100，= May）

AVERAGEIFS(C1:C100,A1:A100,"=2011",B1:B100,"=May")

这篇关于Excel中多列的筛选器的平均值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！