Contidos
Polo momento, Microsoft Excel ten case cincocentas funcións de follas de traballo dispoñibles a través do botón Asistente de funcións fx na barra de fórmulas. Este é un conxunto moi decente, pero, con todo, case todos os usuarios tarde ou cedo atopan unha situación na que esta lista non contén a función que necesita, simplemente porque non está en Excel.
Ata o de agora, a única forma de solucionar este problema eran as macros, é dicir, escribir a túa propia función definida polo usuario (UDF = User Defined Function) en Visual Basic, que require unhas habilidades de programación adecuadas e que, ás veces, non é nada fácil. Non obstante, coas últimas actualizacións de Office 365, a situación cambiou para mellor: engadiuse unha función especial de "envoltorio" a Excel. LAMBDA. Coa súa axuda, a tarefa de crear as súas propias funcións agora resólvese de xeito sinxelo e fermoso.
Vexamos o principio do seu uso no seguinte exemplo.
Como probablemente sabes, Excel ten varias funcións de análise de datas que che permiten determinar o número do día, mes, semana e ano para unha data determinada. Pero por algún motivo non existe ningunha función que determine o número do trimestre, que tamén se necesita moitas veces, non? Imos corrixir esta deficiencia e crear con LAMBDA propia nova función para resolver este problema.
Paso 1. Escribe a fórmula
Comecemos co feito de que manualmente da forma habitual escribiremos unha fórmula nunha cela da folla que calcule o que necesitamos. No caso do número do trimestre, pódese facer, por exemplo, así:
Paso 2. Conclusión en LAMBDA e proba
Agora toca aplicar a nova función LAMBDA e envolver nela a nosa fórmula. A sintaxe da función é a seguinte:
=LAMBDA(Variable 1; Variable 2; ... Variable N ; expresión)
onde aparecen primeiro os nomes dunha ou máis variables e o último argumento é sempre unha fórmula ou unha expresión calculada que as utiliza. Os nomes das variables non deben parecer enderezos de cela e non deben conter puntos.
No noso caso, só haberá unha variable: a data para a que calculamos o número do trimestre. Chamemos á variable, digamos, d. Despois envolvemos a nosa fórmula nunha función LAMBDA e substituíndo o enderezo da cela orixinal A2 por un nome de variable ficticio, obtemos:
Teña en conta que despois de tal transformación, a nosa fórmula (de feito, correcta!) comezou a producir un erro, porque agora a data orixinal da cela A2 non se transfire a ela. Para probas e confianza, podes pasarlle argumentos engadíndoos despois da función LAMBDA entre parénteses:
Paso 3. Crea un nome
Agora a parte fácil e divertida. Abrimos Xestor de nomes aba fórmula (Fórmulas — Xestor de nomes) e crea un novo nome co botón crear (Crear). Crea e introduce un nome para a nosa función futura (por exemplo, Nomkvartala), e no campo ligazón (Referencia) copia coidadosamente desde a barra de fórmulas e pega a nosa función LAMBDA, só sen o último argumento (A2):
Todo. Despois de facer clic en OK a función creada pódese usar en calquera cela de calquera folla deste libro de traballo:
Use noutros libros
LAMBDA e matrices dinámicas
Funcións personalizadas creadas cunha función LAMBDA admite con éxito o traballo con novas matrices dinámicas e as súas funcións (FILTER, ÚNICO, GRADE) engadido a Microsoft Excel en 2020.
Digamos que queremos crear unha nova función definida polo usuario que compare dúas listas e devolva a diferenza entre elas: os elementos da primeira lista que non están na segunda. O traballo da vida, non? Anteriormente, para iso utilizaban calquera das funcións a la VPR (BÚSQUEDA V), ou táboas dinámicas ou consultas de Power Query. Agora podes facelo cunha fórmula:
Na versión en inglés será:
=LAMBDA(a;b;ФИЛЬТР(a;СЧЁТЕСЛИ(b;a)=0))(A1:A6;C1:C10)
Aquí a función COUNTIF conta o número de ocorrencias de cada elemento da primeira lista na segunda e despois a función FILTER selecciona só aqueles deles que non tiveron estas ocorrencias. Ao envolver esta estrutura LAMBDA e crear un intervalo con nome baseado nel cun nome, por exemplo, DISTRIBUCIÓN DE BUSCA – obteremos unha función conveniente que devolve o resultado de comparar dúas listas en forma de matriz dinámica:
Se os datos de orixe non son táboas comúns, senón "intelixentes", a nosa función tamén funcionará sen problemas:
Outro exemplo é dividir o texto de forma dinámica converténdoo en XML e despois analizalo cela por cela mediante a función FILTER.XML que analizamos recentemente. Para non reproducir manualmente esta complexa fórmula cada vez, será máis doado envolvela en LAMBDA e crear un rango dinámico baseado nela, é dicir, unha nova función compacta e cómoda, nomeándoa, por exemplo, RAZDTEXT:
O primeiro argumento desta función será a cela co texto fonte, e o segundo, o carácter separador, e devolverá o resultado en forma de matriz dinámica horizontal. O código da función será o seguinte:
=LAMBDA(t;d; TRANSPOSE(FILTRO.XML(“
A lista de exemplos é infinita: en calquera situación na que moitas veces teñas que introducir a mesma fórmula longa e engorrosa, a función LAMBDA facilitaralle a vida.
Enumeración recursiva de caracteres
Todos os exemplos anteriores mostraron só un lado, o máis obvio, da función LAMBDA: o seu uso como "envoltorio" para envolver fórmulas longas nela e simplificar a súa entrada. De feito, LAMBDA ten outro lado, moito máis profundo, que o converte nunha linguaxe de programación case completa.
O feito é que unha característica fundamentalmente importante das funcións LAMBDA é a capacidade de implementalas recursión – lóxica dos cálculos, cando no proceso de cálculo a función se chama a si mesma. Desde o hábito, pode parecer arrepiante, pero na programación, a recursión é algo común. Incluso nas macros en Visual Basic, podes implementala e agora, como podes ver, chegou a Excel. Imos tentar entender esta técnica cun exemplo práctico.
Supoñamos que queremos crear unha función definida polo usuario que elimine todos os caracteres dados do texto fonte. A utilidade desta función, creo, non é necesario probar: sería moi conveniente borrar os datos de entrada lisos coa súa axuda, non?
Porén, fronte aos exemplos anteriores, non recursivos, agárdannos dúas dificultades.
- Teremos que dar un nome para a nosa función antes de comezar a escribir o seu código, porque nela, este nome xa se utilizará para chamar á propia función.
- Introducir unha función tan recursiva nunha cela e depurala especificando argumentos entre corchetes despois de LAMBDA (como fixemos anteriormente) non funcionará. Terás que crear unha función inmediatamente "desde cero". Xestor de nomes (Xestor de nomes).
Chamemos á nosa función, por exemplo, LIMPO e gustaríanos que tivese dous argumentos: o texto a limpar e a lista de caracteres excluídos como cadea de texto:
Imos crear, como fixemos anteriormente, na pestana fórmula в Nome xestor rango nomeado, nomee BORRAR e introduza no campo Alcance seguinte construción:
=LAMBDA(t;d;IF(d=””;t;BORRAR(SUBSTITUIR(t;ESQUERDA(d);””);MID(d;2;255))))
Aquí a variable t é o texto orixinal que se quere borrar e d é a lista de caracteres que se borrarán.
Todo funciona así:
Iteración 1
O fragmento SUBSTITUTE(t;LEFT(d);””), como podes adiviñar, substitúe o primeiro carácter do carácter esquerdo do conxunto d que se borrará no texto fonte t cunha cadea de texto baleira, é dicir, elimina o “ A”. Como resultado intermedio, obtemos:
Vsh zkz n 125 rublos.
Iteración 2
Entón a función chámase a si mesma e como entrada (o primeiro argumento) recibe o que queda despois da limpeza no paso anterior, e o segundo argumento é a cadea de caracteres excluídos que comeza non polo primeiro, senón polo segundo carácter, é dicir, “BVGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYA. ," sen a inicial "A": isto faise coa función MID. Como antes, a función toma o primeiro carácter da esquerda dos restantes (B) e substitúeo no texto que se lle proporciona (Zkz n 125 rublos) por unha cadea baleira; obtemos como resultado intermedio:
125 ru.
Iteración 3
A función chámase de novo a si mesma, recibindo como primeiro argumento o que queda do texto a borrar na iteración anterior (Bsh zkz n 125 ru.), E como segundo argumento, o conxunto de caracteres excluídos truncados por un carácter máis a a esquerda, é dicir, "VGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYUYA.", sen a inicial "B". Despois volve tomar o primeiro carácter da esquerda (B) deste conxunto e elimínao do texto: obtemos:
sh zkz n 125 ru.
E así por diante, espero que teñades a idea. Con cada iteración, a lista de caracteres a eliminar truncarase á esquerda, e buscaremos e substituiremos o seguinte carácter do conxunto por un baleiro.
Cando se esgoten todos os personaxes, necesitaremos saír do bucle; este papel só o realiza a función IF (SI), no que está envolto o noso deseño. Se non quedan caracteres para eliminar (d=””), entón a función xa non debería chamarse a si mesma, senón que debería simplemente devolver o texto que se borrará (variable t) na súa forma final.
Iteración recursiva de células
Do mesmo xeito, pode implementar unha enumeración recursiva de celas nun intervalo determinado. Supoñamos que queremos crear unha función lambda chamada LISTA DE SUSTITUCIÓN para a substitución por xunto de fragmentos no texto fonte segundo unha lista de referencias determinada. O resultado debería verse así:
Eses. na nosa función LISTA DE SUSTITUCIÓN haberá tres argumentos:
- cela con texto a procesar (enderezo de orixe)
- a primeira cela dunha columna con valores para buscar desde a busca
- a primeira cela da columna con valores de substitución da busca
A función debería ir de arriba a abaixo no directorio e substituír secuencialmente todas as opcións da columna esquerda Atopar ás opcións correspondentes da columna da dereita Suplente. Podes implementar isto coa seguinte función lambda recursiva:
O desprazamento cara abaixo en cada iteración é implementado mediante unha función estándar de Excel ELIMINACIÓN (COMPLEMENTO), que neste caso ten tres argumentos: o intervalo orixinal, o desprazamento de fila (1) e o desprazamento de columna (0).
Ben, axiña que chegamos ao final do directorio (n = ""), debemos rematar a recursividade: deixamos de chamarnos a nós mesmos e mostramos o acumulado despois de todas as substitucións na variable de texto fonte t.
Iso é todo. Non hai macros complicadas nin consultas de Power Query: toda a tarefa resólvese cunha función.
- Como usar as novas funcións de matriz dinámica de Excel: FILTRO, ORDENAR, UNIC
- Substitución e limpeza de texto coa función SUBSTITUTE
- Creación de macros e funcións definidas polo usuario (UDF) en VBA