Como copiar fórmulas condicionais em seções de uma planilha?

Question

Tenho dados dispostos em minitabelas em uma planilha única. Ver foto

Preciso formatar condicionalmente toda a linha (dentro da minitabela) com base em um valor de célula dentro da linha.

Por exemplo, para a tabela U, eu destacaria A4:E4 com base no valor de B.

O problema é que, quando copio a formatação condicional para a tabela T, ela ainda faz referência à coluna B, e não à coluna G, como desejo. Eu tenho que fazer isso muitas vezes (mais de 50), então consertar manualmente levaria muito tempo (7 formatos condicionais por minitabela, mais de 50 tabelas). Alguma ideia?

Answer 1

Supondo que todas as suas minitabelas tenham a mesma largura e não haja espaços (ou apenas um espaço de largura fixa) entre elas, você pode usar uma única fórmula de formato condicional como esta, aplicada às células $A$4:$E$4:

=OFFSET($A4, 0, 5 * QUOTIENT(COLUMN(A4)-1, 5) + 1) = "TWL W/G"

Você poderá então usar a ferramenta de pintura de formato para copiar o formato condicional no restante da primeira tabela e nas outras minitabelas. Você não deve precisar de mais nenhuma edição.

Isso funciona calculando qual minitabela está sendo formatada (da coluna da célula que está sendo avaliada) e, portanto, com qual célula a string de teste deve ser comparada.

$A4é o ponto de partida para o deslocamento. É uma referência absoluta à coluna A, mas possui uma referência relativa de linha, portanto, quando você copiar o formato para outras linhas, elas serão formatadas separadamente.

A QUOTIENT(COLUMN(A4)-1, 5)parte calcula em qual tabela está a célula que está sendo avaliada, assumindo que cada tabela tenha 5 colunas de largura. A4é uma referência totalmente relativa, portanto ela é substituída para cada célula para a qual você copia o formato.

A 5 * QUOTIENT() + 1parte seleciona a 2ª coluna da tabela correta (assumindo que todas tenham 5 colunas de largura). Altere os 5 se suas tabelas usarem um número diferente de colunas ou se + 1precisar testar uma coluna diferente na tabela.

Answer 2

Siga esses passos:

Primeira abordagem:

1. Selecione o intervalo de dados necessário na Tabela U.
2. Clique em Formato Condicional, Nova Regra, Usar Fórmula para determinar qual célula formatar.
3. Em Editar a descrição da regra, escreva esta fórmula=B4 = B4.
4. Aplique o formato desejado e finalize com OK.
5. Selecione o intervalo formatado condicionalmente na Tabela U e copie.
6. Selecione o intervalo alvo na Tabela T e clique em Colar especial.
7. Na categoria Opção de colagem, clique em Formatos e depois em OK.

Segunda abordagem:

1. Copie uma célula da Tabela U para uma célula em branco.
2. Selecione a célula copiada, clique em Formato condicional, opção Gerenciar regras.
3. Em Selecionar Mostrar regras de formatação para, selecione Esta planilha.
4. Para Regra na Tabela U, altere o intervalo de dados Aplica-se à correspondência.
5. Selecione o intervalo de dados na Tabela T para aplicar o formato.
6. Clique OK.

Observação:

Depois de copiar o formato condicional, marque Gerenciar regra para tabela T, agora você descobrirá que a fórmula é =G4 = G4. Aqui, o Excel justifica o 'Formato de cópia' e também ajusta o intervalo de dados do destino.

Mas lembre-se, ao usar isso, o Excel também formatará as linhas que não correspondem ao valor B4.