我有一行想要用如下公式填充
=SUMPRODUCT(B93:B96,B46:B49)/SUM(B46:B49)
=SUMPRODUCT(C92:C95,C45:C48)/SUM(C45:C48)
=SUMPRODUCT(D91:D94,D44:D47)/SUM(D44:D47)
但拖动公式只会改变索引的字母部分。有什么方法可以让 excel 正确更改数组索引吗？
答案1
据我了解，您有一个带有公式的单元格=SUMPRODUCT(B93:B96,B46:B49)/SUM(B46:B49)，并且想要正确填充它，以便下一列但同一行的单元格具有公式=SUMPRODUCT(C92:C95,C45:C48)/SUM(C45:C48)。 也就是说，您希望列正常增加 1，但行也减少 1。
您可以使用OFFSET函数来实现这一点。这允许您构建与已知范围有指定距离的范围。我们可以使用该COLUMN函数来指定我们想要的偏移量。
我们想要的范围是B93:B96和B46:B49，第一列之后的每一列偏移 -1 行和 +1 列。因此，OFFSET(B93:B96, -1, 1)将返回对 的引用C92:C95。但是，我们的引用自然会更新列，因此我们可以忽略列偏移。
B93:B96对于原始公式中的范围，在第一个单元格中我们想要OFFSET(B93:B96, 0, 0)，在第二个单元格中我们想要OFFSET(C93:C96, -1, 0)，在第三个单元格中我们想要OFFSET(D93:D96, -2, 0)等等。我们可以使用=COLUMN(A1)来获得当前列的编号结果（因为引用将更新为第二列中的 B1，等等）。结合这些，我们可以使用 ，
OFFSET(B93:B96, 1 - COLUMN(A1), 0)
我们可以通过改变引用来简化：
OFFSET(B94:B97, -COLUMN(A1), 0)
在第一个单元格中，这转换为OFFSET(B94:B97, -1, 0)。B93:B96在第二个单元格中，公式将直接填充OFFSET(C94:C97, -COLUMN(B1), 0)为C92:C95。
因此我们需要的两个参考点是OFFSET(B94:B97, -COLUMN(A1), 0)和OFFSET(B47:B50, -COLUMN(A1), 0)。因此公式将是
=SUMPRODUCT(OFFSET(B94:B97, -COLUMN(A1), 0), OFFSET(B47:B50, -COLUMN(A1), 0)) / SUM(OFFSET(B47:B50, -COLUMN(A1), 0))
最后还有一个问题。当我在 Excel 2007 上测试时，我发现公式内部SUMPRODUCT返回COLUMN(A1)了一个大批包含值 1，而不仅仅是值 1。（您可以在“计算公式”对话框中看到这一点：计算结果时，COLUMN(A1)结果是{1}而不仅仅是1，并且SUMPRODUCT表达式获得两个#VALUE!参数并最终为 0。）根据您的版本，这种情况可能不会发生在您身上。如果发生这种情况，请将 包装COLUMN(A1)在 内SUM()，如下所示：
=SUMPRODUCT(OFFSET(B94:B97, -SUM(COLUMN(A1)), 0), OFFSET(B47:B50, -SUM(COLUMN(A1)), 0)) / SUM(OFFSET(B47:B50, -COLUMN(A1), 0))
您会注意到，我没有将 final 包装COLUMN(A1)在 中SUM()；这是因为这个像往常一样返回一个数字，而不是数组引用。我假设由于该函数不接受数组引用，Excel 意识到我们在这种情况下SUM不想要数组。COLUMN