Neste vídeo, veremos como destacar pesquisas de correspondência aproximada com formatação condicional.
Aqui temos uma tabela de consulta simples que mostra os custos de material para várias alturas e larguras. A fórmula em K8 usa as funções INDEX e MATCH para recuperar o custo correto com base nos valores de largura e altura inseridos em K6 e K7.
Observe que a pesquisa é baseada em uma correspondência aproximada. Como os valores estão em ordem crescente, MATCH verifica os valores até que um valor maior seja alcançado e, em seguida, recua e retorna à posição anterior.
Vamos construir uma regra de formatação condicional para destacar a linha e coluna correspondentes.
Como sempre, com uma formatação condicional mais complicada, recomendo que você trabalhe primeiro com fórmulas fictícias e, em seguida, transfira uma fórmula de trabalho diretamente para a regra de formatação condicional. Dessa forma, você pode usar todas as ferramentas do Excel quando estiver depurando a fórmula, o que economizará muito tempo.
Vou configurar a fórmula para largura primeiro. Precisamos retornar TRUE para cada célula na linha 7, onde a largura correspondente é 200.
Isso significa que começamos nossa fórmula com $ B5 = e precisamos bloquear a coluna.
= $ B5 =
Agora, não podemos procurar 275 na coluna de larguras, porque não está lá. Em vez disso, precisamos de uma correspondência aproximada que encontre 200, assim como nossa fórmula de pesquisa.
A maneira mais fácil de fazer isso é usar a função LOOKUP. LOOKUP faz uma correspondência aproximada automaticamente e, em vez de retornar uma posição como MATCH, LOOKUP retorna o valor de correspondência real. Então, podemos escrever:
$ B5 = PROCURA ($ K $ 6, $ B $ 6: $ B $ 12)
Com nossa largura de entrada para valor de pesquisa e todas as larguras na tabela para vetor de resultado.
Se eu usar F9, você verá o valor que LOOKUP retorna.
Agora, quando eu insiro a fórmula na tabela, obtemos TRUE para cada célula na linha de largura 200.
Agora precisamos estender a fórmula para corresponder à coluna de altura. Para fazer isso, adicionarei a função OR e, em seguida, uma segunda fórmula para corresponder à altura.
Começaremos a fórmula da mesma maneira, mas desta vez precisamos bloquear a linha:
= B $ 5
Em seguida, usamos a função LOOKUP novamente com altura para o valor de pesquisa e todas as alturas da tabela como vetor de resultado.
= OU ($ B5 = PROCURA ($ K $ 6, $ B $ 6: $ B $ 12), B $ 5 = PROCURA ($ K $ 7, $ C $ 5: $ H $ 5))
Quando copio a fórmula na tabela, obtemos TRUE para cada célula na coluna correspondida e cada célula na linha correspondida - exatamente o que precisamos para a formatação condicional.
Posso apenas copiar a fórmula exatamente na célula superior esquerda e criar uma nova regra.
Agora, se eu alterar a largura ou a altura, o realce funcionará conforme o esperado.
Finalmente, se você deseja apenas destacar o próprio valor de pesquisa, é uma mudança simples. Basta editar a fórmula e substituir a função OR pela função AND.
= AND ($ B5 = LOOKUP ($ K $ 6, $ B $ 6: $ B $ 12), B $ 5 = LOOKUP ($ K $ 7, $ C $ 5: $ H $ 5))
Curso
Formatação condicionalAtalhos relacionados
Insira os mesmos dados em várias células Ctrl + Enter ⌃ + Return Exibir a caixa de diálogo Colar especial Ctrl + Alt + V ⌘ + ⌃ + V Alternar referências absolutas e relativas F4 ⌘ + T
