Pelo que entendi, você tem uma célula com fórmula =SUMPRODUCT(B93:B96,B46:B49)/SUM(B46:B49) e deseja preencher esse direito para que a célula na próxima coluna, mas na mesma linha, tenha a fórmula =SUMPRODUCT(C92:C95,C45:C48)/SUM(C45:C48) . Ou seja, você deseja que a coluna aumente em 1 como normal, mas também a linha diminua em 1.
Você pode conseguir isso usando a função OFFSET . Isso permite que você construa um intervalo com uma distância especificada de um intervalo conhecido. Podemos usar a função COLUMN para especificar quanto de um deslocamento queremos.
Os intervalos que queremos são B93:B96 e B46:B49 , compensados por -1 linha e coluna +1 para cada coluna após a primeira. Portanto, OFFSET(B93:B96, -1, 1) retornará uma referência a C92:C95 . No entanto, nossa referência atualizará naturalmente a coluna, para que possamos deixar a compensação da coluna.
Para o intervalo que é B93:B96 na fórmula original, na primeira célula queremos OFFSET(B93:B96, 0, 0) , no segundo queremos OFFSET(C93:C96, -1, 0) , no terceiro queremos OFFSET(D93:D96, -2, 0) e assim por diante. Podemos usar =COLUMN(A1) para obter um resultado que é o número da coluna atual (porque a referência será atualizada para B1 na segunda coluna, etc). Combinando estes, podemos usar o OFFSET(B93:B96, 1 - COLUMN(A1), 0)
o que podemos simplificar alterando a referência:
OFFSET(B94:B97, -COLUMN(A1), 0)
Na primeira célula, isso se traduz em OFFSET(B94:B97, -1, 0) , que é B93:B96 . Na segunda célula, a fórmula será preenchida como OFFSET(C94:C97, -COLUMN(B1), 0) , que é C92:C95 .
Portanto, as duas referências de que precisamos são OFFSET(B94:B97, -COLUMN(A1), 0) e OFFSET(B47:B50, -COLUMN(A1), 0) . Daí a fórmula será
=SUMPRODUCT(OFFSET(B94:B97, -COLUMN(A1), 0), OFFSET(B47:B50, -COLUMN(A1), 0)) / SUM(OFFSET(B47:B50, -COLUMN(A1), 0))
Existe uma complicação final. Quando eu estava testando isso na minha cópia do Excel 2007, descobri que dentro da fórmula SUMPRODUCT , COLUMN(A1) estava retornando uma matriz contendo o valor 1, em vez de apenas o valor 1. (Você pode ver isso na caixa de diálogo Avaliar Fórmula: quando avalia COLUMN(A1) , o resultado é {1} , em vez de apenas 1 , e a expressão SUMPRODUCT obtém dois #VALUE! de argumentos e acaba sendo 0.) Consoante sua versão pode não acontecer com você. Se isso acontecer, coloque o COLUMN(A1) dentro de um SUM() , da seguinte forma:
=SUMPRODUCT(OFFSET(B94:B97, -SUM(COLUMN(A1)), 0), OFFSET(B47:B50, -SUM(COLUMN(A1)), 0)) / SUM(OFFSET(B47:B50, -COLUMN(A1), 0))
Você notará que eu não agrupei o COLUMN(A1) final em SUM() ; isso é porque este retornou um número como de costume, não uma referência de array. Presumo que, como a função SUM não aceita referências de matriz, o Excel percebeu que, neste caso, não desejávamos uma matriz de COLUMN .