Impede que o Excel altere a fórmula da última linha da tabela ao adicionar uma nova linha

Question

Eu tenho uma tabela com colunas chamadas "date", "cost" e "daily". "Data" é uma data, "custo" é um número e "diário" é uma fórmula que calcula o custo total de cada dia. A fórmula é assim:

= SE (A2 = A3, "", SUMIF (A $ 2: A2, A2, B $ 2: B $ 2)

Isso verifica se a data da linha atual é diferente da data da próxima linha e, em caso afirmativo, retorna a soma dos custos das linhas com a data atual.

O problema ocorre quando adiciono novos dados à tabela. Suponha que haja 10 linhas. Então a célula C10 terá a fórmula:

= SE (A10 = A11, "", SUMIF (A $ 2: A10, A10, B $ 2: B $ 2)

Mas se eu adicionar uma nova linha (por exemplo, pressionando a tecla enquanto a célula C10 estiver selecionada), a fórmula de C10 será alterada automaticamente para:

= SE (A10 = A12 , "", SUMIF (A $ 2: , A10, B $ 2: B $ 2)

Além disso, o estilo da célula é redefinido, de modo que não é mais texto em negrito, e recebo um aviso ao lado da célula que indica "essa célula é inconsistente com a fórmula da coluna".

Como evito esse comportamento?

    

Answer 1

Você pode usar offset () em vez disso. (:

Então você se torna a fórmula:

=IF(A2=offset(A2,1,0),"",SUMIF(A$2:offset(A2,0,0),A2,B$2:B$2)

No meu conjunto de dados de teste funciona, onde adicionar linhas não altera a fórmula removendo as dependências ... por favor, compartilhe se não.

p / s: Coisas (mudança de referência da célula) aconteceram dessa forma porque a fórmula que você definiu está 'dependendo' dos valores A11. E a ação de 'inserir linhas' irá 'reter' a célula original referenciada na fórmula do C10. Que foi "movido" para A12 devido à inserção da linha.