martes, 22 de mayo de 2018

VBA: Saltos en listados no correlativos

Veremos hoy un desarrollo con programación y una alternativa con matriciales para descubrir qué valores faltan entre dos números que indiquemos...
Se trata de dar respuesta a la cuestión planteada por un usuario del blog:
[...]Tengo esta cuestión como puedo encontrar los 'faltantes' del correlativo, no se si puede mostrar en una solo celda o en varias hacia la derecha.[...]

Según esta imagen...

VBA: Saltos en listados no correlativos



Una primera solución sería la aplicación de algo de programación.... donde crearemos una función personalizada-UDF que a través de unos bucles recupere los elementos 'faltantes' entre dos elementos dados.

Crearemos nuestra función personalizada UDF llamada 'SaltoNumeros' en un módulo estándar:

Function SaltoNumeros(valor1 As Range, valor2 As Range) As String
Dim arrSaltos() As Variant
Dim Faltas As Long
'calculamos el número de valores que falta...
Faltas = valor2 - valor1 - 1

'creamos la matriz de dimensión el nñumero de elementos que faltan
ReDim arrSaltos(1 To Application.Max(1, Faltas)) As Variant

If Faltas = 0 Then
    SaltoNumeros = ""
Else
    'llenamos la matriz con los valores que faltan
    For i = 1 To Faltas
        arrSaltos(i) = valor1 + i
    Next i
    'y terminamos componiendo un concatenado de ellos
    For v = 1 To UBound(arrSaltos)
        SaltoNumeros = SaltoNumeros & ", " & arrSaltos(v)
    Next v
    'quitamos e lúltimo separador
    SaltoNumeros = Mid(SaltoNumeros, 3, Len(SaltoNumeros))
End If
End Function



En el rango 'amarillo' de la imagen, rango C2:C14, hemos insertado nuestra fórmula:
=SaltoNumeros($A2;$A3)
que luego arrastraremos hasta el fin del rango.
Observamos que lo retornado coincide con la necesidad expresada manualmente.
Esta solución se obtiene, en este caso, en la misma celda de manera concatenada.


una alternativa a la programación es el uso matricial de diferentes fórmulas.
En la primera imagen (más arriba) en el rango verde (rango D2:H14) veíamos el resultado.
En este segundo caso, obtendremos cada elemento 'faltante' en una celda diferente... estando sujeto a una posible falta de columnas.

Nuestra fórmula matricial en D2 es:
=SI.ERROR(INDICE($A2+TRANSPONER(FILA(INDIRECTO("1:" & $A3-$A2-1)));1;COLUMNAS($D$1:D$1));"")
(recuerda ejecutarla matricialmente presionando Ctrl+Mayusc+Enter !!)

Luego podemos arrastrar al resto del rango.


La clave es el uso extendido de
FILA(INDIRECTO("1:" & $A3-$A2-1))
para obtener un número correlativo: 1,2,3,...
que sumaremos al valor de inicio de nuestro intervalo (A2 en el ejemplo).
Para poder trabajar por columnas hemos tenido que aplicarle la función TRANSPONER.
Esta suma matricial devuelve una constante matricial, por ejemplo: {104\105\106}

Así pues en el siguiente paso con la función INDICE sobre esa matriz, recuperamos los elementos de la columna 1, 2, 3, etc...
Tal como se muestra en el rango D2:H14.


Resolviendo la cuestión planteada por el usuario...

2 comentarios: