Cómo contar números positivos / negativos consecutivos en Excel

Contar números negativos consecutivos en una hoja de cálculo de Excel puede ser un desafío, pero con esta guía aprenderás una solución eficaz para hacerlo. Sigue el ejemplo práctico para contar números negativos consecutivos y optimiza la gestión de tus datos.

¿Estás trabajando con una hoja de clálculo de Excel que contiene números positivos y negativos? ¿Necesitas contar el número de números negativos consecutivos? Contar números negativos en una lista puede parecer sencillo, pero cuando se trata de números negativos consecutivos, la situación se complica un poco. En este artículo, te mostraré una solución eficaz para contar números negativos consecutivos en Excel, ilustrándola con un ejemplo práctico.

Ejemplo

Supongamos que tenemos una serie de datos del mercado de valores semanal y se nos pide calcular el número de semanas consecutivas en las que el mercado sufrió pérdidas. Los datos están organizados en dos columnas, con los encabezados en la fila 1 y los datos que van desde la celda A2 hasta la celda B10.

Para calcular el número de semanas consecutivas con pérdidas, introduce la siguiente fórmula en la celda C2 y pulsa
Ctrl + Mayús + Intro para confirmar la fórmula como una fórmula matricial:

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

El resultado será el número de semanas consecutivas en las que el mercado de valores estuvo en números rojos.

Funcionamiento de la Fórmula

La fórmula utiliza la función » FREQUENCY para calcular el número de veces que aparecen números negativos consecutivos. A continuación, te explicaré paso a paso cómo la fórmula devuelve el resultado deseado.

Paso 1: Encontrar los Números de Fila de los Valores Inferiores a 0

La fórmula » IF(B2:B10<0,ROW(B2:B10)) devuelve una matriz con los números de fila de los valores que son menores que 0. Por ejemplo, si los números negativos se encuentran en las filas 2, 3 y 4, la matriz será » {2;3;4;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}.

Paso 2: Encontrar los Números de Fila de los Valores Mayores o Iguales a 0

La fórmula » IF(B2:B10>=0,ROW(B2:B10)) devuelve una matriz con los números de fila de los valores que son mayores o iguales a 0. Por ejemplo, si los números positivos se encuentran en las filas 5, 6 y 7, la matriz será » {FALSE;FALSE;FALSE;FALSE;5;6;7;FALSE;FALSE}.

Paso 3: Calcular la Matriz de Números Negativos Consecutivos

Utilizando la función » FREQUENCY, calculamos la matriz de números negativos consecutivos. La fórmula será similar a esta: » =FREQUENCY({2;3;4;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE},{FALSE;FALSE;FALSE;FALSE;5;6;7;FALSE;FALSE})«. La matriz resultante será » {3;0;0;0}.

Paso 4: Encontrar el Máximo de la Matriz

El resultado final de la fórmula será el valor máximo presente en la matriz de números negativos consecutivos. En este caso, el resultado será 3, ya que hubo 3 semanas consecutivas de pérdidas en el mercado de valores.

Solución VBA: Función Personalizada para Contar Números Negativos

Además de la fórmula mostrada anteriormente, es posible utilizar una solución VBA para calcular números negativos consecutivos en Excel. A continuación, encontrarás una función personalizada que puedes utilizar para obtener el mismo 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 función, introduce » =ContaNumeriNegativi(B2:B10) en la celda C2. El resultado será el mismo que se obtuvo utilizando la fórmula anterior.

Contar Números Positivos Consecutivos

De manera similar, es posible calcular el número de veces que aparecen números positivos consecutivos. La fórmula será casi idéntica a la utilizada para los números negativos, pero con una ligera diferencia en los signos. La fórmula será la siguiente:

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

Sumar Valores Negativos Consecutivos

En el ejemplo anterior calculamos el número de números negativos consecutivos. Sin embargo, si necesitas sumar los valores negativos consecutivos, puedes utilizar la siguiente 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)))

Ten en cuenta que el ejemplo proporcionado es solo para fines demostrativos y que sumar valores porcentuales no tiene sentido. Contar números positivos o negativos consecutivos en una hoja de clálculo de Excel puede parecer una tarea complicada, pero utilizando las fórmulas y funciones correctas, es posible obtener los resultados deseados. La fórmula y la solución VBA presentadas en este artículo te permitirán contar números positivos o negativos consecutivos de manera eficiente. Espero que esta información te haya sido útil y que pueda facilitar tu trabajo con Excel.

Pubblicato in Excel