У меня есть строка, которую я хочу заполнить формулами типа
=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)a 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(); это потому, что эта функция вернула число, как обычно, а не ссылку на массив. Я предполагаю, что поскольку функция SUMне принимает ссылки на массив, Excel понял, что в этом случае нам не нужен массив COLUMN.