Como mantenho as fórmulas e os intervalos de Formatação Condicional de mudar automaticamente?

Question

Descobri que as fórmulas e intervalos de formatação condicional serão automaticamente ajustados quando você copiar, excluir ou mover dados em uma planilha. Embora essa seja uma boa idéia, ela tende a quebrar as coisas para mim de uma maneira bastante estranha.

Para evitar isso, tentei criar regras que se aplicassem à planilha inteira e removê-las dos cabeçalhos de coluna para destacar os dados que eu queria verificar.

Exemplo: =AND(A$1="Check This Column For Blanks),ISBLANK(A1)) aplicado a =$1:$1048576

No entanto, mesmo com a regra explicitamente aplicada a toda a planilha, ela ainda estava se ajustando automaticamente (e quebrando caminhos estranhos ao fazer isso) enquanto eu trabalhava na planilha.

Como posso evitar isso?

    

Answer 1

Quando eu preciso de um intervalo que não deve ser alterado em nenhuma circunstância, incluindo mover, inserir e excluir células, usei um intervalo nomeado e o INDIRECT função.

Por exemplo, se eu quiser que um intervalo para sempre se aplique às células A1:A50 , eu defini um intervalo nomeado por meio do Gerenciador de nomes:

NoGerenciadordenomes,adicioneumnovointervalo(cliqueemNovo)e,nocampoRefere-sea:,useafunçãoINDIRETOparaespecificarointervalodecélulasdesejado,porexemplo,=INDIRECT("A1:A50") ou =INDIRECT("Sheet!A1:A50") . Como o intervalo é tecnicamente apenas um argumento textual, nenhuma quantidade de reorganizar as células fará com que o Excel as atualize.

Além disso, isso funciona pelo menos no Excel 2010 e no Excel 2013. Embora minha captura de tela seja de 2013, usei essa técnica exata em 2010 no passado.

Advertências

1. Lembre-se de que essa invariância também pode atrapalhar você. Por exemplo, se você alterar o nome da planilha, o intervalo nomeado será interrompido.

2. Tenho notado um pequeno impacto no desempenho ao usar essa estratégia em um número significativo de células. Um modelo que eu uso no trabalho usa essa técnica com intervalos nomeados que abrangem vários milhares de intervalos de células diferentes, e o Excel parece um pouco lento quando eu atualizo as células nesses intervalos. Isso pode ser minha imaginação ou pode ser o fato de o Excel estar fazendo chamadas de função adicionais para INDIRECT.

Answer 2

Eu descobri que as regras são muito fáceis de quebrar, mas aqui está algo que você pode tentar que não pareçam quebrar nenhuma regra.

Você pode alterar o texto dentro das células. Se você precisar adicionar uma linha, adicione seus dados no final da sua tabela e reordene-os. Se você precisar excluir uma linha, remova apenas o texto / números e, em seguida, reorganize a tabela.

Isso funciona para mim quando tenho formatação condicional aplicada a colunas e geralmente defino a formatação para toda a coluna, por exemplo. $ F: $ F Ele ainda deve funcionar se você estiver formatando para um intervalo parcial, apenas certifique-se de que quando terminar de adicionar / remover e recorrer que todos os dados que deseja que sejam formatados ainda estejam dentro dos parâmetros do intervalo original.

É uma enorme frustração para mim também.

Espero que isso ajude.

    

Answer 3

Descobri que o uso da função INDIRECT e da função ROW na regra Formatação condicional elimina o problema de o Excel criar novas regras e alterar o intervalo.

Por exemplo, eu queria adicionar uma linha entre linhas na minha planilha de registro de talão de cheques quando o mês mudava de uma linha para a seguinte. Então, minha fórmula na regra do CF é:

=MONTH(INDIRECT("C"&ROW()))<>MONTH(INDIRECT("C"&ROW()-1))

em que a coluna C da minha planilha contém a data. Eu não precisei fazer nada de especial para o range (não precisei definir um Range Name, etc.).

Assim, no exemplo do autor original, em vez de "A1" ou "A $ 1" na regra do CF, use:

INDIRECT("A"&ROW())

    

Answer 4

Não tenho SO de certeza e enfrento o mesmo problema com frequência.

Eu diria que o campo "Aplicar a" no painel Formatação condicional (CF) funcionará SEMPRE dinamicamente. Então, ele sempre converterá qualquer referência ao formato =$A$1:$A$50 .

É uma dor.

    

Answer 5

Eu experimentei um problema muito semelhante. Fiz algumas macros para adicionar linhas e copiar fórmulas e, em seguida, ajustar colunas e tamanhos de linhas para formatar a aparência da planilha. Eu encontrei este problema ocorre em uma das duas ocasiões.

1) Quando algo do INSIDE o "aplica a" é cortado / colado fora desse intervalo.

