Eu tenho uma planilha Excel com 2 planilhas. A primeira é apenas uma linha de cabeçalho e uma única coluna de nomes de itens. A segunda é uma lista de grupos de itens, com uma linha de cabeçalho e um título na coluna mais à esquerda, com cada linha subsequente sendo um item ou outro da outra planilha:
Sheet1: Sheet2:
+-------+--+--+ +-------+-------+-------+-------+-------+
| Item | | | | Group | Item1 | Item2 | Item3 | ...
+-------+--+--+ +-------+-------+-------+-------+-------+
| Shirt | | | | A | Shirt | Hat | Tie |
+-------+--+--+ +-------+-------+-------+-------+-------+
| Hat | | | | B | Socks | Shirt | SHOES |
+-------+--+--+ +-------+-------+-------+-------+-------+
| Socks | | | | C | Hat | Socks | |
+-------+--+--+ +-------+-------+-------+-------+-------+
| Tie | | | | D | Tie | Tie | Socks |
+-------+--+--+ +-------+-------+-------+-------+-------+
| ... | | |
+-------+--+--+
Gostaria de formatar condicionalmente todas as células em "Planilha2", de modo que qualquer valor que não corresponda a um valor na primeira coluna de "Planilha1" seja marcado com um fundo vermelho; aqueles que o fazem são marcados com um fundo verde. Portanto, todas as células neste exemplo começando em B2 seriam verdes, exceto o valor "SHOES". O valor abaixo não contém nada inserido, portanto não seria formatado.
A regra de formatação para verde que tentei é:
=AND(NOT(ISBLANK(B2)), COUNTIF(Sheet1!$A2:$A1000,B2)>0)
Para vermelho, quase o mesmo:
=AND(NOT(ISBLANK(B2)), COUNTIF(Sheet1!$A2:$A1000,B2)<1)
Ambas as regras são "aplicadas" a um intervalo um tanto arbitrário (gostaria que se aplicasse a toda a planilha, menos a linha/coluna superior e mais à esquerda):
=$C$3:$E$10,$C$36:$Q$50,$E$11,$C$11,$C$2,$E$2:$Q$2,$C$12:$E$35,$F$3:$Q$35
Isso semi-funciona, mas os resultados são imprevisíveis. Alguns valores são destacados conforme esperado, mas apenas para algumas linhas, e outros não. Provavelmente meus intervalos estão fora de sintonia de alguma forma, mas eu não uso o Excel tanto quanto antes. Alguém pode me ajudar?
Obrigado!
Responder1
Como afirma Doktoro Reichard, você deseja usarFormatação condicionalpara fazer isso. Neste caso específico você deseja ter três regras:
- Se a célula estiver em branco, não altere o plano de fundo
- Se a célula corresponder, deixe o fundo verde
- Se a célula não corresponder, deixe o fundo vermelho
Desculpas, meu Excel é japonês. É dia multilíngue.
Para isso, precisamos de 3 fórmulas que retornarão TRUEou FALSEpara cada uma dessas condições. Presumirei que seus dados sejam os seguintes:
Folha1
Planilha2
Regra 1
A fórmula a seguir retornará se a célula está em branco ou não. eu selecionei
=ISBLANK(B2)
Observe que selecionei células B2:D5com referências relativas. Isso aplicará a mesma fórmula, alterando a referência de célula para cada célula no intervalo selecionado. Defina a cor de fundo como branco (ou qualquer que seja sua preferência) quando esta condição for verdadeira.
Regra nº 2
A fórmula a seguir retornará se há ou não uma correspondência perfeita na lista da planilha 1:
=NOT(ISERROR(MATCH(B2,Sheet1!$A:$A,0)))
Regra nº 3
A fórmula a seguir retornará se não há ou não correspondência perfeita na lista da planilha 1:
=ISERROR(MATCH(B2,Sheet1!$A:$A,0))
Ordem
A regra no topo será executada primeiro. Portanto, como todas as células em branco não serão correspondentes, você precisa colocar a regra em branco primeiro. A ordem dos números 2 e 3 não importa (eles nunca se sobreporão).
Responder2
Parece que o que você precisa é de formatação condicional de célula.
Aqui está umlinkdescrevendo o que é isso.
As minhas fotos são de uma versão portuguesa do Excel 2003, mas a funcionalidade também deve estar presente no Office 2010. Na verdade, o Office 2010 permite que exista formatação condicional entre folhas, algo que 2003 não pode e como tal, estou a fazer tudo em uma folha.
Primeiro de tudo, a tabela:
O que você quer fazer é comparar um elemento da 2ª tabela com todos os elementos da 1ª. Então, você precisa escrever uma função como esta:
=OR(EXACT($B$6;E3);EXACT($B$5;E3);EXACT($B$4;E3);EXACT($B$3;E3))
O que EXACT()faz é comparar duas strings de texto. O OR()que acontece é Truese alguma condição lógica dentro for True.
Dito isto, você seleciona todas as células da 2ª tabela e pressiona Formatação Condicional, conforme mostrado.
Conhecendo o Office 2010 e sua nova interface Ribbon, você deve procurar isso no painel Formato. Se bem me lembro, é um ícone.
Clicar nesse ícone abrirá uma janela semelhante a esta:
Lá, primeiro você precisa selecionar que deseja uma fórmula e depois colar a fórmula que mencionei antes. Para deixar todas as células que verificam a condição verdes, basta alterar o formato. Para fazer a formatação em vermelho, basta usar NOT(OR(...)); isso retornará o inverso da condição que você definiu.
Para ter certeza de que não formata as células que não contêm nada, faça uma terceira condição em que a fórmula seja ISBLANK(E3) (sendo E3 o canto superior esquerdo).
Algo que verifiquei depois de escrever o rascunho inicial é a precedência. Pelo menos na minha versão, a Condição 1 é verificada antes da Condição 2 e assim por diante. Portanto, você deve ordenar as condições de forma que não interfiram nelas mesmas. Então:
1st condition - =ISBLANK(E3)
2nd condition - =OR(...)
3rd condition - =NOT(OR(...))
Portanto, você deverá ter uma janela como esta:
Tente adaptar isso à sua situação. Se bem me lembro, não é muito diferente do que estou mostrando. O resultado deve ser algo assim:
