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