contingut
En aquests moments, Microsoft Excel té gairebé cinc-centes funcions de full de treball disponibles a través del botó de la finestra de l'assistent de funcions fx a la barra de fórmules. Aquest és un conjunt molt decent, però, tanmateix, gairebé tots els usuaris tard o d'hora es troben amb una situació en què aquesta llista no conté la funció que necessita, simplement perquè no es troba a Excel.
Fins ara, l'única manera de resoldre aquest problema eren les macros, és a dir, escrivint la teva pròpia funció definida per l'usuari (UDF = User Defined Function) en Visual Basic, que requereix coneixements de programació adequats i, de vegades, no és gens fàcil. No obstant això, amb les últimes actualitzacions d'Office 365, la situació ha canviat a millor: s'ha afegit una funció especial "embolcall" a Excel. LAMBDA. Amb la seva ajuda, la tasca de crear les vostres pròpies funcions ara es resol fàcilment i de manera bella.
Vegem el principi del seu ús en l'exemple següent.
Com probablement sabeu, Excel té diverses funcions d'anàlisi de dates que us permeten determinar el número del dia, mes, setmana i any per a una data determinada. Però per alguna raó no hi ha cap funció que determini el nombre del trimestre, que també es necessita sovint, oi? Arreglem aquesta mancança i creem amb LAMBDA nova funció pròpia per resoldre aquest problema.
Pas 1. Escriu la fórmula
Comencem pel fet que manualment de la manera habitual escriurem una fórmula en una cel·la de full que calcula el que necessitem. En el cas del número del trimestre, això es pot fer, per exemple, així:
Pas 2. Embolcall en LAMBDA i prova
Ara és el moment d'aplicar la nova funció LAMBDA i embolicar-hi la nostra fórmula. La sintaxi de la funció és la següent:
=LAMBDA(Variable 1; Variable 2; ... variable N ; Expressió)
on s'enumeren primer els noms d'una o més variables i l'últim argument sempre és una fórmula o una expressió calculada que les utilitza. Els noms de variables no han de semblar a adreces de cel·les i no han de contenir punts.
En el nostre cas, només hi haurà una variable: la data per a la qual calculem el nombre del trimestre. Anomenem-ne la variable, per exemple, d. A continuació, embolcallem la nostra fórmula en una funció LAMBDA i substituint l'adreça de la cel·la original A2 amb un nom de variable fictícia, obtenim:
Tingueu en compte que després d'aquesta transformació, la nostra fórmula (de fet, correcta!) va començar a produir un error, perquè ara la data original de la cel·la A2 no s'hi transfereix. Per a proves i confiança, podeu passar-hi arguments afegint-los després de la funció LAMBDA entre parèntesis:
Pas 3. Creeu un nom
Ara per la part fàcil i divertida. Obrim Gestor de noms llengüeta fórmula (Fórmules — Gestor de noms) i creeu un nom nou amb el botó Create (Crear). Proporcioneu i introduïu un nom per a la nostra funció futura (per exemple, Nomkvartala), i al camp Enllaç (Referència) copieu amb cura de la barra de fórmules i enganxeu la nostra funció LAMBDA, només sense l'últim argument (A2):
Tot. Després de fer clic a OK la funció creada es pot utilitzar a qualsevol cel·la de qualsevol full d'aquest llibre de treball:
Ús en altres llibres
LAMBDA i matrius dinàmiques
Funcions personalitzades creades amb una funció LAMBDA suportar amb èxit el treball amb noves matrius dinàmiques i les seves funcions (FILTRE, UNIK, GRAU) afegit a Microsoft Excel el 2020.
Suposem que volem crear una nova funció definida per l'usuari que compara dues llistes i retorna la diferència entre elles: els elements de la primera llista que no estan a la segona. L'obra de la vida, no? Anteriorment, per a això utilitzaven qualsevol de les funcions a la VPR (VISUALITZACIÓ), o taules dinàmiques o consultes de Power Query. Ara pots fer-ho amb una fórmula:
En la versió anglesa serà:
=LAMBDA(a;b;ФИЛЬТР(a;СЧЁТЕСЛИ(b;a)=0))(A1:A6;C1:C10)
Aquí la funció COUNTIF compta el nombre d'ocurrències de cada element de la primera llista de la segona, i després la funció FILTRE selecciona només aquells d'ells que no van tenir aquestes ocurrències. En embolicar aquesta estructura LAMBDA i crear un rang amb nom basat en ell amb un nom, per exemple, DISTRIBUCIÓ DE LA CERCADORA – obtindrem una funció convenient que retorna el resultat de comparar dues llistes en forma de matriu dinàmica:
Si les dades d'origen no són taules normals, sinó "intel·ligents", la nostra funció també ho farà sense problemes:
Un altre exemple és dividir text dinàmicament convertint-lo a XML i després analitzar-lo cel·la per cel·la mitjançant la funció FILTER.XML que hem analitzat recentment. Per no reproduir manualment aquesta complexa fórmula cada cop, serà més fàcil embolicar-la en LAMBDA i crear-ne un rang dinàmic basat en ella, és a dir, una nova funció compacta i còmoda, anomenant-la, per exemple, RAZDTEXT:
El primer argument d'aquesta funció serà la cel·la amb el text font, i el segon, el caràcter separador, i retornarà el resultat en forma d'una matriu dinàmica horitzontal. El codi de funció serà el següent:
=LAMBDA(t;d; TRANSPOSE(FILTER.XML(“
La llista d'exemples és infinita: en qualsevol situació en què sovint hagueu d'introduir la mateixa fórmula llarga i feixuga, la funció LAMBDA us facilitarà notablement la vida.
Enumeració recursiva de caràcters
Tots els exemples anteriors han mostrat només un, el més obvi, de la funció LAMBDA: el seu ús com a "embolcall" per embolicar-hi fórmules llargues i simplificar-ne l'entrada. De fet, LAMBDA té un altre costat, molt més profund, que el converteix gairebé en un llenguatge de programació complet.
El fet és que una característica fonamentalment important de les funcions LAMBDA és la capacitat d'implementar-les recursivitat – lògica dels càlculs, quan en procés de càlcul la funció es crida a si mateixa. Per l'hàbit, pot semblar esgarrifós, però en programació, la recursivitat és una cosa habitual. Fins i tot en macros de Visual Basic, podeu implementar-lo, i ara, com podeu veure, ha arribat a Excel. Intentem entendre aquesta tècnica amb un exemple pràctic.
Suposem que volem crear una funció definida per l'usuari que eliminaria tots els caràcters donats del text font. Crec que no cal demostrar la utilitat d'aquesta funció; seria molt convenient esborrar les dades d'entrada amb la seva ajuda, oi?
Tanmateix, en comparació amb els exemples anteriors, no recursius, ens esperen dues dificultats.
- Haurem d'aconseguir un nom per a la nostra funció abans de començar a escriure el seu codi, perquè en ella, aquest nom ja s'utilitzarà per cridar la funció en si.
- Introduir aquesta funció recursiva en una cel·la i depurar-la especificant arguments entre parèntesis després de LAMBDA (com vam fer abans) no funcionarà. Haureu de crear una funció immediatament "des de zero". Gestor de noms (Nom Gestor).
Anem a cridar la nostra funció, per exemple, NETEJA i ens agradaria que tingués dos arguments: el text a netejar i la llista de caràcters exclosos com a cadena de text:
Creem, com hem fet anteriorment, a la pestanya fórmula в Gestor de noms rang amb nom, anomena-lo CLEAR i introduïu-lo al camp Abast construcció següent:
=LAMBDA(t;d;IF(d=””;t;BORRAR(SUBSTITUIR(t;ESQUERRA(d);””);MITJÀ(d;2;255))))
Aquí la variable t és el text original que s'ha d'esborrar i d és la llista de caràcters que s'han d'esborrar.
Tot funciona així:
Iteració 1
El fragment SUBSTITUTE(t;LEFT(d);””), com podeu suposar, substitueix el primer caràcter del caràcter esquerre del conjunt d que s'ha d'esborrar del text d'origen t per una cadena de text buida, és a dir, elimina el “ A”. Com a resultat intermedi, obtenim:
Vsh zkz n 125 rubles.
Iteració 2
Aleshores, la funció es crida a si mateixa i com a entrada (el primer argument) rep el que queda després de netejar al pas anterior, i el segon argument és la cadena de caràcters exclosos que comença no pel primer, sinó pel segon caràcter, és a dir, “BVGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYA. ," sense la "A" inicial; això ho fa la funció MID. Com abans, la funció pren el primer caràcter de l'esquerra dels restants (B) i el substitueix al text que se li ha donat (Zkz n 125 rubles) per una cadena buida; obtenim com a resultat intermedi:
125 ru.
Iteració 3
La funció es torna a cridar a si mateixa, rebent com a primer argument el que queda del text per esborrar a la iteració anterior (Bsh zkz n 125 ru.), I com a segon argument, el conjunt de caràcters exclosos truncats per un caràcter més a l'esquerra, és a dir, “VGDEEGZIKLMNOPRSTUFHTSCHSHSHCHYYYYUYA.”, sense la inicial “B”. A continuació, torna a agafar el primer caràcter de l'esquerra (B) d'aquest conjunt i l'elimina del text: obtenim:
sh zkz n 125 ru.
I així successivament, espero que entenguis la idea. Amb cada iteració, la llista de caràcters a eliminar es truncarà a l'esquerra, i buscarem i substituirem el següent caràcter del conjunt per un buit.
Quan s'acabin tots els personatges, haurem de sortir del bucle: aquesta funció només la realitza la funció IF (SI), en què s'embolica el nostre disseny. Si no queden caràcters per esborrar (d=””), aleshores la funció ja no hauria d'anomenar-se a si mateixa, sinó que simplement hauria de retornar el text per esborrar (variable t) en la seva forma final.
Iteració recursiva de cèl·lules
De la mateixa manera, podeu implementar una enumeració recursiva de cel·les en un interval determinat. Suposem que volem crear una funció lambda anomenada LLISTA DE SUBSTITUCIÓ per a la substitució a l'engròs de fragments en el text font segons una llista de referència determinada. El resultat hauria de ser així:
Aquells. a la nostra funció LLISTA DE SUBSTITUCIÓ hi haurà tres arguments:
- cel·la amb text a processar (adreça d'origen)
- la primera cel·la d'una columna amb valors per cercar des de la cerca
- la primera cel·la de la columna amb valors de substitució de la cerca
La funció hauria d'anar de dalt a baix al directori i substituir seqüencialment totes les opcions de la columna de l'esquerra Trobar a les opcions corresponents de la columna de la dreta Suplent. Podeu implementar-ho amb la següent funció lambda recursiva:
El canvi cap avall a cada iteració s'implementa mitjançant una funció d'Excel estàndard DISPOSICIÓ (OFFSET), que en aquest cas té tres arguments: l'interval original, el desplaçament de fila (1) i el desplaçament de columna (0).
Bé, tan bon punt arribem al final del directori (n = “”), hem d'acabar la recursivitat: deixem d'anomenar-nos i mostrem el que s'ha acumulat després de totes les substitucions a la variable de text font t.
Això és tot. No hi ha macros complicades ni consultes de Power Query: tota la tasca es resol amb una funció.
- Com utilitzar les noves funcions de matriu dinàmica d'Excel: FILTER, SORT, UNIC
- Substitució i neteja de text amb la funció SUBSTITUTE
- Creació de macros i funcions definides per l'usuari (UDF) a VBA