jueves, 19 de mayo de 2022

LAMBDA: Aleatorios sin repetición

Días a trás tuve la necesidad de obtener un listado aleatorio de números sin repetición, y aunque ya escribí al respecto con fórmulas o con VBA) pensé en darle una vuelta con la función LAMBDA, ya que esa faceta recursiva será útil en este caso...

Nuestra fórmula tendrá esta forma:
=LAMBDA(num_eltos;
    min;
    max;
    LET(mn;min;
        mx;max;
        array;ORDENAR(UNICOS(ARCHIVOMAKEARRAY(num_eltos;1;LAMBDA(r;c;ALEATORIO.ENTRE(mn;mx))))); 
        SI(CONTARA(array)<num_eltos;AleatSinRep(num_eltos;mn;mx);array)))

Función que debemos incluir dentro de Administrador de nombres asignándole el nombre 'AleatSinRep', que como ves se emplea dentro de ella misma.
LAMBDA: Aleatorios sin repetición

El resultado será el deseado... un listado de valores sin repetición (probado 10.000 veces)
LAMBDA: Aleatorios sin repetición


Con nuestra recién creado función LAMBDA, tras informar de los tres argumentos necesarios:
num_eltos: para indicar el tamaño del listado
min y max: para marcar el valor más bajo y alto del intervalo de valores en los que se mueve cada aleatorio
Pasamos con la función ARCHIVOMAKEARRAY a crear una matriz/vector del tamaño indicado (num_elementos) de numéros enteros aleatorios entre los valores min y max informados.
Este vector de aleatorios, una vez aplicada la función UNICOS (que elimina posibles duplicidades!!) y ORDENADO por estética, se evalua y verifica cuántos elementos han quedado finalmente... obviamente si en el primer intento hubiera habido algú nrepetido, UNICOS se hubiera encargado de reducir el tamaño de nuestro listado...
De ahí que con el condicional SI comprobemos si disponemos del 'num_eltos' requerido, en caso de no llegar relanzamos nuevamente nuestra función (recursividad al rescate!!), las veces que hagan falta hasta conseguir un listado de elementos únicos sin repetición.

Seguramente no es lo más eficiente en tiempo ni en recursos, pero para pequeños cálculos es válido 👍

martes, 17 de mayo de 2022

VBA: JOIN & TRANSPOSE mejor juntos

Quizá en alguna ocasión hayas requerido recuperar datos de la hoja de cálculo y tratarlos como una cadena de texto, y seguramente en algún momento lo hayas necesitado trabajar desde una Array...
Si has contestado afirmativamente en ambos casos, este artículo te ayudará.

Es probable conozcas la función JOIN(sourcearray, [ delimiter ]):
función interesante que propone la posibilidad de retornar una cadena creada mediante la combinación de varias subcadenas de una matriz/array.
Con dos sencillos argumentos:
sourcearray (obligatorio): Debe ser una matriz unidimensional que contenga las subcadenas que se van a combinar.
delimiter (opcional): carácter que se utilizará para separar las subcadenas en la cadena devuelta.
Si se omite, se utiliza el carácter de espacio (" ").
Si delimitador es una cadena de longitud cero (""), se concatenan todos los elementos de la lista sin delimitadores.

Pinta bien... parece asemejarse a funciones de la hoja de cálculo como UNIRCADENAS, o CONCAT.

Pero hay trampa con ella. Veamos un ejemplo.
Partimos de un listado de algunos países, en forma de tabla, en nuestra hoja de cálculo... llamada 'TblPAIS'
VBA: JOIN & SPLIT mejor juntos

Asi que un primer intento podría ser generar una macro en un módulo estándar:
Sub IntentoJOIN()
Dim arrPaises() As String

arrPaises = Join(Range("TblPAIS"), ",")

Debug.Print arrPaises
End Sub

Donde intentamos, sin éxito, cargar en nuestra variable-matriz 'arrPaises' los elementos del rango 'TblPAIS' para que conformen una cadena de texto...
Puedes intentar cuantas combinaciones desees:
Dim arrPaises() As Variant
Dim arrPaises As String
etc
todas ellas acaban con el mismo mensaje: No coinciden los tipos

¿Qué ocurre?, ¿qué estoy haciendo mal?.
Bien, la cosa tiene que ver con los tipos de datos admitidos por los argumentos de JOIN... en concreto el argumento 'sourcearray', que especificamente indica debe ser una array unidimensional de subcadenas.. y en el ejemplo le he asignado un Range!.

Veamos pues una posible, y simple, solución. Modifiquemos nuestra macro, quedando ahora funcional:
Sub IntentoJOIN()
'defino la futura cadena de texto resultado
Dim strPaises As String

'y aplicamos después de Transponer, y retornar la cadena de texto buscada...
strPaises = Join(Application.Transpose(Range("TblPAIS").Value), ",")

'devolvemos el resultado a la ventana de inmediato
Debug.Print strPaises
End Sub
''''''''''''''''''''
'o alternativamente, si prefieres más claridad
'''''''''''''''''''''
Sub IntentoJOIN_v2()
'defino una array, y la futura cadena de texto resultado
Dim arrPaises As Variant, strPaises As String
'en la primera, como tipo Variant, cargamos los datos de la hoja de cálculo
arrPaises = Range("TblPAIS").Value

'y ya como array, sí podemos trabajar con JOIN, y retornar la cadena de texto buscada...
strPaises = Join(Application.Transpose(arrPaises), ",")

'devolvemos el resultado a la ventana de inmediato
Debug.Print strPaises
End Sub

:OO así de simple.
MUY IMPORTANTE aplicar 'Application.Transpose' para disponer de esa matriz unidimensional requerida.

Una alterantiva, para gustos los colores, es generar la array necesaria empleando una combinación de SPLIT y JOIN... algo retorcida ;-D
La función Split(expression, [ delimiter, [ limit, [ compare ]]]) es otra de esas funciones de uso habitual que seguramente no nos hayamos detenido nunca a analizar.
La función devuelve una matriz/array de base cero unidimensional que contiene un número especificado de subcadenas.
Recalquemos: UNIDIMENSIONAL.
Asi nuestro procedimiento podría ser:
Sub IntentoSPLIT_JOIN()
'defino una array y la cadena de texto final
Dim arrPaises() As String, strPaises As String
'en la primera, como tipo String , cargamos los datos de la hoja de cálculo
arrPaises = Split(Join(Application.Transpose(Range("TblPAIS").Value), "|"), "|")

'utilizo de nuevo JOIN para concatenar los elementos de la array obtenida
'(ya he dicho que es retorcido) :D
strPaises = Join(arrPaises, ",")

'devolvemos el resultado a la ventana de inmediato
Debug.Print strPaises
End Sub


recuerda pues estas DOS líneas... te sacarán del apuro en más de una ocasión
Sub JOIN_TRANSPOSE()
Dim strPaises As String
strPaises = Join(Application.Transpose(Range("TblPAIS").Value), ",")

Debug.Print strPaises
End Sub


Nota: He leído y leído por diferentes sitios en busca de documentación de Transpose, pero a parte de la referida a la función de la hoja de cálculo, poco más :'(
Pero sin duda es fundamental para 'convertir' rangos directamente a Arrays unidimensionales... requeridas, como hemos visto, en distintas funciones.

jueves, 12 de mayo de 2022

Calcular fecha futura de cobro

Seguramente habrás tenido que calcular en muchas ocasiones, para realizar previsiones de tesorería, cuál es la fecha de cobro de cierto cliente, con el que firmaste unas condiciones fijas del estilo: el día diez del mes correspondiente a 60 días después de la fecha de factura.

Siempre me ha llamado la atención, en mis años en departamentos administrativos contables, lo engañoso que pueden ser esos 60 días.. ya que en la gran mayoría de los casos esos sesenta días, con suerte serán solo 70 más o menos...
Otros diez días extra de apalancamiento :D (así son los negocios, no?).

¿Y cómo calculo esa fecha mágica?.
He visto a lo largo del tiempo muchas fórmulas, empleando condicionales sobre todo, que dan con la fecha buscada...
pero hoy os contaré una alternativa empleando funciones como LET o SECUENCIA.
Partimos de un listado de facturas, de las cuales necesitamos conocer la fecha futura de cobro
Calcular fecha futura de pago-cobro


La fórmula requerida la vemos en el rango E5:E15
=LET(rngFechas;SECUENCIA(35;1;B5+60;1);
INDICE(rngFechas;COINCIDIR(10;DIA(rngFechas);0)))

Nota la simpleza de la función...
Generamos un listado de 35 fechas a partir de la fecha futura sesenta días después; para luego localizar la posición del siguiente o próximo día 10 de ese listado... y listo, tenemos nuestra fecha.

Si verificas la columna H verás la diferencia real de días entre la fecha de factura y la de vencimiento calculada... los sesenta días quedan lejos ;-)

martes, 10 de mayo de 2022

VBA: Cargando datos en Arrays

Repasaremos algunos conceptos fundamentales para trabajar con VBA como puede ser la carga de datos en una Array.
No vamos a descubrir ahora a nadie las bondades de trabajar con Arrays en nuestros códigos... la rapidez y agilidad a la hora de trasladar o sencillamente trabajar con datos cargados es simplemente 'brutal'.
Imagina una tabla de datos con 51 campos y 240.000 registros... son muchos datos... de hecho el fichero pesa alrededor de 60 Mb (mucho para un fichero solo con datos y cero cálculo).

Veamos algunos de los procesos habituales de carga de datos desde nuestra celdas a una Array.
Accedemos a un módulo estándar del editor de VBA y escribimos:
Sub OptimizandoCargaDatos()

Start = Timer   'inicia el contador de tiempo
Dim Tbl As ListObject
Set Tbl = ActiveSheet.ListObjects(1)

''A)  bien cargamos la matriz directamente
arrDatos1 = Tbl.DataBodyRange.Value

finA = Round(Timer - Start, 3)  'contabilizamos tiempo de carga
Start = Timer   'inicia el contador de tiempo
'
''B)) o alternativamente a través de una función...
'dentro de nuestro módulo estándar
arrDatos2 = CargaArrays

finB = Round(Timer - Start, 3)  'contabilizamos tiempo de carga
Start = Timer 'inicia el contador de tiempo
'
''C) o desde un módulo de clase
Dim arrClase As New clsCargaArrays
arrDatos3 = arrClase.CargaMatrices

finC = Round(Timer - Start, 3)  'contabilizamos tiempo de carga
Start = Timer   'inicia el contador de tiempo

''D) cargándolo usando Properties desde el módulo de clase
Dim arrMatriz As New Clase1
arrDatos5 = arrMatriz.TodaMatriz

Fin_D = Round(Timer - Start, 3) 'contabilizamos tiempo de carga
Start = Timer   'inicia el contador de tiempo

''Tip/bonus: Seleccionar ciertas columnas de la Matriz
Set arrTest = New Clase1
arrDatos1_3 = arrTest.SelectCols(Array(1, 3))

fin_E = Round(Timer - Start, 3) 'contabilizamos tiempo de carga

Debug.Print "A (carga directa clásica): " & finA & vbCrLf & _
"B (a través de UDF): " & finB & vbCrLf & _
"C (1-módulo clase - funcion): " & finC & vbCrLf & _
"D (2-modulo clase Property Get: " & Fin_D & vbCrLf & _
"E (Bonus: Seleccionar columnas de la Array): " & fin_E & vbCrLf & "___________________"
Set Tbl = Nothing
Set arrTest = Nothing
End Sub
''''''''''''''
Function CargaArrays() As Variant
    CargaArrays = ActiveSheet.ListObjects(1).DataBodyRange.Value
End Function


Por otra parte crearemos dos módulos de clase.
El primero llamado 'Clase1' que contiene los siguientos procesos:
Dim m_Array As Range
'--------------------------------------------
Private Sub Class_Initialize()
    Set m_Array = ActiveSheet.ListObjects(1).DataBodyRange
End Sub
'--------------------------------------------
Private Sub Class_Terminate()
    Set m_Array = Nothing
End Sub
'--------------------------------------------
Property Get ColVector(n As Long) As Variant
    'funciona y devuelve el vector completo
    ColVector = Empty
    
    On Error GoTo Err_Handler
    ColVector = m_Array.Columns(n)
    Exit Property
    
Err_Handler:
End Property
'--------------------------------------------
Property Get SelectCols(nCols() As Variant) As Variant
    SelectCols = Empty
    
    On Error GoTo Err_Handler
    SelectCols = Application.Index(m_Array, Evaluate("Row(1:" & m_Array.Rows.Count & ")"), nCols)
    Exit Property
    
Err_Handler:
End Property
'--------------------------------------------
Property Get TodaMatriz() As Variant
    TodaMatriz = Empty

    On Error GoTo Err_Handler
    TodaMatriz = m_Array
    Exit Property

Err_Handler:
End Property

En este módulo de clase creamos un 'objeto matriz' cargando datos de la hoja de cálculo con un procedimiento 'Sub Class_Initializa', el cual carga el objeto m_Array con los datos de la tabla cuando desde un módulo estandar, por ejemplo, se llame a ese 'objeto' Clase1.
Además he generado distintas propiedades del objeto que permiten obtener un vector, un conjunto de vectores o simplemente la totalidad de la matriz, a partir de la Array cargada.

Necesitamos un módulo de clase más... llamado 'clsCargaArrays', con un simple procedimiento Function:
Function CargaMatrices() As Variant
    CargaMatrices = ActiveSheet.ListObjects(1).DataBodyRange.Value
 End Function


Si lanzamos varias veces nuestro procedimiento, y vemos el tiempo requerido por cada forma descrita, veremos lo siguiente:
VBA: Cargando datos en Arrays

Observamos tiempos de carga prácticamente idénticos, por lo que la decisión de optar por uno u otro es decisión personal...
Nota que estamos simplemente cargando 12.240.000 de celdas con datos, sin ningún otro procesamiento... y que una carga cualquiera de ese volumen de información neutra consume alrededor de 2,5 segundos!!! (asombroso!).

Siempre práctico tener alternativas de trabajo... en este claso incluyendo el trabajo (en varias formas) con Módulos de clase.

jueves, 5 de mayo de 2022

Power Query: Columnas a agrupar dinámicamente

Un caso fuera de lo habitual es poder elegir, de acuerdo a una lista, qué campos de una tabla de trabajo queremos agrupar/agregar.
Power Query: Columnas a agrupar dinámicamente

Partimos de una tabla de acciones detalladas, de acuerdo a una serie de conceptos: País, Ciudad, Año... y unas acciones o tareas desglosadas: Comercial, Estado de cuenta o Acción.
De esta tabla principal queremos obtener una comparativa de ciertos campos (comercial o estado o acción) respecto a los años 2022 y 2023 que aparezcan.
Para forzar qué campos emplearemos para una u otra cosa, dispondremos de una segunda tabla (en azul, a la derecha de la imagen) con dos campos...

Comezaremos cargando las dos tablas y ya dentro del editor de power query realizamos algunas tareas...

En primer lugar generaremos un par de listas a partir de la tabla azul 'TblCampos_a_Mostrar'...
Una primera lista solo con los campos que rigen la agrupación ('LstCamposAgrupacion'), y una segunda con los campos que queremos comparar/agrupar ('LstCamposComparar').
Así pues nuestras dos consultas que generan sendas listas serían:
//LstCamposAgrupacion
  let
    Origen = TblCamposMostrar,
    FilasFiltradas = Table.SelectRows(Origen, each ([Agrupar] = "Sí"))[Listado_campos_mostrar]
in
    FilasFiltradas
y
//LstCamposComparar
  let
    Origen = TblCamposMostrar,
    FilasFiltradas = Table.SelectRows(Origen, each ([Agrupar] = "Para_comparar"))[Listado_campos_mostrar]
in
    FilasFiltradas

dos consultas muy simples para filtrar los elementos deseados...

Finalmente creamos una consulta vacía nueva donde escribimos el código que permite la agrupación variable de columnas:
let
    //cargamos en buffer la tabla para evitar alteraciones de orden a la hora de agrupar
    Tabla=Table.Buffer(TblACCIONES),

    //realizamos la acción dinámica de agrupación
    FilasAgrupadas=Table.Group(Tabla, LstCamposAgrupacion, 
                List.Transform(
                    LstCamposComparar,
                    (Col) => {Col & "(2022-2023)", (Local) => Text.Combine(Table.Column(Local, Col),"-"), type nullable text}
                )  )
in
    FilasAgrupadas

En el ejemplo he optado por una transformación diferente a las habituales (de suma, promedio, etc.), donde aplico un concatenado de texto (Text.Combine) que permite mostrar la situación del 2022 vs 2023.

Pero lo realmente interesante es cómo hemos incluido dentro de la acción de agrupar Table.Group, la función List.Transform que se encarga de operar solo sobre la lista de campos a comparar elegida.
La combinación de estas dos funciones de GROUP y TRANSFORM sobre las listas elegidas en los primeros pasos del desarrollo nos retorna lo que queríamos...
Una agrupación sobre los campos de la 'LstCamposAgrupacion' y una agregación (concatenado en el ejemplo= sobre la 'LstCamposComparar'.