jueves, 30 de noviembre de 2017

Descubriendo tendencias en un rango

A raíz de una consulta de un lector, respecto a la forma de descubrir tendencias dentro de un rango propongo el siguiente post.
[...]Tengo un conjunto de 20 datos ubicados en la columna F, desde F1 a F20. Que función podría usar para identificar si 7 datos consecutivos de esos datos muestran un comportamiento ascendente o si 7 datos consecutivos de esos datos muestran un comportamiento descendente[...]

Veamos el planteamiento.
A partir del listado de valores en el rango descrito F1:F20 queremos descubrir si existe una secuencia de siete valores consecutivos crecientes:



Una primera solución sería aplicando una fórmula en un rango adyacente, tal como se ve en la imagen anterior, donde en G2 insertamos la fórmula:
=SI((F2>=F1)*1=0;0;G1+(F2>=F1)*1)
que luego arrastraremos hasta G20.
Este cálculo nos permite ver si existe o no una secuencia creciente...

A partir de este rango podemos construir una fórmula condicional sencilla que nos indique cuál ha sido la máxima tendencia creciente. En H1 insertamos:
=SI(MAX(G2:G20)>=7;"Crecimiento de "&MAX(G2:G20);"")

De esta forma visualizamos dónde se encuentra esa tendencia y hasta donde crece.


Otra posibilidad la encontramos en la programación de VBA para Excel donde podemos crear una UDF en un módulo estándar de nuestro proyecto VBA:

Function FxTendencias(rngDatos As Range, tope As Long) As String
'rngDatos será el rango a analizar
'tope el valor que nos indique la longitud de la tendencia

'iniciamos contadores
x = 0: y = 0: num = 1: contador = 0
'recorremos el rango de celdas
For dato = 2 To rngDatos.Count
num = num + 1
    'identificamos crecimiento entre celdas
    If rngDatos.Item(dato).Value >= rngDatos.Item(dato - 1).Value Then
        x = x + 1: y = Application.Max(x, y)
        contador = contador + 1
        'registramos máximos cuando superemos el tope elegido
        If y >= tope Then
            UD = rngDatos.Item(dato).Value
            fila = rngDatos.Item(num).Row
            cuenta = Application.Max(contador, cuenta)
        End If
    Else
        x = 0: y = 0: contador = 0
    End If
Next dato

'devolvemos el dato a la hoja
FxTendencias = "Tendencia de " & cuenta & " en el rango: F" & (fila - 7) & ":F" & fila
End Function


Al ejecutarla en una celda de la hoja de cálculo:

Descubriendo tendencias en un rango


En este caso, la función personalizada, nos permite también identificar el rango donde se produce la tendencia...

No hay comentarios:

Publicar un comentario