LAMBDA: Buscar en cualquier parte de una matriz

Continuando con las nuevas funciones LAMBDA hoy destinaremos nuestro tiempo a realizar una búsqueda de un valor en cualquier parte de una matriz... y recuperar un valor asociado.

Obviamente exiten otras formas de llegar al mismo fin, pero me interesa investigar posibilidades de las funciones LAMBDA. Para el caso de hoy emplearé entre otras las funciones REDUCE, BYROW y LAMBDA.

La meta a lograr es que sobre el rango de celdas B2:E8, se pretende recuperar una lista de los paises correspondientes al valor buscado en la celda B10.
Nota que se puede dar el caso de repeticiones tanto por fila como por columna Primera posibilidad en la celda C10 añadiríamos la fórmula:
=UNIRCADENAS(",";VERDADERO;
SI.ERROR(INDICE(E2:E8;LET(vector;LET(arr;(B2:D8=B10)*(FILA(B2:D8)-1);BYROW(arr;LAMBDA(fila;MAX(fila))));SI(vector=0;"";vector)));""))

Una fórmula algo elaborada (quizá se pueda simplificar??)... Analicémosla.
Una parte importante es la matriz (B2:D8=B10)*(FILA(B2:D8)-1) que devuelve un conjunto de ceros o de número de fila para aquellos casos donde se localice el valor buscado

Sobre esa matriz aplicamos la función BYROW, recorriéndola fila por fila, obteniendo finalmente un vector con los valores máximos de cada fila, esto es, aunque haya varias coincidencias en una misma fila, tendremos un único valor por fila.

Finalmente dentro del contexto de la función LET aplicamos una condición 'SI' para reemplazar los ceros del vector (las no coincidencias) por el 'vacío'... con el fin de facilitar el posterior concatenado con UNIRCADENAS.

En una segunda propuesta usaré la funcion REDUCE que nos permitirá, en este caso, recorrer un vector de paises obtenido a partir de una función BYROW.
Veamos la fórmula añadida en C13:
=REDUCE(;
SI.ERROR(INDICE(E2:E8;BYROW(B2:D8;LAMBDA(fila;SI(COINCIDIR(B10;fila;0);FILA(fila)-1;0))));"");
LAMBDA(ac;x;UNIRCADENAS(",";VERDADERO;ac;x)))

En este caso, de forma similar a la primera fórmula, con BYROW conseguimos un vector de los paises donde se han hallado las coincidencias del valor buscado...
La función
BYROW(B2:D8;LAMBDA(fila;SI(COINCIDIR(B10;fila;0);FILA(fila)-1;0)))
nos devuelve un vector con los números de fila donde se han encontrado el valor buscado...
Estas filas las aplicamos posteriormente dentro de un INDICE para obtener los paises (que es lo que nos interesa).
Este vector de paises y celdas vacías.

La función REDUCE se encargará de recorrer cada celda de ese vector de países para aplicar la transformación indicada en LAMBDA, que es básicamente una acción de unir celdas (con UNIRCADENAS).

Sin duda se abren nuevas puertas en nuestro camino ;-)

Seguir leyendo...

LAMBDA recursiva

Uno de los aspectos más relevantes de la nueva función LAMBDA es la posibilidad de referirse a sí misma, esto es, de ser una función recursiva.
Esto puede parecer baladí, pero abre un sinfín de posibilidades... La posibilidad de poder trabajar con el resultado previo obtenido por la misma función para aplicar de nuevo la transformación necesitada, es sin duda alguna, lo más parecido a los bucles en programación.

Hay muchas publicaciones con ejemplos de esta recursividad empleando LAMBDA... pero uno especialmente claro es el que publicó Leila Gharani (desconozco si fué ella la autora original).
Ella planteaba una fórmula para realizar un reemplazamiento masivo basado en una lista de sustituciones.
br/> Probando las nuevas funciones LAMBDA dí con una solución alternativa sin hacer uso de una función sobre sí misma... apoyándome en la función REDUCE que recuerda que se apoyo en el resultado transformado previo para aplicar dicha transformación... esto es, trabaja sobre datos secuenciales acumulados.
Mi función es:
=REDUCE(B13;
BYROW(B3:C9;LAMBDA(a;INDICE(a;0;1)&"|"&INDICE(a;0;2)));
LAMBDA(acum;dato;SUSTITUIR(acum;IZQUIERDA(dato;ENCONTRAR("|";dato)-1);DERECHA(dato;LARGO(dato)-ENCONTRAR("|";dato)))))

Fíjate en el uso de BYROW para obtener un vector que unifica o concatena el dato a reemplazar con el dato por el que se desea sustituir... para luego, en el contexto de 'acumulado/secuencia' de la función REDUCE emplear una función LAMBDA que provoca la transformación línea a línea
:OOO

Un punto a favor, o no... jeje, es que esta fórmula no es necesaria incrustarla en el Administrador de nombres si no queremos.. aspecto importante a mi modo de ver ;-)

Por cierto, la función que planteaba Leila era:
=LAMBDA(txt;esto;por;SI(esto="";txt;SUST(SUSTITUIR(txt;esto;por);DESREF(esto;1;0);DESREF(por;1;0))))
Es fundamental anticipar como llamaremos a nuestra función (SUST en mi caso), ya que se hace necesario emplearla en el desarrollo inicial.
Lo recomendable es escribir la fórmula tal cual en una celda cualquiera (no te preocupes si te devuelve un error.. es normal, esta función solo opera correctamente bajo el paragüas de los nombres definidos).

Cuando hayas acabado de escribirla copiala y accede al Administrador de nombres definidos y crea uno nuevo con el nombre 'SUST', incluyendo en el campo de 'Se refiere a' la fórmula copiada.

Ya puedes hacer uso de la función...

Observa como en el resultado obtenido se han producido todos los reemplazamientos dados en la matriz B3:C9.
En realidad se mantiene el ciclo de sustituciones mientras encuentre celdas no vacías... ;-)

Profundizaremos más sobre este interesante comportamiento!!

Seguir leyendo...

Funciones LAMBDA: REDUCE, MAP, SCAN, BYCOL, BYROW

Si la función LAMBDA ha sido un paso de gigante para Excel, no podemos olvidarnos del paquete de funciones que vienen con ella: REDUCE, MAP, SCAN, BYCOL, BYROW o MAKEARRAY

En general todas estas funciones tienen la peculiaridad de trabajar, de una forma u otra sobre matrices de información, a las que aplica una transformación empleando la función LAMBDA.
De alguna manera podríamos decir que se comportan a modo de bucle o recorrido por los valores de la matriz dada.
Conozcamóslas...
BYCOL: Aplica LAMBDA a cada columna y devuelve un vector columna con los resultados de la transformación resultante de Lambda.
Su sintáxis es muy sencilla:
=BYCOL(columna; LAMBDA(columna))

BYROW: Aplica LAMBDA a cada fila y devuelve un vector columna con los resultados de la transformación resultante de Lambda.
Su sintáxis es muy sencilla:
=BYROW(fila; LAMBDA(fila))

Veamos un ejemplo de estas sencillas funciones... Si disponemos de un matriz de valores en A2:C7 y queremos obtener una fila y una columna de subtotales:

Al añadir en la celda D2 la función:
=BYROW(A2:C7;LAMBDA(fila;SUMA(fila)))
obtenemos un vector de valores que recorre nuestra matriz, recuperando para cada fila la operación deseada.
De forma similar en la celda A8 insertamos:
=BYCOL(A2:C7;LAMBDA(columna;PROMEDIO(columna)))
obteniendo un vector donde asocia para cada columna el cálculo descrito por Lambda.

Otra de las funciones es MAKEARRAY ('traducida' en español como 'ARCHIVOMAKEARRAY' por qué???): Que nos devuelve una matriz calculada de un tamaño de fila y columna especificado, aplicando LAMBDA a cada elemento de dicha matriz.
Su sintáxis sería:
=MAKEARRAY(filas; columnas; LAMBDA(fila; columna))

Un ejemplo simple nos permitiría crear una matriz que identifique cada elemento de la matriz por sus posiciones (fila 1 - col 1, fila 1- col 2, fila 2 - col 1, etc...)
En cualquier celda escribiríamos:
=ARCHIVOMAKEARRAY(3;2;LAMBDA(fila;col;--(fila&col)))
con lo que obtenemos una matriz de 3 filas x 2 columnas y como elementos su identificación de fila&columna

Un ejemplo más elaborado de esta función nos permitiría convertir un vector en una matriz...
Como vemos en la imagen, a partir del vector en G8:G13, podemos obtener la matriz siguiente:

