Digamos que eu tenha uma planilha do Google Sheets ou do Microsoft Excel em que a fórmula na célula A1 seja "=SOMA($A$5:$A$100)". Suponha que haja dados nas células A5 a A10, mas nada (ainda) em A11 a A100.
Agora insira uma linha acima de A5. Isso move os dados da 5ª linha para baixo e A5 agora está em branco. Digite algo em A5. Agora há dados de A5 a A11. Mas a fórmula em A1 foi atualizada para "=SUM($A$6:$A$101)" então não inclui a nova célula.
Marcadores de "referência absoluta" $
foram usados, então eu esperava que a fórmula fosse corrigida.
Existe uma maneira de forçar a fórmula em A1 a ser constante, independentemente de as linhas serem inseridas ou excluídas mais abaixo?
Ideal seria uma solução geral que se aplica tanto ao Planilhas Google quanto ao Microsoft Excel. Se as soluções forem exclusivas para cada uma, observe isso. Obrigado!
Depois de alguma caçada, encontrei uma solução que funciona tanto no Microsoft Excel quanto no Google Sheets.
Ao invés de:
Usar:
Isso pressupõe que a célula com a fórmula seja A1. Espero que isto seja útil para alguém.
Uma opção para sua fórmula na célula A1 é:
Parâmetros de OFFSET:
Isso funciona no Excel e no Planilhas.
A desvantagem é que o OFFSET é volátil, o que significa que será recalculado toda vez que algo na planilha for alterado. Se você tiver muitas fórmulas como essa, isso pode atrasar as coisas.
NOTA: Isso não funciona no Planilhas Google.
INDIRETO e DESLOC são funções voláteis. O que significa que ele recalculará toda vez que houver uma alteração em qualquer célula em qualquer planilha aberta.
Em vez disso, podemos usar INDEX que não é volátil:
E agora, como 5 e 100 são "codificados" e não estão vinculados a uma célula, isso não mudará se uma nova linha for inserida.
Tente esta fórmula:
Comece a fórmula em A4 em vez de A5, você perde o recurso de dados caindo da parte inferior porque a inserção atualizará a linha inferior "A4:A101" após inserir a linha.
etiquetas não valem nada, então se você tiver uma etiqueta em A4 não vai atrapalhar a soma.