vba - 另一个公式中使用的单元格旁边的单元格的平均值

Question

我是在这里提问的新手，所以我希望我能正确回答。我正在帮助我父亲制作电子表格，但我在弄清楚如何做一个公式时遇到了问题。不知道是不是可以用公式来完成，还是必须用宏来完成。

这是一个包含多个匹配项的计分表。每场比赛都有一个总分，得分旁边的单元格是一个 X 计数(靶心数)。在同一行(K 列)中，我计算前 6 名的总分并将它们平均:

=AVERAGE(LARGE((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
请帮忙。如果需要任何澄清，请告诉我。

屏幕截图:

Answer 1

请尝试以下公式:

=SUMPRODUCT(O15:BM15,
--(MOD(COLUMN(N15:BL15)-COLUMN($N15),3)=0),
--(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

它是如何工作的？
SUMPRODUCT有 3 个参数 - 第一个是要求和的数组，接下来的 2 个参数返回 0 的数组和 1只选择第一个数组的有趣元素。

MOD(COLUMN(N15:BL15)-COLUMN($N15),3)=0)

包含此部分是为了避免列出每个单元格。如果分数在输入范围的每第三列，我们可以计算相对于第一列的列号，函数 MOD(column,3) 返回: {1,0,0,1,0,0...} .所以只有输入数组的第三列才会包含在总和中。

(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个分数。最棘手的部分是决定如何处理关系。我的方法是采取:

如果两个分数相同，取靶心数多的一个

如果仍然绑定(bind)，则从第一列中取一个

这意味着，而不是 N15我们计算的值:

N15+O15/10^3+COLUMN(N15)/10^6

使用您的样本数据，它评估为: 566.017014 .前三位小数为靶心数，后三位为列号。

您可以使用相同的公式通过更改第一个参数来计算前 6 名的平均值:

=SUMPRODUCT(N15:BL15,
--(MOD(COLUMN(N15:BL15)-COLUMN($N15),3)=0),
--(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