La fórmula empleada para llegar a este resultado es:
=LET(arrPos;ARCHIVOMAKEARRAY(3;2;LAMBDA(fila;col;--(fila&col)));
arrPosF;COINCIDIR(arrPos;K.ESIMO.MENOR(arrPos;SECUENCIA(6)));
INDICE(G8:G13;arrPosF))
Puedes echar un vistazo al artículo publicado hace algún tiempo en este blog.

Otras funciones más interesantes de las que disponemos serían...

MAP: Devuelve una matriz formada por la asignación de cada valor de las matrices a un nuevo valor aplicando LAMBDA para crear un nuevo valor.
Su sintáxis:
=MAP(array1; LAMBDA_o_array)
Esta función, en definitiva recorre cada elemento ('mapea') de la matriz, aplicándole la transformación indicada con LAMBDA, y recuperando una matriz de igual dimensión tratada...
Por ejemplo

La fórmula aplicada:
=MAP($A$1:$A$6;LAMBDA(param1;SI(ES.PAR(param1);param1;"-")))
recorre la matriz aplicando el condicional indicado por LAMBDA, determinando si es par o no...

LLegamos a las funciones interesantes de verdad...
La siguiente función SCAN examina una matriz aplicando LAMBDA a cada valor y devuelve una matriz que tiene cada valor intermedio.
La sintáxis será:
SCAN ([initial_value]; array; LAMBDA(accumulator; value))
donde:
[initial_value]: establece el valor inicial del acumulador.
array: matriz que se va a examinar.
función LAMBDA a la que se llama para examinar la matriz. Y sus dos importantes parámetros:
accumulator: Valor total y devuelto como resultado final.
value: Cálculo aplicado a cada elemento de la matriz.

Con SCAN conseguimos una matriz con valores secuenciales, calculados en base al resultado previo obtenido por la aplicación de LAMBDA...
Por ejemplo, un acumulado creciente (running total) en valores absolutos y en valores porcentuales.

Las fórmulas aplicadas son:
En C2 añadimos:
=SCAN(0;A1:A6;LAMBDA(acum;param1;acum+param1))
y en D2 insertamos:
=LET(total;SUMA(A1:A6);SCAN(0;A1:A6;LAMBDA(acum;param1;(acum+param1)))/total)

Y por fin, desde mi punto de vista la más importante de todas, nuestra última función es REDUCE, la cual reduce una matriz a un valor acumulado aplicando LAMBDA a cada valor y devolviendo el valor total en el acumulador.
Esto es, hace algo similar a lo que desarrolla SCAN, pero solo retorna la última transformación.. después de todos los pasos intermedios aplicados.
Nuestra sintáxis:
=REDUCE([initial_value]; array; LAMBDA(accumulator; value))
donde:
[initial_value]: establece el valor inicial del acumulador.
array: matriz que se va a 'reducir'.
función LAMBDA a la que se llama para reducir. Y sus dos importantes parámetros:
accumulator: Valor total y devuelto como resultado final.
value: Cálculo aplicado a cada elemento de la matriz.

Por ejemplo, para recuperar el valor acumulado último de una suma corrida

La fórmula del ejemplo es:
=REDUCE(0;A1:A6;LAMBDA(acum;param1;acum+param1))
Quizá pienses que es poco útil y muy 'tonta', pero esta función nos permite aplicar recorridos sobre matrices y trabajar sobre el resultado previo, para de nuevo aplicar la siguiente operación... En definitiva, nos abre un mundo de movimientos y operaciones recursivas...
Lo cual veremos en la siguiente entrada ;-)

Seguir leyendo...

Funciones LAMBDA

Gran hito en la historia de Excel... ayer Microsoft lanzó a producción, para todo el mundo que disponga de Microsoft 365 (Windows y Mac) o de Excel Online, la función LAMBDA y su familia.
Quizá te preguntes por la relevancia de este hecho... simplemente te diré que esta función LAMBDA nos permitirá fácilmente crear nuestras propias funciones personalizadas, siendo estas reutilizables!!! (asignándoles un nombre descriptivo a nuestra elección).
Esta nueva función y las demás asociadas están disponibles como funciones nativas de Excel y las puedes encontrar en la categoría de lógicas (en la Bibiloteca de funciones).

