Inserir células no Excel sem atualizar fórmulas adjacentes

Question

Às vezes, comparo visualmente dois conjuntos de dados colando-os lado a lado no Excel com uma fórmula no meio como =A1=C1 . Isso retornará verdadeiro ou falso com base em se as células são uma correspondência exata. Quando faltam um registro para os conjuntos de dados, posso fazer com que eles se alinhem novamente simplesmente inserindo uma nova célula e alterando os existentes. No entanto, quando faço isso, atualiza as fórmulas para deslocar a célula de referência para baixo pela quantidade de células que inseri, assim:

Issocausaproblemasporqueeutenhoquerefazertodasasfórmulasnomeiocadavezqueissoacontece.Emumconjuntodedadoslongo,issoficamuitotedioso.Existealgumamaneirade"bloquear" as fórmulas nas colunas de comparação para que não se importe se eu inserir ou excluir células, elas sempre ficarão apontando para as células originais que eu configurei?

BTW, sei que $ A $ 1 bloqueará uma referência, mas isso só funciona se você estiver movendo essa célula, não movendo a célula referenciada. Se você colocar isso no topo e arrastá-los para baixo, eles ficarão todos com $ A $ 1, não importa em qual linha você arraste (mais, ele ainda atualiza depois de inserir uma célula na coluna de referência).

    

Answer 1

Se eu entendi sua pergunta, acho que isso pode funcionar:

=OFFSET($B$1,ROW()-1,-1)=OFFSET($B$1,ROW()-1,1)

Onde B1 é uma âncora arbitrária para suas fórmulas e você sempre calcula o deslocamento para a célula em ambos os lados da comparação.

    

Answer 2

Em vez de inserir células, você pode copiar as células que deseja deslocar para baixo e usar valores especiais de colar para colocá-las onde quiser. Em outras palavras, mover os valores em vez das células impedirá que as fórmulas sejam ajustadas.

    

Answer 3

Eu coloquei um conjunto de dados na coluna A e o outro na coluna B. Depois colei a fórmula abaixo em cada célula da coluna C. A função ROW() retorna o número da linha atual para que você possa inserir / excluir dados na coluna A ou B sem ter que colar e repassar suas fórmulas. Você pode colar a fórmula em qualquer coluna e ela ainda funcionará - comparando as colunas A e B na linha em que está.

=IF(INDIRECT(ADDRESS(ROW(),1)) = INDIRECT(ADDRESS(ROW(),2)), "ok", "***diff***")

Eu concordo - gostaria que houvesse alguma maneira de dizer ao Excel para não alterar as fórmulas quando você insere e exclui dados em uma coluna.