jueves, 24 de mayo de 2018

VBA: application.Union-Trabajando sobre rangos discontinuos

Veremos un ejemplo hoy de como trabajar sobre rangos discontinuos desde nuestras macros.
Para ello haremos uso de una de las propiedades del objeto Application que vimos en este otro post


Emplearemos la propiedad: Application.Union para resolver esta cuestión.

La idea del ejemplo de hoy e ser capaz de recorrer con n solo bucle un rango de celdas discontinuo..(B2:B6, D2:D6 y F2:F6)

Para ello crearemos nuestro procedimiento en un módulo estándar:

Sub UnionRangosDiscontinuos()
'Unir tres rangos discontinuos
Dim rng1 As Range, rng2 As Range, rng3 As Range
'definimos los tres rango discontinuos a unir
Set rng1 = Range("B2").CurrentRegion
Set rng2 = Range("D2").CurrentRegion
Set rng3 = Range("F2").CurrentRegion

'los unimos con la propiedad .Unión !!!
Dim rngUnido As Range
Set rngUnido = Application.Union(rng1, rng2, rng3)

'demostramos que el recorrido se hace sobre el rango discontinuo
For Each celda In rngUnido
    With celda
        .Font.Bold = True
        .Interior.Color = vbYellow
    End With
Next celda

End Sub



Listo... rápido y simple.
Podemos comprobar, al ejecutar nuestra macro, que el resultado es el esperado...
solo recorremos las celdas de los tres rangos definidos.


Más adelante expondré una alternativa con arrays más eficiente...

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...

jueves, 17 de mayo de 2018

SUMAR.SI condicionado sobre diferentes hojas

Daremos una segunda vuelta de tuerca a un tema recurrente que ya hemos visto en ocasiones anteriores en el blog (ver uno y dos).

Trataremos la forma de conseguir sumar de manera condicionada sobre todas las hojas del libro deseadas, siempre que existe una distribución por columnas homogénea en todas las hojas.


El desarrollo que veremos nace por la necesidad de una alumna en uno de los cursos que impartí recientemente, donde a partir de ficheros con un número alto de hojas de trabajo (más de cien en algún caso), debía obtener un consolidado de acuerdo a uno elemento o condición... y lo expuesto en esto otro post) quedaba un poco inoperativo.

Supongamos un fichero con tres hojas (en realidad podrían ser 300...): ene, feb y mar; donde tenemos una serie de códigos repetidos en las columnas A de cada hoja, igualmente cantidades a consolidar 'unidades' e 'importe' en las columnas B y C.
Es fundamental que se de esta circunstancia: Datos dispuestos en mismas columnas!!!

SUMAR.SI condicionado sobre diferentes hojas



Nuestro objetivo acumular en una primera hoja 'resumen' los valores de unidades e importes que aparezcan en el resto de hojas...

La diferencia respecto al método empleado en el post anterior comentado será el número de hojas involucradas, que podría ser desconocido.
La clave por tanto es identificar y listar los nombres de todas las hojas de libro empleando la función de Excel 4.0: GET.WORKBOOK o en español INDICAR.LIBRO.

OJO!!, esto requerirá que nuestro libro de trabajo se grabe como libro de Excel habilitado para macros!!.

Por otra parte para poder hacer uso de este tipo de funciones en nuestras hojas, deberemos emplearlo dentro de los nombres definidos!!.


El primer paso es generar un nombre definido que comprenderá todas las hojas.
Nuestro nombre definido se llamará 'NombreHojas' y deberemos incluir la siguiente fórmula:
=REEMPLAZAR(INDICAR.LIBRO(1)&T(AHORA());1;ENCONTRAR("]";INDICAR.LIBRO(1)&T(AHORA()));"")

SUMAR.SI condicionado sobre diferentes hojas


INDICAR.LIBRO devuelve el nombre completo de nuestro libro incluyendo la hoja...
Con la función REEMPLAZAR eliminamos el nombre del libro y nos quedamos únicamente con lo que nos interesa, que es el nombre de la hoja.


Para conseguir el listado de nuestras hojas, aplicaremos la función INDICE sobre el conjunto de nombres que retornará INDICAR.LIBRO.
Para ello crearemos una tabla donde insertaremos un orden desde 2 hasta un número alto, que represente la posición de todas las hojas de nuestro libro (esto es, 2,3,4,5, ..., 300).
Comenzamos en 2 por que la hoja 1 es la de 'resumen', hoja sobre la que no deseamos operar.

En la tabla añadimos la fórmula:
=SI.ERROR(INDICE(NombreHojas;[@orden]);"")

SUMAR.SI condicionado sobre diferentes hojas



Ya tenemos el listado de hojas del libro que necesitábamos.
Este paso se puede obviar y escribir manualmente si el listado fuera personalizado.


En el siguiente paso generaremos un nuevo nombre definido con fórmula para trabajar sobre las hojas existentes.
El nombre lo llamaré 'MisHojas' y tendrá la fórmula:
=DESREF(TblHojas[[#Encabezados];[hojas]];1;0;CONTARA(TblHojas[hojas])-CONTAR.BLANCO(TblHojas[hojas]))

que devolverá las hojas existentes, evitando las vacías que provocarían fallos en los pasos siguientes.

SUMAR.SI condicionado sobre diferentes hojas



Y finalmente compondremos nuestra fórmula matricial definitiva para consolidar los datos de las diferentes hojas según el código buscado.

En D4 insertamos y ejecutamos matricialmente (presionando Ctrl+mayusc+Enter):
=SUMA(SUMAR.SI(INDIRECTO(MisHojas & "!A:A");$C4;INDIRECTO(MisHojas & "!B:B")))

y en E2 insertamos y ejecutamos matricialmente (presionando Ctrl+mayusc+Enter):
=SUMA(SUMAR.SI(INDIRECTO(MisHojas & "!A:A");$C4;INDIRECTO(MisHojas & "!C:C")))

arrastrando después tanto como necesitemos...

SUMAR.SI condicionado sobre diferentes hojas



Consiguiendo nuestra meta...