Este artigo tem a finalidade de demostrar o poder das fórmulas matriciais para resolver problemas não tão fáceis de resolver com fórmulas convencionais.
Um usuário postou a seguinte questão em um fórum que modero:
Suponha uma planilha com 20 posições em cada linha. Em cada uma destas posições encontra-se um número, em ordem crescente, cujo valor vai de 1 até 50, que não se repetem.
Preciso identificar o acontecimento de sequências entre os números, bem como o tamanho destas sequências.
Por exemplo: se acontecer de a sequência de 20 números ser 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 15, 19, 20, 26, 31, 32, 35, 40, 41 e 45, preciso de uma fórmula que me diga:
– quantas vezes ocorreu uma sequência de 2 números (no exemplo – 12 vezes);
– quantas vezes ocorreu uma sequência de 3 números (no exemplo – 8 vezes);
– quantas vezes ocorreu uma sequência de 4 números (no exemplo – 7 vezes);
– …
– quantas vezes ocorreu uma sequência de 20 números (no exemplo – 0 vezes).
Vamos a solução deste problema!
Veja a imagem abaixo:
Na minha avaliação não há muita complexidade em compreender esta fórmula se você já conhece um pouco de fórmulas matriciais. Vamos tentar entender parte a parte usando como referência a fórmula aplicada na célula C5:
1) A ideia é identificar se na posição cruzada há a sequência desejada, e trazer 1 se houver e 0 se não houver, ou seja, na célula C5 desejo conferir o intervalo C4:D4 é uma sequência de 2 números consecutivos. Para isso usei a função CORRESP, sendo que a aplicação matricial se dá no 1º argumento, onde geramos o vetor com a sequência que desejamos buscar através da técnica:
“VALOR INICIAL DA SEQUÊNCIA (C4)” + LIN(INDIRETO(“1:”& QUANTIDADE DE NÚMEROS SEQUÊNCIAIS(B5) ) – 1
A combinação da função LIN e INDIRETO gerará uma sequência númerica base 1, mas neste caso subtraímos 1, para que vire base 0. Veja na imagem a avaliação da fórmula passo a passo:
2) Observe que agora temos um vetor com o tamanho e os elementos desejados da sequência númerica, e podemos buscar estes valores, porém nosso intervalo de busca deve ser limitado ao tamanho da sequência, que neste caso é 2. Para isso usamos a poderosa função DESLOC que nos gerará este intervalo de duas posições que é C4:D4. Basta aplicar os valores corretamente a cada argumento da função. Observe:
Vamos avaliar e ver o resultado:
Na imagem acima, o resultado avaliado {1\2} representa os valores correspondentes ao intervalo C4:D4, sendo que na representação matricial, o dois pontos representa linha e a barra invertida representa coluna.
3) Agora temos a função CORRESP com os elementos que devem ser buscados e temos a matriz de busca do tamanha dessa sequência. A função CORRESP retorna a posição do número caso encontre, e caso não encontre retorna um erro, e então usei a função SEERRO para substituir erros por nada, e em seguida a função ÉNUM para trazer VERDADEIRO para os valores números das posições encontradas ou FALSO para as posições vazias retornadas. Se todas as posições foram encontradas é esperado que somente haja VERDADEIRO. Então entra a dupla negação em ação (–), que se responsabilizará por converter VERDADEIRO em 1 e FALSO em 0. Tendo o vetor com valores exclusivamente 0 ou 1, vamos somar com a função SOMA e comparar se temos o resultado é igual ao número de sequências desejado, que neste caso é 2. Isso retornará VERDADEIRO ou FALSO, e novamente aplicamos a dupla negação para converter em 0 ou 1. Vamos ver toda essa teoria em ação passo a passo:
4) Com a fórmula montada, basta replicarmos para todas demais linhas e colunas, e por fim somar as ocorrências de cada linha para termos o total.
Apenas para melhor compreensão, vamos avaliar passo a passo a célula K6, onde buscamos a sequência de 3 números e pelo resultado sabemos que não encontrou:
Espero que tenham gostado e para melhor estudo, segue abaixo o link para baixas esta planilha.
Abraços!
Link para Download: Modelo ExcelManíacos – Calcular quantidade determinadas sequências
Bem interessante essa fórmula. E se eu quisesse saber na ” coluna” a quantidade de seguencia do número 1( por exemplo) vamos supor que ele tenha uma seguencia de três seguidos , depois falhou dois , em seguida temos mais cinco seguidas. O que quero dizer é temos duas seguencias, uma seguencia de 3 e uma seguencia de 5. Isso é possível?
Obrigado Rafael
CurtirCurtir
Bom o artigo. Gostaria de saber se é possível fazer por colunas. Por exemplo tenho o número um três vezes, depois falhou duas vezes, depois tenho mais quatro vezes, mais uma falha, depois mais duas vezes, ou seja tenho 3 seguencias uma de 3 outra de 4 e outra de 2. É possível?
Obrigado Rafael
CurtirCurtir
Olá wpramosinfo! Tudo é possível com Excel. Só estudar os conceitos e as fórmulas, que é possível adaptar ao seu caso. Abraços!
CurtirCurtir
Bom dia Rafael, preciso lhe mandar um documento por email para sua analise.Isso e possivel?
Grato Jean
CurtirCurtir
Obrigado senhor Rafael, ajudou muito.
CurtirCurtir