代码之家 › 专栏 › 技术社区 › zim

如何获取Excel中另一列对应值的平均值

vlookup average excel

zim · 技术社区 · 8 年前

我有一组excel数据,如下所示:;

A   B        C      D   E

0   0   0.510678961 3   1
0   1   0.582611161 15  1
0   2   0.274447714 2   1
0   3   0.258144341 2   1
0   4   0.574404811 11  1
0   5   0.330260801 2   1
0   6   0.252972823 2   1
0   7   0.536704433 2   1
0   8   0.563287425 2   1
0   9   0.469793466 32  1
0   10  0.504253272 5   1
0   11  0.509408549 36  4
0   12  0.539940366 54  1
0   13  0.472334971 6   1
0   14  0.423524875 2   1
0   15  0.254872928 13  1
0   16  0.231080412 8   1
0   17  0.39818992  22  1
0   18  0.545173384 4   1
0   19  0.280489713 9   1
0   20  0.280509697 6   1
0   21  0.560193886 6   1
0   22  0.485332134 8   2
0   23  0.481483179 3   1
0   24  0.496115634 29  4
0   25  0.466198213 2   1
0   26  0.552777113 5   1
0   27  0.240848653 6   1
0   28  0.30456948  17  2
0   29  0.315246183 6   1
1   0   0.510678961 3   1
1   1   0.254872928 15  1
1   2   0.274447714 2   1
1   3   0.258144341 40  1
1   4   0.231080412 11  1
1   5   0.330260801 2   1
1   6   0.252972823 2   1
1   7   0.536704433 2   1
1   8   0.563287425 2   1
1   9   0.469793466 32  1
1   10  0.504253272 5   1
1   11  0.509408549 36  4
1   12  0.539940366 54  1
1   13  0.472334971 6   1
1   14  0.423524875 2   1
1   15  0.254872928 13  1
1   16  0.231080412 8   1
1   17  0.39818992  22  1
1   18  0.545173384 4   1
1   19  0.280489713 9   1
1   20  0.280509697 6   1
1   21  0.560193886 6   1
1   22  0.485332134 8   2
1   23  0.481483179 3   1
1   24  0.496115634 29  4
1   25  0.466198213 2   1
1   26  0.552777113 5   1
1   27  0.240848653 6   1
1   28  0.30456948  17  2
1   29  0.315246183 6   1
2   0   0.510678961 3   1
2   1   0.254872928 15  1
2   2   0.274447714 2   1
2   3   0.258144341 40  1
2   4   0.231080412 11  1
2   5   0.330260801 2   1
2   6   0.252972823 2   1
2   7   0.536704433 2   1
2   8   0.386891594 2   1
2   9   0.469793466 32  1
2   10  0.504253272 5   1
2   11  0.509408549 5   1
2   12  0.539940366 54  1
2   13  0.472334971 6   1
2   14  0.423524875 2   1
2   15  0.254872928 13  1
2   16  0.231080412 8   1
2   17  0.39818992  22  1
2   18  0.545173384 4   1
2   19  0.280489713 9   1
2   20  0.280509697 6   1
2   21  0.231080412 6   1
2   22  0.485332134 8   2
2   23  0.481483179 3   1
2   24  0.496115634 29  4
2   25  0.466198213 2   1
2   26  0.552777113 5   1
2   27  0.240848653 6   1
2   28  0.30456948  5   2
2   29  0.315246183 6   1
3   0   0.510678961 3   1
3   1   0.254872928 15  1
3   2   0.274447714 2   1
3   3   0.258144341 40  1
3   4   0.231080412 11  1
3   5   0.330260801 2   1
3   6   0.252972823 2   1
3   7   0.536704433 2   1
3   8   0.386891594 2   1
3   9   0.469793466 32  1
3   10  0.504253272 5   1
3   11  0.509408549 17  1
3   12  0.539940366 54  1
3   13  0.472334971 6   1
3   14  0.423524875 2   1
3   15  0.254872928 13  1
3   16  0.231080412 76  1
3   17  0.39818992  22  1
3   18  0.231080412 4   1
3   19  0.280489713 9   1
3   20  0.280509697 6   1
3   21  0.231080412 6   1
3   22  0.485332134 8   2
3   23  0.481483179 3   1
3   24  0.496115634 29  4
3   25  0.466198213 2   1
3   26  0.231080412 5   1
3   27  0.240848653 6   1
3   28  0.30456948  5   2
3   29  0.315246183 6   1

.................................

我需要从C列中为每组30个值找到最小的10个数字,从D列中得到相应的值,并从D列中找到这10个值的平均值。这必须在每组30行中重复,最多100组。

我用SMALL试过VLOOKUP。但我的要求似乎太复杂了,我无法得到正确的答案。我将非常感谢你的帮助。提前谢谢。

2 回复 | 直到 8 年前

Ron Rosenfeld 8 年前

如果 G2 包含所需的组号(来自列A),则以下公式应适用:

=AVERAGEIFS(colD,colA,G2,colC,"<="&AGGREGATE(15,6,1/(1/((colA=G2)*colC)),10))

这个 AGGREGATE 函数将从相应组中的值数组中返回第十个最小值,并且 DIV/0 错误,如果不是。。这个 1/(1/(... 生成错误值(被忽略 骨料 )在该阵列中。

根据您发布的数据,我得到以下结果:

注意:如果每组中最小的10个值中有重复项,则平均值将超过10个。如果存在重复项,请仔细想想您希望发生什么。

mquinn 8 年前

好吧,这很棘手,但我认为这可以用简单的公式来完成:

添加到单元格 一层楼 :

=IF($B1=0,SMALL(OFFSET(INDIRECT(CONCAT("$C",ROW()-MOD(ROW(),30)+1),TRUE),0,0,30),$B1+1),IF($B1<10,SMALL(OFFSET(INDIRECT(CONCAT("$C",ROW()-MOD(ROW(),30)+1),TRUE),0,0,30),$B1+1),""))

添加到单元格 G1级 :

=IF($F1<>"",VLOOKUP($F1,OFFSET(INDIRECT(CONCAT("$C",ROW()-MOD(ROW(),30)+1),TRUE),0,0,30,2),2,FALSE),"")

添加到单元格 H1 :

=IF(B1=0,AVERAGE($G1:$G10),"")

如果你把整张纸都填好了,它应该能满足你的需要。例如,范围1:30如下所示。。。

A   B   C           D   E   F           G   H
0   0   0.510678961 3   1   0.231080412 8   7.1
0   1   0.582611161 15  1   0.240848653 6   
0   2   0.274447714 2   1   0.252972823 2   
0   3   0.258144341 2   1   0.254872928 13  
0   4   0.574404811 11  1   0.258144341 2   
0   5   0.330260801 2   1   0.274447714 2   
0   6   0.252972823 2   1   0.280489713 9   
0   7   0.536704433 2   1   0.280509697 6   
0   8   0.563287425 2   1   0.30456948  17  
0   9   0.469793466 32  1   0.315246183 6   
0   10  0.504253272 5   1           
0   11  0.509408549 36  4           
0   12  0.539940366 54  1           
// The latter 20 rows of each set of 30 will be blank in the computed column...

希望这有帮助,如果你愿意,我很乐意解释这是如何工作的