Uma explicação resumida:
1º) A primeira tabela auxiliar "folga" serve para trazer o número da coluna onde aparece "F" (Ex: A = 1, B = 2 , C = 3, D = 4, ...). Isso é feito com uma fórmula matricial relativamente simples.
2º) A segunda tabela "Tamanho do intervalo" vai determinar a quantidade de células entre os "F". Fórmula muito simples, que dispensa explicação.
3º) A terceira tabela "Endereço" me gera o intervalo referente as os espaçamentos entre "F" que extrapolarem o parâmetro definido na célula AO4.
Veja:
=SE(E(AS9>$AO$4;AS9<>"");ENDEREÇO(LIN();AK9)&":"&ENDEREÇO(LIN();AL9);"") onde:
E(AS9>$AO$4;AS9<>"") é o teste lógico para saber se o tamanho é maior que o paramêtro (AO4) e se existe o intervalo entre "F" (AS9<>"")
ENDEREÇO(LIN();AK9) gera o endereço da primeira célula com F, onde lin() traz a linha atual e AK é o índice da coluna. Com estes dois argumentos a função ENDEREÇO retorna o endereço da célula.
&":"& é para concatenar os dois pontos com o endereço de célula do próximo "F" e então gerar um endereço de intervalo completo (ex: $T$9:$Y:9).
4º) Selecionei a célula E9 para criar o nome “format_cond” no gerenciador de nomes. É importante selecionar a célula E9, pois a fórmula referente ao nome vai ser colocada de forma relativa e não absoluta. A fórmula abaixo está fazendo referência a célula E9, e como está relativa, o resultado do nome definido Format_cond vai ser diferente conforme a célula a ser aplicada.
=OU(NÃO(ÉERROS(INDIRETO(Plan1!$BA9) Plan1!E9));NÃO(ÉERROS(INDIRETO(Plan1!$BB9) Plan1!E9));NÃO(ÉERROS(INDIRETO(Plan1!$BC9) Plan1!E9));NÃO(ÉERROS(INDIRETO(Plan1!$BD9) Plan1!E9));NÃO(ÉERROS(INDIRETO(Plan1!$BE9) Plan1!E9));NÃO(ÉERROS(INDIRETO(Plan1!$BF9) Plan1!E9));NÃO(ÉERROS(INDIRETO(Plan1!$BG9) Plan1!E9)))
É preciso entender somente um parte da fórmula, pois o resto é repetição mudando apenas o intervalo de análise. Toda a fórmula está envolvida com a função OU, ou seja, se um dos 7 testes der VERDADEIRO, o resultado será verdadeiro. Veja:
NÃO(ÉERROS(INDIRETO(Plan1!$BA9) Plan1!E9)) => o que estou fazendo aqui é trabalhar com um recurso de intersecção do Excel, que é realizado pelo espaçamento entre dois intervalos. A função INDIRETO tem a responsabilidade de transformar o texto com o intervalo que geramos lá na tabela 3 em um argumento de intervalo real do Excel, pois se tiver somente o endereço da célula o Excel interpretará o valor da célula como um texto somente. O teste lógico que quero fazer é o ÉERROS, que me retornará VERDADEIRO ou FALSO. Vamos supor que na célula Plan1$BA9 tenho o endereço $T$9:$Y$9, assim a fórmula resulta em ÉERROS($T$9:$Y$9 Plan1!E9) que poderíamos ler mais ou menos assim: “se a célula E9 está dentro do intervalo T9:Y9 então retorne FALSO, se não retorne VERDADEIRO.” Na formatação condicional, a formatação é aplicada para VERDADEIRO, e como o resultado do teste lógico para a célula que está dentro é FALSO, eu tenho que colocar a função NÃO para realizar a inversão desse valor para VERDADEIRO, ou seja, transformar FALSO em VERDADEIRO e VERDADEIRO em FALSO.
A necessidade de criar essa fórmula no gerenciador de nomes é porque na fórmula personalizada na formatação condicional não aceita este recurso de intersecção (“espaçamento”), porém se jogar como fórmula um nome criado no gerenciador de nomes ele aceita (Vai entender....)...
5º) A última etapa é selecionar o intervalo E9:AI31 e aplicar a formatação condicional utilizando o nome “Format_Cond” na regra de formatação.
Falei que era meio complexo... nem sei se vai dar para entender. Acho o tópico legal, com tempo vou tentar criar um vídeo no meu blog Excelmaniacos.com para tentar explicar melhor essa fórmula.
Lembrando que este é um método que criei... muito provável que existam muitos outros métodos para fazer isso, talvez até mais simples.
Abraços!
Postado : 06/08/2015 1:18 pm