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
.