Funciones personalizadas en Excel: LET, LAMBDA y recursividad

Funciones personalizadas en Excel: LET, LAMBDA y recursividad

Cómo pasar de una fórmula escrita a mano a una función propia que puedes llamar por su nombre en cualquier libro. Los tres vídeos de esta página desarrollan el mismo ejemplo de principio a fin: una función MI.RANKING que agrupa, ordena y devuelve el top N de una tabla.

La descarga incluye los dos ficheros de ejemplo: EjemploRankingconLambda.xlsx (vídeos 1 y 3) y CasoRecursividad.xlsx (vídeo 2).

1. Del cálculo desglosado a la función (LET y LAMBDA)

- Montar el ranking paso a paso con UNICOS, SUMAR.SI.CONJUNTO, APILARH, ORDENAR y TOMAR
- Comprimir todos esos pasos en un único LET, reutilizando cada variable ya declarada
- Convertirlo en LAMBDA sustituyendo los rangos fijos por parámetros de entrada
- Guardarlo como función con nombre desde Fórmulas → Administrador de nombres

2. Moldear la salida con LET

- Añadir una fila "otras" que acumule el importe que se queda fuera del top
- TOMAR con índice negativo para quedarse con la última columna del resultado
- APILARV para pegar la fila nueva bajo el ranking

3. Recursividad: una LAMBDA que se llama a sí misma

- Por qué una función recursiva necesita un parámetro de límite
- La condición de parada, que es lo que impide el bucle infinito
- Aplicaciones reales: valoraciones FIFO y LIFO, precios promedio, proyecciones y tratamiento de textos

El truco que ata los tres: primero se resuelve el cálculo a la vista, en celdas separadas; solo después se comprime. Intentar escribir la LAMBDA directamente es lo que hace que la gente abandone.

Casi todo el mundo que intenta escribir su primera LAMBDA lo hace en el orden equivocado: abre una celda en blanco y trata de sacarla entera de la cabeza. Así es como se abandona. El camino que funciona es el contrario, y es el que siguen los tres vídeos de esta página sobre un mismo ejemplo: primero resuelves el cálculo a la vista, en celdas separadas, y solo cuando funciona lo comprimes.

El objetivo es una función propia, MI.RANKING, a la que le pasas una columna de elementos, una de importes y un número, y te devuelve el top N ya agrupado y ordenado.

Primero, el cálculo a la vista

