Formatação condicional do Excel 2010 em várias tabelas

Question

Eu tenho duas tabelas com valores nas colunas A & B. Para ambas as tabelas, A é um valor numérico e B é um valor de texto. Exemplo abaixo. Eu quero destacar células na tabela 2-coluna B para o qual a célula na tabela 2-coluna B corresponde a qualquer célula na tabela 1-coluna B ea célula tabela2-coluna A é maior que a célula na tabela 1 - coluna A para a correspondência correspondente. (Assim, abaixo da Tabela 2, as células B2, B4 e B5 seriam destacadas).

Isso é possível?

    

Answer 1

Você pode usar uma fórmula na formatação condicional para realçar o valor correspondente na coluna B table2 e maior valor na mesma linha da coluna A:

• Selecione seus dados na coluna table2 B
• Formatação condicional
• Nova regra
• Use uma fórmula para determinar quais células devem ser formatadas
• Formate os valores em que essa fórmula é True
• escreva =COUNTIFS(Sheet1!$B$1:$B$3,B1,Sheet1!$A$1:$A$3,"<"&A1)>0
• Formatar como você gosta

Nesta fórmula:
=COUNTIFS(Sheet1!$B$1:$B$3,B1,Sheet1!$A$1:$A$3,"<"&A1)>0
Folha1! $ B $ 1: $ B $ 3 é a coluna B na Tabela 1 alterá-la para corresponder às suas referências
B1 é a primeira célula de seus dados na Tabela 2, coluna B, se seus dados começarem em B2, você terá que alterá-los para B2

    

Answer 2

Sim, é possível usar a formatação condicional.

Você mostrou as Tabelas 1 e 2 acima como se estivessem na mesma planilha, mas ambas as tabelas parecem começar na Coluna A, o que não é possível a menos que estejam em planilhas separadas. Mostrarei soluções para os dois casos abaixo.

Começandocomambasastabelasnamesmafolha,selecioneE1:E5eescolha"Formatação condicional ..." no menu Formatar. Clique no sinal de mais para criar uma nova regra e selecione "Clássico" na lista suspensa Estilo. Na outra lista suspensa, escolha "Usar uma fórmula ..." e digite ou cole isso na caixa da fórmula:

=AND(COUNTIF(B$1:B$3,E1)>0,D1>IFERROR(OFFSET(B$1,MATCH(E1,B$1:B$3,0)-1,-1),0))

(Observe que você também pode colar essa fórmula em uma célula da planilha para testá-la. Ela deve retornar FALSE, TRUE, FALSE, TRUE, FALSE quando colada nas linhas 1 a 5, respectivamente).

Veja como isso funciona: COUNTIF () ... > 0 retorna TRUE se o valor na coluna E ocorrer em B1: B3. MATCH () localiza a posição do valor na Coluna E no intervalo B1: B3, e OFFFSET () encontra o valor correspondente na Coluna A, e IFERROR () elimina os valores # N / A. Se D1 for maior que o número da Coluna A, essa parte da fórmula retornará TRUE e o AND () retornará TRUE se ambas as partes forem verdadeiras: O valor na Coluna E deve estar em B1: B3 AND O valor na coluna D tem que ser maior que o valor na coluna A correspondente ao valor na coluna B que é o mesmo que o valor na coluna E.

Aqui, isso resulta no destaque de E2 e E4.

Agora, se a Tabela 2 estiver em uma planilha diferente, temos que usar a função INDIRECT () juntamente com as referências de Planilha / célula entre aspas, assim:

=AND(COUNTIF(INDIRECT("Sheet1!B$1:B$3"),B1)>0,A1>IFERROR(OFFSET(INDIRECT("Sheet1!B$1"),MATCH(B1,INDIRECT("Sheet1!B$1:B$3"),0)-1,-1),0))

Editar: Talvez isso dependa da versão do Excel (estou usando o Excel 2011 para Mac), mas acontece que as referências INDIRECT () não são necessárias:

=AND(COUNTIF(Sheet22!B$1:B$3,B1)>0,A1>IFERROR(OFFSET(Sheet22!B$1,MATCH(B1,Sheet22!B$1:B$3,0)-1,-1),0))

Isso fornece a formatação correta para a Tabela 2, localizada nas Colunas A e amp; B em outra folha, conforme mostrado abaixo: