我有一行想要用類似的公式填充
=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)which is 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，而COLUMN(A1)不僅僅是值1 {1}。）取決於您的版本這可能不會發生在你身上。如果是這樣，請將其包裹在 a 內，如下所示：1SUMPRODUCT#VALUE!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))
你會注意到我沒有將決賽包裹在;COLUMN(A1)中。SUM()這是因為這個像往常一樣回傳一個數字，而不是數組引用。我假設由於函數不接受數組引用，Excel 意識到我們不希望在這種情況下SUM輸出數組。COLUMN