El ranking sale de encadenar cuatro pasos, cada uno en su columna:

  1. UNICOS sobre la columna de elementos, para saber por qué filas agrupas.
  2. SUMAR.SI.CONJUNTO para asignar a cada elemento su importe. El criterio apunta al desbordamiento del paso anterior con la almohadilla (F2#), no a una celda suelta.
  3. APILARH para juntar esas dos matrices en una sola, que ya se puede tratar como un bloque.
  4. ORDENAR por la segunda columna con sentido descendente, y TOMAR para quedarte con las N primeras filas.

Con eso ya tienes el resultado. Lo que no tienes todavía es algo reutilizable: son cuatro columnas auxiliares atadas a unos rangos concretos.

LET: comprimir sin perder el hilo

LET declara variables y las reutiliza más abajo, así que cada paso intermedio se convierte en una línea con nombre:

=LET(
    filas;A2:A100;
    valores;D2:D100;
    top;3;
    lista_filas;UNICOS(filas);
    lista_valores;SUMAR.SI.CONJUNTO(valores;filas;lista_filas);
    rango_resultado;APILARH(lista_filas;lista_valores);
    rango_ordenado;ORDENAR(rango_resultado;2;-1);
    salida;TOMAR(rango_ordenado;top);
    salida
)

Hay un truco de depuración que conviene conocer: si te pierdes a mitad de una LET larga, pon como último argumento la última variable que acabas de declarar en vez de la final. Excel te enseña ese resultado intermedio y ves exactamente en qué punto estás. Luego sigues escribiendo.

LAMBDA: cambiar los rangos fijos por parámetros

La LET anterior sigue apuntando a A2:A100. LAMBDA es lo que la despega de esa hoja concreta: declaras los parámetros de entrada y sustituyes cada rango fijo por su nombre.

=LAMBDA(rango_lista;rango_valores;top;
    LET(
        lista_filas;UNICOS(rango_lista);
        lista_valores;SUMAR.SI.CONJUNTO(rango_valores;rango_lista;lista_filas);
        rango_ordenado;ORDENAR(APILARH(lista_filas;lista_valores);2;-1);
        TOMAR(rango_ordenado;top)
    )
)

Si la escribes en una celda tal cual, Excel devuelve #¡CALC!. No es un error tuyo: una LAMBDA en celda necesita que le pases los valores de ejecución en un paréntesis final, =LAMBDA(...)(B2:B100;E2:E100;3). Es la forma de probarla antes de guardarla.

Convertirla en una función con nombre

Cuando funciona, copias la fórmula sin ese paréntesis de ejecución, vas a Fórmulas → Administrador de nombres, creas un nombre nuevo (MI.RANKING) y la pegas en el campo de referencia. A partir de ahí aparece en el autocompletado como una función más del libro.

Moldear la salida: la fila "otras"

El segundo vídeo nace de una petición de un espectador: que además del top salga una fila con todo lo que se queda fuera. Es un buen ejemplo de que con LET la salida es plastilina, no está obligada a ser el resultado pelado del último cálculo.

    suma_total;SUMA(rango_valores);
    suma_ranking;SUMA(TOMAR(salida_ranking;;-1));
    resto;suma_total-suma_ranking;
    fila_resto;APILARH("otras";resto);
    salida;APILARV(salida_ranking;fila_resto)

Fíjate en TOMAR(salida_ranking;;-1): el segundo argumento vacío significa todas las filas, y el -1 del tercero coge la última columna. En TOMAR, los números positivos cuentan desde la izquierda y los negativos desde la derecha.

Recursividad: una función que se llama a sí misma

El tercer vídeo da el salto conceptual. Una función recursiva se invoca a sí misma hasta que se cumple una condición, y esa condición de parada es la pieza obligatoria: sin ella el bucle no termina.

El ejemplo mínimo: partiendo de un número, multiplícalo por 3 y réstale 1, repitiendo hasta superar un límite.

=LAMBDA(valor;limite;
    LET(
        resultado;valor*3-1;
        SI(valor>limite;valor;MI.FUNCION(resultado;limite))
    )
)

Las dos claves están a la vista. La primera es que el límite tiene que ser un parámetro más, porque hay que ir arrastrándolo en cada llamada para poder seguir comprobándolo. La segunda es el SI: si ya se superó el límite devuelve el valor y se para; si no, se llama otra vez con el resultado recién calculado. Con límite 1.000 partiendo de 1, devuelve 1.094, que es el primer número de la serie que lo supera.

Un aviso práctico: al añadir el parámetro limite a una función ya guardada, todas las celdas que la usaban se rompen, porque la firma ha cambiado. Es esperado, no un fallo.

Esta técnica es la base de cosas que no se resuelven bien de otra forma: valoraciones FIFO y LIFO, precios promedio, proyecciones y sustituciones encadenadas de texto. Es parecida a SCAN, pero con bastante más margen.

Funciones clave

  • LET — declara variables reutilizables; convierte un churro ilegible en pasos con nombre
  • LAMBDA — despega el cálculo de unos rangos concretos y lo vuelve reutilizable
  • UNICOS y SUMAR.SI.CONJUNTO — el agrupar por, hecho a mano y en dos matrices
  • APILARH y APILARV — juntan matrices en horizontal y en vertical
  • ORDENAR — ordena por el índice de columna que le digas, con sentido ascendente o descendente
  • TOMAR — recorta filas o columnas; con índice negativo cuenta desde el final

Conclusión

Lo valioso aquí no es la función de ranking, es el método: desglosar, comprimir con LET, parametrizar con LAMBDA y guardar con nombre. Ese recorrido sirve igual para cualquier cálculo que estés repitiendo a mano en veinte libros distintos.

Todas estas funciones son de Microsoft 365 y no están disponibles en versiones como Office 2016.

Más casos con estas funciones

Más contenido de Excel en InflueXcel