我很想在这里提问,所以我希望我能说得对 . 我用电子表格帮助我的父亲,我在弄清楚如何做一个公式时遇到了问题 . 不知道是否可以使用公式完成,或者是否必须使用宏完成 .
这是一个包含多个匹配的评分表 . 对于每个匹配,总得分和得分旁边的单元格是X计数(bulleyes的数量) . 在同一行(列K)中,我计算前6个总分并对它们取平均值:
=平均值(大((N15,Q15,T15,W15,Z15,AC15,AF15,AI15,AL15,AO15,AR15,AU15,AX15,BA15,BD15,BG15,BJ15),{1,2,3,4 ,5,6}))
现在我需要获取上面公式中使用的总分数旁边的X计数的AVG,并将解决方案放在L列中 .
例如,如果在该行中用于AVG得分的单元格是:
N15,Q15,T15,W15,Z15,AC15
那么需要用于X计数AVG的单元格将是:
O15,R15,U15,X15,AA15,AD15
这个结果将被放入 L15
请帮忙 . 如果需要任何澄清,请告诉我 .
Screen Shot:
2 回答
请尝试以下公式:
它是如何工作的?
SUMPRODUCT
有3个参数 - 第一个是要求和的数组,接下来的2个参数返回0
和1
的数组,只选择第一个数组的有趣元素 .MOD(COLUMN(N15:BL15)-COLUMN($N15),3)=0)
包含此部分是为了避免列出每个单元格 . 如果得分在输入范围的每三列中,我们可以计算相对于第一列的列数,并且函数MOD(列,3)返回:
{1,0,0,1,0,0...}
. 因此,只有输入数组的每三列都包含在sum中 .(N15:BL15+O15:BM15/10^3+COLUMN(N15:BL15)/10^6>= LARGE(N15:BL15+O15:BM15/10^3+COLUMN(N15:BL15)/10^6,6)
这部分是决定最终总和中应包括哪6个分数 . 最棘手的部分是决定如何处理关系 . 我的方法是采取:
如果两个分数相同,则选择具有较多裸数的分数
如果仍然绑定,请从第一列中取出一个
这意味着我们计算的不是
N15
值而是使用您的示例数据,它的计算结果为:
566.017014
. 前三个小数位是bulleyes的数量,接下来的3是列数 .您可以使用相同的公式通过更改第一个参数来计算前6个分数的平均值:
你可以尝试这个不那么优雅的解决方案:
通过Ctr Shift输入数组公式输入单元格L15:M15(2个单元格),如下所示:
添加括号 .
数字
6
等于您想要返回的最高分数 . 现在,为什么2个细胞(L15:M15) . 我不能让SUMPRODUCT
从INDEX
评估结果数组,所以我们必须在2个单元格中输入它 . 我不认为这会是一个问题,因为在你的屏幕截图中,没有使用M列 .Note: 如果评估的范围少于6个项目,则会出错 . 用户3964075也很好 . 它可能会也可能不会处理关系 .