Como contar números positivos / negativos consecutivos no Excel

Contar números negativos consecutivos em uma planilha Excel pode ser um desafio, mas com este guia você aprenderá uma solução eficaz para fazê-lo. Siga o exemplo prático para contar números negativos consecutivos e otimize o gerenciamento dos seus dados.

Está a trabalhar com uma folha de cálculo Excel que contém números positivos e negativos? Precisa de contar o número de números negativos consecutivos? Contar números negativos numa lista pode parecer simples, mas quando se trata de números negativos consecutivos, a situação torna-se um pouco mais complicada. Neste artigo, mostrar-lhe-ei uma solução eficaz para contar números negativos consecutivos no Excel, ilustrada com um exemplo prático.

Exemplo

Suponhamos que temos uma série de dados relativos ao mercado de ações semanal e somos solicitados a calcular o número de semanas consecutivas em que o mercado sofreu perdas. Os dados estão organizados em duas colunas, com o cabeçalho na linha 1 e os dados a partir da célula A2 até à célula B10.

Para calcular o número de semanas consecutivas com perdas, insira a seguinte fórmula na célula C2 e prima
Ctrl + Shift + Enter para confirmar a fórmula como uma fórmula matricial:

=MAX(FREQUENCY(IF(B2:B10<0,ROW(B2:B10)),IF(B2:B10>=0,ROW(B2:B10))))

O resultado será o número de semanas consecutivas em que o mercado de ações esteve em baixa.

Como Funciona a Fórmula

A fórmula utiliza a função “FREQUENCY” para contar o número de vezes que números negativos consecutivos aparecem. Abaixo, explicarei passo a passo como a fórmula devolve o resultado pretendido.

Passo 1: Encontrar os Números de Linha dos Valores Inferiores a 0

A fórmula “IF(B2:B10<0,ROW(B2:B10))” devolve um array com os números de linha dos valores que são inferiores a 0. Por exemplo, se os números negativos estiverem nas linhas 2, 3 e 4, o array será {2;3;4;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}.

Passo 2: Encontrar os Números de Linha dos Valores Maiores ou Iguais a 0

A fórmula “IF(B2:B10>=0,ROW(B2:B10))” devolve um array com os números de linha dos valores que são maiores ou iguais a 0. Por exemplo, se os números positivos estiverem nas linhas 5, 6 e 7, o array será {FALSE;FALSE;FALSE;FALSE;5;6;7;FALSE;FALSE}.

Passo 3: Calcular o Array de Números Negativos Consecutivos

Utilizando a função “FREQUENCY“, calculamos o array de números negativos consecutivos. A fórmula será semelhante a esta: “=FREQUENCY({2;3;4;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE},{FALSE;FALSE;FALSE;FALSE;5;6;7;FALSE;FALSE})“. O array resultante será {3;0;0;0}.

Passo 4: Encontrar o Máximo do Array

O resultado final da fórmula será o valor máximo presente no array de números negativos consecutivos. Neste caso, o resultado será 3, pois houve 3 semanas consecutivas de perdas no mercado de ações.

Solução VBA: Função Personalizada para Contar Números Negativos

Além da fórmula apresentada anteriormente, é possível utilizar uma solução VBA para calcular números negativos consecutivos no Excel. Abaixo, encontrará uma função personalizada que pode utilizar para obter o mesmo resultado.

Function ContaNumeriNegativi(rng As Range)
  
  Dim r As Range
  Dim c As Long
  Dim m As Long
  
  Application.Volatile
  
  c = 0
  m = 0
  
  On Error Resume Next
  
  For Each r In rng.Cells
    If r.Value < 0 Then
      c = c + 1
      If c > m Then
        m = c
      End If
    Else
      c = 0
    End If
  Next r
  
  ContaNumeriNegativi = m
  
End Function

Para utilizar esta função, insira “=ContaNumeriNegativi(B2:B10)” na célula C2. O resultado será o mesmo obtido utilizando a fórmula anterior.

Contar Números Positivos Consecutivos

De forma semelhante, é possível calcular o número de vezes que números positivos consecutivos aparecem. A fórmula será quase idêntica à utilizada para números negativos, mas com uma ligeira diferença nos sinais. A fórmula será a seguinte:

=MAX(FREQUENCY(IF(B2:B10>0,ROW(B2:B10)),IF(B2:B10<=0,ROW(B2:B10))))

Somar Valores Negativos Consecutivos

No exemplo anterior, calculámos o número de números negativos consecutivos. No entanto, se precisar de somar os valores negativos consecutivos, pode utilizar a seguinte fórmula:

=MIN((COUNTIF(OFFSET(B2:B10,ROW(B2:B10)-ROW(B2),0,C2),"<0")=C2)*SUBTOTAL(9,OFFSET(B2:B10,ROW(B2:B10)-ROW(B2),0,C2)))

Tenha em mente que o exemplo fornecido é apenas para fins demonstrativos e que somar valores percentuais não faz sentido. Contar números positivos ou negativos consecutivos numa folha de cálculo Excel pode parecer uma tarefa complicada, mas utilizando as fórmulas e funções corretas, é possível obter os resultados desejados. A fórmula e a solução VBA apresentadas neste artigo permitir-lhe-ão contar números positivos ou negativos consecutivos de forma eficiente. Espero que estas informações lhe tenham sido úteis e que possam facilitar o seu trabalho com o Excel.

Pubblicato in Excel