[FÓRMULAS MATRICIAIS] CALCULAR QUANTIDADE DE SEQUÊNCIAS NUMÉRICAS CONSECUTIVAS

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:

ExcelManiacos - Determinar Sequências 01

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:

ExcelManiacos - Determinar Sequências 02

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:

ExcelManiacos - Determinar Sequências 03

Vamos avaliar e ver o resultado:

ExcelManiacos - Determinar Sequências 04

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:

ExcelManiacos - Determinar Sequências 05

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:

ExcelManiacos - Determinar Sequências 06

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

 

 

Anúncios

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair /  Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair /  Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair /  Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair /  Alterar )

Conectando a %s