在 Excel 中创建包含第 n 个值的公式

Question 1

一个简单的解决方案是使用顺序（）函数。不过，它在旧版 Excel 中不可用。下面的示例还使用了让（）函数使公式变得更短且更容易理解。

x我设置了一个包含A和m的样本数据表B。我为两列都编了数字，最后每列都有 39 个值。这并不重要，但它有助于理解公式。您需要更改它以匹配您的实际数据。

以下是得出最终结果的公式：

=LET(X,A:A,M,B:B,n,SEQUENCE(COUNTA(X),1,2,6),Xn,INDEX(X,n),Mn,INDEX(M,n),SUMPRODUCT(Xn,Mn)/SUM(Mn))

让我们逐项分解。LET() 允许我们将值定义为变量，因此除最后一项外，其他所有项都是一对的一部分。这些对如下：

X,A:A保存 X 的数据范围。更新它以匹配您的实际数据。
M,B:B保存 M 的数据范围。更新它以匹配您的实际数据。
n,SEQUENCE(COUNTA(X),1,2,6)创建一个数组，计数（在本例中）从 2 到 X 中有多少个非空白，仅报告每 6 个数字。您可能需要编辑它。在我的数据中，实际数据在 X 中从第 2 行开始。结果数组是2,8,14,20,...。关键点：按照这种写法，它将创建一个计数比您的数据高得多的数组，因为它计数相同数量的点，但计数方式为 6。在我的例子中，我拥有的 39 行数据变成了一个数组2,8,14,...224,230,236。当且仅当您在 X 下方没有更多数据时，这才没问题。如果有，您需要编辑它。一个选项是将第一个术语设置为COUNTA(X)/6+1或类似的东西。
Xn,INDEX(X,n)从 X 中提取每个第 n 个项。如果n计数远高于X身高，那么这将包括 X 以下的数据。在我的例子中，这全是空白，算作零，所以没有影响。
Mn,INDEX(M,n)从 M 中提取每第 n 个项。保存与相同的警告Xn。

然后最后一项返回结果：

SUMPRODUCT(Xn,Mn)/SUM(Mn)

SUMPRODUCT()将数组相乘，然后对结果数组求和，因此它等同于x1*m1 + x2*m2 + ... + xn*mn。

这是我的数据的更大屏幕截图，也显示了一些中间步骤。

此公式不能保证完美适合您的用例。不过，它应该能为您提供一个良好的开端。如果您对 X 和 M 使用有限的范围（例如A1:A40而不是A:A），但不SEQUENCE()相应地更改参数，它将尝试提取超出 X 范围的数据并返回 #REF! 错误。

Answer