Formata condicionalmente as células com base na correspondência em outra planilha

Question

Eu tenho uma planilha do Excel com 2 planilhas. O primeiro é 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 subseqüente 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 na "Planilha2" de forma que qualquer valor que não corresponda a um valor na primeira coluna da "Planilha1" esteja marcado com um segundo plano vermelho; aqueles que são marcados com um fundo verde. Então, todas as células neste exemplo começando em B2 seriam verdes, exceto o valor "SHOES". O valor abaixo disso não tem nada inserido, portanto, não seria formatado.

A regra de formatação para verde que eu tentei é:

=AND(NOT(ISBLANK(B2)), COUNTIF(Sheet1!$A2:$A1000,B2)>0)

Para vermelho, aproximadamente o mesmo:

=AND(NOT(ISBLANK(B2)), COUNTIF(Sheet1!$A2:$A1000,B2)<1)

Ambas as regras são "aplicadas a" intervalo um tanto arbitrário (eu gostaria que fosse aplicado à folha inteira, menos à linha / coluna mais à esquerda e à 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

Este semi-funciona, mas os resultados são imprevisíveis. Alguns valores são destacados como eu esperava, mas apenas para algumas linhas, e outros não. Provavelmente minhas faixas estão fora de sintonia de alguma forma, mas eu não uso o Excel tanto quanto uma vez eu fiz. Alguém pode ajudar?

Obrigado!

    

Answer 1

Como afirma Doktoro Reichard, você deseja usar Formatação condicional para fazer isso. Neste caso específico, você quer ter três regras:

1. Se a célula estiver em branco, não altere o plano de fundo
2. Se a célula tiver uma correspondência, torne o plano de fundo verde
3. Se a célula não tiver correspondência, torne o plano de fundo vermelho

_{Desculpas, meu Excel é japonês. É um dia multilingue.}

Para fazer isso, precisamos de três fórmulas que retornarão TRUE ou FALSE para cada uma dessas condições. Assumirei que seus dados são os seguintes:

Folha1

Folha2

Regra # 1

A fórmula a seguir retornará se a célula está em branco ou não. Eu selecionei

=ISBLANK(B2)

NotequeselecioneiascélulasB2:D5comreferênciasrelativas.Issoaplicaráamesmafórmulaalterandoareferênciadacélulaparacadacélulanointervaloselecionado.Definaacordoplanodefundoparabranco(ouqualquerquesejasuapreferência)quandoessacondiçãoforverdadeira.

Regranº2

Afórmulaaseguirretornarásehouverounãoumacorrespondênciaperfeitanalistanaplanilha1:

=NOT(ISERROR(MATCH(B2,Sheet1!$A:$A,0)))

Regra # 3

A fórmula a seguir retornará se houver ou não correspondência perfeita na lista na planilha 1:

=ISERROR(MATCH(B2,Sheet1!$A:$A,0))

Encomendar

A regra no topo será executada primeiro. Então, como todas as células em branco serão não correspondentes, você precisará colocar a regra em branco primeiro. A ordem de # 2 e # 3 não importa (elas nunca se sobrepõem).

    

Answer 2

Parece que você precisa da formatação condicional da célula.

Aqui está um link descrevendo o que é isso.

Minhas fotos são de uma versão em português do Excel 2003, mas a funcionalidade também deve estar lá no Office 2010. Na verdade, o Office 2010 permite a formatação condicional entre planilhas, algo que o 2003 não pode e, como tal, estou fazendo tudo em uma folha.

Primeiro, a tabela:

Oquevocêquerfazerécompararumelementodasegundatabeladetodososelementosdoprimeiro.Então,vocêprecisaescreverumafunçãocomoesta:

=OR(EXACT($B$6;E3);EXACT($B$5;E3);EXACT($B$4;E3);EXACT($B$3;E3))

OqueEXACT()fazécompararduascadeiasdetexto.OqueOR()fazésetornarTruesealgumacondiçãológicadentrodeTrue.

Ditoisso,vocêselecionatodasascélulasdasegundatabelae,emseguida,pressionaFormataçãocondicional,comomostrado.

Sabendo sobre o Office 2010 e sua nova interface da Faixa de Opções, você deve procurar no painel Formato para isso. Se bem me lembro, é um ícone.

Ao clicar nesse ícone, você verá uma janela semelhante a esta:

Lá,vocêprecisaprimeiroselecionarumafórmulaecolarafórmulaquemencioneiantes.Paratornartodasascélulasqueverificamacondiçãoemverde,bastaalteraroformato.Parafazeraformataçãoemvermelho,useapenasNOT(OR(...));issoretornaráoinversodacondiçãoquevocêdefiniu.

Parasecertificardequenãoformataascélulasquenãotêmnada,façaumaterceiracondiçãonaqualafórmulaéISBLANK(E3)(sendoE3nocantosuperioresquerdo).

Algoqueverifiqueidepoisdeescreverorascunhoinicialéprecedência.Pelomenosnaminhaversão,aCondição1éverificadaantesdaCondição2eassimpordiante.Então,vocêdeveterascondiçõesordenadasdemaneiraqueelasnãointerfiramemsimesmas.Então:

1stcondition-=ISBLANK(E3)2ndcondition-=OR(...)3rdcondition-=NOT(OR(...))

Portanto,vocêdeveterumajanelacomoesta:

Tente adaptar isso à sua situação. Se bem me lembro, não é tão diferente do que estou mostrando. O resultado deve ser algo assim: