LAMBDA: Contando únicos

Se me presentó hace unos días una situación interesante... generar en una única matriz un resumen que indicara un conteo de empleados únicos, a partir de un listado de nombres.

El objetivo es, por tanto, obtener una sola matriz con un listado de empleados únicos con el dato de conteo a su lado, i.e., con el número de veces que aparezca en el listado original.

Mi primer intento fue con BYROW, en E4 añadimos:
=BYROW(UNICOS(C3:C17);LAMBDA(x;x&"-"&CONTAR.SI(C3:C17;x)))
donde alimentamos el argumento 'matriz' con el listado de empleados UNICOS, para posteriormente recorrer cada uno de estos trabajadores y obtener con LAMBDA un conteo directo (con CONTAR.SI)... además, concatenamos el resultado del conteo con el nombre del empleado.
Obtenemos un vecto único de resultados concatenados... No me gustaba mucho y le dí otra vuelta.

Recordé el 'viejo truco' para juntar vectores en una sola y única matriz empleando la función ELEGIR... asi que en la celda E12 apoyándonos en LET escribimos:
=LET(lstUnicos;UNICOS(C3:C17);
conteo; BYROW( lstUnicos; LAMBDA(fila;CONTAR.SI(C3:C17; fila) ) );
ELEGIR({1\2}; lstUnicos; conteo ) )
comenzamos asignando valor a dos variables:
- lstUnicos: que será el vector resultado devuelto por la función UNICOS, es decir, listado de empleados únicos
- conteo: la base de la fórmula primigénia con BYROW, es decir, un vector de conteos para cada empleado

Acabamos uniendo los dos vectores en una sola matriz con ELEGIR.

Una última alternativa propuesta por mi colega Sergei Baklan (MVP Excel) sería:
=LET(u; UNICOS(C3:C17); countUnique; BYROW( u; LAMBDA(r; CONTAR.SI(C3:C17; r) ) ); SI({1\0}; u; countUnique ) )
donde aporta una curiosa alternativa a ELEGIR.

Un potente ejemplo de lo que podemos conseguir con estas nuevas funciones LAMBDA, LET, BYROW, etc...!!

Seguir leyendo...

LAMBDA: Método FIFO

Creo que me estoy volviendo un poco loco con las funciones LAMBDA :DD
Hoy he decidido intentar aplicar estas funciones para aplicar el método de valoración de inventario por el método FIFO
Asi que partiremos de una tabla de movimientos para un artículo, donde recogemmos sus entradas y salidas:

El primer cálculo será para obtener un vector con el stock vivo en cada movimiento.
En I4 escribimos:
=SCAN(0;
$E$4:$E$10*SI($C$4:$C$10="Entrada";1;-1);
LAMBDA(ac;itm;ac+itm))

La función SCAN recorre acumulando las unidades de entrada y salida, a las que hemos cambiado el signo para facilitar el correcto acumulado.

El siguiente paso es clave para el desarrollo, ya que con este cálculo obtenemos el consumo actual de cada entrada que exista, de acuerdo a las salidas.
(FUNDAMENTAL QUE LOS DATOS ESTÉN ORDENADOS CON UN CRITERIO TEMPORAL!)

En J4 la fórmulita LAMBDA...
=LAMBDA(vector;ValorReparto;
MAP(SCAN(0;vector;LAMBDA(ac;itm;MIN(itm;ValorReparto-ac;itm)));
SCAN(0;SCAN(0;vector;LAMBDA(ac;itm;MIN(itm;ValorReparto-ac;itm)));LAMBDA(acum;elto;acum+elto));
LAMBDA(prim;seg;SI(seg<ValorReparto;prim;SI(prim=0;0;prim-seg+ValorReparto)))))($E$4:$E$10*SI($C$4:$C$10="Entrada";1;0);$J$14)
Nuesta función LAMBDA requiere dos argumentos, un vector con los movimientos de unidades, y por otro lado el número de unidades totales que salen...
El argumento 'matriz' está compuesto de una función MAP con dos matrices:

matriz 1:

SCAN(0;vector;LAMBDA(ac;itm;MIN(itm;ValorReparto-ac;itm)))
que nos devuelve un vector solo con las unidades de entrada

matriz 2:

SCAN(0;SCAN(0;vector;LAMBDA(ac;itm;MIN(itm;ValorReparto-ac;itm)));LAMBDA(acum;elto;acum+elto))
nos devuelve un acumulado SOLO de las unidades de entrada (y sí, se debería haber empleado LET).
Estas serían las dos matrices/vectores anteriores:

El último argumento de MAP es la función LAMBDA:
LAMBDA(prim;seg;SI(seg<ValorReparto;prim;SI(prim=0;0;prim-seg+ValorReparto)))
que se encarga de comparar y operar sobre los dos vectores anteriores, de tal forma que obtenemos el vectoro final con las unidades consumidas de cada compra!

Con este cálculo clave solo nos queda una sencilla resta en la celda K4:
=$E$4:$E$10*SI($C$4:$C$10="Entrada";1;0)-J4:J10
con lo que obtenemos las unidades pendientes de vender, esto es, el stock vivo no empleado vinculado a cada compra.

Acabamos en la celda L4 multiplicando unidades pendientes por precio, con lo que llegamos a nuestra valoración FIFO.

Seguramente se pueda lograr de una forma más directa con estas funciones, pero como ejercicio donde empleamos dos matrices en la función MAP, me parece muy interesante ;-)

Seguir leyendo...

Power Query: Evitar el error Formula.Firewall

De todos es conocido el problema que se crea cuando parametrizamos nuestras rutas de enlace a las fuentes de datos... Salvo que tuvieramos 'cancelados' los niveles de privacidad (puedes leer el artículo a tal efecto) nos saltaría a nosotros mismos y al resto de compañeros/usuarios la advertencia:
Formula.Firewall: Consulta 'XXXX' (paso 'OOOOOO') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

Hoy veremos una alternativa a modificar las Opciones de la consulta.

Montemos nuestro modelo.
En una celda de nuestro libro de trabajo añadimos la ruta completa al fichero fuente, por comodidad, esta ruta será parte de una tabla:

Cargaremos y desagruparemos hasta conseguir que el valor de la ruta quede comoo un texto... El código M de la consulta quedará:

let
    Origen = Excel.CurrentWorkbook(){[Name="TblRUTA"]}[Content]{0}[Ruta_Carpeta]
in
    Origen

La renombraremos como pRUTACompleta

En el siguiente paso procedemos a enlazar con el fichero fuente indicado en esa celda... con una mínima transformación.
Desde el libro de trabajo, en la ficha Datos > grupo Obtener y transformar > Obtener datos > De un archivo > De un libro empleando el explorador existente localizamos el fichero fuente y aceptamos.
Indicamos qué tabla, de entre las existentes en el libro fuente, queremos transformar

Ya en el editor, si queremos, podemos aplicar algún filtro u otra acción...
En mi ejemplo el código M resultante de aplicar acciones 'normales' es:

let
    Origen = Excel.Workbook(File.Contents("F:\excelforo\PQ_Plegado_FormulaFireWall.xlsx"), null, true),
    compras4_Table = Origen{[Item="compras4",Kind="Table"]}[Data],
    TipoCambiado = Table.TransformColumnTypes(compras4_Table,{{"art", type text}, {"pais", type text}, {"uds", Int64.Type}}),
    FilasFiltradas = Table.SelectRows(TipoCambiado, each ([art] = "a") and ([pais] = "ES"))
in
    FilasFiltradas

Y lo más importante, de momento, no hay fallo... 👏👏

Sobre la consulta anterior, procedemos a reemplazar la ruta completa 'fija' que aparece como texto por nuestro parámetro 'pRUTAcompleta', el código quedará entonces:

let
    Origen = Excel.Workbook(File.Contents(pRUTAcompleta), null, true),
    compras4_Table = Origen{[Item="compras4",Kind="Table"]}[Data],
    TipoCambiado = Table.TransformColumnTypes(compras4_Table,{{"art", type text}, {"pais", type text}, {"uds", Int64.Type}}),
    FilasFiltradas = Table.SelectRows(TipoCambiado, each ([art] = "a") and ([pais] = "ES"))
in
    FilasFiltradas

Y aquí al Aceptar nos encontraremos con una pequeña sorpresa!!!.
El error Formula.Firewall aparece :O

No queremos o no podemos cambiar las Opciones de privacidad de las consultas... asi pues, ¿cuál es la solución??

Lo primero es que vamos a generar una Consulta en blanco (que llamaré 'EvitoFirewall') donde incluiré las siguientes líneas:

let
    //incluyo en esta consulta el parámetro para obtener el texto de la ruta completa
    RutaCarpeta=Excel.CurrentWorkbook(){[Name="TblRUTA"]}[Content]{0}[Ruta_Carpeta],
    //y hago uso de esta variable para incorporarla y recuperar su contenido
    Origen = Excel.Workbook(File.Contents(RutaCarpeta ), null, true)
in 
    Origen

Curiosamente al incluir el 'parámetro' de la ruta dentro de la consulta, no salta el Firewall.... muy bien!

Último paso, incluiremos y reemplazaremos esta consulta recién creada 'EvitoFirewall' en la consulta inicial que nos generaba el error... Y quedaría:

let
    //anulamos el viejo movimiento...
    //Origen = Excel.Workbook(File.Contents(pRUTACompleta ), null, true),

    //y lo sustituimos por
    Origen = EvitoFirewall,

    compras4_Table = Origen{[Item="compras4",Kind="Table"]}[Data],
    TipoCambiado = Table.TransformColumnTypes(compras4_Table,{{"art", type text}, {"pais", type text}, {"uds", Int64.Type}}),
    FilasFiltradas = Table.SelectRows(TipoCambiado, each ([art] = "a") and ([pais] = "ES"))
in
    FilasFiltradas

Te sorprenderás con el resultado!!. Presiona Listo y booom!.
Adios error Formula.Firewall

Resumen. Debemos crear una consulta intermedia que evite el firewall, e incluirla posteriormente como primer paso de nuestra consulta definitiva...
Y olvidate de cambiar tus niveles de privacidad ;-)

Seguir leyendo...

Power Query: Table.Partition vs Table.SelectRows

Hoy analizaremos dos alternativas para segmentar con Power Query grandes volúmenes de información.

Todos los que usamos Power Query en mayor o menor medida hemos leído sobre las bondades de 'filtrar' nuestras filas al inicio de nuestros procesos para reducir los tiempos de ejecución en pasos siguientes... Habitualmente lo conseguimos con la acción de filtrado sobre ciertas columnas, o lo que es lo mismo, empleado la función M : Table.SelectRows, conocida y usada en multitud de ejemplos...

Pero hay más alternativas (y no necesariamente mejores):
Table.Partition(table as table, column as text, groups as number, hash as function) as list
función que nos devuelve una Lista de tablas segmentadas, cada una de las cuales recoge un número indeterminado de filas, pudiendo controlar, en todo caso, qué filas incluimos en cada una.

Sin embargo la forma de 'separar' la información en distintas tablas es un tanto peculiar... ya que la función nos pide trabajar sobre una única columna!! (¿nos olvidamos de segmentar por varias columnas??... espera un poco y sigue leyendo).
Además el cuarto argumento de la función nos pide una función HASH (o función 'resumen'), que basicamente significa que de alguna manera debe trabajar sobre valores numéricos!?.
Si leemos la ayuda? de la documentación de Microsoft nos dice:
'Divide la tabla en una lista de grupos, es decir, en cierto número de tablas, basándose en una función hash y en el valor de la columna de cada fila. La función hash se aplica al valor de columna de una fila para obtener un valor hash para esta última. El valor "groups" del módulo hash determina en qué tablas devueltas se colocará la fila.'

Bastante confuso...

Veamos un ejemplo... supongamos una base de datos (d enombre 'compras4') con 215.000 registros y tres campos: artículo, país y número de unidades.
Queremos obtener una partición únicamente con los artículos 'a'.

Si cargamos dicha tabla a nuestro editor de Power Query, y sobre la consulta creada generamos una segunda consulta como referencia de la primera, tendríamos como único movimiento:

let
    Origen = Table.Partition(compras4,"art",2, each if _="a" then 0 else 1)
in
    Origen

donde indicamos que trabajamos sobre la tabla completa 'compras4' (con 215.000 filas), en concreto sobre la columna llamada 'art', que esperamos obtener dos particiones (i.e., dos tablas), la primera de ellas con las coincidencias del artículo 'a'.
Nota lo retorcido de la función Hash empleada... donde con algú tipo de fórmula o función tenemos que construir la equivalencia de los códigos 'a' y asociarlo a un valor numérico.

Igualmente importante es que el valor numérico asociado/calculado debe estar ordenado en ascendente, ya que la lista de tablas obtenidas con la función respetará esa ordenación.
Es decir, en mi ejemplo, con el condicional estoy asignado el valor cero a los artículos 'a' y el valor 1 al resto...
por tanto, primero irá la tabla con valor cero (la correspondiente a los artículos 'a'), y la segunda tabla generada el resto de valores asociados al valor numérico uno.

La consecuente pregunta es.. si esta función nos pide trabajar exclusivamente sobre el nombre de una columna, ¿cómo puedo aplicar una partición sobre un criterio multicolumna?.
He buscado por internet y no he encontrado nada al respecto... asi que probé algunas 'cosillas' y di con una solución intermedia:

let
    Origen = Table.Partition(
                Table.AddColumn(compras4,"combi", each [art]&[pais]),
                "combi",
                2, 
                each if _="aES" then 0 else 1)
in
    Origen

donde se alimenta el primer argumento con una tabla intermedia al que hemos añadido una nueva columna resultante de concatenar los valores de las columnas sobre las que trabajar (había que salir del paso... y el caso dió resultado).
Esa tabla intermedia, con la nueva columna será la base sobre la que aplicar la función Hash del ejemplo anterior...

Este sistema o forma de convertir textos a números, aunque un poco arcáico, es operativo.. permitiendo además obtener listados mayores 'controlados' de tablas.
Por ejemplo si queremos obtener una partición de tres tablas:
1-la primera para los artículos 'a'
2-la segunda para los artículos 'c'
3-para el resto de artículos
Nuesta consulta podría tener la siguiente forma:

let
    Origen = Table.Partition(
                compras4,
                "art",
                3, 
                each if _="a" then 0 else if _="c" then 1 else 2)
in
    Origen

Fíjate de nuevo en los valores asociados con el if then else a cada código de artículo.

Finalizar comentando que esta función Table.Partition es algo más lenta que la clásica Table.SelectRows !!.
En distintas pruebas, comparando una con otra obtenemos estos tiempos en milisegundos (para los 215.000 registros):
- FiltroMultiple con SelectRows //Total 96 ms
- ParticionMultiple con Partition //Total 132 ms

- FiltroSimple con SelectRows //Total 436 ms
- ParticionSimple con Partition //Total 415 ms

- FiltroMultipleSC //Total 73 ms
- ParticionMultipleSC //Total 100 ms

Concluir que, parece ser más óptimo en tiempo la clásica Table.SelectRows... pero no olvidar Table.Partition para tener alternativas ;-)

Seguir leyendo...

LAMBDA: Promedio de ponderaciones

Expondré hoy un cálculo elaborado empleando LAMBDA que nos permite conocer la media aritmética (por cada fila) de las ponderaciones de cada elemento sobre el acumulado de su propia columna... eligiendo un intervalo de columnas!!.

Permitemé ver qué estamos viendo en la imagen anterior... con una fórmula personalizada con LAMBDA:
en I2: =fxMediaPesos($B$2:$G$7;2;5)
hemos trabajado con los datos de la columna 2 a la 5 sobre nuestra matriz de datos B2:G7, esto es, trabajamos con importes desde febrero hasta mayo.
Nuestra fórmula ha sido capaz de recuperar solo las columnas involucradas, obtener el acumulado de cada columna individualmente, y obtener, por tanto el porcentaje que representa el peso/ponderación de cada elemento de nuestra matriz indicada...
No solo eso, además, sobre el resultado de esa matriz de ponderaciones, ha realizado un cálculo por filas para obtener el promedio de los pesos de cada fila o color!!.

Igual piensas que esto no sirve para nada... pero te sorprenderá saber que es la solución a un caso real planteado por un cliente para determinar tendencias/evoluciones sobre unas ventas :OOO

Detallemos nuestra función... (en realidad dos funciones).
Nuestras dos funciones LAMBDA agregadas en el Administrador de nombres son:
fxMediaPesos =LAMBDA(matriz;ci;cf;BYROW(fxPorcINDEX(matriz;ci;cf);LAMBDA(nFila;PROMEDIO(nFila))))
esta será nuestra función final, basada en el resultado de otra función LAMBDA:
fxPorcINDEX =LAMBDA(arr;ci;cf; LET(eltos;INDICE(arr;SECUENCIA(6);SECUENCIA(1;cf-ci+1;ci;1)); totales;BYCOL(eltos;LAMBDA(col;SUMA(col))); eltos/totales))
función que se encarga de devolver la matriz con los pesos/ponderaciones del intervalo de meses elegidos...


Veamos los pasos seguidos...

En el primer paso nuestra fórmula es sencilla...
En K2 creamos una sencilla función LAMBDA con tres argumentos que alimentarán nuestra función INDICE matricial.
=LAMBDA(arr;ci;cf;INDICE(arr;SECUENCIA(6);SECUENCIA(1;cf-ci+1;ci;1)))($B$2:$G$7;2;5)
el resultado es otra matriz creada a partir de la original que solo retorna ciertas columnas indicadas en los argumentos de LAMBDA.

En el segundo paso necesitamos calcular la suma de cada columna de la matriz anterior... usaremos la funcion BYCOL.
Así en K9 vemos el resultado:
=LAMBDA(arr;ci;cf;BYCOL(INDICE(arr;SECUENCIA(6);SECUENCIA(1;cf-ci+1;ci;1));LAMBDA(col;SUMA(col))))($B$2:$G$7;2;5)
Comprobamos el vector de sumas acumuladas calculado...

Paso tercero. Obvio... si tenemos los elementos por un lado y el acumulado por columnas por otro, dividimos ;-)
En K13 añadimos:
=LAMBDA(arr;ci;cf;
LET(eltos;INDICE(arr;SECUENCIA(6);SECUENCIA(1;cf-ci+1;ci;1));
totales;BYCOL(eltos;LAMBDA(col;SUMA(col)));
eltos/totales))($B$2:$G$7;2;5)

El resultado es una matriz de pesos o ponderaciones!!

Último paso. Dos posibles finales...
Fin A: Acabamos con una función BYROW directamente sobre los resultador anteriores
En K21:
=BYROW(LAMBDA(arr;ci;cf;
LET(eltos;INDICE(arr;SECUENCIA(6);SECUENCIA(1;cf-ci+1;ci;1));
totales;BYCOL(eltos;LAMBDA(col;SUMA(col)));
eltos/totales))($B$2:$G$7;2;5);LAMBDA(nFila;PROMEDIO(nFila)))
la función BYROW se encarga de calcular el promedio de cada fila...

Fin B (mi elección): Terminamos con una función LAMBDA que directamente permite alimentar nuestros tres argumentos...
En N21 añadimos:
=LAMBDA(matriz;ci;cf;BYROW(fxPorcINDEX(matriz;ci;cf);LAMBDA(nFila;PROMEDIO(nFila))))($B$2:$G$7;2;5)
función que luego trasladaremos al Administrador de nombres, y llamaré fxMediaPesos...

A parte de lo 'raro' del cálculo, me parece interesante cómo los argumentos de LAMBDA se transmiten de un nivel a otro inferior..
Fíjate como la función 'fxPorcINDEX' se alimenta con los parámetros cargados en la función LAMBDA principal...
Brutal sin duda.

Seguir leyendo...

LAMBDA: conversor fechas universal

Una problemática habitual al trabajar con datos descargados de documentos wen, pdf, txt, etc... es el formato en que vienen las fechas... de formas muy dispares: DMA, DAM, MDA, MAD, ADM o AMD.
Esto obviamente genera un conflicto al trabajar dentro de Excel, ya que las fechas se intentan 'acoplar' al formato preestablecido en nuestro sistema.
Veamos la imagen siguiente donde comprobamos un caso problemático:

Comprobamos como las fechas originales en formato mm/dd/aaaa:
11/10/2021, 11/13/2021, 12/31/2021, 02/01/2022, 06/01/2022, 01/13/2022
han 'colapsado' y Excel ha tratado de leerlas como fechas cuando ha sido posible y adecuarlas a la configuración regional de Windows de nuestro equipo.. en mi caso: dd/mm/aaaa...
Y como hemos comprobado en la imagen algunas de ellas las ha leído como dd/mm en vez de mm/dd.. mientras que otras directamente las considera como texto... Irritante :(

Soluciones hay varias, pero he optado por comprobar la efictividad de LAMBDA para crear un conversor de fechas.
Así pues el objetivo es pasar de las fechas leídas por Excel a su antojo, previa indicación manual del formato original (esto es, como debemos entender la fecha original), para que nuestra fórmula la convierta a una fecha real pero de acuerdo a nuestra configuración.

Notemos en la imagem como nuestra fórmula trata correctamente tanto las fechas almacendas como texto como las incorrectamente leídas!!.

Nuestra fórmula en D2 (imaginarás que no es precisamente corta) es:
=LAMBDA(fecha;formatoActual;
LET(fechaTransf;SI(ESNUMERO(fecha);TEXTO(fecha;"dd/mm/aaaa hh:mm");fecha&" 00:00");
fechaLimpia;IZQUIERDA(fechaTransf;ENCONTRAR(" ";fechaTransf)-1);
Pos;K.ESIMO.MAYOR((EXTRAE(fechaLimpia;SECUENCIA(LARGO(fechaLimpia));1)="/")*(SECUENCIA(LARGO(fechaLimpia)));{1;2});
primer;EXTRAE(fechaLimpia;1;MIN(Pos)-1);segundo;EXTRAE(fechaLimpia;MIN(Pos)+1;MAX(Pos)-MIN(Pos)-1);tercer;EXTRAE(fechaLimpia;MAX(Pos)+1;LARGO(fechaLimpia));
PosFA;K.ESIMO.MAYOR((EXTRAE(formatoActual;SECUENCIA(LARGO(formatoActual));1)="/")*(SECUENCIA(LARGO(formatoActual)));{1;2});
primerFA;EXTRAE(formatoActual;1;1);segundoFA;EXTRAE(formatoActual;MIN(PosFA)+1;1);tercerFA;EXTRAE(formatoActual;MAX(PosFA)+1;1);
vDMA;ELEGIR({1\4;2\5;3\6};primerFA;segundoFA;tercerFA;primer;segundo;tercer);
FECHA(BUSCARV("a";vDMA;2;0);BUSCARV("m";vDMA;2;0);BUSCARV("d";vDMA;2;0))))(B2;$B$1)

Nuestra función LAMBDA (que he optado por no cargarla en el Administrador de nombres definidos) requiere dos argumentos: la fecha original y el formato de partida, y devuelve una fecha ajustada a nuestra configuración.

Me he apoyado en la función LET (ideal para estos casos) que facilita la definición de variables y/o pasos intermedios...

Los dos primeros pasos:
fechaTransf;SI(ESNUMERO(fecha);TEXTO(fecha;"dd/mm/aaaa hh:mm");fecha&" 00:00");
fechaLimpia;IZQUIERDA(fechaTransf;ENCONTRAR(" ";fechaTransf)-1);
Se encargan de homogeniezar la fecha de partida/original y tratarla en todo caso como una cadena de texto con la información que requerimos.

En los siguientes pasos:
Pos;K.ESIMO.MAYOR((EXTRAE(fechaLimpia;SECUENCIA(LARGO(fechaLimpia));1)="/")*(SECUENCIA(LARGO(fechaLimpia)));{1;2});
primer;EXTRAE(fechaLimpia;1;MIN(Pos)-1);segundo;EXTRAE(fechaLimpia;MIN(Pos)+1;MAX(Pos)-MIN(Pos)-1);tercer;EXTRAE(fechaLimpia;MAX(Pos)+1;LARGO(fechaLimpia));
Obtenemos un vector con las posiciones de los separadores de fecha '/' de la cadena de texto anterior (i.e. de la fecha tratada).
Y con ese vector de posiciones somos capaces con funciones básicas de texto de recuperar los caracteres numéricos que representarán el día, el mes y el año de acuerdo al argumento formato original que hayamos indicadao...

De forma similar a lo anterior conseguimos extraer la secuencia DD, MM, AAAA del formato original:
PosFA;K.ESIMO.MAYOR((EXTRAE(formatoActual;SECUENCIA(LARGO(formatoActual));1)="/")*(SECUENCIA(LARGO(formatoActual)));{1;2});
primerFA;EXTRAE(formatoActual;1;1);segundoFA;EXTRAE(formatoActual;MIN(PosFA)+1;1);tercerFA;EXTRAE(formatoActual;MAX(PosFA)+1;1);

Obviamente el vector o secuencia cambiará según nuestro argumento...

Con los vectores generados creamos una matriz 'virtual' que mostrará las equivalencias entre ambos vectores, el de los tres valores numéricos recuperados y lo que significan (día, mes o año):
vDMA;ELEGIR({1\4;2\5;3\6};primerFA;segundoFA;tercerFA;primer;segundo;tercer);

En el último paso aprovechamos la función FECHA para informar correctamente cuál es el año, cuál es el mes y cuál el día:
FECHA(BUSCARV("a";vDMA;2;0);BUSCARV("m";vDMA;2;0);BUSCARV("d";vDMA;2;0))))
lo que nos retornará una fecha (quizá sin formato) a la que podremos aplicar cualquiera de los formatos de fechas existentes o personalizados que deseemos... con la seguridad, con independencia del aspecto, que estaremos trabajando con la fecha adecuada.

