Impedir que a fórmula do Excel seja alterada ao inserir / excluir linhas

Question

Estou tentando criar uma pasta de trabalho de orçamento para meu orçamento pessoal usando 13 folhas, 1 para os totais e as outras 12 para cada mês. Eu não posso, para a vida de mim, descobrir por que as fórmulas que eu mudei quando insiro uma linha em uma das folhas mensais. Aqui está um exemplo de uma das fórmulas que tenho:

=SUMIF(JUN!$G$2:$G$500,"Utilities", JUN!$D$2:$D$500)

Se eu inserir uma linha na parte superior de uma planilha, ela aumentará os dois para três, descartando os cálculos. Existe alguma maneira que eu possa bloquear a fórmula de mudar em tudo? É incrivelmente frustrante.

    

Answer 1

O que você precisa entender é que o absoluto das referências absolutas, conforme especificado pelo $ , não é absolutamente absoluto; -)

Agora que esse trava-língua está fora do caminho, deixe-me explicar.

O absoluto só se aplica ao copiar-colar ou preencher a fórmula. Inserir linhas acima ou colunas à esquerda de um intervalo absolutamente referenciado "deslocará" o endereço do intervalo de forma que os dados o intervalo apontem para permanecer o mesmo.

Além disso, inserir linhas ou colunas no meio do intervalo irá expandi-lo para abranger as novas linhas / colunas. Assim, para "adicionar" uma linha de dados a um intervalo (tabela), é necessário inseri-lo após a primeira linha de dados.

A maneira mais simples de permitir adicionar uma linha de dados acima do intervalo de dados atual é sempre ter uma linha de cabeçalho e incluir a linha de cabeçalho no intervalo real. Essa é exatamente a solução proposta por cybernetic.nomad em este comente .

Mas , ainda há mais um problema, e isso adiciona uma linha de dados após o fim da tabela. Basta digitar os novos dados na linha depois que a última linha de dados não funcionar. Nem irá inserir uma linha antes da linha após a última linha.

A solução mais simples para isso é usar uma linha especial "última", incluir essa linha no intervalo de dados e sempre acrescentar novas linhas inserindo antes nessa linha especial.

Eu normalmente reduzo a altura da linha e preencho as células com uma cor apropriada:

Paraoseuexemplo,afórmula"mais simples" completa seria:

=SUMIF(JUN!$G$1:$G$501,"Utilities",JUN!$H$1:$H$501)

Outra maneira de atingir o mesmo objetivo é usar uma fórmula dinâmica que se ajusta automaticamente à quantidade de dados na tabela. Existem algumas variações diferentes disso, dependendo das circunstâncias exatas e precisamente o que deve ser permitido à mesa.

Se, como é tipicamente o caso (seu exemplo, por exemplo), a tabela começa no topo da planilha, tem um cabeçalho de linha e os dados são contíguos sem intervalos, uma fórmula dinâmica simples seria:

=SUMIF(INDEX(JUN!$G:$G,2):INDEX(JUN!$G:$G,COUNTA(JUN!$G:$G)),"Utilities",INDEX(JUN!$H:$H,2):INDEX(JUN!$H:$H,COUNTA(JUN!$G:$G)))

Esta é uma solução melhor do que usar INDIRECT() como

1. Não é volátil e, portanto, a planilha é calculada com mais rapidez e
2. Não vai quebrar se você inserir colunas à esquerda da tabela.

A técnica de fórmula dinâmica pode ser aprimorada ainda mais usando-a em um Fórmula Nomeada .

Obviamente, a solução melhor é referências estruturadas .

    

Answer 2

Então, você está dizendo que, se você inserir uma nova linha 2 (entre a linha atual 1 e a linha 2), Você quer que a fórmula olhe para a nova linha 2? Aqui estão algumas variações:

=SUMIF(INDIRECT("JUN!$G$2:$G$500"),"Utilities", INDIRECT("JUN!$D$2:$D$500"))

sempre olhará as linhas 2 a 500, sem considerar as linhas sendo renumeradas por inserções (ou exclusões). Isso significa que, se você inserir uma linha, a Linha 500 original será renumerado para 501 e será eliminado do intervalo. Se você quiser olhar para a linha atual 2 até a Linha 500 original, use

=SUMIF(INDIRECT("JUN!$G$2"):JUN!$G$500,"Utilities", INDIRECT("JUN!$D$2"):JUN!$D$500)

Caso não seja óbvio, INDIRECT() recebe um argumento de string (texto) e o interpreta como um endereço. Permite fazer endereçamento invariável, porque as strings (que parecem endereços) não serão ajustadas quando outros endereços são ajustados devido à inserção / exclusão de linha / coluna.

Observe que os caracteres $ nas cadeias de endereços são opcionais; eles não têm efeito.