Maneira robusta de criar fórmulas no Excel

Question

Para simplificar o problema, digamos que eu tenha uma planilha do Excel que tenha valores numéricos diferentes nas células A1 a B100 (duas colunas por 100 linhas). Na coluna C, gostaria de ter uma fórmula que multiplique os números em A e B para a mesma linha.

A maneira antiga que eu costumava escrever a fórmula era comprar entrando em C1 =A1*B1 e depois copiar a fórmula para C100. Isso funcionou muito bem, mas não é muito robusto porque, se alguém se mover pelas células nas colunas A ou B, a fórmula rastreará o novo local e atrapalhará as coisas.

Alguém então me mostrou um truque para que essa fórmula fosse mais robusta. Eu precisaria inserir em C1 a fórmula =A:A*B:B . Isso efetivamente analisa o valor na mesma linha e, portanto, continuará a funcionar como esperado quando os valores nas colunas A e B forem movidos.

Vamos supor que agora eu queira criar um novo valor calculado na coluna D (por exemplo, valor em A para a mesma linha + valor na coluna A da linha anterior), de modo que a fórmula em D1 seja =A1 . A fórmula em D2 seria =A1+A2 e essa fórmula seria então copiada na coluna D. Isso não é tão robusto, existe uma maneira mais robusta de fazer isso para que fique imune ao movimento das células-fonte ( como meu primeiro exemplo).

Muito obrigado antecipadamente

    

Answer 1

A melhor opção seria usar intervalos nomeados: um intervalo nomeado pode ser relativo à célula inserida. Por exemplo. se você selecionar a célula A2 e inserir um nome _CellAbove with the formula = A1 (without the $ 'inserido automaticamente!), você pode usar CellAbove em qualquer célula para se referir à célula acima da célula. célula com a fórmula.

Portanto, para o seu exemplo, selecione cell D2 e insira dois nomes (usando o Name Manager ): * ColA : =$A2 * ColA_above : =$A1

Em seguida, use =ColA+ColA_above como a fórmula em D2 e copie-a. (Use =ColA em D1 ).

Isso sempre retornará o resultado correto - e não é volátil, o que é realmente útil em grandes cálculos!

Nota adicional: Para o seu primeiro exemplo com a multiplicação, eu recomendo simplesmente converter seus dados para uma tabela do Excel ( Inserir guia - > Tabela . em vez da coluna A e B , você teria colunas, digamos Price e Quantity - e, em vez de =A1*B1 , você acabaria com =[@Price]*[@Quantity] ao inserir a fórmula (da mesma maneira como de costume, isto é, o Excel colocará esses nomes, não precisa se preocupar com isso.) Agora, mesmo se você alterar o strucuter da planilha, as fórmulas funcionarão!

    

Answer 2

Tente isso

=INDIRECT("RC1",0)+INDIRECT("R[-1]C1",0)

Note que isso tem a desvantagem de ser volátil

    

Answer 3

Ou isto:

=SUMPRODUCT(A1:A100+A2:A101)

Espero ter entendido corretamente ...:)

    

Answer 4

Tente este:

=IF(ROW()=1,OFFSET(B1,0,-1),OFFSET(B1,-1,-1)+OFFSET(B1,0,-1))

A única célula que ele referencia é ela mesma, então ela só mudará essa referência se for movida e não importará, de qualquer maneira.