En la imagen siguiente vemos algunos ejemplos de la conversión.. donde se muestra la matriz de equivalencias que se genera en el proceso de manera interna:

Comprueba que parece funcionar correctamente para todas las opciones/combinaciones de tipo de fechas existentes...

No sé si existirá alguna forma más rápida (yo no la pude encontrar)... pero efectiva es ;-)

Seguir leyendo...

LAMBDA: Anexar rangos discontinuos

Profundizamos un poco más en el mundo LAMBDA de Excel. Hoy con un clásico: Anexar rangos discontinuos de celdas.
la idea y la línea de trabajo la tomé de un artículo que publiqué hace tiempo sobre el mismo asunto...
Se trataba de anexar distintos rangos de celdas basándose en el uso de la función INDICE que nos habilita trabajar sobre distintas áreas...

Sobre esa idea, para el ejemplo de hoy, partimos de un conjunto de cuatro rangos:

La fórmula da bastante miedo (hasta a mí, que soy el padre de la criatura jeje):
=LET(rangos;($E$2:$F$6;$B$1:$C$1;$B$3:$C$5;$B$7:$C$8);
totalfilas;REDUCE(0;SECUENCIA(AREAS(rangos));LAMBDA(ac;a;ac+CuentaFilasArray2(($E$2:$F$6;$B$1:$C$1;$B$3:$C$5;$B$7:$C$8);a)));
pos;SCAN(0;SECUENCIA(AREAS(rangos));LAMBDA(ac;a;ac+CuentaFilasArray2(($E$2:$F$6;$B$1:$C$1;$B$3:$C$5;$B$7:$C$8);a)));
filas;SCAN(0;SECUENCIA(AREAS(rangos));LAMBDA(ac;a;CuentaFilasArray2(($E$2:$F$6;$B$1:$C$1;$B$3:$C$5;$B$7:$C$8);a)));
matrizAreasPosiciones;ARCHIVOMAKEARRAY(totalfilas;2;
LAMBDA(f;c;COINCIDIRX(f;pos;1;2)&"_"&(INDICE(filas;COINCIDIRX(f;pos;1;2);0)+f-INDICE(pos;COINCIDIRX(f;pos;1;2);0))&"|"&c));
area;--IZQUIERDA(matrizAreasPosiciones);
fila;--EXTRAE(matrizAreasPosiciones;ENCONTRAR("_";matrizAreasPosiciones)+1;1);
col;--DERECHA(matrizAreasPosiciones;LARGO(matrizAreasPosiciones)-ENCONTRAR("|";matrizAreasPosiciones));
INDICE(rangos;fila;col;area))

