Question

如何在EXCEL中求一列数字X在各个区间内，所对应的Y值的平均值？

如下图，我想求X在各个区间内，所对应的Y值的平均值和总和，谢谢！

Answer 1

C24公式：
=SUMIFS(C$2:C$21,B$2:B$21,">"&(ROW(A1)-1)*0.2,B$2:B$21,"<="&ROW(A1)*0.2)
下拉；
D24公式：
=SUMIFS(C$2:C$21,B$2:B$21,">"&(ROW(A1)-1)*0.2,B$2:B$21,"<="&ROW(A1)*0.2)
下拉。

追问

D24应该是求Y的平均值哦，怎么跟C24的求和公式一样的呢？

追答

是呀，不好意思，公式写差了，修改为：
=AVERAGEIFS(C$2:C$21,B$2:B$21,">"&(ROW(A1)-1)*0.2,B$2:B$21,"<="&ROW(A1)*0.2)

追问

谢谢！

请问能否把区间改为自己输入的方式呢，例如0<X<=0.5, 0.5<X<=1，这个区间有时需要变化的

追答

公式分别修改为：
=SUMIFS(C$2:C$21,B$2:B$21,">"&-LOOKUP(,-LEFT(B24,ROW($1:$10))),B$2:B$21,""&-LOOKUP(,-LEFT(B24,ROW($1:$10))),B$2:B$21,"<="&-LOOKUP(,-RIGHT(B24,ROW($1:$10))))