Eu tenho uma linha que desejo preencher com fórmulas como
=SUMPRODUCT(B93:B96,B46:B49)/SUM(B46:B49)
=SUMPRODUCT(C92:C95,C45:C48)/SUM(C45:C48)
=SUMPRODUCT(D91:D94,D44:D47)/SUM(D44:D47)
mas arrastar a fórmula altera apenas a parte alfabética do índice. Existe alguma maneira de o Excel alterar corretamente os índices do array?
Responder1
Pelo que entendi, você tem uma célula com fórmula =SUMPRODUCT(B93:B96,B46:B49)/SUM(B46:B49)e deseja preencher isso direito para que a célula da próxima coluna mas da mesma linha tenha a fórmula =SUMPRODUCT(C92:C95,C45:C48)/SUM(C45:C48). Ou seja, você deseja que a coluna aumente 1 normalmente, mas também que a linha diminua 1.
Você pode conseguir isso usando a OFFSETfunção. Isso permite que você construa um intervalo a uma distância especificada de um intervalo conhecido. Podemos usar a COLUMNfunção para especificar quanto de deslocamento queremos.
Os intervalos que queremos são B93:B96e B46:B49, compensados por -1 linha e +1 coluna 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 o deslocamento da coluna de fora.
Para o intervalo que está B93:B96na fórmula original, na primeira célula queremos OFFSET(B93:B96, 0, 0), na segunda queremos OFFSET(C93:C96, -1, 0), na terceira 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
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)which is B93:B96. Na segunda célula, a fórmula será preenchida exatamente como OFFSET(C94:C97, -COLUMN(B1), 0)é C92:C95.
Portanto, as duas referências que precisamos são OFFSET(B94:B97, -COLUMN(A1), 0)e OFFSET(B47:B50, -COLUMN(A1), 0). Portanto a fórmula será
=SUMPRODUCT(OFFSET(B94:B97, -COLUMN(A1), 0), OFFSET(B47:B50, -COLUMN(A1), 0)) / SUM(OFFSET(B47:B50, -COLUMN(A1), 0))
Há uma complicação final. Quando eu estava testando isso na minha cópia do Excel 2007, descobri que dentro da SUMPRODUCTfórmula COLUMN(A1)estava retornando umvariedadecontendo 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 SUMPRODUCTexpressão recebe dois #VALUE!argumentos e acaba sendo 0.) Dependendo do seu versão isso pode não acontecer com você. Se isso acontecer, envolva o COLUMN(A1)interior a SUM(), assim:
=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 não envolvi o final COLUMN(A1)em um SUM(); isso ocorre porque este retornou um número como de costume, não uma referência de array. Presumo que, como a SUMfunção não aceita referências de array, o Excel percebeu que não queríamos um array neste COLUMNcaso.