Lo interesante de esta fórmula es que no estamos limitados a un número de rangos... podemos incorporar cuantos necesitemos!!.

Puntos a saber. El conjunto de rangos se deben incluir siempre entre paréntesis.
Se ha creado una función LAMBDA llamada 'CuentaFilasArray2' con la siguiente formulación:
=LAMBDA(rangos;pos;SUMA(BYROW(INDICE((rangos);0;0;pos);LAMBDA(arr;FILAS(arr)))))
función que realiza un conteo del número de filas de un rango dado.

Por otra parte la función LET nos permite ir gestionando mediante variables la creación de valores, vectores y matrices...
Por ejemplo, la variable:
totalfilas;REDUCE(0;SECUENCIA(AREAS(rangos));LAMBDA(ac;a;ac+CuentaFilasArray2(($E$2:$F$6;$B$1:$C$1;$B$3:$C$5;$B$7:$C$8);a)))
retorna, mediante REDUCE, la suma acumulada de todas las filas de los rangos discontinuos indicados.

Con la variable:
pos;SCAN(0;SECUENCIA(AREAS(rangos));LAMBDA(ac;a;ac+CuentaFilasArray2(($E$2:$F$6;$B$1:$C$1;$B$3:$C$5;$B$7:$C$8);a)))
obtenemos un vector con el número de filas de cada rango en modo incremental.

La variable:
filas;SCAN(0;SECUENCIA(AREAS(rangos));LAMBDA(ac;a;CuentaFilasArray2(($E$2:$F$6;$B$1:$C$1;$B$3:$C$5;$B$7:$C$8);a)))
nos devuelve el vector del número de filas de cada rango implicado.

El objetivo de estos cálculos es construir una matriz informativa que identifique todas las posiciones de filas, columnas y el rango concreto (entre los seleccionados)... lo que conseguimos en el paso siguiente:
matrizAreasPosiciones;ARCHIVOMAKEARRAY(totalfilas;2;
LAMBDA(f;c;COINCIDIRX(f;pos;1;2)&"_"&(INDICE(filas;COINCIDIRX(f;pos;1;2);0)+f-INDICE(pos;COINCIDIRX(f;pos;1;2);0))&"|"&c))

Cada elemento de esa matriz indica en primer término cuál es el número del área del rango, entre los seleccionados, seguido de la posición de fila y columna dentro de cada rango!!... Dispuestos dentro de una matriz única.

Los pasos siguientes son muy obvios... desde cada elemento de la matriz previa recuperamos:
area;--IZQUIERDA(matrizAreasPosiciones);
fila;--EXTRAE(matrizAreasPosiciones;ENCONTRAR("_";matrizAreasPosiciones)+1;1);
col;--DERECHA(matrizAreasPosiciones;LARGO(matrizAreasPosiciones)-ENCONTRAR("|";matrizAreasPosiciones));
el número del área, la fila y la columna.

Para finalizar componiendo nuestra matriz anexada resultante:
INDICE(rangos;fila;col;area)

Una gran pega con la que me he encontrado es que LAMBDA no admite el trabajo sobre conjuntos de rangos :'(
Esto impide dinamizar más la selección e incorporación de nuevos rangos de una forma sencilla...
Aún así, creo que la fórmula es bastante potente y válida.

Abierto a mejoras.

Seguir leyendo...