El uso estándar de LAMBDA, apoyada sobrer el resto de funciones, nos permite crear nuevas funciones que reemplzarían nuestras fórmulas complejas de uso habitual, facilitando así acciones básicas como las de copiar y pegar estas fórmulas.
Por otra parte, estas funciones personalizadas basadas en las funciones LAMBDA estarían disponibles en cualquier parte de nuestro libro de trabajo, como cualquier otra función nativa de Excel.
Un punto fuerte de LAMBDA es que no necesitas conocer VBA/macros o JavaScript/Office Script... aunque nocinones de programación, sin duda, facilitarán la creación de tus fórmulas.

Cuales son las nuevas funciones LAMBDA...

LAMBDA, BYCOL, BYROW, MAP, REDUCE, SCAN o ARCHIVOMAKEARRAY son algunas de las funciones interesantes propuestas.
Veamos la sintaxis de LAMBDA:
=LAMBDA([parámetro1, parámetro2, …,] cálculo) Los argumentos 'parámetro' (hasta 253) serían los valores que pasaríamos al cálculo de nuestra operación personalizada.
Este argumento es opcional.
El argumento 'cálculo' será la fórmula que queremos ejecutar y que sea devuelto como el resultado de la función. Debe ser el último argumento y debe devolver un resultado. Este argumento es obligatorio.

¿Cómo usarla?
Primer paso: identifica tu necesidad, y testea la fórmula genérica sobre tu hoja de cálculo.
Segundo paso: determina los parámetros o variables que alimentarán tu fórmula.
Tercer paso: crea o construye tu función LAMBDA en una celda, teniendo presente parámetros y cálculo... Y si funciona bien...
Cuarto paso: accede al Administrador de nombres definidos y agrega tu función LAMBDA a un nombre nuevo, asignándole un nombre descriptivo... Listo para usar!.

Por ejemplo, necesito obtener el número e elevado a un número cualquiera...
En una celda cualquiera escribo:
=LAMBDA(num;EXP(num))(1)
Tras verificar el resultado, accedo al Administrador de nombres definidos y creo un nuevo nombre:

Tras Aceptar ya podemos usar nuestra función en cualquier celda... con la ventaja que el intellisense de Excel detectará nuestra función

No solo el nombre de la función... también sus argumentos!!

Alimentando nuestros argumentos como a cualquier otra función... con datos o referencias a otras celdas!!.

En próximos artículos profundizaremos en esta fantástica función ya liberada!!

Seguir leyendo...

Power Query: Concatenar columnas

Hoy un pequeño truco para concatenar las distintas columnas de nuestra consulta siempre que tengan algún dato.

Una de las claves en este truco será transformar los distintos valores de los campos obtenidos con Record.FieldValues.
La función List.Transform nos permitirá aplicar una conversión a Texto (con Text.From) a cada elemento del Registro obtenido... evitando errores no deseados.
Text.Combine será la función encargada de concatenar los textos resultantes (a semejanza de la función CONCAT, CONCATENAR o UNIRCADENAS).

Si partimos de la siguiente Tabla con algunas celdas sin datos:

Después de cargar la Tabla al editor de Power Query pasaremos al editor Avanzado y escribiremos:

let
    Origen = Excel.CurrentWorkbook(){[Name="Tabla1"]}[Content],
    TipoCambiado = Table.TransformColumnTypes(Origen,{{"Fecha", type date}, {"País", type text}, {"Comercial", type text}, {"Producto", type text}, {"Unidades", Int64.Type}}),

    //OPCIÓN 1 - indicando manualmente los campos...
    Concatenado1=Table.AddColumn(TipoCambiado,
                "Prueba_Concatena1", 
                 each Text.Combine({Text.From([Fecha]),[País],[Comercial],[Producto],Text.From([Unidades])},"|")),
    
    //OPCIÓN 2 - automatizando las referencias a los campos... sean los que sean!!
     Concatenado2=Table.AddColumn(TipoCambiado,
                "Prueba_Concatena2", 
                 each Text.Combine(List.Transform(Record.FieldValues(_), Text.From),"|"))
in
    Concatenado2

Vemos la similitud entre las dos opciones... con la ventaja del segundo frente al primero, ya que la OPCIÓN 2 no requiere de nombrar las distintas columnas... lo que nos evitará fallos de programación si hubiera cambios en los nombres de las columnas...

Un detalle adicional que no debemos pasar por alto es que las celdas vacías no las concatena!!.
MUY INTERESANTE!

Seguir leyendo...