Pular para o conteúdo principal

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!

Comentários

Postagens mais visitadas deste blog

Dr. Planilha: Como Remover Linhas de Grade no #Excel #dica

As linhas de grade do Excel não aparecerão em relatórios impressos, mas pode ser que você também deseje que elas fiquem ocultas quando estiver trabalhando com a planilha por uma questão de estética ou Antes de mais nada é importante ainda que se diga que "linha de grade" não é a mesma coisa que "borda".

Dr. Planilha: Como Fazer Cálculo do IMC no #Excel

Antes de mais nada, vamos entender melhor o que é IMC, emprestando a definição da wikipédia: O índice de massa corporal (IMC) é uma medida internacional usada para calcular se uma pessoa está no peso ideal. Ele foi desenvolvido pelo polímata Lambert Quételet no fim do século XIX. Trata-se de um método fácil e rápido para a avaliação do nível de gordura de cada pessoa, ou seja, é um preditor internacional de obesidade adotado pela Organização Mundial da Saúde (OMS). A fórmula para calcular o IMC é bastante simples, vamos precisar apenas de duas informações, seu Peso em quilogramas e sua Altura em metros. O cálculo consiste em dividir seu peso pela sua altura ao quadrado. Vejamos como ficaria: Ora, isso parece bastante fácil de se fazer no Excel, e de fato é. Vamos à prática! Para começar, faça uma planilha igual à do exemplo abaixo. Agora preencheremos a fórmula para o cálculo na célula B3, ela deve ficar da seguinte forma: =B1/B2^2 Para entender entender melhor a fórm

Dr. Planilha: Como tirar pontos e traços do CPF no #Excel

Um leitor do blog postou uma dúvida recentemente sobre formatação de números de CPF. Na verdade, ele desejava remover todos os caracteres diferentes de números, no caso pontos e hífen, mantendo apenas os números. Segue a pergunta: "A minha planilha tem uma coluna de números de CPF. Gostaria de tirar os pontos e o traço de todos os números da coluna. Como faço isso?" Costumeiramente eu solucionaria essa necessidade usando uma fórmula para substituir os caracteres "." e "-" por <vazio>, o que resulta em uma sequência de apenas números. A seguir descrevo como ficaria essa fórmula.