2) Quando há células mescladas dentro do "aplica a", e qualquer uma das linhas ou colunas são ajustadas.

Aparece durante o problema da célula mesclada, que o Excel precisa desmembrar tudo, recalcular seu aplicativo condicional, ajustar todas as células (adicionar ou excluir linhas ou não) e, em seguida, removê-las de volta. É invisível para nós, mas parece ser como isso é aplicado.

Pensei que isso poderia nos ajudar a encontrar a solução para isso.

-T

    

Answer 6

Eu tenho uma solução simples para isso.

Basta mover para uma célula que esteja no intervalo (a que não está quebrada no intervalo), clique em Pincel e depois cole na coluna inteira. Mais uma vez, ele mostrará onde foi quebrado, você só precisa fazer um pintor de formato para a célula que quebrou o intervalo. Agora, isso também pode parecer um pouco longo, você pode simplesmente criar uma macro simples para isso.

    

Answer 7

Na fórmula de formatação condicional, use a notação R1C1 com INDIRECT function:
Exemplo 1:

If same row column A = 1 ...

=IF($A1=1,1,0) becomes 
=IF(INDIRECT("RC1",FALSE)=1,1,0)

Exemplo 2: If same row column A = 2 AND next row column A = 3 ...

=IF(AND(($A1=2),($A2=3)),1,0) becomes
=IF(AND((INDIRECT("RC1",FALSE)=2),(INDIRECT("R[1]C1",FALSE)=3)),1,0)

    

Answer 8

Se o caso de uso for gerar uma planilha de relatórios, isso deve funcionar bem:

Você pode ignorar a utilidade excessiva do Excel com algumas etapas de configuração únicas, se também estiver disposto a executar manualmente uma simples atualização dos dados finais depois de fazer toda a entrada de dados.

Supondo que seus dados estão organizados normalmente (em linhas):

1. Insira todos os seus dados em uma única planilha. Para este tutorial, vou nomear a folha Input . Não aplique formatação condicional a ele. Sinta-se à vontade para mudar os valores para o conteúdo do seu coração (por exemplo, excluir / inserir / copiar / colar)
2. Crie outra planilha vazia e nomeie-a como quiser (por exemplo, Output ). Copie manualmente a formatação global, como larguras de coluna e formatação de cabeçalho (não o conteúdo dos cabeçalhos, apenas a formatação).
3. Substitua $A$1 na fórmula a seguir pelo local do início da linha de cabeçalho e copie em todas as colunas e linhas das quais deseja gerar: =IF(LEN(OFFSET(Input!$A$1,ROW()-1,COLUMN()-1))>0,OFFSET(Input!$A$1,ROW()-1,COLUMN()-1),"") _{(a instrução IF(LEN(...)>0,...,"") é necessária porque o Excel também útil determina os tipos de dados para você e usa 0 para células vazias, e os -1 termos vêm de compensação versus semântica ordinal)}
4. Aplique a formatação condicional à folha Output .

Isso copia a planilha de dados Input para Output planilha sem nenhuma referência de célula que o Excel faria com o auto-butcher, para que você possa definir com confiança sua formatação condicional na folha Output .

Você só precisa estender manualmente o intervalo ao qual a fórmula é aplicada na folha Output , à medida que o tamanho das alterações dos dados de Input .

_{Eu sugiro que você evite mesclar quaisquer células.}

P.S. Essa pergunta foi feita várias vezes (também se aplica no Planilhas Google, que é um clone do Excel), portanto você pode encontrar uma solução melhor para o seu caso de uso:

1. Fragmentação de formatação condicional do Excel
2. (este aqui) Como faço para manter as fórmulas e intervalos de Formatação condicional mudando automaticamente?
3. Preserve a formatação condicional em uma pasta de recorte
4. Excel: como aplicar permanentemente a formatação condicional a toda a planilha?
5. Por que o Excel está massacrando minha formatação condicional?