Contenido
Por el momento, Microsoft Excel tiene casi quinientas funciones de hojas de trabajo disponibles a través de la ventana Asistente de funciones: botón fx en la barra de fórmulas. Este es un conjunto muy decente, pero, sin embargo, casi todos los usuarios, tarde o temprano, se encuentran con una situación en la que esta lista no contiene la función que necesita, simplemente porque no está en Excel.
Hasta ahora, la única forma de resolver este problema eran las macros, es decir, escribir su propia función definida por el usuario (UDF = Función definida por el usuario) en Visual Basic, lo que requiere habilidades de programación adecuadas y, a veces, no es nada fácil. Sin embargo, con las últimas actualizaciones de Office 365, la situación ha cambiado para mejor: se ha agregado una función especial de "envoltorio" a Excel. LAMBDA. Con su ayuda, la tarea de crear sus propias funciones ahora se resuelve de manera fácil y hermosa.
Veamos el principio de su uso en el siguiente ejemplo.
Como probablemente sepa, Excel tiene varias funciones de análisis de fechas que le permiten determinar el número del día, mes, semana y año para una fecha determinada. Pero por alguna razón, no hay una función que determine el número del cuarto, que también se necesita a menudo, ¿verdad? Solucionemos esta deficiencia y creemos con LAMBDA nueva función propia para resolver este problema.
Paso 1. Escribe la fórmula
Comencemos con el hecho de que manualmente, de la manera habitual, escribiremos una fórmula en una celda de hoja que calcule lo que necesitamos. En el caso del número de cuarto, esto se puede hacer, por ejemplo, así:
Paso 2. Terminando en LAMBDA y probando
Ahora es el momento de aplicar la nueva función LAMBDA y envolver nuestra fórmula en ella. La sintaxis de la función es la siguiente:
=LAMBDA(variable1; variable2; ... VariableN ; expresión)
donde los nombres de una o más variables se enumeran primero y el último argumento siempre es una fórmula o una expresión calculada que los usa. Los nombres de las variables no deben parecerse a las direcciones de las celdas y no deben contener puntos.
En nuestro caso, solo habrá una variable: la fecha para la que calculamos el número de trimestre. Llamemos a la variable para ello, digamos, d. Luego envolviendo nuestra fórmula en una función LAMBDA y sustituyendo la dirección de la celda original A2 por un nombre de variable ficticio, obtenemos:
Tenga en cuenta que después de tal transformación, nuestra fórmula (¡de hecho, correcta!) Comenzó a producir un error, porque ahora la fecha original de la celda A2 no se transfiere a ella. Para probar y confiar, puede pasarle argumentos agregándolos después de la función LAMBDA entre paréntesis:
Paso 3. Crea un nombre
Ahora viene la parte fácil y divertida. Nosotros abrimos Administrador de nombre de la pestaña. fórmula (Fórmulas — Administrador de nombres) y crea un nuevo nombre con el botón Crear (Crear). Proponga e ingrese un nombre para nuestra futura función (por ejemplo, Nomkvartala) y en el campo Enlace (Referencia) copie cuidadosamente de la barra de fórmulas y pegue nuestra función LAMBDA, solo que sin el último argumento (A2):
Todo. Después de hacer clic en OK la función creada se puede usar en cualquier celda en cualquier hoja de este libro:
Usar en otros libros
LAMBDA y matrices dinámicas
Funciones personalizadas creadas con una función LAMBDA admitir con éxito el trabajo con nuevas matrices dinámicas y sus funciones (FILTRO, ÚNICO, GRADO) agregado a Microsoft Excel en 2020.
Digamos que queremos crear una nueva función definida por el usuario que compare dos listas y devuelva la diferencia entre ellas: los elementos de la primera lista que no están en la segunda. El trabajo de la vida, ¿no? Anteriormente, para esto usaban funciones a la VPR (VLOOKUP), tablas dinámicas o consultas de Power Query. Ahora puedes hacerlo con una fórmula:
En la versión en inglés será:
=LAMBDA(a;b;ФИЛЬТР(a;СЧЁТЕСЛИ(b;a)=0))(A1:A6;C1:C10)
Aquí la función COUNTIF cuenta el número de ocurrencias de cada elemento de la primera lista en la segunda, y luego la función FILTRO selecciona solo aquellos de ellos que no tuvieron estas ocurrencias. Al envolver esta estructura en LAMBDA y crear un rango con nombre basado en él con un nombre, por ejemplo, DISTRIBUCIÓN DE BÚSQUEDA – obtendremos una función conveniente que devuelve el resultado de comparar dos listas en forma de matriz dinámica:
Si los datos de origen no son ordinarios, sino tablas "inteligentes", nuestra función también se las arreglará sin problemas:
Otro ejemplo es dividir texto dinámicamente al convertirlo a XML y luego analizarlo celda por celda usando la función FILTER.XML que analizamos recientemente. Para no reproducir esta compleja fórmula manualmente cada vez, será más fácil envolverla en LAMBDA y crear un rango dinámico basado en ella, es decir, una nueva función compacta y conveniente, nombrándola, por ejemplo, RAZDTEXT:
El primer argumento de esta función será la celda con el texto de origen, y el segundo, el carácter separador, y devolverá el resultado en forma de una matriz dinámica horizontal. El código de la función será el siguiente:
=LAMBDA(t;d; TRANSPONER(FILTRO.XML(“
La lista de ejemplos es interminable: en cualquier situación en la que a menudo tenga que ingresar la misma fórmula larga y engorrosa, la función LAMBDA le hará la vida notablemente más fácil.
Enumeración recursiva de caracteres
Todos los ejemplos anteriores han mostrado solo un lado, el más obvio, de la función LAMBDA: su uso como un "envoltorio" para envolver fórmulas largas y simplificar su entrada. De hecho, LAMBDA tiene otro lado, mucho más profundo, que lo convierte casi en un lenguaje de programación completo.
El hecho es que una característica fundamentalmente importante de las funciones de LAMBDA es la capacidad de implementarlas en recursividad – lógica de cálculos, cuando en el proceso de cálculo la función se llama a sí misma. Por costumbre, puede sonar espeluznante, pero en la programación, la recursividad es algo común. Incluso en macros en Visual Basic puedes implementarlo, y ahora, como puedes ver, ha llegado a Excel. Tratemos de entender esta técnica con un ejemplo práctico.
Supongamos que queremos crear una función definida por el usuario que elimine todos los caracteres dados del texto de origen. Creo que no necesita probar la utilidad de tal función; sería muy conveniente borrar los datos de entrada desordenados con su ayuda, ¿verdad?
Sin embargo, en comparación con los ejemplos anteriores, no recursivos, nos esperan dos dificultades.
- Tendremos que pensar en un nombre para nuestra función antes de comenzar a escribir su código, porque en él, este nombre ya se usará para llamar a la función en sí.
- Ingresar una función recursiva de este tipo en una celda y depurarla especificando argumentos entre paréntesis después de LAMBDA (como hicimos antes) no funcionará. Tendrá que crear una función inmediatamente "desde cero" en Administrador de nombre (Gerente de nombre).
Llamemos a nuestra función, digamos, CLEAN y nos gustaría que tuviera dos argumentos: el texto que se limpiará y la lista de caracteres excluidos como una cadena de texto:
Vamos a crear, como hicimos antes, en la pestaña fórmula в Administrador de nombres rango con nombre, asígnele el nombre CLEAR e ingrese en el campo Gama de Colores siguiente construcción:
=LAMBDA(t;d;SI(d=””;t;BORRAR(SUSTITUIR(t;IZQUIERDA(d);””);MEDIO(d;2;255))))
Aquí la variable t es el texto original a borrar, y d es la lista de caracteres a borrar.
Todo funciona así:
1 iteración
El fragmento SUBSTITUTE(t;LEFT(d);””), como puede suponer, reemplaza el primer carácter del carácter izquierdo del conjunto d que se eliminará en el texto fuente t con una cadena de texto vacía, es decir, elimina el “ A". Como resultado intermedio, obtenemos:
Vsh zkz n 125 rublos.
2 iteración
Luego, la función se llama a sí misma y como entrada (el primer argumento) recibe lo que queda después de la limpieza en el paso anterior, y el segundo argumento es la cadena de caracteres excluidos que comienza no desde el primero, sino desde el segundo carácter, es decir, “BVGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYYA. ”, sin la “A” inicial; esto lo hace la función MID. Como antes, la función toma el primer carácter de la izquierda de los restantes (B) y lo reemplaza en el texto que se le da (Zkz n 125 rublos) con una cadena vacía; obtenemos como resultado intermedio:
125 ru.
3 iteración
La función se vuelve a llamar a sí misma, recibiendo como primer argumento lo que queda del texto a borrar en la iteración anterior (Bsh zkz n 125 ru.), y como segundo argumento, el conjunto de caracteres excluidos truncados por un carácter más para la izquierda, es decir, “VGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYUYA.”, sin la inicial “B”. Luego, nuevamente toma el primer carácter de la izquierda (B) de este conjunto y lo elimina del texto; obtenemos:
sh zkz n 125 ru.
Y así sucesivamente, espero que entiendas la idea. Con cada iteración, la lista de caracteres que se eliminarán se truncará a la izquierda y buscaremos y reemplazaremos el siguiente carácter del conjunto con un vacío.
Cuando se agoten todos los caracteres, tendremos que salir del ciclo; esta función solo la realiza la función IF (SI), en el que se envuelve nuestro diseño. Si no quedan caracteres para borrar (d=””), entonces la función ya no debería llamarse a sí misma, sino que simplemente debería devolver el texto a borrar (variable t) en su forma final.
iteración recursiva de celdas
De manera similar, puede implementar una enumeración recursiva de celdas en un rango determinado. Supongamos que queremos crear una función lambda llamada LISTA DE REEMPLAZO para la sustitución al por mayor de fragmentos en el texto fuente de acuerdo con una lista de referencia dada. El resultado debería verse así:
Aquellos. en nuestra funcion LISTA DE REEMPLAZO Habrá tres argumentos:
- celda con texto para procesar (dirección de origen)
- la primera celda de una columna con valores para buscar desde la búsqueda
- la primera celda de la columna con valores de reemplazo de la búsqueda
La función debe ir de arriba a abajo en el directorio y reemplazar secuencialmente todas las opciones de la columna izquierda Encontrar a las opciones correspondientes de la columna de la derecha Suplente. Puede implementar esto con la siguiente función lambda recursiva:
El cambio hacia abajo en cada iteración se implementa mediante una función estándar de Excel ELIMINACIÓN (COMPENSAR), que en este caso tiene tres argumentos: el rango original, el cambio de fila (1) y el cambio de columna (0).
Bueno, tan pronto como lleguemos al final del directorio (n = “”), debemos finalizar la recursividad: dejamos de llamarnos a nosotros mismos y mostramos lo que se ha acumulado después de todos los reemplazos en la variable de texto fuente t.
Eso es todo. Sin macros complicadas ni consultas de Power Query: toda la tarea se resuelve con una sola función.
- Cómo usar las nuevas funciones de matrices dinámicas de Excel: FILTRO, ORDENAR, UNIC
- Reemplazar y limpiar texto con la función SUSTITUIR
- Creación de macros y funciones definidas por el usuario (UDF) en VBA