quarta-feira, 30 de janeiro de 2013

Dr. Planilha: Como testar números em sequência de texto

Um visitante anônimo deixou uma dúvida desafiadora há algumas semanas no blog. Justamente por ser desafiadora, resolvi escrever um artigo para postar a solução, ao invés de tentar responder a dúvida apenas. Também porque a solução, por ser bastante elaborada, não poderia ser explicada como eu gostaria na forma de resposta de comentário.

Vamos ao problema:
"TENHO UMA PLANILHA COM OS SEGUINTES DADOS
A1=1 2 3 10
A2=4 6 9 20
A3=2 3 4 19
A4=1 10 11 13
A5=1 4 10 15
B1=1 8 10
PRECISO DE UMA FÓRMULA QUE ME RESPONDA QUANTAS CELULAS DA
COLUNA A1:A5 POSSUEM 2 DADOS DE B1,NO CASO ACIMA SERIAM 3 QUE
CONTEM OS NUMEROS 1 E 10. JA TENTEI COM O CONT.SE ,MAS NÃO DEU CERTO"
O desafio está no fato de que ele precisa comparar individualmente os números das sequências de texto da coluna A e não há fórmula para resolver isso de forma direta. Esta solução poderia ser construída por meio de códigos de macro, mas meu objetivo principal sempre será o de trazer para a compreensão da grande maioria, que só sabe que existe macro porque alguém um dia lhe contou.

Na grande maioria dos artigos que são publicados neste blog as fórmulas são explicadas de forma esmiuçada, no entanto, os dois passos para resolver o problema são bastante elaborados, com fórmulas encadeadas e que não são tão comuns no dia-a-dia. Por esse motivo, passarei a seguir uma visão macro das duas etapas para resolver o problema e disponibilizarei a planilha de exemplo para que todos os visitantes possam avaliar mais de perto a solução e adequar conforme sua necessidade.

PASSO 1: Separar os números da sequência em colunas individuais para que possam ser testados individualmente. 
Essa foi uma opção pessoal minha para diminuir a complexidade da solução, não sei se a planilha usada pelo amigo possibilitará isso. Antes de mais nada, devemos criar uma coluna para cada um dos 4 possíveis números que estarão contidos em nosso conjunto. É importante observar que a complexidade aumenta em muito em função de não termos uma quantidade de dígitos constante, visto que temos números com 1 e 2 dígitos. A fórmula inserida em cada uma das colunas fará basicamente o seguinte: identificar a posição dos espaços em branco para saber em que posição estão os números e assim, poder extraí-los individualmente. Nesta etapa foram usadas basicamente as funções PROCURAR() e EXT.TEXTO(), a primeira com o objetivo de localizar os espaços em branco para identificar os limites entre os números e a segunda com o objetivo de extrair o número propriamente dito.

PASSO 2: Testar individualmente os valores extraídos nas novas colunas
O segundo passo está na fórmula que efetivamente testará todos os números extraídos em cada uma das colunas usando uma combinação das funções SE(), ÉERROS() e PROCURAR(). A função PROCURAR() serve para identificar se o número extraído está na sequência "1 8 10", a questão é que, caso não encontre, ela retorna um erro. É aí que entram as outras duas fórmulas, usando o SE() e o ÉERROS() nós testaremos aqueles casos onde o PROCURAR() gerou erro, somando zero, e para aqueles onde ele encontrou um número válido, somando um. Resumindo, encontrou, soma 1, não encontrou, soma 0.

IMPORTANTE: 
Essa solução serve apenas para origem com 4 números. No caso de maior quantidade de números, seria necessário criar uma coluna para cada novo número, repetindo as fórmulas das últimas colunas.

Segue abaixo o link para download da planilha no formato Office 2003/2007/2010:

Como testar números em sequência de texto.xls
Como testar números em sequência de texto.xlsx

Um grande abraço do Dr. Planilha!

Related Posts Plugin for WordPress, Blogger...